A Database Management System (DBMS) is computer software that interacts with users, other applications, and the database itself to capture and analyze data. It provides users and programmers with a systematic way to create, retrieve, update and manage data.
1. What is a Database Management System?
A Database Management System (DBMS) is a tool to store, edit and organise data in a database. It provides several key features that make it essential for modern data management.
📖 Key Features of a DBMS
Stores data in one central location
Allows data to be shared by many users
Provides user interfaces to work with the data
Creates backups automatically
Controls who can access and edit the data
Separates applications from the data
1.1 Benefits of Using a DBMS
Benefit
Description
Integrity
The structure of the database can change, but the applications using the data do not need to be changed
Efficiency
Avoids data duplication and inconsistency; less storage space taken up because data is shared
Consistency
Data is the same, regardless of who is viewing it
Backups
It is easy to back up data from one location
Security
Data is in a secure central place; different access rights can be assigned to different people
Customisation
Applications can be customised to suit the needs of the user
💡 Exam Tip
Remember: A key benefit of DBMS is that it separates applications from data. When people use applications, they call on the data they need. They do not need to use all the data every time they use the database.
2. DBMS vs File-Based Systems
Before DBMS, organisations used file-based systems to store data. These systems had several limitations that DBMS was designed to address.
2.1 Problems with File-Based Systems
⚠️ Key Limitations of File-Based Approach
Data Redundancy: Same data stored in multiple files, wasting space and causing confusion
Data Inconsistency: Data may differ between files if one is updated but others are not
Data Isolation: Data scattered in various files in different formats, making retrieval difficult
Dependency on Applications: Changing files would lead to changes in application programs
Atomicity Issues: Difficult to ensure transactions either fully complete or don't happen at all
Poor Security: Hard to apply security constraints across different files
2.2 How DBMS Solves These Problems
Issue
How DBMS Solves It
Data Redundancy
Stores data in separate linked tables using foreign keys, reducing duplication
Data Inconsistency
Stores items only once, so updates are seen by all applications
Data Dependency
Data is independent of applications; structure changes managed by DBMS
Data Security
User accounts and access rights control who can access or change data
Data Management
Provides a data dictionary that defines all data elements
3. Data Redundancy and Inconsistency
3.1 Data Redundancy
📖 Definition: Data Redundancy
Data redundancy occurs when the same data point is multiplied across the database and can be found repeated in an unnecessary form. It refers to the duplication of data.
Example: A common example of data redundancy is when a name and address are both present in different columns within a table. If the link between these data points is defined in every single new database entry, it would lead to unnecessary duplication across the entire table.
📝 How DBMS Solves Data Redundancy
Stores data in separate linked tables
Items of data used to link tables by the use of foreign keys
DBMS will flag errors when any attempt is made to accidentally delete linked items
Data normalization removes redundancy
3.2 Data Inconsistency
📖 Definition: Data Inconsistency
Data inconsistency is a situation where there are multiple tables within a database that deal with the same data but may receive it from different inputs.
Example: If we have an address of someone in many tables and we change it in only one table but not in another, the problem of data inconsistency occurs. The same person could have different addresses in different files!
📝 How DBMS Solves Data Inconsistency
Stores most items of data only once
Updated items are seen by all applications
As data is not inconsistent, integrity of data stored is improved
Consistent data is easier to maintain - item changed once, not multiple times
4. Data Dictionary and Logical Schema
4.1 Data Dictionary
📖 Definition: Data Dictionary
A data dictionary is a central repository that stores metadata (data about data) for a database. It contains information about the structure of the database.
📝 Contents of a Data Dictionary
Definition of tables and their names
Attribute/field names for each table
Data types for each field (integer, text, date, etc.)
Relationships between tables
Primary keys and foreign keys
Indexing information
Validation rules for data entry
Information about physical storage of data
🌟 Purpose of Data Dictionary
Use of a data dictionary improves integrity of data stored, helping to ensure that it is accurate, complete and consistent. It acts as a blueprint for the entire database structure.
4.2 Logical Schema
📖 Definition: Logical Schema
A logical schema is a data model for a specific database that is independent of the DBMS used to build the database. It describes how relationships will be implemented in the logical structure of the database.
📝 Purpose of Logical Schema
Shows the overall logical design of the database
Includes tables, fields, relationships, and constraints
Shows how data is organised independently of how it is physically stored
Data modelling tools (e.g., E-R diagrams) are used to create logical schemas
💡 Exam Tip
The logical schema is the conceptual view of the database structure. It shows what data is stored and how tables relate to each other, but NOT how the data is physically stored on disk.
5. Data Integrity
5.1 What is Data Integrity?
📖 Definition: Data Integrity
Data integrity refers to the accuracy, completeness, and consistency of data throughout its lifecycle. It ensures that data remains reliable and trustworthy.
⚠️ Types of Data Integrity
Entity Integrity: Each row in a table must be unique (primary keys)
Referential Integrity: Foreign keys must match existing primary keys or be NULL
Domain Integrity: All data values must be valid for their defined domain/data type
5.2 How DBMS Ensures Data Integrity
📝 Methods to Maintain Data Integrity
Validation rules: Check data before it is entered (e.g., range checks, format checks)
Referential integrity: Prevents orphaned records and ensures relationships are valid
Input masks: Ensure data is entered in correct format
Setting data types: Ensures only appropriate data can be stored
Removing redundant data: Reduces chances of inconsistency
Backup data: Allows recovery from corruption
Access controls: Prevents unauthorized changes
Audit trail: Records all changes made to data
💡 Exam Tip: Ensuring Data Integrity
When asked how to ensure data integrity, mention: Validation, Referential Integrity, Input Masks, Data Types, Backup, Access Controls, and Audit Trail.
6. Data Security
6.1 Importance of Data Security
📖 Definition: Data Security
Data security in a DBMS refers to protecting the database from unwanted alteration, corruption, deletion, or unauthorized sharing of data with those who have no right to access it.
📝 How DBMS Ensures Data Security
Usernames and passwords: Prevent unauthorized access to the database
Access rights: Manage actions authorized users can take (read/write/delete/read-only/append-only)
Access rights: Manage which parts of the database users can access
Automatic backups: Regular scheduled backups to prevent data loss
Encryption: Encrypt data stored in the database
Audit trail: Automatic recording of actions taken by users
6.2 Access Levels
One of the challenges of creating a DBMS is managing who can access and change the data. If anyone can edit the database, data could easily become corrupted. It is better to have different levels of access for different users.
User Type
Access Level
Example Permissions
Customer
Limited
View own data only, update own profile
Staff Member
Moderate
View relevant records, add new entries
Account Manager
Elevated
View financial details, generate reports
Administrator
Full
Upload/amend entire database, manage users
Example: With an online music library, customers should only see their own music, administrators would have access to upload and amend the entire library, and account managers would be able to see the financial details for the customers when required.
7. Database Views
One of the benefits of using a DBMS is that it allows different views of a database. This is useful as different users have different requirements.
7.1 Three Levels of Database Views
View Level
Description
Used By
External View
The view of data used by customers or people who input and view data; simple forms and interfaces
End users, customers
Conceptual View
Shows how tables are connected; used to plan changes; can be presented using SQL
Database designers
Internal View
Shows how the computer sees the database; includes file structure, storage, hex/binary references
Database programmers
Example: In a bird watching app, the external view would show a simple form with bird type, time of year, date, and location. The user does not need to see the whole database tables. The conceptual view would show how tables are connected. The internal view would show how data is physically stored.
💡 Exam Tip
Remember the External-Conceptual-Internal hierarchy: External = What users see, Conceptual = What designers plan, Internal = How computers store. A customer does not need to see as much information as the administrator or programmer.
8. Concurrency and ACID
8.1 Concurrency
📖 Definition: Concurrency
Concurrency is when many users are interacting with a system or many changes are being made at once. A DBMS needs to use concurrency, but must prevent two people using the data in a conflicting way.
Example: Two people visiting an online shop might try to buy the final product in store at the same time. The database would need to make sure only one person could buy it.
8.2 ACID Rules
A change in a database is called a transaction. Changes to databases must conform to ACID rules:
📖 ACID Rules Explained
Atomicity: The transaction must be completed fully. If it is not completed fully, it will not be recorded. "All or nothing" principle.
Consistency: Any change must not break the database. It must be consistent with how it was before the change. Database rules and constraints must be maintained.
Isolation: A transaction must be isolated and not interfere with another transaction. Each transaction should execute as if it were the only one.
Durability: A transaction must remain in the database. Once committed, changes are permanent even if there is a system failure.
🧠 Memory Trick: ACID
Think of ACID as the four pillars of reliable transactions:
Atomicity = All or nothing
Consistency = Correct state maintained
Isolation = Independent transactions
Durability = Data persists forever
9. DBMS Software Tools - Developer Interface
9.1 What is the Developer Interface?
📖 Definition: Developer Interface
The developer interface is a tool within DBMS that allows developers to create and manage database objects, write queries, and build user interfaces for the database.
📝 Uses of Developer Interface
To create user-friendly features e.g., forms to enter new bookings
To create outputs e.g., reports of bookings on a given date
To create interactive features such as buttons and menus
To write queries in Structured Query Language (SQL)
To create tables, forms and reports
💡 Exam Tip: Developer Interface vs Query-by-Example
The developer interface allows developers to write queries using SQL rather than using query-by-example (QBE) tools. Using SQL allows the developer to create more complex and precise queries to search, update, or manage data.
10. DBMS Software Tools - Query Processor
10.1 What is the Query Processor?
📖 Definition: Query Processor
The query processor is responsible for handling and executing SQL queries. It allows the construction of more complex queries to interrogate the database.
📝 Purpose of Query Processor
Enables users to input criteria for retrieving specific data
Conducts searches within the database based on provided criteria
Structures and organizes retrieved data for presentation
Takes a query written in SQL and processes it
10.2 Components of Query Processor
Component
Function
DDL Interpreter
Interprets Data Definition Language commands (CREATE, ALTER, DROP) and updates the data dictionary
DML Compiler
Compiles Data Manipulation Language statements (SELECT, INSERT, UPDATE) into low-level instructions and optimises the query
Query Evaluation Engine
Executes the compiled instructions to retrieve or manipulate the actual data in the database
⚠️ Key Points to Remember
DDL statements are interpreted and recorded in the data dictionary
DML statements are compiled into low-level instructions
The DML compiler also optimises the query for better performance
The query evaluation engine executes the compiled instructions
11. Exam-Style Questions
1. State what is meant by a data dictionary and give one example of an item typically found in a data dictionary. [2 marks]
Answer:
A data dictionary is data about the data structure in the database [1 mark]
Example items: field name, data type, table name, primary key, foreign key, validation rules, relationships, indexing information [1 mark]
Additional points for deeper understanding: The data dictionary stores metadata, acts as a central repository for all database structure information, helps ensure data integrity by maintaining consistency in definitions.
2. Describe the problems associated with file-based systems that a DBMS addresses. [6 marks]
Answer:
Data Redundancy: Same data stored in multiple files, wasting storage space and causing confusion
Data Inconsistency: Data may differ between files if one is updated but others are not
Data Isolation: Data scattered in various files in different formats, making retrieval difficult
Application Dependency: Changing files requires changes in application programs
Poor Security: Difficult to apply security constraints across different files
Atomicity Issues: Difficult to ensure transactions either fully complete or don't happen at all
Additional points: DBMS solves these by centralizing data, providing data independence, implementing access controls, and supporting transaction management with ACID properties.
3. Explain the purpose of the query processor in a DBMS. [4 marks]
Answer:
Enables users to input criteria or conditions for retrieving specific data from the database
Conducts searches within the database based on the provided criteria to identify and retrieve relevant data
Structures and organizes the retrieved data into a format suitable for presentation to the user
Takes a query written in SQL and processes it through DDL interpreter, DML compiler, and query evaluation engine
Additional points: The query processor optimizes queries for better performance, manages different types of SQL statements (DDL and DML), and ensures efficient data retrieval.
4. The database manager is concerned about data integrity. State what is meant by data integrity and give two examples of how the manager can ensure data integrity in the database. [4 marks]
Answer:
Data integrity means ensuring data is consistent, accurate, complete and reliable throughout its lifecycle. [2 marks for definition]
Examples of ensuring data integrity [2 marks for two examples]:
Validation - Check data before entry (range checks, format checks)
Referential integrity - Ensure foreign keys match existing primary keys
Input masks - Ensure correct data format
Setting data types - Ensure appropriate data storage
Removing redundant data - Reduce inconsistency
Backup data - Allow recovery from corruption
Access controls - Prevent unauthorized changes
Audit trail - Record all changes made
5. Describe how a DBMS can help to make data secure. [5 marks]
Answer:
Using usernames and passwords to prevent unauthorized access to the database
Using access rights to manage actions authorized users can take (read/write/delete, read only, append only)
Using access rights to manage which parts of the database users can access
Automatic creation and scheduling of regular backups
Encryption of the data stored in the database
Automatic creation of an audit trail or activity log to record actions taken by users
Additional points: Security also includes physical security measures, network security, and regular security audits. Different users can be assigned different roles with specific permissions.
11. Exam-Style Questions (Continued)
6. Describe the three levels of database views and explain who would use each level. [6 marks]
Answer:
External View: The view used by customers or people who input and view data. Shows simple forms and interfaces. Used by end users. [2 marks]
Conceptual View: Shows how tables are connected. Used to plan changes. Can be presented using SQL. Used by database designers. [2 marks]
Internal View: Shows how the computer sees the database. Includes file structure, storage, hex/binary references. Used by database programmers. [2 marks]
Additional points: The three-level architecture provides data independence - changes at one level don't necessarily affect other levels. This separation allows for easier maintenance and modification.
7. Explain what is meant by the ACID rules in database transactions. [4 marks]
Answer:
Atomicity: The transaction must be completed fully. If it is not completed fully, it will not be recorded. "All or nothing" principle. [1 mark]
Consistency: Any change must not break the database. It must be consistent with how it was before the change. [1 mark]
Isolation: A transaction must be isolated and not interfere with another transaction. [1 mark]
Durability: A transaction must remain in the database permanently once committed. [1 mark]
Additional points: ACID properties ensure reliable processing of database transactions. Without these properties, databases could be left in inconsistent states, leading to data corruption or loss.
8. Describe the benefits of using a DBMS over a file-based approach. [5 marks]
Answer:
Integrity: Structure can change without affecting applications using the data
Efficiency: Avoids data duplication and inconsistency; less storage space needed
Consistency: Data is the same regardless of who is viewing it
Backups: Easy to back up data from one central location
Security: Data in secure central place with different access rights for different users
Customisation: Applications can be customised to suit user needs
Additional points: DBMS also provides query capabilities, concurrent access control, data independence, and standardization of data management practices.
9. Explain the difference between data redundancy and data inconsistency. [4 marks]
Answer:
Data Redundancy: Occurs when the same data is stored in multiple places unnecessarily. It refers to the duplication of data, wasting storage space. [2 marks]
Data Inconsistency: Occurs when the same data has different values in different places. This happens when data is updated in one location but not in others where it is duplicated. [2 marks]
Additional points: Redundancy causes inconsistency - if the same data is stored in multiple places and one copy is updated, the other copies become inconsistent. DBMS solves both problems by storing data once and using relationships (foreign keys) to link related data.
10. Describe the purpose of the developer interface in a DBMS. [4 marks]
Answer:
To create user-friendly features such as forms for data entry
To create outputs such as reports showing specific data
To create interactive features like buttons and menus for user interaction
To allow developers to write queries in SQL rather than using query-by-example
Additional points: The developer interface provides more flexibility than visual query tools, allowing complex and precise queries. It also enables automation through scripts and macros, and supports the creation of complete database applications.
12. Glossary
ACID: Four properties (Atomicity, Consistency, Isolation, Durability) that ensure reliable database transactions.
Atomicity: Property ensuring a transaction is completed fully or not at all.
Concurrency: The ability to handle multiple users interacting with a database simultaneously.
Conceptual View: The logical view showing how database tables are connected.
Data Dictionary: Central repository storing metadata about the database structure.
Data Integrity: The accuracy, completeness, and consistency of data.
Data Inconsistency: When the same data has different values in different locations.
Data Redundancy: Unnecessary duplication of data across a database.
Data Security: Protecting data from unauthorized access, alteration, or deletion.
DBMS: Database Management System - software to create, manage, and interact with databases.