Descriptions:
Using your knowledge on Entity-Relation Model to draw an ER-diagram, convert the diagram into relations and perform a set of database operations for the following application related to music CD industry.
1) A CD has a title, a year of production and a CD type. You can come with you own CD types.
2) A CD usually has multiple songs on different tracks. Each song has a name, an artist and a track number. Entity set Song is considered to be weak and needs support from entity set CD.
3) A CD is produced by a producer which has a name and an address.
4) A CD may be supplied by multiple suppliers, each has a name and an address.
5) A customer may rent multiple CDs. Customer information such as Social Security Number (SSN), name, telephone needs to be recorded. The date and period of renting (in days) should also be recorded.
6) A customer may be a regular member and a VIP member. A VIP member has additional information such as the starting date of VIP status and percentage of discount.
Task 1(30%):
Draw an ER-diagram based on the above description. Do not forget to specify keys. Pay special attentions to cardinalities of relationships, weak entity sets and entity sets involved in ISA hierarchies. The diagram should be drawn based the notations discussed in the lectures. The diagram should be generated by a computer program (e.g., WORD). Hand-drawn diagram will NOT be accepted to avoid ambiguity.
Task 2 (30%):
Convert the entity sets and relationships to database relations. The relations should be able to be realized in MySQL database, i.e., write SQL statements. Pay attentions to the supporting relationships and weak entity sets. Entity corresponding to child classes in ISA hierarchies should follow E/R style.
Task 3 (40%):
Write a menu-drive program (e.g. using PHP) to enable the following queries. The weight for each query has been specified (out the 40%). You will need to supply with your own test data.
1) Insert a producer (5%)
2) Insert a CD supplied by a particular supplier and produced by a particular producer (6%)
3) Insert a regular-customer borrowing a particular CD (6%)
4) Insert a VIP customer borrowing a particular CD (7%)
5) Find names and Tel# of all customers who borrowed a particular CD and are supposed to return by a particular date. (8%)
6) List producers information who produce CD of a particular artist released in a particular year. (8%)
Comentarios