Free

NORMALIZATION AND SQL DDL STATEMENTS - Essay Example

Comments (0) 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 paperFile format: .doc, available for editing
GRAB THE BEST PAPER93.2% of users find it useful
NORMALIZATION AND SQL DDL STATEMENTS
Read TextPreview

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 Normalization
Tables
The 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*)
1NF
Some 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*)
2NF
There 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.
3NF
In 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 Normalization
Normalized ERD
Oracle DDL Script for Normalized Database
CREATE 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 Oracle
Importance 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 Normalization
Database 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 Exercise
The 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
Comments (0)
Click to create a comment or rate a document

CHECK THESE SAMPLES OF NORMALIZATION AND SQL DDL STATEMENTS

SQL Training Course Experience

...offered us easy-to-understand SQL statements and instructions; however it allowed us to complete practical assignments of what we learned from SQL interpreter. We have received instant outcomes following submitting our SQL commands. This course has taught us how to create tables and apply the commands of insert, select, delete, update and drop into the tables. This SQL course at present supports a division of ANSI SQL. The fundamentals of every SQL command will be enclosed in this course. In this way we have gained a deep and comprehensive overview of all the knowledge areas and expertise. This course has made us believe...
4 Pages(1000 words)Research Paper

SQL Queries

...a. List the s of the who commenced a placement on exactly the same day as at least one other SELECT DISTINCT stu_f FROM Student, Placement WHERE (Student.stu_no=Placement.stu_no) And ((SELECT count( A.stu_no) FROM Placement A WHERE Placement.stu_no <> A.stu_no and Placement.pl_fdate = A.pl_fdate) ) > 0; Description In this query a sub query has been written in the WHERE clause to filter existence of any other student the on same day. b. List the names of all students who attended an event that was attended by a representative of 'Perception'. SELECT DISTINCT Student.stu_fname FROM Student, Event, Company, Representative, Student_attendance, Rep_attendance WHERE Company.co_id=Representative.co_id... List the s of the who...
3 Pages(750 words)Essay

Normalization

...Normalization of group homes entails more than the visual appearance. Non visual features such as poor acoustics can be just as important.There may be a relationship between poor acoustics and resident comfort and behavioral outbursts. Several group homes in Western New York were studied to determine the extent of poor acoustic conditions and their causes. Size and material choices, in conjunction with the use of space contributed to poor acoustic conditions. Definition Normalization involves the acceptance of people with disabilities, with their disabilities, offering them the same conditions as are offered to other citizens. It involves an awareness of the normal rhythm of life -...
8 Pages(2000 words)Essay

Data Analysis by SQL

...decisions. SQL Statements Consumer Behaviour: The following view is initially created: Create View vw_Orders as select * from orderdetails left outer join orders on orders.orderID=orderdetails.orederID go 1. Monetary Value of Purchases: select CustomerID, (select [Company Name] from Customers where Customers.CustomerID=vw_Orders.CustomerID) as [Company Name], (select Country from Customers where Customers.CustomerID=vw_Orders.CustomerID) as Country, sum([unit price]*quantity) as Amount from vw_Orders group by CustomerID order by Amount desc 2. Frequency of Purchase: select CustomerID, (select [Company Name] from Customers where Customers.CustomerID=orders.CustomerID) as...
7 Pages(1750 words)Essay

Ch07_AviaCo database. SQL

...1. Write the SQL that will list the values for the first four attributes in the CHARTER table. SELECT CHAR_TRIP, CHAR_ AC_NUMBER, CHAR_DESTINATION FROM CHARTER Output: 2. Using the contents of the CHARTER table, write the SQL query that will produce the output shown in Figure P7.2. Note that the output is limited to selected attributes for aircraft number 2778V. SELECT CHAR_DATE, AC_NUMBER, CHAR_DESTINATION, CHAR_DISTANCE, CHAR_HOURS_FLOWN FROM CHARTER WHERE AC_NUMBER = 2778V Output: 3. Create the SQL query that will produce a list of customers who have an unpaid balance. The required output is shown in Figure P7.11. Note that the balances are listed in descending order. SELECT CUS_LNAME,...
2 Pages(500 words)Assignment

SQL&Security

...SQL and Security Answers to the assignment SQL Injection is a form of database attack where the attacker attempts to insert amalicious code into non validated input. The attacker tries to get the database to dump its contents. A sample is provided below: Var TrainCity; Traincity – Request from (‘TrainCity’); Var sql = “select * from OrderTable where TrainCity = ‘”+ TrainCity +”’”’ If the user requests the city table, the query will drop all the data in the city table. The technique used here is that the attacker will pose the query to the database about the table TrainCity. When the database responds to the query it will drop the table with the entries. This table will be dropped to the attacker, and he will have access... to the...
2 Pages(500 words)Assignment

SQL/PL Database project

...Yours PL/SQL Project Row Type: The %ROWTYPE is used to specify the field of data types taken from columns of the views or tables. The use of the %Row Type is illustrated in the following code (McLaughlin and Harper 34). For example, Triggers: Triggers in PL/SQL are the special stored programs which are triggered by the use of events for the given program. Triggers run between the issuance of a command and database management action is performed. There are five types of the triggers which belong to Oracle database 11g. Triggers are also fired in the response to the event of database. In the PL/SQL the trigger is a thing that appears in the normal block (McLaughlin and...
1 Pages(250 words)Essay

ACID and Normalization

...of duplicated data in the database. Additionally, there is ordering of data into logical groupings in such a way that each and every group describes a small part of the complete data. Thirdly, there is the building of a database in which manipulation and access to data is rapid and proficient, while still maintaining the integrity of the stored data. Lastly, the data is organized such that one can edit and make changes in one part of the data without affecting other parts. In this exercise, normalization of the database up to the third normal form is achieved. In addition, there is an ERD (entity-relation diagram) and an Oracle database with execution of SQL statements...
3 Pages(750 words)Assignment

SQL Injection Vulnerabilities

...in the request of HTTP that made the database stop for a particular time period. Through the comparison of the times of response between the variously timed injected requests and the normal requests, the tool can establish whether or not the implementation of the SQL statement was successful. The Error based SQL injection entails the supply of invalid SQL statements to the affected areas within the request of HTTP. Monitoring of the HTTP responses is then effected by the tool for the known error messages that have come from the database server. There are two tools that are commonly used in the testing SQL injection;...
5 Pages(1250 words)Research Paper

Web Based Library System Management with Business Intelligence

...database is that it reduces redundancy of data which saves system memory and user time. It makes it very easy to secure and share the data and after the analysis of data it becomes very simple to classify the flow of the information. Database provide security, integrity and easy for application development 4.3.1 Library Database: The relational library database can very easily be modulated into three parts. First we have worked over the conceptual design of the data base which includes its analysis for requirements and inputs and UML. After the UML has been designed these tables are being normalized applying the 1NF, 2NF and 3NF42. The relationship between various tables is then clarified and hence application of...
45 Pages(11250 words)Research Paper
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.

Let us find you another Essay on topic NORMALIZATION AND SQL DDL STATEMENTS for FREE!

Contact Us