Draw an ERD for each of the following situations. (If you believe that you need to make additional
assumptions, clearly state them for each situation.) Draw the same situation using the tool you have
been told to use in the course.
1. A company has a number of employees. The attributes of EMPLOYEE include
Employee ID (identifier), Name, Address, and Birthdate. The company also has several
projects. Attributes of PROJECT include Project ID (identifier), Project Name, and Start
Date. Each employee may be assigned to one or more projects, or may not be assigned
to a project. A project must have at least one employee assigned and may have any
number of employees assigned. An employee’s billing rate may vary by project, and the
company wishes to record the applicable billing rate (Billing Rate) for each employee
when assigned to a particular project.
2. A laboratory has several chemists who work on one or more projects. Chemists also
may use certain kinds of equipment on each project. Attributes of CHEMIST include
Employee ID (identifier), Name, and Phone No. Attributes of PROJECT include Project
ID (identifier) and Start Date. Attributes of EQUIPMENT include Serial No and Cost. The
organization wishes to record Assign Date—that is, the date when a given equipment
item was assigned to a particular chemist working on a specified project. A chemist must
be assigned to at least one project and one equipment item. A given equipment item
need not be assigned, and a given project need not be assigned either a chemist or an
equipment item.
3. A college course may have one or more scheduled sections, or may not have a
scheduled section. Attributes of COURSE include Course ID, Course Name, and Units.
Attributes of SECTION include Section Number and Semester ID. Semester ID is
composed of two parts: Semester and Year. Section Number is an integer (such as 1 or
2) that distinguishes one section from another for the same course but does not uniquely
identify a section.
4. A hospital has a large number of registered physicians. Attributes of PHYSICIAN include
Physician ID (the identifier) and Specialty. Patients are admitted to the hospital by
physicians. Attributes of PATIENT include Patient ID (the identifier) and Patient Name.
Any patient who is admitted must have exactly one admitting physician. A physician may
optionally admit any number of patients. Once admitted, a given patient must be treated
by at least one physician. A particular physician may treat any number of patients, or
may not treat any patients. Whenever a patient is treated by a physician, the hospital
wishes to record the details of the treatment (Treatment Detail). Components of
Treatment Detail include Date, Time, and Results.
5. The loan office in a bank receives from various parties requests to investigate the credit
status of a customer. Each credit request is identified by a Request ID and is described
by a Request Date and Requesting Party Name. The loan office also received results of
credit checks. A credit check is identified by a Credit Check ID and is described by the
Credit Check Date and the Credit Rating. The loan office matches credit requests with
credit check results. A credit request may be recorded before its result arrives; a
particular credit result may be used in support of several credit requests.
6. An art museum owns a large volume of works of art. Each work of art is described by an
item code (identifier), title, type, and size; size is further composed of height, width, and
weight. A work of art is developed by an artist, but the artist for some works is unknown.
An artist is described by an artist ID (identifier), name, date of birth, and date of death
(which is null for still living artists). Only data about artists for works currently owned by
the museum are kept in the database. At any point in time, a work of art is either on
display at the museum, held in storage, away from the museum as part of a traveling
show, or on loan to another gallery. If on display at the museum, a work of art is also
described by its location within the museum. A traveling show is described by a show ID
(identifier), the city in which the show is currently appearing, and the start and end dates
of the show. Many of the museum works may be part of a given show, and only active
shows with at least one museum work of art need be represented in the database.
Finally, another gallery is described by a gallery ID (identifier), name, and city. The
museum wants to retain a complete history of loaning a work of art to other galleries,
and each time a work is loaned, the museum wants to know the date the work was
loaned and the date it was returned.
7. Each case handled by the law firm of Dewey, Cheetim, and Howe has a unique case
number; a date opened, date closed, and judgment description are also kept on each
case. A case is brought by one or more plaintiffs, and the same plaintiff may be involved
in many cases. A plaintiff has a requested judgment characteristic. A case is against one
or more defendants, and the same defendant may be involved in many cases. A plaintiff
or defendant may be a person or an organization. Over time, the same person or
organization may be a defendant or a plaintiff in cases. In either situation, such legal
entities are identified by an entity number, and other attributes are name and net worth.
8. Each publisher has a unique name; a mailing address and telephone number are also
kept on each publisher. A publisher publishes one or more books; a book is published by
exactly one publisher. A book is identified by its ISBN, and other attributes are title, price,
and number of pages. Each book is written by one or more authors; an author writes one
or more books, potentially for different publishers. Each author is uniquely described by
an author ID, and we know each author’s name and address. Each author is paid a
certain royalty rate on each book he or she authors, which potentially varies for each
book and for each author. An author receives a separate royalty check for each book he
or she writes. Each check is identified by its check number, and we also keep track of
the date and amount of each check.

DETAILED ASSIGNMENT

20200930220420hw2_4620