Databases and Information Management
Domain focus
Databases and Information Management tests relational concepts, keys, data types, validation, queries and the difference between a database and a DBMS. Expect questions on reducing redundancy and producing information from tables.
Core rules and concepts
- A database is an organised collection of related data; a DBMS is software that creates, queries and controls it.
- A table has records (rows) and fields (columns). Each field has a data type and may have validation.
- A primary key uniquely identifies a record and cannot be null or duplicated. A foreign key links to a primary key in another table.
- Relationships (one-to-one, one-to-many, many-to-many) reduce unnecessary repetition. A many-to-many relationship needs a junction table.
- Validation checks input format or range; verification checks that data was copied or entered correctly.
- SQL:
SELECT fields FROM table WHERE condition;
Worked example
Tables: Student(StudentID, Name, Grade) and Subject(SubjectCode, Title). A student takes many subjects, and a subject has many students, so create Enrolment(StudentID, SubjectCode, Mark). StudentID and SubjectCode are foreign keys; together they can form a composite primary key. To list names of Grade 11 students: SELECT Name FROM Student WHERE Grade = 11; The WHERE clause filters records; SELECT chooses columns. The junction table prevents repeating subject titles for every enrolment.
Traps that cost marks
Saying a primary key may be blank; confusing a field with a record; using a foreign key as a unique identifier in its own table; writing SELECT * WHERE ... without FROM; confusing validation with verification; and storing the same subject title repeatedly instead of using a relationship.