ProductsLogo
LogoStudy Documents
LogoAI Grader
LogoAI Answer
LogoAI Code Checker
LogoPlagiarism Checker
LogoAI Paraphraser
LogoAI Quiz
LogoAI Detector
PricingBlogAbout Us
logo

Database System: Entities, Attributes, Relationships, and Normalization

Verified

Added on  2023/05/27

|19
|1425
|294
AI Summary
This text explains the concepts of entities, attributes, relationships, and normalization in database systems. It provides examples of tables and SQL queries and emphasizes the importance of avoiding anomalies and reducing redundancy. The text also covers the implementation of a database for an art gallery and a university.

Contribute Materials

Your contribution can guide someone’s learning journey. Share your documents today.
Document Page

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
DATABASE
SYSTEM
pg. 1
Document Page
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
Document Page
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

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
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
Document Page
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
Document Page
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

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
(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
Document Page
-- 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
Document Page
Exercise 3
SELECT * FROM student_1 WHERE student_Fname LIKE '%a%'
OR student_LName LIKE '%a%'
pg. 9

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
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
Document Page
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
Document Page
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

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
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
Document Page
Exercise 3
Each of the above tables is normalized into 3NF normal form in order to reduce
redundancy.
pg. 14
Document Page
Exercise 4
pg. 15

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
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
Document Page
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
Document Page
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
1 out of 19
[object Object]

Your All-in-One AI-Powered Toolkit for Academic Success.

Available 24*7 on WhatsApp / Email

[object Object]