有一题目,弄的很迷糊,上来请教各位高手,大侠!~
题目如下:
Problem
You are required to design and implement a database system for a DVDrental 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/her 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.
Tasks
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.
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 and define suitable integrity constraints.
Populate the SQL tables with sample data.
C Write the following queries in SQL:
1. List the numbers and names of all customers who are currently hiring more than 2 DVDs.
2. List the 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 numbers and names of all customers who are hiring more DVDs than the average number of DVDs currently hired by all customers.
5. List the numbers of all DVDs currently hired by customer with membership number, say, 11. Show also the hire dates.
6. Find the numbers and names of all directors who have never directed a science fiction film.
7. List the numbers and names of all stars that have appeared in at least one comedy.
List all DVDs that were hired in, say, March 2005(INCLUDING THOSE THAT WERE NOT RETURNED IN MARCH). For each DVD, list its number and the number of times it was hired. Sort the DVDs in ascending order of DVD numbers.
题目就是上面的了,我画了7个表格,和同学交流他说一个多对多的关系所以他又多退了个表格出来共8个,本人实在是菜鸟,希望各位帮帮忙!~