Guide IV
Part 4 Methodology
Chapters 16 – 19 Methodology Conceptual, Logical, and Physical Database Design
Case Study 1 – Adult Education Department
An Adult Education Department runs various courses during the daytime and evenings, and at
Monday evenings or Wednesday evenings, and runs over 25 weeks from October to March. On
16.1 Given the above information:
State any justifications or assumptions you make.
For the diagram, four entities can be determined: Tutor, Course, Student, and Offering. Course
to Offering and Tutor to Offering are both 1:*, but Student to Offering is *:*.
The tables should be derived relatively easily from the model, the most tricky one being
Guide IV
Student (matricNo, studentFName, studentLName, street, city, postcode,
telNo)
Offering (courseNo, tutorNo, startDate, startTime, endDate, endTime,
(d) Show that your data model supports the following transactions:
(i) Add a new course to the database, prior to it being offered on any particular day or
Bearing in mind the above transactions, explain how the physical database design might
The transactions can be shown in various ways. For example, a check could be made first to
Guide IV
Case Study 2 – BusyBee Cleaning Company
The BusyBee Cleaning Company specializes in providing cleaning services for both domestic and
commercial clients. Each type of client has a set of requirements. For example, The Cardboard
Box Company requires cleaning services from Monday to Friday 7am until 9am and 5pm until
7pm each day, but P. Nuttall only requires cleaning services on a Wednesday from 10am until
1pm.
Whenever a new client is taken on, a BusyBee administrator assesses how many cleaning staff are
16.2 (a) Develop an Entity Relationship model from the above information.
State any justifications or assumptions you make.
Guide IV
In deriving the tables, the primary keys should be chosen judiciously, without separate attributes
being devised for all of them. So, for example, Client, Cleaner, Admin, Staff, and Equipment
Cleaner (staffNo, fName, lName, address, salary, taxCode, homeTelNo, supervisorStaffNo)
Requirement (reqtNo, startDate, startTime, duration, comments)
Supervises
Guide IV
Foreign Key eqptNo references Equipment(eqptNo)
(c) Demonstrate that your model supports the following transactions and explain how they
might influence physical database design:
Guide IV
Case Study 3 – Reliable Rentals
The requirements collection and analysis phase of the database design process has provided
the following data requirements for a company called Reliable Rentals, which rents out
vehicles (cars and vans). The Company has various outlets (garage/offices) throughout
Clients may hire vehicles for various periods of time (minimum 1 day to maximum 1 year).
Each individual hire agreement between a client and the Company is uniquely identified using
address, and phone number, date the client started the hire period, date the client wishes to
The Company has two types of clients: personal and business. The data stored on personal
clients includes the client number, name (first and last name), home address, phone number,
16.3 (a) Create a conceptual schema for Reliable Rentals using the concepts of the Enhanced
Entity Relationship (EER) model. To simplify the diagram, only show entities,
relationships and the primary key attributes. Specify the cardinality ratio and participation
constraint of each relationship type. State any assumptions you make when creating the
EER model (if necessary).
(b) Map your high-level data model to a set of relational tables that represent the
entity and relationship types. Identify primary, alternate, and foreign keys.
Outlet (outletNo, address, telNo, faxNo, mgrStaffNo)
Guide IV
Vehicle (regNo, model, make, engineSize, capacity currentMileage, motDate, dRate,
outletNo)
16.4 Map the high-level data model shown below to a set of relational tables. Identify
primary, alternate, and foreign keys.
1..1
1..1
0..1
WorksOn
0..*
Controls
1..1
Employee (SSN, fName, MI, lName, address, birthDate, sex, salary, number)
Employee
WorksFor
1..* 1..1 Department
Supervises
0..*
Guide IV
16.5 Map the high-level data model shown below to a set of relational tables. Identify
primary, alternate, and foreign keys.
Company(name, phone)
Guide IV
Case Study 4 – Perfect Pets
A practice called Perfect Pets provides private health care for domestic pets throughout America. This
service is provided through various clinics located in the main cities of America. The Director of
Data Requirements
Veterinary Clinics
Perfect Pets has many veterinary clinics located in the main cities of America. The details of each
Staff
The details stored on each member of staff include the staff number, name (first and last), address
Pet Owners
When a pet owner first contacts a clinic of Perfect Pets the details of the pet owner are recorded, which
Pets
The details of the pet requiring treatment are noted, which include a pet number, pet name, type of pet,
Examinations
When a sick pet is brought to a clinic, the vet on duty examines the pet. The details of each
examination are recorded and include an examination number, the date and time of the examination,
Treatments
Perfect Pets provides various treatments for all types of pets. These treatments are provided at a
standard rate across all clinics. The details of each treatment include a treatment number, a full
description of the treatment, and the cost to the pet owner. For example, treatments include:
T123 Penicillin antibiotic course $50.00
T155 Feline hysterectomy $200.00
Guide IV
Pet Treatments
Based on the results of the examination of a sick pet, the vet may propose one or more types of
Pens
the clinic. Each clinic has 20 30 animal
pens, each capable of holding between one and four pets. Each pen has a unique pen number, capacity,
Invoices
The pet owner is responsible for the cost of the treatment given to a pet. The owner is invoiced for the
treatment arising from each examination, and the details recorded on the invoice include the invoice
Surgical, Non-surgical, and Pharmaceutical Supplies
Each clinic maintains a stock of surgical supplies (for example, syringes, sterile dressings, bandages)
Each clinic also maintains a stock of pharmaceutical supplies (for example, antibiotics, pain killers). The
Guide IV
Appointments
If the pet requires to be seen by the vet at a later date, the owner and pet are given an appointment. The
Transaction Requirements
Listed below are the transactions that should be supported by the Perfect Pets database application.
1. The database should be capable of supporting the following maintenance transactions:
a) Create and maintain records recording the details of Perfect Pets clinics and the members
of staff at each clinic.
b) Create and maintain records recording the details of pet owners.
2. The database should be capable of supporting the following example query transactions:
a)
each clinic, ordered by clinic number.
b) Present a report listing the names and owner numbers of pet owners with the details of
Guide IV
16.6 (a) Create a conceptual schema for Perfect Pets using the concepts of the Enhanced Entity
Relationship (EER) model. To simplify the diagram, only show entities, relationships and the
primary key attributes. Specify the cardinality ratio and participation constraint of each
relationship type. State any assumptions you make when creating the EER model (if
necessary).
(b) Validate the conceptual data model.
(c) Map your high-level data model to a set of relational tables that represent the entity
and relationship types. Identify primary, alternate, and foreign keys.
Clinic (clinicNo, street, city, state, zipCode, telNo,
faxNo, mgrStaffNo)
Primary Key clinicNo
Foreign Key mgrStaffNo references Staff(staffNo)
Staff (staffNo, sFName, sLName, sStreet, sCity,
sState, sZipCode, sTelNo, DOB, sex, SSN,
position, salary, clinicNo)
PetOwner (ownerNo, oFName, oLName, oStreet,
oCity, oState, oZipCode, oTelNo, clinicNo)
Foreign Key
references
Pet (petNo, petName, petType, petDescription
, pDOB,
dateRegistered, petStatus, ownerNo, clinicNo)
Primary Key penNo
Foreign Key clinicNo references Clinic(clinicNo)
Primary Key penNo, petNo, dateIn
Alternate Key penNo, petNo, dateOut
Foreign Key penNo references Pen(penNo)
Foreign Key
petNo
references
Pet(petNo)
PetTreatment (examNo, treatNo, startDate, endDate,
Item (itemNo, itemName, itemDescription, itemCost)
Guide IV
Pharmacy (drugNo, drugName, drugDescription,
dosage, methodAdmin, drugCost)
ItemClinicStock (itemNo, clinicNo, inStock,
reorderLevel, reorderQty)
Foreign Key clinicNo references Clinic(clinicNo)
Foreign Key examNo references Examination(examNo)
(d) Produce a physical database design for a relational DBMS you have access to.
Implement this physical database design.
Interactions between tables and query transactions (with suggested frequencies).
Table Transaction Access Frequency (per day)
Appointment 2(l) join: petNo
search condition: aDate
250
Invoice 2(e), 2(f)
2(n)
join: PetOwner on ownerNo
search condition: datePaid IS NULL
join: PetOwner on ownerNo
search condition: invoiceDate
10
1 per month
Based on the guidelines provided for Oracle in Chapter 16 there may be performance benefits
in adding the indexes shown in the following table.
Additional Oracle indexes for the Perfect Pets database.
Table Index
Pet clinicNo
ownerNo
Part V
Stock
PART IV
Case Study 5 – StayHome Video Rentals
This case study describes a company called StayHome, which rents out videos to its members. The first
branch of StayHome was established in 1982 in Seattle but the company has now grown and has many
provides to its members and the wide and varied stock of videos available for rent.
Below is a description of two views of the company: a Branch view and a Business View.
Branch View of StayHome
Data Requirements
The data held on a branch of StayHome is the branch address made up of street, city, state, and zip
code, and the telephone numbers (maximum of 3 lines). Each branch is given a branch number, which
is unique throughout the company.
Each branch of StayHome has staff, which includes a Manager, one or more Supervisors, and a number
Before renting a video from the company, a customer must first register as a member of a local branch
Once registered, a member is free to rent videos, up to a maximum of 10 at any one time. The data held
PART IV
Transaction Requirements
Data Entry
(a) Enter the details of a new branch.
(b) Enter the details of a new member of staff at a branch (such as an employee Tom Daniels at
branch B001).
(c) Enter the details for a newly released video (such as details of a video called Independence
Day).
(d) Enter the details of copies of a new video at a given branch (such as three copies of
Independence Day at branch B001).
(e) Enter the details of a new member registering at a given branch (such as a member Bob
Adams registering at branch B002).
(f) Enter the details of a rental agreement for a member renting a video (such as member Don
Nelson renting Tomorrow Never Dies on 4- Feb-2000).
Data Update / Deletion
Data Queries
The database should be capable of supporting the following sample queries:
(a) List the details of branches in a given city.
ordered by title.
(f) List the title, category, and availability of all videos for a given director at a specified branch,
PART IV
The Business View of StayHome
sections:
Data Requirements
The details held on a branch of StayHome are the branch address and the telephone number. Each
branch is given a branch number, which is unique throughout the company.
Each branch of StayHome has staff, which includes a Manager. The details held on a member of staff
are his or her name, position, and salary. Each member of staff is given a staff number, which is unique
throughout the company.
Each branch of StayHome is allocated a stock of videos. The details held on a video are the catalog
number, video number, title, category, daily rental rate, and purchase price. The catalog number
Transaction Requirements
Data Entry
(a) Enter the details for a newly released video (such as details of a video called Independence
Day).
Data Update / Deletion
PART IV
Data Queries
(a) List the name, position, and salary of staff at all branches, ordered by branch number.
(b) List the name and telephone number of the Manager at a given branch.
(h) List the number of videos in each category at a given branch, ordered by category.
Initial database size
(a) There are approximately 20000 video titles and 400000 videos for rent distributed over 100
Database rate of growth
(a) Approximately 100 new video titles and 20 copies of each video are added to the database