Video 5

Interfaces for the DBA: (DBA means Database Administrator)

(1) Creating accounts, granting authorizations (2) Setting system parameters (3) Changing schemas or access path

What are the 2 interface approaches in embedding DML in programming languages?

(1) Pre-compiler Approach (2) Procedure (Subroutine) Call Approach

When a schema at a *lower level* is changed, only the

*mappings* between this schema and *higher-level* schemas need to be changed in a DBMS that fully supports data independence. *The higher-level schemas themselves are unchanged*. Hence, the application programs *need not be changed* since the refer to the external schemas. (Data independence occurs because when the schema is changed at some level, the schema at the next higher level remains unchanged; only the mapping between the two levels is changed. Hence, application programs referring to the higher-level schema need not be changed.)

What are Other classifications of DBMSs

- *Single-user* (typically used with micro-computers) vs. *multi-user* (most DBMSs). - *Centralized* (uses a single computer) vs. *distributed* (uses multiple computers). - *Cost* of DBMS software. - Types of access paths used.

Classifications of DBMSs are based on which data models

- *Traditional*: Relational, Network, Hierarchical. - *Emerging*: Object-oriented, Semantic, deductive/logic, rule-based/expert, other.

What are some Database System Utilities?

- Loading data stored in files into a database. - Backing up the database periodically on tape. - Reorganizing database file structures. - Report generation utilities. - Performance monitoring utilities. - Other functions, such as sorting, user monitoring, data compression, etc.

DBMS Interfaces

- Stand-alone query language interfaces (for you to interact with the DBMS) - Programmer interfaces for embedding DML in programming languages: (1) Pre-compiler Approach (2) Procedure (Subroutine) Call Approach - User-friendly interfaces: (1) Menu-based (2) Graphics-based (3) Forms-based (4) Natural language (5) Combinations of the above - Parametric interfaces using function keys. - Report generation languages.

Types of Data dictionary utility?

Active and Passive

Classifications of DBMSs are

Based on the data model used

Declarative or non-procedural DML:

Can be used as a stand-alone query language or can be embedded in a programming language.

Active data dictionary is accessed by

DBMS software and users/DBA.

Procedural DML:

Must be embedded in a programming language.

Procedural DML uses what models?

Network, hierarchical

What are Emerging data models?

Object-oriented, Semantic, deductive/logic, rule-based/expert, other.

Difference between active data dictionary and passive data dictionary?

Passive is only accessed by users/DBA WHEREAS Active is used by users/DBA *AND* DBMS Software

Types of DML:

Procedural DML, Declarative or non-procedural DML

What are Traditional data models?

Relational, Network, Hierarchical.

What belongs to category of Declarative or non-procedural DML

SQL

Logical Data Independence:

The capacity to change the *conceptual schema* without having to change the *external schemas* and their application programs. (We may change the conceptual schema to expand the database)

Physical Data Independence:

The capacity to change the *internal schema* without having to change the *conceptual schema.* Hence, the external schemas need not be changed as well. (Changes to the internal schema may be needed because some physical files were reorganized to improve the performance of retrieval or update.)

Data Definition Language (DDL):

Used by the DBA and database designers to specify the *conceptual schema* of a database. In many DBMSs, the DDL is also used to define internal and external schemas (views). In some DBMSs, separate storage definition language (SDL) and view definition language (VDL) are used to define internal and external schemas.

Data Manipulation Language (DML):

Used to specify database retrievals and updates.

Can you get multiple records using Procedural DML?

Yes but you need to use loops because it only receives one record at a time

Alternatively, stand-alone DML commands can be

applied directly (query language).

Pre-compiler Approach means

before you compile your code. Because its embedded, you got to convert it to syntax compiler can recognize

logical data independence is between

conceptual and external (middle and top)

DML commands (data sublanguage) can be embedded in a

general-purpose programming language (host language), such as COBOL, PL/1 or PASCAL.

Procedural DML searches for and retrieves...

individual database records, and uses looping and other constructs of the host programming language to retrieve multiple records.

Declarative or non-procedural DML searches for and retrieves...

information from multiple related database records in a single command.

physical data independence is between

internal and conceptual (lowest and middle)

Database System Utilities are used to

perform certain functions

Procedural DML is also called

record-at-a-time or low-level DNC.

Declarative or non-procedural DML is also called

set-at-a-time or high-level DML.

What is a Data dictionary utility used for?

store schema descriptions and other information such as design decisions, application program descriptions, user information, usage standards, etc.

Passive data dictionary is accessed by

users/DBA only

Procedure (Subroutine) Call Approach means

you create different procedures, you call them, and they perform whatever function you want to use