Databases

— Computer Science · AS Level (9618) · Paper 1 —

★ Exam Tip: Database is a memory + concept chapter and comparatively easy — the same questions repeat every session (limitations of file-based, PK & FK role, SQL queries, ER diagrams, normalisation). Watch the command word: list / what = just name the points (3 marks → 3 points); describe / explain = each point needs an explanation too.

1. Data Storage — Two Approaches

① File-based approach

“The data is stored in one or more separate computer files”
≈ Excel / flat files. Can still hold data, but it is not a relational database.

② Relational database

“A way of structuring information in tables, rows and columns”
Tables are linked (related) and managed by a DBMS (e.g. MS Access).

linking tables with keys removes the repeated teacher data (redundancy)

Remember: database ≠ DBMS. The database is where data is stored; the DBMS is the software that manages it.

Key idea: a relational DB keeps the same row/column idea as a file, but adds tools (query processor etc.) and lets you form relationships between tables — impossible in the file-based approach, where data just repeats.

2. Limitations of the File-Based Approach

Q: What are the limitations of using a file-based approach?

Limitation	Meaning (board)	Example (sir)
Data redundancy	data is repeated in more than one file	student registers twice (no feedback after 1st form) → same data stored 2×
Data dependency	changes to data mean changes to the program accessing the data	family name changes → must edit every single value in every file
Lack of data integrity	entries that should be the same can differ in different places	ID 1145 = “Taha” in one table but “Ali” in another
Lack of data privacy	all users have access to all data if a single flat file	can't set a username/password or hide part of the file

★ Exam Tip (marks!): If the question says “list” (3 marks) → just write 3 of the names. If it says “describe” → 1 mark for naming + 1 mark for explaining each point. So memorise name + meaning of all four.

Trick: integrity ≈ accuracy / consistency — same fact must read the same everywhere.

3. Benefits of a Relational Database

Q: Benefits of a relational database instead of a flat file?

Just flip each limitation — they are the opposite (“vice versa”):

File-based problem	Relational database gives…
data redundancy	reduced data redundancy
data dependency	reduced data dependency
lack of integrity	improved data integrity
lack of privacy	improved data privacy
—	program–data independence
—	ability to create ad hoc queries

Ad hoc query = a query designed for a particular purpose, written on the spot, e.g. “all licences whose expiry date is after 30 Oct 2022”.

Analogy: Google updates / moves its servers (e.g. California → India) and your data is untouched — and changing your data doesn't break Google's programs. That is independence.

Program–data independence means:

structure of data can change and does not affect the program
structure of programs can change and does not affect the data

Q: Describe the features of a relational database that address the limitations of a file-based approach

✓ Feature → effect (write each as a pair!)

Multiple tables are linked together (using primary & foreign keys) → reduces data redundancy, increases data integrity, referential integrity can be enforced
Program–data independence → data structure can change without affecting the program, and vice versa
Complex queries can be more easily written (SQL) → to find specific data
Different users can be given different access rights → improves security
Different users can be given different views of the data → they don't see confidential information, so data privacy is maintained

Example (sir) – access rights & views:

Table 1 = Jobs assigned, Table 2 = Finance (what I pay / my profit). Employees may edit/view the Jobs table but get no access to Finance. Like Google Drive: editor, viewer or no access.

Example – linked tables cut redundancy:

Students (Taha, Ali, Qasim → teacher X; Abdullah → Y). In one flat file teacher X's details repeat 3×. Make a Student table and a Teacher table and link them → each teacher stored once. (Proper method = normalisation, last section.)

4. Database Terms

DBMS = Database Management System — software that manages databases (e.g. Microsoft Access; AWS/IBM are the databases/platforms). Studied in detail at university; here only features.

The Student table — spot every term

Roll No.	Name	Course
CS08	Steive	Comp. Sci.
EE54	Jhoson	Electronics
B12	Eva	Biology
F32	Jhoson	Finance
M26	Erica	Maths

● Entity = the whole Student table | ● Attributes = Roll No., Name, Course (headings) | ● Field = a column (e.g. Course) | ● Record / Tuple = one row (e.g. M26 Erica Maths)

Note: sir recoloured attributes pink so they show up in light-mode notes. Questions usually give you data and ask “name the entity / an attribute / a field”, or ask you to match a definition to a word.

Term	Definition	Here
Entity	The concept or object in the system that we want to model and store information about	Student (also: employee, course)
Attribute	A data item, represented as a field within a table	Roll No, Name, Course
Field	a column value / column of the table	Course column
Record = Tuple	all the data about one entity instance = one row (like filling one form)	M26 · Erica · Maths
Table	all the data about one entity	the Student table
Primary key	A unique identifier for each tuple	Roll No
Foreign key	A field in one table that links to a primary key in another table	Roll No in Scores

5. Keys — Primary, Foreign, Candidate

① Primary key (PK)

A unique identifier for each tuple (every record has one). Test an attribute: can it repeat?

Name → no (“Jhoson” appears twice)
Course → no (many students take Maths)
Roll No → yes — different for every student (like CNIC, passport no.)

So to tell the two “Jhoson”s apart we use Roll No as the primary key. In exam tables the PK is underlined.

a primary key in the base table, reused in another table, is a foreign key

② Foreign key (FK)

Take the PK of the base table and use the same field in another table (e.g. a Scores table) → there it is a foreign key. Its job: create a relationship between the tables.

③ Candidate key & secondary key

Analogy: sitting the A-level exam makes you only a candidate — not yet passed. Many candidates → one gets chosen (the “primary”).

Student table: Roll No, CNIC, Passport Number, Candidate number can each uniquely identify a student ⇒ all are candidate keys. Choose Roll No → it is the primary key; the rest are secondary keys.

one candidate key is chosen as primary; the others become secondary keys

Note: set of keys that could uniquely identify a record = candidate keys; one of them is used as the primary key and the rest are secondary keys. (No exam question on this yet — but it is in the syllabus.)

Composite key: when no single attribute is unique, two (or more) attributes together form the primary key — e.g. SoftwareName + CustomerID, both underlined in SOFTWARE_PURCHASED.

6. Past-Paper Practice — Terms & Keys

SOFTWARE_MANAGEMENTidentify terms · 3 marks

Database tables (PKs underlined): CUSTOMER_DETAILS(CustomerID, CompanyName, Address1, Address2, City) and SOFTWARE_PURCHASED(SoftwareName, SoftwareDescription, CustomerID, LicenceType, LicenceCost, RenewalDate). Give an example of: entity, foreign key, attribute.

An entity is an object we store info about → a table; “CustomerID” alone is not an object. An attribute is a feature (column) of it. The FK is the field also used in the other table (the PK of CUSTOMER_DETAILS).

Term	Example
Entity	CUSTOMER_DETAILS or SOFTWARE_PURCHASED
Foreign key	CustomerID
Attribute	SoftwareDescription

Tip: when it asks for “examples” give what's asked — any one valid answer per row = free marks (“muft ke 3 marks”).

ACCOUNTS (bank)design tables · PK · FK · 5 marks

A bank stores customers' first name, last name, DOB; each account type has a unique ID, a name (regular/saving) and a bonus ($5/$10/$15); a customer can have more than one account; each customer account has its own ID number and the amount of money. Normalised design = 3 tables: CUSTOMER, ACCOUNT_TYPE, CUSTOMER_ACCOUNT.

Method: pull each attribute out of the wording, underline the PK. No ID given for customer? first/last name and DOB can all repeat → invent a new attribute CustomerID (common sense). CUSTOMER_ACCOUNT needs the two FKs to link the other tables.

CUSTOMER ( CustomerID, FirstName, LastName, DateOfBirth ) ACCOUNT_TYPE ( AccountID, Name, Bonus ) CUSTOMER_ACCOUNT ( ID, Amount, CustomerID, AccountID ) PKs [2]: CUSTOMER → CustomerID · ACCOUNT_TYPE → AccountID · CUSTOMER_ACCOUNT → ID One FK [1]: table CUSTOMER_ACCOUNT → FK CustomerID (AccountID also works)

CUSTOMER_ACCOUNT links the other two tables using two foreign keys

Why a 3rd table? CUSTOMER and ACCOUNT_TYPE had no common key → no relationship. CUSTOMER_ACCOUNT uses CustomerID (PK of CUSTOMER) and AccountID (PK of ACCOUNT_TYPE) as foreign keys — that is how normalisation links tables.

Matching termsdefinition → term · 3 marks

Definition	Term
All the data about one entity	Table
The data in one row of a table	Record / Tuple
A column or field in a table	Attribute

Role of PK & FKdescribe · repeats nearly every session ★

Describe the role of a primary and a foreign key in a database relationship

Primary key uniquely identifies each tuple
Primary key can be used as a foreign key in another table
to form a relationship between the tables

7. Structured Query Language (SQL) — DDL

SQL = Structured Query Language. It has two parts:

DDL — Data Definition Language
defines the structure: create database / table, add a field (ALTER)

DML — Data Manipulation Language
works on the data: INSERT, UPDATE, SELECT, DELETE

① DDL — create a database

CREATE DATABASE <Name>;

e.g. CREATE DATABASE Student;

EMPLOYEESDDL · 1 mark ✔ 1/1

Write a DDL statement to create the EMPLOYEES database.

CREATE DATABASE Employees;

② DDL — create a table

Data types (know examples):

Type	Example	Meaning
Integer	54	whole number
Date	04/04/2020	a date
Time	18:00	a time
Varchar	—	(string)
Varchar(n)	—	string which can contain a maximum of n characters

CREATE TABLE <Name> (
  <attribute name> <datatype>,
  <attribute name> <datatype>,
  <attribute name> <datatype>,
  PRIMARY KEY (name) NOT NULL
);

Remember: commas after every attribute except the last line; PRIMARY KEY(…) goes last; statement ends with );

EMPLOYEE_DATADDL · 5 marks

Define the table EMPLOYEE_DATA and declare EmployeeID as the primary key. Data: EmployeeID varchar(7) (156FJEK), FirstName varchar, LastName varchar, DateOfBirth Date (12/05/1984), Gender varchar(6) (Male/Female), DepartmentNumber varchar(2) (S1, F2).

Read the example data to pick each type: ID has letters → varchar(7); “Female” = 6 letters → varchar(6); “S1” = 2 chars → varchar(2).

CREATE TABLE Employee_Data ( EmployeeID VARCHAR(7), FirstName VARCHAR, LastName VARCHAR, DateOfBirth DATE, Gender VARCHAR(6), DepartmentNumber VARCHAR(2), PRIMARY KEY (EmployeeID) NOT NULL);

Note: this is the exam-board style taught on the board. In many real SQL engines NOT NULL is written beside the column (EmployeeID VARCHAR(7) NOT NULL) and the last line is just PRIMARY KEY (EmployeeID) — stick to the board's form in the exam.

③ DDL — add a new field

ALTER TABLE <table name>
ADD <field name> <datatype>;

STUDENT telephoneDDL · [2] — scored ½ on board

STUDENT needs an extra field for the telephone number, e.g. 012-3456. Write a DDL statement to add it.

ALTER TABLE StudentADD TelNum VARCHAR;

Why only ½? The ✓ was circled on ADD and the field/datatype underlined — the marks are for ALTER TABLE, ADD, a sensible field name, and a data type that fits the data. “012-3456” contains a hyphen, so it must be VARCHAR (text), not INTEGER. Check you write every part, including the final ;

8. DML — Manipulating the Data

① INSERT — add data into a table

INSERT INTO <table name>
VALUES (<data in column 1>, <data in column 2>, …);

ROOMDML · [2]

ROOM(RoomNumber, RoomType) holds 1 Standard, 2 Double, 3 Executive, 4 Standard. Room 5 is a Double room — complete the DML to add it.

INSERT INTO ROOMVALUES ( 5, "Double" );

Text values go in quotes; numbers don't. Values follow the column order.

SHOP_SUPPLIERSQL script · [3]

SHOP_SUPPLIER(ShopID, SupplierID). Existing shop 8765 has used existing supplier SUP89 for the first time — add this data.

INSERT INTO SHOP_SUPPLIER(ShopID, SupplierID)VALUES ( 8765, "SUP89" );

Correction: the board wrote VALUE — the keyword is VALUES.

Two ways to write INSERT INTO:
1. Specify column names and values: INSERT INTO table_name (column1, column2, column3, …) VALUES (value1, value2, value3, …);
2. Adding values for all the columns? You can skip the column names — but the order of the values must match the table's column order: INSERT INTO table_name VALUES (value1, value2, value3, …);

② UPDATE — change data / a record

UPDATE <table name>
SET <column 1> = <value 1>, <column 2> = <value 2>, …
WHERE <condition>;

Warning: forget WHERE and every record is changed!

B_NURSEUPDATE · [3]

Fatima Woo is an Area B nurse, NurseID 076. She married and her new family name is Chi. Write an SQL command to update her record.

Table = B_Nurse; column to change = FamilyName (old value Woo → new Chi); find the record by the unique ID (not the name).

UPDATE B_NurseSET FamilyName = "Chi"WHERE NurseID = "076";

③ SELECT — display / return values

SELECT <column 1>, <column 2>, …
FROM <table name>
WHERE <conditions>;

B_NURSE (NurseID, FamilyName, Specialism)SELECT · [3] ✔ 3/3

Display the Nurse ID and family name for all Area B nurses with specialism ‘THEATRE’.

SELECT NurseID, FamilyNameFROM B_NurseWHERE Specialism = "THEATRE";

LESSONSELECT with 2 conditions

LESSON(LessonID, StudentID, InstructorID, LessonDate, LessonTime). Return the date and time of all future lessons booked with instructor Ins01.

Underline the clues: date and time → SELECT list; future → LessonDate > today's date; Ins01 → InstructorID = “Ins01”. Two conditions ⇒ join with AND.

SELECT LessonDate, LessonTimeFROM LESSONWHERE InstructorID = "Ins01"AND LessonDate > 22/11/2023;(22/11/2023 = “today” on the board; in a paper use the date given in the question)

order of clauses in a query (only SELECT + FROM are compulsory)

GAME_DEVELOPMENT ★ Difficult questionSELECT from 2 tables · WHERE-join

GAME_DEVELOPMENT(GameName, Genre, TeamNumber, DevelopmentStage, ManagerID) e.g. Bunny Hop · Platform · 4 · Analysis · 23KP; Fried Eggs · Retro · 2 · Programming stage 1 · 9RTU; Create-a-game · Action · 1 · Acceptance testing · 11TF. Another table: PRODUCT_MANAGER(ManagerID, FirstName, LastName). Return the game name, genre and team number of all games managed by the product manager James Fitz.

Why two tables? The name James Fitz lives in PRODUCT_MANAGER but the games live in GAME_DEVELOPMENT. They are connected by ManagerID (PK in PRODUCT_MANAGER, FK in GAME_DEVELOPMENT) — so add a join condition in the WHERE.

SELECT GameName, Genre, TeamNumber FROM Game_Development, Product_Manager WHERE Product_Manager.FirstName = "James" ✓ AND Product_Manager.LastName = "Fitz" ✓ AND Product_Manager.ManagerID = Game_Development.ManagerID;

Board slip: the last line was written GameDevelopment.ManagerID (no underscore) — use the exact table name Game_Development. Ticks were given for the first two conditions; the third (the join condition) is the one students forget. A neater way = INNER JOIN (section 9).

LICENCE (software company)SELECT · ORDER BY · 3–4 marks

CUSTOMER(CustomerID, CompanyName) · SOFTWARE(SoftwareID, SoftwareName, OperatingSystem, Description) · LICENCE(LicenceID, CustomerID, SoftwareID, DateOfPurchase, LicenceType, Cost, ExpiryDate). Return CustomerID, SoftwareID, LicenceType, Cost and ExpiryDate for all licences that expire on or before 31/12/2019. Group the output by CustomerID, in ascending order of cost.

SELECT CustomerID, SoftwareID, LicenceType, Cost, ExpiryDate FROM LICENCE WHERE ExpiryDate <= '31/12/2019' GROUP BY CustomerID ORDER BY Cost ASC;

Watch: “on or before” → <= (not <). ASC = ascending (smallest first) · DESC = descending. The board wrote the table as “LICENSE” — the question's spelling is LICENCE.

④ DELETE — remove records

DELETE FROM <table name>
WHERE <condition>;

Table Customers: (1) Alfreds Futterkiste · Maria Anders · Berlin · Germany; (2) Ana Trujillo Emparedados y helados · México D.F.; (3) Antonio Moreno Taquería · México D.F.; (4) Around the Horn · Thomas Hardy · London · UK; (5) Berglunds snabbköp · Christina Berglund · Luleå · Sweden.

Task: delete the information related to “Alfreds Futterkiste”

DELETE FROM CustomersWHERE CustomerName = "Alfreds Futterkiste";

Common mistake: DELETE without WHERE removes all records of the table.

⑤ Aggregate functions — COUNT( ) · AVG( ) · SUM( )

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

SELECT AVG(column_name)
FROM table_name
WHERE condition;

SELECT SUM(column_name)
FROM table_name
WHERE condition;

COUNT = how many · AVG = average · SUM = total. WHERE is optional (omit it to use all records).

Products table: (ProductID, ProductName, SupplierID, CategoryID, Unit, Price) → 1 Chais 18 · 2 Chang 19 · 3 Aniseed Syrup 10 · 4 Chef Anton's Cajun Seasoning 22 · 5 Chef Anton's Gumbo Mix 21.35

Number of products

SELECT COUNT(ProductID)FROM Products;

→ 5

Average price

SELECT AVG(Price)FROM Products;

→ (18+19+10+22+21.35)/5 = 18.07

Sum of all the prices

SELECT SUM(Price)FROM Products;

→ 18 + 19 + 10 + 22 + 21.35 = 90.35

9. INNER JOIN

Why join? data is split over linked tables (PK ↔ FK); a join brings it together in one query.

The INNER JOIN keyword selects records that have matching values in both tables.

INNER JOIN = records with matching values in BOTH tables

SELECT <column name>
FROM <table 1>
INNER JOIN <table 2>
ON table1.column_name = table2.column_name
WHERE <condition>;

Pattern: ON = the PK/FK pair that links the two tables. Prefix columns with TableName. when the same field name exists in both tables (e.g. FILM_ACTOR.FilmID).

FILMSINNER JOIN · [4] ✔ 4/4

ACTOR(ActorID, FirstName, LastName, DateOfBirth) · FILM_FACT(FilmID, FilmTitle, ReleaseDate, Category) · FILM_ACTOR(ActorID, FilmID). Return the IDs of all the actors in the film titled Cinderella.

The title is in FILM_FACT, the actor IDs are in FILM_ACTOR → link them on FilmID.

SELECT ActorID FROM FILM_ACTOR INNER JOIN FILM_FACT ON FILM_FACT.FilmID = FILM_ACTOR.FilmID WHERE FILM_FACT.FilmTitle = "Cinderella";

TECHNOLOGY (staff & devices)INNER JOIN + COUNT · [4]

STAFF(StaffID, FirstName, LastName, DateOfBirth, JobTitle) · DEVICE(DeviceID, Type, DatePurchased, StaffID) (StaffID circled in both = the link). Return the number of devices for staff member Ali Khan.

SELECT COUNT (STAFF.StaffID) FROM STAFF INNER JOIN DEVICE ON STAFF.StaffID = DEVICE.StaffID WHERE STAFF.FirstName = 'Ali' AND STAFF.LastName = 'Khan';

The four blanks to fill were: COUNT · STAFF · ON · AND.

Relationship diagram in MS Access: the Relationships window draws a line from the PK in one table to the FK in the other — e.g. PupilsTable(PupilID, FirstName, LastName, Gender, Town) — BookingTable(BookingID, PupilID, Activity) — AfterSchoolClubsTable(Activity, Cost). PupilID and Activity are the linking fields.

10. Referential Integrity

Q: What is meant by referential integrity? (≈ 3–4 marks)

Referential integrity is making sure tables do not try to reference data which does not exist ✓
A primary key cannot be deleted unless all dependent records are already deleted → cascading delete
A primary key cannot be updated unless all dependent records are already updated → cascading update / edit
Every foreign key value has a matching value in the corresponding primary key
The foreign key must be the same data type as the corresponding primary key

an order whose FK has no matching PK is blocked by referential integrity

Customer 1028 (Bano, 03/01/04, Islamabad) has order no. 75 (Shoes, 13/02/22) ✓. A second order (no. 76) with an ID that is not in Customer “cannot be referenced” — this is enforced by referential integrity.

Cascade idea: delete/change the PK in the parent table → the matching FK rows in the child table are deleted/changed automatically, so no “orphan” rows are left.

11. Entity–Relationship (ER) Diagrams

An ER diagram shows the relationships between tables. Four degrees of relationship:

crow's foot ( < ) = the MANY end; a single bar = the ONE end

Relationship	Example	Meaning
One to One	Employee — Office	1 employee ↔ 1 office
One to Many	Teacher —< Students	1 teacher → many students
Many to One	Students >— Teacher	many students → 1 teacher
Many to Many	Customers >—< Products	needs a link table

Rule of thumb: the crow's foot (many) end is always at the table that contains the foreign key. A many-to-many is split into two one-to-many relationships by a middle link table (e.g. MEMBER_SESSION, INSTRUCTOR_CAR) whose PK is the two FKs together.

A-NURSE / A-WARD · B-NURSE / B-WARDdescribe relationship · [1] ✔ ✔

A-NURSE / A-WARD = many-to-one · B-NURSE / B-WARD = many-to-many

(a) A-NURSE >— A-WARD: Many to One (crow's foot on the NURSE side).

(i) B-NURSE >—< B-WARD: degree of relationship = Many to Many.

CUSTOMER / LICENCEtype of relationship · [1] ✔

CUSTOMER(CustomerID, CompanyName); LICENCE(LicenceID, CustomerID, SoftwareID, …). One customer can hold many licences, each licence belongs to one customer → One to Many.

DRIVING_SCHOOLcomplete the ER diagram

INSTRUCTOR(InstructorID, FirstName, LastName, DateOfBirth, Level) · CAR(Registration, Make, Model, EngineSize) · INSTRUCTOR_CAR(InstructorID, Registration) · STUDENT(StudentID, FirstName, LastName, DateOfBirth, Address1) · LESSON(LessonID, StudentID, InstructorID, LessonDate, LessonTime)

Find each FK, then put the foot at the table holding it: INSTRUCTOR_CAR holds InstructorID & Registration; LESSON holds StudentID & InstructorID.

the MANY end always sits on the table that holds the foreign key (LESSON, INSTRUCTOR_CAR)

SPORTS_CLUBcomplete the E-R diagram · [2] ✔ 2/2

MEMBER(MemberID, FirstName, LastName, MembershipType) · SESSION(SessionID, Description, SessionDate, SessionTime, NumberMembers) · TRAINER(TrainerID, TrainerFirstName, TrainerLastName) · MEMBER_SESSION(MemberID, SessionID) · SESSION_TRAINER(SessionID, TrainerID)

two link tables (MEMBER_SESSION, SESSION_TRAINER) turn many-to-many into two one-to-many

USER · PHOTO · TEXTPOSTE-R diagram of 3 tables · [2] ✔ 2/2

USER(UserName, FirstName, SecondName, DateOfBirth) · PHOTO(PhotoID, UserName, Comment, UploadDate) · TEXTPOST(PostID, UserName, DateOfPost, TheText)

one USER → many TEXTPOSTs and many PHOTOs (UserName is the foreign key)

EMPLOYEESE-R diagram · [3]

An employee can be a manager · a department can have several managers and several employees · an employee belongs to only one department. EMPLOYEE_DATA(EmployeeID, FirstName, LastName, DateOfBirth, Gender, DepartmentNumber) · DEPARTMENT(DepartmentNumber, DepartmentName) · DEPARTMENT_MANAGER(DepartmentNumber, EmployeeID, role)

EMPLOYEE_DATA—DEPARTMENT_MANAGER: board drew a plain line; the crow's foot belongs on the DEPARTMENT_MANAGER end

Note: on the board EMPLOYEE_DATA >— DEPARTMENT and DEPARTMENT —< DEPARTMENT_MANAGER were drawn with feet; the third line (EMPLOYEE_DATA — DEPARTMENT_MANAGER) was left as a plain line. Since DEPARTMENT_MANAGER holds EmployeeID as a FK, its foot goes on the DEPARTMENT_MANAGER end.

12. Database Management System (DBMS)

a DBMS (e.g. Microsoft Access) bundles these tools around the database

Screenshot on board: MS Access with tables (tblDepartment, tblHumanResourcesData, tblParkingInfo, tblPersonalData) and queries listed on the left, and the table data (Id, First Name, Last Name, Address, City, State, Zip, Phone) on the right.

Q: Describe the DBMS tools

Developer interface

to create user-friendly features, e.g. forms to enter a new booking
to create outputs, e.g. report of bookings on a given date
to create interactive features, e.g. buttons or menus

Query processor

to create SQL queries
to search for data that meets set criteria, e.g. all bookings for next week
to perform calculations on extracted data, e.g. number of empty rooms
organises the results to be displayed

Q: The DBMS has a data dictionary — describe what it stores

Stores all the information about the database
e.g. fields, data types, keys

Tasks performed by the DBMS developer interface

Create a table
Set up relationships between tables
Create a form
Create a report
Create a query

Q: How is DBMS software used to ensure the security of the data?

Method	Why / how
Issue usernames and passwords	stops unauthorised access to the data · a strong password should be used
Access rights	only certain usernames can read certain parts of the data · read-only or full access · e.g. only the Finance department can edit/read finance data
Create regular backups	if live data is lost/damaged a copy is available · e.g. backup at the end of each day
Encryption of data	if there is unauthorised access, the data cannot be understood (e.g. … — left blank on the board)

13. Normalisation

Normalisation “is a method to remove or reduce the repetition of the data (redundancy)”.

three stages — each one includes the rules of the one before

① 1NF — First Normal Form

No repeated group of attributes
All attributes should be atomic — an attribute that cannot be decomposed into meaningful components
No duplicate rows

Not 1NF ✗
FirstName | Courses
Taha | Math
Taha | English
(Taha repeats; courses are a repeating group → split into separate tables)

② 2NF — Second Normal Form

Must already be in 1NF ✓
No partial dependency ✓ — every non-key attribute must depend on the whole key, not on part of it

Example from board

Student table(student_id, name, reg_no, branch, address) — all attributes depend directly on student_id (“direct dependency”) ✓
Subject table(subject_id, subject_name) ✓
Score table(score_id, student_id, subject_id, marks, teacher) — compound key = student_id + subject_id, but teacher is only dependent on subject_id ✗ → partial dependency.

depends on part of the key only ⇒ not 2NF (move teacher to the Subject table)

③ 3NF — Third Normal Form

Should be in 1NF and 2NF
No non-key dependency (a non-key attribute depending on another non-key attribute)
No transitive dependency

One-line memory: every non-key attribute must depend on “the key, the whole key, and nothing but the key.”

SOFTWARE_MANAGEMENT — not in 3NFexplain · refer to the tables

CUSTOMER_DETAILS(CustomerID, CompanyName, Address1, Address2, City) · SOFTWARE_PURCHASED(SoftwareName, SoftwareDescription, CustomerID, LicenceType, LicenceCost, RenewalDate) — compound key = SoftwareName + CustomerID.

Explain why this database is NOT in 3NF:

There are partial dependencies in the SOFTWARE_PURCHASED table: SoftwareDescription depends only on SoftwareName and does not depend on both parts of the key.
There is a non-key dependency in SOFTWARE_PURCHASED: LicenceCost depends on LicenceType and does not depend on the primary key.

14. Levels of Schema of a Database

top (what the user sees) → bottom (how it is physically stored)

Schema level	Description (board)
External schema	The individual's view of the database
Conceptual schema	Describes the views which users of the database might have
Logical schema	Describes how the relationships will be implemented in the logic structure of the database
Physical / Internal schema	Describes how the data will be stored on the physical media

Memory: external (what ONE user sees) → conceptual → logical (tables & relationships) → physical (bits on disk). The board's wording for the conceptual level is kept as written.

15. Quick Recap — Full Chapter Cheat-Sheet

✓ Theory in one box

File-based limits: redundancy · dependency · lack of integrity · lack of privacy
Relational benefits: reduced redundancy/dependency · better integrity/privacy · program–data independence · ad hoc & complex queries · access rights/views
Terms: entity = table/object · attribute = field/column · record = tuple = row · PK unique ID · FK = PK of another table (makes the relationship) · candidate keys → 1 primary + rest secondary
Ref. integrity: no reference to non-existent data · cascade delete/update · FK has matching PK, same data type
Security: usernames/passwords · access rights · backups · encryption
DBMS tools: developer interface · query processor · data dictionary
Normalisation: 1NF atomic/no repeats · 2NF no partial dependency · 3NF no non-key/transitive dependency
Schema: external · conceptual · logical · physical/internal

SQL cheat-sheet CREATE DATABASE name;
CREATE TABLE name ( attr type, …, PRIMARY KEY (attr) NOT NULL );
ALTER TABLE name ADD field type;
INSERT INTO name VALUES (v1, v2, …);
UPDATE name SET col = val WHERE cond;
SELECT cols FROM name [INNER JOIN t2 ON t1.col = t2.col] WHERE cond [GROUP BY col] [ORDER BY col ASC|DESC];
DELETE FROM name WHERE cond;
SELECT COUNT(col) | AVG(col) | SUM(col) FROM name;

Exam routine for SQL: ① underline table names, field names & conditions in the question → ② write clause by clause (SELECT → FROM → WHERE) → ③ text in quotes, <= for “on or before”, AND for 2 conditions → ④ more than one table? add the PK = FK join → ⑤ finish with ;

Databases

1. Data Storage — Two Approaches

2. Limitations of the File-Based Approach

Q: What are the limitations of using a file-based approach?

3. Benefits of a Relational Database

Q: Benefits of a relational database instead of a flat file?

Q: Describe the features of a relational database that address the limitations of a file-based approach

4. Database Terms

The Student table — spot every term

5. Keys — Primary, Foreign, Candidate

① Primary key (PK)

② Foreign key (FK)

③ Candidate key & secondary key

6. Past-Paper Practice — Terms & Keys

Describe the role of a primary and a foreign key in a database relationship

7. Structured Query Language (SQL) — DDL

① DDL — create a database

② DDL — create a table

③ DDL — add a new field

8. DML — Manipulating the Data

① INSERT — add data into a table

② UPDATE — change data / a record

③ SELECT — display / return values

④ DELETE — remove records

⑤ Aggregate functions — COUNT( ) · AVG( ) · SUM( )

9. INNER JOIN

10. Referential Integrity

Q: What is meant by referential integrity? (≈ 3–4 marks)

Related theory questions

11. Entity–Relationship (ER) Diagrams

12. Database Management System (DBMS)

Q: Describe the DBMS tools

Developer interface

Query processor

Q: The DBMS has a data dictionary — describe what it stores

Tasks performed by the DBMS developer interface

Q: How is DBMS software used to ensure the security of the data?

13. Normalisation

① 1NF — First Normal Form

② 2NF — Second Normal Form

③ 3NF — Third Normal Form

14. Levels of Schema of a Database

15. Quick Recap — Full Chapter Cheat-Sheet