HOMEWORK #1

Instructions: You may work alone or with one or two classmates. If you work with others, you should discuss all of the problems together, rather than dividing them up between you and you should make sure you understand the problems. If you would like to work with a group, but you don’t know anyone in the class, you may post on EdStem to find partners. It would probably be helpful if you specify whether you’ll want to meet online or in person.

Hand in your solutions via Gradescope as a single PDF file. Follow the Gradescope instructions to mark your solution to each problem or subproblem as indicated in the outline; otherwise, the graders will have trouble finding them and will apply a small penalty to your score. See the student workflow section in GradeScope help to learn how

to do this. If you’re working with a group, only one of you should hand in the assignment, using GradeScope’s group submission feature to indicate all members of your group.

Note: You may find it useful to use draw.io or another drawing tool to draw ER diagrams. Alternatively, you may draw them neatly by hand. You must use the notation used in class ... rectangles for entity sets, diamonds for relationship sets, double lines and arrows for participation/cardinality constraints, etc.

Problem 1

In Orlando, Florida, there are many interesting theme parks, including the Universal Studio, Disneyland, and Legoland, attracting tourists worldwide. In this problem, you need to draw an ER diagram, similar to those shown in class, with at least three entity sets, Tourist, Theme Park, and Ride, and relationship sets in between as needed, and then answer some additional questions. A Tourist should record a unique ID, a name, composed of a first name and a last name, a date of birth, and some (zero, one, or multiple) email addresses. Each Theme Park should have a title, an address, a Google Map rating, and a description. The Ride should record its title, the average waiting time, an instruction, and minimum age restriction (for simplicity). We are assuming there aren’t two attractions that have the same name among all Theme Parks. Some of the necessary relationship sets include Visit, Favorite, and Locate. Visit describes the relationship between Tourist and Theme Park, marking that the Tourist has once visited the Theme Park. If a tourist visits one park, they may rate it with some stars and leave a comment (for example, on Google Maps). Favorite may describe the ride that one tourist enjoyed best among all parks. Each tourist has exactly one favorite ride. Locate is the relationship between Theme Park and Ride, which should also record the IP that is involved (for example, Hogwarts Express has IP Harry Potter).

a. Draw the ER diagram according to the description.

b. List some members of each of the entity sets: For example:

(12345, Joe, Smith, 1990 − 01 − 01, [email protected], [email protected]) ∈ Tourist

Your Tourist set should include yourself, using your N-number as the ID.

c. List at least one member of each of the relationship sets. (You may use any reasonable format to do this; it should be clear which entities are paired with which and which relationship set the pair belongs to.)

Problem 2

Suppose you are designing a database for an airline. The airline managers want you to design a system to keep track of their flights and airplanes for their civil aviation service for a certain day. The flights are identified by a unique flight number and store information about the departure airport, destination airport, and departure time. The airplanes are identified by a unique registration number and store other information like aircraft type and capacity. The manager wants you to store the aircraft type in a separate entity to store other information like manufacturer. The database will keep track of which flight will be undertaken by which airplane and the status of the flight arrived, in flight, or not departed yet.

a. Draw an ER diagram modeling this information. It should have an entity set representing airplanes, an entity set representing flights, an entity set representing aircraft types, and some relationship sets.

b. While reviewing this ER diagram with you, the manager suddenly realizes that there may be different layouts for the same type of airplane. As a result, the capacity may be different for the same aircraft type depending on different layouts. Draw a new ER diagram to deal with this. You may assume the attribute to determine different layouts called layout. Hint: use a weak entity set.

c. Now the airline company wants to upgrade the system to store more detailed information about the departure airport and the destination airport. They want the airport information composed of the IATA code (like JFK), the city where the airport is located, and the terminal. Besides, they want you to store the actual number of passengers in the airplane for a flight. Draw an ER diagram to meet the new requirements.

d. After checking that your system works well for a single day, the manager wants you to modify the system to keep track of the date information for every flight and keep historical data for them to analyze their flight status. Note that adding the date attributes to the relationship set is not sufficient as flights may be carried by different airplanes on different days. Instead, you can take one of the following approaches:

• use a ternary relationship set, involving an additional entity set representing dates;

• or, change the relationship set into an entity set.

You may represent it with a strong entity set by adding an identifier or with a weak entity set. Draw an ER diagram for the improved scenario.

Problem 3

In this problem, you are asked to create a management system for a bookstore, as shown in this ER diagram. This model provides data on an online bookstore’s sales.

a. Which of the following aspects of the data are represented in this model?

i. Each book has exactly one author

ii. Each book can be published by different publishers

iii. Each book has at least one category

iv. A customer can only purchase one copy for each book in one order

v. A customer can have different books in one order

vi. Each Author writes at least one book

vii. A customer can purchase two copies of the same book as long as they are in different orders

b. Derive a relational database schema from this ER diagram. You may either show it as a schema diagram or in a textual format. Indicate all primary key and foreign key constraints.