CSCI-GA.2433-011-Homework 1 Solved

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.  

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.