ISDS 3110: Database Management with SQL

Lab #3: 1:m modeling

Part. I Review examples on one-to-many modeling slides.

Part. II Please draw ER Diagram and write relational data model for the following business.

1. An art researcher has asked you to design a database to record details of artists and the museums in which their paintings are displayed. For each painting, the researcher wants to know the size of the canvas, year painted, title, and style. The nationality, date of birth, and death of each artist must be recorded. For each museum, record details of its location and specialty, if it has one. A museum can display many paintings and an artist can have multiple paintings in her/his life.
2. Delta Airlines needs a database to support the following facts. Please design the database with ER model and relational data model for Delta. An aircraft can fly many different flights and a passenger can be on only one flight at a time. Further, Delta needs to track the make, model and year of each aircraft, and first name, last name, member ID, drive license No, gender, seat No of each passenger. For each flight, flight No, origin, destination, date, dep time and arrive time should be recorded.
3. The manager of the StayHome Videos shop requires you to create a database application system to assist with the administration of the shop. The requirements collection and analysis phase has provided the following requirements for the database.
   • The data stored on the videos for rent includes the video number, catalog number, title, certification, year of release, main actor/actress, purchase price, and daily rental rate. The video number is created by the manager and uniquely identifies each video.
   • The video shop stocks videos that fall into the following categories - comedy, drama, horror, musical, science fiction, children and adults only. The video catalog number uniquely identifies each video category.
   • Data on suppliers of videos should also be stored, such as supplier number, name, street, city, state, zip code, and telephone number.
   • A supplier can provide zero or more videos; a video only can be purchased from one supplier.
   • A video can be classified to zero or one category; a category can have zero to many videos.

Submit your ER model and Relational Data Model in a SINGLE DOCUMENT (word or pdf) via the Moodle shell. Please name your submission as LAB3_firstname_lastname.