Define: data model
a collection of concepts that can be used to describe the structure of a database [structure of a database = the data types, relationships, and constraints that apply to the data];
most data models also include a set of basic operations for specifying retrievals and updates on the DB
Define: database schema
the description of a database; specified during design; not expected to change frequently
Define: database state
AKA snapshot; the data in the DB at a particular moment in time; AKA the current set of occurrences or instances in the DB
Define: internal schema
at the internal level; lowest level of three-schema architecture;
describes the physical storage structure of the DB;
uses a physical data model;
describes the complete details of data storage and access paths for the DB
Define: conceptual schema
at the conceptual level; middle level of three-schema architecture;
describes the structure of the whole DB for a community of users;
described by a conceptual data model;
hides the details of physical storage structures and concentrates on describing entities, data types, relationships, user operations, and constraints;
This implementation conceptual schema is often based on a conceptual schema design in a high-level data model.
Define: external schema
at the view level AKA external level
AKA user views; top level of three-schema architecture;
The external or view level includes a number of external schemas or user views.
each external schema:
- describes the part of the DB that a particular user group is interested in and hides the rest of the DB from that user group;
- is typically implemented using a representational data model, possibly based on an external schema design in a high-level conceptual data model.
Define: data independence
the capacity to change the schema at one level of a DB system without having to change the schema at the next higher level;
2 types:
- logical data independence:
- the capacity to change the conceptual schema without having to change the external schema or app programs
- Only the view definition and the mappings need to be changed in a DBMS that supports logical data independence. After the conceptual schema undergoes a logical reorganization, application programs that reference the external schema constructs must work as before. Changes to constraints can be applied to the conceptual schema without affecting the external schemas or application programs.
- 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
- If the same data as before remains in the DB, we should not have to change the conceptual schema.
Define: DDL
Data Definition Language;
- in DBMSs where NO strict separation btwn arch. levels is maintained: DDL is used by the DBA and DB designers to define conceptual and internal schemas
- in DBMSs WITH clear separation btwn arch. levels is maintained: DDL is used to specify the conceptual schema only; can also be used to specify mappings btwn internal and conceptual schemas
- in most DBMSs the DDL is used to define both the conceptual and external schemas (instead of a VDL)
Define: DML
Data Manipulation Language;
(once DB schemas are compiled and the DB is populated with data) DML is used to perform manipulations of the data in the DB, such as:
retrieval, insertion, deletion, and modification of the data
The DBMS provides a set of operations or a language called the data manipulation language (DML) for these purposes.
2 main types of DMLs:
- high-level/nonprocedural DMLs:
- AKA set-at-a-time DMLs, AKA set-oriented DMs, AKA a declarative language
- can be used on its own to specify complex DB ops concisely; specifies WHICH data to retrieve rather than HOW to retrieve it
- low-level/procedural DMLs:
- AKA record-at-a-time DMLs
- MUST be embedded in a general purpose programming language; typically retrieves individual records or objects from the DB and processes each separately
Define: SDL
Storage Definition Language;
in DBMSs WITH clear separation btwn arch. levels is maintained: SDL is used to specify internal schema; can also be used to specify mappings btwn internal and conceptual schemas;
in relational DBMSs today, there is no specific language that performs the role of the SDL
Define: VDL
View Definition Language;
in true three-tier architecture, VDL would be used to specify user views and their mappings to the conceptual schema (but in most DBMSs the DDL is used to define both the conceptual and external schemas)
in relational DBMSs, SQL is used in the role of VDL to define user or application views as a result of predefined queries
Define: query language
a high-level DML used in a standalone interactive manner;
in general, both retrieval and update commands of a high-level DML may be used interactively and are hence considered part of the query language
Define: host language
whenever DML commands, whether at a high level or low level, are embedded in a general-purpose programming language, that language is called the host language and the DML is called the data sublanguage
Define: data sublanguage
whenever DML commands, whether at a high level or low level, are embedded in a general-purpose programming language, that language is called the host language and the DML is called the data sublanguage
Define: database utility
part of a DBMS that helps the DBA manage the DB system;
common functionalities of utilities:
* Loading utility [load existing files into the DB]
* Backup utility
* Database storage reorganization utility
* Performance monitoring utilitiy [monitors performance and usage]
* others:
** sorting files
** handling data compression
** monitoring access by users
** interfacing with the network
** performing other functions
Define: catalog
[DBMS catalog?? AKA system catalog]
- holds the meta-data (descriptions of the schemas)
- [in the case of multiple-level DBMS:] can also hold mapping information between the various levels
- includes info such as the names and sizes of files, names and data types of data items, storage details of each files, mapping info among schemas, and constraints
- also stores many other types of info that are needed by the DBMS modules, which can then look up the catalog info as needed
[generic catalog??]
* “Three of the four important characteristics of the database approach, listed in Section 1.3, are (1) use of a catalog to store the database description (schema) so as to make it self-describing, (2) insulation of programs and data (program-data and program-operation independence), and (3), support of multiple user views.”
Define: client/server architecture
an architectural framework where a large number of computing environments are connected via a network;
- defines specialized servers with specific functionalities;
- client machines provide the user with appropriate interfaces to utilize these servers, as well as with the local processing power to run local applications
- the resources provided by specialized servers can be accessed by many client machines
Define: three-tier architecture
a three-tiered client/server architecture:
- client tier: AKA client, AKA presentation layer, AKA user interface
- contains UIs and web browsers
- middle tier: AKA application server, AKA web server, AKA business logic layer, AKA application rules
- runs app programs and stores business rules; accepts requests from clients, processes requests and sends DB queries and commands to the DB server, then acts as a conduit for passing partially processed data from the DB server to the clients
- server tier: AKA database server, AKA database services layer, AKA data access
- includes all data management services
Define: n-tier architecture
divide up the layers between the user and the stored data into finer components;
n may be four or five tiers;
typically the business logic layer is divided into multiple layers;
distributes programming and data throughout a network;
another advantage; any one tier can run on an appropriate processor or OS platform and handled independently
What concepts do high-level or conceptual data models use?
concepts that are close to the way many users perceive data;
concepts such as entities, attributes, and relationships
How are they [high-level or conceptual data models] different from low-level or physical models?
low-level/physical models:
- provide concepts that describe how data is stored on the computer storage media, typically magnetic disks;
- describe how data is stored as files in the computer by representing information such as record formats, record orderings, and access paths
concepts provided by physical data models are generally meant for computer specialists, not for end users
What is the difference between a database schema and a database state?
- DB schema: a description of the database; specified during data design and not expected to change frequently; AKA “intension”
- DB state: the data in the DB at a particular moment in time; may change quite frequently; AKA “extension”
- when we define a new DB, we specify the DB schema only; at this point the corresponding DB state is the empty state with no data
- when we first populate or load the initial data into the DB we get the initial state
- from then on, any time an update operation is applied to the DB, we get another DB state
- at any given point in time, the DB has a current state
- DBMS is partially responsible for ensuring that every state of the DB is a valid state: i.e. a state that satisfies the structure and constraints specified in the schema
Describe the three-schema architecture.
AKA ANSI/SPARC arch;
- proposed to help achieve and validate three of the four important characteristics of the DB approach:
1) use of a catalog to store the DB description (schema) so as to make it self-describing;
2) insulation of programs and data (program-data and program-operation independence);
3) support of multiple user views; - The three-level ANSI[/SPARC] architecture has an important place in database technology development because it clearly separates the users’ external level, the database’s conceptual level, and the internal storage level for designing a database.
internal schema:
at the internal level; lowest level of three-schema architecture;
describes the physical storage structure of the DB;
uses a physical data model;
describes the complete details of data storage and access paths for the DB
conceptual schema:
at the conceptual level; middle level of three-schema architecture;
describes the structure of the whole DB for a community of users;
described by a conceptual data model;
hides the details of physical storage structures and concentrates on describing entities, data types, relationships, user operations, and constraints;
This implementation conceptual schema is often based on a conceptual schema design in a high-level data model.
external schemas:
at the view level AKA external level
AKA user views; top level of three-schema architecture;
The external or view level includes a number of external schemas or user views.
each external schema:
- describes the part of the DB that a particular user group is interested in and hides the rest of the DB from that user group;
- is typically implemented using a representational data model, possibly based on an external schema design in a high-level conceptual data model.
[three-schema architecture] Why do we need mappings among schema levels?
the actual data is only stored at the physical level; each user-group refers to its own external schema:
- so the DBMS has to transform a request specified on an external schema into a request specified against the conceptual schema, and then into a request on the internal schema for processing over the stored DB;
- if the request is a retrieval, then the DBMS has to reformat the result data extracted from the stored DB into the user’s external view
