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...
|
