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 |
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 |
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 |
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 | If Grade = 10, Year must be after 2005 (15-year-old) |
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