1 / 12

Normal Forms in Relational Databases 1

Normal Forms in Relational Databases 1. 1 Bolstad, P. pp 283-291. Problem. Massive tables with information about multiple entities become unwieldy making data update and maintenance difficult. They suffer from performance issues, consistency, and redundancy. Problem.

ayame
Download Presentation

Normal Forms in Relational Databases 1

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Normal Forms in Relational Databases1 1 Bolstad, P. pp 283-291

  2. Problem • Massive tables with information about multiple entities become unwieldy making data update and maintenance difficult. • They suffer from performance issues, consistency, and redundancy

  3. Problem Redundancy: Alderman Johnson in twice Access: linear search to find parcels owned by Yamane Independence: If Devlin, Yamane and Prestovic sell the parcel they jointly own Devlin is purged from the database!

  4. Solution: Normalization • Data structured in sequentially higher orders of normal form to: • improve consistency • Reduce redundancy • Increase stability

  5. Definitions • Supper key: one or more attributes that may be used to uniquely id one and only one record • Candidate key is a subset of the supper key which may also be a superkey. • Primary key chosen from candidate keys that has a one to one correspondence to each record • Functional dependency • For a given point in time each value of the dependent attribute is determined by a value of another attribute. • Own_name -> Own-add • Tshp_name -> Thall-add • Transitive

  6. 1st NF • A table is in first normal form when there are no repeat columns • Most basic and still suffers from excessive storage, redundancy, inefficient searches and potential loss of data upon updating

  7. 2nd NF • In 1st NF form and every non-key attribute is functionally dependent on the primary key. • Note in 1st NF parcel-ID, Alderman and Tship-ID are duplicated when there are multiple owners of a parcel. Upon update of say Alderman, each redundant record needs to be updated.

  8. First to Second NF Parcel – ID -> Alderman Parcel – ID -> Tship-ID Parcel – ID -> Tship_Name Parcel – ID -> Thall_add Link of two Own-ID -> Own_name Own-ID -> Own_add

  9. Still a problem with 2nd NF • The 2nd NF still has problems though it’s much better than 1st. • The problem is transitive dependency. In our table Land Record 1, Parcel-ID specifies Tship-ID and Tship-ID specifies Tship_nam and Thall_add, so: • Parcel-ID -> Tship-ID, Alderman • And • Tship-ID -> Tship-nam, Thall-add • If we delete a parcel we remove the parcel from tables Land Records 1 and Land Records 3 and loose relationship between Tship-ID, Tship_name and Thall_add.

  10. Solution: 3rd NF • A table is in third normal fom if and only if for every functional dependency A-> B, A is a superkey or B is a member of a candidate key. • Must ID all transitive functional dependencies and remove then by creating new tables

  11. Example

More Related