DATABASE ENVIRONMENT

1
DATABASE ENVIRONMENT
2
Dream World Case Study

• Real World entities Staff, property, Owner and
  Renters
• Attributes describing properties or qualities of
  each entity
• Relationships between these entities (Staff
  MANAGES Property)

3
ANSI-SPARC three-level architecture
View 1
View 2
View n
External Level
Conceptual Level
Conceptual schema
Internal Level
Internal schema
Database
American National Standard Institute (ANSI)-
Standards Planning and Requirements Committee
(SPARC)
4

• EXTERNAL LEVEL
• Users view of the database
• Part of the database that is relevant to a user
• CONCEPTUAL LEVEL
• Community view of the database
• What data is stored in the database
• Relationships among the data
• Entities, their attributes and relationships
• Security and integrity information

5
INTERNAL LEVEL

• Physical representation of the database on the
  computer
• Internal level
• Storage space allocation of data and indexes
• Record description for storage
• Record placement (pointers)
• Data compression and encryption techniques

6
Differences between the three levels
External View 1
External View 2
Sno Staff_No Fname Lname Age Salary
Staff_No Lname Branch_No
Staff_No Fname Lname DOB Salary Branch_No
Conceptual level
Struct STAFF int Staff_No int
Branch_No char Fname15 char
Lname15 struct date Date_of_Birth float
salary struct STAFF next //pointer to next
staff record index Staff_No index
Branch_No // define indexes for staff
Internal level
7
DATA INDEPENDENCE

• Logical Data Independence Possibility for
  addition/removal of new entities, attributes or
  relationships
• Physical Data Independence Possibility for
  changes to storage structures

8
Data Independence
External schema
External schema
External schema
External/ conceptual mapping
Logical data independence
Conceptual schema
Conceptual/ Internal mapping
Physical data independence
Internal schema
9
Database Languages

• Data definition Language (DDL)
• A descriptive language
• Name the entities and relationships
• Data Manipulation Language (DML)
• Data Manipulation Operations
• Insertion of new data
• Modification of data
• Retrieval of data
• Deletion of data

10
DMLTypes

• Procedural DML
• What data is needed
• How to retrieve this? (record by record)
• Non-Procedural DML
• What data is needed rather than how to retrieve
  it. (based on set of records)

11
4GL

• Query languages, Form generators, Report
  generators.
• Specialty languages such as spreadsheets and
  database languages
• Application generators (define, insert, update
  and retrieve data)
• Program Generators
• Graphics generators

12
Data Model

• An integrated collection of concepts
• For describing data
• Relationships
• Constraints
• Object Based Data Models
• Entity
• Attributes
• Behavior
• Record Based Data Model
• Relational, Network, Hierarchical,
  Multidimensional

13
(No Transcript)
14
Network Data Model
15
Relational Data Model
16
Multidimensional Data Model
17
Object Oriented Data Model
18
Functions of a DBMS

• 1) Data storage, retrieval and update
• 2) A user accessible catalog
• System Catalog stores
• Names, sizes and types of data items
• Names of relationships
• Integrity constraints
• Names of authorized users who have access
• External, conceptual and internal schemas
• Usage statistics

19
(Functions contd)

• 2) A user accessible catalog (contd.)
• Benefits of a System catalog
• Collection and storage at one place
• Meaning of the data can be referred
• Communication is simplified
• Redundancy and inconsistencies can be identified
  more easily
• Changes to the database can be recorded
• Security
• Integrity
• 3) Transaction Support
• 4) Concurrency control services

20
The Lost Update Problem
Time T1 T2 balx t1 read
(balx) 100 t2 real (balx) balx balx
100 100 t3 balxbalx-10 write (balx
) 200 t4 write (balx ) 90 t5 90
21
(Functions contd)

• 5) Recovery services
• 6) Authorization services
• 7) Support for data communication
• 8) Integrity services
• 9) Service to promote data independence.
• 10) Utility services

22
Components of a DBMS
Users
DBA
Programmers
Queries
Application Programs
Database schema
DBMS
DML Preprocessor
Query Processor
DDL Compiler
Program object code
Database Manager
Dictionary Manager
Access methods
File Manager
System buffers
Database and system catalog
23
Components of a database manager
Program Object Code
Query processor
Catalog Manager
Database manager
Authorization Control
Integrity Checker
Command processor
Query Optimizer
Transaction Manager
Scheduler
Data manager
Recovery Manager
Buffer Manager
File Manager
Access Methods
System buffers
Databases and system catalogs
24
Teleprocessing Topology
25
File Server architecture
A Large amount of network traffic A full copy of
DBMS is required on each workstation Concurrency,
recovery and integrity control are more complex
Workstation 2
Workstation 1
Workstation 3
LAN
Request for data
database
File Server
Client Server architecture
Client 2
Client 1
Client 3
LAN
Selected data returned
Request for data
database
Server (with DBMS)
26
Alternate client-server topologies
Single Client - Single Server
client
Server
Client 1
Multiple Client - Single Server
Server
Client 2
Multiple Client - Multiple Server
Client 1
Server 1
Client 2
Server 2
Client 3
27
Summary of Client server applications

• Client
• Manages the user interface
• Accepts and check syntax of user input
• Process application
• Generates database requests and transmits to the
  server
• Passes response back to user

• Server
• Accepts and processes database requests from
  clients
• Checks authorization
• Ensures integrity constraints not violated
• Performs query/ update processing and transmits
  response to client
• Maintains system catalog
• provides concurrent database access
• Provides recovery control