Relational Database Design and Implementation Project: ICT 701

Desklib provides past papers and solved assignments for students. This project demonstrates database design and SQL query implementation.

ICT 701 Relational Database Systems
(Task 2)

Table of Contents
Introduction................................................................................................................................2
Part 1..........................................................................................................................................3
Part 2..........................................................................................................................................7
Conclusion................................................................................................................................20
References................................................................................................................................21
List of Figures
Figure 1: ER Diagram................................................................................................................4
Figure 2: RDM Diagram............................................................................................................5
Figure 3: Table Publisher Structure...........................................................................................8
Figure 4 Table Customer Structure............................................................................................9
Figure 5 Table Orders Structure.................................................................................................9
Figure 6 Table Book Structure.................................................................................................10
Figure 7 Table Publisher..........................................................................................................11
Figure 8 Table Customer..........................................................................................................12
Figure 9 Table Orders..............................................................................................................12
Figure 10 Table Book...............................................................................................................13
Figure 11 Query Number 3......................................................................................................13
Figure 12: Query Number 4.....................................................................................................14
Figure 13: Query Number 5.....................................................................................................15
Figure 14: Query Number 6(a).................................................................................................15
Figure 15 Query Number 6(b)..................................................................................................16
Figure 16 Query Number 6(c)..................................................................................................16
Figure 17 Query Number 6(d)..................................................................................................16
Figure 18 Query Number 6(e)..................................................................................................16
Figure 19 Query Number 6(f)..................................................................................................16
Figure 20 Query Number 6(g)..................................................................................................17
Figure 21 Query Number 7......................................................................................................17
Figure 22 Query Number 8......................................................................................................17
Figure 23 Query Number 9(a)..................................................................................................17
Figure 24 Query Number 9(b)..................................................................................................18
Figure 25 Query Number 10....................................................................................................18
Figure 26 Query Number 11....................................................................................................18
Figure 27 Query Number 12....................................................................................................18
Figure 28 Query Number 13....................................................................................................19
Figure 29 Query Number 14....................................................................................................20
Figure 30 Query Number 15(a)................................................................................................20
Figure 31Query Number 15(b).................................................................................................20
2

Figure 32 Query Number 15(c)................................................................................................20
Figure 33 Query Number 15(d)................................................................................................20
3

Introduction
The given assessment is based on Database and two different tables are given. One needs to
produce ER diagram and Relational data model. MS word is used for the representation. All
the assumptions and relations that are considered while producing er diagram are explained
and issues which are faced while creating the whole database system. All the above
mentioned points are covered in part A of the assessment while in part B one need to perform
some given SQL queries with the help of MYSQL and attach their outputs with the
screenshots
4

Part 1
ERD(Entity Relationship Diagram)
Assumptions:
Multiple Books can be published by a single Publisher.
Multiple Orders can be given by a Customer.
Orderingof Books can be done multiple times.
PublisherID here is defined as the primary key in Publisher Entity.
ISBN here is defined as the primary key in Book Entity.
Also, OrderID is defined as the primary key in Orders Entity.
CustomerID is also defined as the primary key in Customer entity.
Figure 1: ER Diagram
5

RDM (Relational Data Model)
Figure 2: RDM Diagram
Now, moving on, the next point is to make sure that all the entities must be in 3rd Normal
Form. We have also mentioned the correct attributes for each entity mentioned in above two
diagrams (Maes et al.,2010). Also Primary keys and Foreign keys are mentioned. We
encountered the Partial Dependency in the given tables. The given tables were in First
Normal Form, Firstly; the tables are being converted into Second Normal Form by removing
the Partial Dependency present. We haven’t encountered the Transitive Dependency here so
the tables need not to be converted into the Third Normal Form (Taylor et al., 2003).
6

First Normal Form
ORDERS {CUSTOMERID,ORDERNO,ISBN,PUBLISHERID,NAME,
ADDRESS,ORDERDATE,QTYORDERED, DELIVERDATE, DATEPUBLISHED,TITLE,
COST, RETAIL, CATEGORY, ,PUBLISHERNAME, PUBLISHERCONTACT,
PUBLISHERPHONE}
CUSTOMER{CUSTOMERID, FIRSTNAME, SURNAME, ADDRESS, CITY, STATE,
POSTCODE, USERNAME, PASSWORD}
Second Normal Form
PUBLISHER {PUBLISHERID, PUBLISHERNAME, PUBLISHERCONTACT,
PUBLISHERPHONE}
BOOK{ISBN, PUBLISHERID, CATEGORY, RETAIL, COST, TITLE,
DATEPUBLISHED}
ORDERS {ORDERNO,ORDERDATE, CUSTOMERID, ISBN, DELIVERYDATE,
QTYORDERED}
CUSTOMER {CUSTOMERID, FIRSTNAME, ADDRESS, CITY, STATE, POSTCODE,
USERNAME, SURNAME, PASSWORD}
Third Normal Form:
PUBLISHER {PUBLISHERID, PUBLISHERNAME, PUBLISHERCONTACT,
PUBLISHERPHONE}
BOOK{ISBN, PUBLISHERID, CATEGORY, RETAIL, COST, TITLE,
DATEPUBLISHED}
ORDERS {ORDERNO,ORDERDATE, CUSTOMERID, ISBN, DELIVERYDATE,
QTYORDERED}
CUSTOMER {CUSTOMERID, FIRSTNAME, ADDRESS, CITY, STATE, POSTCODE,
USERNAME, SURNAME, PASSWORD}
7

Issues Faced:
Issues which are being faced actually can create problems later on or while creating the
database and inserting the data into the tables, so the issues are to be solved to keep the
companies data secure and efficient. Some of the issues faced are:
Naming Conventions can actually create a problem while creating the database as this created
an issue while creating the table Order because this is the keyword that can’t be used as a
table name so we changed the name to Orders.
Also, issue encountered is Partial Dependencies that are resolved by creating the given data
into Second Normal Form. Transitive Dependency wasn’t here so there was no need of
converting the data into Third Normal Form (Staley et al., 2016).
8

Part 2
MYSQL Queries:
Query Number 1:
Table Publisher:
create table Publisher(
PublisherID varchar(45) Not Null Primary Key,
PublisherName varchar(45) Not Null,
PublisherContact Varchar(45) Not Null,
PublisherPhone int Not Null);
Figure 3: Table Publisher Structure
Table Customer:
create table Customer(
CustomerID varchar(45) Not Null Primary Key,
FirstName varchar(45) Not Null,
SurName varchar(45) Not Null,
Address varchar(45) Not Null,
City varchar(45) Not Null,
State varchar(45) Not Null,
PostCode int Not Null,
UserName varchar(45) Not Null,
Password varchar(45) Not Null);
9

Figure 4 Table Customer Structure
Table Orders:
create table Orders(
OrderNo varchar(45) Not Null,
QtyOrdered int Not Null,
DeliveryDate Date Not Null,
ISBN varchar(45) Not Null,
CustomerID varchar(45) Not Null,
OrderDate Date Not Null,
Primary key(OrderNo,ISBN,CustomerID));
Figure 5 Table Orders Structure
10

Table Book:
create table Book(
PublisherID varchar(45) Not Null,
ISBN varchar(45) Not Null,
Category varchar(45) Not Null,
Retail float Not Null,
Cost float Not Null,
Title varchar(45) Not Null,
DatePublished int Not Null,
Primary Key(PublisherID,ISBN));
Figure 6 Table Book Structure
Adding Foreign Keys Query:
ALTER TABLE BOOK
ADD FOREIGN KEY (PUBLISHERID) REFERENCES PUBLISHER (PUBLISHERID);
ALTER TABLE ORDERS
ADD FOREIGN KEY (CUSTOMERID) REFERENCES CUSTOMER(CUSTOMERID);
ALTER TABLE ORDERS
ADD FOREIGN KEY (ISBN) REFERENCES BOOK(ISBN);
11

Query Number 2:
Values Insertion in Table Publisher:
INSERT INTO PUBLISHER VALUES ('0-07212','Mercer','Wilson William',0352430012);
INSERT INTO PUBLISHER VALUES ('0-61906','Thomson','Dresler Anne',0733239088);
INSERT INTO PUBLISHER VALUES ('0-13120','Prentice Hall','Friel Julie',0223541200);
INSERT INTO PUBLISHER VALUES ('0-47126','Wiley','Pearson Quentin',0733456721);
Figure 7 Table Publisher
Values Insertion in Table Customer:
INSERT INTO CUSTOMER VALUES('AA0621', 'Aaron', 'Adamson','23 Chardonnay
Crt','Wilson','WA',6107,'Aadam','2duI8k');
INSERT INTO CUSTOMER VALUES ('GY0001', 'Gareth', 'Yardley','88 Graham
Rd','Launceston','TAS',7250,'Gyard','6K9Io3');
INSERT INTO CUSTOMER VALUES ('HA8870', 'Harriet', 'Annerley','6 First
Ave','Belmont','QLD',4153,'Hanne','0ubnl2');
INSERT INTO CUSTOMER VALUES ('JB0012', 'Jordan', 'Black ','12 Blackbird
Lne','Vermont','VIC',3133,'Jblack','9lowsi');
INSERT INTO CUSTOMER VALUES ('JB1165', 'John', 'Brown','54 Georgette St','Battery
Point','TAS',7004,'Jbrown','7eInhd');
INSERT INTO CUSTOMER VALUES ('JB1302', 'Jonathon', 'Brown','45 Quail
Crt','Buderim','QLD',4556,'Jbrown2','3nGbks');
INSERT INTO CUSTOMER VALUES ('JB6544', 'Juliet', 'Bardensley','7 Jamieson
Circ','Belmont','NSW',2280,'Jbard','6Hujsl');
INSERT INTO CUSTOMER VALUES ('SA0010', 'Sally', 'Adams','187 Main
Rd','Broadbeach','QLD',4218,'Sadam','8jwxmy');
12