Deck 6: Methodology-Conceptual Database Design and Methodology-Physical Database Design for Relational Databases

Question 1

Case Study 1 - Adult Education Department
An Adult Education Department runs various courses during the daytime and evenings, and at different times of the year. For example, 'Spanish level 1' is offered on Monday mornings, Monday evenings or Wednesday evenings, and runs over 25 weeks from October to March. On the other hand, 'Introduction to Digging Up Your Ancestors' only runs for 8 weeks, but is offered on Tuesday or Wednesday evenings from October to December, January to March, and April to June, with an optional field week in August.
There is always a maximum number of places for each course offering, which is dependent on the individual tutor. For example, 'Spanish level 1' on Monday evenings may be limited to 20 places, but on Wednesday evenings the limit may be 25. Each course offering is only taken by one tutor, however, a tutor may take different courses, for example, 'French level 1' and 'Spanish level 2'. To guarantee enrolment, prospective students must pay the fee before the start of the first class. There is a special reduction for those unemployed. All applicants are kept on a register for subsequent mailshots.

-Given the above information:
(a) Develop an Entity-Relationship model to illustrate the logical database design.Produce a set of tables from your Entity-Relationship model, clearly identifying the primary keys. State any justifications or assumptions you make.
(b) 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 from any particular date.
(ii) Enrol a new student on the 'German level 2' course that runs on Monday evenings commencing October 10 1994.
Bearing in mind the above transactions, explain how the physical database design might be influenced describing what changes you might make etc, and how the application (and transactions) would be affected. Your comments can apply to both computerized procedures and manual procedures.

Answer 1

(a) 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 Offering. There also needs to be a table to represent the relationship between Student and Offering, which will also contain the courseFee attribute.
Student (matricNo, studentFName, studentLName, street, city, postcode, telNo)
Primary Key matricNo
Course (courseNo, courseName, courseDescription)
Primary Key courseNo
Offering (courseNo, tutorNo, startDate, startTime, endDate, endTime, maxStudents)
Primary Key courseNo, matricNo
Foreign Key courseNo references Course(courseNo)
Foreign Key tutorNo references Tutor (tutorNo)
Registration (courseNo, tutorNo, matricNo, registrationDate, courseFee)
Primary Key courseNo, tutorNo, matricNo
Foreign Key (courseNo, tutorNo) references Offering(courseNo, tutorNo)
Foreign Key matricNo references Student(matricNo)
(b) The transactions can be shown in various ways. For example, a check could be made first to ensure no details have been entered. If separate tables exist for both Course and Offering, simply insert the new details into Course (identifier, name, and level). If the tables are combined, this table is simply checked for the existence of any records, and then the new details are inserted, leaving the rest of the attributes null.
For the second transaction, check whether Student details exist, and insert if necessary. Find the identifier for the Course, then repeatedly check Offering to find the correct Offering identifier for the day/time. Repeat read the Registration records, and if space, insert a new Registration record.
Alterations may be made to the physical design, such as adding a derived field to Offering indicating how many students are enrolled - alters procedures. To aid searching, set up secondary indexes on required fields.

Question 2

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 required for the premises prior to assigning any staff to the job. Note that this is the ideal number, it may differ in practice. In addition, the administrator also assesses whether any specialist equipment is required and when. For example, three industrial floor cleaners may be needed on two out of five occasions for one commercial client.
The cleaning staff work in groups of six, with a supervisor to oversee the work done. The other staff are administrative staff who manage the day-to-day office work including visiting new clients and ensuring the specialist equipment is properly maintained.

-(a) Develop an Entity-Relationship model from the above information.Produce a set of tables from your Entity-Relationship model clearly identifying each primary key.
State any justifications or assumptions you make.
(b) Demonstrate that your model supports the following transactions and explain how they might influence physical database design:
(i) For a specific client, produce a schedule of the cleaning times together with the number of staff assigned, and details of any specialist equipment required.
(ii) For a specific supervisor, produce a list of staff on their team together with their assignment details.

Answer 2

(a) Four main entities can be identified: Client, Requirement, Equipment, and Staff. Staff can form a superclass with Cleaner and Admin forming subclasses. There is a recursive relationship (1:*) on Cleaner. Cleaner to Requirement and Equipment to Requirement are both *:* relationships (Assigned and Booked), whereas Client to Requirement is 1:*. This represents the core of the problem.

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 will have a reference number each. Requirement will have the Client reference number with day and time from. Assigned will have Client reference number with day, time from and Cleaner reference number. Booked will have Client reference number with day, time from and equipment reference number.
Cleaner (staffNo, fName, lName, address, salary, taxCode, homeTelNo, supervisorStaffNo)
Primary Key staffNo
Foreign Key supervisorStaffNo references Cleaner(staffNo)
Admin (staffNo, fName, lName, address, salary, taxCode, homeTelNo)
Primary Key staffNo
Client (clientNo, name, address, telNo, faxNo)
Primary Key clientNo
Equipment (eqptNo, description, usage, cost)
Primary Key eqptNo
Requirement (reqtNo, startDate, startTime, duration, comments)
Primary Key reqtNo
Assigned (staffNo, reqtNo)
Primary Key staffNo, reqtNo
Foreign Key staffNo references Cleaner(staffNo)
Foreign Key reqtNo references Requirement(reqtNo)
Booked (reqtNo, eqptNo)
Primary Key reqtNo, eqptNo
Foreign Key reqtNo references Requirement(reqtNo)
Foreign Key eqptNo references Equipment(eqptNo)
(b) The transactions often demonstrate whether the data model is reasonable or incorrect. For the first transaction you could assume a reference number is used to access the client table, then each requirement can be accessed in turn. For each requirement, the assigned table will be accessed repeatedly, to count the number of staff assigned, and the booked table will be accessed repeatedly and linked to the equipment table using the equipment reference number.
For the second transaction, a similar procedure is carried out. The difference is that there is a recursive relationship involved, but if the table structures are correct, this should not pose a problem. In looking at the transactions refinements may be obvious, such as using derived attributes or posting in some non-key attributes from one table to another or creating secondary indexes.

Question 3

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 Glasgow. Each outlet has a number, address, phone number, fax number, and a manager who supervises the operation of the garage and offices at each site.
Each site is allocated a stock of vehicles for hire, however, individual vehicles may be moved between outlets, as required. Only the current location for each vehicle is stored. The registration number uniquely identifies each vehicle for hire and is used when hiring a vehicle to a client.
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 a hire number. Information stored on the vehicles for hire include: the vehicle registration number, model, make, engine size, capacity, current mileage, date MOT due, daily hire rate, and the current location (outlet) of each vehicle.
The data stored on a hire agreement includes the hire number, the client's number, name, address, and phone number, date the client started the hire period, date the client wishes to terminate the hire period, the vehicle registration number, model and make, the mileage before and after the hire period. After each hire a member of staff checks the vehicle and notes any fault(s). Fault report information on each vehicle is stored, which records the name of the member of staff responsible for the check, date checked, whether fault(s) where found (yes or no), the vehicle registration number, model, make and the current mileage.
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, date of birth, and driving licence number. The data stored on business clients includes the client number, name of business, type of business, address, telephone, and fax numbers. The client number uniquely identifies each client and the information stored relates to all clients who have hired in the past and those currently hiring a vehicle.
Information is stored on the staff based at various outlets including: staff number, name (first and last name), home address, home phone number, date of birth (DOB), sex, National Insurance Number (NIN), date joined the Company, job title, and salary. Each staff member is associated with a single outlet but may be moved to an alternative outlet as required, although only the current location for each member of staff is stored.

-(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.

Answer 3

(a)
(b) Outlet (outletNo, address, telNo, faxNo, mgrStaffNo)
Primary Key outletNo
Foreign Key mgrStaffNo references Staff(staffNo)
Staff (staffNo, fName, lName, address, telNo, DOB, sex, NIN, dateJoined, jobTitle, salary, outletNo)
Primary Key staffNo
Alternate Key NIN
Foreign Key outletNo references Outlet(outletNo)

Vehicle (regNo, model, make, engineSize, capacity currentMileage, motDate, dRate,
outletNo)
Primary Key regNo
Foreign Key outletNo references Outlet(outletNo)
FaultReport (staffNo, regNo, dateChecked, faults)
Primary Key regNo, dateChecked
Foreign Key staffNo references Staff(staffNo)
Foreign Key regNo references Vehicle(regNo)
Note: Assumption is that a vehicle is checked for faults, only once on a given date.
PersonalClient (pclientNo, fName, lName, address, telNo, DOB, licenceNo)
Primary Key pclientNo
Alternate Key licenceNo
BusinessClient (bclientNo, bName, bType, address, telNo, faxNo)
Primary Key bclientNo
HireAgreement (hireNo, clientNo, regNo, dateStart, dateFinish, mileageBefore, mileageAfter)
Primary Key hireNo
Foreign Key clientNo references PersonalClient(pclientNo) and BusinessClient(bclientNo)
Foreign Key regNo references Vehicle(regNo)

Question 4

Map the high-level data model shown below to a set of relational tables. Identify primary, alternate, and foreign keys.

Question 5

Map the high-level data model shown below to a set of relational tables. Identify primary, alternate, and foreign keys.

Question 6

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 Perfect Pets is concerned that there is a lack of communication within the practice and particularly in the sharing of information and resources across the various clinics. To resolve this problem the Director has requested the creation of a centralized database system to assist in the more effective and efficient running of the practice. The Director has provided the following description of the current system.
Data Requirements
Veterinary Clinics
Perfect Pets has many veterinary clinics located in the main cities of America. The details of each clinic include the clinic number, clinic address (consisting of the street, city, state, and zipcode), and the telephone and fax numbers. Each clinic has a Manager and a number of staff (for example, vets, nurses, secretaries, cleaners). The clinic number is unique throughout the practice.
Staff
The details stored on each member of staff include the staff number, name (first and last), address (street, city, state, and zipcode), telephone number, date of birth, sex, social security number (SSN), position, and current annual salary. The staff number is unique throughout the practice.
Pet Owners
When a pet owner first contacts a clinic of Perfect Pets the details of the pet owner are recorded, which include an owner number, owner name (first name and last name), address (street, city, state, and zipcode), and home telephone number. The owner number is unique to a particular clinic.
Pets
The details of the pet requiring treatment are noted, which include a pet number, pet name, type of pet, description, date of birth (if unknown, an approximate date is recorded), date registered at clinic, current status (alive/deceased), and the details of the pet owner. The pet number is unique to a particular clinic.
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, the name of the vet, the pet number, pet name, and type of pet, and a full description of the examination results. The examination number is unique to a particular clinic. As a result of the examination, the vet may propose treatment(s) for the pet.
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 $\quad$ Penicillin antibiotic course $\quad$ $50.00
T155 $\quad$ Feline hysterectomy $\quad$ $200.00
T112 $\quad$ Vaccination course against feline flu $\quad$ $70.00
T56 $\quad$ Small dog - stay in pen per day (includes feeding) $\quad$ $20.00
A standard rate of $20.00 is charged for each examination, which is recorded as a type of treatment. The treatment number uniquely identifies each type of treatment and is used by all Perfect Pets clinics.
Pet Treatments
Based on the results of the examination of a sick pet, the vet may propose one or more types of treatment. For each type of treatment, the information recorded includes the examination number and date, the pet number, name and type, treatment number, description, quantity of each type of treatment, and date the treatment is to begin and end. Any additional comments on the provision of each type of treatment are also recorded.
Pens
In some cases, it's necessary for a sick pet to be admitted to 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, and status (an indication of availability). The sick pet is allocated to a pen and the details of the pet, any treatment(s) required by the pet, and any additional comments about the care of the pet are recorded. The details of the pet's stay in the pen are also noted, which include a pen number, and the date the pet was put into and taken out of the pen. Depending on the pet's illness, there may be more than one pet in a pen at the same time. The pen number is unique to a particular clinic.
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 number, invoice date, owner number, owner name and full address, pet number, pet name, and the details of the treatment given. The invoice provides the cost for each type of treatment and the total cost of all treatments given to the pet. Additional data is also recorded on the payment of the invoice, including the date the invoice was paid and the method of payment (for example, check, cash, visa). The invoice number is unique throughout the practice.
Surgical, Non-surgical, and Pharmaceutical Supplies
Each clinic maintains a stock of surgical supplies (for example, syringes, sterile dressings, bandages) and non-surgical supplies (for example, plastic bags, aprons, litter trays, pet name tags, pet food). The details of surgical and non-surgical supplies include the item number and name, item description, quantity in stock (this is ascertained on the last day of each month), reorder level, reorder quantity, and cost. The item number uniquely identifies each type of surgical or non-surgical supply. The item number is unique for each surgical or non-surgical item and used throughout the practice.
Each clinic also maintains a stock of pharmaceutical supplies (for example, antibiotics, pain killers). The details of pharmaceutical supplies include a drug number and name, description, dosage, method of administration, quantity in stock (this is ascertained on the last day of each month), reorder level, reorder quantity, and cost. The drug number uniquely identifies each type of pharmaceutical supply. The drug number is unique for each pharmaceutical supply and used throughout the practice.
Appointments
If the pet requires to be seen by the vet at a later date, the owner and pet are given an appointment. The details of an appointment are recorded and include an appointment number, owner number, owner name (first name and last name), home telephone number, the pet number, pet name, type of pet, and the appointment date and time. The appointment number is unique to a particular clinic.
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.
c) Create and maintain the details of pets.
d) Create and maintain records recording the details of the types of treatments available for pets.
e) Create and maintain records recording the details of examinations and treatments given to pets.
f) Create and maintain records recording the details of invoices to pet owners for treatment to their pets.
g) Create and maintain records recording the details of surgical, non-surgical, and pharmaceutical supplies at each clinic.
h) Create and maintain records recording the details of pens available at each clinic and the allocation of pets to pens.
i) Create and maintain pet owner/pet appointments at each clinic.
2. The database should be capable of supporting the following example query transactions:
a) Present a report listing the Manager's name, clinic address, and telephone number for each clinic, ordered by clinic number.
b) Present a report listing the names and owner numbers of pet owners with the details of their pets.
c) List the historic details of examinations for a given pet.
d) List the details of the treatments provided to a pet based on the results of a given examination.
e) List the details of an unpaid invoice for a given pet owner.
f) Present a report on invoices that have not been paid by a given date, ordered by invoice number.
g) List the details of pens available on a given date for clinics in the New York area, ordered by clinic number.
h) Present a report that provides the total monthly salary for staff at each clinic, ordered by clinic number.
i) List the maximum, minimum and average cost for treatments.
j) List the total number of pets in each pet type, ordered by pet type.
k) Present a report of the names and staff numbers for all vets and nurses over 50 years old, ordered by staff name.
l) List the appointments for a given date and for a particular clinic.
m) List the total number of pens in each clinic, ordered by clinic number.
n) Present a report of the details of invoices for pet owners between 1997 to 1999, ordered by invoice number.
o) List the pet number, name, and description of pets owned by a particular owner.
p) Present a report listing the pharmaceutical supplies that need to be reordered at each clinic, ordered by clinic number.
q) List the total cost of the non-surgical and surgical supplies currently in stock at each clinic, ordered by clinic number.

-(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.
(d) Produce a physical database design for a relational DBMS you have access to. Implement this physical database design.

Question 7

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 branches throughout the United States. The company's success is due to the first class service it provides to its members and the wide and varied stock of videos available for rent.
As StayHome has grown, so has the difficulties in managing the increasing amount of data used and generated by the company. To ensure the continued success of the company, the Director of StayHome has urgently requested that a database application be built to help solve the increasing problems of data management.
Below is a description of two views of the company: a Branch view and a Business View.
Branch View of StayHome
The users' requirements specification for the Branch view is listed in two sections:
• the 'data requirements' section describes the data used by the Branch view;
• the 'data transactions' section provides examples of how the data is used by the Branch view (that is, the transactions that staff have to perform on the data).
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 of other staff. The Manager is responsible for the day-to-day running of a given branch. Each branch has several Supervisors and each Supervisor is responsible for supervising a group of staff. The data held on a member of staff is 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 data held on a video is the catalog number, video number, title, category, daily rental rate, purchase price, status, and the names of the main actors (and the characters played), and the director. The catalog number uniquely identifies each video. In most cases, there are several copies of each video at a branch, and the individual copies are identified using the video number. A video is given a category such as Action, Adult, Children, Thriller, Horror, or Sci-Fi. The status indicates whether a specific copy of a video is available for rent.
Before renting a video from the company, a customer must first register as a member of a local branch of StayHome. The data held on a member is the first and last name, address, and the date that the member registered at the branch. Each member is given a member number, which is unique across all branches and is used even when a member chooses to register at more than one branch. The name of the member of staff responsible for processing the registration of a member at a branch is also noted.
Once registered, a member is free to rent videos, up to a maximum of 10 at any one time. The data held on each video rented is the rental number, the member's full name and member number, the video number, title, and daily rental cost, and the dates the video is rented out and returned. The rental number is unique throughout the company.
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
(a) Update / delete the details of a branch.
(b) Update / delete the details of a member of staff at a branch.
(c) Update / delete the details of a given video.
(d) Update / delete the details of a copy of a video.
(e) Update / delete the details of a given member.
(f) Update / delete the details of a given rental agreement for a member renting a video.
Data Queries
The database should be capable of supporting the following sample queries:
(a) List the details of branches in a given city.
(b) List the name, position, and salary of staff at a given branch, ordered by staff name.
(c) List the name of each Manager at each branch, ordered by branch number.
(d) List the title, category, and availability of all videos at a specified branch, ordered by category.
(e) List the title, category, and availability of all videos for a given actor at a specified branch, ordered by title.
(f) List the title, category, and availability of all videos for a given director at a specified branch, ordered by title.
(g) List the details of all videos a specified member currently has on rent.
(h) List the details of copies of a given video at a specified branch.
(i) List the titles of all videos in a specified category, ordered by title.
(j) List the total number of videos in each video category at each branch, ordered by branch number.
(k) List the total cost of the videos at all branches.
(l) List the total number of videos featuring each actor, ordered by actor name.
(m) List the total number of members at each branch who joined in 1999, ordered by branch number.
(n) List the total possible daily rental for videos at each branch, ordered by branch number.

The Business View of StayHome
The users' requirements specification for the Business view is listed in two sections:
• the 'data requirements' section describes the data used by the Business view;
• the 'data transactions' section provides examples of how the data is used by the Business view (that is, the transactions that staff have to perform on the data).
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 uniquely identifies each video. However, in most cases there are several copies of each video at a branch, and the individual copies are identified using the video number.
Each branch of StayHome receives videos from video suppliers. The details held on video suppliers are the supplier number, name, address, telephone number, and status. Orders for videos are placed with these suppliers and the details held on a video order are the order number, supplier number, supplier address, video catalog number, video title, video purchase price, quantity, date order placed, date order received, and the address of the branch receiving the order.
A customer of StayHome must first register as a member of a local branch of StayHome. The details held on a member are name, address, and the date that the member registered at a branch. Each member is given a member number, which is unique throughout all branches of the company and is used even when a member chooses to register at more than one branch.
The details held on each video rented are the rental number, full name and member number, the video number, title, and daily rental rate, and the dates the video is rented out and returned. The rental number is unique throughout the company.
Transaction Requirements
Data Entry
(a) Enter the details for a newly released video (such as details of a video called Independence Day).
(b) Enter the details of a video supplier (such as a supplier called WorldView Videos).
(c) Enter the details of a video order (such as ordering 10 copies of Saving Private Ryan for branch B002).
Data Update / Deletion
(a) Update / delete the details of a given video.
(b) Update / delete the details of a given video supplier.
(c) Update / delete the details of a given video order.
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.
(c) List the catalog number and title of all videos at a given branch, ordered by title.
(d) List the number of copies of a given video at a given branch.
(e) List the number of members at each branch, ordered by branch number.
(f) List the number of members who joined this year at each branch, ordered by branch number.
(g) List the number of video rentals at each branch between certain dates, ordered by branch number.
(h) List the number of videos in each category at a given branch, ordered by category.
(i) List the name, address, and telephone number of all video suppliers, ordered by supplier number.
(j) List the name and telephone number of a video supplier.
(k) List the details of all video orders placed with a given supplier, ordered by the date of order.
(l) List the details of all video orders placed on a certain date.
(m) List the total daily rentals for videos at each branch between certain dates, ordered by branch number.
Initial database size
(a) There are approximately 20000 video titles and 400000 videos for rent distributed over 100 branches. There are an average of 4000 and a maximum of 10000 videos for rent at each branch.
(b) There are approximately 2000 staff working across all branches. There are an average of 15 and a maximum of 25 members of staff working at each branch.
(c) There are approximately 100000 members registered across all branches. There are an average of 1000 and a maximum of 1500 members registered at each branch.
(d) There are approximately 400000 video rentals across all branches. There are an average of 4000 and a maximum of 10000 video rentals at each branch.
(e) There are approximately 1000 directors and 30000 main actors in 60000 starring roles.
(f) There are approximately 50 video suppliers and 1000 video orders.
Database rate of growth
(a) Approximately 100 new video titles and 20 copies of each video are added to the database each month.
(b) Once a copy of a video is no longer suitable for renting out, (this includes those of poor visual quality, lost, or stolen), the corresponding record is deleted from the database. Approximately 100 records of videos for rent are deleted each month.
(c) Approximately 20 members of staff join and leave the company each month. The records of staff who have left the company are deleted after one year. Approximately 20 staff records are deleted each month.
(d) Approximately 1000 new members register at branches each month. If a member does not rent out a video at anytime within a period of two years, his or her record is deleted. Approximately 100 member records are deleted each month.
(e) Approximately 5000 new video rentals are recorded across 100 branches each day. The details of video rentals are deleted two years after the creation of the record.
(f) Approximately 50 new video orders are placed each week. The details of video orders are destroyed two years after the creation of the record.
The types and average number of record searches
(a) Searching for the details of a branch - approximately 10 per day.
(b) Searching for the details of a member of staff at a branch - approximately 20 per day.
(c) Searching for the details of a given video - approximately 5000 per day (Sunday to Thursday), approximately 10000 per day (Friday and Saturday). Peak workload 6-9pm daily.
(d) Searching for the details of a copy of a video - approximately 10000 per day (Sunday to Thursday), approximately 20000 per day (Friday and Saturday). Peak workload 6-9pm daily.
(e) Searching for the details of a specified member - approximately 100 per day.
(f) Searching for the details of a rental agreement for a member renting a video - approximately 10000 per day (Sunday to Thursday), approximately 20000 per day (Friday and Saturday). Peak workload 6-9pm daily.

-(a) Create a conceptual schema for each view of StayHome using the concepts of the Enhanced Entity-Relationship (EER) model. To simplify each 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 local conceptual data models to local logical data models. Identify primary, alternate, and foreign keys.

Question 8

Which of the three basic file organizations (heap, ordered, hash) would you choose for a file where the most frequent operations were as follows:
(a) Inserts and scans where the order of records does not matter.
(b) Record searches based on a range of field values.
(c) Record searches based on a particular field value.

Question 9

Discuss the difference between each of the following types of indexes:
(a) Dense versus sparse indexes.
(b) Primary versus secondary indexes.
(c) Clustered versus unclustered indexes.