SIT772 - Database Design and Implementation Report - Deakin University

This report details the database design and implementation for the SIT772 course, focusing on the Saffron Gallery, student records, and pet data. It includes the creation of database entities such as Work, Artist, and Visitors, along with their attributes and relationships (one-to-many and many-to-many). The report provides SQL scripts for creating tables, inserting data, and executing queries to retrieve specific information. It covers normalization techniques to 3NF, and demonstrates the application of SQL for data retrieval based on various criteria, such as course locations and student names. The report also includes database schemas, dependency diagrams, and normalized table structures to reduce redundancy and improve data integrity, and also includes references.

DATABASE
SYSTEM
pg. 1

Question 1
Exercise 1
The database to be implemented at the Saffron Gallery has various entities and attributes. The
entities have the various relations between each other. The entities in the database will include
Work, Artist, and Visitors. These entities have attributes that describe that describe them fully in
order to be identified.
Work is one of the entity of the database it has a barcode, title, and description as attributes. The
barcode is the primary key of the entity it uniquely identifies any description that is given in this
entity. The title is another property of this entity that describes the type of the job in this case
the.It is of datatype text [1]. The title, in this case, can include painting, sculptures, and e.t.c. The
description is of datatype text gives a clear definition of the entity.
Artist is the second entity in the database its attributes include name, address, and contact. The
name is of datatype text.Address attribute describes the location of the artist and is of datatype
varchar. Contact attribute is of datatype integer they give details on how the artist can be
reached.
Visitors entity contains attributes such name, address, phone and the barcode id that is given to
them. Name attribute is of datatype text, the address is of datatype varchar while phone number
is of data type integer.
Relationship
The entities, in this case, have different types of relationship.
The relationship between artist and work is one too many, meaning that one artist may be
involved in working in different categories of jobs.
The relationship between visitors and work is many to many.Meaning that different visitors may
visit various categories of work at any given point.
pg. 2

Exercise 2
The diagram shows a clear relationship between the entities
Exercise 3
Artist
The above table was created using the below script.
CREATE TABLE IF NOT EXISTS `Artist` (
`Name` TEXT(255) NOT NULL,
`Address` VARCHAR NOT NULL,
`Contact` INTEGER (10)NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
pg. 3

Work
The above table was created using the following script.
CREATE TABLE IF NOT EXISTS `Work` (
` Barcode ` varchar(20) NOT NULL,
`Description` Varchar NOT NULL,
`Title` text NOT NULL,
PRIMARY KEY (`Barcode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Visitors
The above table was created using the following script.
CREATE TABLE IF NOT EXISTS `visitors` (
` Barcode_id ` Integer(20) NOT NULL,
`Name` TEXT(255) NOT NULL,
`Address` VARCHAR(255) NOT NULL,
`Phone`INT(10) NOT NULL,
PRIMARY KEY (`Barcode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
pg. 4

Question 2
Exercise 1
Student 1 [2]
CREATE TABLE IF NOT EXISTS `student_1` (
`student_ID` int(5) NOT NULL,
`student_Fname` text NOT NULL,
`student_LName` text NOT NULL,
PRIMARY KEY (`student_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `student_1` (`student_ID`, `student_Fname`, `student_LName`) VALUES
(10001, 'John', 'Smith'),(10002, 'Dave', 'Franklin'),(10003, 'Febby', 'Johns'),(10004, 'Mary',
'Gibson'),(10005, 'Glory', 'Anson');
pg. 5

Student 2
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
CREATE TABLE IF NOT EXISTS `student_2` (
`student_ID` int(5) NOT NULL,
`Course_ID` varchar(10) NOT NULL,
`Year_joined` date NOT NULL,
PRIMARY KEY (`student_ID`),
UNIQUE KEY `Course_ID` (`Course_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `student_2` (`student_ID`, `Course_ID`, `Year_joined`) VALUES
(10001, 'SIT772', '2018-12-05'),
pg. 6

(10002, 'SIT774', '2015-09-16'),
(10003, 'SIT775', '2017-06-21'),
(10004, 'S1T712', '2016-09-14'),
(10005, 'SIT712', '2017-08-16');
Course
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
CREATE TABLE IF NOT EXISTS `course` (
`Course_ID` varchar(20) NOT NULL,
`Course` text NOT NULL,
`Location` text NOT NULL,
PRIMARY KEY (`Course_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
pg. 7

-- Dumping data for table `course`
INSERT INTO `course` (`Course_ID`, `Course`, `Location`) VALUES
('SIT712', 'Project Management', 'Burwood'),
('SIT772', 'Database', 'Burwood'),
('SIT774', 'IT security', 'Burwood'),
('SIT775', 'Software Development', 'Geelong');
Exercise 2
SELECT * FROM student_2 WHERE Course_ID IS NOT NULL
AND Year_joined >=2016
pg. 8

Exercise 3
SELECT * FROM student_1 WHERE student_Fname LIKE '%a%'
OR student_LName LIKE '%a%'
pg. 9

Exercise 4
SELECT student_2. * , Course.Location FROM student_2
JOIN course ON student_2.Course_ID = Course.Course_ID
WHERE course.Location = 'Burwood';
pg. 10

Exercise 5
SELECT student_2. * , Course.Course FROM student_2
JOIN course ON student_2.Course_ID = Course.Course_ID
WHERE course.Course = 'Database';
pg. 11

Question 3
Exercise 1
Relation schema.
The above is the structure of the table.The columns of the table include CUS_ID,
name,Ord_NO,Ord_Date,Proc_ID,Description,Qty_ord.
The dependency diagram of the database is as shown above.
pg. 12

Exercise 2
The above is the normalization of the table. It is divided into different types of table’s e.g
customer table, selling table, product table, and order details
pg. 13

Exercise 3
Each of the above tables is normalized into 3NF normal form in order to reduce
redundancy.
pg. 14

Exercise 4
pg. 15

Question 4
Exercise 1
The above is how the table is normalized into different tables. It was normalized as shown above
in order to avoid anomalies.
Exercise 2
Pet table
pg. 16

CREATE TABLE IF NOT EXISTS `pet` (
`PET_ID` INT(3) NOT NULL,
`PET_NAME` TEXT(255) NOT NULL,
`PET_TYPE` TEXT (30)NOT NULL,
`OWNER` TEXT (30)NOT NULL,
`PROD_ID` TEXT (30)NOT NULL,
PRIMARY KEY (`PET_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- Dumping data for table `course`
Procedure table
CREATE TABLE IF NOT EXISTS `procudure` (
`Proc_id` INT(3) NOT NULL,
`Procedure` TEXT(255) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- Dumping data for table `course`
Owner table
CREATE TABLE IF NOT EXISTS `owner` (
`Proc_id` TEXT(255) NOT NULL,
`Visit_date` TEXT(255) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- Dumping data for table `course`
pg. 17

References
[1] O'flaherty, K.W., Stellwagen Jr, R.G., Walter, T.A., Watts, R.M., Ramsey, D.A., Veldhuisen,
A.W., Ozden, R.K. and Dempster, P.B., NCR Corp. System and method for managing data
privacy in a database management system. U.S. Patent 6,275,824, 2010.
[2] Nadkarni, U.P.. Skills database management system and method. U.S. Patent 6,266,659,
2011.
pg. 18