Department of Computer
C
Sciience
CSC-211: Database Ma
anagement Sy
ystem
Bahrria University
y, Karachi Ca
ampus
Semester 04
0 (Summer 2017)
ASSIG
GNMENT
T 01
M
Marks: 10
ENTITY RELATION
R
NSHIP DIA
AGRAM (ER
RD)
Read
R
Carefully:
• This assignment has 10 question
ns that you have
h
to answ
wer and subm
mit in HAND
DWRITTEN
N
form.
•
•
The deaddline for this assignment is before orr on Thursda
ay July 24, 2017 (11:000 a.m).
WARNIINGS:
• This is an individdual assignm
ment; you muust solve it byy yourself. A
Any form off plagiarism will
w
resultt in receivingg zero in thee assignmentt.
• Late submission will
w not be accepted.
a
Anny assignmennt submittedd after the cuutoff time wiill
receivve zero.
• Do no
ot alter the sequence
s
of questions
q
annd illegitimatte handwritiing is not accceptable.
156 Part 2 Design Concepts
Problems
1. Use the following business rules to create a Crow’s Foot ERD. Write all appropriate
connectivities and cardinalities in the ERD.
• A department employs many employees, but each employee is employed by only
one department.
• Some employees, known as “rovers,” are not assigned to any department.
• A division operates many departments, but each department is operated by only
one division.
• An employee may be assigned many projects, and a project may have many
employees assigned to it.
• A project must have at least one employee assigned to it.
• One of the employees manages each department, and each department is managed by only one employee.
• One of the employees runs each division, and each division is run by only one
employee.
2. Create a complete ERD in Crow’s Foot notation that can be implemented in the
relational model using the following description of operations. Hot Water (HW) is a
small start-up company that sells spas. HW does not carry any stock. A few spas are
set up in a simple warehouse so customers can see some of the models available, but
any products sold must be ordered at the time of the sale.
• HW can get spas from several diferent manufacturers.
• Each manufacturer produces one or more diferent brands of spas.
• Each and every brand is produced by only one manufacturer.
• Every brand has one or more models.
• Every model is produced as part of a brand. For example, Iguana Bay Spas is a
manufacturer that produces Big Blue Iguana spas, a premium-level brand, and
Lazy Lizard spas, an entry-level brand. he Big Blue Iguana brand ofers several
models, including the BBI-6, an 81-jet spa with two 6-hp motors, and the BBI-10,
a 102-jet spa with three 6-hp motors.
• Every manufacturer is identiied by a manufacturer code. he company name,
address, area code, phone number, and account number are kept in the system
for every manufacturer.
• For each brand, the brand name and brand level (premium, mid-level, or entrylevel) are kept in the system.
• For each model, the model number, number of jets, number of motors, number of
horsepower per motor, suggested retail price, HW retail price, dry weight, water
capacity, and seating capacity must be kept in the system.
3. he Jonesburgh County Basketball Conference (JCBC) is an amateur basketball
association. Each city in the county has one team as its representative. Each team
has a maximum of 12 players and a minimum of 9 players. Each team also has up to
3 coaches (ofensive, defensive, and physical training coaches). During the season,
Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
Chapter 4 Entity Relationship (ER) Modeling 157
each team plays 2 games (home and visitor) against each of the other teams. Given
those conditions, do the following:
• Identify the connectivity of each relationship.
• Identify the type of dependency that exists between CITY and TEAM.
• Identify the cardinality between teams and players and between teams and city.
• Identify the dependency between COACH and TEAM and between TEAM and
PLAYER.
• Draw the Chen and Crow’s Foot ERDs to represent the JCBC database.
• Draw the UML class diagram to depict the JCBC database.
4. Create an ERD based on the Crow’s Foot notation using the following requirements:
• An INVOICE is written by a SALESREP. Each sales representative can write many
invoices, but each invoice is written by a single sales representative.
• he INVOICE is written for a single CUSTOMER. However, each customer can
have many invoices.
• An INVOICE can include many detail lines (LINE), each of which describes one
product bought by the customer.
• he product information is stored in a PRODUCT entity.
• he product’s vendor information is found in a VENDOR entity.
5. he Hudson Engineering Group (HEG) has contacted you to create a conceptual
model whose application will meet the expected database requirements for the company’s training program. he HEG administrator gives you the following description
of the training group’s operating environment. (Hint: Some of the following sentences
identify the volume of data rather than cardinalities. Can you tell which ones?)
he HEG has 12 instructors and can handle up to 30 trainees per class. HEG ofers 5
Advanced Technology courses, each of which may generate several classes. If a class
has fewer than 10 trainees, it will be canceled. herefore, it is possible for a course
not to generate any classes. Each class is taught by one instructor. Each instructor
may teach up to 2 classes or may be assigned to do research only. Each trainee may
take up to 2 classes per year.
Given that information, do the following:
a. Deine all of the entities and relationships. (Use Table 4.4 as your guide.)
b. Describe the relationship between instructor and class in terms of connectivity,
cardinality, and existence dependence.
6. Automata, Inc., produces specialty vehicles by contract. he company operates several departments, each of which builds a particular vehicle, such as a limousine,
truck, van, or RV.
• Before a new vehicle is built, the department places an order with the purchasing
department to request speciic components. Automata’s purchasing department
is interested in creating a database to keep track of orders and to accelerate the
process of delivering materials.
• he order received by the purchasing department may contain several diferent
items. An inventory is maintained so the most frequently requested items are
delivered almost immediately. When an order comes in, it is checked to determine whether the requested item is in inventory. If an item is not in inventory, it
must be ordered from a supplier. Each item may have several suppliers.
Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
158 Part 2 Design Concepts
Given that functional description of the processes at Automata’s purchasing department, do the following:
a. Identify all of the main entities.
b. Identify all of the relations and connectivities among entities.
c. Identify the type of existence dependence in all the relationships.
d. Give at least two examples of the types of reports that can be obtained from the
database.
7. United Helpers is a nonproit organization that provides aid to people ater natural
disasters. Based on the following brief description of operations, create the appropriate fully labeled Crow’s Foot ERD.
• Volunteers carry out the tasks of the organization. he name, address, and telephone number are tracked for each volunteer. Each volunteer may be assigned
to several tasks, and some tasks require many volunteers. A volunteer might be
in the system without having been assigned a task yet. It is possible to have tasks
that no one has been assigned. When a volunteer is assigned to a task, the system
should track the start time and end time of that assignment.
• Each task has a task code, task description, task type, and task status. For example,
there may be a task with task code “101,” a description of “answer the telephone,”
a type of “recurring,” and a status of “ongoing.” Another task might have a code of
“102,” a description of “prepare 5,000 packages of basic medical supplies,” a type
of “packing,” and a status of “open.”
• For all tasks of type “packing,” there is a packing list that speciies the contents of the
packages. here are many packing lists to produce diferent packages, such as basic
medical packages, child-care packages, and food packages. Each packing list has an ID
number, a packing list name, and a packing list description, which describes the items
that should make up the package. Every packing task is associated with only one packing list. A packing list may not be associated with any tasks, or it may be associated with
many tasks. Tasks that are not packing tasks are not associated with any packing list.
• Packing tasks result in the creation of packages. Each individual package of supplies produced by the organization is tracked, and each package is assigned an
ID number. he date the package was created and its total weight are recorded. A
given package is associated with only one task. Some tasks (such as “answer the
phones”) will not produce any packages, while other tasks (such as “prepare 5,000
packages of basic medical supplies”) will be associated with many packages.
• he packing list describes the ideal contents of each package, but it is not always
possible to include the ideal number of each item. herefore, the actual items
included in each package should be tracked. A package can contain many diferent items, and a given item can be used in many diferent packages.
• Each item that the organization provides has an item ID number, item description, item value, and item quantity on hand stored in the system. Along with
tracking the actual items that are placed in each package, the quantity of each
item placed in the package must be tracked as well. For example, a packing list
may state that basic medical packages should include 100 bandages, 4 bottles of
iodine, and 4 bottles of hydrogen peroxide. However, because of the limited supply of items, a given package may include only 10 bandages, 1 bottle of iodine, and
no hydrogen peroxide. he fact that the package includes bandages and iodine
needs to be recorded along with the quantity of each item included. It is possible
Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
Chapter 4 Entity Relationship (ER) Modeling 159
for the organization to have items that have not been included in any package yet,
but every package will contain at least one item.
8. Using the Crow’s Foot notation, create an ERD that can be implemented for a medical clinic using the following business rules:
• A patient can make many appointments with one or more doctors in the clinic,
and a doctor can accept appointments with many patients. However, each
appointment is made with only one doctor and one patient.
• Emergency cases do not require an appointment. However, for appointment
management purposes, an emergency is entered in the appointment book as
“unscheduled.”
• If kept, an appointment yields a visit with the doctor speciied in the appointment. he visit yields a diagnosis and, when appropriate, treatment.
• With each visit, the patient’s records are updated to provide a medical history.
• Each patient visit creates a bill. Each patient visit is billed by one doctor, and each
doctor can bill many patients.
• Each bill must be paid. However, a bill may be paid in many installments, and a
payment may cover more than one bill.
• A patient may pay the bill directly, or the bill may be the basis for a claim submitted to an insurance company.
• If the bill is paid by an insurance company, the deductible is submitted to the
patient for payment.
9. Create a Crow’s Foot notation ERD to support the following business operations:
• A friend of yours has opened Professional Electronics and Repairs (PEAR) to
repair smartphones, laptops, tablets, and MP3 players. She wants you to create a
database to help her run her business.
• When a customer brings a device to PEAR for repair, data must be recorded about
the customer, the device, and the repair. he customer’s name, address, and a contact phone number must be recorded (if the customer has used the shop before,
the information already in the system for the customer is veriied as being current). For the device to be repaired, the type of device, model, and serial number
are recorded (or veriied if the device is already in the system). Only customers
who have brought devices into PEAR for repair will be included in this system.
• Since a customer might sell an older device to someone else who then brings the
device to PEAR for repair, it is possible for a device to be brought in for repair by
more than one customer. However, each repair is associated with only one customer. When a customer brings in a device to be ixed, it is referred to as a repair
request, or just “repair,” for short. Each repair request is given a reference number,
which is recorded in the system along with the date of the request, and a description of the problem(s) that the customer wants ixed. It is possible for a device to be
brought to the shop for repair many diferent times, and only devices that are
brought in for repair are recorded in the system. Each repair request is for the
repair of one and only one device. If a customer needs multiple devices ixed, then
each device will require its own repair request.
• here are a limited number of repair services that PEAR can perform. For each
repair service, there is a service ID number, description, and charge. “Charge” is
how much the customer is charged for the shop to perform the service, including
Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
160 Part 2 Design Concepts
any parts used. he actual repair of a device is the performance of the services
necessary to address the problems described by the customer. Completing a
repair request may require the performance of many services. Each service can be
performed many diferent times during the repair of diferent devices, but each
service will be performed only once during a given repair request.
• All repairs eventually require the performance of at least one service, but which
services will be required may not be known at the time the repair request is
made. It is possible for services to be available at PEAR but that have never been
required in performing any repair.
• Some services involve only labor activities and no parts are required, but most
services require the replacement of one or more parts. he quantity of each part
required in the performance of each service should also be recorded. For each
part, the part number, part description, quantity in stock, and cost is recorded in
the system. he cost indicated is the amount that PEAR pays for the part. Some
parts may be used in more than one service, but each part is required for at least
one service.
10. Luxury-Oriented Scenic Tours (LOST) provides guided tours to groups of visitors
to the Washington D.C. area. In recent years, LOST has grown quickly and is having
diiculty keeping up with all of the various information needs of the company. he
company’s operations are as follows:
• LOST ofers many diferent tours. For each tour, the tour name, approximate length (in hours), and fee charged is needed. Guides are identiied by an
employee ID, but the system should also record a guide’s name, home address,
and date of hire. Guides take a test to be qualiied to lead speciic tours. It is
important to know which guides are qualiied to lead which tours and the date
that they completed the qualiication test for each tour. A guide may be qualiied
to lead many diferent tours. A tour can have many diferent qualiied guides.
New guides may or may not be qualiied to lead any tours, just as a new tour may
or may not have any qualiied guides.
• Every tour must be designed to visit at least three locations. For each location, a
name, type, and oicial description are kept. Some locations (such as the White
House) are visited by more than one tour, while others (such as Arlington Cemetery) are visited by a single tour. All locations are visited by at least one tour. he
order in which the tour visits each location should be tracked as well.
• When a tour is actually given, that is referred to as an “outing.” LOST schedules outings well in advance so they can be advertised and so employees can
understand their upcoming work schedules. A tour can have many scheduled
outings, although newly designed tours may not have any outings scheduled.
Each outing is for a single tour and is scheduled for a particular date and time.
All outings must be associated with a tour. All tours at LOST are guided tours,
so a guide must be assigned to each outing. Each outing has one and only one
guide. Guides are occasionally asked to lead an outing of a tour even if they are
not oicially qualiied to lead that tour. Newly hired guides may not have ever
been scheduled to lead any outings. Tourists, called “clients” by LOST, pay to
join a scheduled outing. For each client, the name and telephone number are
recorded. Clients may sign up to join many diferent outings, and each outing
can have many clients. Information is kept only on clients who have signed up
for at least one outing, although newly scheduled outings may not have any
clients signed up yet.
Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
Chapter 4 Entity Relationship (ER) Modeling 161
a. Create a Crow’s Foot notation ERD to support LOST operations.
b. he operations provided state that it is possible for a guide to lead an outing
of a tour even if the guide is not oicially qualiied to lead outings of that tour.
Imagine that the business rules instead speciied that a guide is never, under
any circumstance, allowed to lead an outing unless he or she is qualiied to
lead outings of that tour. How could the data model in Part a. be modiied to
enforce this new constraint?
Note