Module 2 Relational Model

1
Module 2Relational Model

• Teknik Informatika Fakultas Teknik
• Universitas Dr. Soetomo Surabaya

2
Module 2 - Motivation

• The Relational Model introduced in 1970, has been
  the turning point for modern database systems
• The Relational Model is the basis for several
  commercial Database Management Systems, for
  example, Oracle, IBMs DB2, Microsoft Access
• The Relational Model has a sound theoretical basis

3
Module 2 - Contents

• The Relational Data Model
• Introductory Concepts
• Data Model
• Schemas and Instances
• The Relational Model
• Basic Concepts
• Relational Schemas and Instances
• Characteristics of relations
• Relational database design and Constraints

4
Data Model

• An abstract view of data that excludes many
  details that are either too complex or not of
  interest to the users
• Comprised of logical concepts, e.g., objects or
  entities, their properties and their
  interrelationships
• May be easier for most users to understand

5
Categories of Data Model

• The data may be modeled from different
  perspectives

Users perspective How the user views the data
Conceptual perspective
Storage perspective Describes the details of how
the data is stored in the computer
6
Types of Data Models

• Relational Data Model Focus of this
  lecture
• Most commonly used model for traditional
• applications, and therefore basis for many
• commercially successful DBMSs
• Legacy Data Models
• Mostly used in the past, for example Network
• and Hierarchical Data Models
• Object Data Models
• New paradigm, in response to applications
• with complex data and the success of object
• oriented concepts in software engineering

7
Schemas and Instances

• Most data models have the concept of schema and
  instance
• Schema
• A Schema is the meta-data, or data describing
• data
• Schema is specified during database design,
  and
• is not expected to change frequently
• Instance
• An Instance is the data in the database at a
• particular time
• Instances are created during data updates and
• change frequently

8
Module 2 - Contents

• The Relational Data Model
• Introductory Concepts
• Data Model
• Schemas and Instances
• The Relational Model
• Basic Concepts
• Relational Schemas and Instances
• Characteristics of relations
• Relational database design and Constraints

9
Relational Data Model

• Introduced by E.F. Codd in 1970
• Many DBMS products based on this model
• (but few completely implement it)
• Based on sound theoretical foundation
• Simple and uniform data structure
• Relation

10
Relational Model Concepts

• Relations
• Domains
• Attributes
• Tuples

11
Relations

• A Relation is the main construct for
• representing data in the Relational Model
• Informally, a relation
• is a set of records
• is similar to a table
• with columns and rows

Columns
rows
12
Relations, not Tables

• The term Table is used interchangeably with
  Relation
• Every relation is a table
• Every table is not necessarily a relation !
• Relations have specific properties, based on the
  mathematical set theory

Not a Relation !
13
Relation Components
14
Domains

• A Domain D is a set of atomic values
• An atomic value is indivisible (as far as the
• relational data model is concerned)
• Each domain has a data type or format
• Examples
• Domain of Names
• Joe Smith, Alan Yates, Bob Lovell, Jane
  Austin, ...
• Domain of Degrees
• Phd, MSc, BInfTech, BSc, MInfTech,

15
Domain Types

• integers
• real numbers
• fixed or variable length character strings
• date
• time stamp
• currency
• sub-range from a data type, e.g. 1_ Grade _ 7
• enumerated data type, e.g. Male, Female

16
Example Domains

• Indonesian telephone numbers
• Values the set of telephone numbers valid in
  Indonesia
• Format the digits 62 followed by 9 digits 0-9
• Auto registration numbers
• Values The set of valid vehicle registration
  codes (i.e., arbitrary combinations of characters
  and digits)
• Format 6 characters (either alpha or digits but
  no Qs allowed)

17
Attributes

• Each attribute A is the name of a role played by
  some domain D in the relation named R
• The number of attributes in a relation R is
  called the degree of R
• Example StudentNo is an attribute name

18
Domains / Attribute Restrictions

• Same attribute name does not necessarily imply
  same domain

Lecturer
Subject
Domains for Subject.CODE and Lecturer.CODE
must differ, even though Attribute names are the
same
19
Domains / Attribute Restrictions

• Different attribute name does not necessarily
  imply different domain

Domains for EID and SID are the same but the
Attribute names must differ
20
Tuples

• Each Tuple t is an ordered list of n values t
  ltv1,v2, , vngt where each value vi (1 i n)
  is an element of the corresponding domain of
  attribute Ai or a special value called null
• t is called an n-tuple
• Example
• (254, John, Smith, 45K, 3453-2543,M)

21
Relation Components - Review
22
Relation Schema and Instance

• Relation Schema
• Denoted by R(A1,A2,A3,,An), includes a
  relation
• name R and list of attributes A1, A2, An
• Integer n is termed degree of the relation
• Example relation schema of degree 4
• STUDENT(Name, Age, Id, Address)
• Relation Instance
• A relation instance r of the relation schema
  R,
• denoted by r(R), is a set of n-tuples r
  t1,t2, ,
• tm.

23
Example Schema and Instance
Schema description of the relation
Instance data within the relation
24
Ordering Characteristics

• Ordering of Tuples
• Ordering of Values in a Tuple

25
Ordering of Tuples

• Relations are sets of tuples
• Mathematically, elements of a set have no implied
  order
• Semantically, when reasoning with relations,
  e.g., when formulating queries, order is
  irrelevant
• Physically, tuples reside on blocks of secondary
  storage, which have a partial ordering, hence
  tuples have an ordering

26
Ordering of Tuples
Subject-Lecturer-1
Subject-Lecturer-2
Same Relation
27
Ordering of Values within a Tuple

• n-tuple is an ordered list of n values
• Syntactically, all tuples in a relation
  havevalues in the same order
• Semantically,the order chosen is irrelevant, as
  long as the correspondence between the attributes
  and the values can be maintained

28
Ordering of Values within a Tuple
Subject-Lecturer-3
Subject-Lecturer-4
Same Relation
29
Module 2 - Contents

• The Relational Data Model
• Introductory Concepts
• Data Model
• Schemas and Instances
• The Relational Model
• Basic Concepts
• Relational Schemas and Instances
• Characteristics of relations
• Relational database design and Constraints

30
Relational Database Design

• Relational Database
• A collection of relations with distinct
  relation
• names
• Relational Database Schema
• A collection of schemas for the relations in
  the
• database
• Relational Database Design
• Process of capturing the semantics of an
• application, and translating it into a
  relational
• database schema

31
Importance of Design

• Poor design can lead to redundant information in
  tuples and update anomalies
• Can also result in inability to represent
  information and consequently, loss of information
• Conceptual modeling, and other formal techniques,
  can assist a designer in obtaining good design
  characteristics

32
What are we designing?
Relations
33
Simple Design Example

• Good database design is not automatic!

34
Naive Implementation

• Consider these updates
• Printing department moved to Ipswich
• Smith is new manager of Printing Department

35
Better Implementation
Relation Department
Relation Employee

• Again consider these updates
• Printing department moved to Ipswich
• Smith is new manager of Printing Department

36
Implementation of Constraints

• DBMS must enforce constraints
• User is not permitted to remove department if
• employees remain
• User is not permitted to insert employee
• without reference to an existing department
• (in relation Department)
• User is not permitted to delete an employee
  
• who is a manager

37
Database Integrity Constraints

• Integrity constraints are specified on the
  database schema
• They must hold on every instance of that schema,
  as well as on transitions of the schema
• Some integrity constraints are enforced by the
  DBMS itself
• Some are enforced using procedural logic

38
Categories of Integrity Constraints

• Static and Dynamic constraints
• Single and Multiple Relation constraints
• Structural and Semantic constraints

39
Database Integrity Constraints

• Restrictions on data that can be specified on a
  relational database schema
• Structural, Static, Single Relation
• Domain Constraints
• Key Constraints
• Entity Integrity Constraints
• Structural, Static, Multiple Relation
• Referential Integrity Constraints

40
Domain Constraints

• A Domain is a set of atomic values
• Each attribute in a relation will belong to some
  domain
• Data types associated with domains
• integers, real numbers, fixed or variable
  length,
• character strings, date, time stamp,
  currency,
• subrange from a data type, e.g. 1_ Grade
  _ 7,
• enumerated data type, e.g. Male,
  Female

41
Example Domain Constraints
Atomicity of Values in the Tuples
Non-Atomic Values
Atomic Values
Non-Atomic Values
42
Key Constraints

• All tuples in a relation must be distinct, that
  is no two tuples can have same values for all
  attributes E uniqueness constraint

Violation of Uniqueness Constraint ?
43
Notion of a Superkey

• A Superkey is a subset of attributes (SK) of a
  relation schema R, such that for any two tuples,
  ti and tj in a relation state r of R ti SK ¹
  tjSK
• Every relation has at least one superkey the
  set of all its attributes
• Superkey can have redundant attributes, that is,
  by removing some attributes, the uniqueness
  constraint is still maintained

44
Example Superkey
Superkey for the Relation STUDENT (Name,
StudentNo, Sex, Degree) (Name, StudentNo,
Sex) (Name, StudentNo, Degree) (StudentNo,
Degree)
Which of these attributes are redundant ? Is
(StudentNo) the smallest set of attributes that
uniquely identify a tuple in the relation STUDENT
?
STUDENT
45
Notion of a Key

• K is a key in a relation schema R if
• K is a Superkey of R, and
• removing any attribute from K leaves a set of
• attributes K', where K' is not a superkey of
  R,
• that is, K does NOT maintain the uniqueness
• constraint
• Key is a minimal Superkey
• smallest set of attributes that uniquely
  identify
• a tuple (StudentNo) is the smallest set of
• attributes that uniquely identify a tuple in
  the
• relation STUDENT

46
Example Key
WORKS-IN
47
Example Key
ENROLMENT
48
Characteristics of Keys

• Value of key attributes uniquely identify a tuple
  in a relation
• Key constraints hold on every relation instance
• Name cannot always be used as key
• A schema may have more than one key
• Each is called a candidate key
• One is selected as the primary key

49
Entity Integrity Constraint
No primary key can be null How would you
distinguish between John Smith and Janet
Smith if the primary key was missing?
50
Referential Integrity Constraint

• Key and Entity Integrity constraints are
  specified on individual relations
• Referential Integrity constraints are specified
  between two relations
• EMP Eno, Name, Salary, SuperEno, DeptNo
• DEPT Dnumber, Dname, Dlocation

51
Foreign Keys

• A set of attributes FK in relation schema R1 is
  a foreign key if
• the attributes of FK have the same
• domain as the the primary key attributes
• PK of another schema R2
• t1FK t2PK or t1FK is null
• FK is said to reference PK

52
Example Foreign Key

• Every employee is assigned to one department
• DeptNo is a foreign key of EMP referencing
  DEPT
• Every Department has a Manager. The manager is
  also an employee
• Manager is a foreign key of DEPT referencing
  EMP
• Every Employee has a supervisor who is also an
  employee
• SuperEno is a foreign key of EMP referencing
  EMP

53
Other Integrity Constraints

• General class of constraints that cannot be
  enforced by the preceding constraints
• Semantic Constraints
• The salary of an employee should not exceed
• the employees supervisors salary
• The maximum number of hours that an
• employee can work on a project is 56
• Transition Constraints
• The salary of an employee can only increase
• Often implemented in a constraint specification
  language (SQL3) using triggers and assertions

54
Database Schema and Instance

• Relational Database Schema
• set of relational schemas R1, R2, ..., Rn
• set of integrity constraints
• Relational Database Instance
• set of relation instances r1, r2, ..., rn
  such
• that each ri is an instance of Rj
• the ri relations satisfy the integrity
  constraints

Relational Database schema (current) instances
55
Designing a Relational DB

• Deciding which attributes belong in each relation
• Choosing appropriate names for each relation
• Specifying domains and data types for the
  attributes
• Identifying candidate keys and selecting a
  primary key for each relation
• Specifying all foreign keys

56
Constraints and Operations

• Enforcement of integrity constraints ensures that
  the database remains consistent
• Changes to the database must not violate
  integrity constraints (leave the database in an
  inconsistent state)
• If a Database update is submitted to the DBMS
  that would violate integrity, it must be rejected

57
Constraints Insertion

• Integrity constraints can be violated by
  inserting a new tuple
• Student with StudentNo already exists
• Age is old instead of 18
• The insert can be rejected, or the reason for
  rejection corrected

58
Constraints Deletion

• Referential integrity can be violated if the
  tuple being deleted is referenced by Foreign Keys
  from other tuples
• deleting a DEPT while there are still
  EMPloyees
• working in that DEPT
• The deletion can be rejected, cascaded or the
  referencing attribute values can be modified

59
Constraints Modification

• Non key values
• domain check
• Primary key
• similar to performing a delete and an insert
• Foreign key
• DBMS must ensure new value refers to
• existing tuple in referenced relation

60
Module 2 - Review

• Key Words Relation, Attribute, Domain, Tuple,
  Schema, Instance, Constraint
• Key points
• Defining a database includes the specification of
  the schema
• Each update to the database causes a new database
  state
• The DBMS ensures that every database instance (or
  state) is valid/legal, and that every state
  transition is also legal

61
Recommended Readings

• Elmasri Navathe
• Chapter 7 (7.1 - 7.3)

62
Next ...

• Module 3
• Entity Relationship
• Model