COMP

453
Homework #1: ER Diagrams
(Some of the problems will be done together in class. The rest will be assigned as homework.)
1. Below is a list associated with the Furniture City ERD included with this assignment. Each point
represents a business rule of the Furniture City Company. For each of the 10 points on the list,
identify the subset of the Furniture City ERD that is described by that point. You need only list any
entities and relationships described by that point.
a. The company sells a number of different furniture products. These products are grouped into
several product lines. The identifier for a product is Product ID, whereas the identifier for a
product line is Product Line ID. We identify the following additional attributes for product:
Product Description, Product Finish and Product Standard Price. Another attribute for product
line is Product Line Name. A product line may group any number of products but must group
at least one product. Each product line may group any number of products but must group at
least one product. Each product must belong to exactly one product line.
b. Customers submit orders for products. The identifier for an order is Order ID, and another
attribute is Order Date. A customer may submit any number of orders, but need not submit
any orders. Each order is submitted by exactly one customer. The identifier for a customer is
Customer ID. Other attributes include Customer Name, Customer Address, and Customer
Postal Code.
c. A given customer order must request at least one product and only one product per order line
item. Any product sold by Furniture City may not appear on any order line item or may appear
on one or more order line items. An attribute associated with each order line item is Ordered
Quantity.
d. Furniture City has established sales territories for its customers. Each customer may do
business in any number of these sales territories or may not do business in any territory. A
sales territory has one to many customers. The identifier for a sales territory is Territory ID and
an attribute is Territory Name.
e. Furniture City Company has several salespersons. The identifier for a salesperson is
Salesperson ID. Other attributes include Salesperson Name, Salesperson Telephone, and
Salesperson Fax. A salesperson serves exactly one sales territory. Each sales territory is served
by one or more salespersons.
f. Each product is assembled from a specified quantity of one or more raw materials. The
identifier for the raw material entity is Material ID. Other attributes include Unit of measure,
Material Name, and Material standard Cost. Each raw material is assembled into one or more
products, using a specified quantity of the raw material for each product.
g. Raw materials are supplied by vendors. The identifier for a vendor is Vendor ID. Other
attributes include Vendor Name and Vendor Address. Each raw material can be supplied by
one or more vendors. A vendor may supply any number of raw materials or may not supply
any raw materials to Furniture City. Supply Unit Price is the unit price a particular vendor
supplier a particular raw material.
h. Furniture City has established a number of work centers. The identifier for a work center is
Work Center ID. Another attribute is Work Center Location. Each product is produced in one
or more work centers. A work center may be used to produce any number of prducts or may
not be used to produce any products.
i. The company has more than 100 employees. The identifier for employee is Employee ID.
Other attributes include Employee Name, Employee Address, and Skill. An employee may have
more than one skills. Each employee may work in one or more work centers. A work center

j.

must have at least one employee working in that center, but may have any number of
employees. A skill may be possessed by more than one employee or possibly no employees.
Each employee has exactly one supervisor; however, a manager has no supervisor. An
employee who is a supervisor may supervise any number of employees, but not all employees
are supervisors.

2. The Student-Club-School figure included with this homework assignment represents a situation of
students who attend and work in schools and who also belong to certain clubs that are located in
different schools. Study this diagram carefully to try to discern what business rules are represented.

a. You will notice that cardinalities are not included on the Works For relationship. State a
business rule for this relationship and then represent this rule with the cardinalities that match
your rule. State the cardinality similarly to: For every one student, there can be many courses;
for every one course, there can be many students. Therefore, it is a m:n relationship. That is
just an example.
b. State a business rule that would make the Located In relationship redundant (i.e., where the
school in which a club is located can be surmised or derived in some way from other
relationships).
c. Suppose a student could work for only a school that student attends but might not work.
Would the Works For relationship still be necessary, or could you represent whether a student
works for the school she attends in some other way (if so, how)?


Draw ER Diagrams for each of the following situations. If you believe that you need to make additional
assumptions, clearly state them for each situation.

3. 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 DI (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. If you have any associative entities on your ERD, be sure to explicitly show the
primary key for ? If so, what are the identifiers for those associative entities? Does your ERD allow a
project to be created before it has any employees assigned to it? Explain. How would you change your
ERD if the Billing Rate could change in the middle of a project?
4. 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. Provide good definitions for all of the relationships in this situation.
5. 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.

6. 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. Did you draw more than one relationship between
physician and patient? Why or why not? Did you include hospital as an entity type? Why or why not?
Does your ERD allow for the same patient to be admitted by different physicians over time? How
would you include on the ERD the need to represent the date on which a patient is admitted for each
time they are admitted?
7. 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.
8. Companies, identified by Company ID and described by Company Name and Industry Type, hire
consultants, identified by Consultant ID and described by Consultant Name, Consultant Specialty,
which is multivalued. Each consultant charges an Hourly Rate for his or her services, regardless of the
company for which he or she consults. Now, consider that each time a consultant works for a
company, a contract is written describing the terms for this consulting engagement. Over time, a con-
sultant can work for many companies, and the consulting firm would like to keep a complete history
of all consulting engagements for each consultant and company.
9. 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.
10. 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.
11. Prepare an ER Diagram for a real estate firm that lists property for sale. Also prepare a definition for
each entity type, attribute, and relationship on your diagram. In addition, draw a data model for this
situation using the tool you have been told to use in your course. The following describes this
organization:

The firm has a number of sales offices in several states. Attributes of sales office include Office
Number (identifier) and Location.
Each sales office is assigned one or more employees. Attributes of employee include Employee
ID (identifier) and Employee Name. An employee must be assigned to only one sales office.
For each sales office, there is always one employee assigned to manage that office. An
employee may manage only the sales office to which he or she is assigned.
The firm lists property for sale. Attributes of property include Property ID (identifier) and
Location. Components of Location include Address, City, State, and Zip Code.
Each unit of property must be listed with one (and only one) of the sales offices. A sales office
may have any number of properties listed or may have no properties listed.
Each unit of property has one or more owners. Attributes of owners are Owner ID (identifier)
and Owner Name. An owner may own one or more units of property. An attribute of the
relationship between property and owner is Percent Owned.
12. Projects, Inc., is an engineering firm with approximately 500 employees. A database is required to
keep track of all employees, their skills, projects assigned, and departments worked in. Every
employee has a unique number assigned by the firm and is required to store his or her name and date
of birth. If an employee is currently married to another employee of Projects, Inc., the date of
marriage and who is married to whom must be stored; however, no record of marriage is required if
an employee's spouse is not also an employee. Each employee is given a job title (e.g., engineer,
secretary, and so on). An employee does only one type of job at any given time, and we only need to
retain information for an employee's current job.

There are 11 different departments, each with a unique name. An employee can report to only 1
department Each department has a phone number. To procure various kinds of equipment, each
department deals with many vendors. A vendor typically supplies equipment to many dpeartments.
We are required to store the name and address of each vendor and the date of the last meeting
between a department and a vendor.

Many employees can work on a project. An employee can work on many projects (e.g., Southwest
Refinery, California Petrochemical, and so on) but can only be assigned to at most one project in a
given city. For each city, we are interested in its state and population. An employee can have many
skills (preparing material requisitions, checking drawings, and so on), but she or he may use only a
given set of skills on a particular project. (For example, an employee MURPHY may prepare requisitions
for the Southwest Refinery project and prepare requisitions as well as check drawings for California
Petorchemicals.) Employees use each skill that they possess in at least one project. Each skill is
assigned a number, and we must store a short description of each skill. Projects are distinguished by
project numbers, and we must store the estimated cost of each project.

Homework #1, Problem #1: Furniture City

Problem #2: Student School Club