Question

The table below shows an extract from a tour operator's data on travel agent bookings. Derive the third normal form of the data, showing all the intermediate steps. holiday code quantitv booked airport airport code batcbno agentng agent name cost name 363 10 248 20 322 18 363 15 568 2 9721 B563 Bairns trvel B248 B428 B563 C930 A270 B728 C930 A430 Luton 12 Edinburgh 76 Glasgow Luton Newcastle Active Holidays 142 Newcastle 14 2485 12 Edinburgh 568 11 Luton 76 Bairns travel | Glasgow 27915

Answer 1

The agents are having more than one holiday codes,cost,quantity booked airport code,airport name.

This given data table is not in 1NF as the rule says “each attribute of a table must have atomic (single) values”.

To make the table complies with 1NF we should have the data like this:

batchno	agentno	Agent name	Holiday code	cost	Quantity booked	airportcode	Airport name
1	76	Bairns travels	B563	363	10	1	Luton
1	76	Bairns travels	B248	248	20	12	Edinburgh
1	76	Bairns travels	B428	322	18	11	Glasgow
2	142	Active holidays	B563	363	15	1	Luton
2	142	Active holidays	C930	568	2	14	Newcastle
2	142	Active holidays	A270	972	1	14	Newcastle
2	142	Active holidays	B728	248	5	12	Edinburgh
3	76	Bairns travels	C930	568	11	1	Luton
3	76	Bairns travels	A430	279	15	11	Glasgow

Now the table is in 1NF

A table is said to be in 2NF if both the following conditions hold:

• Table is in 1NF (First normal form)
• No non-prime attribute is dependent on the proper subset of any candidate key of table

The above data table is already in 2NF.

A table design is said to be in 3NF if both the following conditions hold:

• Table must be in 2NF
• Transitive functional dependency of non-prime attribute on any super key should be removed.

To make this table complies with 3NF we have to break the table into four tables to remove the transitive dependency:

Batch table:

batchno	agentno	Holiday code	Quantity booked	airportcode
1	76	B563	10	1
1	76	B248	20	12
1	76	B428	18	11
2	142	B563	15	1
2	142	C930	2	14
2	142	A270	1	14
2	142	B728	5	12
3	76	C930	11	1
3	76	A430	15	11

Airport table:

airportcode	Airport name
1	Luton
12	Edinburgh
11	Glasgow
14	Newcastle

cost table:

Holiday code	cost
B563	363
B248	248
B428	322
C930	568
A270	972
B728	248
A430	279

Agent table:

agentno	Agent name
76	Bairns travels
142	Active holidays