(a) Taking an unnormalised list, describe how you would normalise it using the n

Question

(a) Taking an unnormalised list, describe how you would normalise it using the normal forms technique and show how the result of this method is used.

(b) You are currently in the process of developing a RDBMS for a national rail company. They provide train services between various counties and towns in London. The marketing manager has given you the following form, which is completed for every train journey: (departure_station, arrival_station, date, departure_time, distance, estimated_time, actual_time_taken, number_of_stops, driver_id, driver_name, train_reg_number, number_of_carriages, max_number_of_passengers, actual_number_of_passengers, amount_collected (passenger_num, passenger_name, passenger_address, cost)

Note 1: A combination of Departure Station, Arrival Station, Date and Departure Time identifies each journey.

Note 2: Each passenger is allocated a passenger number when the first travel with the company. Each time they travel, they use the same passenger number.

Note 3: Cost relates to the amount the customer is charged for that journey. From this list, you should:

Normalise the list using the Normal Forms technique. You should explain any changes you are making at each step of the Normal Forms process.

(ii) Transform the 3NF lists into a set of database tables. Include one row of meaningful data for each of the tables

Explanation / Answer

Hi,

Please find below the answer-

Ans a) To understand normalization lets us first understand the drawback of keeping the data un-normalised. Un-normalised data refers to a data which has lots of redundancies as the entities holds attributes from others entities which then lead to Insertion, Updation and deletion anamolies. Normalization is a process of segregating the attributes to their respective entities by listing them under the entities to which they belong to. This helps to reduce the above mentioned anamolies.

Exmaple-

1. First Normal Form- In this normal form, there must not be any column which has multiple values in it. This means each column should have one value in it.

This may lead to unwanted redundancy in data. To convert the below table into first notmal form, row1 should be splitted ito two rows

After first normal form-

2. Second Normal form- This normal form ensures that there is no partial dependency of thr attributes. If a non-key attribute is not fully dependent on its composite primary key then it is called as partial dependency. In below example consider {student,courses} as composite primary key, then the value of blood_group attribute can be determined by student alone and it has no dependency on courses attribute. This is against 2nd normal form. In order to convert it to second normal form, we need to split the data ito two tables. First table has student and Blood_group attribute and the other one has student and courses attributes.

Example-

After applying second normal form below is the result-

3. Third normal form- This normal form says that every attributes which is not a primary key of the table must depend on the primary key of the table for its existance. In belo example the attribute zip can be determined by the city attribute which is not a primary key of th table.

Example-

After normalization this table should be splited into tables as below so that address related attributed should be kept in separate table-

and address table

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.