DATABASE

MANAGEMENT SYSTEM

Prepared By
Ms.S.S.GITE
Unit I: Introduction to DBMS

Introduction to Database Management Systems, Purpose of

Database Systems, Database-System Applications, Data
Abstraction and Database System Structure.
Relational Model: Structure of relational databases, Domains,
Relations, Relational algebra – fundamental operators and
syntax, relational algebra queries, tuple relational calculus.
Entity-Relationship model: Basic Concepts, Entity Set,
Relationship Sets and Weak Entity Sets, Mapping Cardinalities,
Keys, E-R diagrams, Design Issues, Extended E-R Features,
Converting E-R & EER diagram into tables.
Unit I: Introduction to DBMS

• Introduction to Database Management Systems

• Purpose of Database Systems
• Database-System Applications
• Data Abstraction
• Database System Structure
 Introduction to Database
Management Systems

• Definition of DBMS:
 Collection of interrelated data and a set of programs
to access those data
• The primary goal of a DBMS is
 to provide a way to store and retrieve database
information that is both convenient and efficient
Purpose of Database Systems
 In the early days, database applications were built directly on top of file
systems, which leads to:
 Data redundancy and inconsistency: data is stored in multiple file formats
resulting in duplication of information in different files
 Difficulty in accessing data
 Need to write a new program to carry out each new task
 Data isolation
 Multiple files and formats
 Integrity problems
 Integrity constraints (e.g., account balance > 0) become “buried” in
program code rather than being stated explicitly
 Hard to add new constraints or change existing ones
Purpose of Database Systems

 Atomicity of updates
 Failures may leave database in an inconsistent state with partial updates carried
out
Example: Transfer of funds from one account to another
should either complete or not happen at all
 Concurrent access by multiple users
 Concurrent access needed for performance
 Uncontrolled concurrent accesses can lead to inconsistencies
Ex: Two people reading a balance (say 100) and updating it
by withdrawing money (say 50 each) at the same time
 Security problems
 Hard to provide user access to some, but not all, data
 Limited Data Sharing:
To share data we need to write complex program.
 Data dependence:
If format of any file is change, we need to changes in programs which
process the file.
 Poor Data control:
It does not have centralized data control. It is in distributed in various
folders. Same field may have different names in files of different
departments of an organization leads to different meaning of same
data field
Database-System Applications
 Enterprise Information: Sales, Accounting, Human
Resource, Manufacturing, Online retailers
 Banking and Finance: Banking, Credit card transactions,
Finance
 Universities
 Airlines
 Telecommunication: For keeping records of calls made,
generating monthly bills, maintaining balances on prepaid
calling cards, and storing information about the
communication networks
 View of Data
 A database system is a collection of interrelated data and a set of
programs that allow users to access and modify these data.
 A major purpose of a database system is to provide users with an
abstract view of the data.
 Complex Data structures

 Extraction of important data by ignoring remaining irrelevant data.

 To makes user interaction easy with hiding internal irrelevant details

from user
1. Physical level
 Lowest level of abstraction

 It deals with how

 Complex low level data structures

 Database level

 Storage level
2. Logical Level
 Deals with what and relationships.
 Entire databases with simple data structures.
 Physical Data Independence

The ability to modify the physical schema without changing the

logical schema
 DBA
–type instructor = record
ID : string;
name : string;
dept_name : string;
salary : integer;
end;
3. View Level
 Highest level of abstraction

 Application programs hide details of data types. Views can also hide
information (such as an employee’s salary) for security purposes.
 Users and Access.

 Interaction with the system

 Application Programs

 Provides several views and security.

Data Abstraction
 Instances and Schemas
• Similar to types and variables in programming languages
• Logical Schema – the overall logical structure of the database
– Example: The database consists of information about a set of
customers and accounts in a bank and the relationship between
them
 Analogous to type information of a variable in a program
• Physical schema–
schema the overall physical structure of the database
• Instance – the actual content of the database at a particular point
in time
– Analogous to the value of a variable
• Physical Data Independence – the ability to modify the physical
schema without changing the logical schema
– Applications depend on the logical schema
– In general, the interfaces between the various levels and components
should be well defined so that changes in some parts do not seriously
influence others.
 Data Models
• A collection of tools for describing
– Data
– Data relationships
– Data semantics
– Data constraints
• Relational model
• Entity-Relationship data model (mainly for
database design)
• Object-based data models (Object-oriented and
Object-relational)
• Semi structured data model (XML)
• Other older models:
– Network model
– Hierarchical model
 Data Models:
• Underlying structure of database
• Conceptual tool
• Data+ Relational +Semantic+ Constraint
• Design Database at Physical and Logical level
and View Level
 Categories of Data Models:
• Relational Models
• Entity Relational Model
• Object Based Data Model
• Semi structured Data model
 Relational Model:
• Collection of tables
• Tables represent both data and relationship
• Multiple columns with unique name
• Tables with Relation
• Record Based Model
• Fixed format Record of several types
• Each record having Fixed no of Field and Attributes
• Most widely used
• Current DBS are based on relational model
 Entity- Relationship Model

• E-R Model
• Logical representation of data as objects and
relationship among them
• Collection of basic objects called entities and
its relationship
• An entity is a real world thing or object
• Widely use in database Design
 Object-Based Data Model
• OOP Language, C++, Java etc.
• Need for Object oriented data model
• Object based model= E-R model + OO features
• Extension of ER-Model with notions of
functions, encapsulation and object identity
• Support system with structured and collection
types
 Semistructured Data Model
• Specification of data
• Individuals data items of the same type may
have different set of attributes
• Contrast to Previous Data models
• Extensible Markup Language(XML)
• Consider web-based data sources as an example of a
semi-structured data model.
• Other examples of semi-structured data models
include emails, HTML, and web pages.
 Other Data Models:
• Network data Model Hierarchical data Model
• Not widely used
• Complicated
 Relational Model
• All the data is stored in various tables.
• Example of tabular data in the relational model Columns

Rows
A Sample Relational Database
 Data Definition Language (DDL)
• Specification notation for defining the database schema
Example: create table instructor (
ID char(5),
name varchar(20),
dept_name varchar(20),
salary numeric(8,2))

• DDL compiler generates a set of table templates stored in a data

dictionary
• Data dictionary contains metadata (i.e., data about data)
– Database schema
– Integrity constraints
• Primary key (ID uniquely identifies instructors)
– Authorization
• Who can access what
 Data Manipulation Language (DML)
• Language for accessing and manipulating
the data organized by the appropriate
data model
– DML also known as query language
• Two classes of languages
– Pure – used for proving properties about
computational power and for optimization
• Relational Algebra
• Tuple relational calculus
• Domain relational calculus
– Commercial – used in commercial systems
• SQL is the most widely used commercial
language
 SQL
• The most widely used commercial language
• SQL is NOT a Turing machine equivalent
language
• SQL is NOT a Turing machine equivalent
language
• To be able to compute complex functions SQL is
usually embedded in some higher-level
language
• Application programs generally access
databases through one of
– Language extensions to allow embedded SQL
– Application program interface (e.g., ODBC/JDBC)
which allow SQL queries to be sent to a database
Database System Internals
 Database Architecture
The architecture of a database systems is greatly
influenced by the underlying computer system on
which the database is running:
• Centralized
• Client-server
• Parallel (multi-processor)
• Distributed
 Relational Model
• Collection of tables
• Tables represent both data and relationship
• Multiple columns with unique name
• Tables with Relation
• Record Based Model
• Fixed format Record of several types
• Each record having Fixed no of Field and Attributes
• Most widely used
• Current DBS are based on relational model

35
 Relational Model Terminology
⚫ Tuple is a row of a relation.

⚫ Degree(Arity) is the number of attributes

in a relation.

⚫ Cardinality is the number of tuples in a

relation.

⚫ RelationalDatabase is a collection of
normalized relations with distinct relation
names.

36
Instances of Branch and Staff (part)
Relations

37
Examples of Attribute Domains

38
Relational Model: Structure of relational
databases

 Relation Schema and Instance:

 A1, A2, …, An are attributes

 R = (A1, A2, …, An ) is a relation schema

Example:
instructor = (ID, name, dept_name, salary)
 A relation instance r defined over schema R is
denoted by r (R).
 The current values a relation are specified by a table
 An element t of relation r is called a tuple and is
represented by a row in a table
Relational Model: Attributes and Domains

 The set of allowed values for each attribute is called the

domain of the attribute
 Attribute values are (normally) required to be atomic;
that is, indivisible
 The special value null is a member of every domain.
Indicated that the value is “unknown”
 The null value causes complications in the definition of
many operations
 Database Relations
• Relation schema
– Named relation defined by a set of attribute
and domain name pairs.

• Relational database schema

– Set of relation schemas, each with a distinct
name.

41
 Properties of Relations
• Relation name is distinct from all other relation names in
relational schema.

• Each cell of relation contains exactly one atomic (single)

value.

• Each attribute has a distinct name.

• Values of an attribute are all from the same domain.

42
 Properties of Relations
• Each tuple is distinct; there are no duplicate
tuples.

• Order of attributes has no significance.

• Order of tuples has no significance,

theoretically.

43
 Relational Algebra
• Relational algebra is a formal language
associated with the relational model.
• Informally, relational algebra is a (high-level)
procedural query language
• How to form queries in relational algebra.
• It performs set of operations on relations.
• Set of algebraic operations.
• Structured Query Language.

44
 Relational Model: Relational Algebra

 A procedural language consisting of a set of operations

that take one or two relations as input and produce a
new relation as their result.
 Six basic operators
 select: 
 project: 
 union: 
 set difference: –
 Cartesian product: x
 rename: 
KEYS
⚫ A key is single attribute are combination of
two or more attributes of an entity set that is
used to identify one or more instances of set.
KEY
LoginVino Kani Anandh
Frist
Last
Priya Deepa Riyan
Sona Geetha Janani

Logi Phon
n e
Vino 9843744345
Types of
Keys:
•Primary Key.
•Super Key.
•Candidate Key.
•Foreign Key.
•Alternate Key.
Primary Key:
⚫A primary key is a field that
uniquely identified each
record in a table.
⚫It contain can’t null values
and duplicate values.
Super Key:
⚫ A super key is a set of one or more attributes
that allows identifying uniquely an entity in
the entity set.
Candidate Key:
⚫ A candidate key can be any column or a
combination of columns that can qualify as
unique key in database.
⚫ There can be multiple candidate key in one
table.
Foreign Key:
⚫ An attributes or set of attributes
within one relation that matches the
candidate key of some relation.
 Selection (or Restriction)
• predicate (Relation)
• Select row from relation that satisfy given
condition(predicate)
– Comparison operators: =, ≠,>,<,≥,and ≤
– Connectives: AND(^), OR(v), and NOT(¬)

49
 Example - Selection (or Restriction)
• List all staff with a salary greater than
£10,000.

salary > 10000 (Staff)

51
 1. Select()
Example 1: Write an RA expression
to find all instructors working INSTRUCTOR
in Mechanical Department. ID Name Dept_Name Salary
10101 Ravi Electrical 65000

Solution: 12121 Raj Computer 90000

Dept_name= ‘‘Mechanical’’(INSTRUCTOR) 25252 Sani Finance 40000

26589 Tanu AIDS 45000
54789 Sam Mechanical 95000
Output:
78787 Ram Mechanical 60000
ID Name Dept_name Salary
12345 Sita E&TC 87000
54789 Sam Mechanical 95000
78787 Ram Mechanical 60000 45123 John IT 75000
54789 Neha E&TC 80000
 1. Select()
Example 2: Find all instructors
with Salary greater than 87,000 INSTRUCTOR
ID Name Dept_Name Salary

Solution: 10101 Ravi Electrical 65000

Salary > 87000 (INSTRUCTOR) 12121 Raj Computer 90000

25252 Sani Finance 40000
26589 Tanu AIDS 45000
Output:
54789 Sam Mechanical 95000
78787 Ram Mechanical 60000
ID Name Dept_name Salary
12345 Sita E&TC 87000
54789 Sam Mechanical 95000
12121 Raj Computer 90000 45123 John IT 75000
54789 Neha E&TC 80000
 1. Select()
Example 2: Find all instructors
who are working in ‘Mechanical’ INSTRUCTOR
ID Name Dept_Name Salary
Department with Salary greater
10101 Ravi Electrical 65000
than 87,000
12121 Raj Computer 90000
Solution:
25252 Sani Finance 40000
dept_name= ‘‘Mechanical’’ ^ salary > 87000 (INSTRUCTOR) 26589 Tanu AIDS 45000

54789 Sam Mechanical 95000

Output: 78787 Ram Mechanical 60000

ID Name Dept_name Salary 12345 Sita E&TC 87000

54789 Sam Mechanical 95000 45123 John IT 75000

54789 Neha E&TC 80000

 Projection
• col1, . . . , coln(Relation)

– Works on a single relation R and defines a relation

that contains a vertical subset of R, extracting the
values of specified attributes and eliminating
duplicates.
– Select specific Columns from relation, discarding
the rest

55
 Example - Projection
• Produce a list of salaries for all staff, showing
only staffNo, fName, lName, and salary
details.

staffNo, fName, lName, salary(Staff)

56
Relational Model: Relational Algebra &
Queries
 Relational Model: Relational Algebra &
Queries
 Union Operation (Same for Intersection and Difference)
 The union operation allows us to combine two relations
Notation: r  s
 For r  s to be valid.
1. r, s must have the same arity (same number of
attributes)
2. The attribute domains must be compatible (example: 2nd
column of r deals with the same type of values as does the
2nd column of s)
 Example: to find all courses taught in the Fall 2017 semester, or in the Spring
2018 semester, or in both

course_id ( semester=“Fall” Λ year=2017 (section)) 

Set Difference:
Relation1-Relation2
•Produces new relation with rows that appear in
the first relation but not in second.
•Example: Depositor- Borrower
 Relational Model: Relational Algebra &
Queries
 Cartesian-Product Operation
 The Cartesian-product operation (denoted by X) allows us to combine
information from any two relations.
 Example: the Cartesian product of the relations instructor and teaches is written
as:

instructor X teaches
 We construct a tuple of the result out of each possible pair of tuples: one from
the instructor relation and one from the teaches relation (see next slide)
 Since the instructor ID appears in both relations we distinguish between these
attribute by attaching to the attribute the name of the relation from which the
attribute originally came.
instructor.ID
teaches.ID
Oooo0-gv bh-23
 Extended operators in relational Algebra
• Derived from basic operators.
• Types
 Intersection(∩)
 Inner Join
1. Natural join
2. Theta join
3. Equijoin
 Outer join
 Left outer Join
 Right Outer Join
 Full outer Join
 Division
1.Intersection:
• Relation1 ∩ Relation2
• Binary Operator
• Produces new relation with rows that appear
in both relations.
• For this the degree must be same of both
relations and corresponding attributes in two
relation have same domain.
Example- STUDENT∩ EMPLOYEE

ROLL_ NAME ADRES PHON AGE

NO S E

1 RAM DELHI 94551 18

23451

4 SURES DELHI 91567 18

H 68971
Inner Join:
Inner Join is a join operation in DBMS that combines two or more
tables based on related columns and returns only rows that have
matching values among tables.
1.Natural Join Operator(⨝)
• It is the combination of Cartesian product &
selection Process.
• Set of all combinations of tuples in R and S
that are equal on their common attributes.
• Used to combine related tuples from multiple
relations.
• R⨝S
Theta Join(⨝ɵ)
• A general case of join operation.
• Conditional Join
• Used to join two or more relations on same
conditions
• Relation1 ⨝ɵ Relation2.
• P ⨝ɵ Q = ɵ (P × Q)
3.Equijoin:
• When theta join uses only equivalence, it
becomes Equijoin
Relational Model: Tuple Relational Calculus

 Query has the form: {T | p(T)}

p(T) denotes a formula in which tuple variable T appears.
 Answer is the set of all tuples T for which the formula
p(T) evaluates to true.
 Formula is recursively defined:
 start with simple atomic formulas (get tuples from
relations or make comparisons of values)
 build bigger and better formulas using the logical
connectives.
 Selection and Projection
• Find all sailors with rating above 7

{S |S Sailors  S.rating > 7}

– Modify this query to answer: Find sailors who
are older than 18 or have a rating under 9, and
are called ‘Bob’.
 Find names and ages of sailors with rating above 7.

{S | S1 Sailors(S1.rating > 7

 S.sname = S1.sname
 S.age = S1.age)}
Note, here S is a tuple variable of 2 fields (i.e. {S} is
a projection of sailors), since only 2 fields are ever
mentioned and S is never used to range over any
relations in the query.
 Example - Union
• List all cities where there is either a branch
office or a property for rent.

city(Branch)  city(PropertyForRent)

86
 Set Difference
• R–S
– Defines a relation consisting of the tuples that are
in relation R, but not in S.
– R and S must be union-compatible.

87
 Example - Set Difference
• List all cities where there is a branch office but
no properties for rent.

city(Branch) – city(PropertyForRent)

88
 Intersection
• RS
– Defines a relation consisting of the set of all
tuples that are in both R and S.
– R and S must be union-compatible.

• Expressed using basic operations:

R  S = R – (R – S)

89
 Example - Intersection
• List all cities where there is both a branch
office and at least one property for rent.

city(Branch)  city(PropertyForRent)

90
 Cartesian product
• RXS
– Defines a relation that is the concatenation of
every tuple of relation R with every tuple of
relation S.

91
 Example - Cartesian product
• List the names and comments of all clients who have viewed a
property for rent.
(clientNo, fName, (Client)) X (clientNo,
lName propertyNo, comment
(Viewing))

92
 Example - Cartesian product and
Selection
• Use selection operation to extract those tuples where
Client.clientNo = Viewing.clientNo.
Client.clientNo = Viewing.clientNo((clientNo, fName, lName(Client))  (clientNo,
propertyNo, comment(Viewing)))

 Cartesian product and Selection can be reduced to a single

operation called a Join.

93
 Join Operations
• Join is a derivative of Cartesian product.

• Equivalent to performing a Selection, using join

predicate as selection formula, over Cartesian
product of the two operand relations.

• One of the most difficult operations to implement

efficiently in an RDBMS and one reason why
RDBMSs have intrinsic performance problems.

94
 Join Operations
• Various forms of join operation
– Theta join
– Equijoin (a particular type of Theta join)
– Natural join
– Outer join
– Semijoin

95
 Example - Equijoin
• List the names and comments of all clients
who have viewed a property for rent.
(clientNo, fName, lName(Client)) Client.clientNo = Viewing.clientNo
(clientNo, propertyNo, comment(Viewing))

96
 Natural join
• R S
– An Equijoin of the two relations R and S over all
common attributes x. One occurrence of each
common attribute is eliminated from the result.

97
 Example - Natural join
• List the names and comments of all clients
who have viewed a property for rent.
(clientNo, fName, lName(Client))
(clientNo, propertyNo, comment(Viewing))

98
Relationship Model
⚫ There are two entities, both of them are of the
person type.
⚫ There is a relationship called married between
the two
person.
⚫ In this relationship each of these two person
entity has a role.
⚫ One person play the role of husband and another
person plays the role of wife.
Person Person
Entit Entit
y y
Husband(rol Wife(rol
e) e)
Relationsh
ip
Mathematical Relationship on Entity Set

⚫ A relationship type R among n entity types

R1,R2,…….Rn define a set of association
among entities from this types.
⚫ Mathematically R is a set of relationship
instance ri
associate an n entities(e1,e2,….en) and each
entity Ei in ri is a member of entity type.
Types of relationship:
One-to-One:
⚫ The one-to-one relationship also denoted as
1:1 is
simple.
⚫ An entity there is exactly one occurrence of
another entity.
One-to-Many:
⚫ In a one-to-many relationship also denoted as
1:M for given occurrence of an entity there
can be one or more occurrence of another
entity.
Many-to-Many:
⚫ In a many-to-many relationship also denoted
as M:N there can be one or more values on
both relationship of values.
⚫ Exampl
e:
Degree of Relationship:
⚫The degree of relationship is the number of
entities associated in the relationship.
⚫Relationship of degree greater than two are
called higher degree relationship.

Degree of Relationship:
•Unary Relationship.
•Binary Relationship.
Ternary Relationship.
•Quaternary Relationship.
Unary Relationship:
⚫ If a relationship type is between entity type
it is called an unary relationship type.

Manager

Manage by

Employees
Binary Relationship:
⚫ A binary relationship exist when two
entities are associated in a relationship .
⚫ Binary relationship are most common
conceptual design, possible most higher
order relationship.

Teach
Lecture es Class
 Ternary
Relationship:
⚫ A ternary relationship implies an
association among three different entities.
⚫ A doctor writes one or more prescription.
⚫ A patient may receive one or more
prescription.
⚫ A drug may appear in one or more
prescription.
Write s e
prescriptio
Doctor receive n. Patie
nt

Appears
In
Drug
 Quaternary
Relationship:
⚫ A quaternary relationship exit there are
four entity associated.

Teacher

Studi Course Material

Student es

Subject
Cardinality Constraints/Ratio

⚫ The cardinality specify the maximum number

of relationship instant that the entity can
participate. The term used in database relation to
denote the occurrence on data on either side of
the relationship.
⚫ The possible cardinality ratio binary relationship
types are,
⚫1 : 1
⚫1 : M
⚫M : N
 Participation
Constraints:
⚫ A Participation constraints specifies the
existence of an entity depends on its
related to another
entity relationship type.

Types of Participation
Constraints:

⚫Total Participation
Constraints
⚫Partial Participation
Constraints
Entity Relationship diagram
⚫ The basic ER diagram was conceived by
Peter Chen 1976 as a unifying data model
for all data models that existed at that time.
⚫ ER diagram or data schema map or data
map is another means of capturing the data
and their organization.
Importance of ERD:
⚫ Straight forward relationship
representation.
⚫ Easy conversion of ER to other data model.
⚫ Graphical representation for better
understanding.
ER Symbol
Enhanced ER Modeling

⚫ER model was introduced for modeling most

common business problems and has widespread
use.
⚫The term enhanced entity relationship model
is used to identify the result for extending the
original ER model with the new modeling
constructs.
Super types and Sub types:
⚫⚫AA super
sub typetype is
is aasub grouping of the
genericentity entities
that has
in type
an relationship
entity type with
that one
is or
meaningful
more a to the
organization.
sub type.
EX : Student – Subtype.
Graduate , not graduate – Super
type.
Reasons of using Super type/Sub type:

Two important reasons:

⚫It eliminate the of describing
requirements
concept more than one. The similar to time from
saving
data modeling and also results in more
readable ER diagram.
⚫ The super type / sub type relationship mor
add semantic concept and information e
of the design.
Basic
⚫ Theconcepts andsub
line to each Notation:
type that has been U –
Shaped symbol on each line connecting a sub
type to the circle emphasises that the sub
type is a sub set of the super type.
EX
⚫That the organisation has three types of
employees,
⚫Hourly Employees, Salaried Employees and
Contract consultants.
Hourly Employees:
Employee-no, Name, Address, date-hired,
hourly –rate.
Salaried Employees:
Employee-Number, Employee-Name, Address,
Date- Hired, Annual-Salary, Stock-Option.
Contract Consultants:
Employee-No, Name, Address, date-hired,
contact no, billing rate.
 IS-A Relationship and Attributes
Inheritance
⚫ The relationship support attribute inheritance
and relationship participation.
⚫ In the EER diagram, the sub class relationship
is represented by IS-A relationship.
Multiple Inheritances:
⚫ A sub class with more than one super class is
called a shared sub class.
⚫ A sub class inherit attributes not only of its
direct class.
⚫ In multiple inheritance a sub class be sub class
has more than one super class.
 Extended E-R features:

• Specialization
• Generalization
• Attribute Inheritance
• Constraints on Generalization
• Aggregation
 Specialization
 Top-down design process;
 we designate sub-groupings within an entity set that are distinctive from other entities in the
set.
 These sub-groupings become lower-level entity sets that have attributes or participate in
relationships that do not apply to the higher-level entity set.
 Depicted by a triangle component labeled ISA (e.g., instructor “is a” person).
 Specialization is the process of one or more sub type of the super type and forming
relationship.
 Attribute inheritance – a lower-level entity set inherits all the attributes and relationship
participation of the higher-level entity set to which it is linked.
 e.g., Person entity set can be divided into customer, student, employee. Person is superclass
and other specialized entity sets are subclasses.
 1. We have “is-a” relationship between superclass and subclass.
 2. Depicted by triangle component.
Specialization and Generalization
Generalisation
:
⚫ In data modeling generalisation is the process of
defining a more general entity type from a set of
more specialised entity types.
⚫ This is an generalisation is a bottom up process.
⚫ combine a number of entity sets that share the
same features into a higher-level entity set.
⚫ Specialization and generalization are simple
inversions of each other;
⚫ they are represented in an E-R diagram in the
same way.
⚫ The terms specialization and generalization are
used interchangeably.
Attribute Inheritance:

• A Crucial property of the Higher and lower level

entities created by specialization and
Generalization is Attribute Inheritance.
• The attribute of Higher- Level entity sets are
inherited by Lower-Level Entity sets.
• E.g. Student and Employee inherit the attributes
of Person
Constraints on Generalization
⚫ Constraints on generalization define the rules
governing the relationship between supertypes
and subtypes. They specify which entities can
belong to specific subtypes, whether an entity
can belong to multiple subtypes, and whether a
super type entity must belong to at least one
subtype.
⚫ To model an enterprise more accurately, there
are some constraints that are applicable to the
database on the particular generalization.

⚫ Condition-Defined
⚫ User-Defined
⚫ Disjoint
⚫ Overlapping
Aggregation:
• One limitation of E-R model is that it cannot
Express relationships among relationships.

• Aggregation is an abstraction through which

relationships as treated as higher level Entities.

Entity-Relationship model: Converting ER to
Tables

 Convert Entity Sets, Relationships to tables

 Convert all attributes to columns
 Assign all Primary attributes of Entity Sets to Relationship table as columns
Example:
 Table 1.19.1 Student:

Student_Id Student_Name Course_Id Street City State Pin Dob

Table 1.19.2 Student_Hobby

Student_Id Hobby

Table 1.19.3 Lecturer

Lecturer_Id Lecturer_Name Course_Id Subject_Id

Table 1.19.4 Subject

Subject_Id Subject_Name Course_Id

Table 1.19.5 Course

Course_Id Course_Name