Homework Assignment 1

ER and Relational Data Models

CS 3140
Database Systems
Fall Semester 2020

Due: Thursday, September 10, at 8:00 AM

Introduction

The Problems

Problems 1-2 deal with conceptual modeling and ER diagrams. Problems 3-4 deal with logical modeling and relational models.

The Deliverable

Create a single PDF file named homework01.pdf that contains the text and diagrams for all of your solutions.

Problems

1. Design a database to store information about a legislature's voting activity. The database must keep track of each state's name (for example, "Iowa") and region (for example, "Midwest"). States contain one or more districts, each of which is served by a legislator. For each legislator, the database stores a name, the district they represent, the start date of their term, and their political party. The database stores information about every bill introduced in the legislature, including its name, the date the legislature votes on the bill, whether it passed or failed, and the sponsors of the bill (the one or more legislators who introduced the bill). It also records how each legislator voted on each bill: "yes", "no", "abstain", or "absent".
   
   Draw an ER diagram for your conceptual design. State clearly any assumptions you make in your design.


2. Design a database to store information for an airline reservation system. Each flight is identified by a flight number and consists of one or more numbered legs. (A leg is a nonstop portion of a flight.) Each leg has an originating airport, a scheduled departure time, a scheduled arrival time, and a terminating airport. It also has one or more occurrences (one for each date on which the flight travels). The database records a fare for each leg. For each occurrence, it records the airplane used and the actual departure and arrival times. An airplane is identified by its ID number and its type (for example, "jet" or "turboprop"). The system needs to know which airplane types can land at which airports, which are identified by an FAA airport code.
   
   Draw an ER diagram for your conceptual design. State clearly any assumptions you make in your design.


3. In simple cases like Problem 1, we convert an ER diagram into a relational data model by creating one schema for each entity and one schema for each relationship.
   
   Write relational schemas for your ER diagram from Problem 1. Specify the type of each attribute. If an attribute has a well-defined domain, specify that, too.


4. Problem 2 leads to a simple ER model, too.
   
   Write relational schemas for your ER diagram from Problem 2. Specify the type of each attribute. If an attribute has a well-defined domain, specify that, too.

Deliverables

By the due time and date, submit your homework01.pdf file using the electronic submission system, under course CS 3140 and assignment homework01.

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.