Entity relationship diagram and Normalization of Airline Reservation System - a good example

E-R DIAGRAM : E-R Diagram constitute a technique for representing the logical structure of a database in a pictorial manner. This analysis is then used to organize data as a relation, normalising relation and finally obtaining a relation database. Entities : Which specify distinct real-world items in an application. Properties (or): Which specify properties of an entity and relationships. Relationships : Which connect entities and represent meaningful dependencies between them. NORMALIZATION : The basic objective of normalization is to be reduce redundancy which means that information is to be stored only once. Storing information several times leads to wastage of storage space and increase in the total size of the data stored. If a Database is not properly designed it can gives rise to modification anomalies. Modification anomalies arise when data is added to, changed or deleted from a database table. Similarly, in traditional databases as well as improperly designed relational databases, data redundancy can be a problem. These can be eliminated by normalizing a database. Normalization is the process of breaking down a table into smaller tables. So that each table deals with a single theme. There are three different kinds of modifications of anomalies and formulated the first, second and third normal forms (3NF) is considered sufficient for most practical purposes. It should be considered only after a through analysis and complete understanding of its implications. FIRST NORMAL FORM (1NF) : This form also called as a “flat file”. Each column should contain data in respect of a single attributes and no two rows may be identical. To bring a table to First Normal Form, repeating groups of fields should be identified and moved to another table. SECOND NORMAL FORM (2NF) : A relation is said to be in 2NF if it is 1NF and non-key attributes are functionality dependent on the key attributes. A ‘Functional Dependency’ is a relationship among attributes. One attribute is said to be functionally dependent on another if the value of the first attribute depends on the value of the second attribute. In the given description flight number and halt code is the composite key. Splitting of relation given in table 1 into 2NF relations

FLIGHT DETAILS :

AR_FLIGHT_MST

FL_NO VARCHAR2(7) PRIMARY KEY FL_NAME VARCHAR2(25) NOT NULL ORG_CD NUMBER(3) NOT NULL DES_CD NUMBER(3) NOT NULL CAPACITY NUMBER(3) NOT NULL

AR_FLIGHT_DET

FL_NO VARCHAR2(7) PRIMARY KEY (REFERENCE AR_FLIGHT_MST.FL_NO) HALT_CD NUMBER(3) PRIMARY KEY DIST_FRM_ORG NUMBER(3) - NO_QUOTA NUMBER(3) - NO_BOOKED NUMBER(3)  - JOUR_DATE - NOT NULL

THIRD NORMAL FORM (3NF) :

A Third Normal Form normalization will be needed where all attributes in a relation tuple are not functionally dependent only on the key attribute. A transitive dependency is one in which one in which one attribute depends on second which is turned depends on a third and so on.

FLIGHT DETAILS :

AR_FLIGHT_MST

FL_NO VARCHAR2(7) PRIMARY KEY FL_NAME VARCHAR2(25) NOT NULL ORG_CD NUMBER(3) NOT NULL DES_CD NUMBER(3) NOT NULL CAPACITY NUMBER(3) NOT NULL

AR_FLIGHT_DET

FL_NO VARCHAR2(7) PRIMARY KEY (REFERENCE AR_FLIGHT_MST.FL_NO) HALT_CD NUMBER(3) PRIMARY KEY DIST_FRM_ORG NUMBER(3) - NO_QUOTA NUMBER(3) - NO_BOOKED NUMBER(3 - JOUR_DATE - NOT NULL

AR_HALT_MST

HALT_CD NUMBER(3) PRIMARY KEY DESCRIPTION VARCHAR2(30) NOT NULL