Homework Assignment 2

More ER and Relational Data Models

CS 3140
Database Systems
Fall Semester 2020

Due: Friday, September 18, at 8:00 AM

Introduction

The Problems

Problem 1 deals with weak entities. Problems 2-3 deal with converting an ER diagrams into a set of relational schemas. Problem 4 asks you to critique an ER diagram as a model.

The Deliverable

Create a single PDF file named homework02.pdf that contains the text and diagrams for all of your solutions. Please type up your answers to Problems 2-4. You can include your diagram for Problem 1 in the same document as an image.

Problems

1. Consider this scenario that involves weak entities.
   
   There are three entities: League, Team, and Player. League names are unique. No league has two teams with the same name, and no team has two players with the same name. However, there can be players of the same name on different teams, and there can be teams of the same name in different leagues.
   
   Draw an ER diagram for this system. Identify keys for each entity. State clearly any assumptions you make in your design.


2. This ER diagram models a part of the domain of sea-going vessels. Two ships are "sisters" if they are designed from the same engineering plan.
   
   
   
   Convert this ER diagram into a set of relational database schemas.


3. This ER diagram models the booking an airline customer makes on a commercial flight using a weak entity.
   
   
   
   Convert this ER diagram into a set of relational database schemas.


4. In Session 1, I used a music database such as Spotify as an example when discussing flat files. Then, in Online Session 2, I gave an ER diagram of the files in my example:
   
   
   Evaluate this ER diagram as a conceptual model of domain.
   
   Start with the design principles you learned about in that session. But also use other knowledge you have gained about ER diagrams from the course, as well as any knowledge you may have about the domain of music and music databases.

Deliverables

By the due time and date, submit your homework02.pdf file using the electronic submission system.

Be sure to use the names specified for the file you submit. This enables an autograder to find and process your submission.

If you need help or have any any questions, please ask promptly.