Relational Data Model

1
Relational Data Model

• Introduction
• Components
• Relations, Tuples, Attributes, Domains
• Relational Rules
• Update Operations

2
Introduction

• Developed in 1970 by E.F. Codd
• Based on the mathematical theory of relations
• A relational database consists of a set of
  relations (tables)
• A column is a set of values from a common domain
• A row is a relationship among a set of values
• Strictly a model of database as seen by users
• - says nothing about how data are physically
  stored
• - do not confuse with UML class model
• Entire information content of the database is
  represented in exactly one way - as explicit data
  values

3
Components
4
Domains

• Value - an undefined term (primitive)
• e.g., 12.34, St. Johns, 96/10/03
• Domain - a set of meaningful values
• e.g., real numbers from 0 through 25
• set of all character strings of length 25
  or less
• AB, BC, YK
• Values from a domain are subject to the same set
  of operations
• Can be defined by intension or extension
• A value is represented as a single cell in a
  table

5
Attributes

• Attribute - a named set of values from a common
  domain
• A subset of a domain
• Reflects the semantics of attribute of an
  object
• Each value is interpreted as describing one or
  more objects with respect to that attribute at a
  point in time
• e.g., discount 0.00, 8.00, 10.00
• Represented as a single column in a table

6
Tuples

• Tuple - a set of attribute-value pairs
• An element of the Cartesian product of a set of
  attributes
• Interpreted as describing a single entity
• e.g., ltcidc001gt, ltcnameTip Topgt,
• ltcityDuluthgt, ltdiscount10.00gt
• Represented as a single row in a table

7
Relations

• A relation consists of
• header - a fixed set of attribute names
• body
• a set of tuples
• a subset of the Cartesian product of domains of
  the attributes listed in the header
• Represented as a table
• A relational database is a set of relations

8
Relational Rules

• 1. Multi-valued attributes are not permitted
• A single cell in a relation may not contain more
  than one value
• E.g., Suppose we have an EMPLOYEE table
• EMPLOYEE(eid, name, position, dependents)
• an employee clearly may have several dependents
• we cannot represent this in a single cell

9
Relational Rules(contd)
Prohibited
Employee
eid ename position dependents e001 Smith,
J. Agent Mike Susan e002 Jones, F.
Clerk e003 Anson, D. Agent
Louisa Andy Mark
10
Relational Rules(contd)
Alternative 1
Employee
eid ename position dep1 dep2 dep3 . . .
e001 Smith, J. Agent Mike Susan null . . .
e002 Jones, F. Clerk null null null . . .
e003 Anson, D. Agent Louisa Andy Mark .
. .
Problems
1. Inefficient 2. Makes queries difficult
11
Relational Rules(contd)
Alternative 2
Employee
Dependent
eid dname e001 Mike e001 Susan e003 Louisa e003 An
dy e003 Mark
eid ename position
e001 Smith, J. Agent
e002 Jones, F. Clerk
e003 Anson, D. Agent
12
Relational Rules(contd)

• 2. Tuples can be accessed only by their contents
• In principle, the ordering of rows in a relation
  is of no consequence
• Data retrieval is based solely on the values of
  attributes
• Accordingly, it would be meaningless to retrieve
  the nth row of a table
• Illustrates differences between the pure
  relational model and pragmatic implementation
  issues

13
Relational Rules(contd)

• 3. Every tuple in a relation is unique
• Two tuples in a relation cannot have identical
  values for all attributes
• A logical consequence of the fact that a relation
  is a set of tuples
• Otherwise, a row could not be uniquely retrieved
  based on values
• Gives rise to a need for keys

14
Superkeys and Keys

• The set of all attribute values distinguishes any
  two tuples in a relation
• It is possible that a strict subset of attribute
  values distinguishes any two tuples
• Any subset of attributes whose combined values
  always uniquely identify tuples in a relation is
  known as a superkey
• A superkey is a key if no proper subset of it is
  a superkey
• Key is synonymous with candidate key

15
Primary Key and Foreign Key

• A relation may have more than one key
• Primary key - key chosen by the database designer
  from among the candidate keys as the preferred
  key to distinguish tuples in a relation
• Foreign key - a subset of the attributes of a
  relation R such that
• those attributes constitute the primary key of
  another relation, S and
• the value of those attributes in R at every point
  in time appears as a primary key value of some
  tuple in S

16
Null Values

• From time to time, values of some attributes may
  not be available for some tuples in the database
• Such attributes may be assigned a special value
  called null
• Null has several possible meanings, including
• Unknown
• not yet defined
• missing
• Inapplicable

17
Relational Rules(contd)

• 4. Null values are prohibited for primary keys
• (Entity Integrity Rule)
• Since data can only be retrieved from a relation
  based on value, inserting a tuple into a relation
  without a value for its primary key may mean the
  data cannot be retrieved
• A primary key value must be assigned before
  placing a tuple in a database

18
Relational Rules(contd)

• 5. Foreign key values must be present as primary
  key values in the target relation (Referential
  Integrity Rule)
• If a value appears as the value of a foreign key
  in one relation, it must refer to an existing
  value of that key
• Otherwise, the integrity of the database is
  compromised
• Not all relational DBMSs support referential
  integrity

19
Update Operations - Insert

• Adds a tuple to a relation
• Insertion is rejected if it violates in any of
• Entity integrity constraint
• Key constraint
• Referential integrity constraint

20
Update Operations - Delete

• Removes a tuple from a relation
• Can lead to violations of referential integrity
• Violations can lead to
• Rejection
• Cascading (propogation)

21
Update Operations - Update

• Changes the value of one or more attributes
• Can lead to violations of
• Entity integrity
• Key constraint
• Referential integrity
• if PK or FK attributes are involved

22
Running ExampleThe CAP Database
CUSTOMERS
PRODUCTS
cid cname city discnt c001 TipTop
Duluth 10.00 c002 Basics Dallas 10.00 c003
Allied Dallas 8.00 c004 Acme Duluth
8.00 c006 Acme Kyoto 0.00
pid pname city quantity price p01 comb
Dallas 111400 0.50 p02 brush Newark
203000 0.50 p03 razor Duluth 150600
1.00 p04 pen Duluth 125300 1.00 p05
pencil Dallas 221400 2.00 p06 folder
Dallas 123100 2.00 p07 case Newark
100500 1.00
INACTIVE_CUSTOMERS
ORDERS
cid cname city discnt c005 Nadir
Seattle 5.00 c006 Acme Kyoto 0.00 c007
Omega Montreal 6.50
ordno month cid aid pid qty dollars 1011
jan c001 a01 p01 1000 450.00 1012
jan c001 a01 p01 1000 450.00 1019 feb
c001 a02 p02 400 180.00 1017 feb
c001 a06 p03 600 540.00 1018 feb c001
a03 p04 600 540.00 1023 mar c001 a04
p05 500 450.00 1022 mar c001 a05 p06
400 720.00 1025 apr c001 a05 p07 800
720.00 1013 jan c002 a03 p03 1000
880.00 1026 may c002 a05 p03 800
704.00 1015 jan c003 a03 p05 1200
1104.00 1014 jan c003 a03 p05 1200
1104.00 1021 feb c004 a06 p01 1000
460.00 1016 jan c006 a01 p01 1000
500.00 1020 feb c006 a03 p07 600
600.00 1024 mar c006 a06 p01 800
400.00
AGENTS
aid aname city percent a01 Smith New
York 6 a02 Jones Newark 6 a03 Brown
Tokyo 7 a04 Gray New York 6 a05
Otasi Duluth 5 a06 Smith Dallas 5
23
Running ExampleThe CAP Database - contd

• CUSTOMERS A relation containing information about
  customers
• cid Customer identification number (PK)
• cname Customer name
• city Location of customer headquarters
• discnt Negotiated price discount
• AGENTS A relation containing information about
  agent employees
• aid Agent identification number (PK)
• aname Surname of agent
• city Location where agent is based
• percent Percentage commission agent receives on
  each sale

24
Running ExampleThe CAP Database - contd

• PRODUCTS A relation containing information about
  products for sale
• pid Product identification number (PK)
• pname Product description (name)
• city Location where product is warehoused
• quantity quantity on hand
• price Wholesale price per unit product
• ORDERS A relation containing information about
  orders
• ordno Order identification number (PK)
• month Month order was placed (current year)
• cid Customer who placed order (FK references
  CUSTOMER)
• aid Agent who took order (FK references AGENT)
• pid Product ordered (FK references PRODUCT)
• qty Quantity ordered
• dollars Total order amount