Database Management Grade 11
A database is like a highly organised digital filing cabinet — instead of papers stuffed into folders, your data is stored in neat tables with rows and columns, so you can find, sort and filter it in seconds. A DBMS (Database Management System) is the software that manages it all.
Key Terms
| Term | Definition |
|---|---|
| Database | An organised collection of data stored in tables |
| Table | Rows and columns storing one type of entity (e.g. Students) |
| Record | One complete row — all data about one item |
| Field | One column — a specific attribute (e.g. Surname, Age) |
| Primary key | Unique field that identifies each record (e.g. StudentID) |
| Foreign key | A field that links to the primary key of another table |
| DBMS | Database Management System — software to create, manage and query databases |
T2Term 2 · DBMS, Database Types & Careers
Common DBMS Software
| DBMS | Type | Cost | Use Case |
|---|---|---|---|
| Microsoft Access | Desktop | Paid (MS Office) | Small databases, school/office, learning, Grade 11–12 IT practicals |
| MySQL | Open-source server | Free | Web applications, websites (used with PHP) |
| Oracle | Enterprise server | Very expensive | Banks, airlines, hospitals — mission-critical systems |
| PostgreSQL | Open-source server | Free | Advanced queries, large datasets, analytics |
| Microsoft SQL Server | Enterprise server | Paid | Large corporate databases, Windows environments |
Database Types by Usage
| Type | Description | Example |
|---|---|---|
| Desktop / Personal | Single user, stored locally on one computer | MS Access for a school IT project |
| Server / Centralised | Multi-user, stored on a server, accessed over a network | School administration system accessed by teachers and admin |
| Distributed | Spread across multiple locations/servers, synchronised | A national learner database accessed from all provinces |
Database Careers
- DBA (Database Administrator) — maintains security, backups, performance tuning
- Database Programmer / Developer — writes queries, stored procedures, applications
- Database Analyst — designs structure, models relationships, plans migrations
- Data Scientist — analyses large datasets to find patterns and insights
- Project Manager — manages database projects, budgets and teams
T3Term 3 · Database Design, Integrity & Working With Data
What a Database Table Looks Like
A table in a database looks exactly like a spreadsheet grid. The table has a name, column headers (fields), and rows of data (records).
Primary Key vs Foreign Key
Keys are what make databases powerful — they link tables together so you do not repeat data.
| Key Type | Description | Example |
|---|---|---|
| Primary Key (PK) | Uniquely identifies each record. Cannot be NULL. Cannot repeat. | StudentID = 1001 |
| Foreign Key (FK) | A field in one table that refers to the PK of another table. | ClassID in tblStudents → ClassID in tblClasses |
| Composite Key | Two or more fields combined to form a unique identifier. | StudentID + SubjectCode |
Entity Relationship Diagram (ERD)
An ERD shows the tables in a database and how they are related. The line between tables shows the type of relationship (one-to-many, etc.).
Database Field Data Types
Each field in a table has a data type that controls what kind of data can be stored.
| Data Type | Also Called | Description | SA School Example |
|---|---|---|---|
| Text / Short Text | VARCHAR, String | Letters, numbers, symbols — max 255 chars | Student name, school name |
| Number | Integer, Long Integer | Whole numbers or decimals (for calculations) | Mark out of 100, age |
| Date/Time | DateTime | Dates and/or times stored as numeric values internally | Date of birth, test date |
| Yes/No | Boolean | Only two values: True/False, Yes/No, 1/0 | Has paid school fees, is a prefect |
| AutoNumber | Identity, Auto-increment | Automatically assigns the next unique integer — ideal for PK | StudentID, RecordID |
| Currency | Money, Decimal | Monetary values with fixed decimal precision | Fee amount, textbook cost |
| Memo / Long Text | Text (large) | Longer text — more than 255 characters | Teacher comments, notes |
| OLE Object | Blob | Stores files — images, documents, audio | Student photo, report PDF |
Metadata
Metadata is "data about data" — information that describes other data, rather than being the actual content itself. It helps you organise, categorise, search and correctly interpret data.
| Data | Example Metadata |
|---|---|
| A photograph | Date taken, camera type, GPS location |
| A document | Author, creation date, date last revised |
| A database field | Data type, size, constraints (e.g. required, max length) |
Table Relationships
| Relationship | Description | Example |
|---|---|---|
| One-to-One | One record in Table A links to exactly one record in Table B | Student ↔ Locker (each student has one locker) |
| One-to-Many | One record in Table A links to many records in Table B | Class → Students (one class has many students) |
| Many-to-Many | Many records in A link to many records in B (needs a linking table) | Students ↔ Subjects (students take many subjects; subjects have many students) |
Data Quality Characteristics
Data quality means the data is fit for purpose. Poor quality data leads to wrong decisions.
| Characteristic | Meaning | SA School Example |
|---|---|---|
| Accuracy | Data is correct and free from errors | A learner's mark recorded as 78, not 87 (transposition error) |
| Correctness | Data conforms to defined rules and constraints | Grade field only contains values 8–12 |
| Currency | Data is up to date — not outdated | A learner's address updated after they move house |
| Completeness | All required data is present — no blank fields where required | Every learner record has a valid ID number and grade |
| Relevance | Only data that is needed is stored | A school database does not need to store a learner's favourite colour |
Data Integrity
Data integrity ensures data is accurate, consistent and reliable over its entire lifetime.
- Physical integrity — protection from hardware failure using RAID, backups, and UPS.
- Logical integrity — data follows rules: input validation, referential integrity, access control.
- Referential integrity — a FK value must exist as a PK in the related table (no orphan records).
Validation Types
Validation checks that data entered is reasonable and in the correct format — it does not check if data is true, just if it is acceptable.
| Check | Description | SA School Example |
|---|---|---|
| Format check | Data matches required pattern or structure | Email must contain @ and a dot — alice@school.co.za |
| Range check | Value falls within acceptable minimum and maximum | Test mark must be between 0 and 100 |
| Type check | Data entered is the correct data type | Age field must be a number, not text |
| Presence check | The field must not be blank (required) | Surname cannot be empty — every learner must have a surname |
| Check digit | A calculated digit is appended and re-calculated to verify correctness | SA ID number: last digit is a check digit |
| Lookup check | Value must match an item in a predefined list | Province field: only one of the 9 SA province names allowed |
| Consistency check | Two or more fields must be logically consistent | Date of Birth must match the learner's stated age |
Manual vs Electronic Methods
Data doesn't have to live in a computer database — it can also be recorded and processed by hand. Comparing the two shows why databases and DBMS software are so valuable.
| Method | Description | Advantages | Disadvantages |
|---|---|---|---|
| Manual | Recording and processing data by hand — no computer involved (e.g. registers, index cards, paper forms) | No electricity or computer needed; simple for very small datasets | Slow; prone to errors (typos, miscalculations); hard to search, sort or summarise large amounts of data |
| Electronic | Using a computer and software (e.g. a DBMS) to capture, store and process data | Fast access and processing of large datasets; less prone to error when validation rules are used; easy to search, sort, filter and generate reports; many users can access data at once | Needs electricity and working hardware/software; security risks if not managed properly |
Creating and Maintaining a Database
Setting up a working database is a process, not a single step:
- Design the table — decide the purpose (e.g. track student information), the fields (columns) needed, and set a primary key (e.g. StudentID) so every record is unique.
- Insert / import data — add records manually through a form (e.g. an MS Access form), or import them in bulk from a spreadsheet or CSV file.
- Edit / update data — correct mistakes or update values as information changes (e.g. a learner moves up a grade).
- Delete data — remove records that are no longer needed or were entered incorrectly.
Processing Data: Sort, Query, Filter, Report
Once data is stored, a DBMS lets you turn it into useful information in several ways:
| Technique | Purpose | Example |
|---|---|---|
| Sorting | Arrange records in a specific order | Sort students alphabetically by Surname, or by Mark from highest to lowest |
| Querying | Retrieve specific records that answer a question you ask the database | "Find all students in Grade 11"; "List students older than 16" |
| Filtering | Display only the records that meet a condition, hiding the rest | Show only students with Grade = 12 |
| Generating reports | Summarise or present data in a readable format — tables, charts or dashboards | Count of students in each grade; average age of students; list of email addresses |
Looking ahead
You will practise Filter and Sort for real using TADOTable in Databases in Delphi, and full querying with SQL next year in Grade 12.