Introduction to Database

Systems
Lecture 2
Ms. Qudsia Yousaf
Spring 2024
 Three-Level
ANSI-SPARC
Architecture
American National
Standards Institute (ANSI)

Standards Planning and

Requirements Committee
(SPARC)

• Architecture: Refers to permanent structure

• Represented by three schemas or three models
2
  The way users perceive the data is called the
external level.
 The way the DBMS and the operating system
perceive the data is the internal level, where
the data is actually stored using the data
structures and file organizations.
Three-level
 The conceptual level provides both the
Architecture mapping and the desired independence
between the external and internal levels.
 The objective of the three-level architecture is
to separate each user’s view of the database
from the way the database is physically
represented.
3
Three Views
of the Data
 Reasons Behind Separation?
 Each user should be able to access the same data, but have a different customized
view of the data. Each user should be able to change the way he or she views the
data, and this change should not affect other users.

 Users should not have to deal directly with physical database storage details, such as
indexing or hashing. In other words, a user’s interaction with the database should be
independent of storage considerations.

 The Database Administrator should be able to change the database storage structures
without affecting the users’ views.

 The internal structure of the database should be unaffected by changes to the physical
aspects of storage, such as the changeover to a new storage device.

 The DBA should be able to change the conceptual structure of the database without
affecting all users.
5
 External View
 The users’ view of the database. This level describes only that part of the
database that is relevant to each user.
 Different views may have different representations of the same data. For
example, one user may view dates in the form (day, month, year), while
another may view dates as (year, month, day).
• Some views might include derived or calculated data (data not actually
stored in the database as such, but created when needed. E.g. age of a
member of staff)
 External schema evolves as user needs are modified over time.

6
  The community view of the database. This level
describes what data is stored in the database and the
relationships among the data.
 This level contains the logical structure of the entire
database as seen by the DBA.
 It is a complete view of the data requirements of the
organization that is independent of any storage
Conceptual considerations. It represents

Level  All entities, their attributes, and their relationships and the
constraints on the data;
 Semantic information about the data;
 Security and integrity information.
 Conceptual level supports each external view, in that any
data available to a user must be contained in, or
derivable from, but No storage considerations, such as
the number of bytes occupied.
7
  The physical representation of the database on the
computer. This level describes how the data is stored
in the database. It is concerned with:

Internal Level  storage space allocation for data and indexes;

 record descriptions for storage (with stored sizes for data
items);
OR  data compression and data encryption techniques.

 Below the internal level, there is a physical level that

Physical Level may be managed by the operating system under the
direction of the DBMS.
 However, the functions of the DBMS and the
operating system at the physical level vary from
system to system.

8
 First Name: Rana Name:
Last Name: Aslam R. Aslam
Date of Birth: Age: 24y, 10d
12 Sep, 1970 Dept: Sales

Name DoB Deps DepId

Three-level Rana Aslam 12/09/70 5 D001

Architecture Marya Wasti 29/02/80 0 D005

BH RH Rana Aslam 120970 5 D001 RH Marya

Wasti

01110011010011100101001010100
101

9
  The overall description of the database is called the database schema. A
database schema is a predefined, structured framework that defines the
organization, relationships, and constraints of data within a database.
There are three different types of schemas in the database.
 At the highest level, we have multiple external schemas
 that correspond to different views of the data.
 At the conceptual level,
Database  we have the conceptual schema, which describes all the entities, attributes,
Schema and relationships together with integrity constraints.
 At the lowest level of abstraction we have the internal schema,
 which is a complete description of the internal model, containing the
definitions of stored records, the data fields, and the indexes and storage
structures used.
 There is only one conceptual schema and one internal schema per
database.`
 The database schema is specified during the database design process
and is not expected to change frequently. However, the actual data10in the
database may change frequently.
 Difference
between
Three-levels

11
 Example: University Database

 External Schema (View 1 ): Course_info(cid:string,cname:string)

 External Schema (View 2): student_info(cid:string, name:string)

 Students(sid: string
name: string,
login: string,
 Conceptual schema: age: integer)
 Courses(cid: string,
cname:string,
credits:integer)
 Enrolled(eid:string,
cid:string,
grade:string)

 Physical schema:  Relations stored as unordered files.

 Index on first column of Students.
Instance
• State of the database or snapshot
• When the schema framework is filled in the data item values or
the contents of the database at any point of time is referred to as
instance of the database
• At any point of time, the current state of the database is called
instance

13
Instance

www.cs.gsu.edu/
.../
CSCI%203360%20Database%20Syst
ems/
Chapt
er%20.. 14
 Data
Independence

15
  A major objective for the three-level architecture is
to provide data independence, which means that
upper levels are unaffected by changes to lower
levels. There are two kinds of data independence:

 Logical data independence: Logical data

Data independence refers to the immunity of the external
Independence schemas to changes in the conceptual schema.

 Physical data independence: Physical data

independence refers to the immunity of the
conceptual schema to changes in the internal
schema.

16
  The Data Definition Language
 A language that allows the DBA/user to describe and name
the entities, attributes, and relationships required for the
application, together with any associated integrity and
security constraints.
 Data Manipulation Language (DML)
Database  Provides basic data manipulation operations on data held in
the database.
Languages  Procedural DML
 A language that allows the user to tell the system what data
is needed and exactly how to retrieve the data.
 Specify what data is required and how to access that data
• Non-Procedural DML
 Allows user to state what data is needed rather than how it is
to be retrieved.
 Specify what data is required but not specify how to retrieve
14
Procedural DML
CREATE PROCEDURE CalculateOrderTotal
@OrderId INT,
@TotalAmount DECIMAL(10, 2) OUTPUT
AS
BEGIN
DECLARE @ItemCount INT
DECLARE @UnitPrice DECIMAL(10, 2)
DECLARE @Discount DECIMAL(5, 2)
-- Calculate total number of items and their unit price for the given order
SELECT @ItemCount = COUNT(*), @UnitPrice = SUM(UnitPrice)
FROM OrderDetails
WHERE OrderId = @OrderId
-- Determine discount based on the total number of items
IF @ItemCount > 10
SET @Discount = 0.1
ELSE
SET @Discount = 0
-- Calculate total amount after discount
SET @TotalAmount = @UnitPrice * (1 - @Discount)
END
Non-Procedural DML
SELECT OrderId,
SUM(UnitPrice * Quantity) * (1 - CASE WHEN COUNT(*) >
10 THEN 0.1 ELSE 0 END) AS TotalAmount
FROM OrderDetails
WHERE OrderId = 123
GROUP BY OrderId;
 Fourth Generation Languages (4GLs)
 There is no agreement about what constitutes a 4GL.

Database  An operation that requires hundreds of lines of code in

3GL, generally requires fewer lines in a 4GL.
Languages
 3GL are procedural, while a 4GL is nonprocedural: in
which user defines what is to be done, not how.

 The user does not define the steps that a program

needs to perform a task, but instead defines
parameters for the tools that use them to generate an
application program.

14
  Fourth-generation languages encompass:
 Presentation languages, such as query
Database languages and report generators;

Languages  Specialty languages, such as spreadsheets

and database languages;
 Application generators that define, insert,
update, and retrieve data from the database
to build applications;
 Very high-level languages that are used to
generate application code. 14
  A model is a representation of ‘real world’ objects
and events, and their associations.
 Data model is integrated collection of concepts for
describing and manipulating data, relationships
between data, and constraints on the data in an
organization.
Data Model  A data model comprising three components:
 A structural part, consisting of a set of rules
according to which databases can be constructed
 A manipulative part, defining the types of operation
that are allowed on the data (this includes the
operations that are used for updating or retrieving
data from the database and for changing the
structure of the database)
 Possibly a set of integrity constraints, which
22
  The purpose of a data model is to represent
data and to make the data understandable.
 If it does this, then it can be easily used to
design a database.
Data Model  There have been many data models proposed
in the literature. They fall into three broad
categories:
 Object-based
 Record-based,
 Physical data models.
 The first two are used to describe data at the
conceptual and external levels, the latter is
used to describe data at the internal level.
23
  Object-based data models use concepts such as
entities, attributes, and relationships.
 An entity is a distinct object (a person, place, thing,
Object Based concept, event) in the organization that is to be
represented in the database.
Data Models  An attribute is a property that describes some aspect
used to describe of the object that we wish to record,
data at the
conceptual and
 A relationship is an association between entities.
external levels  Common types of object-based data model are:

 Entity-Relationship (ER)
 Object-Oriented

24
  The ER model is primarily used to represent
the structure of data in a database system. It
focuses on entities, their attributes, and the
relationships between entities.
Object Based
Data Models  Object-Oriented data model extends the
definition of an entity to include not only the
attributes that describe the state of the object
but also the actions that are associated with
the object, that is, its behavior.
 The object is said to encapsulate both state
and behavior.

25
Object Oriented
Model

ER Model
  In a record-based model, the database consists of
a number of fixed-format records possibly of differing
types.
 Each record type defines a fixed number of fields,
each typically of a fixed length.
Record Based  There are three principal types of record-based
Data Models logical data model
used to describe  Relational data model
data at the
 Network data model
conceptual and
external levels  and the Hierarchical data model.

 The hierarchical and network data models were

developed almost a decade before the relational data
model, so their links to traditional file processing
concepts are more evident.
27
 Relational Data Model

© Pearson Education Limited 1995, 2005 28

Network Data Model

© Pearson Education Limited 1995, 2005

29
Hierarchal Data Model

© Pearson Education Limited 1995, 2005 30