CUSTOMER

Create Database bfe_nnnnn

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’, ”);

ORDER1

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);

ORDER_LINE

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’);

INVENTORY

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;

Hababeh, I., Khalil, I., & Khreishah, A. (2015). Designing high performance web-based computing services to promote telemedicine database management system. IEEE transactions on services computing, 8(1), 47-64.

Howard, N., Corcoran, N., Peters, J., Moon, D., Murphy, D., Kerger, M., … & Costello, A. (2016). Caisis: A Prostate Cancer Database Management System for Translational Research.

Lee, H., Chapiro, J., Schernthaner, R., Duran, R., Wang, Z., Gorodetski, B., … & Lin, M. (2015). How I do it: a practical database management system to assist clinical research teams with data collection, organization, and reporting. Academic radiology, 22(4), 527-533.

Nidzwetzki, J. K., & Güting, R. H. (2016). DISTRIBUTED SECONDO: An extensible highly available and scalable database management system. FernUniversität, Fakultät für Mathematik und Informatik. 

Nidzwetzki, J. K., & Güting, R. H. (2017). Distributed secondo: an extensible and scalable database management system. Distributed and Parallel Databases, 35(3-4), 197-248.

Rein, M. S., Jackson, K. V., Nureddin, A., Clarke, R. N., Friedman, A. J., & Hornstein, M. D. (2015). A self-programmable in vitro fertilization/gamete intrafallopian transfer patient database management system for MacIntosh Computers.