Comprehensive Report: Database Management System for University

This assignment is a comprehensive report on a Database Management System (DBMS), specifically utilizing MySQL to implement an Entity-Relationship Diagram (ERD) into a database. The report begins with an introduction to DBMS concepts, followed by the relational schema. It details create queries for establishing tables and the database, insert queries for populating the database with data, and DML (Data Manipulation Language) queries for retrieving specific datasets based on predefined conditions. The report includes detailed SQL code for creating tables such as CUSTOMER, ORDER1, ORDER_LINE, INVENTORY, BOOK, WHOLESALER, E_BOOK, E_BOOK_TYPE, CATEGORY, AUTHOR, WRITTEN_BY, REVIEWER, and REVIEW, along with insert statements to populate each table with sample data. Furthermore, the report provides DML queries that demonstrate data retrieval, ordering, and filtering. The conclusion summarizes the successful implementation and functionality of the database, highlighting the correct execution of queries and the effective use of primary and foreign keys. The report also includes a bibliography of relevant sources.

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