1

Chapter Two

The Relational models

 Relational Data Model
• Objective: -
▫ We will see basic and terminology associated with
relational model.
▫ We will learn theory behind relational model
▫ Properties of database relations.
▫ How to identify candidate, primary and foreign keys.
▫ The meaning of entity integrity and referential integrity.

2
 Introduction
• Data model is a notation for describing data or
information. The description is generally consists of
▫ Structure of the data
▫ Operation on the data e.g. Queries to retrieve, to
modify information.
▫ constraints on the data:- limitations on what the
data can be

3
 Introduction
• Relational data model
▫ RDBMS is the dominant database software in use
today.
▫ RDBMS is based on the relational model proposed by
a paper written by E.F. Codd. in 1970.
▫ The major objectives of Relational Database specified
on the paper are
 To allow a high degree of data independence (application
program is independent of internal schema).
 To deal with semantic, inconsistency and redundancy
problems.
 To enable set oriented manipulations languages. E.g. SQL
4
 Relational Model
▫ The relational model represents data as a two
dimensional table called a relation.
▫ Each relation represents a single real world entity
and has name and is made up of a named
attributes(columns).
▫ Each tuple(row) contains one value per attribute.
▫ A relational database consists of a collection of
tables.
▫ The great strength of relational model is its simple
structure.
5
 6

A Relational model…Example
7
 Customer Relation

• The rows each represent a bank Customer

• columns each represent a property which describe the
Customer.

8
 RDM Terminology
• Relation:
▫ Relation is a table with rows and columns.
▫ In relational model database is perceived by the user as tables.
(Logical structure).
▫ But physically the database is stored with different storage
structures.
▫ A relation represents a single real world entity and has it‟s own
unique name.
▫ E.g. Student, course, Bank_account etc.
• Attribute-
▫ Is a named column of a relation
▫ Are characteristics of the object that is to be represented by the
relation.
▫ It tells about the values in the column below.
9
 More about Attribute
• Attribute Types:-attributes can be divided into two
▫ Main attributes:-
 are attributes of an entity which have a constant
value.
 They have almost no probability of change.
 Example F_Name, L_name, Birth_Date, Sex, … etc of a
student have constant values which cannot change
through time.
▫ Derived Attributes:-
 are types of attributes which are derived from an
main attributes of the entity.
 Example: Age of a student can be derived from
10
birthDate and current day attributes.
 RDM Terminology
• Domain
▫ is set of permitted (allowable) value for an
attribute.
 E.g. Numeric, string, date, currency, Autonumber
▫ Every attribute in a relation is defined on a
domain.
 E.g. the domain for attribute title is string.
▫ More than one attributes can have the same
domain.
▫ The domain concept is important, because it allows
the user to define in a central place the meaning
11
and source of values that attributes can hold.
 RDM Terminology
▫ The domains of all attributes must be atomic – the
values must be indivisible i.e. a simple, single value,
that cannot be further divided.
 The set of all possible names of people – is
atomic.
 The set of full names (first name and
father‟s name) – is not atomic.
▫ Null value – is a special value that can be a member
of any possible domain. Null means the actual value is
unknown or does not exist.
 E.g. a student may not have email-address.
12
 RDM Terminology
• Tuples:-
▫ A tuple is a row of a relation.
▫ has one component for each attribute of the relation.
▫ The change in the order of tuples doesn‟t change the
meaning of the relation.
• Degree
▫ The degree of a relation is the number of attributes it
contains.
 Uniary, Binary, n-ary
• Cardinality
▫ The cardinality of a relation is the number of tuples it
contains.
13
 • A relation has the following properties:
1. the relation has a name that is distinct from all other
relation names in the relational schema;
2. each cell of the relation contains exactly one atomic
(single) value;
3. each attribute has a distinct name;
4. the values of an attribute are all from the same
domain;

6. the order of attributes has no significance;

7. the order of tuples has no significance, theoretically

14
 Schema
• Relational Schemas
▫ The name of a relation followed by the set of
attributes for a relation is called the schema for that
relation or relational schema.
Customer(cust_Id, name, street, city)
• Database Schema
▫ The database schema is the logical design of the
database.
▫ In a relational database the schema defines the
tables, the fields in each table, and the relationships
between fields and tables.
15
 Example:- University Database

• Schema Diagram
 Example:- University Database
• Database schema of a university database with five
relations
Student-schema (student_id, student_firstname,
student_fathersname, Program_code)
Program-schema (Program_code, Program_name,
Program_description)
Course-schema(course_code, course_name,
course_description, credit_hours)
Student-Course-Schema (student_id,course_code, Grade)

17
 Instances
• Relation Instance
▫ The content of the relation at some moment.
▫ Is the set of tuples and data which exist in a table at a
specific moment
▫ Database systems only maintain one version of any
relation
• Database Instance
▫ A database instance is a snap-shot (picture) of the data
in the database at any given instant in time.
▫ it is the instance of the data in all the tables in the
database.

18
 Examples:
Relational Instances

19
 Keys
• In a relation no two tuples can have exactly the same
values for all the attributes. (why?)
• We need some way to distinguish the tuples from one
another.
• A key is an attribute or set of attributes in a relation
that uniquely identifies each tuples in the relation.
• The values of those key attributes of a single instance
of tuple must uniquely identify that instance.
• Types of keys
▫ Super key
▫ Candidate key
▫ Primary key
20
 Super Key
• Super key can be an attribute or a set of attributes.
• It is a key made of maximum combination of attributes.
▫ For example: For a relation “Student”
Student(stud_id, F_name, L_name)
▫ Examples of superkeys in this table would be
 {stud_id, F_name}
 {stud_id,L_name}
 {stud_id,F_name, L_name} or
 {stud_id}
▫ Superkey may contain additional attributes that are not
necessary for unique identification.
21
 Candidate Keys
• In a real database we don't need values for all of
those columns to identify a row.
▫ We only need, per our example, {stud_id}.
• If a super key is having only one attribute, it is
automatically a Candidate key
• A candidate key is a superkey for which no subset is
itself a superkey. (i.e. stud_id)
• It is a combination of attributes which can be a key by
themselves individually.
E.g. Department(Dept_Id, Dept_Name, specialization)

Dept_Id and Dept_Name can be the candidate keys

22
 Primary Keys
• A primary key is a candidate key chosen to be the
main way to uniquely identify tuples in the relation.
• For each records(tuples) in the relation the Primary
key value should be unique.
▫ For example: Dept_Id
 Dept_Id must be unique for every department.
• For example: stud_id of student
• Some times a candidate key may contain more than 1 attribute.
• And more than one attributes can be chosen as a primary key
• This is called composite-primary-key

23
 Primary key
• There are two types of primary key
▫ Simple Primary key
 Is a primary key made up of a single field only.
 E.g. Student_Id in student relation.
▫ Composite primary key
 Is a primary key composed of more than one attribute.
 E.g. Grade Relation
Grade
Stud_Id Course_Code Grade
001 GIS231 A

24
 Relationships
• Example: university-database
 Relationships
• Relationship is the connection between different
relations(tables) in a database.
• For the university database given above, there are
connections between the data in the different relations.
▫ In the university database schema, relationship is
represented as a line connecting each relations together
• A row in one table can be related to a row(s) in another
table(s).
• These connections are called relationships.
• How to create a relationship?
▫ 1:1(One to one)
▫ 1:M(One to Money)
▫ M:M(Many to many)
26
 Relationships
• Relationships are used to avoid duplication of data in
different tables.
▫ E.g. If there is a new grade for a student, it will be
added to ‘studentCourse’ relation with the relevant
‘studentId’ and ‘courseCode’.
▫ The other detailes about the student and the course can be
found using studentId and coursecode from their home
relations.
▫ Why do we need an additional relation ‘studentCourse’?
 Answer: because student and course relations has Many to
Many relationship. (It will be discussed more in chapter 5)
27
 Foreign Key

• Foreign key: is an attribute in one relation which is

linked with a primary key in another table.
▫ For example: in the relation “Grade”, Stud_Id is a
primary key in “Student” relation. But is a foreign key in
“Grade” relation.
▫ Prog_code is a foreign key in student relation. But it is a
primary key in program relation.

• Foreign key is used to link tables together and create

relationship.
28
Properties of Relational Database Model

29
 Constraints
• Constraints are rules that restrict the possible
values that can go into a relation.
 NULL constraint
 Entity Integrity constraint
 Referential Integrity constraint
 General constraints : Domain Constraint

30
 NULL
• Null Represents a value for an attribute that is
currently unknown or is not applicable for this tuple.
• Nulls are a way to deal with incomplete or
exceptional data.
• Null is not Zero (0).
• Null is not the same as a zero numeric value : a null
represents the absence of a value.

31
 Entity Integrity Constraints
• Entity Integrity is enforced using Primary
Key.
• Entity integrity rule states that
▫ Each relation must have a primary key.
▫ A primary key value cannot be NULL
 Nulls can cause implementation problems.

32
 Referential Integrity Constraints
▫ If primary key value of one relation is used in
another relation, it will become a Foreign Key for
that relation.
▫ In other words, a Foreign Key is an attribute in one
relation which is a primary key in another relation.
▫ Foreign key‟s are used for relationship purpose.
• Referential integrity states that:
▫ If a foreign key exists in a relation, either the foreign
key value must match a primary key value of some
tuple in its home(referenced) relation or the foreign
key value must be wholly null.
▫ Referential Integrity is enforced using Foreign Keys.
33
 General constraints
• Domain constraint:
▫ every attribute has a domain.
▫ A restriction on the value to be entered in these
attributes is called domain constraint.
• Other Constraints
▫ There can be additional rules specified by the
users or database administrators of a
database that define or constrain some aspect
of the enterprise.
 For example: The total mark for a course must not
34
be greater than 100.
35
Chapter 4 – revision questions
1. In relational database data is represented by using
_____________.
2. ________________ of an attribute is the set of
permitted value for an attribute.
3. If we don‟t know the actual value of an attribute we
use ________ value.
4. What does it mean the domains of all attributes must
be atomic?
5. In relational database one relation can be used to
represent more than one entity. (True/False)
Revision Example
• Student
Stud_id First_name Last_name Sex CGPA Program_id
107 Getachew Yiheyis M 2.82 P003
113 Mesfin Negesse M 3.51 P003
106 Leyla Endris F 3.21 P001
109 Edlawit Amare F 2.37 P002

• program
Program_id Program_name
P001 Natural Resource Management
P002 Georgraphic Information Science
P003 Land Administraiton and Surveying
Revision Question
Based on the given relationship between Student and Program
given above answer the following questions
1. The name of the two relations are ___________ and _______
2. The relation „student‟ has _______ number of attributes and
______ number of tuples.
3. List all the attributes of the relation student and program and
identify the possible domain of each attributes.
4. What are the possible primary key attribute for the relations
student and program?
5. What is the foreign key attribute in student relation?
6. Is there any foreign key attribute in program relation?
7. What is the degree & cardinality of the relation student and
program?
8. Write the relational schema of the student and program
relations.
 Example:- University Database
Create the university database based on the database-
schema given below
 E.g. University Database
• Discuss the following revision topics using the university
database example.
▫ Relation, attribute, domain
▫ Candidate key
▫ Primary key (simple/composite)
▫ How relationship is created in relational mode.
▫ Foreign key
▫ Entity Integrity Constraints
▫ Referential Integrity Constraint
▫ Relational schema
▫ Database Schema

40
 Sample data: Department

41
 Discussion …

• Add a column named “Telephone Number” and

discuss the probability the value for it not Atomic.

42
 Sample data: Student

43
 Sample data: Course

44
 Sample data: studentCourse (Grade)

45
 Review questions

What is the primary key for the relation Grade?

1) _________ is an attribute used to uniquely identify

each tuples of a relation.
2) What does NULL value indicates?

46