Shanghai New York University
Engineering and Computer Science Department
CSCI-SHU 213
Professor Ratan Dey
Homework No. : 01 Total points: 100
Due Date: 06/04/2020 11:55 pm. No Late Submission will be accepted.
Please submit electronically as either PDF or image file(s) using NYU-Classes. One PDF file having
all the solutions is preferable. If you would like to do on paper, you can scan or take pictures of your
solutions and then submit using NYU-Classes. You can use drawing tools from the website https://
www.draw.io. Homework#1 can be done individually or with a partner. If you work with a partner,
either of you can submit but please mention both of your names and NetIDs in the solution file.
HOMEWORK #1
Problem 1:
Suppose you’re designing a database for a railroad company. The manager says they want to
keep track of customers, train routes (e.g. “New Haven line”), and how many times the
customer purchased a ticket for that route. A customer has a phone number and an address.
No two customers have the same phone number. Each train route has a unique name, a line
color, and a ticket price. (Note: Different train routes may have different ticket prices.) The
database will keep track of how many of which route a customer purchased tickets for, along
with the trip status (e.g. “on time”, “delayed”, “ahead of schedule”).
1. Draw an ER diagram modeling this information. It should have an entity set representing
customers, an entity set representing routes, and one relationship set. For this part, you
need to keep only the status of customer’s last trip of a route and total number of
tickets purchased by the customer for that route.
2. While reviewing this ER diagram with you, the store manager realizes that it would be a
better idea to charge people based on how far they’re going on each line in terms of
number of stops (e.g., “New Haven line for 2 stops is $5.00” and “Hudson line for 3
stops is $8.00”). Modify the ER diagram to deal with this.
Hint: Use a weak entity set.
3. And after further thought, the manager decides that they should also keep track of the
date, time, and method of payment (e.g. “cash”, “credit card”) when each ticket was
purchased and keep historical data, so that they’ll know which customers have
purchased which tickets in the past. Modify the ER diagram to allow this. Note that
adding date and time attributes to the ordered relationship set is not sufficient, as this
still will not allow a customer to purchase the same tickets at different date/times. (Why
not?). Hint: One approach is to use a ternary relationship set, involving an additional
entity set representing dates/time.
Problem 2:
Consider the bookstore E-R on the last page of this assignment sheet. You may submit one E-R
diagram with all of these modifications. (Hint: You can use cardinality limits like l….h).
1. Modify the E-R diagram to indicate that a customer can have at most one shopping
basket.
2. Modify the E-R diagram to indicate that each book is stocked in at least one warehouse.
Problem 3
A theatre has hired you to design a database to represent information about their plays, tickets,
actors, and directors. Each person (actor or director) has a unique ID, a name, and an email
address. An actor also has an age, which is derived from their date of birth. There are several
categories of plays, each with a unique name and a description. Each play falls into one
category. Each play has a unique identification number within its category. Each play consists
of between five and twenty actors. An actor can perform in at most three plays, a play has at
least one director, and a director cannot direct more than one play. Draw an E-R diagram for
the theatre.
Problem 4:
Consider the E-R diagram at the bottom of this assignment sheet (before the modifications
from Problem 2). Make the following changes:
• Omit the warehouse entity set and the stocks relationship set.
• Omit the publisher entity set and the published by relationship set
• Change the cardinality constraints on the written_by relationship set to indicate
that every book is published by exactly one author.
• Change the phone number attribute of customer to a multi-valued attribute.
• Change the address attribute of the customer entity set and the author entity set
to a composite attribute with components “building number”, “street”, “city”,
“state”, and “zip code”.
Part-1: Draw the modified E-R diagram.
Part-2: Following the rules we have studied, derive the corresponding relational schema from
the E-R diagram. Show your answer in the form of a schema diagram, in the style of figure 2.8
of text book (Schema diagram of the university database). (That is, for each relation, draw a
rectangle that lists the relation’s attributes; underline the primary key(s); draw arrows to
indicate foreign key constraints.)
E-R Diagram for Problems 2 and 4: