CPIS-241

Introduction to
Database
Management Systems

Create :Dr. HALIMA SAMRA

The Relational
Model
Objectives
Understand the basic concepts of database models
Learn the four common types of Database Model
Learn the relational model concepts
Learn important terminologies in relational model
Identify the relational model components
Understand the basic of data structure in relational model
Understand the meaning of data manipulation in relational model
Learn the purpose of data integrity and how is achieved in relational model
Learn the meaning and importance of keys, foreign keys, and related terminology
Understand how foreign keys represent relationships
Learn the meaning of functional dependencies
 Types of Database Model
• A database model shows the logical structure of a database, including the relationships and
constraints that determine how data can be stored and accessed. Most data models can be
represented by an accompanying database diagram.
There are four common types of database models that are useful for different types of data or information.

1. Hierarchical database model

2. Network model

3. Relational model

4. Object-oriented database model

https://www.lucidchart.com/pages/database-diagram/database-models
 1. The Hierarchical Model
One of the oldest database model. The data is organized into a tree-like structure , where each
record has a single parent or root.
This model was developed by IBM for Information Management Systems in the 60s and 70s, but they
are rarely seen today due to certain operational inefficiencies.
University

Advantages
Coll. Of Eng. Coll. Of Buss. Coll. Of Science.
• The model allows us easy addition and deletion of new information.
CS CV. Eng. Acct. IS Math. Stat.
• Data at the top of the Hierarchy is very fast to access.

Disadvantages

• Searching for data requires the DBMS to run through the entire model from top to bottom until the required
information is found, making queries very slow.

• This model support only one to many relationships, many to many relationships are not supported.

https://www.learntek.org/blog/types-of-databases/
 2. The Network Model
The network model is based on the hierarchical model by allowing many-to-many relationships
between related records, which implies the presence of multiple parent records. Network database
model organized data more like a graph and can have more than one parent node.

Advantages
• The network model can represent redundancy in data more effectively than in the
hierarchical model.
• The network model can handle the one to many and many to many relationships.

Disadvantages

• All records are maintained using pointers, so the entire database structure becomes very complex.
• Structural changes to the database are very difficult.

https://www.learntek.org/blog/types-of-databases/ https://www.lucidchart.com/pages/database-diagram/database-models https://prepinsta.com/dbms/data-models/

 3. Object-oriented database model
An object database is a system in which information is represented in the form of objects as used in
object-oriented programming. Object oriented databases are different from relational databases
which are table-oriented.
Advantages
• The object-oriented databases can handle the different types of data, for example,
pictures, voice video, including text, numbers, etc.
• Object-oriented databases provide us with code reusability, real-world modeling, and
improved reliability and flexibility..
Disadvantages

• The system more complex than that of traditional DBMSs.

• There is a Lack of support for security in Object oriented databases management systems .

https://www.learntek.org/blog/types-of-databases/ https://www.lucidchart.com/pages/database-diagram/database-models https://prepinsta.com/dbms/data-models/

 4. The Relational Model
The most common model is the relational model which sorts data into tables, also known as
relations, each made up of columns and rows.

Advantages
• In relational model, changes in the database structure do not affect the data access.
• A relational database supports both data independence and the concept of structure independence
which makes database design, maintenance, management, and use much easier than other models.
• It is easier to maintain security as compared to other models.

Disadvantages

• Relational Model is not suitable for huge database but suitable for small database.
• Data Integrity is difficult to ensure with Relational database.
• Ease of design can lead to bad design.
https://www.learntek.org/blog/types-of-databases/ https://www.lucidchart.com/pages/database-diagram/database-models https://prepinsta.com/dbms/data-models/
 The Relational Model
- The Relational Model simplified the design and implementation of databases.
- It represents how data is stored in Relational Databases.
- A relational database stores data in the form of relations (tables).

The Relational Model consists of the following three components.

⁻ Data Structure
⁻ Data Manipulation
⁻ Data Integrity
 The Relational Model
Data Structure
⁻ Data is organized in the form of tables (relations).

Important Terminologies in Relational Model

Record Oriented Relation Tuple Attribute

Table Oriented Table Row Column

Set Oriented File Record Field

 The Relational Model
Data Structure
Record Oriented Relation Tuple Attribute

Consider a relation (entity) STUDENT with attributes ST_ID, NAME, ADDRESS, PHONE and AGE
Terms Description ST_ID NAME ADDRESS PHONE AGE
Attributes are characteristics that define a relation (entity).
Attribute for example ; ST_ID, NAME 1 Mohammed 25 North St 900400 18

Relation A relation schema represents name of the relation (entity) with its attributes. for 22 South Rd
2 Fahad 900200 18
Schema example ; STUDENT (ST_ID, NAME, ADDRESS, PHONE and AGE)
15 North St
Each row in the relation (entity) is known as tuple. For example; the above relation 3 Bassam 900300 20
Tuple
contains 4 tuples.
10 South Rd
4 Ahmed 19
Relation Relation instance is the set of tuples of a relation (entity) at a particular instance of
Instance time. It can change whenever there is an insert, delete or update in the database.
The degree is the number of attributes in a relation (entity). The STUDENT relation
Degree
defined above has degree 5
Cardinality is the number of tuples in a relation (entity). The STUDENT relation
Cardinality
defined above has cardinality 4.
An unknown or unavailable value is called NULL value. It is represented by blank
Null Values
space. for example ; The phone number of STUDENT with ST_ID 4 is NULL.
https://www.geeksforgeeks.org/relational-model-in-dbms/
 The Relational Model
Data Structure
Table Oriented Table Row Column

Terms Description Characteristics

Is a two-dimensional structure composed of rows and columns,

Table that have specific characteristics. St_Id F_Name L_Name
⁻ Each table has a unique name
1002 Ali Salim

1003 Omar Khalid

St_Id F_Name L_Name

- Each row is unique
1002 Ali Salim
Rows Contain data about an entity. 1003 Omar Khalid
- No two rows may be identical
- The order of the rows is unimportant

St_Id F_Name L_Name

⁻ Each column has a unique name
1002 Ali Salim
Columns Contain data about attributes of the entity. ⁻ All entries are of the same data type
1003 Omar Khalid

⁻ The order of the columns is unimportant

St_Id F_Name L_Name

1002 Ali Salim

Cells A cell is the intersection of column and row. 1003 Omar Khalid
- Each cell represents a single data value
 The Relational Model
Data Structure
Student_table (Table view)
St_ID F_Name L_Name Age Address Phone

220220 Hana Ahmed 19 49 South Rd 9860023

220221 Amal Khalid 20 20 North Way 9860024

220222 Reem Saeed 19 15 Madinah Rd 9860025

Student_table (Record view)

St_ID: 220220
F_Name: Hana L_Name: Ahmed
Address: 49 South Rd Phone: 9860023
 The Relational Model
Check Point

Why do the following examples represent non-relational tables?

A B C
Employee_No Phone LastName Employee_No Phone LastName St_No Age LastName
100 335-6421, Abernathy 100 335-6421 Abernathy 100 20 Smith
454-9744 101 215-7789 Cadley 101 22 Jones
101 215-7789 Cadley 104 610-9850 Copley 104 19 Smith
104 610-9850 Copley 100 335-6421 Abernathy 105 20/05/2000 Wong
107 299-9090 Jackson 107 335-6421 Jackson 107 21 Jackson
 The Relational Model
Check Point

The Answers:
A B C
Employee_No Phone LastName Employee_No Phone LastName ST_ID Age LastName
100 335-6421, Abernathy 100 335-6421 Abernathy 20212 20 Smith
454-9744 101 215-7789 Cadley 20213 22 Jones
101 215-7789 Cadley 104 610-9850 Copley 20214 19 Smith
104 610-9850 Copley 100 335-6421 Abernathy 20215 20/05/2000 Wong
107 299-9090 Jackson 107 335-6421 Jackson 20216 21 Jackson
Cells of the table hold multiple values Two rows are identical The Age column contains multiple data
types
 The Relational Model
Data Manipulation
- Relational databases are typically written in Structured Query Language (SQL).
- The SQL is an international standard for creating, processing and querying databases and their
tables.
- A query is a request for data or information from a database table or combination of tables.

Example: Find the name of the customer with customer_id = 221-45-5890.

select customer.customer_name
from customer
where customer.customer_id = ‘221-45-5890’

Note: SQL will be studied in detail in Chapter 3

 The Relational Model
Data Integrity
- A relational DBMS must include features that maintain the accuracy of data.
Constraints
While designing the relational model, we define some conditions that must contain the data in the
database called constraints. These constraints are checked before any operation (insertion, deletion,
update) is performed in the database.
Domain Constraints: These are attribute level constraints.For example, if we put a condition the AGE>0
in the STUDENT relation, so inserting negative value will result in failure.

Entity Integrity
Each relation in the database should have at least one or set of attributes that defines a tuple (row) uniquely is called
the primary key of that relation (table). For example, ST_ID in STUDENT is the primary key.

https://www.geeksforgeeks.org/relational-model-in-dbms/
 The Relational Model
Data Integrity
The Primary Key
The primary key has two properties:
- It should be unique for all tuples(rows).
- It can’t have NULL values.

Composite Key
- A composite key is a key that contains two or more
attributes.
- When a combination of attributes is required to make
up a primary key the combination is also called a
composite key
 The Relational Model
Data Integrity
Referential Integrity
When one attribute of a relation(table) can only take values from another attribute of the same relation
or any other relation, it is called referential integrity.

Foreign Key
- A foreign key is a combination of one or more attributes that
can be used to identify a relationship between two tables

Example:
A relationship exist between the Department and Employee entities.
An Employee is tied to one and only one Department
and a Department may have many Employees working for it.

https://www.geeksforgeeks.org/relational-model-in-dbms/
 The Relational Model
Data Integrity
Referential Integrity
Example:
- This relationship is implemented by copying the attribute Depart_NO
into the Employee relation (table). Depart_NO is therefore called a
foreign key.
- To implement a foreign key into a table you have to place the primary
key of the one side of the relationship into the table of the many side of
the relationship.
1

- Referential integrity states that every value of a foreign key must

match a value of an existing primary key.
- Referential Integrity implies that a primary key record cannot be
deleted if it orphans a record in the many side of the relationship M
https://www.geeksforgeeks.org/relational-model-in-dbms/
 The Relational Model
Types of key (Summary)
Key Description
Is an attribute or set of attributes that can uniquely identifies all
Super attributes in a relation.
Key
Super key’s attributes can contain NULL values.

Candidate Is an attribute or set of attributes that can uniquely identify a tuple.

Key Candidate Key is a super key with no repeated attributes.

Is an attribute or set of attributes that can uniquely identify a tuple.

Primary Key
The Primary key should be selected from the candidate keys.
Is an attribute or set of attributes that can be used to identify a
Foreign relationship between two tables.
Key
It acts as a cross-reference between two tables because it points to the
primary key of another table.
https://www.javatpoint.com/dbms-keys#:~:text=Super%20key%20is%20a%20set,can%20also%20be%20a%20key.
 The Relational Model
Types of key (Summary)

Example

https://www.javatpoint.com/dbms-keys#:~:text=Super%20key%20is%20a%20set,can%20also%20be%20a%20key.
 The Relational Model
Check Point
Answer the following questions based on the information in the Department and Teacher table.

Can we delete the row of the DeptCode 001 from the Department_Table?

Can we delete the row of the DeptCode 005 from the Department_Table?

Why did Teacher Samar’s record in the Teacher_Table have an invalid entry?
 The Relational Model
Check Point
The Answers:

Can we delete the row of the DeptCode 001 from the Department_Table?

No, it will result in an orphaned record.

Can we delete the row of the DeptCode 005 from the Department_Table?

YES, there are no related record in the Teacher_Table.

Why did Teacher Samar’s record in the Teacher_Table have an invalid entry?

Because DeptCode 003 does not exist in Departement_Table.

 The Relational Model
Check Point
Answer the following questions based on the information in the Department and Teacher table.
What is the primary key in the Department_Table?
What is the primary key in the Teacher_Table?
The DeptCode column in the Teacher_Table is:
A. Primary key
B. Foreign key
C. Candidate key
The DeptCode column in the Department_Table is:
A. Primary key
B. Foreign key
C. Candidate key
The Email column in the Teacher_Table is:
A. Primary key
B. Candidate key
C. Foreign key
 The Relational Model
Check Point
The Answers:
What is the primary key in the Department_Table? DeptCode
What is the primary key in the Teacher_Table? Teacher_ID
The DeptCode column in the Teacher_Table is:
A. Primary key
B. Foreign key
C. Candidate key
The DeptCode column in the Department_Table is:
A. Primary key
B. Foreign key
C. Candidate key
The Email column in the Teacher_Table is:
A. Primary key
B. Candidate key
C. Foreign key
 The Relational Model
Functional Dependency
Functional Dependency: A relationship between attributes in which one attribute (or group of attributes)
determines the value of another attribute in the same table

Example:
The price of one cookie can determine the price of a box of 12 cookies. (CookiePrice, Qty) BoxPrice

Determinants: The attribute (or attributes) that we use as the starting point (the variable on the left side of the
equation) is called a determinant.
(CookiePrice, Qty) BoxPrice

Dependent
Determinant

https://www.geeksforgeeks.org/relational-model-in-dbms/
 The Relational Model
Primary Key and Functional Dependency
A primary key of a relation will functionally determine all other attributes in the row.

Determinant Dependent

(EmployeeID) (EmpLastName, EmpPhone)

(ProjectID) (ProjectName, StartDate)

https://www.geeksforgeeks.org/relational-model-in-dbms/
 References
1. KROENKE and AUER - DATABASE CONCEPTS (6th Edition) Copyright © 2013 Pearson Education, Inc. Publishing as
Prentice Hall

2. Rob,P, Morris,S & Coronel,C.& Database Systems, Design, Implementation, and Management, 10th Ed,
Thomson Course Technology ISBN: 13:978-1-111-96960-8

3. Silberschatz, Korth and Sudarshan Database System Concepts, 5th Ed.

4. https://www.geeksforgeeks.org/relational-model-in-dbms/
5. https://www.javatpoint.com/dbms-keys#:~:text=Super%20key%20is%20a%20set,can%20also%20be%20a%20key.