Design and implement a relational database

Reference no: EM132643502

ITECH 2004 Data Modelling - Federation University

Assignment: SQL

Overview
The purpose of this task is to develop student's skills in designing and implementing a relational database for a given case study.

Learning Outcome 1: Describe relational algebra and its relationship to Structured Query Language (SQL). S1. Interpret entity-relationship diagrams to implement a relational database.

Learning Outcome 2: Demonstrate skills in designing and building a database application using a commercially available database management system development tool.

Learning Outcome 3: Use a query language for data manipulation.

Learning Outcome 4: Design and implement a relational database using a database management system.

Learning Outcome 5: Utilise a query language tools and techniques to obtain data and information from a database.

Assessment Details

Background

In this assignment you are asked to write several SQL queries on a relational movie database. The data in this database is from the IMDB website

Requirements

Question 1. Write the SQL code to create the table structures for the entities shown in Figure 1. The diagram was created using Microsoft Access designer. The structures should contain the attributes specified in the diagram. Use data types that are appropriate for the data that will need to be stored in each attribute. Enforce primary key and foreign key constraints as indicated by the diagram.

Question 2. The following tables provide some example data that will be kept in the database. Write the INSERT commands necessary to place the following data in the tables that were created in Question 1. Alternatively provide the text files (copy and pasted into your final report) and the open/insert from file commands..

For Questions 3-18, use the tables that were created in Question 1 and the data that was loaded into those tables in Question 2.

Question 3. Write the SQL command to find the name and year of the movies that contain the word "the".

Question 4. Write the SQL command to return the name of all genres and name of movies together in a single list.

Question 5. Write the SQL command to aggregate sum the number of ratings and average number of stars per reviewer.

Question 6. Write the SQL command to list the names of the actors who did not play a role in the movie Deliverance.

Question 7. Write the SQL command to find the titles of all movies that have more than one reviewer.

Question 8. Write the SQL command to find the movie title, and the total number of actors that appeared in that movie, and arrange the result according to the number of actors in descending order.

Question 9. Write the SQL command to find the titles of all movies where the average stars rating is higher than 8.0 and the actor was Christian Bale.

Question 10. Update the director table to set the values in DETAIL_RETURNDATE to include a date of birth (dob) component, and also correct some of the name mistakes. For those not listed, assume the original name is correct, and also give a null value for dob. Make each entry match the values shown in the following Table.

Question 11. Write the SQL command to list all the directors who have not directed a movie since 2000.

Question 12. Write the SQL command to list all the last movie that each actor has appeared in.

Question 13. Write the SQL command that determines the total length of film time for all the films that an actor has appeared in.

Question 14. Write the SQL command to find movie title and number of stars for each movie that has at least one reviewer and find the least number of stars that movie received and sort the result by number of stars.

Question 15. Write the SQL command to determine if any actors worked with the same director more than once.

Question 16. Write the SQL command to list all directors in descending order of the number of films they directed.

Question 17. Write the SQL command to find the film(s) with the largest cast. Find the film(s) with the smallest cast. In both cases, also return the size of the cast.

Question 18. Write the SQL command to find the films with more women actors than men.

Attachment:- Data Modelling.rar

Reference no: EM132643502

Questions Cloud

Describe the effect on the financial statements : Describe the effect on the financial statements when an adjustment is prepared that records (a) unrecorded revenue and (b) unrecorded expense

What total amount should be reported as cash at year-end : What total amount should be reported as "cash" at year-end? At year-end, Myra Company reported cash and cash equivalents which comprised

Describe programming features available in r : Compare the statistical feature of R to its programming features. In a table format, describe the programming features available in R,

Calculate the net present value of three buildings : Calculate the net present value of three buildings, assuming a 10% cost of funds. (For calculation purposes, use 5 decimal places as displayed)

Design and implement a relational database : Design and implement a relational database using a database management system and Utilise a query language tools and techniques to obtain data and information

Determine the best financing for the purchase : Determine the best financing for the purchase of a new piece of equipment. The vendor is offering repayment options of $10,200 at the end of each year

Create the t-accounts for each account : Create the T-accounts for each account, enter the unadjusted balances, post the adjusting journal entry, and report the adjusted balance.

How systematic and unsystematic risks affect risk planning : Explain how systematic and unsystematic risks affect risk planning? Name 3 or more systematic or unsystematic risks a company might face.

Prepare the closing entry required at september : Prepare the closing entry required at September 30, 2018. (If no entry is required for a transaction/event, select "No Journal Entry Required)

User Account

All Pages