| Home > Computer Science > CS 17 |
| |
|
|
| Relational
Database Management Systems |
| |
| Airline Reservation System |
| |
| 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 |
|
| |
| Cont... |
| TOP |
| |
|
