CIS 365 Business Database Concepts
Assignment 1 - E/R Diagramming
(66 points)
Draw the ERDs for the following business rules using Gliffy. Copy/paste your diagrams to Word and then
convert to .pdf for submission. Professional quality is expected. Identify all entities, attributes, relationships,
primary & foreign keys, cardinalities and constraints where appropriate.
Each complete entity (including all attributes, primary key, and foreign key(s)) receives one point and each
relationship (labeled with cardinalities in both directions) receives one point. Any constraint used correctly,
receives one point. Primary keys will be bold or underlined, foreign keys in italics ONLY (no PK or FK on your
diagrams). Label your relationships if there are more than one between the same entities.
Make sure your diagrams are clear and easy to read - show one diagram per page - do not include the
business rules with your diagrams. Include your name and section on all pages. Show resolved M:N
relationships.
1. (6) A hospital has a large number of registered physicians which includes a physician ID and specialty.
Patients are admitted to the hospital by physicians. The hospital tracks the patient ID, and 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 records the details of the treatment. Other requirements of treatment include
date, time, and results.
2. (10) A real estate firm has the following business rules:
- the firm has a number of sales offices in several states. Attributes of sales office include OfficeNo and
location.
- each sales office is assigned one or more employees. Attributes of employee include EmpId and
EmpName. 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 she is assigned.
- the sales office lists property for sale. Attributes of property include PropertyId and location.
- each unit of property has one or more owners. Attributes of owners are OwnerId and OwnerName. An
owner may own one or more units of property. An attribute of the relationship between property and
owner is PercentOwned.
3. (9) An art museum owns a large volume of works of art. Each work of art is described by an item code,
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 ID, name, date of birth, and date of death
(which is null for living artists) is recorded for each artist. Only data about artists for works currently
owned by the museum are kept in the database. At any point in time, the status of 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, the art's location within the museum is recorded. If the
art work is in storage, storage date and return date from storage are recorded. A traveling show is
described by a show ID, 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, the museum wants to retain a
complete history of loaning a work of art to other galleries; a gallery ID, name, and city are recorded for
other galleries. Each time a work of art is loaned, the museum wants to record the date the work was
loaned and the date it was returned.
4. (11) Peck and Paw, Attorneys at Law require a database system based on the following business rules: An
attorney is retained by one or more clients for each case. Attorney’s ID, name, address, city, state, zip,
specialty (could be more than one), and bar (may be more than one) are recorded. Client information
needed includes client ID, name, address, city, state, zip, phone, and date of birth. A client may have more
than one attorney for each case. A client may also have more than one case. Case ID, case description,
and case type are maintained. An attorney may be working on more than one case. Each case is assigned
to one and only one court where court ID, court name, city, state, and zip are required. Each court has one
or more judges assigned and a judge can be assigned to exactly one court. For each judge, judge ID, name,
and years in practice are stored.
5. (15) Trendy Trade is a rental business that rents musical instruments to bands. For each instrument,
Trendy Trade keeps track of a unique serial number as well as the model and brand, year the instrument
was made, and the age (in years) of the instrument. For each rental, the rental date and return date are
recorded. The customers of Trendy Trade are bands. A unique band identifier and name are stored as
well as the band’s address, contact person’s name, and several phone numbers (band members, contact
person’s phone, etc.). A band does not have to rent any instruments, but may rent up to 30. Each
instrument may be rented by any band. Repair technicians maintain the instruments. An instrument
may not need repair, but if it does, it can be repaired by several technicians; the date of the repair and
the tech’s identifier is recorded as well as the reason for the repair. For each technician, a unique
technician number, name, address, and mobile phone number is kept. Shows that the bands perform. in
are also tracked. Each performance has a date and total tickets sold. For each show, a unique show
identifier is composed of using the show venue name and the stage number (1 if there is only 1 stage).
The show type and show name (a show may not have a name) is also recorded. The bands may perform.
in many shows and each show must have at least one band performing. Trendy Trade keeps track of the
number of shows that each band performs in.
6. (15) Superior Styles is an exclusive fashion designer business. Each designer designs many outfits, but
the outfit has only one designer. For each designer, a unique identifier and name (first and last) are kept
in the database. Outfits are sold to exactly one customer (in advance). A unique outfit identifier,
planned date of completion and unreasonable price is recorded. The customer’s name (first and last)
and phone are stored along with a customer identifier. When the outfit is sold, the date of sale and
actual sale price is recorded. The company does not keep track of customers who have not made any
purchases. A tailoring tech must work on at least one outfit. Info kept for the tailoring techs include an
identifier, name (first and last), and phone. Each outfit has at least one tailoring tech, but can have
many. Superior Styles will keep track of the date when a tailoring tech started working on a particular
outfit, and the completion date – these may be different dates for different techs working on the same
outfit. Designers can participate in a number of fashion shows. Each show can feature one or two
designers from Superior Styles. Shows that do not feature our designers are not tracked. For each show
a unique show identifier as well as the date and location of the show is recorded.
Be sure to use the ‘official class file format’ for naming your file: firstinitialLastname-A1.pdf (points will be
deducted for incorrect filenames (-2)).
(e.g. jsmith-A1.pdf)
Email submissions to the professor will not be accepted
under ANY circumstances.