The Relational Data Model

1
The Relational Data Model
2
Relational DBMSs

• Data model proposed by Codd in 1970
• Database is a collection of tables
• Characterized by
• High degree of data independence
• Theoretical background for data structure and
  data manipulation
• SQL combines DDL and DML

3
Basic Concepts

• A relation is a set of unique tuples
• A tuple a set of data items (fields)
• A relation schema
• Relation name
• Relation structure (field names and data types)
• A relation instance
• Field access
• By name
• According to field order

4
Example

• Relation schema
• Book (bid char(3), title char(50), author
  char(30), price decimal(5,2), av_q integer)
• Relation instance

5
Alternative Terminology
6
Relation Properties

• A relation must have a unique name
• Each field contains exactly one atomic value
• Each field has a distinct name
• The order of fields has no significance
• Each tuple is distinct
• The order of tuples has no significance.

7
Basic concepts (continued)

• Field Domain
• Degree of a relation
• Cardinality of a relation instance
• Relational database
• Relational (database) schema
• An instance of a relational database

8
Integrity constraints

• Conditions that are specified on a database
  schema
• Legal instance an instance that satisfies all
  integrity constraints specified on the schema.
• Constraints are
• Defined with database schema definition or
  modification
• Enforced when database applications are run

9
Constraints classification

• Domain constraints
• Key constraints
• Entity integrity constraint
• Referential integrity constraints
• General constraints

10
Key Constraints

• A superkey
• A candidate key
• Irreducible subset of fields
• Distinct records have distinct values in key
  fields
• A primary key
• Each relation has a primary key
• A secondary key

11
Entity Integrity

• Null value - the content of the field is not
  defined
• All primary key entries are unique
• Null value is not allowed in the fields forming a
  primary key

12
Examples of violation of entity integrity
constraint
Instructor
IINSERT INTO Instructor VALUES (345, Robert,
Brown, 456789123, 905,
4561313) IINSERT INTO Instructor VALUES (null,
Robert, Brown, 456789123, 905,
4561313)
13
General Constraints

• General conditions that may be specified on a
  table
• Assertions
• Conditions associated with several relations from
  a database
• DBMS checks an assertion any time before relation
  instances involved in the assertion are modified

14
Referential Integrity Constraints

• Foreign key
• refers to the primary key from another relation
• A set of fields in one relation whose values must
  match primary key values from another relation
• Referential integrity constraint allows Null
  appearance in the foreign key fields

15
Example

• Customer (parent table)
• Order (dependent table)

16
Violation of Referential Integrity Constraint

• INSERT INTO Order VALUES(O14, C44, B22,
  4-Jan-02)
• DELETE FROM Customer WHERE CIDC11
• UPDATE Order O SET O.CIDC44 WHERE O.OIDO13
• UPDATE Customer C SET C.CIDC55 WHERE
  C.CIDC22

17
Enforcing Integrity Constraints

• Constraints are checked at each SQL command or at
  the end of transaction execution
• Key, entity integrity, domain and general
  constraints
• command violating a constraint is rejected

18
Enforcing Referential Integrity Constraints

• If insertion violates referential integrity
  constraint, it is rejected
• If deletion violates referential integrity
  constraint
• Delete all rows from Order that refer to the
  deleted row from Customer
• Reject the deletion
• Set CID field in Order table to the default value
• Set CID field in Order table to the Null value

19
Enforcing Referential Integrity Constraints
(continued)

• If update violates referential integrity
  constraint
• Reject the update
• Make changes and change corresponding fields in
  other relations. (For parent table updates only)

20
Queries (introduction)

• A query a request for data
• The result of the query is a relation
• Query languages
• Relational algebra
• Relational calculus

21
Relational Algebra

• Relational algebra defines basic operations on
  relation instances
• Results of operations are also relation instances
• Formal query language

22
Basic Operators

• Unary algebra operators
• Selection
• Projection
• Binary algebra operators
• Union
• Difference
• Cross-product

23
Additional Operators

• Can be expressed through 5 basic operators
• Join
• Intersection
• Division

24
Basic Example

• Student (sid char(4), sname char(8), age int,
  gpa decimal (3,1))
• Transcript (sid char(4), cid char(6), mark int)

25
Basic Example (Instance 1)

• Student S1

26
Basic Example (Instance 2)

• Student S2

27
Basic Example (Instance 3)

• Transcript T

28
Selection

• ?criterion(I)
• where criterion selection condition, and I- an
  instance of a relation.
• Result
• the same schema
• A subset of tuples from the instance I
• Criterion conjunction (AND) and disjunction (OR)
• Comparison operators lt,lt,,?,gt,gt

29
Projection

• Vertical subset of input relation instance
• The schema of the result
• is determined by the list of desired fields
• types of fields are inherited
• ?a1,a2,,am(I),
• where a1,a2,,am desired fields from the
  relation with the instance I

30
Example

• ?age(S1)

31
Nested Operators

• The result of a relational operator is a relation
  instance
• ?sname, gpa(?agelt20(S1))
• Is it the same as
• ?agelt20 (?sname, gpa (S1)) ?

32
Binary Operators

• Union-compatible relations
• The same number of fields
• Corresponding fields have the same domains
• Union of 2 relations
• Intersection of 2 relations
• Set-difference
• Cross-product does not require
  union-compatibility

33
Joins

• Join is defined as cross-product followed by
  selections
• Based on the conditions, joins are classified
• Theta-joins
• Equijoins
• Natural joins
• Outer joins

34
Theta Join

• R??Cond S ?Cond(R x S)
• Where Cond refers to the attributes of both
  relations R and S in the form of comparison
  expressions with operators
• lt,lt,,?,gt,gt

35
Other Types of Joins

• Equijoins
• the condition is a set of equalities connected by
  conjunctions
• Natural joins
• equalities are specified for all common fields
• The result does not have two duplicate fields

36
Outer Joins

• Left (natural) outer join R??S is a join with all
  tuples from R. If there is no matching tuple from
  S, missing values are set to Null
• Right outer join R??S keeps all tuples from S in
  the result
• Full outer join R??S keeps all tuples from both
  relations R and S

37
Useful Relational Algebra Expressions

• If relations R and S are union compatible R?S R
  S
• For arbitrary relations R, S, and T
• R?(S ?T) (R?S) ?T
• For any arbitrary relations R and S
• ?Cond(R?S) R? ?Cond( S),
• if3 Cond involves only the attributes from schema
  S

38
Simple Exercise

• Let A be a set of common fields in relation
  schemas for R and S.
• What is the difference between two of relational
  algebra expressions?
• R S
• and R R.A S.AS

39
Basic Example

• Student (sid char(2), Sname char(8), Y_of_B
  integer)
•  
• Transcript (sid char(2), cid char(6), mark
  integer )
•  
• Course (cid char(6), ctitle char(20), Pcode char
  (4))

40
Examples of Queries

• Find the IDs of the courses, which students are
  registered for
• Find the titles of courses, which students are
  registered for
• Find the names of students who have been
  registered for the course IT1010
• Find the students who are enrolled in ITEC
  program courses

41
Examples of Queries (cont.)

• Find the students who have been registered for at
  least 1 course
• Find the students who had been registered for
  ITEC or COSC programs
• Find the students who have been registered for
  both ITEC and BA courses
• Find the students who have been enrolled in at
  least two courses

42
Examples of Queries (cont.)

• Find the program(s), which courses student Brown
  is registered for
• Find student ID numbers for students who were
  born after 1983 and are not registered for COSC
  courses

43
Meaning of Relational Algebra Expressions

• ?mark(Transcript ?PcodeCOSC(Course))
• ?T1.Sid(?T1.Cid?T2.Cid(Transcript1
  T1.SidT2.Sid
• Transcript2))
• ?Sname(?GPAgt4.0(Student) ?Marklt75(Transcript
  ))

44
Views

• Virtual or derived relations
• Result of one or more relational operators
• Dynamic
• External schema consists of relations and views
• Support logical data independence!