1. AN Entity is represented by a set of attributes. Attributes are descriptive propereties possessed
by each member of an entity set.
2. The attributes of customer entity set are customer_id, customer _ name, and city possible
attributes of the account entity set are account_no and balance.
3. each entity has a value for each of its attributes. For instance, a particular customer entity may
have the value C101 for attributes customer_id, hari for customer _name, and bombay for city.
Attributes are classified as
1. Simple attributes.
2. Composite attributes.
3. Single valued attribute
4. Multi valued attribute
5. Derived Attribute .
• 1. Simple attributes :
- it is an attribute composed of a single components with an
- simple attribute can not be further subdivided.
eg : roll _no, acc_no etc.
2. composite attribute :
- an attribute composed of multiple components each with an
indepentent existence called composite.
eg : a) Name , which is composed of attributes like first name, middle name
and last name.
b) Address, which is composed of other components like street, city,
3. Single valued attributes( ATOMIC) :
- A single valued attribute is one that holds a single value for a single
eg : cust_id, Room_no
• Multi value attributes :
- it holds multiple values for a single entity.
eg: student entity can have multiple values for the hobby attributes such as
Derived attribute :
- it represent a value that is derivable from the value of a related attribute .
Eg : age attribute can be derived from the dat of birth attribute.
RELATIONSHIP AND RELATIONSHIP SETS :
- A relationalship expresses an association among several entities
- A relationalship set is a set of relationalships of the same type.
eg : consider two entities person and company
Person_ name addr
E R DIAGRAM :
1. The relationship Works for represents association between persaon and company .
2. this is binary relationship set.
ENTITY ROLE :
- The function that an entity plays in a relationship called that entity role . The role is
one end of an association.
employee Works employer
- person and company participates in association ‘work for’ .
- person assumes the role of employee with respect to company ,A company assumes the
role of employer with respect to person.
- A relationship may also have attributes called descriptive attributes.
- consider a relationalship set ‘depositor’ with entity sets customer and account.
- we can associate attributes access – date to the depositor relationship to specify the
most recent date on which a customer accessed an account.
customer depositor account
Cust_id Cust_ Access_dat Acc balance
name e _no
ER DIAGRAM WITH AN ATTRIBUTE ATTACHED TO THE RELATIONSHIP SET
TYPES OF RELATIONALSHIPS :
1. UNARY RELATIONSHIP.
2. BINARY RELATIONAL SHIP
3. TERNARY RELATIONSHIP.
4. QUATERNARY RELATIONSHIP.
• 1. UNARY RELATIONSHIP.
- A Unary relationship exits when an association is maintained with in a
single entity .
- boss and worker distingishes the two employees participating in the
2. Binary relationship :
- when two entities are associated.
publisher publishes book
BOOK -PUBLISHER RELATIONSHIP
• TERNARY RELATIONSHIP:
- When there are three entities associated.
eg : the entities teacher, subject,student are related using aternary relationship
teacher teaches subject
QUATERNARY RELATIONSHIP :
- when there are four entities associated.
student studies Course material
• Constraints :
- An E-R enterprise schema may define certain constraints to which the
content of a database system must conform .
Two main important types of constraints are :
1. MAPPING CARDINALITIES .
2. PARTICIPATION CONSTRAINTS.
1. MAPPING CARDINALITIES :
- It express the number of entities to which another entity can be
associated via relationship set.
- for binary relationship set R between entity sets A and B the mapping
cardinalities must be one of the following.
Cardinality specifies how the number of occurance of one object is related to
the number of occurance of another object .
• One-to-one (1:1) – one object can relate to only one other object.
• One-to-many (1:N) – one object can relate to many objects.
• many –to- one(N:1) –Many object can relate to one object
• Many-to-many (N:N) – some number of occurances of an object can relate
to some other number of occurances of another object.
1. One-to-one(1:1) :
- AN entity in A is associated with at most one entity in B and an entity
B is associated with at most one entity in A,
1. One-to-one(1:1) MAPPING:
• EG: A customer with single account at given branch
• ii) One –to – many:
- AN entity in A is associated with any number ofentities in B and an
entity B is associated with at most one entity in A,
• eg : A customer having two accounts at a given branch
Many –to- one(N:1):
- AN entity in A is associated with at most one entity in B and an entity B is
associated with any number of entities in A,
- Many employees works for a company.
• Many – to - Many :
- AN entity in A is associated with Any number of entities in B and an
entities B is associated with any number of entities in A,
eg : employee works on number of projects and project is handled by
number of employees. Therefore the relationship between employee and
project is a many – to - many
employee on project
PARTICIPATION CONSTRAINTS :
- The participation of an entity set E in a relational set R IS SAID TO BE
TOTAL if every entity in E participates in atleast one relationship in R.
- If only some entities in E partcipate in relationships in r , the participation of
entity set E in relationship R is said to be partial.
• KEYS :
- Key allows us to identify a set of attributes and then distinguishes entities
from each other.
- Keys also help uniquely identify relationship and thus distiguish relationships
from each other.
Different types of keys
1. Super key .
2. Candidate key.
3. Primary key
4. Foreign key.
1. Super key.
- it is a set of one or more attributes that allows us to identify uniquely an
entity in the entity set.
- eg: Roll _No attributes of the entity set ‘ student ‘ distinguishes one student
entity from another.
2. Candidate key :
- A super key may contain extraneous attributes and we are often
interested in the smallest superkey.
- A superkey for which no subset is a superkey Is called candidate key.
eg: student name and student street are sufficient to uniquely identify one
3. Primary key :
- It is a candidate key that is choosen by the database designer as the
principal means of identifying entities within the entity set.
eg: roll no is a primary set of ‘student’ entity set.
4. Foreign key :
- An attributes or set of attributes , within one relation that matches the
candidate key of some relation.
EXTENDED E R MODEL(EER MODEL):
- E R model that is supported with additional semantics concepts
called EER model.
1. Specialisation .
1. Specialisation .
- It is the process of designating subgroupings within an entity
set. it is a top – down process
- Specilaization , which is represented by triangle symbol . The
label ISA – stands for “is a” and represents .eg that customer
“ is a” person.
- ISA relationship may also reffered to as a superclass –subclass
eg : consider an entity set person , with attributes name, street and city. A person
may be further classified as one of the following .
- Each of these person types is described by a set of attributes that includes all
the attributes of entity set person plus additional attributes.
- eg :customer entities are further described by cust_id , employee entities by
emp_code and salary .
- It is the process of defining a more general entity type from a set of more
specialized entity types.
- It is botton- up approach .This approach results in the identification of a
generalized entity types.
• ISA SPECIALIZATION &GENERALIZATION
OFFICER TELLER SECRETARY
OFFICE Station HOUSE HOURS
• AGGREGATION ( A PART OF RELATIONSHIP):
- Aggregation is a form of association . It is used to represent the
components of a complex object.
• Relational model :
- it is a collection of tables to represent both data and the relationship
among those data.
- Each table has multiple column, and each column has a unique name.
- The relational model is an example of a record based model.
- RBM are so named because the database is structured in fixed – format
records of several types.
- each table contains records of a particular type , each record type defines a
fixed number of fields or attributes.
- The R DATAMODEL is the most widely used data model .
Cust_id Cust_n Cust Cust
ame _street _city
192-83- johnson 12 alma palo
67-89- hayes 3 main hariso
• customer table
Acc_number balance CUST_ID ACC_NUMBER
A-101 500 192-83-7465 A-101
A-201 900 192-83-7465 A-201
• HIERARCHICAL MODEL:
- it is a kind of database management system that links records
together in a tree data structures such that each record type has only
NETWORK MODEL :
- The network model replaces the hierarchical tree with a graph
thus allowing more general connection among the nodes.
- The main difference of the network model from the hierarchical
model is its ability to handle many to many relationships.
- it allows a record to have more than one parent.
eg: An employee working for two departments.
• Object oriented model :
- it is based on a collection of objects. An object contains values stored in instance
variables within the object.
- An object also contains bodies of code that operate on the object. These bodies of
code called methods
- object contain the same types of values and the same methods are grouped together
in to classes.
• E R DIAGRAM
• DATABASE SYSTEM STRUCTURE
DATABASE USERS AND ADMINISTRATOR :
- People who work with a database can be categorized as.
1. Database users.
2. Database administrators.
1. Database users
There are four different types of database system users, differentiated
by the way they interact with the system.
a) Naïve users.
- users who interact with the system by invoking one of the
application programs that have been written previously.
eg : bank teller
b) Application programmers :
- There are computer professionals who write application programs.
- Application programmer can choose from many tools to develop user
- Rapid application development(RAD) tools that enable an application
programmers to construct forms and reports without writing a program.
c) sophisticated users:
- They interact with the system without writing programs , they form their
requests in a database query language.
- They submit each query to a query processor, whose function is to
breakdown DML statements in to instruction that the storage manager
d) Specialized users:
- These are sophisticated users who write specialized database
application that do not fit in to the traditional data processing frame work.
- Among these application are computer aided design system knowledge
base and expert system, system that store data with complex data types.
• DATABASE ADMINISTRATOR :
- A person who has central control over the system called database
The Function of a DBA include :
1. Schema definition :
- The DBA creates the original database schema by executing a set of
data definition statement in the DDL.
2. Storage structure and access method definition.
3. Schema and physical organization modification:
- The DBA carries out changes to the schema and physical organization
to reflect the changing needs of the organization.
4. Granting of authorization for data access :
- By granting different types of authorization, the DBA can regulate
different users accessing different parts of database.
• Routine maintenance:
1) Periodic backup, either on to tapes or onto remote server, to prevent
loss of data in case of disasters such as flooding.
2) Ensuring that enough free disk space is available for normal operations
and upgrading disk space as required.
3) Monitoring jobs running on the database and ensuring that performance
is not degraded by very expensive tasks submitted by some users.
DATA DICTIONARY(System catalogs)
- A repository of information describing the data in the database , that is the
metadata. Or the data about the data.
DATABSE SYSTEM ARCHITECTURE :
- A database system is partitioned in to modules that deal with each of the
responsibilities of the overall system.
- The functional components of a database system can be divided in to
1. Storage manager.
2. Query processor components.
STORAGE MANAGER :
- It is a program module that provides the interface between the low
level data stored in the database and the application programs and
queries submitted to the system.
- The storage manager translates the various DML statement in to low
level file system commands.
- it is responsible for storing , retrieving and updating data in the
various components of the storage manager :
1. Authorization and integrity manager :
- It test for satisfaction of various integrity constraints and checks the
authority of users accessing the data.
2. Transaction manager :
- It ensure that the database remains in a consistent state despite
system failures and concurrent executions proceed without conflicting
3) File manager :
- It manages the allocation of space on disk storage and the data
structures used to represent information stored on disk.
4) Buffer manager :
- It is responsible for fetching data from disk storage into main memory
and deciding what data to cache in main memory.
Several data structures as part of physical system implementation:
1. DATA FILES: - which stores the database itself.
2. Data dictionary: - It contains metadata that is data about data.
3. indices : which provide fast access to data items that hold particular
Query Processor :
- The query processor is an important part of the database system. It helps
the datbase system to simplify and facilitate access to data.
Query components include :
1. DDL interpreter – which interprets DDL statements and records the
definitions in the data dictionary.
2. DML compiler – which translates DML statements in a Query language
into an evaluation plan consisting of low level instruction That the query
evaluation engine understands.
3. Query evaluation engine – which executes low level instruction generated
by DML compiler.
- It is a procedural query language. It consists of a set operations that
take one or two relation as input and produce a new relation as their result.
• A basic expression in the relational algebra consists of either one of the
– A relation in the database
– A constant relation
• Let E1 and E2 be relational-algebra expressions; the following are all
– E1 E2
– E1 - E2
– E1 x E2
– p (E1), P is a predicate on attributes in E1
– s(E1), S is a list consisting of some of the attributes in E1
– x (E1), x is the new name for the result of E1
• The relational algebraic operations are divided in to two groups:
1) The first group includes the set operations. Set operations include the
c) set difference
d) cartesian product
2) The second group of relational algebraic operation is developed specially
relational databases .some of the operation of this group are
- Two relations are said to be union compatible if the following conditions
• The two relations/tables must contain the same number of columns(have the
• Each column of the first relation/ table must be either the same data type as
the corresponding column of the second relation/table or convertible to the
same data type as corresponding column of the second.
Customer _ name city
Customer name city
• The result of operation is denoted by depositor U borrower is the
relation that includes all tuples that are either in depositor or borrower
or in both.
• duplicates are eliminated.
• Query : find the names of all bank customer who have an account or
loan or both.
• the result of union.
dep U borrow
Cust name city