Database - Discussions

The Relational Data Model and

Relational Database Constraints

Dr. Buleje

Slide 1- 1

Slide 5- 2

Outline

 Relational Model Concepts

 Relational Model Constraints and Relational

Database Schemas

 Update Operations

Slide 5- 3

Relational Model Concepts

 The relational Model of Data is based on the concept of a

Relation

 We will review the essentials of the formal relational

model

 In practice, there is a standard model based on SQL

Slide 5- 4

Relational Model Concepts

 A Relation is a mathematical concept based on

the ideas of sets

 The model was first proposed by Dr. E.F. Codd of

IBM Research in 1970 in the following paper:

 "A Relational Model for Large Shared Data

Banks," Communications of the ACM, June 1970

Slide 5- 5

Informal Definitions

 Relation looks like a table of values.

 Contains a set of rows.

 The data elements in each row represent certain facts that correspond to a real-world entity or relationship

 Each column has a column header

Slide 5- 6

Example of a Relation

Slide 5- 7

Informal Definitions

 Key of a Relation

 The key

 In the STUDENT table, SSN is the key

Slide 5- 8

Formal Definitions - Schema

 The Schema (or description) of a Relation:

 Denoted by R(A1, A2, .....An)

 R is the name of the relation

 The attributes of the relation are A1, A2, ..., An

 Example:

CUSTOMER (Cust-id, Cust-name, Address, Phone#)

 CUSTOMER is the relation name

 Defined over the four attributes: Cust-id, Cust-name,

Address, Phone#

 Each attribute has a domain or a set of valid values.

Slide 5- 9

Formal Definitions - Tuple

 A tuple

 Domain

 A row in the CUSTOMER relation is a 4-tuple and would

consist of four values, for example:

 <632895, "John Smith", "101 Main St. Atlanta, GA 30332",

"(404) 894-2000">

 This is called a 4-tuple as it has 4 values

 A tuple (row) in the CUSTOMER relation.

Slide 5- 10

Formal Definitions - Domain

 A domain: definition

 Example

 A domain also has a data-type or a format defined for it.

 The attribute name designates the role played by a domain in a relation

Slide 5- 11

Formal Definitions - State

 The relation state

 Example: attribute Cust-name is defined over the domain of character strings of maximum length 25

 dom(Cust-name) is varchar(25)

Slide 5- 12

Definition Summary

Informal Terms Formal Terms

Table Relation

Column Header Attribute

All possible Column

Values

Domain

Row Tuple

Table Definition Schema of a Relation

Populated Table State of the Relation

Slide 5- 13

Example – A relation STUDENT

Slide 5- 14

Characteristics Of Relations

 Ordering of tuples in a relation r(R):

 Tuples are not considered to be ordered

 Appear to be in the tabular form.

 Ordering of attributes in a relation schema R (and of values within each tuple):

 We will consider the attributes in R(A1, A2, ..., An) and the values in t=<v1, v2, ..., vn> to be ordered .

Slide 5- 15

Same state as previous Figure (but

with different order of tuples)

CONSTRAINTS

DEFINITION: Constraints

They are of three main types:

1. Inherent or Implicit Constraints

2. Schema-based or Explicit Constraints

3. Application based or semantic constraints

Slide 5- 16

Slide 5- 17

Relational Integrity Constraints

 Constraints are conditions that must hold on all valid

relation states.

 There are three main types of (explicit schema-based)

constraints that can be expressed in the relational model:

 Key constraints

 Entity integrity constraints

 Referential integrity constraints

 Another schema-based constraint is the domain

constraint

 Every value in a tuple must be from the domain of its

attribute (or it could be null, if allowed for that attribute)

Slide 5- 18

Relational Database Schema

 Relational Database Schema:

 A set S of relation schemas that belong to the

same database.

 S is the name of the whole database schema

 S = {R1, R2, ..., Rn} and a set IC of integrity

constraints.

 R1, R2, …, Rn are the names of the individual

relation schemas within the database S

Slide 5- 19

COMPANY Database Schema

Relational Database State

 A relational database state DB of S is a set of

relation states DB = {r1, r2, ..., rm} such that each ri is

a state of Ri and such that the ri relation states satisfy

the integrity constraints specified in IC.

 A relational database snapshot or instance.

Slide 5- 20

Slide 5- 21

Populated database state

 Each relation: many tuples in its current relation state

 The relational database state is a union of all the

individual relation states

 Basic operations for changing the database:

 INSERT

 DELETE

 MODIFY

Slide 5- 22

Populated database state for COMPANY

Slide 5- 23

Summary  Presented Relational Model Concepts

 Definitions

 Characteristics of relations

 Discussed Relational Model Constraints and Relational Database

Schemas

 Domain constraints

 Key constraints

 Entity integrity

 Referential integrity

 Described the Relational Update Operations

 Insert

 Delete

 Modify