Databases Question

College of Computing and Informatics

Project
Deadline: Wednesday 04/12/2024@ 23:59
[Total Mark for this Project is 14]

Student Details:
Name:
Name:
Name:

ID:
ID:
ID:

Instructions:

• You must submit two separate copies (one Word file and one PDF file) using the Assignment Template on
Blackboard via the allocated folder. These files must not be in compressed format.

• It is your responsibility to check and make sure that you have uploaded both the correct files.
• Zero mark will be given if you try to bypass the SafeAssign (e.g., misspell words, remove spaces between
words, hide characters, use different character sets, convert text into image or languages other than English
or any kind of manipulation).

• Email submission will not be accepted.
• You are advised to make your work clear and well-presented. This includes filling your information on the cover
page.

• You must use this template, failing which will result in zero mark.
• You MUST show all your work, and text must not be converted into an image, unless specified otherwise by
the question.

• Late submission will result in ZERO mark.
• The work should be your own, copying from students or other resources will result in ZERO mark.
Restricted •
– ‫مقيد‬Use Times New Roman font for all your answers.

Project Instructions
• You can work on this project as a group (minimum 3 and maximum 4 students).
Each group member must submit the project individually with all group member
names mentioned in the cover page.

• This project worth 14 marks and will be distributed as in the following:
a) Identify the entity types, attributes, keys.
b) Identify the relationship and cardinalities.
c) Draw the ERD.
d) Schemas before Normalization.
e) Schemas after Normalization.
f) Create the tables.
g) Populate your tables with at least 5 rows.
h) Execute the requested sample queries.

(2 marks)
(2 marks)
(2 marks)
(1.5 marks)
(1.5 marks)
(1.5 marks)
(1.5 marks)
(2 marks)

• Each student must submit one report about his/her chosen Project via the Blackboard
(Email submission will not be accepted which will be awarded ZERO marks)

• Screenshots for answering SQL questions (f, g, and h).
• You are advised to make your work clear and well presented; marks may be reduced
for poor presentation. This includes filling your information on the cover page.

• You MUST show all your work, and text must not be converted into an image, unless
specified otherwise by the question.
A) Late submission will result in ZERO marks being awarded.
B) The work should be your own, copying from students or other resources will
result in ZERO marks.

Restricted – ‫مقيد‬

Learning
Outcome(s):

Online Bookstore Management System
Consider an online bookstore that sells a variety of books to customers. The bookstore
requires a database system to manage its inventory, customer orders, shipping
information, and book details. The data requirements for the system are outlined

LO 4

below:

Design a
database starting
from the
conceptual
design to the
implementation
of database
schemas.

1. Book Inventory Management:
•

publication date, price, and quantity available.
•

The system tracks the stock levels of each book and alerts when the stock is
low.

•

It maintains information on book categories and subcategories for easy
classification.

LO 3

Create EntityRelationship
model, Relational
model, and write
SQL queries.

Each book in the inventory is identified by a unique ISBN, title, author, genre,

2. Customer Order Processing:
•

Customers can place orders for books by providing their details such as name,
shipping address, contact number, and email.

•

The system records each customer’s order history, including the books
purchased, order date, and total amount.

•

It generates invoices for each order and tracks the payment status.

3. Shipping and Delivery:
•

The system manages shipping information, including the shipping address,
delivery status, and tracking number.

•

It calculates shipping costs based on the customer’s location and the weight of
the books in the order.

•

Restricted – ‫مقيد‬

It provides estimated delivery dates and updates customers on the order status.

4. Book Details and Reviews:
•

The system stores detailed information about each book, including a brief
description, cover image, ratings, and reviews.

•

Customers can rate and review books they have purchased, which helps in
recommending books to other customers.

•

It tracks popular books, best sellers, and new arrivals for marketing and
promotion purposes.

5. Customer Accounts and Preferences:
•

Customers can create accounts on the platform to manage their orders, wish
lists, and preferences.

•

The system stores customer preferences such as favorite genres, authors, and
recommendations based on past purchases.

•

It allows customers to update their account information, password, and
communication preferences.

By implementing a robust database system for the online bookstore, the management
can efficiently handle book inventory, customer orders, and enhance the overall
shopping experience for book enthusiasts.

Restricted – ‫مقيد‬

Online Bookstore Management System
a) Fill the table below with all the Entities based on the given requirements:

Entity Type

Restricted – ‫مقيد‬

Attributes of the Entity

Key

b) Fill the table below with all the relationship type based on the given requirements.

Relationship
Type

Restricted – ‫مقيد‬

Entities
connected by the
relationship type

Cardinality

Relationship attribute

c) Draw the ER Diagram
d) Tables before the normalization
e) Tables after the normalization
f) Create the normalized tables and populate them with at least 10 rows.
g) Write the necessary SQL statements to create the tables.
h) Insert at least five rows into each table.
i) Write SQL queries to find the following:
1. List the first and last names of customers who have purchased more than
three books.
2. List all books’ ids and titles published since 1/1/2024.
3. List all customers who have rated books (First and last name, customer id).
4. Find all customers who have purchased books in the “Fiction” genre.
5. List all customers who have books in their wish list.
6. Find all customers who have purchased books and reviewed them.

Restricted – ‫مقيد‬

Purchase answer to see full
attachment