Key Concepts in DBMS

Managing data efficiently with a DBMS requires a small vocabulary of core ideas. Each term below answers a different question about a database: what is its design (schema), what data is allowed in (constraints), where the design itself is stored (meta-data), what the data looks like at a moment in time (instance), how we ask for data (query), how we change data (data manipulation), and what actually does the work underneath (database engine).

(A) Database Schema

The database schema is the design of a database — its skeleton. It represents:

• the structure: table names and their fields (columns),
• the type of data each column can hold,
• any constraints on the data to be stored, and
• the relationships among the tables.

Because the schema tells us how the data are organised in the database, it is also called the visual or logical architecture of the database.

Figure 7.3 pictures the StudentAttendance database environment built on this idea: multiple users (the teacher, the office staff) send queries; the DBMS software processes each query, accesses the database and its definition (the database catalog), and returns the query result. The three tables — Student, Guardian and Attendance — sit inside one database that everyone shares.

(B) Data Constraint

Sometimes we deliberately put restrictions or limitations on the type of data that can be inserted into one or more columns of a table. These restrictions, specified while creating the table, are called constraints.

Examples:

• A mobile-number column can be constrained to accept only non-negative integer values of exactly 10 digits.
• Since every student must have one unique roll number, the RollNumber column can carry the NOT NULL constraint (a value must be present) and the UNIQUE constraint (no two rows may share a value).

Constraints exist to ensure the accuracy and reliability of the data in the database — bad values are rejected at the door instead of being discovered later.

(C) Meta-data or Data Dictionary

The database schema, along with the various constraints on the data, is stored by the DBMS in a database catalog (also called the data dictionary). This stored description is the meta-data — literally, data about the data. When the DBMS processes a query, it consults this catalog to know what tables exist, what columns they have, and what rules apply.

(D) Database Instance

When the database structure (schema) is first defined, the database is empty — there is no data in it yet. After data is loaded, the state or snapshot of the database at any given time is called the database instance.

From then on, data can be retrieved through queries or changed through updation, modification or deletion — so the state of the database keeps changing. This gives an important distinction:

(E) Query

A query is a request made to the database for obtaining information in a desired way. A query may pull data from a single table or from a combination of tables. For example, "find the names of all those students present on Attendance Date 2000-01-02" is a query to the StudentAttendance database — answering it needs both the ATTENDANCE table (who was present) and the STUDENT table (their names).

To retrieve or manipulate data, the user writes queries in a query language; this language is taken up in detail in the next chapter (Chapter 8).

(F) Data Manipulation

Modification of a database consists of three operations — Insertion, Deletion and Update:

• Insertion — when Rivaan joins as a new student in the class, his details must be added to the STUDENT table, and his guardian's details to the GUARDIAN table, of the Student Attendance database.
• Deletion — when a student leaves the school, his or her data and the corresponding guardian details must be removed from STUDENT, GUARDIAN and ATTENDANCE respectively.
• Update — when Atharv's guardian changes his mobile number, the GPhone value in the GUARDIAN table must be updated.

(G) Database Engine …