Database 1

PHYSICAL DESIGN IMPLEMENTATION

By

Course name

Tutor

School

Date
 Database 2

Table of Contents

Introduction ................................................................................................................ 3

Requirements specification ........................................................................................ 3

Data Requirements ................................................................................................. 3

Transaction Requirements ...................................................................................... 5

Conceptual design...................................................................................................... 6

EERD ...................................................................................................................... 6

Constraints .............................................................................................................. 7

Explain entities ........................................................................................................ 8

Logical Design .......................................................................................................... 11

Logical schema ..................................................................................................... 11

Normalization ........................................................................................................ 11

Reflection ................................................................................................................. 12

References ............................................................................................................... 12
 Database 3

PHYSICAL DESIGN IMPLEMENTATION

Introduction

In the HKS Public Ltd context, the process of implementing physical design is

the representations of the organization’s database from conceptual and logical

design into operational relational database. Its result facilitates achievement of the

appropriate and efficient organizational structure of the database to support and

accomplish the Internal Control, MIS and security policy of the business. The

following sections explain how physical design can solve key outcomes such as data

mining queries that might be necessary when running the bookstore. All the data is

stored in a MySQL database with eight tables including Suppliers, user, and books,

Order Details, favourite, review and orders. The choice is whether to store the

database in an on premise server or in a cloud environment like AWS, Azure or

Google Console.

Requirements specification

Data Requirements

 Client/Users

The data that needs to be collected from the clients or users include the client’s

full name, the date when they contacted the firm or organization, the email address

and password that they use on the system, (Quach et al., 2021). New clients have to

enter the system, provide some information and create a password to this service for

the first time. Following this, the details which have been shared are stored in the

database.

 Authors
 Database 4

The table holds information of all the authors of the books available in the

bookstores. The users can apply the number in the column titled ‘Author_Id’ to

search for the desired books in the Books table. There is also a list of all the genres

in which the author has written in that table. The data is collected from books written

by the author of the articles in the study.

 Books:

Such table hold all the records of books that the store has, the description of the

book, the ISBN, the description of the cover page, the title of the book, the author’s

name and the price of the book. Some of this information is obtained from the books

themselves while the store owns the right to determine the price.

 Order_Details:

Serves as the transaction table as well as the orders table. The table documents all

the orders made and the user that has made those orders. This table also stores the

quantity of the books as well as the prices of the books and the date the orders were

placed.

 Reviews:

The table holds all the information of the responses and feedbacks given by the

customers. The data type for the column: Comment is Text to give the client free

reign and not restrict them by the number of characters. The same table also stores

the rating of the books they have purchased or added to their collection.

 Favourite:

 This table contains information abouth books with the highest rating and will

always appear on the website advising other clients/usery that the particular

books are worthy time to read them or they should add those books to the cart.
 Database 5

 Suppliers:

The table holds data about the suppliers. Suppliers provide information on the

contacts including the phone numbers and e-mails address, employer and the ISBN

numbers of the supplied books.

Transaction Requirements

Data Entry:

The first column in the Order_Details table is Order_Details_Id; it is an auto-

incremental column, so for each entry placed, the number automatically increases

and does not have to be entered.

Insert the Book’s ISBN which should correspond with the ISBN in books table

and which makes for the field Book_ISBN to be a foreign key to the book table.

Fill in the Amount of books bought by the client/user.

Input any other mode of payment which is either rtgs, cheque, etc or any other form

of payment.

One has to enter the receipt number which is generated by the system

automatically.

Data Update/Delete

Yes, a transaction could be deleted if it was invalid or if the error occurred and it was

made by mistake. The database admin can also alter an order before payment;

however, upon payment, an order cannot be changed, and hence, a new record

needs to be generated and the right time and date have to be attributed to the

transaction.
 Database 6

Data Queries

1.Specifically, the quantity of books purchased by a particular client

2.The total of the different orders that a user has placed

3.The number of books purchased on a specified date

4.The sales made on fixed dates or days of the week.

5.The time period with little or high rate of orders or patronage

6.The top five books that were most popular and bought by the largest number of

people

7.By employing joins, the age of buyers of particular books or specific genres can be

determined.

8.Ranking of the genre with highest or least sale

 Database 7

Conceptual design

EERD

table columns datatype Relationship

Order_Details Order_Detail_ID Integer, Primary key  Books to Order_Details:

User_ID Integer one to many

Book_ISBN String, Foreign Key

Quantity Integer

Price Integer

Order_Date Date

Receipt_number Biginteger

Mode_of_payment String

Reviews Review_ID Integer Primary Key  User to reviews: one to

Book_ISBN Foreign Key, String many

User_ID Foreign Key, Integer

Integer
 Database 8

Rating Text

Comment

Books ISBN Integer, Primary key  Books to Order_Details:

Title String one to many

Cover_page_descr String

iption Foreign key (Integer)

Author_ID String

Genre Integer

Price

Favourite Favorite_ID Primary key  Users to Favourite: Many

User_ID Integer to many

Book_ISBN String

Author Author_ID primary key  Author to books: many to

Name String many

Bio String

Nationality String

Users User_ID Primary key  User to reviews: one to

firstName String many

LastName String  User to order: one to

Contact Integer many

Dob Date

Email String

Password String

Suppliers Supplier_ID Primary Key  Suppliers to books: One

ComapnyName String to many

 Database 9

Contact_Person String

Email String

Phone Integer

Address String

Book_ISBN String

price Integer

Orders Order_ID INT Primary Key  Users to Order: One to

User_ID INT Integer Many

Order_Date Date
 Database 10

Database Code

#creating the database

create database Online_Bookstore;

use Online_Bookstore;

#hosts the name of the authors and users can use this table to search for a book

from their favourite users

CREATE TABLE Authors (

Author_ID INT PRIMARY KEY,

Name VARCHAR(100),

Bio TEXT,

Nationality VARCHAR(50));

#contains all names of books and their description and the authors

CREATE TABLE Books (

ISBN VARCHAR(50) PRIMARY KEY,

 Database 11

Title VARCHAR(255),

Cover_page_description VARCHAR(100), #this includes descriptions such as

hardcover, softcover etc

Author_ID INT, #this column is a foreignkey from the author tabke when a user

searches their faouvrite author they can the come to this column and search for their

respective books

Genre VARCHAR(50),

Price DECIMAL(10,2),

FOREIGN KEY (Author_ID) REFERENCES Authors(Author_ID));

# this table has users that have registered or interracted with the book store and the

information can be used for analysis of even marketing

CREATE TABLE Users (

User_ID INT PRIMARY KEY AUTO_INCREMENT,

firstName VARCHAR(10),

LastName VARCHAR(10),

Contact INT,

Dob DATE,

Email VARCHAR(50),

Password VARCHAR(8));
 Database 12

#tha table contains reviews and feedback and can be used for analysis and to

improve the services

CREATE TABLE Reviews (

Review_ID INT PRIMARY KEY,

Book_ISBN VARCHAR(50),

User_ID INT,

Rating INT,

Comment TEXT,

FOREIGN KEY (Book_ISBN) REFERENCES Books(ISBN),

FOREIGN KEY (User_ID) REFERENCES Users(User_ID));

#acts as a link between the users and order_details tables

CREATE TABLE Orders (

Order_ID INT PRIMARY KEY AUTO_INCREMENT,

User_ID INT,

Order_Date DATE,

FOREIGN KEY (User_ID) REFERENCES Users(User_ID));

#details of the orders that users have placed

CREATE TABLE Order_Details (

 Database 13

Order_Detail_ID INT PRIMARY KEY,

Book_ISBN VARCHAR(50),

Quantity INT,

Price DECIMAL(10,2),

mode VARCHAR(10),

Receipt_Number INT,

Order_Date DATE,

FOREIGN KEY (Book_ISBN) REFERENCES Books(ISBN));

#table contains information books that have been rated as favourite

CREATE TABLE Favorites (

Favorite_ID INT PRIMARY KEY,

User_ID INT,

Book_ISBN VARCHAR(50),

FOREIGN KEY (User_ID) REFERENCES Users(User_ID),

FOREIGN KEY (Book_ISBN) REFERENCES Books(ISBN));

 Database 14

Constraints

Constraints are among the most vital constituents in the design of relational

database frameworks since they help in maintaining data integrity. They define and

regulate the constraints of the tables, thus ensuring any information stored is correct

and credible (Huang et al. 2023). Primary keys uniquely number the records of a

table and hence act as the table’s identification number. Foreign keys create

connections of tables with the help of keys from another table and provide data

consistency when working with related entities, (Angles et al., 2021). Qualitative

constraints ensure that every value entered in a given column has to be unique other

than the cases of the primary key constraints (Mancas and Mancas

2023).Altogether, these constraints belong to the architecture of relational databases

and help in organizing data and providing reliable further queries in systems such as

your virtual bookstore.

Explained entities

table columns Description

 Database 15

Order_Details Order_Detail_ID Unique identifier of the record in the order_details table

User_ID Unique identifier of the user

Book_ISBN Unique number of each book as record in each book

Quantity Number of items ordered

Price Total cost of the order

Order_Date Date of order

Receipt_number Receipt generated after payment

Mode_of_payment Mode of payment

Reviews Review_ID Unique identifier of the record

Book_ISBN Unique number of each book as record in each book

User_ID Unique identifier of the users

Rating The rating of the book

Comment The feedback

Books ISBN Unique number of each book as record in each book

Title Title of the book as recorded on the book

Cover_page_description The description of the cover page or hardcover or softcover etc.

Author_ID Unique number of author

Genre Genre of the books for example romance, thriller etc.

Price The price of each book

Favourite Favorite_ID Unique identifier of the record

User_ID Unique identifier of the users

Book_ISBN Unique number of each book as record in each book

 Database 16

Author Author_ID Unique number of author

Name The full name of the Author

Bio Profile of the author

Nationality The country in which the author lives

Users User_ID Unique identifier of the record. Which becomes a unique

firstName number for the user

LastName First name of the user/client

Contact Last name of the user/client

Dob Phone number of the client/user

Email Date of birth of the client/user

Password Email of the client/user

Password generated using the registration

Suppliers Supplier_ID Unique identifier of the record

ComapnyName The company supplies books.

Contact_Person The person who supplied the books

Email The email of the supplier

Phone The phone number of the supplier

Address The address of the supplier

Book_ISBN Unique number of each book as record in each book

price The buying price

 Database 17

Logical schema

Normalization

The First Normal Form (1NF) guarantees that every column has atomic

values and contains no other group or arrays of values. The Second Normal Form

(2NF) Goes a step beyond 1NF by making sure that all non-key attributes are

dependent on the primary key thus eliminating the issue of partial dependencies,

(Effendy 2018). Third Normal Form (3NF): An extension of the 2NF by guaranteeing

that non-key fields cannot depend on other fields that are not the key—transitive

dependencies are removed (Demba 2013). Sug and Korea (2020) notes that

normalization is essential for ensuring that there is order when organizing data to

prevent data anomalies, namely insertion anomalies, update anomalies, and deletion
 Database 18

anomalies.

Normalization offers several benefits in database design:

Reduction of Data Redundancy: Since data is separated into different tables and the

presence of redundant data is eliminated, normalization therefore reduces storage

and enhances consistency.

Improved Data Integrity: Normalization makes each piece of data to be stored in the

primary table, this eliminates update anomalies since data is stored only in one place

(Huang et al., 2023).

Simplification of Queries: Normalisation of data is easier if performed through

structures that apply the principles of normalisation. The queries can also be easier

since the data is ‘arranged’ logically to fit the typical usage patterns, it may therefore

not be as difficult to join as well as get data efficiently.

 Database 19

Easier Maintenance: If the databases are normalized then insertion, updating and

deletion of data is easier to perform. This is advantageous as most modifications are

required in fewer areas hence minimizing mistakes, also the database can easily pull

through modifications needed for business evolution.

Scalability: Normalization helps scalability by minimising the amount of data

repetition, and hence the best utilization of space. Normalization is beneficial when

the size of the database increases because it makes individual tables more

manageable and minimizes the joining of the tables in queries (Wang et al., 2010).

REFLECTION

The concept of the web-based online bookstore database was effectively

used for understanding the principles of normalization and applying different integrity

constraints as well as experience in writing complex queries. To avoid data

redundancy and improve the tables’ integrity, we used the tables Authors, Books,

Users, Orders, and Order_Details within the schema. Enforcing the existence of

foreign keys helped to keep the references in tables intact, for example, tying

Author_ID in the Books table to the Authors table and User_ID in the Orders table to

the Users table.

Simulation of preparing and practising SQL queries to get the number of

books purchased by the client and the amount of income earned on particular dates

proved the skills in dealing with numerous joins and aggregation. These included

issues like the creation of a solid schema, realization of referential integrity and

working with large data sets: these were solved by proper indexing and trials, (Sug

and Korea, 2020). This project also revealed the significance of carrying out a proper
 Database 20

definition of the database structure to enhance proper management and subsequent

analysis of the data collected.

REFERENCES

Demba, M. (2013), “Algorithm for Relational Database Normalization Up to 3NF”,

International Journal of Database Management Systems, Vol. 5 No. 3, doi:

10.5121/ijdms.2013.5303.

https://www.researchgate.net/publication/269674039_Algorithm_for_Relational_Data

base_Normalization_Up_to_3NF

Efendy, Z. (2018), “NORMALIZATION IN DATABASE DESIGN”, Jurnal CoreIT: Jurnal

Hasil Penelitian Ilmu Komputer Dan Teknologi Informasi, Vol. 4 No. 1, doi:

10.24014/coreit.v4i1.4382. https://ejournal.uin-

suska.ac.id/index.php/coreit/article/view/4382
 Database 21

Angles, R., Bonifati, A., Dumbrava, S., Fletcher, G., Hare, K.W., Hidders, J., Lee, V.E., Li,

B., Libkin, L., Martens, W. and Murlak, F., 2021, June. Pg-keys: Keys for property

graphs. In Proceedings of the 2021 International Conference on Management of

Data (pp. 2423-2436). https://dl.acm.org/doi/abs/10.1145/3448016.3457561

Huang, H., Shen, B., Zhong, L. and Zhou, Y. (2023), “Protecting Data Integrity of Web

Applications with Database Constraints Inferred from Application Code”, International

Conference on Architectural Support for Programming Languages and Operating

Systems - ASPLOS, Vol. 2, doi: 10.1145/3575693.3575699.

https://dl.acm.org/doi/abs/10.1145/3575693.3575699

Mancas, C., Serban, C. and Mancas, D.C. (2023), “On Software Application Database

Constraint-driven Design and Development”, Journal of Computer Science

Research, Vol. 5 No. 1, doi: 10.30564/jcsr.v5i1.5476.

https://journals.bilpubgroup.com/index.php/jcsr/article/view/5476

Sug, H. and Korea, R.O., 2020. A method for normalization of relation schema based on

data to abide by the third normal form. WSEAS Trans. Math, 19, pp.216-225.

https://www.wseas.com/journals/mathematics/2020/a405106-054.pdf

Wang, T.J. (T. J.), Du, H. and Lehmann, C.M. (2010), “Accounting For The Benefits Of

Database Normalization”, American Journal of Business Education (AJBE), Vol. 3

No. 1, doi: 10.19030/ajbe.v3i1.371.

https://clutejournals.com/index.php/AJBE/article/view/371

Quach, S., Thaichon, P., Martin, K.D., Weaven, S. and Palmatier, R.W., 2022. Digital

technologies: tensions in privacy and data. Journal of the Academy of Marketing

Science, 50(6), pp.1299-1323. https://link.springer.com/article/10.1007/s11747-022-

00845-y
Database 22