Relational Database Concepts
IBM Information Management Cloud Computing Center of Competence
IBM Canada Labs
1 © 2011 IBM Corporation
Agenda
" Overview
" Information and Data Models
" The relational model
" Entity-Relationship diagrams
" Types of relationships
" Mapping entities to tables
" Relational model concepts
" Relational model constraints
" Normalization
2 © 2011 IBM Corporation
Supporting reading material & videos
" Reading materials
" Database Fundamentals eBook
" Chapter 1: Databases and information models
" Chapter 2: The relational data model
" Chapter 3: The conceptual data model (optional)
" Chapter 4: Relational database design (optional)
" Videos
" db2university.com course AA001EN
" Lesson 1: Relational database concepts
3 © 2011 IBM Corporation
Agenda
" Overview
" Information and Data Models
" The relational model
" Entity-Relationship diagrams
" Types of relationships
" Mapping entities to tables
" Relational model concepts
" Relational model constraints
" Normalization
4 © 2011 IBM Corporation
Data vs. Information
" Data: Collection of letters, numbers or facts
" Information: Processed data that provides value
5 © 2011 IBM Corporation
Databases and DBMS
" Databases
" A repository of data
" DBMS (Database management system)
" Software system that manages databases
" The terms  Database ,  DBMS ,  data server ,  database
server often used interchangeably to refer to a DBMS
" Why a DBMS?
" Security
" Can handle many users with good performance
" Allows for concurrency while keeping data consistent
" Protects from disaster
6 © 2011 IBM Corporation
Agenda
" Overview
" Information and Data Models
" The relational model
" Entity-Relationship diagrams
" Types of relationships
" Mapping entities to tables
" Relational model concepts
" Relational model constraints
" Normalization
7 © 2011 IBM Corporation
Information and Data Models
Relationship between an Information Model and a Data Model
© 2011 IBM Corporation
8
Data Models
" Network
" Semantic
" Hierarchical
" Object-oriented
" Relational
" Object-relational
" Entity-Relationship
" Semi-structured
" Extended relational
© 2011 IBM Corporation
9
Agenda
" Overview
" Information and Data Models
" The relational model
" Entity-Relationship diagrams
" Types of relationships
" Mapping entities to tables
" Relational model concepts
" Relational model constraints
" Normalization
10 © 2011 IBM Corporation
Relational Model
© 2011 IBM Corporation
11
Entity-Relationship Diagrams
" Building Blocks
Entity
" Entities
" Attributes
Attribute
© 2011 IBM Corporation
12
Entity and Attributes
© 2011 IBM Corporation
13
ER diagram
Title
Description
Editon
Aisle Book Year
Pages
Price
ISBN
© 2011 IBM Corporation
14
Exercise: Identify entities and attributes
House Phone #
Social Security Number
Computer
Product
Date
Height Order #
© 2011 IBM Corporation
15
Did you get them right?
House Phone #
Social Security Number
Computer
Product
Date
Height Order #
© 2011 IBM Corporation
16
Agenda
" Overview
" Entity-relationship diagrams
" The relational model
" Entity-Relationship diagrams
" Types of relationships
" Mapping entities to tables
" Relational model concepts
" Relational model constraints
" Normalization
17 © 2011 IBM Corporation
Relationships
" Building Blocks

Entity sets

Relationship sets

Crows Foot notations
© 2011 IBM Corporation
18
ERD of Book
Title
Description
Editon
Aisle Book Year
Pages
Price
ISBN
© 2011 IBM Corporation
19
ERD of Author
Lastname
Author_Id
Country Author Firstname
City Email
© 2011 IBM Corporation
20
Example 1
© 2011 IBM Corporation
21
Example 2
© 2011 IBM Corporation
22
Types of Relationships
Authored By
Book Author
One-to-one Relationship
© 2011 IBM Corporation
23
Types of Relationships (Continued)
Authored By
Book Author
One-to-many Relationships
© 2011 IBM Corporation
24
Types of Relationships (Continued)
Authored By
Book
Author
Many-to-many Relationships
© 2011 IBM Corporation
25
Agenda
" Overview
" Entity-relationship diagrams
" The relational model
" Entity-Relationship diagrams
" Types of relationships
" Mapping entities to tables
" Relational model concepts
" Relational model constraints
" Normalization
26 © 2011 IBM Corporation
ERD revisited
Title
Description
Editon
Aisle Book Year
Pages
Price
ISBN
© 2011 IBM Corporation
27
Mapping entity to a table
Title
Description
Editon
Entity
Aisle Attributes Year
Book
Pages
Price
ISBN
Table
Columns
© 2011 IBM Corporation
28
Mapping entity to a table (Continued)
Table: Book
Title Edition Year Price ISBN Pages Aisle Description
Database 1 2010 24.99 978-0- 300 DB- Teaches you
Fundamentals 98662 A02 the
83-1-1 fundamentals
of databases
Getting started 1 2010 24.99 978-0- 280 DB- Teaches you
with DB2 98662 A01 the essentials
Express-C 83-5-1 of DB2 using
DB2 Express-
C, the free
version of
DB2
© 2011 IBM Corporation
29
Mapping entity to a table (Continued)
Table: Author
Author_ID Lastname Firstname Email City Country
A1 Chong Raul [e-mail] Toronto CA
A2 Ahuja Rav [e-mail] Toronto CA
A3 Hakes Ian [e-mail] Toronto CA
A4 Sharma Neeraj [e-mail] Chennai IN
A5 Perniu Liviu [e-mail] Transilva RO
nia
© 2011 IBM Corporation
30
Agenda
" Overview
" Entity-relationship diagrams
" The relational model
" Entity-Relationship diagrams
" Types of relationships
" Mapping entities to tables
" Relational model concepts
" Relational model constraints
" Normalization
31 © 2011 IBM Corporation
Relational Model Concepts
Dr. E.F. Codd of IBM in 1970:
 A Relational Model for Large Shared Data Banks
" Building Blocks
" Relation
" Sets
© 2011 IBM Corporation
32
A Relational Database
" Relational Database
" Relation
" Relation Schema
" Relation Instance
© 2011 IBM Corporation
33
A Relation
AUTHOR(Author_ID: char, lastname: varchar, firstname: varchar, Relation
email: varchar, city: varchar, country: char)
Schema
ATTRIBUTES
Relation Instance
DEGREE=6
Author_ Lastname Firstna Email City Country
ID me
CARDINALITY=5
A1 Chong Raul [e-mail] Toronto CA
CA
A2 Ahuja Rav [e-mail] Toronto
CA
A3 Hakes Ian [e-mail] Toronto
TUPLES
IN
A4 Sharma Neeraj [e-mail] Chennai
RO
A5 Perniu Liviu [e-mail] Transilvania
A DOMAIN is the set of all possible values for a specific attribute
© 2011 IBM Corporation
34
Agenda
" Overview
" Entity-relationship diagrams
" The relational model
" Entity-Relationship diagrams
" Types of relationships
" Mapping entities to tables
" Relational model concepts
" Relational model constraints
" Normalization
35 © 2011 IBM Corporation
Relational Model Constraints
Business Rules
Authored By
Book Author
Referencing
Data Integrity
© 2011 IBM Corporation
36
ERD representation of a Relational Data Model
Primary Key
Parent Table
Dependant Table
Foreign Key
© 2011 IBM Corporation
37
Constraints
" Entity Integrity Constraint
" Referential Integrity Constraint
" Semantic Integrity Constraint
" Domain Constraint
" Null Constraint
" Check Constraint
© 2011 IBM Corporation
38
Entity Integrity Constraint
AUTHOR
Author_ID Lastname Firstname Email City Country
[PK]
A1 Chong Raul [e-mail] Toronto CA
A2 Ahuja Rav [e-mail] Toronto CA
A3 Hakes Ian [e-mail] Toronto CA
A4 Sharma Neeraj [e-mail] Chennai IN
A5 Perniu Liviu [e-mail] Transilvania RO
© 2011 IBM Corporation
39
Entity Integrity Constraint
AUTHOR
Author_ID Lastname Firstname Email City Country
[PK]
A1 Chong Raul [e-mail] Toronto CA
A2 Ahuja Rav [e-mail] Toronto CA
A3 Hakes Ian [e-mail] Toronto CA
A4 Sharma Neeraj [e-mail] Chennai IN
A5 Perniu Liviu [e-mail] Transilvania RO
© 2011 IBM Corporation
40
Entity Integrity Constraint
AUTHOR
Author_ID Lastname Firstname Email City Country
[PK]
A1 Chong Raul [e-mail] Toronto CA
NULL
A2 Ahuja Rav [e-mail] Toronto CA
A3 Hakes Ian [e-mail] Toronto CA
A4 Sharma Neeraj [e-mail] Chennai IN
A5 Perniu Liviu [e-mail] Transilvania RO
© 2011 IBM Corporation
41
Entity Integrity Constraint
AUTHOR
Author_ID Lastname Firstname Email City Country
[PK]
A1 Chong Raul [e-mail] Toronto CA
NULL
A2 Ahuja Rav [e-mail] Toronto CA
A3 Hakes Ian [e-mail] Toronto CA
A4 Sharma Neeraj [e-mail] Chennai IN
NULL
A5 Perniu Liviu [e-mail] Transilvania RO
© 2011 IBM Corporation
42
Referential Integrity Constraint
Authored By
Book Author
Referencing
© 2011 IBM Corporation
43
Semantic Integrity Constraint
AUTHOR
Author_ID Lastname Firstname Email City Country
[PK]
A1 Chong Raul [e-mail] Toronto CA
A2 Ahuja Rav [e-mail] Toronto CA
A3 Hakes Ian [e-mail] Toronto CA
A4 Sharma Neeraj [e-mail] Chennai IN
A5 Perniu Liviu [e-mail] Transilvania RO
© 2011 IBM Corporation
44
Semantic Integrity Constraint
AUTHOR
Author_ID Lastname Firstname Email City Country
[PK]
A1 Chong Raul [e-mail] CA
Toronto
12(*)&^23
A2 Ahuja Rav [e-mail] Toronto CA
A3 Hakes Ian [e-mail] Toronto CA
A4 Sharma Neeraj [e-mail] Chennai IN
A5 Perniu Liviu [e-mail] Transilvania RO
© 2011 IBM Corporation
45
Domain Constraint
AUTHOR
Author_ID Lastname Firstname Email City Country
A1 Chong Raul [e-mail] Toronto
CA
A2 Ahuja Rav [e-mail] Toronto
CA
A3 Hakes Ian [e-mail] Toronto
CA
A4 Sharma Neeraj [e-mail] Chennai IN
A5 Perniu Liviu [e-mail] Transilvania RO
© 2011 IBM Corporation
46
Domain Constraint
AUTHOR
Author_ID Lastname Firstname Email City Country
A1 Chong Raul [e-mail] Toronto
CA
34
A2 Ahuja Rav [e-mail] Toronto
CA
34
A3 Hakes Ian [e-mail] Toronto
CA
34
A4 Sharma Neeraj [e-mail] Chennai IN
A5 Perniu Liviu [e-mail] Transilvania RO
© 2011 IBM Corporation
47
NULL Constraint
AUTHOR
Author_ID Lastname Firstname Email City Country
A1 Chong Raul [e-mail] Toronto CA
A2 Ahuja Rav [e-mail] Toronto CA
A3 Hakes Ian [e-mail] Toronto CA
A4 Sharma Neeraj [e-mail] Chennai IN
A5 Perniu Liviu [e-mail] Transilvania RO
© 2011 IBM Corporation
48
NULL Constraint
AUTHOR
Author_ID Lastname Firstname Email City Country
A1 Chong Raul [e-mail] Toronto CA
NULL
A2 Ahuja Rav [e-mail] Toronto CA
A3 Hakes Ian [e-mail] Toronto CA
NULL
A4 Sharma Neeraj [e-mail] Chennai IN
A5 Perniu Liviu [e-mail] Transilvania RO
© 2011 IBM Corporation
49
Check Constraint
BOOK
Title Edition Year Price ISBN Pages Aisle Description
Database 1 2010 24.99 978-0- 300 DB- Teaches you
Fundamentals 98662 A02 the
83-1-1 fundamentals
of databases
Getting started 1 2010 24.99 978-0- 280 DB- Teaches you
with DB2 98662 A01 the essentials
Express-C 83-5-1 of DB2 using
DB2 Express-
C, the free
version of
DB2
© 2011 IBM Corporation
50
Check Constraint
BOOK
Title Edition Year Price ISBN Pages Aisle Description
Database 1 2010 24.99 978-0- 300 DB- Teaches you
Fundamentals 98662 A02 the
83-1-1 fundamentals
of databases
Getting started 1 2010 24.99 978-0- 280 DB- Teaches you
2015
with DB2 98662 A01 the essentials
Express-C 83-5-1 of DB2 using
DB2 Express-
C, the free
version of
DB2
© 2011 IBM Corporation
51
Agenda
" Overview
" Entity-relationship diagrams
" The relational model
" Entity-Relationship diagrams
" Types of relationships
" Mapping entities to tables
" Relational model concepts
" Relational model constraints
" Normalization
52 © 2011 IBM Corporation
Normalization
%
Process in database design to remove redundancies
%
Example:
Consider the following table listing all the tasks of an employee:
Problem:
If John moves to a new city, all entries related to John must be updated
53 © 2011 IBM Corporation
Normalization (continued)
No redundancy, no anomalies, no loss of information
54 © 2011 IBM Corporation
Thank you!
55 © 2011 IBM Corporation