DATABASE Lecture # 5, 6
SYSTEMS
IN LAST WEEK….
Defining ( Data, Database, Data Model,
DBMS, DBA)
Roles in Database
File System Vs. Database
Meta Data
IN THIS LECTURE….
Three Level Architecture
Phases of Database Development
3-LEVEL
ARCHITECTURE OF Book Page#29
DATABASE
SYSTEMS
INTRO
• The ANSI-SPARC database architecture
is the basis of most of the modern
databases.
• It is important to keep in mind that all
these schemas are just different ways
of visualizing structure of the same
database by different stakeholders.
THREE LEVEL
ARCHITECTURE
External Schema 1 External Schema 2 External Schema 3
Conceptual Schema
Physical Schema
PHYSICAL SCHEMA
• It is the lowest level and also called internal
level.
• 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.
• The physical level also discusses
compression and encryption techniques.
CONCEPTUAL SCHEMA
• Sometimes called Logical Schema.
•It describes how the database appears to the
users conceptually and the relationships
between various data tables.
•Database constraints and security are also
implemented in this level of architecture.
•The conceptual level does not care for how the
data in the database is actually stored.
• Define the stored data in terms of data model.
• This schema describes 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)
EXTERNAL SCHEMA
• External view for the end users of Database
is called external schema.
• Each external level view is used to cater to
the needs of a particular category of users.
• 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?
EXTERNAL SCHEMA
• For Example, FACULTY of a university
is interested in looking course details of
students, STUDENTS are interested in
looking at all details related to
academics, accounts, courses and
hostel details as well.
• So, different views can be generated
for different users. The main focus of
external level is data abstraction.
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
Two levels of data independence which we have to achieve:
Level 1:Physical Data Independence
Any change in the physical location of tables and
indexes should not affect the conceptual level or
external view of data.
This data independence is easy to achieve and
implemented by most of the DBMS.
Level 2: Conceptual Data Independence
Changes made to the conceptual model should not
effect the external model.
Adding or deleting attributes of a table should not
affect the user’s view of the table.
But this type of independence is difficult to achieve as
compared to physical data independence because the
changes in conceptual schema are reflected in the
user’s view.
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 detailed conceptual data model,
including entities and relationship.
•Conceptual data model, describes the
database at a very high level and is
useful to understand the needs or
requirements of the database.
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/create database in DBMS
• Decide physical organization of data
(data structure, indexing)
• Design database processing programs
(flowcharts, algorithms)
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 fulfilling.
• update database for improved performance.
• Fix error in database and database
application.
• Recover database when it is contaminated.
CONCEPTUAL
DATA MODELING
DATA MODELING
• Crucial phase in Database development
•Representation of real-time objects, their
properties and defining relation among
them is called data modeling.
•Output of this phase will be ERD(Entity
Relationship Diagram) that represent
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
David
Michelle Johnson
Brady
Accounts Manufacturing
Name Gender
Qualification
Teacher
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
STRONG VS WEAK
ENTITIES
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
treasurer 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 in capital letters in ERD
Name should be specific to the
organization
Entity Name should be concise
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_Addre
ss,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
