Relational Database Design and Implementation Project: ICT701

Desklib provides past papers and solved assignments for students. This project covers relational database design and implementation using MySQL.

ICT701
Relational Database Systems
Task 2
1

Contents
Introduction:...................................................................................................................................................3
Part A:............................................................................................................................................................4
Assumptions:.............................................................................................................................................4
NORMALIZATION:.................................................................................................................................6
Issues Encountered:...................................................................................................................................7
Part B:............................................................................................................................................................9
MySQL Queries Task:...............................................................................................................................9
Query 1:.....................................................................................................................................................9
Conclusion:..................................................................................................................................................27
References:...................................................................................................................................................28
Table of Figures:
Figure 1: ER Diagram.................................................................................................................................4
Figure 2: Relational Data Model.................................................................................................................5
Figure 3 Structure of Table Publisher..........................................................................................................9
Figure 4 Structure of Table Book..............................................................................................................10
Figure 5 Structure of Table Customer.......................................................................................................11
Figure 6 Structure of Table Orders............................................................................................................12
Figure 7 Values Insertion of Table Publisher............................................................................................13
Figure 8 Values Insertion of Table Book...................................................................................................14
Figure 9 Values Insertion of Table Customer............................................................................................15
Figure 10 Values Insertion of Table Orders...............................................................................................16
Figure 11 Query 3......................................................................................................................................16
Figure 12 Query 4......................................................................................................................................17
Figure 13 Query 5......................................................................................................................................17
Figure 14 Query 6(a).................................................................................................................................18
Figure 15 Query 6(b).................................................................................................................................18
Figure 16 Query 6(c).................................................................................................................................18
Figure 17 Query 6(d).................................................................................................................................19
Figure 18 Query 6(e).................................................................................................................................20
Figure 19: Query 6(f).................................................................................................................................20
Figure 20: Query 6(g)................................................................................................................................20
2

Figure 32 22Query 15(d)...........................................................................................................................26
Introduction:
In the given brief, two different tables are given, and one needs to make an ER diagram and
RDM using draw.io and representing it on MS Word. One should explains all the assumptions
which are considered while making ER diagram or RDM diagram and all the problems that are
encountered while doing so. In another part of the given assessment, some given SQL queries
need to perform using MYSQL and mention all queries in a text file (Chande, Sinha, 2011).
3

Part A:
Assumptions:
 A Publisher can publish multiple books.
 A Customer can give multiple orders.
 A book can be ordered multiple times.
 PublisherID is the primary key in Publisher entity as all the attributes are depending on
PublisherId.
 ISBN is the primary key in Book entity as all other attributes are depending on ISBN.
 Similarly, OrderID is also a primary key in order as all others are depending on it.
 And CustomerID is the primary key in customer entity.
Figure 1: ER Diagram
4

Figure 2: Relational Data Model
5

NORMALIZATION:
Normalization tool is used for making above ER Diagram, initially, the tables were given in the
first normal form and there were four columns that are considered as primary keys in table name
Order, and one primary key in the table name, Customer. On encountering partial dependency
one need to perform the second normalization to remove it which results in developing four
different tables named: Publisher, Order, Book, and Customer. Also one needs to perform a third
normalization form if the transitive dependency is encountered but in this case, there is no
transitive dependency, therefore, 2NF and 3NF tables will be similar.
1NF:
ORDER {CustomerID, Name, Address,OrderDate,Order No, QtyOrdered, DeliverDate,ISBN,
DatePublished,Title, Cost, Retail, Category, PublisherID,PublisherName, PublisherContact,
PublisherPhone}
CUSTOMER{CustomerID, FirstName, Surname, Address, City, State, Postcode, UserName,
Password}
2NF:
PUBLISHER {PublisherID, PublisherName, PublisherContact,
PublisherContactPublisherPhone}
BOOK{PublisherID, ISBN, Category, Retail, Cost, Title, Date Published}
ORDER{OrderDate, CustomerID, ISBN, DeliveryDate, QtyOrdered, OrderNo}
CUSTOMER{CustomerID, FirstName, Address, City, State, PostCode, UserName, Surname,
Password}
3NF:
6

PUBLISHER {PublisherID, PublisherName, PublisherContact,
PublisherContactPublisherPhone}
BOOK {PublisherID, ISBN, Category, Retail, Cost, Title, Date Published}
ORDER {OrderDate, CustomerID, ISBN, Delivery Date, QtyOrdered, OrderNo}
CUSTOMER {CustomerID, FirstName, Address, City, State, Postcode, UserName, Surname,
Password}
Issues Encountered:
While creating databases some major issues can occur and this can affect adversely to the
company’s data, so one should be very careful while handling with databases. Some issues which
may occur are:
A first and most important issue that can occur is due to naming conventions, similar names can
lead to confusion between entities and attributes. Naming conventions allow the user to
understand the data easily and frequently.
Another issue that can be faced is normalization which means partial or transitive dependencies
that may occur in the database should be removed by using a normalization tool.
Further, one can find difficulty in assigning primary and foreign keys, a plethora of confusion
can occur if primary and foreign keys are not assigned properly.
7

Part B:
MySQL Queries Task:
Query 1:
Creation of Table Publisher:
create table Publisher(
PublisherIDvarchar(100) Not Null Primary Key,
PublisherNamevarchar(100) Not Null,
PublisherContactVarchar(100) Not Null,
PublisherPhoneint(11) Not Null);
Screenshot:
Figure 3 Structure of Table Publisher
Creation of Table Book:
create table Book(
PublisherIDvarchar(100) Not Null,
ISBN varchar(100) Not Null,
8

Category varchar(100) Not Null,
Retail float Not Null,
Cost float Not Null,
Title varchar(100) Not Null,
DatePublishedint(11) Not Null,
Primary Key(PublisherID,ISBN));
Screenshot:
Figure 4 Structure of Table Book
Creation of Table Customer:
create table Customer(
CustomerIDvarchar(100) Not Null Primary Key,
FirstNamevarchar(100) Not Null,
SurNamevarchar(100) Not Null,
Address varchar(100) Not Null,
City varchar(100) Not Null,
9

State varchar(100) Not Null,
PostCodeint(20) Not Null,
UserNamevarchar(100) Not Null,
Password varchar(100) Not Null);
Screenshot:
Figure 5 Structure of Table Customer
Creation of Table Orders:
create table Orders(
OrderNovarchar(100) Not Null,
QtyOrderedint(20) Not Null,
DeliveryDate Date Not Null,
ISBN varchar(100) Not Null,
CustomerIDvarchar(100) Not Null,
10

OrderDate Date Not Null,
Primary key(OrderNo,ISBN,CustomerID));
Screenshot:
Figure 6 Structure of Table Orders
Adding Foreign Keys:
 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);
Query 2:
Insertion of 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);
11

Insert into Publisher values('0-47126','Wiley','Pearson Quentin',0733456721);
Screenshot:
Figure 7 Values Insertion of Table Publisher
Insertion of Table Book:
Insert into Book values('0-07212','0-87322-308-X','Sport',73.85,28.45,'Motor Learning &
Performance',2012);
Insert into Book values('0-07212','0-81514-077-0','Sport',94.25,41.65,'Basic
Biomechanics',2011);
Insert into Book values('0-61906','0-61901-525-X','Computer',75.85,31.55,'Active Server
Pages',2015);
Insert into Book values('0-13120','0-13120-117-4','Computer',102.95,41.25,'Introduction to Java
Programming',2011);
Insert into Book values('0-07212','0-07212-741-4','Computer',84.95,29.99,'ASP A Beginners
Guide',2015);
Insert into Book values('0-47126','0-80892-017-X','Medical',105.95,45.65,'Basic
Pathology',2002);
Screenshot:
12