Relational Database Systems

This guide covers the basics of entity relationship diagram, supplementary design requirements, and SQL commands for updating and deleting data. It also includes sample SQL codes for creating tables from ERD, entering data from a spreadsheet, and listing customers who have ordered books.

Running head: RELATIONAL DATABASE SYSTEMS
Relational Database Systems
Name of the Student
Name of the University
Author’s Note

1
RELATIONAL DATABASE SYSTEMS
Table of Contents
PART A......................................................................................................................................3
Approaches for modelling......................................................................................................3
Entity Relationship Diagram..................................................................................................3
Supplementary design requirements......................................................................................3
Assumptions...........................................................................................................................4
PART B......................................................................................................................................4
Creating tables from ERD......................................................................................................4
SQL Code to enter data from spreadsheet..............................................................................4
SQL Code for listing Postcode of 4556.................................................................................6
SQL code for changing postcode to 6501 for the customer ID AA0621...............................7
SQL code for adding the attribute CUST_LEVEL and CUST_DISCOUNT to the customer
table........................................................................................................................................7
SQL command sequences for updating CUST_LEVEL and CUST_Discount.....................7
SQL Command for displaying first and last names of the customers who have not ordered
books......................................................................................................................................8
SQL command sequence for listing all customers who have ordered books.........................9
SQL command sequence for increasing the retails of all books by $20 (Computer) or if
cost > $40...............................................................................................................................9
SQL command for listing ISBN and Book Title for the expensive book in the technical
book database.......................................................................................................................10
SQL command that identifies the profit of each book.........................................................10

2
RELATIONAL DATABASE SYSTEMS
SQl command for listing the customer name starting with Smith.......................................11
SQL commands for creating view for the customer table without username and password
..............................................................................................................................................11
Trigger for automatically updating the cust_level and cust_discount when a customer
purchases a book..................................................................................................................11
SQL command for deleting data from all the tables............................................................12
Bibliography.............................................................................................................................12

3
RELATIONAL DATABASE SYSTEMS
PART A
Approaches for modelling
The entity relationship modelling is used for designing the database and identification
of the flow of information. The elements are needed for the development of the database are
identified and their relationship is mapped visually for the development of the database.
Entity Relationship Diagram
Supplementary design requirements
For the development of the tables in the ER diagram the entities are identified and the
attributes used for defining the elements are used. 5 tables are created namely customer,
supplier, publisher, books and order. The tables are normalized for eliminating the duplicate

4
RELATIONAL DATABASE SYSTEMS
entry of data and it is joined using a foreign key such that the data can be combined to shown
an appropriate result of the querry.
Assumptions
For the development of the database it is assumed that the supplier would have a
supplier ID and they can be uniquely identified with it. It is also assumed that the publisher
would also have a publisher ID and they would sell book.
PART B
Creating tables from ERD
SQL Code to enter data from spreadsheet
Book
INSERT INTO `book` (`ISBN`, `DatePublished`, `Title`, `Cost`) VALUES
('0-07212-741-4', '2015', 'ASP A Beginner\'s Guide', '29.99'),
('0-13120-117-4', '2011', 'Introduction to Java Programmi', '41.25'),
('0-61901-525-X', '2015', 'Active Server Pages', '31.55'),
('0-80892-017-X', '2002', 'Basic Pathology', '45.65'),
('0-81514-077-0', '2011', 'Basic Biomechanics', '41.65'),
('0-87322-308-X', '2012', 'Motor Learning & Performance', '28.45');

5
RELATIONAL DATABASE SYSTEMS
Customer
INSERT INTO `customer` (`Customer_ID`, `OrderNumber`, `FirstName`, `Lastname`,
`Address`, `City`, `State`, `postcode`, `username`, `password`) VALUES
('AA0621', '', 'Aaron', 'Adamson', '23 Chardonnay Crt', 'Wilson', 'WA', '6107', 'Aadam',
'2duI8k'),
('GY0001', '', 'Gareth', 'Yardley', '88 Graham Rd', 'Launceston', 'TAS', '7250', 'Gyard',
'6K9lo3'),
('HA8870', '0098LLP', 'Harriet', 'Annerley', '6 First Ave', 'Belmont', 'QLD', '4153', 'Hanne',
'0ubml2'),
('JB0012', '0087KZQ', 'Jordan', 'Black', '12 Blackbird Lne', 'Vermont', 'VIC', '3133', 'Jblack',
'9lowsi'),
('JB1165', '', 'John', 'Brown', '54 Georgette St', 'Battery Point', 'TAS', '7004', 'Jbrown',
'7eInhd'),
('JB1302', '0098LLY', 'Jonathon', 'Brown', '45 Quail Crt', 'Buderim', 'QLD', '4556', 'Jbrown2',
'3nGbks'),
('JB6544', '', 'Juliet', 'Bardensley', '7 Jamieson Circ', 'Belmont', 'NSW', '2280', 'Jbard',
'6Hujsl'),
('SA0010', '', 'Sally', 'Adams', '187 Main Rd', 'Broadbeach', 'QLD', '4218', 'Sadam', '8jwxmy');
Order
INSERT INTO `order` (`Order_ID`, `Ordernumber`, `Supplier_ID`, `ISBN`, `orderdate`,
`orderquantity`) VALUES
('01', '0087KZQ', '01', '0-87322-308-X', '15-01-2018', '25'),

6
RELATIONAL DATABASE SYSTEMS
('02', '0087KZQ', '02', '0-81514-077-0', '16-01-2018', '30'),
('03', '0098LLP', '03', '0-61901-525-X', '26-01-2018', '12'),
('04', '0098LLP', '04', '0-13120-117-4', '26-01-2018', '5'),
('05', '0098LLP', '05', '0-07212-741-4', '26-01-2018', '3'),
('06', '0098LLY', '06', '0-80892-017-X', '15-01-2018', '45');
Publisher
INSERT INTO `publisher` (`PublisherID`, `SupplierID`, `PublisherName`,
`Publishercontact`, `Publisherphone`) VALUES
('0-07212', '01', 'Mercer', 'Wilson, William', '[phone]'),
('0-13120', '03', 'Prentice Hall', 'Friel, Julie', '[phone]'),
('0-47126', '04', 'Wiley', 'Pearson, Quentin', '[phone]'),
('0-61906', '02', 'Thomson', 'Dresler, Anne', '[phone]');
Supplier
INSERT INTO `supplier` (`Supplier_ID`, `PublisherID`, `Retail`, `Category`) VALUES
('01', '0-07212', '73.85', 'Sport'),
('02', '0-07212', '94.25', 'Sport'),
('03', '0-61906', '75.85', 'Computer'),
('04', '0-13120', '102.95', 'Computer'),
('05', '0-07212', '84.95', 'Computer'),
('06', '0-47126', '105.95', 'Medical');

7
RELATIONAL DATABASE SYSTEMS
SQL Code for listing Postcode of 4556
SELECT * FROM `customer` WHERE
postcode = 4556;
SQL code for changing postcode to 6501 for the customer ID AA0621
UPDATE customer
SET postcode = "6501"
WHERE
Customer_ID = "AA0621"
SQL code for adding the attribute CUST_LEVEL and CUST_DISCOUNT to the
customer table
ALTER TABLE `customer` ADD `CUST_LEVEL` CHAR(1) NOT NULL AFTER
`password`, ADD `CUST_DISCOUNT` FLOAT(4,2) NOT NULL AFTER `CUST_LEVEL`;

8
RELATIONAL DATABASE SYSTEMS
SQL command sequences for updating CUST_LEVEL and CUST_Discount
UPDATE customer
INNER JOIN `order` ON customer.OrderNumber = `order`.Ordernumber
SET customer.CUST_LEVEL = "2", customer.CUST_DISCOUNT = "0.05"
WHERE `order`.orderquantity <= "2"
UPDATE customer
INNER JOIN `order` ON customer.OrderNumber = `order`.Ordernumber
SET customer.CUST_LEVEL = "3", customer.CUST_DISCOUNT = "0.10"
WHERE `order`.orderquantity > "3"
SQL Command for displaying first and last names of the customers who have not
ordered books
SELECT FirstName, Lastname FROM `customer`
WHERE OrderNumber =""

9
RELATIONAL DATABASE SYSTEMS
SQL command sequence for listing all customers who have ordered books
SELECT CONCAT(customer.FirstName, " ", customer.Lastname) AS FullName,
customer.Customer_ID, customer.OrderNumber, `order`.Ordernumber, `order`.orderdate
FROM customer
INNER JOIN `order` ON customer.OrderNumber = `order`.Ordernumber
SQL command sequence for increasing the retails of all books by $20 (Computer) or if
cost > $40
UPDATE supplier
SET Retail = Retail + 20

10
RELATIONAL DATABASE SYSTEMS
WHERE `Category` = "Computer"
UPDATE book
SET cost = cost + 20
Where `Cost` > 40
SQL command for listing ISBN and Book Title for the expensive book in the technical
book database
SELECT ISBN, TITLE, MAX(`Cost`)
FROM book
SQL command that identifies the profit of each book
SELECT `ISBN`, `TITLE`, (`Retail` - book.`cost`) AS Profit
FROM supplier, book

11
RELATIONAL DATABASE SYSTEMS
SQl command for listing the customer name starting with Smith
SELECT * from customer
WHERE lastname LIKE 'Smith%'
SQL commands for creating view for the customer table without username and
password
CREATE VIEW customers AS SELECT Customer_ID, FirstName, Lastname, Address, City,
State, postcode, CUST_LEVEL, CUST_DISCOUNT
FROM customer
Trigger for automatically updating the cust_level and cust_discount when a customer
purchases a book

12
RELATIONAL DATABASE SYSTEMS
CREATE or REPLACE TRIGGER CUST_LEVEL
BEFORE UPDATE Of CUST_LEVEL ON customer
FOR EACH ROW
BEGIN
CUST_LEVEL = CUST_LEVEL + 1
END;
SQL command for deleting data from all the tables
TRUNCATE TABLE book;
TRUNCATE TABLE customer;
TRUNCATE TABLE order;
TRUNCATE TABLE publisher;
TRUNCATE TABLE supplier;
Bibliography
Arulmurugan, A., Nandini, R., Jayasri, P., Rahini, E. and Priyanka, B., 2015. Study of
Various Keywords Searching in Large Databases. i-manager's Journal on Cloud
Computing, 2(3), p.26.
Ullah, M.A., 2015. A Digital Library for Plant Information with Performance Comparison
between a Relational Database and a NoSQL Database (RDF Triple Store).