For this coursework, you are required to submit a written report on creation of a relational 
database solution for the problem scenario provided. 
This report should be of 1500 words. You are required to use at least 10 high quality 
reference materials in this report. All references should be in APA 7th style. This coursework 
consists of three parts: database designing, query writing, and critical analysis of various 
data storage solutions available. Your Submission should include: 
 Title Page 
Include project title, your name, student ID, module name, instructor name, and date. 
 Table of Contents. 
 Introduction 
Briefly describe the Cozy Book Café scenario, the purpose of the database, and what 
your report will cover. 
 ERD (Entity Relationship Diagram) 
Visual diagram showing all entities, attributes, and relationships. Include a short 
explanation of your design. 
  
SQL Table Creation Scripts 
Show your CREATE TABLE statements with appropriate data types, keys, and 
constraints. 
 Sample Data (INSERT Statements) 
Include Screenshots of the sample INSERT queries to populate your tables with realistic 
data. 
 SQL Queries and Explanations 
List the image all the required queries along with the output.  
 Critical Reflection 
Discuss how your database design meets the needs of the café and analyze how 
technologies like NoSQL, cloud databases, etc., could enhance operations. Use 
academic references here. 
  
Conclusion 
 References  
List at least 10 high-quality academic references (15 for good marks). Use proper APA 
formatting. 
 Appendix (Optional) 
Add extra screenshots, SQL outputs, ERD diagram. 
Problem Scenario: 
Cozy Book Café is a place where customers can read books and enjoy coffee. Customers can 
either become members or pay per visit. The café offers a collection of books in various genres 
and allows reservations for reading sessions. Additionally, it hosts events like book readings 
and poetry nights, which require separate bookings. 
The café has a small team of staff who manage book maintenance, customer service, and 
event coordination. Customers can reserve seats in advance for events or walk in if space is 
available. Orders for coffee and snacks are placed at the counter, and payments are made at 
the end of the visit. 
To efficiently manage operations, the café requires a database system to handle: 
 Customer information 
 Book inventory 
 Event management 
 Reservations 
 Orders and payments 
1. Customers Table 
Column Name Type Description Constraints 
customer_id INT Unique customer ID PRIMARY KEY, AUTO_INCREMENT 
full_name VARCHAR(100) Customer's full name NOT NULL 
email VARCHAR(100) Email address UNIQUE, NOT NULL 
phone VARCHAR(15) Contact number UNIQUE, NOT NULL 
membership ENUM Membership status (Regular, 
Member) 
DEFAULT 'Regular' 
 
2. Books Table 
Column Name Type Description Constraints 
book_id INT Unique book ID PRIMARY KEY, AUTO_INCREMENT 
title VARCHAR(200) Book title NOT NULL 
author VARCHAR(100) Author name NOT NULL 
genre VARCHAR(50) Book genre NOT NULL 
available_copies INT Number of copies available DEFAULT 1 
 
3. Events Table 
Column Name Type Description Constraints 
event_id INT Unique event ID PRIMARY KEY, AUTO_INCREMENT 
event_name VARCHAR(150) Name of event NOT NULL 
event_date DATE Scheduled date NOT NULL 
start_time TIME Start time NOT NULL 
entry_fee DECIMAL(8,2) Fee for the event NOT NULL 
 
4. Reservations Table 
Column Name Type Description Constraints 
reservation_id INT Unique reservation ID PRIMARY KEY, AUTO_INCREMENT 
customer_id INT Customer making the reservation FOREIGN KEY (Customers) 
event_id INT Event being booked FOREIGN KEY (Events) (Nullable) 
reservation_date DATE Date of booking NOT NULL 
status ENUM (Pending, Confirmed, Cancelled) DEFAULT 'Pending' 
 
5. Orders Table 
Column Name Type Description Constraints 
order_id INT Unique order ID PRIMARY KEY, AUTO_INCREMENT 
customer_id INT Customer placing the order FOREIGN KEY (Customers) 
total_price DECIMAL(8,2) Total order cost NOT NULL 
order_time TIMESTAMP Order timestamp DEFAULT CURRENT_TIMESTAMP 
 
6. Payments Table 
Column Name Type Description Constraints 
payment_id INT Unique payment ID PRIMARY KEY, AUTO_INCREMENT 
reservation_id INT Linked reservation FOREIGN KEY (Reservations) (Nullable) 
order_id INT Linked order FOREIGN KEY (Orders) (Nullable) 
payment_method ENUM (Cash, Card, Online) NOT NULL 
amount DECIMAL(8,2) Amount paid NOT NULL 
payment_date TIMESTAMP Payment date DEFAULT CURRENT_TIMESTAMP 
 
Task 1: Database Design (20 Marks) 
1. Design an Entity Relationship Diagram (ERD) for the proposed database system for Cozy 
Book Cafes’, showing all relevant entities, attributes, and relationships. The ERD should 
reflect the system’s core functionality, such as managing users, books, orders, and 
inventory. (10 Marks) 
2. Discuss how the normalization process improved the database structure based on the 
given data. (10 Marks) 
Task 2: Database Queries (20 Marks) 
1. Create all normalized tables in SQL. Show your SQL scripts and the final tables. (2 Marks) 
2.  Enter data for all tables based on the given problem statement. (4 Marks) 
3. Write a query to retrieve all customers who haven’t paid their order yet. (2 Marks) 
4.  Write a query to select all books available in the "Fiction" genre in descending order 
along with their number of copies. (2 Marks) 
5.  Write a query that counts the number of books available in the "Mystery" genre. (2 
Marks) 
6. Write a query to retrieve all customers who have made reservations for events named as 
'Book Reading'. (2 Marks) 
7. Update the book genre of any uploaded book from "Classic Literature" to "Modern 
Classics". (2 Mark) 
8. Write a query to find the top 3 customers who have spent the highest total amount on 
orders. (2 Marks) 
9. Write a query to retrieve all orders placed in the last 7 days, including the customer’s full 
name and order total. (2 Marks) 
Task 3: Critical Reflection (10 Marks) 
You are required to reflect on how the work you have done meets the requirements of Cozy 
Book Cafe. Additionally, critically analyze how recent advancements in database technologies 
(such as cloud databases, NoSQL, etc.) could enhance the cafe’s business operations. Your 
discussion should be supported by at least 8 high-quality academic references.