Case Study 1
This is the first of three case studies that are used to guide the reader through the six steps outlined in Six-Step Relational Database Design™. The most important outputs of the six step database design process are outlined here, but the details of each of the steps and intermediary outputs are described in the book.
| Quick links for Case Study 1 |
|---|
| Case Study 1 List of entities and their corresponding attributes Entity-Relationship diagrams Relational-Model diagram SQL commands |
Scenario for Case Study 1
A corporate office intends to implement a digital front desk solution to assist receptionists in managing key administrative functions for senior management staff (e.g. executives, department heads). The application for the new system will streamline the management of appointments, meeting rooms, visitors, and administrative tasks.
The application must be capable of:
- Tracking all appointments scheduled for senior management staff.
- Managing meeting rooms assigned to these appointments and preventing scheduling conflicts.
- Recording details of visitors attending appointments, including check-in/check-out times (visitor log).
- Managing administrative tasks associated with senior staff and linking them to relevant appointments and visitors, where applicable.
Additionally, the system must be password-protected and support role-based access control, ensuring that users (e.g. receptionists, assistants, senior staff) can only access features appropriate to their roles. User login activity must also be recorded.
To promote transparency and accountability, the application should include basic auditing features, such as tracking who created or modified records and when. Extensive logging of database changes is not required.
Your job is to design a database that will serve as the back end of this application.
Case Study 2
This is the second of three case studies that are used to guide the reader through the six steps outlined in the Six-Step Relational Database Design™ book. The most important outputs of the six step database design process are outlined here, but the details of each of the steps and intermediary outputs are described in the book.
| Quick links for Case Study 2 |
|---|
| Case Study 2 List of entities and their corresponding attributes Entity-Relationship diagrams Relational-Model diagram SQL commands |
Scenario for Case Study 2
A mid-sized restaurant plans to implement a digital system to streamline and manage its daily operations. The application will support key functions such as customer reservations, table management, menu offerings, order processing, and billing.
The application must be capable of:
- Managing customer reservations, including the customer, reservation times, and table assignments.
- Tracking table availability and status, indicating whether tables are free, reserved, or currently in use.
- Maintaining a digital menu, listing all available items along with their descriptions, prices, and availability status.
- Recording and tracking customer orders, including quantities, menu items selected, and special instructions (e.g. allergies, dietary restrictions such as strict vegetarian).
- Generating itemized bills, with support for splitting by order, and including applicable service charges, taxes, and the total amount due.
Additionally, the system must be password-protected and support role-based access control, ensuring that users (e.g. host/hostess, customers, waiters, kitchen staff) can only access features appropriate to their roles. User login activity must also be recorded.
To promote transparency and accountability, the application should include basic auditing features, such as tracking who created or modified records and when. Extensive logging of database changes is not required.
Your job is to design a database that will serve as the back end of this application.
Case Study 3
This is the third of three case studies that are used to guide the reader through the six steps outlined in the Six-Step Relational Database Design™ book. The most important outputs of the six step database design process are outlined here, but the details of each of the steps and intermediary outputs are described in the book.
| Quick links for Case Study 3 |
|---|
| Case Study 3 List of entities and their corresponding attributes Entity-Relationship diagrams Relational-Model diagram SQL commands |
Scenario for Case Study 3
A startup company plans to develop a digital streaming platform to deliver on-demand video content, including movies, TV shows, and documentaries. The platform must support multiple user accounts, each with one or more associated profiles that provide a personalized, subscription-based experience. The system must be capable of managing users, subscriptions, content, viewing activity, ratings, and billing to ensure a seamless and engaging user experience.
The application must be capable of:
- Managing user accounts, including login credentials and account status.
- Managing user profiles, including preferences and viewing history.
- Maintaining user subscriptions, including subscription plans, start and end dates, renewal status, and billing records (invoices and payments).
- Managing a digital content catalog, including metadata, availability, and categorization (e.g. movies, documentaries and series with seasons and episodes).
- Recording and tracking user viewing activity through their various profiles, including content viewed, time of viewing, and completion status.
- Recording and tracking user-generated ratings (e.g. star ratings or like/dislike) to support content evaluation and personalized recommendations.
Additionally, the system must be password-protected and support role-based access control, ensuring that users (e.g. viewers, content managers, administrators) can only access features appropriate to their roles. User login activity must also be recorded.
To promote transparency and accountability, the application should include basic auditing features, such as tracking who created or modified records and when. Extensive logging of database changes is not required.
Your job is to design a database that will serve as the back end of this application.
Follow