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.
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,
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),
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),
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 (
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),
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)
);
INSERT INTO WHOLESALER VALUES(1, ‘Tuggeranong Bookbarn’, ‘Jack Pratt’, 0266061458, ‘[email protected]‘, ‘1 Jones St’, ‘Tuggeranong’, 2601, ‘Australia’, ‘Good prices on health books’);
INSERT INTO WHOLESALER VALUES(2, ‘Mega Books’, ‘James McGrath’, 355551549, ‘[email protected]‘, ’32 East St’, ‘Perth’, 6140, ‘Australia’, ”);
INSERT INTO WHOLESALER VALUES(3, ‘Adelaide Booksellers’, ‘Henry Moriaty’, 84100216, ‘[email protected]‘, ’12 Twin St’, ‘Adelaide’, 5000, ‘Australia’, ‘The cost of the books are relevantly low’);
INSERT INTO WHOLESALER VALUES(4, ‘Time Booksellers’, ‘Peter Davor’, 59776809, ‘[email protected]‘, ‘1/45 Simcock St’, ‘Somerville’, 3912, ‘Australia’, ”);
INSERT INTO WHOLESALER VALUES(5, ‘Monsoon Books & Films’, ‘Doris Mackenzie’, 74019841, ‘[email protected]@monsoon.com’, ‘3 Ironworks Turnpike’, ‘Lexington’, 78428, ‘USA’, ‘Minimum order $500 USD’);
INSERT INTO CUSTOMER VALUES(1, ‘Joseph’, ‘Nilsen’, 49479379, ‘[email protected]‘, ’32 Webb Road’, ‘WICKHAM’, 2293, 0.8);
INSERT INTO CUSTOMER VALUES(2, ‘Mary’, ‘Virgo’, 13265413, ‘[email protected]‘, ’98 Neil St’, ‘Cressy’, 3373, ”);
INSERT INTO CUSTOMER VALUES(3, ‘Mason’, ‘Iverson’, 49344474, ‘[email protected]‘, ’41 Butler Crescent’, ‘YALLAMBIE’, 2325, 1.5);
INSERT INTO CUSTOMER VALUES(4, ‘Toby’, ‘McMillan’, 040853185, ‘[email protected]‘, ’65 Crest Ave’, ‘Ballarat’, 3353, ”);
INSERT INTO CUSTOMER VALUES(5, ‘Sultan’, ‘Shaik’, 30307885, ‘[email protected]‘, ‘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 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);
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’);
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);
INSERT INTO BOOK VALUES(5, 3, 11);
INSERT INTO `reviewer` (`reviewerID`, `name`, `email`, `avatar`, `customer`) VALUES (1, ‘Ali Heritage’, ‘[email protected]‘, ‘Excited’, NULL);
INSERT INTO `reviewer` (`reviewerID`, `name`, `email`, `avatar`, `customer`) VALUES (2, ‘Max Utz’, ‘[email protected]‘, ‘Satisfied’, NULL);
INSERT INTO `reviewer` (`reviewerID`, `name`, `email`, `avatar`, `customer`) VALUES (3, ‘Mason Iverson’, ‘[email protected]‘, ‘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’);
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;
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’;
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
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;
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.
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.
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.
Essay Writing Service Features
Our Experience
No matter how complex your assignment is, we can find the right professional for your specific task. Contact Essay is an essay writing company that hires only the smartest minds to help you with your projects. Our expertise allows us to provide students with high-quality academic writing, editing & proofreading services.
Free Features
Free revision policy
$10Free bibliography & reference
$8Free title page
$8Free formatting
$8How Our Essay Writing Service Works
First, you will need to complete an order form. It's not difficult but, in case there is anything you find not to be clear, you may always call us so that we can guide you through it. On the order form, you will need to include some basic information concerning your order: subject, topic, number of pages, etc. We also encourage our clients to upload any relevant information or sources that will help.
Complete the order form
Once we have all the information and instructions that we need, we select the most suitable writer for your assignment. While everything seems to be clear, the writer, who has complete knowledge of the subject, may need clarification from you. It is at that point that you would receive a call or email from us.
Writer’s assignment
As soon as the writer has finished, it will be delivered both to the website and to your email address so that you will not miss it. If your deadline is close at hand, we will place a call to you to make sure that you receive the paper on time.
Completing the order and download