Oracle sql Normalization

• Modification Anomalies:
– If we want to change the value of one of the attributes of a particular branch, we must update the tuples of all staff located at that branch
students and the grades they have earned in courses they have taken:
Student # Student Major Name Course Course Instructor Instructor Instructor Grade Office Title Number Name #
– Similar to the difference between engineering specs and actually driving -- both are application dependent – For example, for safely operating an automobile, the engineering specs are useful but incomplete -- we need driving methodologies (e.g., defensive driving) and a system of laws.
10
First Normal Form (1NF) C S 6 • A relation is in the First Normal Form (1NF) if it does not include any repeating 0 groups 5
11
C S 6 0 5
Relation In 1NF
3
C S 6 0 5
Why Normalize?
• Provides a strategy for constructing relations and selecting keys • Improves interfaces for ad hoc interaction with the database by end users • Reduces problems with anomalies • Makes changes to data and programs easier • Makes physical database design easier • Helps you identify potential problems that may require additional analysis and documentation
9
C S 6 0 5
Non-Normalized Grade Report
Student# Student Name Williams Major Course# Course Title SQL SA&D Cost Mgmt SA&D VAW Instructor # 1 5 2 5 3 Instructor Name Jones Smith Barnes Smith Westin Instructor Office SMI403 SMI412 AAC213 SMI412 SMI422 Grade 234343444 Acctg CS361 CS360 AC310 345455455 Baker CIS CS360 CS362 4.0 3.7 4.0 1.7 3.3
– Implies relative “operational risk” – Unlike debt ratings, normal forms may be accurately and precisely ascertained by analysis of the relations 8
Repeating Group C S • A collection of logically related attributes 6 that occur multiple times within one row 0 • For example, assume that we have a database that maintains information about 5
– To insert a new staff member, we must include details of the branch assigned to – It is impossible to insert a new branch that currently has no members (why?)
Student# 234343444 234343444 234343444 345455455 345455455 Student Name Williams Williams Williams Baker Baker Major Acctg Acctg Acctg CIS CIS Course# CS361 CS360 AC310 CS360 CS362 Course Title SQL SA&D Cost Mgmt SA&D VAW Instructor # 1 5 2 5 3 Instructor Name Jones Smith Barnes Smith Westin Instructor Office SMI403 SMI412 AAC213 SMI412 SMI422 Grade 4.0 3.7 4.0 1.7 3.3
Staff_No SName SL21 John White SG37 Ann Beech SG14 David Ford SA9 Mary Howe SG5 Susan Brand SL41 Julie Lee SAddress 19 Taylor Street, London 81 George Street, Glasgow 63 Ashby Street, Glasgow 2 Elm Place, Aberdeen 5 Gt Western Rd, Glasgow 28 Malvern Street, Kilburn Position Salary Branch_No BAddress Manager 30000 B5 22 Deer Riadm Kibdib Snr Asst 12000 B3 163 Main Street, Glasgow Deputy 18000 B3 163 Main Street, Glasgow Assistant 9000 B7 16 Argyll Street, Aberdeen Manager 24000 B3 163 Main Street, Glasgow Assistant 9000 B5 22 Deer Riadm Kibdib Tel_No 0171-886-1212 0141-339-2178 0141-339-2178 01224-67111 0141-339-2178 0171-886-1212
Staff_Branch (Staff_No, SName, SAddress, Position, Salary, Branch_No, BAddress, Tel_No)
• Note the redundant information above. This leads to update anomalies: • Insert Anomalies:
• Formal definition: For any relation R, attribute B is functionally dependent on attribute A if, for every valid instance of A, that value of A uniquely determines the value of B.
– In English: functional dependence exists between A and B if based on every valid value of A we can explicitly determine the value of B – Functional dependence of B on A is represented as A B
4
Important Concepts C S 6 • Functional dependency 0 • Repeating group 5 • Partial functional dependency
• Transitive dependency
5
C S 6 0 5
Functional Dependency
C S 6 0 5
Normalization
CS605 Prof. Jay Cooprider
1
C S 6 0 5
Normalization
• Relational Data Model focuses on structural & operational characteristics of storing and retrieving data • The Model doesn’t dictate its functional or semantic use -- i.e., how it’s applied to a real-world application
7
C S 6 0 5
Normal Forms
• A normal form is a minimal set of rules applied to a relation that determines its “safe” behavior under certain relational operations -- particularly updates. • By “safe,” we mean the maintenance of integrity -the information stored in the database is not diminished as a result, and “truth” is maintained • The normal forms may be thought of as “standards of quality” similar to grading debt instruments: AA,A,B...