Description
1. Problem 1
For this problem, you will design a small database for “used textbooks”, as it might be
used by an on-line trading site like Amazon. The database will store textbooks for sale.
Each textbook has an ISBN, a category (e.g., Math) it belongs to, and a title. In addition,
multiple descriptions about each book may be maintained, but each book is required to
have at least one such a description. Each textbook is published by exactly one company
in the publishing year. Each publisher has to have published at least one textbook. Each
textbook has zero or more sellers, which may be either companies (corporation sellers)
or individuals (individual sellers). Each seller may have a different number of copies on
sale. For each company, the database maintains a name of the company, its address, its
phone numbers (could be more than one phone number, each with a number and a
description), and its contact person (who is an individual with all the related information
an individual has, see next sentence). For each individual, the database keeps a name and
an email address. You may assume the email address uniquely identifies an individual. A
contact person for a company can also “moonlight” as an individual seller, but the books
sold must be different from the ones sold by the company he/she serves as the contact
person.
CSCI-GA.2433-011
2. Problem 2
Consider the following version of a hospital, which focuses on a few aspects of a
hospital, rather than on looking a real-world example.
(a) There is an entity set, Person. It has attributes ID-number which identifies a
person entity, Name and Email. The value of ID-number and Name are always
known.
(b) There is an entity set, Doctor, which is a set of some of the entities in Person. It
has an attribute Specialty and a composite attribute Shift, which consists of
attributes DayOfWeek and HourOfDay.
(c) There is an entity set, Patient, which is a set of some of the entities in Person.
(d) An entity in Person is in at least one of the entity sets Doctor, Nurse and Patient.
(e) There is a binary relationship Admits between Doctor and Patient. Every Patient
is admitted by at most one Doctor. The relationship Admits has an attribute Time
which records the admission time.
(f) There is an entity set Surgery. It has attributes Time, Location, and Specialty. An
entity in Surgery is identified by specifying Time and Location, which are always
known.
(g) There is a binary relationship Operates between Doctor and Surgery. Every
Surgery must have at least one Doctor to operate. The Specialty of Doctor must
match the Specialty of Surgery.
(h) There is a binary relationship Evaluates between Operates and Doctor. Each
operation (element of Operates) is evaluated by at most one doctor.
(i) There is an entity set Prescription. It has an attribute PrescrID, which is always
known and which identifies a prescription entity.
(j) There is a ternary relationship Uses among Patient, Prescription, and Surgery.
The relationship has an attribute Amount. Any entity in Prescription is used by
exactly one Patient.
For problems 1&2 above:
(1) Design an ER-diagram for each problem above. You must identify the following: (i) all
the entity sets; (ii) all the relationship sets with their Functionality; (iii) the primary
key for each entity set (and weak entity set, if any). Draw the ER diagram with
software of your choice, and submit the PDF output. You are to only use the
conventions used in the class slides for the ER diagram, and you are not allowed to
invent your own conventions or use those appear in the literature that’re not in
the class notes.
(2) For each problem, write a separate description file, indicating which properties in
the problem description (if any) are NOT reflected by your design. For each of these
properties you give (again, if any), use as an example situation that is allowed by
your ERD but this situation contradicts the property in the description. Typing the
solution is recommended and submit the PDF output. If hand-writing (and scanned
into PDF), your writing must be clear/clean.
(3) Name your files carefully: your ER-diagram PDF should be names as 1ER.pdf and
2ER.pdf, and the two descript files as 1DES.pdf and 2DES.pdf.
3. Problem 3
For the “application” in problem 2 above, produce a relational implementation of your
ER design.
For problem 3 above:
(1) You should give your solution in the form of Slide 59 (of the class notes file
03_From_ER_Diagrams_To_Relational_Databases.pptx). You may either use the
crow’s feet notation, or write specific cardinality constraints in the form of Slide 55
(in the same class notes file). Draw your implementation with software of your
choice, and output a PDF file, named 3REL.pdf.
(2) Produce a one-page text file listing the set of the required and only the required
annotations to list all you know about the application and that are not implemented
by your relational design. Produce a file named 3DES.pdf.
(3) Note that if something can be easily implemented in the relational design 1 above,
yet you put it in the list that your design doesn’t implement, some points may be
deducted from your solution.
CSCI-GA.2433-011
