Database and data Modelling

This document provides some idea and knowledge in Data analysis and Database Design.

Click Here To Download File

Normalization

Normalization is the simplest form of data representation, that basically reflects separate entity type, attributes and relationship among the related entities to avoid unnecessary duplication and anomalies of data.

Advantages of normalization

• ·         Efficient database design and performance
• ·         Reduction of unnecessary data and anomalies
• ·         Achievement of  relevant and accurate relationship
• ·         Easy maintenance and manipulation of data without restructure of data
• ·         Charts Rules regarding relational database

Un-normalised form (UNF)

Assumption before normalization is performed

Transaction_id – to track rented videos

Rental – entity that is used to convert “many to many relationships” into “one to many relationships”

Rental (cus_id, cus_name, cus_address, deposit, membership_category, joined_date, staff_id, staff_name, staff_address, transaction_id, issued_date, Valid_date, returned_date, overhead_charge , amount, transaction_id, {video_id, v_name, category, media_type, language, producer, actor, director, length_time})

First Normal from (1NF)

To normalize the un-normalised data to first normal form we should remove the repeating group to separate entity for simplicity and uniform access.

Applying 1NF to above collected and un-normalised data

Rental (cus_id, cus_name, cus_address, deposit, membership_category, joined_date, staff_id, staff_name, staff_address, transaction_id, issued_date, Valid_date, returned_date, overhead_charge, amount)

Video (video_id, v_name, category, media_type, language, producer, actor, director, length_time, transaction_id)

2NF

To normalise the 1NF data to 2NF we should eliminate partial dependencies hence make the determinant the primary key (PK).

Applying 2NF to above 1NF data

Rental (cus_id, cus_name, cus_address, deposit, membership_category, joined_date, staff_id, staff_name, staff_address, transaction_id, issued_date, Valid_date, returned_date, overhead_charge, amount)

Rental_line (transaction_id, video_id)

Video (video_id, v_name, category, media_type, language, producer, actor, director, length_time)

3NF

To perform 3NF the entity must be in 2NF, then separate transitive dependency that seems to appear in the entity.

Customer – (cus_id, cus_name, cus_address, deposit, membership_category, joined_date)

Staff – (staff_id, staff_name, staff_address)

Rental (transaction_id, issued_date, Valid_date, returned_date, overhead_charge, amount, cus_id, staff_id)

Rental_line (transaction_id, video_id)

Video (video_id, v_name, category, media_type, language, producer, actor, director, length_time)