StudentShare
Contact Us
Sign In / Sign Up for FREE
Search
Go to advanced search...
Free

NORMALIZATION AND SQL DDL STATEMENTS - Essay Example

Cite this document
Summary
The same content is already contained in the Employee table. By removing the Emp_Job_Status table and making the emp_status a simple attribute of the Employee table, 1NF of the database can be ensured i.e. Employee (num,…
Download full paper File format: .doc, available for editing
GRAB THE BEST PAPER93.9% of users find it useful
NORMALIZATION AND SQL DDL STATEMENTS
Read Text Preview

Extract of sample "NORMALIZATION AND SQL DDL STATEMENTS"

Normalization and SQL DDL ments By: First Last 8th December Database from Module 3 The database before normalization is as follows.Figure 1 Database before NormalizationTablesThe six tables forming the database have the following structure:Customer (num, name, address)Employee (num, name, city, state, emp_status*, emp_salary, emp_hiredate)Product (num, name, price)Invoice (num, customer_num*, emp_num*)Order (invoice_num*, product_num*)Emp_Job_Status (emp_status, emp_num*)1NFSome of the database tables (Customer, Product, Invoice) are in their 1NF as the data in each field is atomic and has unique name, there is no data redundancy and each table has a unique primary key.

Following changes are required to bring the remaining database tables to their 1NF.Order – the table has no primary key defined in the database. By making the invoice_num*, product_num* a composite primary key, the table would confirm 1NF i.e. Order (invoice_num*, product_num*)The required SQL statements would be as follows:DROP TABLE orders;CREATE TABLE orders ( invoice_num int NOT NULL, product_num int NOT NULL, PRIMARY KEY (invoice_num,product_num), CONSTRAINT orders_ibfk_1 FOREIGN KEY (product_num) REFERENCES products (product_num) ON DELETE CASCADE, CONSTRAINT orders_ibfk_2 FOREIGN KEY (invoice_num) REFERENCES invoice (invoice_num) ON DELETE CASCADE );Emp_Job_Status – the table has redundant information.

The same content is already contained in the Employee table. By removing the Emp_Job_Status table and making the emp_status a simple attribute of the Employee table, 1NF of the database can be ensured i.e. Employee (num, name, city, state, emp_status, emp_salary, emp_hiredate).The required SQL to make these changes would be:DROP TABLE employees;CREATE TABLE employees ( emp_num int NOT NULL, emp_name varchar(45) DEFAULT NULL, city varchar(45) DEFAULT NULL, state varchar(45) DEFAULT NULL, emp_status varchar(45) DEFAULT NULL, emp_salary varchar(45) DEFAULT NULL, emp_hire_date varchar(45) DEFAULT NULL, PRIMARY KEY (emp_num)) ;DROP TABLE emp_job_status; After 1NF, the database table structure is as follows:Customer (num, name, address)Employee (num, name, city, state, emp_status, emp_salary, emp_hiredate)Product (num, name, price)Invoice (num, customer_num*, emp_num*)Order (invoice_num*, product_num*)2NFThere is only one composite primary key in the database i.e. in the Order table.

And as there are no non-key attributes in the Order table, the table is in 2NF. Thus, since in all the database tables, the non-key attributes are dependent on the complete primary key, the database is in its 2NF. 3NFIn the database tables, Customer, Product, Invoice and Order, there are no transitive dependency between the non-key attributes, so these four tables are in their 3NF.The Employee table does not exist in its 3NF as the state attribute is dependent on the city attribute. To bring the table in its 3NF, the following changes need to be made:Create a new table States, with city as the primary key i.e. States (city, state).

Remove the state attribute from the Employee table, and set city as the foreign key i.e. Employee (num, name, city, state, emp_status, emp_salary, emp_hiredate)The SQL required to make the changes would be:DROP TABLE employees;CREATE TABLE employees ( emp_num int NOT NULL, emp_name varchar(45) DEFAULT NULL, city varchar(45) DEFAULT NULL, emp_status varchar(45) DEFAULT NULL, emp_salary varchar(45) DEFAULT NULL, emp_hire_date varchar(45) DEFAULT NULL, PRIMARY KEY (emp_num), CONSTRAINT fk_city2 FOREIGN KEY (city) REFERENCES states (city)) ;After 3NF, the database table structure is as follows:Customer (num, name, address)States (city, state)Employee (num, name, city, state, emp_status, emp_salary, emp_hiredate)Product (num, name, price)Invoice (num, customer_num*, emp_num*)Order (invoice_num*, product_num*)Figure 2 Database after NormalizationNormalized ERDOracle DDL Script for Normalized DatabaseCREATE TABLE products ( product_num int NOT NULL, product_name varchar(45) DEFAULT NULL, product_price varchar(45) DEFAULT NULL, PRIMARY KEY (product_num));CREATE TABLE states ( city varchar(45) NOT NULL, state varchar(45) NOT NULL, PRIMARY KEY (city));CREATE TABLE customer ( customer_num int NOT NULL, customer_name varchar(45) DEFAULT NULL, customer_address varchar(45) DEFAULT NULL, city varchar(45) DEFAULT NULL, PRIMARY KEY (customer_num), CONSTRAINT fk_city1 FOREIGN KEY (city) REFERENCES states (city) ON DELETE CASCADE);CREATE TABLE employees ( emp_num int NOT NULL, emp_name varchar(45) DEFAULT NULL, city varchar(45) DEFAULT NULL, emp_status varchar(45) DEFAULT NULL, emp_salary varchar(45) DEFAULT NULL, emp_hire_date varchar(45) DEFAULT NULL, PRIMARY KEY (emp_num), CONSTRAINT fk_city2 FOREIGN KEY (city) REFERENCES states (city)) ;CREATE TABLE invoice ( invoice_num int NOT NULL, inv_customer_num int DEFAULT NULL, inv_emp_num int DEFAULT NULL, PRIMARY KEY (invoice_num), CONSTRAINT FK_invoice FOREIGN KEY (inv_emp_num) REFERENCES employees (emp_num) ON DELETE CASCADE, CONSTRAINT FK_invoice1 FOREIGN KEY (inv_customer_num) REFERENCES customer (customer_num) ON DELETE CASCADE);CREATE TABLE orders ( invoice_num int NOT NULL, product_num int NOT NULL, PRIMARY KEY (invoice_num,product_num), CONSTRAINT orders_ibfk_1 FOREIGN KEY (product_num) REFERENCES products (product_num) ON DELETE CASCADE, CONSTRAINT orders_ibfk_2 FOREIGN KEY (invoice_num) REFERENCES invoice (invoice_num) ON DELETE CASCADE ); Figure 3 Database in OracleImportance of ACID in Databases:The ACID model is a theoretical transactional model for database systems that puts forward the four goals (Atomicity, Consistency, Isolation and Durability) achieving which is essential for a database system to become a reliable system.

Atomicity ensures that no incomplete transactions are ever executed by the system which ensures the state of data always remains stable despite any hardware/software failures. Consistency ensures that only those transactions will execute that do not violate any of the database consistency rules which ensures data remains valid. Isolation ensures uninterrupted single transaction processing e.g. the concurrent transaction requests are handled serially i.e. one transaction at a time. Durability ensures that transactions are logged and therefore transactions can be rolled back at any time.

The Need for Database NormalizationDatabase normalization is a set of guidelines that helps in organizing data efficiently which improves the overall performance of a DBMS. It helps remove data redundancy (does not allow duplicate data storage in multiple tables) which in turn reduces the space consumed by database. Furthermore, it improves data consistency by allowing only sensible and logical data dependencies between tables (e.g. a table stores only data that is related). There are five normal forms of database; 1NF, 2NF, 3NF, BCNF and 4NF.

Mostly, normalization till the third form is enough for most database system applications. 1NF helps eliminate duplicate column from a table and identifies unique column(s) i.e. through primary key. 2NF separates data subsets into separate tables forming foreign keys. And 3NF ensures all columns in a table are dependent only on the primary key.Lessons Learnt from ExerciseThe exercise helped in understanding the concept of ACID model and offered a hands-on approach to designing and optimizing a database design.

It helped in practicing the application of the most widely used normalization guidelines on a previously formed database model. Furthermore, it also helped practice the DDL statements involved in the actual creation of a database application in Oracle.

Read More
Cite this document
  • APA
  • MLA
  • CHICAGO
(“NORMALIZATION AND SQL DDL STATEMENTS Essay Example | Topics and Well Written Essays - 500 words”, n.d.)
NORMALIZATION AND SQL DDL STATEMENTS Essay Example | Topics and Well Written Essays - 500 words. Retrieved from https://studentshare.org/information-technology/1670710-normalization-and-sql-ddl-statements
(NORMALIZATION AND SQL DDL STATEMENTS Essay Example | Topics and Well Written Essays - 500 Words)
NORMALIZATION AND SQL DDL STATEMENTS Essay Example | Topics and Well Written Essays - 500 Words. https://studentshare.org/information-technology/1670710-normalization-and-sql-ddl-statements.
“NORMALIZATION AND SQL DDL STATEMENTS Essay Example | Topics and Well Written Essays - 500 Words”, n.d. https://studentshare.org/information-technology/1670710-normalization-and-sql-ddl-statements.
  • Cited: 0 times

CHECK THESE SAMPLES OF NORMALIZATION AND SQL DDL STATEMENTS

My Statement to the University of California

 The writer of this personal statement analyses a talent, experience, contribution or personal quality he will bring to the University of California.... Aside from this, He also has the leadership qualities which he can bring to the University.... In his country, he is the vice president of the faculty of youth....
1 Pages (250 words) Personal Statement

Personal statement - economics and finance studies

I want to get admission in master degree program in order to gain complete understanding of all issues related to the field of economics.... Economics is my favorite subject and I think this is a right… time for me to go further in my desired field as I have not only completed my graduation but also I am fully determined to establish my career as an expert economist....
1 Pages (250 words) Personal Statement

The Decision to Pursue a Masters in Computer Science

The very first factor is my innate affinity towards computer science that has driven me ahead throughout my life.... I was very… I think my father too played a role in developing this interest in me.... He is a diploma holder in geographical information systems, and he used to take me to his workplace....
2 Pages (500 words) Personal Statement

Purpose for Undertaking and Continuing Graduate Study

This is true in every sense and especially in terms of literacy levels.... More than 80% of people in the world today are educated and especially in the United States.... A bachelor's degree has become very common… This is my sole reason for wanting to continue with my graduate study.... I want to have an added advantage, over the rest in the professional field. ...
2 Pages (500 words) Personal Statement

Personal Statment

Perfect Personal statements.... This realization came as a shock since I love doing sports, as well as physical education.... I came to the realization of my profound desire to study business after selling a lot of my… I had bought items rather cheaply from an offline market and sold them through an online platform thereby attaining a lucrative margin....
2 Pages (500 words) Personal Statement

Why I Want to Study at Santa Monica College

It is known to have lots of fields of study and has very famous alumni.... Therefore, joining Santa Monica College will be a great opportunity since it is known to have excellent and… Hence, one is guaranteed to leave after having cleared the required duration, a better person with a higher level of education than they came in. ...
2 Pages (500 words) Personal Statement

Software as a Service Methodology

I have same level of expertise in C/ C++, PHP, sql and Python programming language.... sql injection and XSS are web penetration testing (pentest) which is helpful in attacking the computer system (ethical hacking) to gain information about the weakness of the security....
2 Pages (500 words) Personal Statement

Student from Saudi Arabia Taking Master of Public Policy and Administration in the United State

I am on a scholarship from Saudi Arabia to take my PhD in the United States.... I am expecting to graduate this coming May 2013, with a Master's of Public… Policy and Administration degree from California Lutheran university (CLU).... uring my studies, I have acquired extensive knowledge and experience both in confinement of the classroom and in practice....
2 Pages (500 words) Personal Statement
sponsored ads
We use cookies to create the best experience for you. Keep on browsing if you are OK with that, or find out how to manage cookies.
Contact Us