DBAS27198 Database Design And Implementation

Eessaar, (2016), describes normalization as a technique of arranging in database to avoid data update, insertion, deletion anomaly and redundancy. Normalization involves the following steps:

1. Select data origin and convert the data to un-normalized table.
2. Transform un-normalized data to 1NF by removing every repeated fields to new table.
3. Transform data in 1NF to 2NF by removing each non-key attributes that are only reliant on part of the entity.
4. Transform data in 2NF to 3NF by removing new entity which is a non-key primary key.

We are going to match the above rules with our tables to ensure that our database tables are in third-normal-form:

First Normal Form 

In order to convert our entities into first-normal-form Noh, et al (2018), says, we are required to answer this question: are the columns combined make exceptional record at every time and which column is used to uniquely identify the row? To answer this, let’s look at our tables below:

Dinner (Din_ID, Din_Title, Din_Description, Din_Price)

Visitor (Vis_ID,Vis_Name,Vis_Gender,Vis_Address, Vis_Country,Vis_Visit_Date)

Attraction (Attraction_ID, Attraction_Description, Operation_Hours, Admission_Amount, Attraction_Type)

Employee (Emp_ID, Emp_Username, Emp_Password, Emp_Name,Emp_Gender,Emp_Address,

Emp_Country)

Event (Event_ID,Event_Title,Location,Event_Type,Event_Time,Event_Date, Admission_Amount)

Movies (Movie_ID,Movie_Title, Movie_Description,Genre,Year,Director,Language,Show_Time, Rating)

Restaurant (Res_ID, Res_Title, Res_Description,Res_Location,Res_Price_Range,Res_Rating) 

The answer to the first and second part the question is YES. Reason being that, all the columns combined together make a unique row at every moment due to use of primary key.

Second normal form

For any entity to pass 2NF, it has to pass 1NF test and each non-primary key doesn’t functionally depend on PK. Keenly observing our tables above, we notice that each entities has passed 1NF. However, for the second condition, every entity fulfils this rule except for visitor and employee tables.

Employee (Emp_ID, Emp_Username, Emp_Password, Emp_Name, Emp_Gender, Emp_Address, Emp_Country)

Visitor (Vis_ID,Vis_Name,Vis_Gender,Vis_Address, Vis_Country,Vis_Visit_Date)

These two entities have address field which does not specify which kind of address we are talking about. Therefore, we are going to have address entity arranged as below:

Address (Address_ID,Personal_email_address, Compnay_address, Street, City, Zipcode) whereas the other two tables will have a new look as below

Employee (Emp_ID, Emp_Username, Emp_Password, Emp_Name, Emp_Gender, Emp_Country)

Visitor (Vis_ID,Vis_Name,Vis_Gender, Vis_Country,Vis_Visit_Date)

Third Normal Form

A db is said to be in third-normal-form if and only if it satisfies 2NF requirements and doesn’t have any transitive functional dependency. For instance, field A determines field B which determines field C.  Looking at our tables none of them has this characteristic, thus our db is in 3NF. 

Normalized Logical model(See clear drawing in attached Ms.Vis1o)

SQL Data Types

Jukic, Vrbsky, & Nestorov, (2016), insists that every column in a database entity must have a data type and a name. A database developer must make a decision on what type of data will be stored inside every column during the creation of an entity. Letkowski, (2015), seconds that data type is a guide for Structured Query Language to understand what kind of data is to be kept inside every column.

In our database design, we used the following datatypes:

CHAR (size) to hold fixed length string.

VARCHAR (size): to hold varying length string

Decimal (size,d): used to store large number stored as a string.

Date(): used to store date format

Time (): to indicate time of the day.

Year (): used to store year in 2-digit or 4-digit format.

SQL Scripts to Create tables

Address table

CREATE TABLE Address(

Address_ID varchar (4) NOT NULL UNIQUE,

Personal_email_Address varchar (20),

Compnay_address varchar(20),

Street varchar (20),

City varchar(20),

Zipcode varchar(12),

PRIMARY KEY (Address_ID);

Employe Table

CREATE TABLE Employee(

Emp_ID varchar(4) NOT NULL UNIQUE,

Emp_Username varchar (12),

Emp_Password varchar(6),

Emp_Name varchar (30),

Emp_Gender varchar(6),

Emp_Country varchar(20),

PRIMARY KEY (Emp_ID));

Visitor Table

CREATE TABLE Visitor(

Vis_ID varchar(4) NOT NULL UNIQUE,

Vis_Name varchar (20),

Vis_Gender varchar(6),

Vis_Country varchar (20),

Vis_Date Date,

PRIMARY KEY (Vis_ID),

CHECK (Vis_Gender = "Male" or "Female"));

Dinner Table

CREATE TABLE Dinner (

Din_ID varchar(4) NOT NULL UNIQUE,

Din_Title varchar (20),

Din_Description varchar(100),

Din_Price Dec (3,2),

PRIMARY KEY (Din_ID));

CREATE TABLE Attraction (

Attraction_ID varchar(4) NOT NULL UNIQUE,

Attraction_Description varchar (100),

Operation_Hours Time,

Attraction_Type varchar(20),

Admission_Amount Dec(3,2),

PRIMARY KEY (Attraction_ID));

Event Table

CREATE TABLE Event (

Event_ID varchar(4) NOT NULL UNIQUE,

Event_Title varchar (30),

Location varchar(20),

Event_Type varchar(20),

Event_Time Time,

Event_Date Date,

Admission_Amount dec (3,2),

PRIMARY KEY (Event_ID));

Movies Table

CREATE TABLE Movies(

Movie_ID varchar(4) NOT NULL UNIQUE,

Movie_Title varchar(20),

Movie_Description varchar(100),

Genre varchar(50),

Published_Year Year(4),

Director varchar(20),

Language varchar(20),

Rating Dec (2,1),

PRIMARY KEY (Movie_ID));

Restaurant Table

CREATE TABLE Restaurant(

Res_ID varchar(4) NOT NULL UNIQUE,

Res_Title varchar(20),

Res_Description varchar(100),

Res_Location varchar(20),

Res_Price_Range dec(3,2),

Res_Rating dec(2,1),

PRIMARY KEY (Res_ID));

SQL Statements to Insert Into Table

INSERT INTO Restaurant

VALUES ('R001', 'NileValley', 'NileValley bla bla bla', 'Santiago Street', '$78.02-100.00','7.5');

INSERT INTO Movies

VALUES ('M001', 'The Running Dead', 'The running dead is a fictious bla bla bla', 'Fiction', '2014','Liutenantoro', 'English','9.1');

INSERT INTO Event

VALUES ('EV01', 'Twisted Beginning ', 'MaliMA','Art', '9.00AM', '16/8/2018', '$23.90')

INSERT INTO Attraction

VALUES ('AT01', 'Gallery Ma Bla bla bla bla', '8.00Am to 5p.m','Gallery', '$34.03');

INSERT INTO Dinner

VALUES ('D001', 'Salmi', 'Salmi is a meal bla bla bla. It is taken together wiht bla bla','$45.30');

INSERT INTO Visitor

VALUES ('V001', 'Marcos Luis', 'Male','North Korea','12/08/2018');

INSERT INTO Employee

VALUES ('E001', 'Tony','Pasword','Tony Mike','Male','Australia');

INSERT INTO Address

VALUES ('A001', '[email protected]ail.com', '[email protected]','Ti1','Toronto','00200');

SQL statements to select required Values

SELECT * FROM employee;

SELECT * FROM visitor;

SELECT * FROM dinner;

SELECT * FROM Attraction;

SELECT * FROM event;

SELECT * FROM movies;

SELECT * FROM Restaurant;

SELECT * FROM Address; 

References

Armbrust, M., Xin, R. S., Lian, C., Huai, Y., Liu, D., Bradley, J. K., ... & Zaharia, M. (2015, May). Spark sql: Relational data processing in spark. In Proceedings of the 2015 ACM SIGMOD International Conference on Management of Data(pp. 1383-1394). ACM.

Bittorf, M. K. A. B. V., Bobrovytsky, T., Erickson, C. C. A. C. J., Hecht, M. G. D., Kuff, M. J. I. J. L., Leblang, D. K. A., ... & Yoder, M. M. (2015). Impala: A modern, open-source SQL engine for Hadoop. In Proceedings of the 7th Biennial Conference on Innovative Data Systems Research.

Chou, H. M. (2016). F4K Database Schema. Fish4Knowledge: Collecting and Analyzing Massive Coral Reef Fish Video Data, 303.

Coronel, C., & Morris, S. (2016). Database systems: design, implementation, & management. Cengage Learning.

Eessaar, E. (2016). The database normalization theory and the theory of normalized systems: finding a common ground. Baltic Journal of Modern Computing, 4(1), 5.

Jukic, N., Vrbsky, S., & Nestorov, S. (2016). Database systems: Introduction to databases and data warehouses. Prospect Press.

Keith, M., Schincariol, M., & Nardone, M. (2018). Using Queries. In Pro JPA 2 in Java EE 8 (pp. 269-311). Apress, Berkeley, CA.

Kramer, F., & Thalheim, B. (2016). Holistic Conceptual and Logical Database Structure Modeling with ADOxx. In Domain-Specific Conceptual Modeling (pp. 269-290). Springer, Cham.

Letkowski, J. (2015). Doing database design with MySQL. Journal of Technology Research, 6, 1.

Noh, H. N., Bahari, M., & Zakaria, N. H. (2018). A Conceptual Model of Database Normalization Courseware Using Gamification Approach. In PROCEEDINGS OF NEW ACADEMIA LEARNING IN