Ch 1. Overview of Database System (DBMS)
Role of end users of a database?
who wish to store and use data in a DBMS
When would you store data in a DBMS instead of in operating system files and vice-versa? (Section 1.3)
1) A database application must stage large datasets between main memory and secondary storage (e.g., buffering, page-oriented access, 32-bit addressing, etc.)- 2) DBMS has special code for different queries 3) DBMS must protect data from inconsistency due to multiple concurrent users 4) DBMS must support crash recovery 5) DBMS must have security and access control
What are the main benefits of using a DBMS to manage data in applications involving extensive data access? (Sections 1.1, 1.4) [7 items]
1) Data independence 2) Efficient data access (DBMS can retrieve faster than other prog methods) 3) Reduced application and development time 4) Data Integrity and Security (Data protected) 5) Uniform data administration 6) Concurrent access 7) Recovery from crashes
Role of database administrators of a database?
1) Designs logical / physical schemas 2) Handles security and authorization 3) Data availability, crash recovery 4) Database tuning as needs evolve. Must understand how a DBMS work!
Identify the main components in a DBMS and briefly explain what they do. (Section 1.8)
1) Query Optimization and Execution >> When a user issues a query, the parsed query is presented to a query optimizer >> An execution plan is a blueprint for evaluating a query, usually represented as a tree of relational operators (with annotations that contain additional detailed info about access methods to use, etc.) 2) Relational Operators Serve as the building blocks for evaluating queries posed against the data. 3) Files and Access Methods The code that implements relational operators sits on top of the file and access methods layer. This layer supports the concept of a file, which, in a DBMS, is a collection of pages or a collection of files. >> Heap files, or files of unordered pages, as well as indexes, are supported. >> In addition to keeping track of the pages in a file, this layer organizes the information within a page. 4) Buffer Management brings pages in from disk to main memory as needed in response to read requests 5) Disk Space Management deals with management of space on disk, where data is stored. Higher layers allocate, deallocate, read, and write pages through (routines provided by) this layer, called the disk space manager.
What is logical data independence and why is it important?
>> Logical data independence: Protection from changes in the logical structure of data. --> conceptual schema hides details such as how the data is actually laid out on disk, the file structure, and the choice of indexes,
If we assume that the DBA is never interested in running his or her own queries, does the DBA still need to understand query optimization? Why?
A DBA needs to understand query optimization even if s/he is not interested in running his or her own queries because some of these responsibilities (database design and tuning) are related to query optimization. Unless the DBA understands the performance needs of widely used queries, and how the DBMS will optimize and execute these queries, good design and tuning decisions cannot be made.
Explain the advantages of using a query language instead of custom programs to process data. (Section 1.6)
A DBMS takes great care to evaluate queries as efficiently as possible. Indexes can be used to speed up many queries----in fact, a good choice of indexes for the underlying relations can speed up each query in the preceding list. A DBMS enables users to create, modify, and query data through a data manipulation language (DML).
What is a data model?
A collection of concepts for describing data.
Why would you choose a database system instead of simply storing data in operating system files?
A database is an integrated collection of data, usually so large that it has to be stored on secondary storage devices such as disks or tapes. This data can be maintained as a collection of operating system files, or stored in a DBMS (database management system).
What is data independence and how does a DBMS support it? (Section 1.5)
Applications insulated from how data is structured and stored. Logical data independence: Protection from changes in logical structure of data. Physical data independence: Protection from changes in physical structure of data. Data independence is achieved through use of the three levels of data abstraction. The conceptual schema (aka logical schema) describes stored data in terms of a data model. Defines logical structure. The physical schema specifies additional storage details. The external schema, allows data access to be customized and authorized at the level of individual users or groups.
What is checkpointing and why is it used? (1.7)
Bringing the database to a consistent state after a system crash can be a slow process, since the DBMS must ensure that the effects of all transactions that completed prior to the crash are restored, and that the effects of incomplete transactions are undone. The time required to recover from a crash can be reduced by periodically forcing some information to disk; this periodic operation is called a checkpoint.
Who needs to know the most about database systems? (Section 1.9)
Database administrator (DBA)
What are locks in a DBMS, and why are they used? (Section 1.7)
Every object that is read or written by a transaction is first locked in shared or exclusive mode, respectively. Placing a lock on an object restricts its availability to other transactions and thereby affects performance. Suppose that the following locking protocol is followed: Every transaction begin by obtaining a shared lock on each data object that it needs to read and an exclusive lock on each data object that it needs to modify, then releases all its locks after completing all actions. Consider two transactions T1 and T2 such that T1 wants to modify a data object and T2 wants to read the same object. Intuitively, if T1's request for an exclusive lock on the object is granted first, T2 cannot proceed until T1 releases this lock, because T2's request for a shared lock will not be granted by the DBMS until then. Thus, all of T1's actions will be completed before any of T2's actions are initiated.
Explain the difference between external, internal, and conceptual schemas
External Schemas: allows data access to be customized (and authorized) at the level of individual users or group of users. Conceptual (logical) Schemas: >> describes all the data that is actually stored in the database. While there are several views for a given database, there is exactly one conceptual schema to all users. >> define logical stucture Internal (physical) Schemas: >> summarize how the relations described in the conceptual schema are actually stored on disk (or other physical media). >> describes the files and indexes used
How are these different schema layers related to the concepts of logical and physical data independence?
External schemas provide logical data independence, while conceptual schemas offer physical data independence.
When would it make sense not to use a database system?
If these advantages are not important for the application at hand, using a collection of files may be a better solution because of the increased cost and overhead of purchasing and maintaining a DBMS.
Explain the difference between logical and physical data independence?
Logical data independence: users are shielded from changes in the logical structure of the data. physical data independence: insulates users from changes in the physical storage of the data. We saw an example of logical data independence in the answer to Exercise 1.2. Consider the Students relation from that example (and now assume that it is not replaced by the two smaller relations). We could choose to store Students tuples in a heap file, with a clustered index on the sname field. Alternatively, we could choose to store it with an index on the gpa field, or to create indexes on both fields, or to store it as a file sorted by gpa. These storage alternatives are not visible to users, except in terms of improved performance, since they simply see a relation as a set of tuples. This is what is meant by physical data independence.
What is the relational data model?
Main concept: relation is a table with rows and columns)(Every relation has a schema which defines columns or fields)
What is a transaction? What guarantees does a DBMS offer with respect to transactions? (Section 1.7)
The ACID Test Atomic - Atomicity requires that each transaction be "all or nothing" Consistency - The consistency property ensures that any transaction will bring the database from one valid state to another Isolation - The isolation property ensures that the concurrent execution of transactions results in a system state that would be obtained if transactions were executed serially Durability - Durability means that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors
What are the responsibilities of a DBA?
The DBA is responsible for: 1) Designing the logical and physical schemas, as well as widely-used portions of the external schema. 2) Security and authorization. 3) Data availability and recovery from failures. 4) Database tuning: The DBA is responsible for evolving the database, in particular the conceptual and physical schemas, to ensure adequate performance as user requirements change.
What is write-ahead logging (WAL), and why is it used?
The Write-Ahead-Log is a history of all actions carried out by the DBMS while executing a set of Xacts. Before a change is made to the database, the corresponding log entry is forced to a safe location. After a crash, the effects of partially executed transactions are undone using the log. (Thanks to WAL, if log entry wasn't saved before the crash, corresponding change was not applied to database!) The WAL property ensures atomicity.
Role of application programmers of a database?
develop packages that facilitate data access for end users, who are usually not computer professionals, using the host or data languages and software tools that DBMS vendors provide.