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
- 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.
- 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.
- 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.
- 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.
Eugene Wallingford .....
wallingf@cs.uni.edu .....
September 3, 2020