Learning Objectives
By the end of this lesson, you will be able to:
- Show understanding of features provided by a Database Management System (DBMS) that address issues of a file based approach
- Explain how DBMS handles data management, including maintaining a data dictionary
- Understand data modelling concepts and the purpose of a logical schema
- Describe how DBMS ensures data integrity and data security
- Explain backup procedures and the use of access rights for individuals/groups of users
- Show understanding of how software tools within DBMS are used in practice
- Describe the use and purpose of Developer Interface and Query Processor
Key Terms
Database Management System (DBMS)
Computer software application that interacts with users, applications, and database to capture and analyze data
Data Redundancy
Unnecessary duplication of data in a database system
Data Inconsistency
Same data stored in multiple places with different values
Data Dependency
When applications depend on specific data structure or format
Data Dictionary
Stores metadata for a database including table definitions, attribute names, data types, relationships, and validation rules
Metadata
Data about data - describes the structure, constraints, and properties of data
Data Modelling Tool
Used to show data structure of database (e.g., E-R diagram)
Logical Schema
Data model for a specific database that is independent of DBMS used to build the database
Data Integrity
Accuracy, completeness, and consistency of data
Data Security
Protection of data from unauthorized alteration, corruption, deletion, or access
Developer Interface
Tool within DBMS to create user-friendly features like forms, reports, and interactive elements
Query Processor
Component that processes SQL queries, includes DDL interpreter, DML compiler, and query evaluation engine
Foreign Key
Field in one table that uniquely identifies a row in another table, used to link tables
Query Language
Structured language (like SQL) used to query data stored in database
Query-by-Example
Facility that allows users to query database by providing examples of desired results
Introduction to Database Management Systems
A Database Management System (DBMS) is a computer software application that interacts with users, other applications, and the database itself to capture and analyze data. DBMS provides users and programmers with a systematic way to create, retrieve, update and manage data.
DBMS Architecture Visualization
In a DBMS, different users and applications access the same central database, but each only sees the data they need. For example:
- A payroll officer needs access to staff salary details
- A customer service advisor needs access to customer accounts
- A sales manager needs access to stock levels
Real-Life Example: School Management System
Consider a school with different staff needing different information:
- Teachers need access to student marks and attendance
- Administration needs access to student personal details and fees
- Librarian needs access to book records and borrowing history
- Principal needs access to all data for reporting
A DBMS allows all this information to be stored in one central database while controlling who can access what data.
How DBMS Solves File-Based System Problems
1. Data Redundancy Issue
Problem: Same data stored multiple times in different files
DBMS Solution: Stores data in separate linked tables, reducing duplication. Uses foreign keys to link tables. DBMS flags errors when attempts are made to accidentally delete linked data.
2. Data Inconsistency Issue
Problem: Same data has different values in different files
DBMS Solution: Stores most items of data only once. Updated items are seen by all applications. Improves data integrity. Easier to maintain as data is changed only once.
3. Data Dependency Issue
Problem: Applications depend on specific file structures
DBMS Solution: Data is independent of applications. Changes to data structure are managed by DBMS and don't affect applications. Each application only accesses needed fields/tables.
Activity 1: File-Based vs DBMS Comparison
A college stores student data in separate files: "Student_Personal.txt", "Student_Marks.txt", and "Student_Fees.txt". Each file contains student name, ID, and other relevant data.
- What problem occurs if a student changes their name? How would this be solved in a DBMS?
- What happens if the "Student_Marks.txt" file structure changes (e.g., adds new columns)? How does DBMS handle this better?
- How would data redundancy be reduced using a DBMS approach?
- List three advantages of using a DBMS over separate files for this college system.
Solution:
- Name change problem: The name must be updated in all three files separately. In DBMS, student name would be stored once in a "Students" table, and all other tables would reference it using a student ID. Changing it once updates it everywhere.
- File structure change: Applications using "Student_Marks.txt" might break. In DBMS, applications only access data they need through queries. Adding new columns doesn't affect applications that don't use those columns.
- Reducing redundancy: DBMS stores data in normalized tables linked by foreign keys. Student personal details stored once, marks in another table linked by student ID, fees in another table linked by same ID.
-
Three advantages:
- Data consistency - changes made once appear everywhere
- Reduced data redundancy - no duplicate data storage
- Data independence - applications not affected by structure changes
- Better security - access controls for different users
- Data integrity - validation rules ensure accurate data
Check Your Understanding: Introduction to DBMS
1. What is a Database Management System (DBMS)? [2 marks]
Answer
- [1 mark] A computer software application that interacts with users, other applications, and the database itself
- [1 mark] Used to capture and analyze data, providing systematic way to create, retrieve, update and manage data
- [Additional] Acts as an interface between the database and users/applications
2. How does DBMS solve the data redundancy problem? [3 marks]
Answer
- [1 mark] Stores data in separate linked tables instead of duplicating data
- [1 mark] Uses foreign keys to link related tables together
- [1 mark] Flags errors when attempts are made to accidentally delete linked data items
- [Additional] Normalization process eliminates redundant data by organizing it efficiently
3. What is data dependency and how does DBMS address it? [3 marks]
Answer
- [1 mark] Data dependency occurs when applications depend on specific data structure or format
- [1 mark] DBMS makes data independent of applications using the database
- [1 mark] Changes to data structure are managed by DBMS and don't affect applications
- [Additional] Applications only access data they need through queries, not directly from files
4. Explain with an example how different users access the same database through DBMS. [3 marks]
Answer
- [1 mark] Different users access the same central database but see only relevant data
- [1 mark] Example: Payroll officer accesses staff details, customer service accesses customer accounts, sales manager accesses stock levels
- [1 mark] DBMS controls access through user accounts and permissions
- [Additional] All users work with the same underlying data but through different views or queries
5. What is the main advantage of storing data only once in a DBMS? [2 marks]
Answer
- [1 mark] Eliminates data inconsistency - updated items are immediately seen by all applications
- [1 mark] Improves data integrity and makes maintenance easier as data is changed only once
- [Additional] Saves storage space and ensures all users work with the same current data
DBMS Features and Components
DBMS uses a structured approach to the management, organisation and maintenance of data in a database. It includes several key components that work together to provide comprehensive data management.
Core DBMS Components
Data Management
DBMS manages all aspects of data handling:
- Entry of new data
- Storage of data
- Alteration of existing data
- Deletion of data
All data operations are controlled and coordinated by the DBMS, ensuring consistency and integrity.
Data Dictionary
The data dictionary stores metadata (data about data) for a database:
Data Dictionary Contents:
- Definition of tables and their structure
- Attribute/field names and data types
- Relationships between tables
- Indexing information
- Validation rules for data entry
- Information about physical storage of data
Data Dictionary Example: School Database
Below is a simplified example of what a data dictionary might contain for a school database:
| Table Name | Field Name | Data Type | Constraints | Description |
|---|---|---|---|---|
| Students | StudentID | INTEGER | PRIMARY KEY, NOT NULL | Unique identifier for each student |
| Students | FirstName | VARCHAR(50) | NOT NULL | Student's first name |
| Students | LastName | VARCHAR(50) | NOT NULL | Student's last name |
| Students | DateOfBirth | DATE | NOT NULL | Student's date of birth |
| Courses | CourseID | VARCHAR(10) | PRIMARY KEY | Unique course code |
| Courses | CourseName | VARCHAR(100) | NOT NULL | Full course name |
| Enrollments | EnrollmentID | INTEGER | PRIMARY KEY | Unique enrollment record |
| Enrollments | StudentID | INTEGER | FOREIGN KEY | References Students.StudentID |
| Enrollments | CourseID | VARCHAR(10) | FOREIGN KEY | References Courses.CourseID |
How the Data Dictionary helps: The DBMS uses this metadata to:
- Validate data entry (e.g., ensure StudentID is unique)
- Enforce relationships (e.g., prevent enrolling non-existent students)
- Optimize queries (using indexing information)
- Manage storage efficiently
Data Modelling and Logical Schema
Data Modelling Tool
Used to show the data structure of a database. An E-R (Entity-Relationship) diagram is a common example of a data model.
FirstName
LastName
DateOfBirth
CourseName
Credits
TeacherID
This E-R diagram shows that STUDENTS enroll in COURSES. The relationship "ENROLLS" connects the two entities.
Logical Schema
A logical schema is a data model for a specific database that is independent of the DBMS used to build it.
Logical Schema Characteristics:
- Describes how relationships will be implemented in the logical structure
- Independent of physical storage details
- Focuses on data relationships and constraints
- Provides a blueprint for database implementation
A logical schema for a library might show Books, Members, and Loans entities with their relationships, without specifying whether data is stored on SSD or HDD.
Real-Life Example: Hospital Management System
A hospital uses a DBMS with these components:
Data Dictionary
- Patients table structure
- Doctors qualifications data types
- Appointment validation rules
- Medicine inventory constraints
Data Model
- PATIENTS treated by DOCTORS
- DOCTORS prescribe MEDICINES
- PATIENTS have APPOINTMENTS
- APPOINTMENTS in ROOMS
Logical Schema
- One doctor many patients
- One patient many appointments
- One room many appointments
- Appointment links patient+doctor+room
The data dictionary ensures correct data types for medical records, the data model visualizes relationships, and the logical schema implements these relationships regardless of whether the DBMS is MySQL, Oracle, or SQL Server.
Activity 2: Designing a Library DBMS
Design components for a library database management system:
- List 5 pieces of metadata that would be in the data dictionary for a "Books" table.
- Draw a simple E-R diagram showing relationships between Books, Members, and Loans.
- Describe what the logical schema would specify about the relationship between Members and Loans.
- Explain how the data dictionary would help prevent a librarian from entering invalid data (e.g., negative number of copies).
- Why is it important that the logical schema is independent of the DBMS software?
Solution:
-
Books table metadata:
- BookID (INTEGER, PRIMARY KEY, NOT NULL)
- Title (VARCHAR(200), NOT NULL)
- Author (VARCHAR(100), NOT NULL)
- ISBN (VARCHAR(13), UNIQUE)
- CopiesAvailable (INTEGER, CHECK > 0)
- PublicationYear (INTEGER, CHECK between 1800 and CURRENT_YEAR)
-
E-R diagram:
[MEMBER] --- BORROWS --- [BOOK]
(One member can borrow many books, one book can be borrowed by one member at a time)
The "BORROWS" relationship would have attributes like LoanDate and DueDate. - Logical schema relationship: The logical schema would specify that there is a one-to-many relationship between Members and Loans (one member can have many loans), and each Loan must reference exactly one Book and one Member.
- Data dictionary validation: The data dictionary would include a validation rule for the "CopiesAvailable" field specifying it must be >= 0. The DBMS would reject any attempt to insert or update with a negative value.
- DBMS independence: A logical schema independent of DBMS allows the database design to be portable between different database systems (e.g., MySQL, PostgreSQL, Oracle). The same logical design can be implemented on different platforms without redesign.
Check Your Understanding: DBMS Features
1. What is a data dictionary and what does it store? [3 marks]
Answer
- [1 mark] A data dictionary stores metadata (data about data) for a database
- [1 mark] It contains definitions of tables, attribute names, data types, and relationships between tables
- [1 mark] It also stores validation rules and information about physical storage of data
- [Additional] The DBMS uses the data dictionary to manage and validate data
2. What is the purpose of a data modelling tool? [2 marks]
Answer
- [1 mark] To show the data structure of a database visually
- [1 mark] E-R (Entity-Relationship) diagrams are an example of data models created by such tools
- [Additional] Helps database designers plan and communicate database structure before implementation
3. What is a logical schema and how is it different from physical implementation? [3 marks]
Answer
- [1 mark] A logical schema is a data model for a specific database that is independent of the DBMS used
- [1 mark] It describes how relationships will be implemented in the logical structure
- [1 mark] It focuses on data relationships and constraints, not physical storage details
- [Additional] Same logical schema can be implemented on different DBMS software (MySQL, Oracle, etc.)
4. How does the use of a data dictionary improve data integrity? [3 marks]
Answer
- [1 mark] Stores validation rules that ensure data meets specific criteria
- [1 mark] Defines data types and constraints that prevent invalid data entry
- [1 mark] Maintains relationship constraints that ensure referential integrity between tables
- [Additional] Helps ensure data is accurate, complete and consistent throughout the database
5. Why is data independence important in a DBMS? [2 marks]
Answer
- [1 mark] Allows changes to data structure without affecting applications using the database
- [1 mark] Applications only access data they need through queries, not directly from storage structure
- [Additional] Provides flexibility to optimize storage or add new features without breaking existing applications
Data Security and Integrity
DBMS helps to provide data security to prevent unwanted alteration, corruption, deletion or sharing of data with unauthorized users. It also ensures data integrity - that data is accurate, complete and consistent.
How DBMS Ensures Data Security
DBMS provides multiple layers of security to protect data:
Access Control Methods
Usernames and Passwords
Prevent unauthorized access to the database system
Access Rights Management
Control what actions users can perform: read/write/delete, read only, or append only
Data Scope Control
Manage which parts of the database users can access
Protection and Monitoring
Automatic Backups
Scheduled regular backups to recover from data loss
Data Encryption
Protect data stored in database from unauthorized reading
Audit Trails
Record all actions taken by users for security monitoring
Access Rights Simulation: Hospital System
In a hospital DBMS, different staff members have different access rights to patient data:
| User Role | Access Rights | Can View | Can Modify | Cannot Access |
|---|---|---|---|---|
| Doctor | Read/Write | All patient records, medical history, test results | Diagnosis, prescriptions, treatment plans | Billing information, staff salaries |
| Nurse | Read/Append | Patient vitals, medication schedules | Add observations, update vitals | Medical diagnosis, delete records |
| Receptionist | Read Only | Patient contact info, appointment schedule | None (view only) | Medical records, test results |
| Accountant | Limited Read/Write | Billing information, insurance details | Update payment status, generate bills | Medical records, diagnosis |
How DBMS enforces this: The DBMS maintains an access control list that specifies what each user can do. When a nurse tries to view a patient's diagnosis, the DBMS checks permissions and denies access if not allowed.
Data Integrity in DBMS
How DBMS Ensures Data Integrity
1. Entity Integrity
Ensures each row in a table is uniquely identifiable (primary keys cannot be null or duplicate)
2. Referential Integrity
Ensures relationships between tables remain consistent (foreign keys must reference existing primary keys)
3. Domain Integrity
Ensures data values follow defined rules and constraints (data types, ranges, formats)
Real-Life Example: Banking System Security
A bank's DBMS implements multiple security measures:
If a hacker breaches the system, encryption protects data meaning. If data is corrupted, backups restore it. Audit trails help trace any unauthorized actions.
Important: Backup Strategies
Simply having backups isn't enough. Effective backup strategies include:
3-2-1 Backup Rule
- 3 copies of your data (original + 2 backups)
- 2 different media types (e.g., hard drive + cloud)
- 1 offsite backup (different physical location)
- Protects against hardware failure, theft, fire, ransomware
Backup Scheduling
- Full backups weekly or monthly (complete copy)
- Incremental backups daily (only changed data)
- Automated scheduling by DBMS
- Test restoration periodically to ensure backups work
Why Automated Backups Matter
Manual backups often get forgotten. DBMS can schedule automatic backups during low-usage periods (e.g., 2 AM). If the database crashes at 4 PM, you only lose data since the last backup (or since the last transaction log entry in some systems).
Activity 3: Security and Integrity Scenarios
Analyze these scenarios and explain how DBMS security and integrity features would help:
- A disgruntled employee tries to delete all customer records before leaving the company.
- Two receptionists at a clinic accidentally schedule two different patients for the same appointment slot.
- A hospital's server room floods, destroying the database server hardware.
- A doctor needs to access a patient's records from 5 years ago for continuity of care.
- An application bug tries to set a patient's age to 250 years.
Solution:
-
Deletion prevention:
- Access rights would limit the employee to only necessary functions (likely not bulk deletion)
- Audit trail would record the attempt and identify the employee
- Regular backups would allow recovery if deletion occurred
-
Double booking prevention:
- DBMS would enforce data integrity constraints on the Appointments table
- A unique constraint on appointment slot would prevent double booking
- Second receptionist would get an error when trying to book an already-taken slot
-
Hardware failure recovery:
- Automated offsite backups would provide copies of the database
- DBMS could be restored on new hardware from backups
- Transaction logs might allow recovery up to the point of failure
-
Historical data access:
- Historical data access:
- Backups would preserve historical records
- Access rights would allow the doctor to view but not modify old records
- Audit trail would show the doctor accessed those records
-
Invalid data prevention:
- Data dictionary validation rule would reject age > 150 (or similar reasonable limit)
- Domain integrity constraint would ensure age is within valid range
- DBMS would return an error instead of accepting invalid data
Check Your Understanding: Data Security & Integrity
1. List three ways DBMS provides data security. [3 marks]
Answer
- [1 mark] Usernames and passwords to prevent unauthorized access
- [1 mark] Access rights to control what users can do (read/write/delete)
- [1 mark] Automatic creation and scheduling of regular backups
- [Additional] Other methods: encryption, audit trails, access rights to specific database parts
2. What is the purpose of an audit trail in a DBMS? [2 marks]
Answer
- [1 mark] To record actions taken by users of the database
- [1 mark] Provides security monitoring and helps identify unauthorized or suspicious activities
- [Additional] Can be used for compliance, troubleshooting, and investigating security incidents
3. Differentiate between "read/write", "read only", and "append only" access rights. [3 marks]
Answer
- [1 mark] Read/write: User can view data and modify/delete existing data
- [1 mark] Read only: User can only view data, cannot modify or add new data
- [1 mark] Append only: User can add new data but cannot modify or delete existing data
- [Additional] Example: Receptionist = read only for medical records, Nurse = append only for observations, Doctor = read/write for diagnoses
4. How does encryption protect data in a DBMS? [2 marks]
Answer
- [1 mark] Converts data into unreadable format using encryption algorithms
- [1 mark] Prevents unauthorized users from understanding data even if they access it
- [Additional] Protects sensitive data like passwords, financial information, personal details
5. Why are automatic backups important compared to manual backups? [3 marks]
Answer
- [1 mark] Ensures backups are performed regularly and consistently
- [1 mark] Eliminates human error of forgetting to perform backups
- [1 mark] Can be scheduled during low-usage periods to minimize performance impact
- [Additional] Provides reliable recovery point in case of system failure or data corruption
DBMS Software Tools
DBMS includes various software tools that help in database development, management, and usage. Two key tools are the Developer Interface and the Query Processor.
Developer Interface
Purpose and Functions
Create User-Friendly Features
Design forms for data entry (e.g., forms to enter new bookings)
Create Outputs
Generate reports (e.g., report of bookings on a given date)
Create Interactive Features
Design buttons, menus, and other UI elements for user interaction
SQL Query Development
Write queries in Structured Query Language (SQL) instead of using query-by-example
Real-World Example
The developer interface allows creating these elements without programming everything from scratch.
Query Processor
The Query Processor is a core component that handles database queries. It includes several sub-components:
DDL Interpreter
Interprets Data Definition Language statements (CREATE, ALTER, DROP). Records schema changes in the data dictionary.
DML Compiler
Compiles Data Manipulation Language statements (SELECT, INSERT, UPDATE, DELETE) into low-level instructions. Optimizes queries for better performance.
Query Evaluation Engine
Executes the compiled query instructions. Retrieves data from storage, performs calculations, applies filters, and returns results.
Query Processor Simulation: Student Database
Follow how a query is processed through the query processor components:
Input SQL Query:
Query Purpose:
Find all students with grade 85 or higher, display their names and grades sorted by last name.
Query Processing Steps:
- DML Compiler: Analyzes SQL, checks syntax, validates table/column names
- Query Optimization: Determines most efficient way to execute (use index on Grade?)
- Compilation: Converts to low-level instructions the database can execute
- Execution: Query engine retrieves data, applies WHERE filter (Grade >= 85)
- Sorting: Orders results by LastName as specified
- Output: Returns only FirstName, LastName, Grade columns
Optimization example: If there's an index on the Grade column, the query processor might use it to quickly find students with Grade >= 85 instead of checking every row. This makes the query much faster on large databases.
Purpose of Query Processor
User Input
Allows users to enter criteria or conditions for retrieving specific data
Data Search
Conducts searches within database based on provided criteria to identify relevant data
Results Formatting
Structures and organizes retrieved data into suitable format for presentation
Real-Life Example: E-commerce Database Tools
An online store uses DBMS tools extensively:
Developer Interface Creates:
Query Processor Handles:
During Black Friday, the query processor optimizes queries to handle high traffic, while the developer interface allows quick updates to promotional banners.
Activity 4: DBMS Tools Application
A library wants to improve its database system with better tools:
- What features would the developer interface provide for library staff?
- Describe how the query processor would handle a search for "all books by J.K. Rowling published after 2000".
- Why would a librarian use SQL queries instead of query-by-example for complex reports?
- What might the DML compiler optimize in a query for "most borrowed books last month"?
- How do these tools help library patrons (users) indirectly?
Solution:
-
Developer interface features:
- Book entry form with ISBN scanner integration
- Member registration form
- Check-out/check-in interface with barcode reader
- Overdue book reports and reminder system
- Interactive catalog search for patrons
- Reservation management system
-
Query processing:
- DML compiler analyzes: SELECT * FROM Books WHERE Author='J.K. Rowling' AND YearPublished > 2000
- Optimizer might use indexes on Author and YearPublished columns
- Query engine searches Books table, applies both conditions
- Returns matching rows formatted as a results table
- SQL vs query-by-example: SQL allows more complex queries with multiple conditions, joins across tables, calculations, grouping, and sorting. For example, a report showing "books borrowed more than 5 times last month by genre with average borrower age" would need SQL.
-
Query optimization: The DML compiler might:
- Use an index on the BorrowDate column to quickly find last month's loans
- Pre-calculate counts instead of counting repeatedly
- Cache intermediate results if similar queries are run often
- Choose to scan the Loans table first, then join with Books
- Benefits to patrons: Faster book searches, accurate availability information, online reservation system, personalized recommendations (based on borrowing history), mobile app integration - all powered by efficient database tools.
Check Your Understanding: DBMS Tools
1. What is the purpose of the developer interface in a DBMS? [3 marks]
Answer
- [1 mark] To create user-friendly features like forms for data entry
- [1 mark] To create outputs such as reports based on database data
- [1 mark] To create interactive features like buttons and menus for user interaction
- [Additional] Allows developers to write SQL queries instead of using only query-by-example
2. What are the main components of a query processor? [3 marks]
Answer
- [1 mark] DDL interpreter - interprets Data Definition Language statements
- [1 mark] DML compiler - compiles Data Manipulation Language statements
- [1 mark] Query evaluation engine - executes the compiled query instructions
- [Additional] DML compiler also optimizes queries for better performance
3. What does the DDL interpreter do with DDL statements? [2 marks]
Answer
- [1 mark] Interprets Data Definition Language statements (CREATE, ALTER, DROP)
- [1 mark] Records the schema changes in the database's data dictionary
- [Additional] Updates metadata about database structure when tables are created or modified
4. Why does the DML compiler optimize queries? [2 marks]
Answer
- [1 mark] To improve query performance and efficiency
- [1 mark] To reduce execution time and resource usage
- [Additional] Chooses the best execution plan, like using indexes instead of scanning entire tables
5. How does the query processor help users find specific data? [3 marks]
Answer
- [1 mark] Enables users to input criteria or conditions for retrieving data
- [1 mark] Conducts searches within the database based on provided criteria
- [1 mark] Structures and organizes retrieved data into suitable format for presentation
- [Additional] Processes complex queries efficiently even on large databases
Key Takeaways
- Database Management System (DBMS) is software that manages database creation, retrieval, update and administration
- DBMS solves file-based system problems: reduces data redundancy, eliminates inconsistency, provides data independence
- Data dictionary stores metadata including table definitions, data types, relationships, and validation rules
- Data modelling tools (like E-R diagrams) visualize database structure and relationships
- Logical schema describes database structure independently of specific DBMS software
- DBMS ensures data security through access control, encryption, backups, and audit trails
- Access rights control what users can do: read/write, read only, or append only
- Automatic backups protect against data loss and enable recovery from failures
- Data integrity is maintained through entity, referential, and domain constraints
- Developer interface allows creating forms, reports, and interactive features without coding everything
- Query processor handles SQL queries through DDL interpreter, DML compiler, and query evaluation engine
- Query optimization improves performance by choosing efficient execution plans
- DBMS advantages over file-based systems: data consistency, security, concurrent access, reduced redundancy
- Different users can access the same database with different views and permissions
Question Bank
1. Explain how a DBMS addresses the three main problems of file-based systems: data redundancy, data inconsistency, and data dependency. [6 marks]
Marking Scheme & Answer
- [2 marks] Data Redundancy: DBMS stores data in separate linked tables using foreign keys, reducing duplication. Flags errors when trying to delete linked data.
- [2 marks] Data Inconsistency: Stores most data only once so updates are seen by all applications. Improves integrity as data is changed only once, not multiple times in different files.
- [2 marks] Data Dependency: Data is independent of applications. Changes to data structure managed by DBMS don't affect applications. Applications access only needed fields/tables through queries.
- [Additional] DBMS provides a centralized approach vs. decentralized file-based systems, ensuring consistency and efficiency.
2. Describe the contents and purpose of a data dictionary in a DBMS. [5 marks]
Marking Scheme & Answer
- [1 mark] A data dictionary stores metadata (data about data) for a database
- [1 mark] Contains definitions of tables and their structure
- [1 mark] Stores attribute/field names and their data types
- [1 mark] Maintains information about relationships between tables and any indexing
- [1 mark] Defines validation rules for data entry and contains physical storage information
- [Additional] Purpose: Helps ensure data integrity, provides information for query optimization, documents database structure for developers and administrators.
3. Explain the difference between a data model and a logical schema. [4 marks]
Marking Scheme & Answer
- [2 marks] Data Model: A tool used to show the data structure of a database (e.g., E-R diagram). It's a visual representation of entities and their relationships.
- [2 marks] Logical Schema: A specific data model for a particular database that is independent of the DBMS used to build it. Describes how relationships will be implemented in the logical structure.
- [Additional] Analogy: Data model is like a blueprint for houses in general; logical schema is the specific blueprint for one house that could be built by different contractors (DBMS software).
4. Describe five security features provided by a DBMS to protect data. [5 marks]
Marking Scheme & Answer
- [1 mark] Usernames and passwords to prevent unauthorized access
- [1 mark] Access rights management (read/write/delete, read only, append only)
- [1 mark] Control over which parts of database users can access
- [1 mark] Automatic creation and scheduling of regular backups
- [1 mark] Encryption of stored data
- [Additional] Also: Automatic audit trails/activity logs, validation rules to prevent invalid data, concurrent access control to prevent conflicts.
5. Compare and contrast the developer interface and query processor in a DBMS. [6 marks]
Marking Scheme & Answer
| Aspect | Developer Interface | Query Processor |
|---|---|---|
| Primary Purpose | Create user-friendly features and applications | Process and execute database queries |
| Main Functions | Create forms, reports, buttons, menus; write SQL queries | Interpret DDL, compile DML, optimize and execute queries |
| Users | Database developers, administrators | All users (through applications) and developers |
| Output | User interfaces, applications, reports | Query results, data retrieval |
| Relationship | Uses query processor to execute SQL queries | Processes queries generated by developer interface |
Key point: Developer interface creates the front-end for users to interact with the database; query processor handles the back-end processing of data requests.
6. Why is data independence important in a DBMS, and how is it achieved? [4 marks]
Marking Scheme & Answer
- [1 mark] Importance: Allows changes to data structure without affecting applications
- [1 mark] Applications don't need modification when database structure changes
- [1 mark] Achieved by: DBMS managing the interface between applications and data
- [1 mark] Applications access data through queries/views, not directly from files
- [Additional] Example: Adding a new column to a table doesn't break applications that don't use that column. DBMS handles the structural change transparently.
7. Explain how a DBMS maintains data integrity. Provide examples. [5 marks]
Marking Scheme & Answer
- [1 mark] Entity Integrity: Primary keys ensure each row is unique (e.g., StudentID must be unique and not null)
- [1 mark] Referential Integrity: Foreign keys maintain relationships (e.g., Can't enroll student in non-existent course)
- [1 mark] Domain Integrity: Data types and constraints validate values (e.g., Age between 0-150, email format)
- [1 mark] Validation Rules: Defined in data dictionary (e.g., Grade must be between 0-100)
- [1 mark] Consistency: Data stored once ensures updates propagate everywhere
- [Additional] DBMS automatically enforces these rules when data is inserted, updated, or deleted.
8. What is the role of the DML compiler in the query processor? [3 marks]
Marking Scheme & Answer
- [1 mark] Compiles Data Manipulation Language statements (SELECT, INSERT, UPDATE, DELETE)
- [1 mark] Converts SQL queries into low-level instructions for execution
- [1 mark] Optimizes queries to improve performance and efficiency
- [Additional] Example: Might choose to use an index instead of scanning entire table, or reorder operations for faster execution.
9. Describe a scenario where different users need different access rights to the same database. [4 marks]
Marking Scheme & Answer
- [1 mark] Scenario: Hospital database with patient records
- [1 mark] Doctors: Read/write access to medical records, diagnosis, prescriptions
- [1 mark] Nurses: Read/append access to vital signs, observations; cannot modify diagnoses
- [1 mark] Receptionists: Read-only access to patient contact info, appointment schedules
- [Additional] Accountants: Access to billing information only, not medical records. DBMS enforces these rights through user accounts and permissions.
10. Explain the advantages of using a DBMS over traditional file-based systems for a school managing student records. [6 marks]
Marking Scheme & Answer
- [1 mark] Reduced Data Redundancy: Student information stored once, not in separate files for attendance, grades, personal details
- [1 mark] Data Consistency: When student address changes, updated once not in multiple files
- [1 mark] Data Integrity: Validation rules ensure valid data (e.g., grades 0-100, valid dates)
- [1 mark] Security: Different access for teachers (grades), admin (personal data), students (view only)
- [1 mark] Concurrent Access: Multiple teachers can enter grades simultaneously without conflicts
- [1 mark] Backup and Recovery: Automated backups protect against data loss
- [Additional] Query Capability: Complex queries like "students with >90% attendance and >80% average" are easy. Data Independence: Changes to database structure don't break existing applications.