Database Management System

This document contains study material and solved assignments for Database Management System. It includes topics such as ERD, SQL code, dependency diagrams, and more.

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)

 Product (Prod_ID, description)
 Order_Product (Order_NO, Prod_ID,Qty_ord)
Exercise 4: ERD

Q4
Exer 1
Tables in 3NF
 Owner (OwnerID, name)
PK (ownerID)
 Procedure (Proc_ID, prodcedure)
PK (Proc_ID)
 Pet (Pet_ID, Pet_Name, Pet, Type, Pet_Age, OwnerID)
PK (Pet_ID)

FK (ownerID) references owner (ownerID)
 Visit (VisitID,Visit_Date,Proc_ID)
PK (visitID)
FK (Proc_ID) references Procedure (Proc_ID)
Exer 2: SQL Code
create table owner (
ownerID integer primary key,
name varchar(50) not null
);
create table pet_procedure (
proc_ID integer primary key,
procedure_name varchar(50) not null
);
create table pet (
pet_ID integer primary key,
pet_Name varchar(50) not null,
pet_age integer not null,
ownerID integer not null,
foreign key (ownerID) references owner (ownerID)
);
create table visit (
visitID integer primary key,
visit_date date not null,
proc_ID integer not null,
foreign key (proc_ID) references pet_procedure (proc_ID)

);