Relational Model

1

• Relational Model
• E.F. Codd at IBM 1970
• Chapter 7.1-7.2

2
Relational Model

• Most popular
• simplest
• most uniform data structures
• most formal
• files mathematical foundation

3
Relational Model

• database is a collection of relations
• relation is a table of values 
• a table is composed of rows and columns
• row - a collection of related data values 
• describing an entity or relationship instance
• column same attribute for different entities

4
Glossary of terms

• Table -gt RELATION
• Row -gt TUPLE
• Column header -gt ATTRIBUTE
• Data type of a column  -gt  DOMAIN
•  Fig. 7.1

5
Glossary Contd

• DOMAIN D
• specify data type, format for each domain
• is of data-type (or format)
• set of atomic values (relational model
  requirement)
• TUPLE
• no composite values (all values are atomic)
• no multivalued attributes (First Normal Form)

6
 Relations

• RELATION Characteristics 
• Set of tuples - not ordered
• Values within tuples are ordered
• ATTRIBUTES
•  Ai is an attribute with a domain dom(Ai)
•  degree of a relation - the number of attributes
• RELATION SCHEMA R(A1, A2,...,An)
• includes relation name R and list (set) of
  attributes Ai
• Fig 7.5

7
Relations

• RELATION (instance) r( R )
• current relation state  
• set of n-tuples (where n is a number of
  attributes)
• r t1, t2,...,tm each n-tuple t is an ordered
  list on n values
• t ltv1, v2,...vngt where vi is an element of
  dom(Ai) or null
• t1Ai is the value vi for attribute Ai of
  tuple t1
• r( R ) is a subset of dom(A1) X dom(A2) X...X
  dom(An)
• the tuples themselves are not ordered

8
Relations

• relation intension refers to the schema
• relation extension to the state
• all tuples within a relation are distinct
• Fig 7.6
•  

9
Constraints

• DOMAIN CONSTRAINTS
• 1st normal form 1NF
• value of each attribute must be an atomic
  (single) value from the domain for that attribute
  
• no composite attributes

10
Constraints

• 2. KEY CONSTRAINTS
• All elements are distinct
• no two tuples can have the same combination of
  values for all their values (uniqueness
  constraint). Therefore, all elements are
  distinct.
• superkey sk
• any set of attributes with property
• no two tuples from the relation have the same
  combination of values for those attributes
• t1sk ! t2sk
• every relation has at least one superkey, what
  is it?

11
Constraints contd 

• key is a minimal superkey if 
• we cannot remove any attribute and still have
  uniqueness constraint hold
• candidate key is any one of the keys
• primary key PK is designated candidate key
  (underlined)

12
Constraints

• 3. ENTITY INTEGRITY CONSTRAINTS
• no primary key value can be null
•  

13
Constraints

• 4. REFERENTIAL INTEGRITY CONSTRAINTS
• a tuple in one relation that refers to another
  relation must refer to an existing tuple in that
  relation
• specified between two relations
• maintain the consistency among tuples in two
  relations
• if one relation refers another, then the primary
  key of the referred relation is a foreign key FK
  in the referring relation.
• t1PK t2FK
• Fig 7.7

14
Relations

• RELATIONAL DATABASE SCHEMA
• a set of relation schemas and a set of
  referential integrity constraints
• S R1, R2,..., Rm and a set of integrity
  constraints IC
• RELATIONAL DATABASE INSTANCE DB
• DB r1, r2,...rm