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

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.

Quizplus · Accepted Answer

The Answer of 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.