Transcript Relational Terminology - California State University, East Bay

Relational Terminology
Relational DB File System
Mathematics
Table
File
Relation
column
Field label
Attribute
Column value Field
Attribute value
Row
Tuple
Record
Normalization
• A method where data items are grouped
together to better accommodate business
changes
• Provides a method for representing data and
relationships precisely in tabular format that
makes the database efficient (redundancy
kept to a minimum)
• Normalization begins after analysis of
system needs
• Normalization requires identification of
entities, attributes and relationships
Advantages of Normalization
• Direct translation from logical to physical
design in RDMS
• Reduced Data Redundancy
• Protection against update and delete
anomalies
• Ability to add or delete entities, attributes
and relations without wholesale
restructuring of tables
Advantages (cont’d)
• Smaller tables
• Tables with fewer columns(fields) and
therefore shorter rows(records), allowing
more rows of data being used per I/O
(input/output) operation making the
database very efficient
1NF
Repeating Groups Not Allowed
• Unnormalized
–
–
–
–
–
–
–
–
–
–
Employee
SSN
Name
Dept Code
Dept Name
Dept Location
Skill Code
Skill
Skill Name
Skill Level
• Normalized
• Employee(p)
–
–
–
–
SSN
Name
Dept Code
Dept Name
• SSN(p)
– Skill Code
– Skill Name
– Skill Level
2NF
• Separate relations are required for any
attributes that depend on only part of a
composite key
2NF
• Second Normal Form • Second Normal Form
Achieved
Violated
–
–
–
–
–
SSN
Skill Code
Skill
Skill Name
Skill Level
– SSN(p)
– Skill Code(p)
– Skill Level
– Skill Code(p)
– Skill Name
3NF
• Non key attributes should not contain facts
about another nonkey attribute in the
relation
3NF
• Third Normal Form
Violated
–
–
–
–
–
SSN
Employee Name
Department Code
Department Name
Department Location
• Third Normal Form
Achieved
– SSN(p)
– Employee Name
– Department Code
– Department Code(p)
– Department Name
– Department Location
3NF
• Each Attribute is a fact about the key, the
whole key and nothing but the key
3NF
• Experienced Database Architects will move
directly to 3rd Normal Form. Fourth and
Fifth Normal Forms are rarely used.
• Normalization results in one Table, or
Relation for each entity with attributes
shown as columns(fields) and each
occurrence as a row(record).
Summary
• Produces database designs that offer
efficient performance
• Minimizes chances for data inconsistency
• In some cases performance advantages may
be gained by violating normalization Denormalization must be done with great
care and caution
Referential Integrity
• Every foreign key value must have a
corresponding primary key value
• Enforcement optional
Relational Model
Data Stored & Retrieved As Tables
• SQL Statement
–
–
–
–
SELECT NAME, PAYGRADE
FROM EMPLOYEE
WHERE DEPT = FIN001
AND SALARY >50000
Relational Model
Data Stored & Retrieved As Tables
• SQL Statement with two tables joined
–
–
–
–
SELECT NAME, GRADE, DEPT
FROM EMPLOYEE, PROJECT
WHERE AGE>40
AND EMPLOYEE.LABOR_CODE =
PROJECT.LABOR_CODE
Guidelines For Development of
Unique Identifiers(keys)
•
•
•
•
•
•
Must be unique
Must be mandatory
Values must never change
Values must be factless
Must be controllable
Must be usable