NORMALIZATION AND SQL DDL STATEMENTS - Essay Example

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.