DBS211 - Project 02

DATA MODELING AND DATABASE DESIGN

LEARNING OUTCOMES
Upon successful completion of this assignment, you will have demonstrated the abilities to:

1. Analyze business rules of a problem domain to identify entities and their relationships.
2. Create a conceptual data model in light of the business rules.
3. Map the conceptual data model to a relational model.
4. Normalization (1NF, 2NF, and 3NF).
5. Implement the relational database model.
6. Insert data into the database.
7. Query the database to retrieve information.

Group Work
Please work in groups to complete this project. This project is worth 10% of the total course grade and
will be evaluated through your written submission.

SUBMISSION
Please submit the following files through Blackboard. Only one person must submit for the team.

Project SQL file: Project02_Group#.SQL

Project document: Project02_Group#.docx

Project Overview
Online bookstore management scenario

"Readers' Haven" is an online bookstore that offers a wide selection of books for readers to explore and
purchase. The store provides a user-friendly platform for customers to browse, order, and review books
from various genres. The focus of "Readers' Haven" is to create a welcoming and engaging online
environment for book lovers to discover their next literary adventure. The system is designed to handle
book catalog management, order processing, inventory tracking, customer management, and review
management.

Throughout the online bookstore management process, data is stored and organized in the various
tables, ensuring efficient management of books, customers, orders, inventory, and reviews. This online
bookstore allows customers to browse and purchase books from a wide selection. The store also
manages customer orders, inventory, and book reviews.

See some of the online bookstore specification, business rules, and policies:

• Customers can create customer accounts on the online store by providing their name and email
address. Each customer is assigned a unique customer ID.
• The system maintains a catalog of books, including their titles, authors, genres, publishers, and
prices. Each book is identified by a unique book ID.
 • Multiple stores offer books, each identified by a unique Store ID.
• The system keeps track of the quantity of each book available in different store locations using the
store inventory table. Multiple different books can be available for sale in different stores.
• Customers can purchase books. Book are avaiable to be purchased by customers. When a
customer places an order, a new order is created with a unique order ID and an order date. Each
order is linked to a single customer.
• An order can include one or more books, each identified by a unique Book ID, along with the
corresponding quantity for each.
• Customers can provide book reviews by assigning a rating (on a scale of 1 to 5) and adding a
review text for any number of books. Each review is associated with the book ID being reviewed,
the customer ID who provided the review, the rating, and the review text. Each review is
associated with one customer. Each customer can write one review for a single book but may have
multiple reviews for different books. Each book can receive multiple reviews from various
customers.
• A book can be written by multiple authors. An author may write multiple books.

First find all entities from the above business rules. Then for each entity, list their attributes. Determine a
primary key for each entity. From the ER diagram, then find the relationships between entities and
determine FKs in a parent/child relationship.

The entities extracted from this scenario are connected through relationships such as "Customer orders
Books," "Book is written by Author," "Book belongs to Genre," "Book is published by Publisher," and
"Book has Reviews."
Using the given scenario, draw an ERD that visually represents the relationships between these entities,
helping you understand the structure and connections within the online bookstore system.

Part I. Conceptual Database Model (20%)

Draw the ERD using Lucidchart. Note that your design must not have any many-to-many relationships or
multi-valued attributes. PK and FK must be clearly stated. Strong and weak relationships must be labelled
correctly. Export the ERD to .jpg and insert in your project document file.
Draw the ERD using Lucidchart (Crow’s Foot Diagram).

Include the following in your project document:

• The ERD
• Define all PKs and FKs
• Fix any many-to-many (M: N) relationships using a bridge entity

Write all corresponding relations for each entity in the entity relationship diagram using Database Design
Language (DBDL).

Database Design Language (DBDL)

• Table name (relation name) followed by columns (attributes) in
parentheses.
o Primary key column(s) underlined.
• FK identifies foreign keys.
 See the following example of DBDL:
Director (director_id, name, dob, country)
Movie (movie_id, title, year, director_id)
FK: director_id refers to director (director_id)

Part II. Normalization (1NF, 2NF, and 3NF) (30%)

Convert the following user views to 3NF, showing all steps (from 1NF to 3NF).
Determine all PKs and FKs in the derived relations.

Orders Table:
Order Customer
ID ID Customer Name Customer Address Book ID Book Title Qnty Order Date
1 1001 John Doe 123 Main Street 1 The Great Gatsby 2 2022-05-10
1 1001 John Doe 123 Main Street 3 To Kill a Mockingbird 1 2022-05-10

2 1002 Jane Smith 456 Elm Avenue 2 Pride and Prejudice 1 2022-06-15
2 1002 Jane Smith 456 Elm Avenue 4 1984 3 2022-06-15
3 1003 Bob Johnson 789 Oak Street 1 The Great Gatsby 2 2022-07-20
3 1003 Bob Johnson 789 Oak Street 2 Pride and Prejudice 2 2022-07-20
3 1003 Bob Johnson 789 Oak Street 3 To Kill a Mockingbird 1 2022-07-20

Books/Authors Table:
Book Author Publisher
ID Title ID Author Name ID Publisher Name Price
1 The Great Gatsby 1 F. Scott Fitzgerald 1 Scribner 10.00
2 Pride and Prejudice 2 Jane Austen 2 Penguin Classics 8.00
3 To Kill a Mockingbird 3 Harper Lee 3 HarperCollins 12.00

4 1984 4 George Orwell 4 Signet Classic 9.00

5 The Catcher in the Rye 5 J.D. Salinger 5 Little, Brown 11.00
2 Jane Austen

Books/Genres
Book Genre
ID Title ID Genre
1 The Great Gatsby 1 Fiction
2 Pride and Prejudice 2 Romance
3 To Kill a Mockingbird 2 Romance
3 Mystery

4 1984 1 Fiction
5 The Catcher in the Rye 1 Fiction
Store Book Inventory Table:
Store
ID Store Name Location Book ID Book Title Quantity
1 Book Haven 209 Yonge Street 1 The Great Gatsby 10
1 Book Haven 209 Yonge Street 2 Pride and Prejudice 5
1 Book Haven 209 Yonge Street 3 To Kill a Mockingbird 3
2 Bookworm Emporium 101 Holmes 1 The Great Gatsby 8
Avenue
2 Bookworm Emporium 101 Holmes 3 To Kill a Mockingbird 6
Avenue
3 The Reading Nook 454 Dufferin 2 Pride and Prejudice 4
Street
3 The Reading Nook 454 Dufferin 3 To Kill a Mockingbird 2
Street
3 The Reading Nook 454 Dufferin 4 1984 10
Street
3 The Reading Nook 454 Dufferin 5 The Catcher in the Rye 7
Street

Book Reviews Table:

Book ID Customer ID Rating Review Text

1 1001 4.5 Great book!
2 1002 5 Highly recommended
3 1003 3.5 Average read
1 1002 4 Interesting!
4 1001 3 Good!
5 1001 5 I liked the book.

Part III. Merge Relations (10%)

Combine the tables obtained at the end of the normalization process with created relations in Part I
(ERD). All relations with the same PK must be merged. Do not add duplicate attributes in a relation.
List all the relations in your submission document.

PART IV. Database IMPLEMENTATION (20%)

In your SQL file, write the SQL statements to:
1. Create a database named DBS211_P2_YourGroupNo.
2. Create the tables of your relational database model. Specify the PK, FK, and other necessary
constraints.
3. Show a screenshot of the database relationships of all the tables you have created in the
submission document.
Part V. Enter Data (20%)
Enter the data from the samples shown in the given tables in Part II. Include all INSERT queries in
your SQL file. Also, include screen shot of all tables to show their inserted data in the word document.

Good luck.