Transcript Ch no 03 Relational Database Model

By ILTAF MEHDI (MCS, MCSE, CCNA)
An introduction to Relational Database
Distribution of Marks
For
Second Semester
External
Internal Sessional Evaluation
Evaluation
Assignment
/Project
Quizzes
Class
Attendance
Mid-Term
Test
Total
Sessional
Terminal
Exam
Final
Evaluation
10
10
5
25
50
50
100
Remember:
1
By ILTAF MEHDI (MCS, MCSE, CCNA)
An introduction to Relational Database
Course Contents
Chapter No
Chapter Name
1.
Database Foundation OR An Overview of Database
Management System (DBMS)
2.
Semantic Modeling OR Entity Relationship Model (ERD)
3.
An introduction to Relational Database
4.
Normalization
5.
Relational Algebra
6.
An Introduction to Structured Query Language (SQL)
Page No
2
By ILTAF MEHDI (MCS, MCSE, CCNA)
An introduction to Relational Database
DATABASE-1
Chapter No: 03
Chapter No: 03
COURSE TITLE:
COURSE INSTRUCTOR:
ILTAF MEHDI
3
By ILTAF MEHDI (MCS, MCSE, CCNA)
An introduction to Relational Database
DATA MODEL
Def:“A data model is a representation of data
about entities, events, activities and their
relationship”.
 A data model represents an organization itself.
 The purpose of data model two-fold: First “to
represent data” and second “to be
understandable”.
 If a data model accurately and completely
represent required data and is understandable
then it can be used in some application.

4
By ILTAF MEHDI (MCS, MCSE, CCNA)
An introduction to Relational Database
TYPES OF DATA MODEL
There are basically three types of data model:
1) Hierarchical Data Model
2) Network Data model
3) Relational Data model
5
By ILTAF MEHDI (MCS, MCSE, CCNA)
An introduction to Relational Database
TYPES OF DATA MODEL
Relational Data Model:
 It also called Relational Database Model.
 The Relational Data Model is the most recent and
best of the three database models.
 It was developed by C.F Codd in 1970.
 This model represents database as a set of
normalized relations.
 “In Relational Model data are represented in the
form of tables with rows and columns”.
1)
6
By ILTAF MEHDI (MCS, MCSE, CCNA)
An introduction to Relational Database
RELATIONAL DATABASE MODEL
DEPARTMENT
EMPLOYEE
Deptt-No
Deptt-Name
DepttLocation
Emp-No
EmpName
EmpPhone #
Deptt-No
D100
IT
West
E10
Mehdi
336
D100
D101
Management
East
E11
Danish
448
D101
D102
English
South
E12
Nasir
500
D102
Relational Database Model
Notice that there is no physical data structure representing relationship
between two tables (relations). Instead relationships are represented logically
by the values that are stored within table’s columns.
For example: In above figure, the Department-Number (Deptt-No) for each
Employee is stored in Employee table.
7
By ILTAF MEHDI (MCS, MCSE, CCNA)
An introduction to Relational Database
PROPERTIES OF RELATION (TABLE)
The above tables have the following properties:
1. Each column contained values about some
attribute.
2. Each column has distinct name.
3. Each row of a table must be unique, means,
that no two rows have the identical data.
4. Each row in the relation is called TUPLE.
5. Each column in the relation is called DOMAIN.
8
By ILTAF MEHDI (MCS, MCSE, CCNA)
An introduction to Relational Database
PROPERTIES OF RELATION
6. Record:
 “The combination of more than one fields about a single
entity is called record of that entity”.
For example:
 The record of STUDENT may have fields Roll-No, Name,
Father Name, Gender, Address, Program and Marks. Then
this combination of fields is called the record of a particular
STUDENT.
STUDENT FILE
Roll-No Name
F-Name
Gender
Address
Program Marks
100
Abdullah
Zain
Male
Kabul
BCS
40
1st RECORD
101
Spogmay
Iqbal
Female
Kabul
BCS
47
2nd RECORD
FIELDS
9
By ILTAF MEHDI (MCS, MCSE, CCNA)
An introduction to Relational Database
PROPERTIES OF RELATION
7. File or Data File:
 “The collection of more than one related records is called a
file or data file”.
 All the records of the should be of the same type.
 In the above File, two logical records about STUDENT are
shown.
8. Degree of file:
 “The total number of the columns in a file is called Degree of
a file”.
9. Cardinality:
 The total number of rows or records in a file is called
cardinality.
10
By ILTAF MEHDI (MCS, MCSE, CCNA)
An introduction to Relational Database
Properties of Relation
STUDENT Relation
Roll-No Name
100
Abdullah
F-Name
Gender
Address
Zain
Male
Kabul
Program Marks
BCS
40
Two Records
101
Spogmay
Iqbal
Female
Kabul
BCS
47
Cardinality: 02
SEVEN FIELDS
Degree of File: 07
11
By ILTAF MEHDI (MCS, MCSE, CCNA)
An introduction to Relational Database
ADVANTAGES OF RELATIONAL DATA MODEL
The Relational Data Model has several advantages
but some of them are given below:
1) Implementation Independence:
 R.D.M logically represents all relationship implicitly
and completely.
2) Terminology:
 The R.D.M has developed with its own set of
terminology.
3) Logical Key Pointer:
 The R.D.M uses primary and secondary keys to
represent relationship between two relations.
12
By ILTAF MEHDI (MCS, MCSE, CCNA)
An introduction to Relational Database
KEYS
Def: “a key is a data item which is used to
identify a record or relation”.
OR
 An attribute through which we can access an
entity within entity class”.

13
By ILTAF MEHDI (MCS, MCSE, CCNA)
An introduction to Relational Database
TYPES OF KEY
There are different types of keys which are given
below:
1. Super Key
2. Candidate Key
3. Primary Key
4. Atomic Key
5. Concatenated Key
6. Compound Key
7. Secondary Key
8. Foreign Key
14
By ILTAF MEHDI (MCS, MCSE, CCNA)
An introduction to Relational Database
TYPES OF KEY
Super Key:
“An attribute or combination of attributes that can
uniquely identify an entity is called super key”.
For Example: consider the STUDENT relation;
STUDENT (st-no, st-name, st-age, st-gender etc)
 A super key may contain some extra attributes
that are not necessary to uniquely identify an
entity.
i.e. { st-no, st-name, st-age }
 In-fact a super key can be:
{ Primary-key + Non-key attribute }
1)
15
By ILTAF MEHDI (MCS, MCSE, CCNA)
An introduction to Relational Database
TYPES OF KEY
Candidate Key:
 “An attribute or combination of attributes that
uniquely identifies each instances of an entity is
called Candidate Key”.
 For Example: consider the EMPLOYEE relation;
 EMPLOYEE (NIC-No, Name, Address etc)
 Here NIC-No and Name is a super key that contain
extra attributes that is not necessary for unique
identification. So it is not a candidate key. But
NIC-No is a Candidate Key.
 An entity may be uniquely identified by more than
one candidate keys.
2)
16
By ILTAF MEHDI (MCS, MCSE, CCNA)
An introduction to Relational Database
TYPES OF KEY
3) Primary Key:
 Def: “A primary key is a key that have been
selected as an identifier for an entity type”.
 It is also called identifier.
For example: Consider the EMPLOYEE relation;
EMPLOYEE (Emp-No, Emp-Name, Emp-Address)
 If we select Emp-No as an identifier then it is
Primary Key.
17
By ILTAF MEHDI (MCS, MCSE, CCNA)
An introduction to Relational Database
TYPES OF KEY
4) Atomic Key:
 Def: “A Primary Key is called atomic key or
simple key when it consists of a single attribute
to identify a record or relation”.
 For Example: Consider the STUDENT relation;
STUDENT (St-ID, St-Name, St-F/Name,St-Address)
 St-ID is atomic key for the STUDENT relation
18
By ILTAF MEHDI (MCS, MCSE, CCNA)
An introduction to Relational Database
TYPES OF KEY
5) Concatenated Key:
 Def: “The combination of two or more keys to uniquely
identify a record is called Concatenated Key”.
For Example: Consider the STUDENT relation;
STUDENT (class-no, name, address, class)
 “We have student file of Computer Science Department
that contain information about Previous and Final
students”. In this case class-no can’t uniquely identify a
student because there may be duplication of students
for class i.e. Previous and Final. So both class-no and
class can be used as Primary Key in this case. Such a
key is called Concatenated Key.
19
By ILTAF MEHDI (MCS, MCSE, CCNA)
An introduction to Relational Database
TYPES OF KEY
6) Compound Key:
 Def: “A Compound Key is a Primary Key that
consists of more than one attribute and every
attribute in the key is Primary key in some other
relation”.
 For Example: Consider the relation;
ORDER-LINE (order-no, product-no, quantity, date)
 So the primary key consists of order-no and
product-no. But order-no is primary key in ORDER
relation and product-no is primary key in
PRODUCT relation. So it is a Compound Key.
20
By ILTAF MEHDI (MCS, MCSE, CCNA)
An introduction to Relational Database
TYPES OF KEY
7) Secondary Key:
 “A secondary key is a data item that does not
uniquely identify a record but identifies a number
of records in a set that share some common
property ”.
For Example: Name, Address, Age etc are the
examples of Secondary Key.
 Now consider the Query:
Select * from student where Age=‘20’ ;
So based on secondary key “Age” this query will
select all students whose age is 20.
21
By ILTAF MEHDI (MCS, MCSE, CCNA)
An introduction to Relational Database
TYPES OF KEY
8) Foreign Key:
 Def: “An attribute that appear as non-key in one
relation but primary key in another relation is
called Foreign Key”.
 For Example: Consider the relations;
DEPARTMENT (Deptt-No, Deptt-Name, Location)
TEACHER (T-No, T-Name, Deptt-No )
 Here Deptt-No is a foreign key because it act as
primary key in DEPARTMENT and as non-key
attribute in TEACHER relation.
22