CSE3330 Homework #3
Due on Wednesday November 10, before midnight
Worth 4% of the final grade


Directions for this homework: In your Entity-Relationship diagrams you need to identify:

Do not draw the participation or the structural constraints. Avoid the following common mistakes: to use two different entities for the same object; to represent a complex entity as a (multivalued) attribute of another entity (when the former entity is related to other entities); to have N:M identifying relationships for weak entities.

Problems:

  1. (35 points) Suppose we informally define the data in the database of a department store as follows. Each employee is represented. The data about an employee are her employee number, name, address, and the department she works for. Each department is represented. The data about a department are its name, employees, manager, and items sold. Each item sold is represented. The data about an item are its name, manufacturer, price, model number (assigned by the manufacturer), and an internal item number (assigned by the store). Each manufacturer is represented. The data about a manufacturer are its name, address, items supplied to the store, and their prices. Draw an E-R diagram representing the above scenario.

  2. (35 points) Construct an E-R diagram for a bookstore database, which maintains information about books, professional journals, their authors, and publishers. Books and journals are collectively referred to as publications even though publications will not appear in the E-R diagram. All publications have a unique ISBN, a title, and a year of publication. No publication is published by more than one publisher. Books have one or more authors, whereas journals have a volume and a number. Journals contain articles, each of which is authored by one or more authors. Articles have a title and a subject category and no article can appear in more than one journal. Journal article authors are distinct from book authors. A book author may be employed by only one publisher. All authors have a unique SSN, a name, and a date of birth, whereas a publisher has a unique name, and an address.

  3. (30 points) Construct an E-R diagram for a hospital to hold current information on its operations. An initial analysis of these requirements produced the following facts. Each of the clinics in the hospital is identified by a unique name. A director is responsible for each clinic. The director is also a doctor. There are a large number of laboratories at the hospital; some labs are managed by one clinic, whereas others are managed jointly by two or more clinics. Doctors are assigned to one clinic; each clinic has many doctors. Each patient is assigned to one primary doctor. A doctor takes care of many patients. Each patient has a number of tests done at a number of labs; each lab can carry out a number of tests.

How to Submit the Homework

You need to submit your homework solution online using the following web form. Acceptable document formats are: Microsoft word/powerpoint document, PDF, and postscript. You may submit it as many times as you like, but only the most recently submitted file will be retained and evaluated.

Submit Homework #3:

Last modified: 11/03/10 by Leonidas Fegaras