有一题目,弄的很迷糊,上来请教各位高手,大侠!~

4thehonor 2005-11-30 05:30:28
题目如下:
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个,本人实在是菜鸟,希望各位帮帮忙!~
...全文
157 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
lovcal 2005-11-30
  • 打赏
  • 举报
回复
上面的tHire就是rentals(租赁信息表),可以统计客户与DVD之间的租赁信息

E-R图就不画了,不好描述……呵呵
talantlee 2005-11-30
  • 打赏
  • 举报
回复
太長,基本要求是建立幾個表
custom(CurstomID,name,address,createdate,buyedcount,hiredcount)
DvdInfo(DvdId,Nanme,PurchaseDate,intime,outtime,enable)
FilmInfo(FilmID,DvdID,Detail,director,country,actorInfo)
HiredInfo(HiredID,DvdID,Hiredtime,CurstomID,returntime,enable)
PurchaseInfo(SellID,DvdID,SellTime,CurstomID,enable)
lovcal 2005-11-30
  • 打赏
  • 举报
回复

tCustomer:ID(PK),Name,Address,TotalDVD,RegistedDate
tDVD:ID(PK),FilmNum,PurchasedDate
tHire:pk_hired(PK),CustomID(FK),DvdID(FK),HiredDate,ReturnDate(关系表)
tDirector:ID(PK),name,born,death
tFilm:ID(PK),title,madedate,director,stars,categoryid(FK)
tCategory:ID(PK),action adventure, science fiction, horror, romance, comedy, classic children's(属性表)
tFilmDVD:filmID(FK),DvdID(FK),hireType(关系表)
tStart:ID(PK),name,birthplace,born,death
lizq2004 2005-11-30
  • 打赏
  • 举报
回复
顶一下吧!
lxzm1001 2005-11-30
  • 打赏
  • 举报
回复
看不懂啊

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧