What does a DBMS contain?
Information about a particular enterprise: a collection of interrelated data; a set of programs to access the data; an environment that is both convenient and efficient to use.
On what were database applications built in the early days?
Directly on top of file systems.
File systems: data redundancy and inconsistency vs data isolation.
Redundancy and inconsistency: multiple file formats, duplication of information in different files. Isolation: data scattered in multiple files and formats.
Why is "difficulty in accessing data" a drawback of file systems?
You need to write a new program to carry out each new task.
What are integrity problems in file systems?
Integrity constraints (e.g., account balance > 0) become "buried" in program code rather than being stated explicitly; it is hard to add new constraints or change existing ones.
Atomicity of updates: what is the problem? Give an 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.
Why is concurrent access needed, and what can go wrong?
It is needed for performance; uncontrolled concurrent accesses can lead to inconsistencies (e.g., two people reading a balance of 100 and each withdrawing 50 at the same time).
What is the security problem of file systems?
It is hard to provide user access to some, but not all, data.
Physical, logical and view levels of abstraction.
Physical: how a record is stored. Logical: data stored in the database and the relationships among the data. View: application programs hide details of data types; views can hide information (e.g., salary) for security.
What is a schema, and what is designing one called?
A structural design that defines how data is organized, stored, and related. The process of designing a schema is known as data modeling.
Physical schema vs logical schema.
Physical: how data is physically stored on disk or storage devices (file structures, indices, partitioning, compression). Logical: what data is stored and the overall logical structure — tables, attributes, primary keys, foreign keys, integrity constraints — without worrying about physical storage.
What is the view schema?
The highest level of abstraction: it defines how end-users interact with the database, providing a view of the database as per the user role of application.
What is an instance of a database?
The actual content of the database at a particular point in time — analogous to the value of a variable.
Define data independence.
The ability to modify the database schema at one level of a database system without affecting the schema at the next higher level.
Physical vs logical data independence, with an example of each.
Physical: modify the physical schema without changing the logical schema — e.g., switching from a Hard Disk to an SSD. Logical: modify the conceptual schema without modifying the view/external schema — e.g., adding a new column (attribute) to a table.
What do data models describe?
Data, data relationships, data semantics, data constraints.
List the data models.
Relational; Entity-Relationship (mainly for database design); object-based (object-oriented and object-relational); semistructured (XML); older: network and hierarchical.
How do object-relational data models extend the relational model?
By including object orientation and constructs for added data types; attributes of tuples may have complex types, including non-atomic values such as nested relations; relational foundations (declarative access) are preserved, with upward compatibility with existing relational languages.
Who defined XML, and what was it originally intended for?
The WWW Consortium (W3C); originally intended as a document markup language, not a database language. It became the basis for new generation data interchange formats.
What is DDL, and what does the DDL compiler generate?
Data Definition Language: specification notation for defining the database schema. The DDL compiler generates a set of table templates stored in a data dictionary.
What does the data dictionary contain?
Metadata (data about data): database schema; integrity constraints (e.g., primary key); authorization (who can access what).
What is DML also known as, and what are its two classes of languages?
Query language. Pure (relational algebra, tuple relational calculus, domain relational calculus — for proving computational power and optimization) and commercial (SQL).
Why is SQL usually embedded in a higher-level language?
Because SQL is NOT a Turing machine equivalent language; to compute complex functions it is embedded in some higher-level language.
How do application programs generally 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; business and computer-science decisions). Physical design: deciding on the physical layout of the database.
What are the two approaches to database design?
Entity Relationship Model (an enterprise as a collection of entities and relationships, drawn as an E-R diagram) and Normalization Theory (formalize what designs are bad, and test for them).
Name the three parts of the database engine.
Storage manager; query processor; transaction manager.
What is the storage manager responsible for?
It is the interface between the low-level data stored in the database and the application programs and queries; responsible for interaction with the OS file manager and efficient storing, retrieving and updating of data.
Database engine: name the steps the query processor goes through.
1. Parsing and translation; 2. Optimization; 3. Evaluation.
On what does estimating the cost of query operations critically depend?
On statistical information about relations which the database must maintain (and estimated statistics for intermediate results).
A transaction is a collection of operations that does what?
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 (power failures, OS crashes) and transaction failures. Concurrency control controls the interaction among concurrent transactions to ensure consistency.
Which users use query tools, and which write application programs?
Sophisticated users (analysts) use query tools; application programmers write application programs. (Naive users use application interfaces; database administrators use administration tools.)
List the four database architectures.
Centralized; client-server; parallel (multi-processor); distributed.