Relational Data Model

1
Relational Model
2
Outline

• Background and terminology
• Foundations
• Data Structure
• Data Integrity
• Data Manipulation

3
What is Relational Model

• A data model based on relations and their
• representation as tables first proposed by
• E.F. Codd in 1978.
• Supports powerful, yet simple and declara-
• tive languages with which operations on
• data are expressed.

4
Database Relations
Attributes
VOTER
Tuple
5
Basic Concepts

• Relation Table
• Tuple Row or Record
• Cardinality of rows
• Attribute Column or Field
• Degree of Column
• Domain Pool of legal value

6
Basic Concepts

• Relation scheme
• Set of attribute names for a relation
• e.g. student (ssn, name, address, phone, ...)
• class(class, title, ...)

7
Example
CITYINFO relation
8
Properties of the Relational Model

• All data must be presented in tabular form.
• All data must be atomic.
• No duplicate tuples are allowed.
• Tuples and attributes may be rearranged in almost
  any order

9
Example
CAND (NAME, DATE-OF-BIRTH, JOB)
10
Data Integrity

• Data must satisfy certain integrity rules and
  constraints before it is allowed to be entered
  into the relation.
• Entity Integrity
• Referential Integrity

11
Keys

• Relations have sets of one or more attributes
  that
• serve as a key.
• The following two properties must be satisfied
  for
• key
• 1. Unique Identification
• 2. Nonredundancy

12
Keys

• Super key
• Candidate key
• Primary key
• Secondary key
• Foreign key
• Composite key

13
Reducing E-R Diagrams to tables
E-R diagram.
date
transaction- number
street
customer- city
date
social security
amount
account- number
customer- name
balance
transaction
log
customer
account
CustAcct
-
14
Developing Relational Models

• Representation of Strong Entity Sets
• Representation of Weak Entity Sets
• Multivalued Attributes of Entities

15
Example entity set by a table called ACCOUNT
with two columns gt account
number gt balance
The account table
16
Example
Entity set Customer (customer name, social
security, street, customer city)
The customer table.
17
Elimination of a multivalued attribute from an
entity
PRODUCT_CODE MATERIAL_CODES (1,n) DESCRIPTION PRIC
E
PRODUCT
PRODUCT_CODE DESCRIPTION PRICE
PRODUCT
PRODUCT_CODE MATERIAL_CODE
PRODUCT_MATERIAL
18
LAST_NAME AGE SEX
PERSON
Composite Attributes of Entities
STREET
ADDRESS
CITY
STATE
(a) Schema with a composite attribute
LAST_NAME AGE SEX STREET CITY
PERSON
STATE
(b) Composite attribute reduced to components
LAST_NAME AGE SEX ADDRESS
PERSON
(c) Composite attribute considered as single
attribute
Elimination of a composite attribute
19
Table CUST ACCT has three columns
20
Example
branch- city
CAB
branch- name
assets
branch
street
customer- city
social- security
balance
account- number
customer- name
customer
CAB
account
E-R diagram with a ternary relationship
21
The CAB table.
22
Multivalued Attributes of Relationships
If the multivalued attribute belongs to a
relationship R between entities E1 and E2, create
a separate, new entity to represent it. The new
entity includes one or two attributes taken from
E1, E2 or both, depending on the type of the
relationship. 1. one-to-one include primary key
of either E1 or E2 2. one-to-many include
primary key of E2 3. many-to-many include
primary keys of E1 and E2 (e.g. students take
courses)
23
INSTRUCTOR_SSN DEPARTMENT PHONE_NUMBER
INSTRUCTOR
Elimination of a multivalued attribute from a
relationship by creating a separate entity
INSTRUCTOR_SSN DEPARTMENT PHONE_NUMBER
INSTRUCTOR
MAX_NO_STUD
OFFERS
COURSE_NO
COURSE

INSTRUCTOR_NNS COURSE_NO SEMESTER
COURSE_ OFFERING
24
Recursive Relationships
NAME
DATE_OF_BIRTH
EMPLOYEE
IS_MANAGER_OF
IS_SUBORD
MANAGER_ OF
Translation?
25
Generalization and Specialization
--- Two ways of creating tables from E-R diagrams
account
Generalization
ISA
savings-account
checking-account
26
Generalization
Create a table for the higher-level entity. For
each lower-level entity, create a table which
includes a column for each of the descriptive
attributes of that entity plus a column for
each attribute of the primary key of the
high-level entity.
27
Specification
Do not create a table for the higher level
entity. Create a table for each lower-level
entity which includes a column for each
attribute of the entity plus a column for each
attribute of the higher-level entity.
28
Advantages of the Relational Model

• True data independence
• Communicability
• Set processing
• Accessing information