Database Implementation Project MySQL

This assignment details the implementation of a database system using MySQL. It includes the creation of tables with defined primary and foreign keys to establish relationships between entities like books, authors, and inventory. The document showcases various SQL queries, including subqueries, demonstrating how to retrieve specific information from the database. Finally, it provides a conclusion summarizing the project's success in implementing a functional and efficient database.

Running head: THE DATABASE MANAGEMENT SYSTEM
The Database Management System
Name of the Student
Name of the University

1THE DATABASE MANAGEMENT SYSTEM
Table of Contents
1. Introduction:................................................................................................................................2
2. Relational Schema:......................................................................................................................3
3. Create Query:...............................................................................................................................4
4. Insert Query:..............................................................................................................................10
5. DML Query:..............................................................................................................................15
6. Conclusion:................................................................................................................................18
Bibliography:.................................................................................................................................19

2THE DATABASE MANAGEMENT SYSTEM
1. Introduction:
The Database Management System is the way of storing, manipulating, deleting and
retrieving data to the database. The MySQL database has been used for implementing the ERD
into a database. The report is consists of the relational schema, create queries to create table and
database, insert queries to insert data into database and the quires to retrieve various data set
form the database based on predefined condition.

3THE DATABASE MANAGEMENT SYSTEM
2. Relational Schema:

4THE DATABASE MANAGEMENT SYSTEM
3. Create Query:
Create Database bfe_30307885
CREATE TABLE CUSTOMER (
customerID int NOT NULL PRIMARY KEY,
firstName varchar(40),
lastName varchar(40),
phone int,
email varchar(40),
address varchar(40),
suburb varchar(40),
postcode int,
loyaltyDiscount double
);
CREATE TABLE ORDER1 (
orderID int NOT NULL PRIMARY KEY,
customerID int,
dateOrdered DATE,
dateFulfilled DATE,

5THE DATABASE MANAGEMENT SYSTEM
postageAmount decimal(10,2),
discountApplied double,
FOREIGN KEY (customerID) REFERENCES CUSTOMER(customerID)
);
CREATE TABLE ORDER_LINE (
orderID int NOT NULL,
inventoryID int NOT NULL,
quantity int,
amount decimal(10,2),
CONSTRAINT PK_O_LINE PRIMARY KEY (orderID, inventoryID),
FOREIGN KEY (orderID) REFERENCES ORDER1 (orderID),
FOREIGN KEY (inventoryID) REFERENCES INVENTORY(inventoryID)
);
CREATE TABLE INVENTORY(
inventoryID int NOT NULL PRIMARY KEY,
category int NOT NULL,
title varchar(40),
abstract varchar(150),

6THE DATABASE MANAGEMENT SYSTEM
yearPublished YEAR,
unitPrice decimal(10,2),
FOREIGN KEY (category) REFERENCES CATEGORY(categoryID)
);
CREATE TABLE BOOK (
inventoryID int NOT NULL PRIMARY KEY,
wholesaler int,
qtyInStock int,
FOREIGN KEY (inventoryID) REFERENCES INVENTORY(inventoryID),
FOREIGN KEY (wholesaler) REFERENCES WHOLESALER(wholesalerID)
);
CREATE TABLE WHOLESALER (
wholesalerID int NOT NULL PRIMARY KEY,
name varchar(40),
contactName varchar(40),
phone int,
email varchar(40),
address varchar(40),

7THE DATABASE MANAGEMENT SYSTEM
suburb varchar(40),
postcode int,
country varchar(40),
comments varchar(40)
);
CREATE TABLE E_BOOK (
inventoryID int NOT NULL,
EBookType int NOT NULL,
FOREIGN KEY (inventoryID) REFERENCES INVENTORY(inventoryID),
FOREIGN KEY (EBookType) REFERENCES E_BOOK_TYPE(ebookTypeID),
CONSTRAINT PK_E_BOOK PRIMARY KEY (inventoryID)
);
CREATE TABLE E_BOOK_TYPE(
ebookTypeID int NOT NULL PRIMARY KEY,
fileType varchar(40),
description varchar(40)
);
CREATE TABLE CATEGORY (

8THE DATABASE MANAGEMENT SYSTEM
categoryID int NOT NULL PRIMARY KEY,
name varchar(40),
description varchar(100)
);
CREATE TABLE AUTHOR (
authorID int NOT NULL PRIMARY KEY,
firstName varchar(40),
surname varchar(40),
country varchar(40),
bio varchar(500)
);
CREATE TABLE WRITTEN_BY (
authorID int NOT NULL,
inventoryID int NOT NULL,
role varchar(40),
CONSTRAINT PK_WR_BOOK PRIMARY KEY (authorID, inventoryID),
FOREIGN KEY (inventoryID) REFERENCES INVENTORY(inventoryID),

9THE DATABASE MANAGEMENT SYSTEM
FOREIGN KEY (authorID) REFERENCES AUTHOR(authorID)
);
CREATE TABLE REVIEWER(
reviewerID int NOT NULL PRIMARY KEY,
name varchar(40),
email varchar(40),
avatar varchar(40),
customer int,
FOREIGN KEY (customer) REFERENCES CUSTOMER(customerID)
);
CREATE TABLE REVIEW(
reviewerID int NOT NULL,
inventoryID int NOT NULL,
reviewText varchar(150),
reviewDate DATE,
CONSTRAINT PK_WR_BOOK PRIMARY KEY (reviewerID, inventoryID),
FOREIGN KEY (inventoryID) REFERENCES INVENTORY(inventoryID),
FOREIGN KEY (reviewerID) REFERENCES REVIEWER(reviewerID)

10THE DATABASE MANAGEMENT SYSTEM
);
4. Insert Query:
INSERT INTO WHOLESALER VALUES(1, 'Tuggeranong Bookbarn', 'Jack Pratt',
0266061458, '[e-mail]', '1 Jones St', 'Tuggeranong', 2601, 'Australia', 'Good
prices on health books');
INSERT INTO WHOLESALER VALUES(2, 'Mega Books', 'James McGrath',
355551549, '[e-mail]', '32 East St', 'Perth', 6140, 'Australia', '');
INSERT INTO WHOLESALER VALUES(3, 'Adelaide Booksellers', 'Henry Moriaty',
84100216, '[e-mail]', '12 Twin St', 'Adelaide', 5000, 'Australia', 'The cost of the
books are relevantly low');
INSERT INTO WHOLESALER VALUES(4, 'Time Booksellers', 'Peter Davor',
59776809, '[e-mail]', '1/45 Simcock St', 'Somerville', 3912, 'Australia', '');
INSERT INTO WHOLESALER VALUES(5, 'Monsoon Books & Films', 'Doris
Mackenzie', 74019841, 'sales@[e-mail]', '3 Ironworks Turnpike', 'Lexington',
78428, 'USA', 'Minimum order $500 USD');
INSERT INTO CUSTOMER VALUES(1, 'Joseph', 'Nilsen', 49479379,
'[e-mail]', '32 Webb Road', 'WICKHAM', 2293, 0.8);
INSERT INTO CUSTOMER VALUES(2, 'Mary', 'Virgo', 13265413,
'[e-mail]', '98 Neil St', 'Cressy', 3373, '');
INSERT INTO CUSTOMER VALUES(3, 'Mason', 'Iverson', 49344474,
'[e-mail]', '41 Butler Crescent', 'YALLAMBIE', 2325, 1.5);

11THE DATABASE MANAGEMENT SYSTEM
INSERT INTO CUSTOMER VALUES(4, 'Toby', 'McMillan', 040853185,
'[e-mail]', '65 Crest Ave', 'Ballarat', 3353, '');
INSERT INTO CUSTOMER VALUES(5, 'Sultan', 'Shaik', 30307885,
'[e-mail]', '1254 Sturt St', 'Ballarat', 3353, '');
INSERT INTO ORDER1 VALUES(1, 3, 2017-05-03, 2017-05-09, 3.30, '');
INSERT INTO ORDER1 VALUES(2, 1, 2017-09-08, 2017-09-15, 1.00, 2.3);
INSERT INTO ORDER1 VALUES(3, 4, 2017-09-01, 2017-09-06, 9.50, '');
INSERT INTO ORDER1 VALUES(4, 5, 2017-03-21, '2017-03-25', '', '');
INSERT INTO ORDER1 VALUES(5, 2, 2017-10-29, 2017-11-04, '', 4.4);
INSERT INTO CATEGORY VALUES(1, 'Science', 'Popular Science');
INSERT INTO CATEGORY VALUES(2, 'Thriller', 'Mystery Fiction, Legal thriller,
Medical thriller');
INSERT INTO CATEGORY VALUES(3, 'Children', 'Books for all age groups to 15');
INSERT INTO CATEGORY VALUES(4, 'General Fiction', 'Any other fiction');
INSERT INTO CATEGORY VALUES(5, 'History', 'Historical research and ancient
book reprints');
INSERT INTO CATEGORY VALUES(6, 'Health & Wellbeing', '');
INSERT INTO INVENTORY VALUES(1, 3, 'Leave Me Alone!', 'One day, a
grandmother shouts, "LEAVE ME ALONE!" and leaves her tiny home and her very big family

12THE DATABASE MANAGEMENT SYSTEM
to journey to the moon and beyond to find peace and quiet to finish her knitting. Along the way,
she encounters ravenous bears, obnoxious goats, and even hordes of aliens!', 2016, 10.33);
INSERT INTO INVENTORY VALUES(2, 1, 'The Grand Design', 'The Grand Design is
a popular-science book written by physicists Stephen Hawking and Leonard Mlodinow and
published by Bantam Books in 2010.', 2010, 9.34);
INSERT INTO INVENTORY VALUES(3, 3, 'Creepy Carrots', 'Jasper Rabbit loves
carrots—especially Crackenhopper Field carrots. He eats them on the way to school.', 2017,
24.68);
INSERT INTO INVENTORY VALUES(4, 2, 'The Wife Between Us', 'It’s about a
jealous wife, obsessed with her replacement.It’s about a younger woman set to marry the man
she loves.', 2018, 16.19);
INSERT INTO INVENTORY VALUES(5, 3, 'Where the Wild Things Are', 'Where the
Wild Things Are is a 1963 children's picture book by American writer and illustrator Maurice
Sendak, originally published by Harper & Row.', 1963, 12.34);
INSERT INTO INVENTORY VALUES(6, 1, 'A Brief History of Time', 'A landmark
volume in science writing by one of the great minds of our time, Stephen Hawking’s book
explores many profound questions.', 1998, 14.00);
INSERT INTO INVENTORY VALUES(7, 1, 'Astrophysics for People in a Hurry', 'What
is the nature of space and time? How do we fit within the universe? How does the universe fit
within us? There’s no better guide through these mind-expanding questions than acclaimed
astrophysicist and best-selling author Neil deGrasse Tyson.', 2017, 18.00);

13THE DATABASE MANAGEMENT SYSTEM
INSERT INTO AUTHOR VALUES(1, 'Vera', 'Brosgol', 'USA', '');
INSERT INTO AUTHOR VALUES(2, 'Maurice', 'Sendak', 'USA', 'Maurice Bernard
Sendak was an American illustrator and writer of children's books.');
INSERT INTO AUTHOR VALUES(3, 'Aaron ', 'Reynolds', 'USA', '');
INSERT INTO AUTHOR VALUES(4, 'Neil deGrasse', 'Tyson', 'United Kingdom',
'Astrophysics for People in a Hurry is a 2017 popular science book by Neil deGrasse Tyson,
centering around a number of basic questions about the universe.');
INSERT INTO AUTHOR VALUES(5, 'Sarah', 'Pekkanen', 'USA', 'SARAH
PEKKANEN is the internationally and USA Today bestselling author of seven previous
novels.');
INSERT INTO AUTHOR VALUES(6, 'Stephen', 'Hawking', 'USA', 'A Brief History of
Time, which has sold nearly 10 million copies in 40 languages. ');
INSERT INTO AUTHOR VALUES(7, 'Greer', 'Hendricks', 'USA', '');
INSERT INTO WRITTEN_BY VALUES(1, 1, 'Author');
INSERT INTO WRITTEN_BY VALUES(6, 2, 'Author');
INSERT INTO WRITTEN_BY VALUES(3, 3, 'Author');
INSERT INTO WRITTEN_BY VALUES(7, 4, 'Author');
INSERT INTO WRITTEN_BY VALUES(5, 4, 'Co Author');
INSERT INTO WRITTEN_BY VALUES(2, 5, 'Author');
INSERT INTO WRITTEN_BY VALUES(6, 6, 'Author');

14THE DATABASE MANAGEMENT SYSTEM
INSERT INTO WRITTEN_BY VALUES(4, 7, 'Author');
INSERT INTO ORDER_LINE VALUES(1, 4, 3, 64.76);
INSERT INTO ORDER_LINE VALUES(2, 6, 2, 28.00);
INSERT INTO ORDER_LINE VALUES(3, 7, 1, 18.00);
INSERT INTO ORDER_LINE VALUES(4, 4, 1, 16.19);
INSERT INTO ORDER_LINE VALUES(5, 1, 2, 20.66);
INSERT INTO E_BOOK_TYPE VALUES(1, 'PDF', 'Portable document format – all
devices');
INSERT INTO E_BOOK_TYPE VALUES(2, 'Kindle', 'Kindle device, also readers');
INSERT INTO E_BOOK_TYPE VALUES(3, 'EPUB', 'EPUB is a .epub extension file');
INSERT INTO E_BOOK_TYPE VALUES(4, 'MOBI', 'MOBI file extension is a
Mobipocket eBook file');
INSERT INTO E_BOOK VALUES(6, 1);
INSERT INTO E_BOOK VALUES(1, 2);
INSERT INTO E_BOOK VALUES(7, 4);
INSERT INTO E_BOOK VALUES(5, 3);
INSERT INTO BOOK VALUES(2, 5, 15);
INSERT INTO BOOK VALUES(4, 1, 3);
INSERT INTO BOOK VALUES(3, 3, 15);

15THE DATABASE MANAGEMENT SYSTEM
INSERT INTO BOOK VALUES(5, 3, 11);
INSERT INTO `reviewer` (`reviewerID`, `name`, `email`, `avatar`, `customer`)
VALUES (1, 'Ali Heritage', '[e-mail]', 'Excited', NULL);
INSERT INTO `reviewer` (`reviewerID`, `name`, `email`, `avatar`, `customer`)
VALUES (2, 'Max Utz', '[e-mail]', 'Satisfied', NULL);
INSERT INTO `reviewer` (`reviewerID`, `name`, `email`, `avatar`, `customer`)
VALUES (3, 'Mason Iverson', '[e-mail]', 'Motivating', '3');
INSERT INTO REVIEW VALUES(1, 5, 'A masterfully written fiction', '2017-10-13');
INSERT INTO REVIEW VALUES(2, 4, 'The story is boring at the beginning and the
end is not clear', '2017-01-14');
INSERT INTO REVIEW VALUES(3, 6, 'Well researched and a thoroughly great read!',
'2017-07-15');
5. DML Query:
SELECT * FROM WHOLESALER ORDER BY suburb ASC;
SELECT title, w.name, w.email, w.phone, w.suburb from INVENTORY as i INNER
JOIN BOOK as b ON i.inventoryID = b.inventoryID INNER JOIN WHOLESALER as w ON
b.wholesaler = w.wholesalerID;
SELECT CONCAT(a.firstName, ' ', a.surname) as 'Author Name', COUNT(i.title) AS
'Number of BOOK' FROM AUTHOR as a INNER JOIN WRITTEN_BY as wb ON a.authorID
= wb.authorID INNER JOIN INVENTORY as i ON wb.inventoryID = i.inventoryID WHERE
a.country = 'USA' AND NOT bio='' GROUP BY firstName;

16THE DATABASE MANAGEMENT SYSTEM
SELECT i.title, c.name FROM INVENTORY as i INNER JOIN CATEGORY as c ON
i.category = c.categoryID ORDER BY i.title DESC ;
SELECT rvr.name, r.reviewDate, i.title, a.firstName, a.surname FROM REVIEWER as
rvr INNER JOIN REVIEW as r ON rvr.reviewerID = r.reviewerID INNER JOIN INVENTORY
as i ON r.inventoryID = i.inventoryID INNER JOIN WRITTEN_BY as wb ON i.inventoryID =
wb.inventoryID INNER JOIN AUTHOR as a ON wb.authorID = a.authorID ORDER BY
rvr.name ASC;
SELECT DISTINCT c.name FROM CATEGORY as c RIGHT JOIN INVENTORY as i
ON c.categoryID = i.category;
DELETE ol, o, c
FROM order_line ol
INNER JOIN
order1 o ON ol.orderID = o.orderID
LEFT JOIN
Customer c ON o.customerID = c.customerID
WHERE
c.postcode = 3353;
UPDATE INVENTORY SET unitPrice = 15.99 WHERE title = 'A Brief History of
Time';

17THE DATABASE MANAGEMENT SYSTEM
SELECT i.title, CONCAT(a.firstName, ' ', a.surname, ' ', wb.role) AS 'Author Detail'
FROM AUTHOR as a INNER JOIN WRITTEN_BY as wb ON a.authorID = wb.authorID
INNER JOIN INVENTORY as i ON wb.inventoryID = i.inventoryID;
INSERT INTO INVENTORY VALUES(7, 1, 'Astrophysics for People in a Hurry', 'What
is the nature of space and time? How do we fit within the universe? How does the universe fit
within us? There’s no better guide through these mind-expanding questions than acclaimed
astrophysicist and best-selling author Neil deGrasse Tyson.', 2017, 18.00);
INSERT INTO AUTHOR VALUES (4, 'Neil deGrasse', 'Tyson', 'USA', '');
INSERT INTO WRITTEN_BY VALUES (4, 7, 'AUTHOR');
select c.firstName, c.lastName, o.dateOrdered, ol.quantity, ol.amount from customer c
INNER JOIN ORDER1 o ON c.customerID = o.customerID INNER JOIN order_line ol ON
o.orderID = ol.orderID WHERE o.dateFulfilled>'2017-07-01' GROUP BY c.firstName;
SELECT inventory.title AS 'Book Title', inventory.yearPublished AS 'Year of Publish',
MAX(cc) AS 'Frequency of Order', CONCAT(author.firstName, ' ', author.surname) AS 'Name
of Author'
FROM order_line
LEFT JOIN
(SELECT order_line.inventoryID, COUNT(*) AS cc
FROM order_line
GROUP BY order_line.inventoryID

18THE DATABASE MANAGEMENT SYSTEM
ORDER BY cc DESC
LIMIT 1) AS kk
ON order_line.inventoryID = kk.inventoryID
INNER JOIN inventory ON kk.inventoryID = inventory.inventoryID
INNER JOIN written_by ON inventory.inventoryID = written_by.inventoryID
INNER JOIN author ON written_by.authorID = author.authorID;
6. Conclusion:
The study concludes that the database has been implemented properly. All the queries run
accurately and required data is get as output. The database is a complex tool to manipulate. The
study has shown the process of defining primary and referential integrities into the MySQL
DBMS. The sub queries has been developed so that complex queries can be constructed.

19THE DATABASE MANAGEMENT SYSTEM
Bibliography:
Ali, S., Aslam, M. F., Hayat, A., & Shah, S. I. A. (2016). Reengineering of Economic Operation
of a Thermal Power Plant based on Multi-Tier Web Architecture. International Journal of
Computer Science and Information Security, 14(8), 801.
Ansari, M. A., Sharma, S., Shete, A., Ghorpade, S., & Ghodke, N. (2017). Event Organization
Using GPS Based Location Tracking Including Communication System. Imperial Journal
of Interdisciplinary Research, 3(6).
Hasan, S., Biswas, S., Siddiq, M., & Bakkar, A. (2016). My doctor: security, payment reporting
and online pharmacy (Doctoral dissertation, BRAC University).
Ishak, S. A., Abidin, H. Z., & Muhamad, M. (2018). Improving Medical Adherence using Smart
Medicine Cabinet Monitoring System.
Jagtap, S. S., & Hanchate, D. B. (2017). Development of Android Based Mobile App for
PrestaShop eCommerce.
John, N. W., Johnson, K. K., Daniel, K., Christopher, G. W., & John, M. (2015). Development of
an interactive web portal for Kenyan Tea. Journal of Tea Science Research, 5.
Kumar, L., Rajawat, S., & Joshi, K. (2015). Comparative analysis of NoSQL (MongoDB) with
MySQL Database. International Journal of Modern Trends in Engineering and Research,
2(5), 120-127.
Maryana, S., Kurnia, E., & Ruyani, A. (2017, February). Web-based application on employee
performance assessment using exponential comparison method. In IOP Conference
Series: Materials Science and Engineering (Vol. 166, No. 1, p. 012019). IOP Publishing.

20THE DATABASE MANAGEMENT SYSTEM
Mishra, S. K., Tawani, A., Mishra, A., & Kumar, A. (2016). G4IPDB: A database for G-
quadruplex structure forming nucleic acid interacting proteins. Scientific reports, 6,
38144.
Raju, D., & Mallesh, V. (2016). An Experimental On Time Table Generator. International
Journal Of Engineering And Computer Science, 5(11).
Reddy, E., Reddy, S. R., Kora, J. R., Minnakanti, S. C., & Tallapaneni, V. (2017). Digitized
Engineering Notebook.
Shu, L. (2017). Contact information manager prototype.
Walia, E. S., & Gill, E. S. K. (2014). A framework for web based student record management
system using PHP. International Journal of Computer Science and Mobile Computing,
3(8), 24-33.