DATABASE

MANAGEMENT SYSTEM
(DBMS)
PC-CS-301A

UNIT-1
SYLLABUS: UNIT-1
 Introduction: Concept & Overview of DBMS,
Data Models: Network, Hierarchical and
Relational Model, Levels of abstraction.
Administrator, Database Users, Three Schema
architecture of DBMS, Application.

 Entity-Relationship Model: : Entities, Attributes

and Entity Sets, Relation and Relationships sets,
Mapping Constraints, Keys, Entity-Relationship
Diagram, Weak Entity Sets, Extended E-R
features.
2
BASIC DEFINITIONS
 Data
 Can be defined as representation of facts,
concepts or instructions in formalized manner
that have some implicit meaning.

 Information
 Information is organised or classified data.
 It is a collection of related data.
 Processed data is called as Information.

3
KEY ATTRIBUTES OF INFORMATION
 Accuracy
 The information should be free from errors.
 It should clearly and accurately reflect the meaning
of data on which it is based.

 Timeliness
 The recipient should receive the information within
the required time frame.

 Relevancy
 The relevancy or usefulness of the piece of
4
information for the corresponding person.
BASIC DEFINITIONS
 Meta Data
 It is the data about data.
 It describes the database structure, sizes of data
types, constraints, applications, authorization etc.
that are used as an integral tool for information
resource management.

 Data Dictionary
 It contains the information of data stored in the
database.
 It stores meta data i.e. information about the
database, attribute names and definitions for each 5
table in the database.
DATABASE
 Database
 Database is a collection of Inter related data.
 It is organised to store, manage and access the
information.
 E.g. Telephone directory, Dictionary

 Database has the following features:

 It is well organised.
 It is related.
 It is accessible in different orders.
 It is stored only once.
6
DATABASE MANAGEMENT SYSTEM

Database
Management
System

Management
Database
System 7
CONTD...

 DBMS
 DBMS is a set of programs that provide a lot of
functionalities to store, manage, manipulate and access
information from a database in an efficient way.

 It is a collection of inter related data and set of

programs to access those data.

 It allows insertion, deletion, updation and processing of

this data.

 Examples of DBMS are:

 Oracle
8
 MS-Access

 MS SQL Server
PRIMARY FUNCTIONS OF DBMS
 Define, create and organise a database
 It defines the schemas and sub-schemas using DDL (Data
Definition Language).

 Input Data
 Enters data into the database with input devices.

 Process Data
 Manipulation and Processing of data stored in the database
using DML (Data Manipulation Language)

 Maintain Data Integrity and Security

 Limited access of database to authorized user to maintain
data integrity and security

 Query Database 9
 Information can be accessed from the database querying the
database using SQL.
COMPONENTS OF DBMS

10
COMPONENTS OF DBMS
 DBMS has three main components:
 Data Definition Language (DDL)
 Defines the database, specify the data types, data structures
and constraints on the data to be stored in database.

 Data Manipulation and Query Language (DML/SQL)

 Allows user to insert, update, delete and retrieve data from
the database SQL.

 Software for controlled access of database

 The software provides controlled access of the database by
the users.
 Provides concurrency control to allow shared access of
database.
 Provides recovery control system to restore the database in
11
case of software failure.
APPLICATIONS OF DBMS
 Banks: to maintain information about accounts,
loans, customers, schemes etc.

 Universities: to maintain information about all

colleges, faculty, students, courses offered etc.

 Airlines reservation systems: to maintain details

of flights, passengers, tickets booked.

 Railway Reservation System

12
 Libraries: to maintain information about books
etc.
TRADITIONAL FILE PROCESSING

 Inthese systems, groups of records were

stored in separate files.

 Each department has its own files,

designed specifically for those applications.

 Programs are dependent on the files and

vice versa.
13
DISADVANTAGES OF FILE PROCESSING
SYSTEM

 Data Redundancy (duplication)

 Data Inconsistency

 Lack of Data Integration.

 Lack of data security 14

INTEGRATED DATABASE ENVIRONMENT

15
INTEGRATED DATABASE ENVIRONMENT
 Large repository of data known as database, which
can used simultaneously by many users.
 All data is stored together with minimum
redundancy.
 It is a shared source.
 Managed by database administrator who is
responsible for designing, creating and
maintaining the database to satisfy the need of
users.
 The access to the database is controlled by DBMS.
 Allows concurrent use of data without having users
interface with each other.
 Performs backup and recovery procedures 16
automatically.
ADVANTAGES OF DATABASE SYSTEM

 Minimal Redundancy

 Data Consistency

 Sharing of data

 Improved Data Integrity

 Search capability

 Concurrency Control
17
 Privacy and security
DISADVANTAGES OF DATABASE SYSTEM

 High cost of DBMS

 High Hardware Cost

 Higher Programming Cost

 Confidentiality, Privacy and Security

18
 COMPARISON
File System Approach Database Approach

 Redundancy in stored  Minimal redundancy in

data data stored
 Inconsistency of data  Data is consistent

 Data is isolated and  Data is not isolated

separated and separated
 No sharing of data  Sharing of data

 Poor data security  Good data security

 Application programs are  Data independence is

dependent on file formats there
 Used in small systems  Used in large systems

 Cost is very less  High cost 19

 Single user system  Multiuser system

COMPONENTS OF A DATABASE SYSTEM

 Database system involves four major

components. These components are:
 Data
 Hardware
 Software
 Database Users

20
CONTD...
 Data

 Hardware
 Consists of secondary storage volumes like disks, drums etc.
on which database is stored with the associated devices,
control channels etc.

 Software
 There is software between hardware and user.
 It is called as DBMS.
 All the requests from the user to access the database are
handled by the DBMS.

 Users
 Database users interact with data to update, read and
modify the given information on a daily basis. 21
 There are different types of database users
DATABASE USERS

 Database Administrator (DBA)

 Application Programmer or Specialized users or

Back-End Developer

 System Analyst

 End Users

22
DATABASE ADMINISTRATOR (DBA)

 DBA is a person having the central control of both

data and the programs that access those data.

 Responsible for overall performance of database

i.e. authorizing access to the database,
coordinating and monitoring its use.

 Acquiring software and hardware resources as

needed.

23
RESPONSIBILITIES OF DBA
 Deciding the information contents of the
database system.

 Deciding hardware devices to be used.

 Deciding the users and the data to be used by

the users.

 Deciding the validation checks on the data.

 Monitoring performance and responding to 24

changing requirements.
CONTD...
 Deciding the backup and recovery method.

 Define the storage structure and access

strategies.

 Defines integrity constraints.

 Security administration.

 Ensuring availability of data.

25

 Installation of new software.

APPLICATION PROGRAMMER
 Responsible for writing application
programs in some high level language that
use the database to meet the user
requirement.

 These programs can retrieve the

information, creates new information,
deletes or changes the existing
information.
26
SYSTEM ANALYST
 System analyst is also known as a Business
Technology Analyst.

 Responsible for the design, structure, and properties of

databases.

 Provides specifications to the application programmer

to develop software that is to be used by the end users

 The analyst will gather information from the

shareholders as well as end users to understand their
requirements and translate it into functional
specifications for the new system.

 Examples of System Analysts −

27
 Team Leaders.
 Supervisor who manages the lower-level information Staff.
END USERS
 These are the people who access the database for
querying, updating and generating reports.

 End users can be categorised into several

categories:
 Casual End Users
 Naive or Parametric Users
 Sophisticated Users
 Stand Alone Users

28
CONTD...
 Casual End Users
 They occasionally access the database, but they may need
different information each time.

 They use a sophisticated database query language to

specify their request.

 Naive or Parametric Users

 They constantly query and update the database, using
standard types of queries and updates known as canned
transactions.

 E.g. Bank tellers check account balances and post with-

drawls and deposits.

 Standalone Users
 These users maintain personal databases by using ready 29
made program packages that provide easy to use menu
based or graphic based interfaces.
CONTD...

 Sophisticated Users
 These are the users who creates, updates,
deletes the database.

 They do not write programs for the software but

interacts with the database system using query
languages like SQL

 These includes engineers, scientists, business

analysts and others who thoroughly familiarize
themselves with the facilities of DBMS to
implement their own applications to meet their 30

complex requirements.
DATABASE SCHEMAS & INSTANCES
 The description of database like the names of
entities and attributes and the relationship
among them is called the Database Schema.

 The schema is specified during database

design and cannot be changed frequently.

A displayed schema is called a Schema

Diagram.

 Thedata in the database at a particular

moment in time is called an Instance or a 31

Database State or Snapshot.

THREE SCHEMA ARCHITECTURE

32
THREE LEVEL ARCHITECTURE OF DBMS
 Themajor purpose of a database system is
to provide users with an abstract view of
data.

 Ithides the details of how the data is

stored and maintained

 The DBMS architecture is divided into

three levels or views:
 External Level/ View
 Conceptual Level/ View 33
 Internal Level/ View
NEED FOR THREE LEVEL ARCHITECTURE
 DBA can change the structure of the database
without changing the application programs.

 Each user can access the data according to his/

her requirements.

 Users are independent of storage complexities

like indexing, constraints etc. of the database.

 The conceptual structure of the database has

no effect due to the change of the physical
storage devices. 34
EXTERNAL LEVEL
 Externallevel is concerned with the way in
which the data is viewed by the individual
users.

 Most users need to access only a part of the

database without knowing the background
details.

 The external/ view level of abstraction exists

to simplify their interaction with the system.

 Thesystem may provide many views for the 35

same database.
CONCEPTUAL LEVEL
 Thislevel describes what data are stored in
the database and the relationship among
the data.

 The conceptual schema hides the details of

physical storage structures and concentrate
on following activities:
 All entities, their attributes and their
relationships.
 Constraints on the data.
 Security and integrity rules. 36

 User operations.
INTERNAL LEVEL
 This is the lowest level of abstraction that
describes how the data is actually stored.
 This level describes complex low level data
structures in detail.
 This level is also called physical level.

 The following aspects are considered at this level:

 Storage space allocation for data.
 Record description for storage with stored sizes for
data items.
 Access paths e.g. Specification of primary and
secondary keys, indexes and pointers.
 Data compression and encryption techniques.
37
 Optimization of the internal structures.
MAPPINGS
 In three schema architecture, each user refers
to its own external schema.

 The data actually exists on the physical level

only. Therefore, the DBMS must transform a
request specified on an external schema for
processing over the stored database.

 The data extracted from the database must be

reformatted to match the user’s external view.

 This process of transforming requests and 38

results between levels is called Mapping.

TYPES OF MAPPING
 External/ Conceptual Mapping
 Defines the correspondence between a
particular external view and the conceptual
view.
 There is a mapping from a particular logical
record in the external view to one or more
conceptual records in the conceptual view.

 Conceptual/ Internal Mapping

 Defines the correspondence between the
conceptual view and the stored database.
39
 Specifies how conceptual record and fields are
represented at the internal level.
DATA INDEPENDENCE
 The ability to use the database without
knowing the representation details is
called Data Independence.

 It
can be defined as the capacity to change
the schema at one level of database system
without having to change the schema at
next higher level.

 There are two stages of data independence:

 Logical Data Independence
40
 Physical Data Independence
CONTD...
 Logical Data Independence
 Capacity to change the conceptual
schema without having to change the
external schemas or application
program.

 Physical Data Independence

 The capacity to change the internal
schema without having to change the
conceptual schema. 41
DATABASE LANGUAGES
 DBMS provides appropriate languages and
interfaces for each category of users to express
query and updates.

 After the database is designed, conceptual and

internal schemas should be specified and any
mappings between the two.

 The languages used to specify the database

schemas are:
 Data Definition Language (DDL)
 Storage Definition Language (SDL)
 View Definition Language (VDL) 42
 Data Manipulation Language (DML)
...CONTD
 DDL
 Supports the definition or declaration of database
objects
 Allows DBA to describe and name the entities,
attributes and relationships required along with
any associated integrity and security constraints

 SDL/ DSDL
 Used to specify the internal schema in the database.
 Mappings between the conceptual and internal
schame
 Storage structure and access methods used by the 43
database system.
...CONTD
 VDL
 Specify user’s views and their mappings to
conceptual schema
 Generally, DDL is used for both conceptual and
external schema.

 DML
 Retrieves data and records from the database
 Adds records to the database
 Deletes records
 Retrieve records
 sequentially according to key
 In physically recorded sequence
44
 Modify records that have been updated
 Rewrite records that have been updated
...CONTD

 DML is of two types:

 Procedural DML
 Non-Procedural DML

 Procedural DML
 There is an application program which
issues an instruction to the DBMS to find
certain data in the database and returns it
to the program.
 PDML allows the user to tell the system
 what data is needed 45

 How to retrieve that data

...CONTD

 Non-Procedural DML
 Allows the user to state what data is
required irrespective of how it is retrieved.
 Queries are applied directly to the DBMS to
find certain data and returns the same to
the display screen.

46
DATA MODELS
 A data model is a collection of concepts that can be
used to describe the structure of the database
including data types, relationships and the constraints
that apply on the data.

 Supports communication between users and database

designers.

 Helps in understanding the meaning of the data and

ensures:
 the data requirements of each user.
 the use of data across various applications.
 the nature of data independent of its physical
representations.

 Major use is to understand the meaning of the data

47
and to facilitate communication about the user
requirements.
TYPES OF DATA MODELS
 Record based Data Models
 Represents the data by using the record structures.
 Describes data at user view level.
 Further categorised into three types:
 Hierarchical Data Model
 Network Data Model
 Relational Data Model

 Objects based Data Models

 These models are used in describing the data at the
logical and user view levels.
 Allow the users to implicitly specify the constraints in the
data.

 Physical Data Models

 Describes the details of how the data is stored in the
computer along with their record structures, access paths
48
and ordering. Only specialized or professional users can
use these models.
HIERARCHICAL DATA MODEL
 One of the oldest database models

 The model organizes records in a tree structure i.e.,

hierarchy of parent and child records relationships.

 This model employs two main concepts :

 Record: collection of field values that provide information of an
entity.
 Parent Child Relationship: It is a 1 : N relationship between
two record types.
 The record type of one side is called the parent record type and
the one on the N side is called the child record type.

 In terms of tree data structure, a record type corresponds to

node of a tree and relationship type corresponds to edge of
the tree.

 The model requires that each child record can be linked to 49

only one parent and child can only be reached through its
parent.
HIERARCHICAL DATA MODEL

50
HIERARCHICAL DATA MODEL
 Advantages
 It is simple & efficient.
 Maintains data integrity.
 Provides the concept of data security.

 Disadvantages
 It is complex to implement
 Lack of flexibility.
 Data management is difficult.

51
NETWORK DATA MODEL
 Network Model was designed to overcome the
limitations of Hierarchical Data Model.

 There is many to many (N:N) relationship between

its records.

 Directed graphs are used instead of tree structure

in which a node can have more than one parent.

52
NETWORK DATA MODEL
 Advantages
 Conceptually simple
 Promotes database integrity
 Flexibility in accessing data

 Disadvantages
 Complex structure
 Lack of structural independence.

53
NETWORK DATA MODEL

54
RELATIONAL DATA MODEL
 Relational model can represent as a table with columns
and rows.

 Each row is known as a tuple.

 Each table of the column has a name or attribute.

 Domain: It contains a set of atomic values that an

attribute can take.

 Attribute: It contains the name of a column in a

particular table.

 Relational instance: In relational database system,

the relational instance is represented by a finite set of 55
tuples.
 Relation instances do not have duplicate tuples.
RELATIONAL DATA MODEL

56
RELATIONAL DATA MODEL
 Advantages
 Easy to use and understand
 Very flexible
 Widely used
 Security control and authorization are easy to
implement
 Data independence
 Disadvantages
 Performance degrades for large databases.
 Index must be maintained in the database which
requires sequential search. It increases time
complexity.
57
E-R MODEL
 E-R stands for Entity-Relationship Model

 The entity-relationship model (or ER model) is a

way of graphically representing the logical
relationships of entities (or objects) in order to
create a database.

 An entity is an object with physical existence like

person, car or it may be an object with conceptual
or logical existence like course, job.

 Each entity has attributes-the properties which 58

describe it.
COMPONENTS OF E-R MODEL

59
ATTRIBUTES
 Attributes are properties used to describe an
entity. For example an EMPLOYEE entity may
have a Name, SSN, Address, Gender, BirthDate.

 An entity may contain any number of

attributes.

 Attribute Domain: The set of allowable values

for one or more attributes.
60
TYPES OF ATTRIBUTES
 Composite and Simple Attributes

 Single-valued and Multi-valued Attributes

 Stored and Derived Attributes

 Complex Attributes

 Key Attribute
61
TYPES OF ATTRIBUTES
 An attribute that cannot be further
subdivided into components is called
Simple Attribute.
E.g. Rollno of a student

 Composite attributes can be divided into

smaller independent sub-parts.
For e.g.: the address attribute of the
EMPLOYEE entity can be subdivided into
street address, city, state etc.
62
TYPES OF ATTRIBUTES
 Single attributes are those attributes
that have only one value for each entity
 e.g. name, age of Employee.

 Multi-valued attributes takes up more

than a single value for each entity
instance.
 E.g. phone number of a person

63
TYPES OF ATTRIBUTES
 Stored and derived attributes
 Attributes can also derived from other
attributes
 e.g. age can be derived from date of birth &
current date.
 Age is called derived attribute and date of
birth is a stored attribute.

 Complex attributes are the nesting of

two or more composite and multi-valued
attributes.
 These multi-valued and composite attributes
64
are called ‘Components’ of complex attributes.
TYPES OF ATTRIBUTES
 Keyattributes are those attributes that can
uniquely identify the entity in the entity set.
 E.g. Roll-No is the key attribute because it can
uniquely identify the student.

 There are two types of key attributes

 Primary Key identifies each row in a table individually.
 It must be unique for each row and cannot be null.
 E.g. in an online purchasing database, Order ID can be the
primary key.

 Foreign Key is a key characteristic that refers to another

table’s primary key. It connects two tables. E.g. in an online 65
purchasing database, the Customer ID can be the foreign
key that relates to the Customer table’s primary key.
ENTITY TYPE
 An Entity is a real-world thing which can be distinctly
identified like a person, place or a concept.

 Entity type is a collection of the entity having similar

attributes.
 E.g.
 In STUDENT, each row represents an entity and they are
having common attributes i.e each row has its own value for
attributes Roll_no, Age, Student_name and Mobile_no.

 STUDENT table is an entity type because it is a collection of

entities having the same attributes.

 So, an entity type in an ER diagram is defined by a

name(here, STUDENT) and a set of attributes(here, 66
Roll_no, Student_name, Age, Mobile_no).
E-R REPRESENTATION OF ENTITY TYPE

67
TYPES OF ENTITY TYPES
 Strong Entity Type
 The entity types having a key attribute.
 The primary key helps in identifying each entity uniquely.
 It is represented by a rectangle.
 E.g. In STUDENT, Roll_no identifies each element of the table
uniquely and hence, we can say that STUDENT is a strong
entity type.

 Weak Entity Type

 Weak entity type doesn't have a key attribute.
 It depends upon some other strong entity for its distinct
identity.
 There can be a room only if building exits. There can be no
independent existence of a room.
 A weak entity is represented by a double outlined rectangle.

 The relationship between a weak entity type and strong

entity type is called an Identifying Relationship and 68
represented with a double outlined diamond instead of a
single outlined diamond
TYPES OF ENTITY TYPES

69
ENTITY SET
 Entity Set is a collection of entities of the same entity
type.
 E.g. In the previous example of STUDENT entity type, a
collection of entities from the Student entity type would
form an entity set.

 An entity type is a superset of the entity set as all the

entities are included in the entity type.

70
COMPONENTS OF E-R DIAGRAM

71
RELATIONSHIP TYPE
 A Relationship Type represents the association
between entity types.
 For example, ‘Enrolled in’ is a relationship type that
exists between entity type Student and Course.

 In ER diagram, the relationship type is

represented by a diamond and connecting the
entities with lines.

72
RELATIONSHIP SET
 A relationship set is a set of relationships of the same
type.
 Consider an example, employees work in different
departments.
 Then relationship exists between employees and
departments because each employee must belongs to some
department.
 Relation of all employees with department when combined
makes the relationship set because each employee has same
kind of relation with departments.

73
DEGREE OF A RELATIONSHIP TYPE
 The degree of a relationship type is the number of
participating entity types.

 Unary (recursive) relationship

 It involves only one entity.

 Binary relationship
 Has 2 entity types linked together.

 Ternary relationship
 If there are 3 entity types linked together.

74
ROLE NAMES & RECURSIVE RELATIONSHIPS
 The Role name signifies the role that the
participating entity from the entity type plays in
each relationship instance.

 A relationship between two entities of a similar

entity type is called a Recursive Relationship.

 The same entity type participates more than once in a

relationship type with a different role for each
instance.

 A relationship has always been between occurrences in

two different entities.
75
 If the same entity participates in the relationship is
termed as a recursive relationship.
MAPPING CONSTRAINTS
 Relationships types usually have certain
constraints that limit the possible
combinations of entities that may
participate in the corresponding
relationship set.

 Constraints act as rules to which the

contents of database must conform.

 There are two types of mapping

constraints:
 Mapping cardinalities/ Cardinality Ratio 76

 Participation constraints.
MAPPING CARDINALITY/ CARDINALITY RATIO

 Mapping cardinality is the maximum

number of relationship instances in which
an entity can participate.

 There
are four types of Cardinality Ratio in
Database Management Systems:
 One to one (1:1)
 One to many (1:N)
 Many to one (N:1)
 Many to many (M:N) 77
ONE TO ONE CARDINALITY
 When a single instance of an entity is associated with
a single instance of another entity, it is called as One
to One Cardinality

 e.g. one student can have only one student id, and one
student id can belong to only one student.

 Principal of the as one school can have a maximum of one

Principal, and one Principal can belong to only one school.

78
ONE TO MANY CARDINALITY
 When is a single instance of an entity is
associated with more than one instance of
another entity, this type of relationship is
called One to Many Relationships.
 E.g. Many students can study in a single college
but the student cannot register to more than one
college at the same time

79
MANY TO ONE CARDINALITY
 When entities in one entity set can participate
only once in a relationship set and entities in
another entity can participate more than once in
the relationship set, then such type of cardinality
is called Many to One.
 E.g. a student can join only one college but a college
can admit any number of students

80
MANY TO MANY CARDINALITY
 More than one instance of an entity is associated
with more than one instance of another entity.
 E.g. a student can take more than one course and the
single course can be taken by any number of students

 It is represented by M: N or N: M

81
PARTICIPATION CONSTRAINTS
 It tells the participation of entity set in relationship
sets.

 There are two types of participations.

 Total participation
 If every entity from entity set E is participated with at least one
relation in relationship set R then it is known as Total
participation.
 Total participation is also called Existence Dependency.
 For eg: If a company policy states that every employee must work
for a department then an employee entity can exist only if it
participates in atleast one WORKS_FOR relationship.
 Thus the participation of EMPLOYEE in WORKS_FOR
relationship is called total participation.

 Partial participation
 If only some entities from entity set E is participating in
relationships in set R then it is known as Partial participation.
 In the manages relationship every employee cannot be MANAGER 82
of the department,so the participation of EMPLOYEE in the
MANAGES relationship is partial.
PARTICIPATION CONSTRAINTS

83
PARTICIPATION CONSTRAINTS

84
RELATIONSHIP BETWEEN CARDINALITY
AND PARTICIPATION CONSTRAINTS

 Minimum Cardinality tells whether the

participation is partial or total.
 If minimum cardinality = 0
 it signifies partial participation.
 If minimum cardinality = 1
 then it signifies total participation.

 Maximum Cardinality tells the

maximum number of entities that
participates in a relationship set. 85
KEYS
 A key is an attribute or set of attributes that is
used to identify a tuple (row) in an entity set.

 The attributes which are used as key are known

as key attributes and the others are called Non-
key attributes.

 There are broadly seven types of keys in DBMS:

 Primary Key
 Candidate Key
 Super Key
 Foreign Key
 Composite Key
86
 Alternate Key
 Unique Key
SUPER KEY
 A super key is a set of collection of one or more than
one attributes that can identify data uniquely.
 Any entity set has more than one super key.
 E.g. In entity set Employee, shown in figure below, Super
Keys are
 (ID, Name, Salary, Reg. No.)
 (ID, Name, Reg. No.)
 (ID)
 All combinations can identify data uniquely.

87
CANDIDATE KEY
 A candidate key is an attribute or set of attributes that can
uniquely identify a tuple.

 Except for the primary key, the remaining attributes are

considered a candidate key.

 The candidate keys are as strong as the primary key.

 E.g. In the EMPLOYEE table, if we choose id as the primary

key, the rest of the attributes, like SSN, Passport_Number,
License_Number, etc., are considered a candidate key.

88
PRIMARY KEY
 It is the first key used to identify one and only one instance of an entity
uniquely.

 An entity can contain multiple keys but the key which is most suitable
becomes a primary key.
 E.g. In the EMPLOYEE table, ID can be the primary key since it is unique for
each employee.
 We can even select License_Number and Passport_Number as primary keys
since they are also unique.

 For each entity, the primary key selection is based on requirements and
developers.

89
FOREIGN KEY
 Foreign keys are the attributes of the table used to
point to the primary key of another table.
 E.g. Every employee works in a specific department in
a company, and employee and department are two
different entities.
 Therefore, we link these two tables through the
primary key of one table.
 In the EMPLOYEE table, Department_Id is the
foreign key, and both the tables are related.

90
ALTERNATE KEY
 There may be one or more attributes or a combination of attributes
that uniquely identify each tuple in a relation.
 These attributes or combinations of the attributes are called the
candidate keys.
 One key is chosen as the primary key from these candidate keys,
and the remaining candidate key, if it exists, is termed the
alternate key.
 If there is only one candidate key in a relation, it does not have an
alternate key.
 For example, employee relation has two attributes, Employee_Id and
PAN_No, that act as candidate keys.
 Employee_Id is chosen as the primary key
 PAN_No, acts as the Alternate key.

91
COMPOSITE KEY
 A composite Key is a set of two or more attributes
that help identify each tuple in a table uniquely.
 The attributes in the set may not be unique when
considered separately.
 However, when taken all together, they will
ensure uniqueness.
 The ‘Concatenated Key’ is another name for a
composite key.

92
UNIQUE KEY
 Unique Key is a column or set of columns that
uniquely identify each record in a table.
 A unique Key differs from a primary key because
it can have only one null value, whereas a primary
Key cannot have any null values.
 E.g. driving_license attribute in EMPLOYEE table can
be a unique key but an employee may not have a
driving_license.
 So, it has a unique value but can be null.

93
RELATION BETWEEN PRIMARY KEY,
CANDIDATE KEY & SUPER KEY

94
ENTITY RELATIONSHIP DIAGRAM
 The Entity Relationship Model is a model for identifying
entities to be represented in the database and
representation of how those entities are related.

 The ER model specifies schema that represents the overall

logical structure of a database graphically.

 The following points should be considered while creating an

ER diagram:

 Identify all the entities in the system. An entity should appear

only once in a particular diagram. Create rectangles for all entities
and name them properly.

 Identify relationships between entities. Connect them using a

line and add a diamond in the middle describing the relationship.

 Add attributes for entities. Give meaningful attribute names so 95

they can be understood easily.
E-R DIAGRAM: AN EXAMPLE
 E-R diagram of Company has the following
description :
 Company has several departments.
 Each department may have several Location.
 Departments are identified by a name, D_no, Location.
 A Manager control a particular department.
 Each department is associated with number of projects.
 Employees are identified by name, id, address, dob,
date_of_joining.
 An employee works in only one department but can
work on several project.
 We also keep track of number of hours worked by an
employee on a single project.
 Each employee has dependent 96
 Dependent has D_name, Gender and relationship.
E-R DIAGRAM: COMPANY DATABASE

97
E-R DIAGRAM: AN EXAMPLE
 Make an E-R diagram with entities Customer, Loan
and Payment

98
ADVANTAGES OF E-R MODEL
 Conceptually Simple
 ER model is very simple
 if the relationship between entities and attributes is known, we can
easily draw an ER diagram.

 Better visual representation

 ER model is a diagrammatic representation of any logical structure of
database.
 With ER diagram, we can easily understand relationship among
entities and relationship.

 Effective communication tool

 It is an effective communication tool for database designer.

 Highly integrated with relational model

 ER model can be easily converted into relational model by simply
converting ER model into tables.

 Easy conversion to any data model 99

 ER model can be easily converted into another data model like
hierarchical data model, network data model and so on.
DISADVANTAGES OF E-R MODEL
 Limited constraints and specification

 Loss of information content

 Some information be lost or hidden in ER model

 Limited relationship representation

 ER model represents limited relationship as compared
to another data models like relational model etc.
 It is not possible to relationship set between
relationship set.

 No representation of data manipulation

100
 It is difficult to show data manipulation in ER model.
EXTENDED E-R (EER) MODEL
 Extended/ Enhanced ER (EER) is a high-level
data model that incorporates the extensions to the
original ER model.

 Includes all the basic concepts of E-R model.

 Represent the requirements and complexities of

complex databases.

 The Extended Entity Relationship (ER) models

incorporates three main features:
 Aggregation
 Specialization 101
 Generalization
SPECIALIZATION
 In specialization, an entity is divided into sub-
entities based on its characteristics.

 It is a top-down approach where the higher-level

entity is specialized into two or more lower-
level entities.
 For Example, an EMPLOYEE entity in an Employee
management system can be specialized into
DEVELOPER, TESTER, etc.
 Common attributes like E_NAME, E_SAL, etc. become
part of a higher entity (EMPLOYEE)
 Specialized attributes like TES_TYPE become part of
a specialized entity (TESTER). 102
SPECIALIZATION

103
GENERALIZATION
 Generalization is the process of extracting
common properties from a set of entities and
creating a generalized entity from it.

 It is a bottom-up approach in which two or more

entities can be generalized to a higher-level entity
if they have some attributes in common.

 For Example, STUDENT and FACULTY can be

generalized to a higher-level entity called PERSON.

 Common attributes like P_NAME, and P_ADD become

part of a higher entity (PERSON), and
specialized attributes like S_FEE become part of a 104
specialized entity (STUDENT).
GENERALIZATION

105
AGGREGATION
 An ER diagram is not capable of representing the
relationship between an entity and a relationship which may
be required in some scenarios.

 In those cases, a relationship with its corresponding entities

is aggregated into a higher-level entity.

 Aggregation is an abstraction through which we can

represent relationships as higher-level entity sets.

 E.g. an Employee working on a project may require some

machinery.

 REQUIRE relationship is needed between the relationship

WORKS_FOR and entity MACHINERY.

 Using aggregation,
 WORKS_FOR relationship with its entities EMPLOYEE and PROJECT
is aggregated into a single entity and 106
 relationship REQUIRE is created between the aggregated entity and
MACHINERY.
AGGREGATION

107
EXAMPLE

108
REDUCTION OF ER & EER DIAGRAM
INTO TABLES

 Torepresent the database in tabular form,

E-R diagrams have to be reduced in tables.

 For each entity set, make different table

and for each relationship set make a
different table.

109
 Following points are to be considered:
 Reduction of Strong Entity Sets into Tables
 For a strong entity set E and having n number of
columns or table name is equal to entity set name and
number of columns is equal to number of attributes.

 Reduction of Composite Attributes

 Create a separate column for each component attribute
or parts of composite attributes

 Reduction of Multi-valued Attributes

 Make a separate table with columns C1 which
represent the primary key of entity set or relationship
set and with columns C2 which represent the multi-
valued attributes.
110
…CONTD.
 Reduction of Weak Entity Sets
 Make a table with table name as of weak entity set
having columns equal to the attributes of weak entity
set plus primary key of the strong entity set on which
weak entity set depends.

 Reduction of Relationship Sets

 Make a table with table name as of Relationship Set
having columns equal to number of attributes in
relationship set (descriptive attributes) and primary
keys of all participating entity sets.

 Reduction of Generalization
111

 Reduction of Aggregation
E-R MODEL OF EMPLOYEE & DEPARTMENT ENTITY SETS

112