CIS 5500: Database and Information Systems Homework 2: Relational DB Design

发布时间：2025-09-25

Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

CIS 5500: Database and Information Systems

Homework 2: Relational DB Design

Fall 2025

This homework is about relational database design, from ER diagrams to relational schemas and their normalization.

Submission: Create one .pdf file with your answers, with the answer to each question on a separate page(s), and submit using Gradescope. Be sure to label your responses as numbered here.

Question 1 (30 points)

A. (20 points)

Model the application below using an ER diagram. Use the notation from class: cardinality constraints on edges are n..m annotations, where n and m are integers or m is * (e.g. 0..1, 1..1, 25..30, 1..*, etc); weak entity sets use bold or double boxes and the defining relationship is bold or double edged; etc. Create an electronic version of each diagram using draw.io, Powerpoint, Word, Visio, OpenOffice Draw, OmniGrae, Google Drive Document or any other software that you are familiar with, and export it to PDF format. Do not turn in handwritten diagrams as they are hard to read!

You are the data architect for Global Skyways, an international airline group, and want to model the non-passenger side of the operation, which consists of airlines, airports, aircraft, flights, pilots and attendants. In this simplified version, each flight is a single segment (one leg).

● An Airport has an IATACode (which is unique), Name, City, and Country.

● An AircraftType has a TypeCode (which is unique), Manufacturer, Model, and SeatCapacity.

● An Aircraft has a TailNumber (which is unique), InService (boolean flag) and an AircraftType.

● A Flight has an Airline and FlightNumber (which are jointly unique), Origin, Destination, DepTime, and ArrTime. Origin and Destination are Airports.

● A FlightInstance is a dated instance of a Flight. It has a Date (which is unique), Status (which can be Scheduled, Canceled or Delayed), Actual DepTime (which is nullable), ActualArrTime (which is nullable), and is assigned exactly one Aircraft.

● An Employee has an EmpID (which is unique), Name and Level. An Employee is either a Pilot or a FlightAttendant.

● Pilots have one or more Certifications of an AircraftType, each of which has a date interval in which they are valid (ValidFrom, ValidTo).

● Each FlightInstance has two or more CrewAssignments of Employees, each of which has a Duty (which includes Pilot, head flight attendant, etc).

B. (5 points)

You now wish to ensure that every FlightInstance has at least two CrewAssignments who are Pilots, and at least one other CrewAssignment. Does your ER diagram capture this? If so, explain how. If not, explain how you could modify your ER diagram to capture this.

C. (5 points)

Suppose you also want to ensure that the pilots assigned to a FlightInstance must be certified for the AircraftType that is assigned to the FlightInstance as of the time of the flight (i.e. the Date of the FlightInstance falls within the ValidFrom, ValidTo interval). Does your ER diagram from Part A or B capture this? If so, explain how. If not, explain how this constraint can be captured.

Question 2 (20 points)

The following ER diagram models Teams of Players (e.g. the NBA). Teams can win championships (e.g. the NBA Finals) and players can receive awards (e.g. MVP).

A. (12 points)

Translate the ER diagram to the relational model by specifying the resulting relations, their attributes, keys, foreign keys and NOT NULL attributes. Attributes with the annotation (O) may be null (e.g. “Position” in Players), whereas those with no annotation must have some value (i.e. are NOT NULL). Keys are underlined.

You may use the abbreviated notation used in class (e.g. “R(A, B, C) B foreign key referencing S(B), C NOT NULL” ) or SQL DDL (using whatever is natural for the domains of attributes).

B. (4 points)

Describe how you can capture the cardinality constraint 13..15 on the edge from Teams to IsOn in the relational translation.

C. (4 points)

Suppose now that you wish to enforce the fact that every player must be on a team. Discuss how the ER diagram must be changed, and how this changes your translation to the relational model (if at all).

Question 3 (40 points)

You are given a schema containing information about order shipments and a minimal set of functional dependencies F:

R(OrderID, TrackingNo, CustomerID, CustomerEmail, ShipDate, Carrier, ItemSKU, Qty)

F= { TrackingNo → OrderID, ShipDate, Carrier

OrderID → CustomerID

CustomerID → CustomerEmail

CustomerEmail → CustomerID

OrderID, ItemSKU → Qty }

Please (briefly, in 1-2 sentences) explain your answer for all parts of this question.

A. (4 points)

Can an order (OrderID) contain more than one item (multiple ItemSKUs)? Explain your answer.

B. (4 points)

Can an order (OrderID) have multiple tracking numbers? Explain your answer.

C. (6 points)

What are the candidate keys for R? Prove your answer.

D. (5 points)

Which (if any) of the functional dependencies in F violate 3NF?

E. (4 points)

Does the following decomposition of R have a lossless join? Explain your answer.

R1(TrackingNo, OrderID, CustomerID, CustomerEmail)

R2(TrackingNo, ShipDate, Carrier, ItemSKU, Qty)

F. (12 points)

Using the algorithm given in class, give a decomposition of R into 3NF. For each decomposed relation Ri, give the set of functional dependencies that are preserved in Ri and its candidate keys.

G. (5 points)

Are each of the Ri in your decomposition in BCNF?