3-LEVEL ARCHITECTURE OF

DATABASE SYSTEMS
 Three Level Architecture

External Schema 1 External Schema 2 External Schema 3

Conceptual Schema

Physical Schema
 Conceptual Schema
Sometimes called Logical Schema
Define the stored data in terms of
data model
RDBMS describe all relations(Tables)
that are stored on computer
 Conceptual Schema
In University environment we may have
tables of:
Students(sid,name,login,age,gpa),
Faculty(fid,fname,sal)
Courses(cid,cname,credits),
Rooms(rno,address,capacity)
Arriving at a good conceptual schema is
called conceptual database design
 Physical Schema
Physical Schema define the storage
details
Data is stored on computer in the form
of 0 and 1
Data Structures and indexes are use
at this level
 External Schema
External view for the end users of Database is
called external schema
Sometimes generated in terms of Data
Model
DBMS have to process one or more
relations(Tables) to generate external views
Consider the query in university environment:
Who is teaching algorithms to Ali?
 Data Independence
In conventional system we must know
following :
1. What is the format of data?
2. Where is it located?
3. How is it accessed?
Use of data without its representation
details is called data independence
 Data Independence
Changes made in the storage of data
access methods should not effect the
external model
Changes made to the conceptual
model should not effect the external
model
 Data Independence
Two levels of data independence which we
have to achieve:
Level 1:Physical Data Independence
Change made in the storage of data access
methods should not effect the external
model
Level 2: Logical Data Independence
Changes made to the conceptual model
should not effect the external model
DATABASE DEVELOPMENT
PROCESS
Database Development Process
Enterprise Modeling

Conceptual Data Modeling

Logical Database Design

Physical Database Design and definition

Database Implementation

Database Maintenance
Enterprise Modeling
Analyze current data processing
Analyze the general business
functions and their database needs
Justify the need of new databases
in support of business
 Conceptual Data Modeling
Identify the scope of database requirements
Analyze overall data requirements for
business functions supported by
database
Develop preliminary conceptual data model,
including entities and relationship
Develop detailed conceptual data model
including all entities and relationships
Conceptual Data Modeling
 Logical Database Design
Analyze in detail the transactions, form,
displays and inquiries required by the
business functions and supported
by database
Identify data integrity and security
requirements
Logical Database Design
Logical Database Design
Logical Database Design
 Physical Design
Define database to DBMS
Decide physical organization of data
Design database processing programs
 Database Implementation
Code and test database processing
programs
Complete database documentation
and training materials
Install database and convert data from
prior systems
 Database Maintenance
Ensure evolving information requirements
are met
Tune database for improved performance
Fix error in database and database
application
Recover database when it is contaminated
 Database Development Process
Enterprise Modeling

Conceptual Data Modeling

Logical Database Design

Physical Database
Design and definition

Database Implementation

Database Maintenance
 Data Modeling
Crucial phase in Database
development
Technology independent analysis of
the organization's data
Output of this phase will be
ERD(Entity Relationship Diagram)
that model common Business Rules
 Data Modeling
In Short we perform two activities in
this phase
1. Make Entity Relationship Diagram
2. Collect and Documents Business
Rules which effect organizational data
Entity Relationship Model
Introduced by Chen(1976)
Entity Relationship Model is logical
representation of data for an
organization
Define Entities and relationships and
their associated attributes in an
organization using some common
notations and conventions
 Entity Relationship Model
ER- Model consists of three basic
components:
1. Entities
2. Attributes
3. Relationships
Entity Relationship Diagram
 Entity
Entity is a person, place, object,
event, or concept in the user
environment about which the
organization wishes to maintain data
Entities are represented as
boxes(rectangles) in ER-Diagram
 Entities
Person: EMPLOYEE,STUDENT,PATIENT
Place: STORE, WAREHOUSE,STATE
Object: MACHINE,BUILDING,
AUTOMOBILE
Event: SALE,REGISTRATION,RENEWAL
Concept: ACCOUNT, COURSE,
Entity Type & Entity Instance
• Entity Type:
• A collection of entities that share
common properties and characteristics
• Showed on ERD
Entity Instance: A single
occurrence of an entity type in a
specific environment
 Entity Type
Entity Type: EMPLOYEE
Attributes:
EMPLYEE NUMBER
CHAR(10) NAME
CHAR(25) DEPARTMENT
CHAR(20) DATE HIRED
DATE
 Entity Instance
Here we have two instances of
EMPLOYEE entity type:
642-17-8360 534-10-8360

Michelle Brady David Johnson

Accounts Manufacturing

03-21-1992 02-10-1982
 Strong Entity types
Entity type that exists independently of
other entity types is called strong
entity
STUEDNT, AUTOMOBILE etc.
Strong entity always has a unique
characteristics(attribute) called
identifier
 Weak Entity types
Weak Entity is an Entity type whose
existence depends on some other
entity
Weak Entity type has no business
meaning without the
entity(Identifying Owner) on which
it depends
Weak entity does not have its own
identifier
Weak Entity types
 Entity type Selection
Entity Selection is critical issue in ERD
design
ERD is not a Data Flow Diagram(DFD)
Suppose in a business environment one
treasure manages the accounts of
business according to the expenses. He
generates expense report after one
weak.
 Example ERD

Correct

Incorrect
 Naming Entity Types
Entity name is a singular noun and
should write is capital letters in ERD
Name should be specific to the
organization
Entity Name should be concise
Event Entity Types should be named
for the result of the event, not the
activity of the process of the event
Attributes
 Attributes
Each entity has a set of attributes
associated with it
Attribute is a property or
characteristic of an entity types that
is of interest of an organization
 Attributes
STUDENT
Student_ID,Student_Name,Home_Addr
ess,Phone_Number,Blood_Group
AUTOMOBILE
Vehicle_ID,Color,Weight,Horsepower
EMPLOYEE
Employee_ID, Employee_Name,Skill
 Attributes
First letter of the attribute name should
be capital letter
Use under score(_) between two or
more words i.e. Employee_ID
In ERD we use ellipse to represent
attributes of an entity type
Now a days its better to write attributes
inside the Rectangle of Entity Type
Attributes
 Attributes in ERD
DEPENDENT_NAME
Employee_ID Employee_Name Date_of_Birth

EMPLOYEE Has DEPENDENTS

 Types of Attributes
We can distinguish between the following
types of the attributes
1. Required Versus Optional Attributes
2. Simple vs. Composite Attributes
3. Single-Values vs. Multi-valued
Attributes
4. Stored vs. Derived Attributes
5. Identifier Attributes
Attributes
Required versus Optional
An attribute that must be present for each
entity instance is called a required
attribute,
Whereas an attribute that may not have a
value is called an optional attribute. For
 Simple Vs. Composite
Composite Attribute
An attribute that can be broken down into
meaningful component parts
For example Address can be further divided
into Street_Address,
City,State,Postal_Code
Address (Street_Address, City,State,Postal_Code)
 Simple Vs. Composite
Street_Address City
Province
First_Name Last_Nam

Address

Name
 Simple Vs. Composite
Simple Attribute
It is type of attribute that cannot
further divided into smaller parts
For AUTOMOBILE entity type
attributes Vehicle_ID,Color,Weight etc.
are examples of simple attributes
Single Valued Vs. Multi-valued
Employee_ID Employee_Name
Skill

EMPLOYEE

A Multi-Valued attribute is an
attributes which may take more than
one value for a given entity instance
Multivalued attribute is written as: {Skill}
 Stored vs. Derived
Derived Attributes
• An attributes whose value can be
calculated from related
attributes(Stored in database or not)
• For Example: [Years_Employed]
 Identifier Attributes
An identifier attribute is a type of
attribute that uniquely identify every
entity instance
Student_ID in STUDENT entity type
and Employee_ID in EMPLOYEE
Identifier are underlined in Entity
Relationship Diagram(ERD)
 Identifiers
Employee_Name
Employee_ID Skill

EMPLOYEE

Date_Employed
 Identifier Attributes
An identifier attribute is a type of
attribute that uniquely identify every
entity instance
Student_ID in STUDENT entity type
and Employee_ID in EMPLOYEE
Identifier are underlined in Entity
Relationship Diagram(ERD)
Composite Identifier Attribute
Composite identifier is one that
consists of more than one attributes
An entity type may have more than one
identifier
be r
t_ Num Da
h
Flig te

Flight_ID Passenger_Name

Flight
 Identifier Selection
Chose an identifier that will not
change its value
The attribute is guaranteed to have a
valid value and not to be Null
Avoid the use of intelligent identifiers or
keys, whose structure shows
classification, location, so on