SIT772 Database Design and Implementation Report - Deakin University

This report presents a comprehensive solution to a database design and implementation assignment for the SIT772 course at Deakin University. The solution encompasses several exercises, beginning with the creation of database schemas for companies, products, customers, orders, and order details. SQL statements for table creation are provided. Subsequent exercises involve creating tables, inserting data, and executing various SQL queries, including joins and WHERE clauses with different conditions. The report further addresses database normalization, explaining transitive and partial dependencies, and providing a relational database design. Finally, the solution explores normalization forms (1NF, 2NF, and 3NF) and provides SQL scripts for creating tables based on these normalized forms, including primary and foreign key constraints.

Solution 1
Exercise 1
Below are the elements –
1. Table Name :Company
Schema: Company (code,name,address,countryCode,telephoneNumber)
Constraint: Code is primary key
2. Table Name :Product
Schema: Product (productNumber,name,price,category,description,code)
Constraint:
productNumber is primary key
Code is foreign key
3. Table Name:Customer
Schema :Customer(customerID,name,address,phoneNumber)
Constraint :
customerID primary key
4. Table Name :Order
Schema :Order(OrderID,customerID,date,time)
Constraint :
OrderID is primary key
CustomerId is foreign key
5. Table Name :OrderDetails
Schema :OrderDetails (OrderID,productNumber)

Constraint :
OrderID,productNumber is foreign key.
Exercise 2
Exercise 3
SQL statement to create table:
CREATE TABLE COMPANY (
CODE INT PRIMARY KEY
,NAME VARCHAR(100) NOT NULL
,ADDRESS VARCHAR(100) NOT NULL
,TELEPHONENUMBER VARCHAR(10)
);
CREATE TABLE PRODUT (
PRODUCTNUMBER INT PRIMARY KEY
,NAME VARCHAR(100) NOT NULL
,PRICE DECIMAL(8, 2)

,CATEGORY VARCHAR(10)
,DESCRIPTION VARCHAR(100) NOT NULL
,CODE INT
,FOREIGN KEY (CODE) REFERENCES COMPANY(CODE)
);
CREATE TABLE CUSTOMER (
CUSTOMERID INT PRIMARY KEY
,NAME VARCHAR(100) NOT NULL
,ADDRESS VARCHAR(100) NOT NULL
,PHONENUMBER VARCHAR(10)
);
CREATE TABLE `ORDER` (
ORDERID INT PRIMARY KEY
,CUSTOMERID INT
,DATE DATE
,TIME TIME
,FOREIGN KEY (CUSTOMERID) REFERENCES CUSTOMER(CUSTOMERID)
);
CREATE TABLE ORDERDETAILS (
ORDERID INT
,PRODUCTNUMBER INT
,PRIMARY KEY (
ORDERID
,PRODUCTNUMBER
)
,FOREIGN KEY (ORDERID) REFERENCES `ORDER`(ORDERID)
,FOREIGN KEY (PRODUCTNUMBER) REFERENCES PRODUCT(PRODUCTNUMBER)
);
Solution 2
Exercise 1
CREATE TABLE Customer (
ID INT
,Customer_FName VARCHAR(255)
,Customer_LName VARCHAR(255)
);
INSERT INTO Customer (
ID
,Customer_FName
,Customer_LName
)
VALUES (
'10001'
,'John'
,'Smith'
);
CREATE TABLE Buying (
Customer_ID INT
,Product_ID INT

,Order_Time DATE
);
INSERT INTO Customer (
Customer_ID
,Product_ID
,Order_Time
)
VALUES (
'10001'
,'772'
,'2016/09/01'
);
CREATE TABLE Product (
ID INT
,Name VARCHAR(255)
,Address VARCHAR(255)
);
INSERT INTO Customer (
ID
,Name
,Address
)
VALUES (
'772'
,'Telephone'
,'22 Ave, Burwood'
);
Exercise 2
SELECT *
FROM Customer
INNER JOIN Buying ON Buying.Customer_ID = Customer.ID
WHERE extract(year FROM DATE Buying.Order_Time) <= 2017;
Exercise 3
SELECT *
FROM Customer
INNER JOIN Buying ON Buying.Customer_ID = Customer.ID
WHERE Customer.Customer_FName LIKE '%o%'
OR Customer.Customer_LName LIKE '%o%'
Exercise 4
SELECT *
FROM Customer
INNER JOIN Buying ON Buying.Customer_ID = Customer.ID
INNER JOIN Product ON Buying.Product_ID = Product.ID
WHERE Product.Address LIKE '%Burwood%'
Exercise 5
SELECT *
FROM Customer

INNER JOIN Buying ON Buying.Customer_ID = Customer.ID
INNER JOIN Product ON Buying.Product_ID = Product.ID
WHERE Name = 'Telephone'
OR Product.Address LIKE '%Geelong%';
Solution 3
Exercise 1
Transitive dependencies are as follows –
{ord_no}  {Name}
So, we will split the tables having customer details and order details
Exercise 2
Partial dependencies are –
Company depends only on Prod_ID
Description depends only on Prod_ID
Order date depends only on order_no
Exercise 3
Relational db is as follows –
CUST_I
D NAME
C001 GOLD
C009 BLUE
C075 RED
ORD_N
O
ORD_DAT
E
CUST_I
D
81 15-Apr C001
99 16-Apr C075
56 16-Apr C009
88 17-Apr C001
PRD_ID DESC COMPANY ORD_N PROD_I QUANTIT

O D Y
P005 CHISEL A 81 P005 6
P004 PLANE A 81 P004 14
P015 SAW B 99 P015 3
P033 PUNCH C 56 P033 24
56 P004 9
88 P015 10
88 P005 2
Exercise 4
Solution 4
Exercise 1
Without any Normalization
customer [ customer_no, customer_name, customer_address, customer_phone, ( artist_id,
artist_name,artist_title, purchase_date, price) ]
First Normal Form(1NF)

customer [ customer_no, customer_name, customer_address, customer_phone]
customer_art [ customer_no, art_code, purchase_date, artist_id, artist_name, artist_title, price ]
Please note that the key selected for the repeating group is the piece of art itself than once, the
purchase date was added as part of the key to make the rows unique.
Second Normal Form(2NF)
customer [ customer_no, customer_name, customer_address, customer_phone]
customer_art [ customer_no, art_code, purchase_date, price ]
art [ art_code, artist_title, artist_id, artist_name ]
Third Normal Form(3NF)
customer [ customer_no, customer_name, customer_street, customer_city, customer_prov,
cust_postalcode, customer_phone]
customer_art [ customer_no, art_code, purchase_date, price ]
art [ art_code, artist_title, artist_id(FK) ]
artist [ artist_id, artist_fname, artist_lname ]
Exercise 2
CREATE TABLE customer (
[customer_no] INT NOT NULL identity
,[customer_name] NVARCHAR(255)
,[customer_street] NVARCHAR(255)
,[customer_city] NVARCHAR(255)
,[customer_prov] NVARCHAR(255)
,[cust_postalcode] NUMERIC(9, 2)
,[customer_phone] NUMERIC(9, 2)
,PRIMARY KEY ([customer_no])
);
CREATE TABLE customer_art (
[customer_no] INT NOT NULL identity
,[art_code] NUMERIC(9, 2)
,[purchase_date] DATE
,[price] NUMERIC(9, 2)
,PRIMARY KEY ([customer_no])
);

CREATE TABLE art (
[art_code] INT NOT NULL identity
,[artist_title] NVARCHAR(255)
,[artist_id] NUMERIC(9, 2)
,PRIMARY KEY ([art_code])
);
CREATE TABLE artist (
[artist_id] INT NOT NULL identity
,[artist_fname] NVARCHAR(255)
,[artist_lname] NVARCHAR(255)
,PRIMARY KEY ([artist_id])
);