The Relational Model 1

1
The Relational Model 1
Lecture 5

• Prof. Sin-Min Lee
• Department of Mathematics and Computer Science

Prof. Sin-Min Lee Department of Computer Science
2
History of Relational Model

• First proposed by a E.F. Codd in 1970. Codd
  proposed the Relational model in 1970.
• A relational model of data for large shared
  data banks.
• He linked the representation of data with that of
  mathematical sets.
• First research started at IBMs San Jose
  Research Laboratory. Prototype was called System
  R.
• Commercial RDBMSs started to appear in late
  1970s and early 1980s. Most well known is
  Oracle.

3
Codds Original Paper

• A Relational Model of Data for Large Shared Data
  Banks
• Communications of the ACM, Volume 13, Number 6,
  June 1970
• Lower level (basement) of the new Martin Luther
  King, Jr. Library
• Get to roll the shelves apart to access journals.

4
Codds Reasons

• Data independence from database implementation
  such as machine representation
• Natural structure of data
• Can be analyzed mathematically (Codd was a
  mathematician by training)

5
Alternative Network Model

• Charles A. Bachman 1973 ACM Turing Award Lecture
  The Programmer as Navigator
• Communications of the ACM, Volume 16, Number 11,
  November 1973, pp. 653-657

6
(No Transcript)
7
(No Transcript)
8
Data Models

• Codd suggests that any data model has three
  components
• the data structures
• the integrity constraints
• the data manipulation operators.

9
(No Transcript)
10
Relational Data Structure
Relation Attribute

• EMPLOYEE
• Emp Name Emp Sex Mgr Emp
• E1 Jones Male E65
• E6 Smith Male E28
• E28 Jones Female -

Heading Body
Female Male
Gender
Domain
11
The Domain
Employee
Emp E1 E2 E3
Name Red Brown Black
Mgr - E1 E1
Attributes
Person Name
E1, E2, E3,E4
Red, Brown Black, Blue
Domains
Emp
12
(No Transcript)
13
(No Transcript)
14
Seven Characteristics of a relation

• The name of the relation is different from all
  others.
• Each cell of the relation contains only one
  value
• Each attribute (or field) has a name that is
  distinct.
• All the values of a particular attribute are
  from the same domain.
• The order of the attributes makes no difference.
• There are no duplicate tuples
• The order of the tuples makes no difference.

15
Example of the Student table.
Primary Key
16
Other terms...

• Cardinality Number of rows
• Degree Number of columns

Degree 5
Cardinality 6
17
(No Transcript)
18
(No Transcript)
19
(No Transcript)
20
(No Transcript)
21
(No Transcript)
22
(No Transcript)
23
(No Transcript)
24
The Relational Data Model

• DATA STRUCTURES - domain, attribute, relation,
  tuple, primary key, degree, cardinality.
• INTEGRITY CONSTRAINTS - entity integrity and
  referential integrity.
• DATA MANIPULATION OPERATIONS - defined through
  relational algebra and equivalent relational
  calculus.

25
Entity Integrity

• No component of the Primary Key of a base
  relation is allowed to accept nulls.

Surname Red Black Red Black
Given Name John Fred
Salary 40,000 50,000 60,000 70,000
26
(No Transcript)
27
(No Transcript)
28
(No Transcript)
29
(No Transcript)
30
(No Transcript)
31
(No Transcript)
32
(No Transcript)
33
Foreign Key

• A foreign key is an attribute or attribute
  combination of one relation R2 whose values are
  required to match those of the primary key of
  relation R1 where R1 and R2 are not necessarily
  distinct. Note that a foreign key and the
  corresponding primary key should be defined on
  the same domain(s).

Employee
Dept
Emp e1 e2 e3
ename red blue brown
Worksfordept d1 d2
Dept d1 d2 d3
Dname Pay Tax Art
Foreign key
34
Referential Integrity

• If base relation R2 includes a foreign key FK
  matching the primary key PK of some base relation
  R1 then every value of FK in R2 must either
• (a) be equal to the value of PK in some tuple of
  R1, or
• (b) be wholly null.
• Note that PK and FK may comprise more than one
  attribute and that R1 and R2 are not necessarily
  distinct.

( Stated more simply a foreign key should be a
valid primary key value or null.)
35
Foreign Key Rules

• For each foreign key three rules need to be
  answered
• Can the foreign key accept nulls ?
• What should happen on an attempt to delete the
  target of a foreign key reference?
• What should happen on an attempt to update the
  target of a foreign key reference ?

Employee
Dept
Dept d1 d2 d3
Dname Pay Tax Art
Emp e1 e2 e3
ename red blue brown
Worksfordept d1 d2
36
Foreign Key Rules

• When should foreign key rules be checked ?
• Dept (Dept, Dname, Budget)
• Emp (Emp, Ename, Salary, WorksforDept)
• WorksforDept References Dept delete
  cascades, update cascades
• Depend (Emp, Dependname, Date-of-birth)
• Emp references Emp delete cascades, update
  cascades

37
Example of the Class table.
Primary Key
38
Foreign key example
39
Relational instances in the Student relation

• The content of the table (a grouping of rows) are
  called relational instances
• These instances are unordered, and no two rows
  can be exactly alike

A relational instance
40
Integrity Constraints

• All DBMS must have some form of ICs to prevent
  invalid data from being entered.
• Domain constraints specify the set of values
  which may be used for each field.
• Other constraints, such as key or tuple, may
  limit which values from the domain can be used
  for a given field in a given instance.
• Key constraints require that each set of fields
  in the key be unique for each entry.

41
Enforcing Integrity Constraints

• Each DBMS should have means to resolve invlaid
  entries such as
• What happens if an entry that duplicates a key
  entry is entered?
• What should be done if an entry of a foreign key
  is deleted?
• A foreign key is a key where at least one field
  depends on a field from a different table.
• What happens when an invalid entry is entered?

42
Relation schema

• Associated with each attribute is a set of
  values, called a domain, that can be assigned to
  the entry of a tuple corresponding to the
  attribute.
• A relation schema is a set of attributes.
• Example EMP Name, SSN, DeptName, Salary,
  Birthdate
• Convention EMP(Name,SSN,DeptName,Salary,Birthdate
  )

43
Relational DBMS Products
IBM Relational Products DB2 MVS/370
MVS/XA SQL/DS VM/CMS DOS/VSE QMF
front-end to DB2 and SQL/DS CSP application
development tool Numerous other RDBMS ORACLE
(SQLForms) INGRES from ASK Corp.
(OSL,ABF) AIM/RDB from Fujitsu INFORMIX VAXSQL
/Rdb from DEC NonStop SQL from Tandem
Microcomputer versions ORACLE INGRES dBase
IV microSQL practically all micro DBMS
44

• The relational model is based on set operations.
  Tables are sets of rows. The actual storage
  structure is hidden from the user. The relational
  model is just concerned with a logical view of
  the data, not the physical view. There are no
  pointers for the user to worry about. The only
  data are explicit values in tables. All data
  values in the cells of tables are Atomic (also
  known as Scalar). Exactly one data value and not
  a set or a repeating group is allowed in each
  cell.
• Relational databases are the most widely used in
  the world ( 90.)
• A mathematical viewpoint helped to shape a
  database industry.
• Future? Possibly Object Oriented Database model.