SIT772 Database & Information Retrieval - SQL, ERD and Normalization

This assignment solution covers fundamental database concepts, including data modeling and SQL implementation. It presents solutions to various exercises involving creating database schemas, writing SQL queries for data retrieval and manipulation, and performing database normalization to achieve 3NF. The solution includes creating tables, inserting data, and writing SELECT statements with different conditions. Additionally, the assignment demonstrates the creation of Entity-Relationship Diagrams (ERD) and the process of removing partial and transitive dependencies to normalize database tables. The document provides SQL code for table creation and data manipulation, offering a comprehensive overview of database design and query implementation. Desklib provides a platform where students can find solved assignments and past papers.

COVER PAGE

Contents
Q1................................................................................................................................................................3
Exer 1:......................................................................................................................................................3
Exer 2:......................................................................................................................................................3
Exer 3:......................................................................................................................................................5
Q2................................................................................................................................................................6
Exer 1..................................................................................................................................................6
Exer 2.......................................................................................................................................................8
Exer 3.......................................................................................................................................................9
Exer 4.....................................................................................................................................................10
Exer 5.....................................................................................................................................................11
Question 3.................................................................................................................................................12
Exer 1: Dependency diagram.................................................................................................................12
Exer 2: removing partial dependencies.................................................................................................13
Exer 3: removing transitive dependency...............................................................................................13
Exercise 4: ERD......................................................................................................................................14
Q4..............................................................................................................................................................15
Exer 1.....................................................................................................................................................15
Exer 2: SQL Code....................................................................................................................................15

Q1
Exer 1:
Product (productNumber, name, price, category, description, organizationCode)
PK (productNumber)
FK (organisationCode) references company (organizationCode)
Company (organizationCode, name, address, tel)
PK (organizationCode)
Customer (customerID, name, address, phoneNumber)
PK (customerID)
Purchase (PurchaseID, date, customerID)
PK (purchaseID)
FK (customerID) references customer (CustomerID)
Purchase_line (purchaseID, productID)
PK (purchaseID, productID, quantity)
FK (purchaseID) references Purchase (purchaseID)
FK (productID) references product (productID).
Exer 2:
ERD

Exer 3:
Write SQL code to create the tables;
create table company (
organizationCode char(10) primary key,
name varchar(50) not null,
address varchar(100) not null,
tel varchar(25) not null
);
create table product (
productNumber integer primary key,
name varchar(50) not null,
price decimal not null,
category varchar(50) not null,
description varchar(500) not null,
organizationCode char(10) not null,
foreign key (organizationCode) references company
(organizationCode)
);
creat table customer (
customerID integer primary key,
name varchar(50) not null,
address varchar(100) not null,
phoneNumber varchar(25) not null
);
create table purchase (
purchaseID integer primary key,
pdate datetime not null,
customerID integer not null,

foreign key (customerID) references customer (customerID)
);
create table purchase_line (
purchaseID integer not null,
productNumber integer not null,
quantity not null,
primary key (purchaseID,productNumber),
foreign key (purchaseID) references purchase (purchaseID),
foreign key (productNumber) references product (productNumber)
);
Q2
Exer 1
SQL code
create table course (
course_ID char(6) primary key,
course varchar(50) not null,
location varchar(50) not null
);
create table student(
student_ID integer primary key,
student_FName varchar(50) not null,
student_Lname varchar(50) not null
);
create table student_course(
student_ID integer not null,

course_ID char(6) not null,
year_joined integer(4) not null,
primary key (student_ID,course_ID),
foreign key (student_ID) references student (student_ID),
foreign key (course_ID) references course (course_ID)
);
insert into student values
(10001,'John','Smith'),
(10002,'Dave','Franklin'),
(10003,'Febby','Johns'),
(10004,'Mary','Gibson'),
(10005,'Glory','Anson');
insert into course values
('SIT772','Database','Burwood'),
('SIT774','IT security','Burwood'),
('SIT775','Software Development','Geelong'),
('SIT712','Project management','Burwood');
insert into student_course values
(10001,'SIT772',2016),
(10002,'SIT774',2015),
(10003,'SIT775',2017),
(10004,'SIT712',2016),
(10005,'SIT772',2019);

Exer 2
SELECT
student.student_ID,
student_FName,
student_Lname,
year_joined
FROM
student,
student_course
WHERE
year_joined <= 2017
AND student_course.student_id = student.student_id;

Exer 3
SELECT
student_ID,
student_FName,
student_Lname
FROM
student
WHERE
student_Fname LIKE '%o%'
OR student_Lname LIKE '%o%';
Exer 4

SELECT
student.student_ID,
student_FName,
student_Lname,
course.course,
course.location
FROM
student,
student_course,
course
WHERE
course.location = 'Geelong'
AND student_course.student_id = student.student_id
AND course.course_id = student_course.course_id;

Exer 5
FROM
student
INNER JOIN
student_course ON student_course.student_id = student.student_id
INNER JOIN
course ON course.course_id = student_course.course_id
WHERE
course.location = 'Geelong'
OR course.course = 'Database';

Question 3
Exer 1: Dependency diagram
Exer 2: removing partial dependencies
Exer 3: removing transitive dependency
 Customer (Cus_ID, Name)
 Order (Order_No, Order_Date, Cus_ID)