Databases are used in various areas, traditional applications include numeric and textual databases, more recent applications incl. multimedia databases, geographic information systems, data warehouses, real-time and active databases, and many others.
Database
A database is a collection of related [[Data|data]].
Take a sample Mini-world: part of a university environment.
Some mini-world entities include:
STUDENTs
COURSEs
SECTIONs (of COURSEs)
academic DEPARTMENTs
INSTRUCTORs
Some relationships:
SECTIONs are of specific COURSEs
STUDENTs take SECTIONs
COURSEs have prerequisite COURSEs
INSTRUCTORs teach SECTIONs
COURSEs are offered by DEPARTMENTs
STUDENTs register in DEPARTMENTs
Characteristics of the Database Approach
Self-describing nature of a database system:
A DBMS catalog stores the metadata (description) of a particular database (e.g. data structures, types, and constraints).
This allows the DBMS software to work with different database applications.
Program-data independence
Program-data independence is the insulation between programs and data, it allows us to change data structures and storage organisation without having to change the DBMS access programs.
A **data model** offers data abstraction and is used to hide storage details and present the users with a conceptual view of the database. Programs refer to the data model constructs rather than data storage details.
The database approach supports multiple views of the data, each user may see a different view of the database, which describes only the data of interest to that user.
Sharing of data and multi-user transaction processing:
Allowing a set of concurrent users to retrieve from and to update the database.
Concurrency control within the DBMS ensures that each transaction is correctly executed or aborted.
Recovery subsystem ensures each completed transaction has its effect permanently recorded in the database.
Online Transaction Processing
OLTP (online transaction processing) is a major part of database applications, allowing hundreds of concurrent transactions to execute per second.
Controlling redundancy in data storage and in development and maintenance efforts.
Providing multiple interfaces to different classes of users, facilitate sharing data across users.
Restricting unauthorised access to data.
Providing storage structures (indexes) for efficient query processing.
Providing backup and recovery services.
Representing complex relationships across data.
Enforcing integrity constraints on the database.
Drawing inferences and actions from the stored data.
Implications of using databases
Flexibility to change data structures: db structure may evolve as new requirements are defined
Availability of current information: extremely important for online transaction systems such as airline, hotel and car reservations.
Economies of scale: wasteful overlap of resources and personnel can be avoided by consolidating data and applications across departments.
When not to use a DBMS
Main inhibitors of using a DBMS:
High investment required for additional hardware, providing generality, security, concurrency control, recovery, and integrity functions.
When a DBMS may be unnecessary:
If the database and applications are simple and well-defined, and not expected to change or if access to data by multiple users is not required.
When no DBMS may suffice:
If the database system is not able to handle the complexity of data because of modelling limitations.
If there are stringent real-time requirements that may not be met because of the DBMS overhead.
If the database users need special operations not supported by the DBMS.
2. System Concepts and Architecture
Data Model
A data model is a set of concepts to describe the structure of a database, this includes the data types, relationships, and certain constraints that apply to the data. May include basic operations for retrievals and updates.
Constructs are used to define the database structure, constructs typical include elements and their data types, as well as groups of elements (e.g. entity, record, table) and relationships among such groups.
Constraints specify some restrictions on valid data; these must be enforced at all times.
Data model operations are used for specifying database retrievals and updates by referring to the constructs of the data model. Operations on the data model may include: basic model operations (insert, delete, update) or user-defined operations (compute_value, update_inventory).
Categories of Data Models
Conceptual Data Models
Conceptual (high-level, semantic) data models:
Provide concepts that are close to the way many users perceive data.
Also called entity-based or object-based data models.
Provide concepts that describe details of how data is stored in the computer. These are usually specified in an ad-hoc manner through DMBS design and administration manuals.
Provide concepts that fall between the above two, used by many commercial DBMS implementations. (e.g. relational data models used in many commercial systems)
A network model is able to model complex relationships, can handle most situations for modelling using record types and relationship types. Language is navigational and uses constructs like FIND, FIND member, FIND owner, FIND NEXT within set, GET, etc.
Disadvantages include the navigational and procedural nature of processing, the database contains a complex array of pointers that thread through a set of records. There is little scope of automated query optimisation.
The relational model is the dominant model for developing database applications.
It was proposed in 1970 by E.F. Codd (IBM) with the first commercial system appearing in 1981-82. It appears in several commercial and open source implementations.
The Database Schema (also called an intension) changes infrequently while the Database State (also called an extension) changes every time the database is updated.
Database Schema
Database Schema
The database schema is the description of a database, includes descriptions of the database structure, data types, and the constraints on the database.
The actual data stored in a database at a particular moment is the database state, this includes the collection of all data in the database at a moment in time.
Initial Database State
The Initial Database State refers to the database state when it is initially loaded in to the system.
The three-schema architecture defines DBMS schema at three levels:
Internal schema at the internal level to describe physical storage structures and access paths (e.g. indexes). Typically uses a physical data model.
Conceptual schema at the conceptual level to describe the structure and constraints for the whole database for a community of users. Uses a conceptual or implementation data model.
External schema at the external level to describe the various user views. Usually uses the same data model as the conceptual schema.
The three-schema architecture is proposed to support DBMS characteristics of Program-data independence and support of multiple views of data. It’s not explicitly used in DBMS products but it is useful in explaining the organisation.
Mappings among schema levels are needed to transform requests and data, programs refer to an external schema and are mapped by the DBMS to the internal schema for execution. Data extracted from the internal DBMS is reformatted to match the user’s external view (e.g. formatting results of a SQL query for display in a web page).
Database Implementation
DBMS Languages
Data Definition Language
A Data Definition Language specifies conceptual and internal schemas, it is used by the database admins and designers to specify the conceptual schema of a database. In some DBMSs, separate storage definition language (SDL) and view definition language (VDL) are used to define internal and external schemas.
A Data Manipulation Language is used to query and manipulate the database.
High-level or non-procedural languages: these include the relational language SQL, which may be used in a standalone way or embedded in a programming language.
Low-level or procedural languages: these must be embedded in a general-purpose programming language.
A centralised DBMS combines everything into a single system including DBMS software, hardware, application programs, and user interface processing software. The user can still connect through a remote terminal, however, all processing is done at a centralised site.
A three tier client-server architecture is common for web applications, it consists of the client, the application / web server and the database server.
The intermediate layer (application / web server) stores the web connectivity software and the business logic part of the application used to access the corresponding data from the database server. Acts like a conduit for sending partially processed data between the database server and the client.
This can enhance security, as the database server is only accessible via the middle tier and clients cannot directly access it.