What does a DBMS contain?
Information about a particular enterprise: a collection of interrelated data, a set of programs to access the data, and an environment that is both convenient and efficient to use.
Data redundancy and inconsistency in file systems
Multiple file formats, duplication of information in different files.
Difficulty in accessing data (file systems)
Need to write a new program to carry out each new task.
Integrity problems (file systems)
Integrity constraints (e.g., account balance > 0) become "buried" in program code rather than being stated explicitly; hard to add new constraints or change existing ones.
Atomicity of updates: the problem and the example
Failures may leave the database in an inconsistent state with partial updates carried out. Example: a transfer of funds from one account to another should either complete or not happen at all.
Concurrent access problem: the example
Two people reading a balance (say 100) and updating it by withdrawing money (say 50 each) at the same time — uncontrolled concurrent accesses can lead to inconsistencies.
Security problems (file systems)
Hard to provide user access to some, but not all, data.
Physical level of abstraction
The lowest level of abstraction: describes how a record (e.g., instructor) is stored.
Logical level of abstraction
Describes what data is stored in the database, and the relationships among the data.
View level of abstraction
Application programs hide details of data types; views can also hide information (such as an employee's salary) for security purposes.
Database schema vs instance
Schema: the overall design of the database (analogous to the type of a variable). Instance: the actual content of the database at a particular point in time (analogous to the value of a variable).
Physical data independence
The ability to modify the physical schema without changing the logical schema (applications depend on the logical schema).
What is a data model?
A collection of tools for describing data, data relationships, data semantics and data constraints.
List the data models.
Relational; Entity-Relationship (mainly for database design); object-based (object-oriented and object-relational); semi-structured (XML); older: network and hierarchical.
What is DDL?
Data Definition Language: a specification notation for defining the database schema.
What does the data dictionary contain?
Metadata (data about data): the database schema; integrity constraints (e.g., primary key); authorization (who can access what).
What is DML, and what else is it called?
Data Manipulation Language: a language for accessing and manipulating the data organized by the appropriate data model; also known as query language.
Pure vs commercial query languages
Pure: used for proving properties about computational power and for optimization (relational algebra, tuple and domain relational calculus). Commercial: used in commercial systems — SQL is the most widely used.
How do application programs access databases?
Through language extensions to allow embedded SQL, or an application program interface (e.g., ODBC/JDBC) which allows SQL queries to be sent to a database.
Logical design vs physical design
Logical design: deciding on the database schema (a "good" collection of relation schemas). Physical design: deciding on the physical layout of the database.
The two approaches to ensuring each relation is "good"
The Entity Relationship Model (an enterprise as entities and relationships, drawn as an E-R diagram) and Normalization Theory (formalize what designs are bad, and test for them).
What do object-relational data models add to the relational model?
Object orientation and constructs for added data types; attributes with complex types, including non-atomic values such as nested relations — while preserving relational foundations and upward compatibility.
What was XML originally intended for, and what is it now used for?
Originally a document markup language, not a database language; new and nested tags made it a great way to exchange data — the basis for all new generation data interchange formats.
What is the storage manager, and what are its tasks?
A program module that provides the interface between the low-level stored data and the application programs and queries. Tasks: interaction with the OS file manager; efficient storing, retrieving and updating of data.
The three steps of query processing
1. Parsing and translation; 2. Optimization; 3. Evaluation.
What is a transaction?
A collection of operations that performs a single logical function in a database application.
Transaction-management component vs concurrency-control manager
Transaction management: keeps the database consistent despite system failures and transaction failures. Concurrency-control manager: controls the interaction among concurrent transactions to ensure consistency.
Which database users use query tools, and which use application interfaces?
Sophisticated users (analysts) use query tools; naive users (tellers, agents, web users) use application interfaces.
The four database architectures
Centralized; client-server; parallel (multi-processor); distributed.
Two-tier vs three-tier architecture
Two-tier: the client application talks over the network directly to the database system. Three-tier: the application client talks to an application server, which uses the database system.
Who defined the relational data model?
Ted Codd (late 1960s and 1970s); he won the ACM Turing Award for this work.