CS 311 LECTURE 07 DATABASE MANAGEMENT SYSTEM Somchai

CS 311 LECTURE 07 DATABASE MANAGEMENT SYSTEM Somchai Thangsathityangkul

Functional Dependencies • Important concept associated with normalization. • Functional dependency describes relationship between attributes. • For example, if A and B are attributes of relation R, B is functionally dependent on A (denoted A B), if each value of A in R is associated with exactly one value of B in R. 2

Characteristics of Functional Dependencies • Property of the meaning or semantics of the attributes in a relation. • Diagrammatic representation. • The determinant of a functional dependency refers to the attribute or group of attributes on the left-hand side of the arrow. 3

An Example Functional Dependency 4

A Dependency Diagram 5

A Dependency Diagram 6

Try this : identify function dependencies 7

Database Tables and Normalization • Normalization – Process for evaluating and correcting table structures to minimize data redundancies • Reduces data anomalies – Series of stages called normal forms: • First normal form (1 NF) • Second normal form (2 NF) • Third normal form (3 NF) 8

Database Tables and Normalization (cont’d. ) • Normalization (continued) – 2 NF is better than 1 NF; 3 NF is better than 2 NF – For most business database design purposes, 3 NF is as high as needed in normalization – Highest level of normalization is not always most desirable • Denormalization produces a lower normal form – Increased performance but greater data redundancy 9

The Normalization Process • Each table represents a single subject • No data item will be unnecessarily stored in more than one table • All nonprime attributes in a table are dependent on the primary key • Each table is void of insertion, update, deletion anomalies 10

The Normalization Process (cont’d. ) • Objective of normalization is to ensure that all tables are in at least 3 NF • Higher forms are not likely to be encountered in business environment • Normalization works one relation at a time • Progressively breaks table into new set of relations based on identified dependencies 11

Conversion to First Normal Form • A table is said to be in First Normal Form (1 NF) if and only if every entry of the table has at most a single value. • Objective: to remove a table’s repeating groups and ensure that all entries of the resulting table have at most a single value. 12

Conversion to First Normal Form (cont’d. ) • Step 1: Eliminate the Repeating Groups – Eliminate nulls: each repeating group attribute contains an appropriate data value • Step 2: Identify the Primary Key – Must uniquely identify attribute value – New key must be composed 13

Repeating Group example • Repeating group – Group of multiple entries of same type can exist for any single key attribute occurrence 14

Multi-value example 15

First Normal Form (1 NF) Step 2 : Identify primary key Use line# to separate customer order item Step 1 : eliminate repeating 16 group

First Normal Form (1 NF) Step 2 : Identify primary key Step 1 : eliminate multi-value 17

First Normal Form (1 NF) 18

First Normal Form (1 NF) 19

(1 Nf) Dependency diagram 20

Second Normal Form (2 NF) • Table is in second normal form (2 NF) if: – It is in 1 NF and – It includes no partial dependencies: • No attribute is dependent on only a portion of the primary key 21

Second Normal Form (2 NF) 22

Third Normal Form (3 Nf) • A table is in third normal form (3 NF) when both of the following are true: – It is in 2 NF – It contains no transitive dependencies • Step 1: Make New Tables to Eliminate Transitive Dependencies • Step 2: Reassign Corresponding Dependent Attributes 23

Third Normal Form (3 Nf) 24

Final relational schema 25

The Data Dictionary • Data dictionary – Used to provide detailed accounting of all tables found within the user/designer-created database – Contains (at least) all the attribute names and characteristics for each table in the system – Contains metadata—data about data – Sometimes described as “the database designer’s database” because it records the design decisions about tables and their structures 26

Sample data dictinary 27

Try this • Doing normalization to 3 NF and draw a final schema diagram 28