You are required to design and implement a database system for a DVD rental shop.
Each customer has a unique membership number. For each customer, you keep his/her name, address, the total number of DVDs the customer is currently hiring, and the date the customer first registered with the shop.
You also keep information about the DVDs that the shop owns. Each DVD is assigned a unique number. For each DVD, you store the number of the recorded film, and the date the DVD was purchased.
You want to keep track of all DVDs hired and produce monthly statements for customers, so you also need to store information about all rentals, i.e. the DVD numbers and the dates the DVDs were hired and returned.
In addition, you gather and organise facts on films, directors and film stars. For each director, you
record his/her unique number, name, the year in which he/she was born, and the year of death (if deceased). For each film star, you record his/her unique number, name, birthplace, the year in which he/she was born, and the year of death (if deceased).
For each film, you record its unique number, title, the year in which it was made, its category (action adventure, science fiction, horror, romance, comedy, classic, children’s), its director, and all stars that appeared in it. For each film, you also want to store the type of DVD hire (new release, classics, other).
The following assumptions are made:
• Each DVD contains one film.
• Each film is directed by one director.
• A film has only one category.
• You want to store information about films that you do not have on the DVDs.
You want to store information about directors and film stars whose films are not recorded in
• your database.
Note: The number of DVDs hired by a customer can be calculated using the record of all rentals. However, in order to improve performance of some queries, you also store the total number of DVDs each customer is currently hiring.
Based on the above requirements, you are required to carry out the following tasks:
A Develop an Entity-Relationship model of the information requirements for the above system including a description of the necessary data integrity constraints.
B Translate your model into an equivalent set of relations. Specify all relation headings, indicating primary and foreign keys. Transform the relations to Third Normal Form and explain why they are in Third Normal Form.
Translate the set of relations into an equivalent set of SQL tables.
Populate the SQL tables with sample data.
C Write the following queries in SQL and provide screenshots of the results:
1. List the unique numbers and names of all customers who are currently hiring more than 2 DVDs.
2. List the unique numbers, names, and ages of all film stars who are deceased.
3. Count the number of customers who are currently hiring DVDs.
4. List the unique numbers and names of all customers who are hiring more DVDs than the average number of DVDs currently hired by all customers.
1. List the unique numbers of all DVDs currently hired by customer with <some membership number>. Also show the hire dates.
2. Find the unique numbers and names of all directors who have never directed a science fiction film.
3. List the unique numbers and names of all stars that have appeared in at least one comedy.
List all DVDs that were hired in October 2015 (including those that were not returned in October). For each DVD, list its unique number and the number of times it was hired. Sort the DVDs in ascending order of DVD numbers
You are required to submit a report including your E-R diagram, data integrity constraints, a set of relations, an explanation why the relations are in Third Normal Form, SQL table definitions, any comments you may have on the design and implementation, as well as the formulation of the queries requested in Task C. Each query should be accompanied by a screenshot of data retrieved from the database.
Place your order now to enjoy great discounts on this or a similar topic.
People choose us because we provide:
Essays written from scratch, 100% original,
Delivery within deadlines,
Competitive prices and excellent quality,
24/7 customer support,
Priority on their privacy,
Unlimited free revisions upon request, and
Plagiarism free work,