Transcript SSSS - Computer Science

The Database System
Environment
Dr. Awad Khalil
Computer Science Department
AUC
CSCI 453 -- DBMS Environment
1
Content

DBMS Environment

DBMS Utilities

The System Catalog
The Data Dictionary
 The System Catalog for Relational DBMS
 Accessing Catalog Information

CSCI 453 -- DBMS Environment
2
Database System
Users/Programmers
DATABASE
SYSTEM
Application Programs/Queries
DBMS
Software
Software to Process
Queries/Programs
Software to Access
Stored Data
Stored Database
Definition
(Meta-Data)
Stored
Database
CSCI 453 -- DBMS Environment
3
DBMS Components
CSCI 453 -- DBMS Environment
4
A Database Schema in SQL - DDL
CREATE TABLE PROJECT
CREATE TABLE EMPLOYEE
(PNAME
VARCHAR(15)
(FNAME
VARCHAR(15) NOT NULL,
NOT NULL,
MINIT
CHAR,
PNUMBER
INT
LNAME
VARCHAR(15) NOT NULL,
NOT NULL,
SSN
SSN_TYPE NOT NULL,
PLOCATION VARCHAR(15)
BDATE
DATE
DNUM
INT
ADDRESS VARCHAR(30),
NOT NULL,
SEX
CHAR,
PRIMARY KEY (PNUMBER),
SALARY
DECIMAL(10,2),
UNIQUE (PNAME)
SUPERSSN SSN_TYPE,
FOREIGN KEY (DNUM) REFERENCES
DEPARTMENT(DNUMBER));
DNO
INT
NOT NULL,
CREATE TABLE WORKS_ON
PRIMARY KEY (SSN),
(ESSN
SSN_TYPE NOT NULL,
FOREIGN KEY (SUPERSSN) REFERENCES
EMPLOYEE(SSN),
PNO
INT
NOT NULL,
FOREIGN KEY (DNO) REFERENCES
HOURS
DECIMAL(3,1) NOT NULL,
DEPARTMENT(DNUMBER));
PRIMARY KEY (ESSN, PNO),
CREATE TABLE DEPARTMENT
FOREIGN KEY (ESSN) REFERENCES EMPLOYEE(SSN),
(DNAME
VARCHAR(15) NOT NULL,
FOREIGN KEY (PNO) REFERENCES PROJECT(PNUMBER));
DNUMBER INT,
NOT NULL,
CREATE TABLE DEPENDENT
MGRSSN
SSN_TYPE NOT NULL,
(ESSN
SSN_TYPE NOT NULL,
MGRSTARTDATE
DATE
DEPENDENT_NAME VARCHAR(15) NOT NULL,
PRIMARY KEY (DNUMBER),
SEX
CHAR,
UNIQUE (DNAME)
BDATE
DATE,
FOREIGN KEY (MGRSSN) REFERENCES
RELATIONSHIP
EMPLOYEE(SSN));
VARCHAR(8)
CREATE TABLE DEPT_LOCATIONS
PRIMARY KEY (ESSN, DEPENDENT_NAME),
(DNUMBER INT
NOT NULL,
FOREIGN KEY (ESSN) REFERENCES EMPLOYEE(SSN));
DLOCATION VARCHAR(15) NOT NULL,
PRIMARY KEY (DNUMBER, DLOCATION),
FOREIGN KEY (DNUMBER) REFERENCES
CSCI 453 -- DBMS Environment
DEPARTMENT(DNUMBER));
5
DBMS Components (Cont’d)

A DBMS is a complex software system.

The database and the system catalog are usually stored
on disk.

Access to the disk is controlled primarily by the operating
system (OS), which schedules disk input/output..

A higher-level stored data manager module of the DBMS
controls access to DBMS information stored on disk.

The precompiler extracts DML commands from an
application program written in a host language. These
commands are sent to the DML compiler for compilation
into object code for database access.
CSCI 453 -- DBMS Environment
6
DBMS Components (Cont’d)
 The
DDL compiler processes schema definitions
and stores descriptions of the schemas (metadata) in the DBMS catalog.
 The
run-time database processor
database accesses at run time.
handles
 The
query compiler handles high-level queries
that are entered interactively.
CSCI 453 -- DBMS Environment
7
DBMS Components (Cont’d)










DBMS Engine
Interface Subsystem (DDL, DML, DCL, Graphical User
Interface, Forms Interface, Natural Language Interface
System Catalog
Concurrency Control Subsystem
Backup and Recovery Subsystem
Query Optimization Subsystem
Performance Management Subsystem
Data Integrity Management Subsystem
Application Development Subsystem
Security Management Subsystem
CSCI 453 -- DBMS Environment
8
DBMS Utilities

Loading: A loading utility is used to load existing data files such as text files or sequential files - into the database.

Backup: A backup utility creates a backup copy of the
database, usually by dumping the entire database onto
tape.

File reorganization: This utility can be used to reorganize
a database file into a different file organization to improve
performance.

Performance monitoring: Such a utility monitors database
usage and provides statistics to the DBA.
CSCI 453 -- DBMS Environment
9
The System Catalog
System Catalog is at the heart of any generalpurpose DBMS.
 The
is a “minidatabase” itself, and its function is to
store the schemas, or descriptions, of the databases that
the DBMS maintains.
 It
 The
catalog stores data that describes each database;
such data is often called meta-data. It includes a
description of the conceptual database schema, the
internal schema, any external schemas, and the
mappings between the schemas at different levels.
CSCI 453 -- DBMS Environment
10
Data Dictionary versus System Catalog

The term data dictionary is often used to indicate a more
general software utility than a catalog.
catalog is closely coupled with the DBMS software;
it provides the information stored in it to users and the
DBA, but it is mainly accessed by the various software
modules of the DBMS itself, such as DDL, and DML
compilers, the query optimizer, the transaction
processor, report generators, and the constraint enforcer.
A
data dictionary software package may interact with
the software modules of the DBMS but is mainly used
by the designers, users, and administrators of a computer
system for information management.
A
CSCI 453 -- DBMS Environment
11
Data Dictionary
CSCI 453 -- DBMS Environment
12
Catalogs for Relational DBMSs
 The
information stored in a catalog of a relational
DBMS includes description of the following:










Relation names,
Attribute names,
Attribute domains (data types),
Primary keys,
Secondary key attributes,
Foreign keys,
Other types of constraints.
Descriptions of views.
Internal-level description,
Security/authorization information.
CSCI 453 -- DBMS Environment
13
Catalogs for Relational DBMSs (Cont’d)
 In
relational DBMSs it is common practice to store the
catalog itself as relations and to use the DBMS software
for querying, updating, and maintaining the catalog.
CSCI 453 -- DBMS Environment
14
Catalogs for Relational DBMSs (Cont’d)
CSCI 453 -- DBMS Environment
15
Catalogs for Relational DBMSs (Cont’d)
CSCI 453 -- DBMS Environment
16
Accessing Catalog Information
following DBMS modules use and access a catalog
very frequently; that is why it is important to implement
access to the catalog as efficiently as possible.
 The

DDL compilers.

Query and DML parser and verifier.

Query and DML compiler.

Query and DML optimizer.

Authorization and security checking.

External-to-conceptual
commands.
mapping
of
CSCI 453 -- DBMS Environment
queries
and
DML
17
Thank you
CSCI 453 -- DBMS Environment
18