Database Systems Lecture 15 Chapter 10 Normalization Purpose

Database Systems Lecture # 15 Chapter # 10 Normalization

Purpose of Normalization • Normalization is the process of efficiently organizing data in a database. • There are two goals of the normalization process: 1. Eliminating redundant data (for example, storing the same data in more than one table) 2. Ensuring data dependencies make sense (only storing related data in a table) • Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored. 2

Purpose of Normalization • The benefits of using a database that has a suitable set of relations is that the database will be: 1. Easier for the user to access and maintain the data; 2. Take up minimal storage space on the computer. 3

Data Redundancy and Update Anomalies 4

Data Redundancy and Update Anomalies Staff. Branch relation has redundant data; the details of a branch (b. Address) are repeated for every member of staff. The branch information (b. Address) appears only once for each branch in the Branch relation and only the branch number (branch. No) is repeated in the Staff relation, to represent where each member of staff is located 5

Data Redundancy and Update Anomalies • Relations that contain redundant information may potentially suffer from update anomalies. • Types of update anomalies include – Insertion – Deletion – Modification 6

Insertion Anomalies Staff. Branch (Problems) • To insert new member in Branch B 007 • We must enter the details of branch B 007 • To insert new branch that has no member (Staff. No is Primary key) • We could not insert a branch. No & b. Address unless we enter staff. No which is the Primary key This Design solves the above problems. Here one can insert a branch without inserting a Staff. No. Why? 7

Deletion Anomalies Staff. Branch (Problems) • To delete a member • The details about his branch will also be lost • Example: delete staff number SA 9 • It will also delete branch B 007 This Design will avoid the above problem. Because if we want to delete any staff we will not lose it’s corresponding branch information. 8

Modification Anomalies Staff. Branch (Problems) • To change the value of one of the attributes • We must update set of rows • Example: update the address of branch number B 003 • We must update 3 rows This Design avoids the above problems. Just in a single table update is required and it will be reflected any where. 9

Functional Dependencies • Functional dependency describes relationship between attributes. 10

Characteristics of Functional Dependencies • For example, if A and B are attributes of relation R, B is functionally dependent on A (denoted by A B), if each value of A in R is associated with exactly one value of B in R. • A is the determinant of B (means A determines B) 11

An Example of Functional Dependency 12

The Process of Normalization • Formal technique for analyzing a relation based on its primary key and the functional dependencies between the attributes of that relation. • As normalization proceeds, the relations become progressively more restricted (stronger) in format and also less vulnerable to update anomalies. 13

The Process of Normalization 14

First Normal Form (1 NF) • A relation in which the intersection of each row and column contains one and only one value. Each attribute must be Atomic 15

1 NF • Remove the repeating group by – Eliminate duplicative columns from the same table. – Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key). 16

Example 1: Branch table is not in 1 NF 17

Example 1: Converting Branch table to 1 NF PK PK Both Makes Composite PK 18

Example(2) 1 NF • DNo • DName • DLocation : Department Number. : Department Name. : Department Location. • Is table in 1 NF ? 19

Example(2) 1 NF Department Table is NOT in 1 NF PK DNO Dname Dlocation 5 Research {Standford} 3 Admin {Houston, London, New. York} Department Table is in 1 NF Composite PK DNO Dname Dlocation 5 Research Standford 3 Admin Houston 3 Admin London 3 Admin New. York 20

Example(3) 1 NF • Student_No • Courses_ ID : Student Number. : Course Numbers. • Is table in 1 NF ? 21

Example(1 -3) 1 NF Student Table is Not in 1 NF PK Student_No Courses_ID 200 {CS 424, CS 595, CS 100} 300 {CS 595, CS 100} Composite PK Student Table is in 1 NF Student_No Course_ID 200 CS 424 200 CS 595 200 CS 100 300 CS 595 300 CS 100 22