A-Level Computer Science / Unit 8: Relational Databases and SQL

8.2.2 Data Dictionaries and DBMS Tools

🔒 Lesson slides are available to signed-in users. Sign in

8.2.2 Data Dictionaries and DBMS Tools

A DBMS needs an accurate description of its own database before it can create tables, check queries or enforce structural rules. It stores this description as metadata in a data dictionary and provides software tools that let developers define structures and users submit queries.

By the end of this section, you should be able to:

  • Distinguish data from metadata.
  • Explain the purpose of a data dictionary.
  • Identify the kinds of structural information stored in a data dictionary.
  • Explain how a DBMS keeps dictionary entries consistent with the database schema.
  • Describe how a developer interface is used to create and modify database structures.
  • Describe how a query processor checks and executes a database request.
  • Explain how the data dictionary, developer interface and query processor work together.
Scope note: Security, access rights, integrity and backup procedures were covered in 8.2.1. Detailed SQL syntax is introduced in 8.3 Creating and Querying Databases with SQL.

Data and Metadata

The values stored in a table are data. The descriptions that tell the DBMS what those values mean and how they must be stored are metadata.

This lesson uses an original database for a school makerspace that lends equipment to students and staff.

Example Data or metadata? Reason
EQ-417 Data A value stored for one equipment item.
EquipmentID Metadata The name of an attribute.
VARCHAR(8) Metadata Describes the permitted type and maximum size of the attribute.
Thermal camera Data A stored equipment name.
PRIMARY KEY Metadata Describes the role and constraint applied to an attribute.
TRUE in the Available field Data A current value for one tuple.
Metadata: data that describe the structure, meaning or rules of other data.
Common mistake: Metadata are not merely headings shown on screen. They also include data types, sizes, keys, relationships, constraints and other definitions used internally by the DBMS.

The Data Dictionary

A data dictionary is the DBMS-managed collection of metadata describing the database. It provides one authoritative source for structural definitions.

Data dictionary: a repository of metadata maintained by the DBMS, containing definitions of database objects and the rules associated with them.

The dictionary may describe tables, attributes, views, keys, relationships, constraints and indexes. Some systems also record ownership, permissions and physical storage details. Ordinary users usually work through database tools rather than editing the dictionary directly.

Because the DBMS maintains the dictionary centrally, every application can use the same table and attribute definitions. This reduces the risk of different programs interpreting the same data in different ways.

What Can the Data Dictionary Store?

The exact entries vary between DBMS products, but the following categories are typical and appropriate at this level.

Metadata category Example entry How the DBMS can use it
Table name Equipment Checks that a referenced table exists.
Attribute name ReplacementCost Checks that a query uses a valid field.
Data type REAL Rejects incompatible values and operations.
Length or size EquipmentName: VARCHAR(60) Controls the maximum stored character length.
Null or presence rule EquipmentName: NOT NULL Prevents required information from being omitted.
Default value Available: TRUE Supplies a value when none is provided.
Key role EquipmentID: PRIMARY KEY Enforces unique identification.
Relationship Loan.EquipmentID → Equipment.EquipmentID Supports referential-integrity checks.
Validation or domain constraint ReplacementCost ≥ 0 Rejects values outside the permitted domain.
Index definition Index on Category Helps the query processor identify a possible access route.

Example dictionary entries

Table Attribute Type Size Required? Key / rule
Equipment EquipmentID VARCHAR 8 Yes Primary key
Equipment EquipmentName VARCHAR 60 Yes —
Equipment Category VARCHAR 24 Yes Indexed
Equipment ReplacementCost REAL — Yes Must be at least 0
Equipment Available BOOLEAN — Yes Default TRUE
Exam tip: When explaining a data dictionary, give both its content and its purpose. For example: “It stores attribute data types so the DBMS can reject values of an incompatible type.”

How the Dictionary Is Kept Current

The dictionary must match the actual schema. Therefore, the DBMS updates dictionary entries whenever an authorised structural change is made.

  1. A developer uses a visual designer or a definition command to request a schema change.
  2. The DBMS checks whether the change is valid and whether it conflicts with existing objects.
  3. The DBMS changes the database structure.
  4. The corresponding metadata in the data dictionary are updated.
  5. Later tools read the revised definitions when validating queries or presenting the schema.
Maintaining the data dictionary means keeping its metadata synchronised with the current database structure.
Common mistake: A data dictionary is not normally a separate document that a developer updates manually after changing the database. The DBMS should maintain its internal metadata as part of the structural change.

The Developer Interface

A developer interface gives a database developer access to tools for defining and modifying database structures. It may be graphical, text-based or a combination of both.

Developer interface: the DBMS tools through which a developer creates or modifies tables, attributes and other database objects.
Developer task Possible interface action Result
Create a table Enter a table name and add fields in a visual table designer. A new table structure is created.
Choose data types Select INTEGER, REAL, DATE, BOOLEAN or a character type. The DBMS knows how each value must be stored and checked.
Set sizes Specify a maximum length for a variable-length character attribute. Overlong values can be rejected.
Define keys Mark one or more fields as a primary key or foreign key. Uniqueness and references can be enforced.
Add constraints Set required fields, defaults or permitted ranges. Invalid structures or values can be prevented.
Define relationships Link a foreign key to a referenced primary key. The DBMS records and enforces the relationship.

Worked design decision

A developer adds an attribute named SafetyCheckedOn to the Equipment table:

NameSafetyCheckedOn
Data typeDATE
RequiredNo
DefaultNone
ReasonNew items may not yet have completed their first check

After the change is accepted, the data dictionary contains a new entry describing Equipment.SafetyCheckedOn.

Exam tip: “The developer interface creates the database” is too vague. State practical actions such as defining tables, attributes, types, keys and constraints.

The Query Processor

A query is a request to retrieve or change data. The query processor is the DBMS component that checks the request and carries out the necessary operations.

Query: a request for the DBMS to retrieve or modify data according to stated conditions.
Query processor: the DBMS software that interprets, checks and executes a query.

Imagine that a makerspace coordinator requests:

Show the names and replacement costs of all available sensor equipment, ordered from lowest to highest cost.
1

Receive

Accept the query from a user interface or application.

2

Check

Check syntax, referenced objects, data types and permissions.

3

Plan

Choose a suitable way to access the required tuples.

4

Execute

Read or modify the relevant stored data.

5

Return

Provide a result or a clear error message.

During the checking stage, the processor can consult the data dictionary. It can confirm that Equipment, EquipmentName, ReplacementCost, Category and Available exist and have suitable definitions.

Common mistake: A query processor is not the person writing the query. It is software within the DBMS.

How the Tools Work Together

The three syllabus concepts are closely connected:

1

Developer interface

A developer defines Equipment and its fields.

2

Data dictionary

The DBMS records the table, field types, keys and rules as metadata.

3

Query processor

A later query is checked against those definitions before execution.

Situation Tool or resource involved What happens
A developer creates EquipmentID as a primary key. Developer interface The developer specifies the field and key rule.
The DBMS records that EquipmentID is VARCHAR(8). Data dictionary The metadata become available to DBMS tools.
A query refers to an attribute called EquipName. Query processor + data dictionary The invalid field name is detected before execution.
A query filters ReplacementCost < 'cheap'. Query processor + data dictionary The processor can detect an incompatible comparison with a REAL field.
A table is renamed by an authorised developer. Developer interface + data dictionary The DBMS applies the structural change and updates the metadata.
Strong explanation: Use a complete chain: “The developer interface defines the structure; the DBMS records it in the data dictionary; the query processor later uses that metadata to validate and execute requests.”

Interactive: DBMS Tools Lab

Explore metadata, construct part of a table definition and run a query through a simplified query-processing pipeline.

Inspect an attribute definition

Table
Attribute
Type
Required
Key / rule
DBMS use
These are metadata about the attribute, not a tuple from the Equipment table.

Common Mistakes and Misconceptions

  • Confusing metadata with the ordinary tuple values stored in a table.
  • Describing the data dictionary as a dictionary of words or a list of user data.
  • Claiming that the data dictionary contains only attribute names.
  • Assuming developers should manually edit the DBMS's internal dictionary records.
  • Describing the developer interface only as a form for entering data.
  • Calling a query and the query processor the same thing.
  • Jumping directly to SQL syntax instead of explaining the purpose of the DBMS tool.
  • Forgetting that the query processor can use dictionary metadata to detect invalid references and types.
  • Repeating security and backup detail from 8.2.1 instead of focusing on the required tools.

Practice

Original practice tasks

  1. Explain the difference between data and metadata using one database example.
  2. Define data dictionary and state four kinds of metadata it can contain.
  3. Explain how the DBMS could use dictionary entries for data type and foreign key when checking an attempted insertion.
  4. A developer adds MaximumLoanDays INTEGER to Category. Describe how the developer interface and data dictionary are involved.
  5. State four practical tasks that can be completed through a developer interface.
  6. Describe the stages a query processor might follow after receiving a request.
  7. A query refers to Borrower.Fullname, but the dictionary records Borrower.FullName. Explain how the DBMS can respond.
  8. Explain how the developer interface, data dictionary and query processor form one connected workflow.

Review

Concept Purpose Typical example
Metadata Describe stored data and database rules. Attribute name, type, size or key role.
Data dictionary Provides a central DBMS-managed repository of metadata. Definition of Equipment.ReplacementCost.
Developer interface Allows developers to define and modify database objects. Create a table and choose its field types.
Query Requests retrieval or modification of data. Find all available sensors.
Query processor Checks, plans and executes the query. Reject an unknown field or return matching tuples.
Final exam tip: Keep the roles distinct: the developer interface defines, the data dictionary describes, and the query processor checks and executes.