2 the relational model and normalization
TRANSCRIPT
1
IT468 DB @ ITAM 1
§2 – The Relational Model and
Normalization
Fen Wang
Lecture 4
IT468 DB @ ITAM 2
Chapter Objectives
• The foundation of the relational model
• Characteristics of relations
• The basic relational terminology
• Primary, candidate, and composite keys
• The purpose and use of surrogate keys
• How foreign keys represent relationships
• The meaning of functional dependencies
• Possible insertion, deletion, and update anomalies
• Apply a process for normalizing relations
2
IT468 DB @ ITAM 3
The Relational Database Model
• The dominant database model is the relational database model – all current major DBMS products are based on it
• Created by IBM engineer E. F. Codd in 1970
• It was based on mathematics called relational algebra
• Now the standard model for commercial DBMS products
IT468 DB @ ITAM 4
Important Relational Model Terms
• Entity • Relation • Functional Dependency • Determinant • Candidate Key • Composite Key • Primary Key • Surrogate Key • Foreign Key • Referential integrity constraint • Normal Form • Multivalued Dependency
3
IT468 DB @ ITAM 5
Entity
• An entity is something of importance to a user that needs to be represented in a database:
– Customers
– Computers
– Sales
• An entity represents one theme or topic
IT468 DB @ ITAM 6
Relation
• Relational DBMS products store data about entities in relations, a special type of table
• A relation is a two-dimensional table that has specific characteristics
• The table dimensions, like a matrix, consist of rows and columns
4
IT468 DB @ ITAM 7
Characteristics of a Relation
Smart tip: if a table qualifies to be a relation, it must satisfy all of the
above characteristics!
IT468 DB @ ITAM 8
A Sample Relation
5
IT468 DB @ ITAM 9
A Relation with Values of Varying Length
IT468 DB @ ITAM 10
Tables That Are Not Relations
Cells of the table hold multiple values
6
IT468 DB @ ITAM 11
Tables That Are Not Relations
Cells of the table require a particular row order/arrangement
IT468 DB @ ITAM 12
Tables That Are Not Relations
EmployeeNumber Phone LastName
100 335-6421 Abernathy
101 215-7789 Cadley
104 610-9850 Copley
100 335-6421 Abernathy
107 299-9090 Jackson
No two rows may be identical
7
13
Alternative Terminology
• Although not all tables are relations, the terms table and relation are often used interchangeably
• The following sets of terms are equivalent:
Line format to indicate a table structure
• In addition to using graphic format to display or show a table structure, you could also utilize a line format (a.k.a. textual format) to indicate a table structure:
RELATION_NAME (Column01, Column02, … LastColumn)
IT468 DB @ ITAM 14
8
IT468 DB @ ITAM 15
Keys
• A key is a combination of one or more columns that is used to identify rows in a relation
• A composite key is a key that consists of two or more columns
16
Uniqueness of Keys
Unique Key Nonunique Key
Data value is unique for each row.
Consequently, the key will uniquely identify a row.
Data value may be shared among several rows.
Consequently, the key will identify a set of rows.
9
Uniqueness of Keys (cont.)
• See below for a sample table using the line format:
TEXTBOOK (Author, Title, ISBN, Publisher, Copyright)
– Analyze each column in the table
• Is it a unique or non-unique key?
IT468 DB @ ITAM 17
Sample answers:
Possible unique keys: ISBN
Possible non-unique keys: Author,
Title, Publisher, Copyright
IT468 DB @ ITAM 18
A Candidate Key
• A candidate key are keys that uniquely identify each row in a relation
• A candidate key is a unique key
10
19
Primary Keys
• A primary key is a candidate key selected as the primary means of identifying rows in a relation:
– There is one and only one primary key per relation
– The primary key may be a composite key
– The ideal primary key is short, numeric and never changes
EMPLOYEE(EmployeeNum,FirstName,LastName,Department,Email,Phone)
20
Primary Keys Example
IT468 DB @ ITAM
11
IT468 DB @ ITAM 21
Composite Primary Key Example
• To identify a grade, you need to know a StudentID, CourseID, and Session (e.g., Fall 2009)
• The composite key is:
(StudentID, CourseID, Session)
• One needs to know the value of all three columns to uniquely identify a grade
IT468 DB @ ITAM 22
A Surrogate Key
• A surrogate key is a unique, numeric value that is added to a relation to serve as the primary key
• Surrogate key values have no meaning to users and are usually hidden on forms, queries and reports
• A surrogate key is often used in place of a composite primary key
12
IT468 DB @ ITAM 23
Surrogate Key Example
NOTE: The primary key of the relation is underlined below:
• RENTAL_PROPERTY without surrogate key:
RENTAL_PROPERTY (Street, City, State/Province, Zip/PostalCode, Country, Rental_Rate)
• RENTAL_PROPERTY with surrogate key: RENTAL_PROPERTY (PropertyID, Street, City,
State/Province, Zip/PostalCode, Country, Rental_Rate)
IT468 DB @ ITAM 24
Defining the Primary Key in Microsoft Access
13
IT468 DB @ ITAM 25
Defining the Primary Key in Microsoft SQL Server
IT468 DB @ ITAM 26
Defining the Primary Key in MySQL
Check the PK
checkbox to
indicate the
Primary Key
14
IT468 DB @ ITAM 27
Defining the Primary Key in Oracle
Make drop-down
box selection to
indicate the
Primary Key
IT468 DB @ ITAM 28
Before next class
• Practice more with MS Access 2010 and MS Visio Professional
• Keep working on Project D1
• Start working on Assignment#2 (due 10PM on Friday)
• Read Textbook Chapter 1 – The Access WorkBench Section 1