Exam practice

• Database: an organized collection of data kept so it can be searched, changed and shared. Example: the school’s data on students, classes, subjects and marks.
• DBMS (database management system): the program that stores, finds, changes and checks the data in a database. Example: SQLite, the DBMS in this unit’s playgrounds (more are named in the Today box at the start of the unit).
• Record: one row of a table, everything about one thing. Example: student 139, Sita Shrestha, 10B, born 2011-05-13, Bhaktapur.
• Field: one column of a table, one fact about every record. Example: the town field.
• Primary key: a field (or fields) whose value is unique, never empty and stable, identifying each record. Example: student_id.
• Foreign key: a field holding another table’s primary key, linking the tables. Example: class_id in students, pointing to a row of classes.

Problems (any three):

• Redundant data: the patient’s details and the doctor’s phone are written again for every visit, and the copies already disagree (“Ward 3, Banepa” and “Wd 3 Banepa”).
• Age instead of date of birth: the age is wrong a year later.
• The address is one field: finding all patients from Banepa means searching inside text, with different spellings.
• No patient key: two patients called Hari Thapa couldn’t be told apart.

Better tables:

• patients (patient_id primary key, name, date_of_birth DATE, ward INTEGER, municipality TEXT, phone TEXT)
• doctors (doctor_id primary key, name, phone TEXT)
• visits (visit_id primary key, patient_id → patients, doctor_id → doctors, visit_date DATE, notes)

Each patient and doctor is written once; a visit points to them with foreign keys. One patient has many visits, and one doctor has many visits: both one-to-many.

i. SELECT name FROM students WHERE class_id = '9B' ORDER BY name;

ii. SELECT town, COUNT(*) FROM students GROUP BY town;

iii. SELECT MAX(mark) FROM marks WHERE subject_id = 'NEP';

iv.

SELECT students.name, marks.markFROM marksJOIN students ON marks.student_id = students.student_idWHERE marks.subject_id = 'SCI' AND marks.mark < 40;

v.

SELECT students.class_id, AVG(marks.mark)FROM studentsJOIN marks ON marks.student_id = students.student_idGROUP BY students.class_id;

(You can run each in the playgrounds above to check it. The answers: iii is 98; iv is Sita Shrestha with 18 and Yamuna Dangol with 36; v gives 10A 71.5, 10B 69.5, 9A 59.4 and 9B 66.6, rounded.)

WHERE picks rows before they are grouped, by conditions on single rows. HAVING picks groups after GROUP BY, by conditions on totals such as COUNT or AVG. WHERE can’t use aggregate functions, because no groups exist yet.

Example, the subjects whose average mark among Grade 10 students is over 70:

SELECT marks.subject_id, AVG(marks.mark)FROM marksJOIN students ON marks.student_id = students.student_idWHERE students.class_id IN ('10A', '10B')GROUP BY marks.subject_idHAVING AVG(marks.mark) > 70;

WHERE keeps only Grade 10 students’ rows; HAVING keeps only the subjects whose average is over 70.

Relationships: none in this one table; if the shop also kept a customers table, mobile could become a foreign key to it.
A rule a constraint can’t check: that the mobile number really belongs to the network chosen, or that it is the customer’s own number and typed correctly. That needs verification: reading the number back to the customer before the recharge is sent.

• The form (a number box that accepts only 0 to 100): catches the mistake while the teacher is typing and can fix it at once, which is the friendliest. But marks loaded from a spreadsheet file, or saved by another program, don’t pass through the form.
• The program that saves the marks: can give a clear message and check rules too complicated for the database, but another program writing to the same database would skip it.
• A CHECK constraint in the database: nothing gets in without passing it, whatever sends the data, so it is the last line of defense. Its error message is less friendly, and it only knows the rules written into it.
• A query afterwards (SELECT * FROM marks WHERE mark < 0 OR mark > 100;): finds bad data already in, such as data loaded before the rule existed, but stops nothing; someone has to fix what it finds.

I would use the form and the database constraint: the form so teachers fix their mistakes as they type, the constraint so no wrong value can ever be saved, whatever happens. A query from time to time checks data from older years. None of them catches a possible but wrong mark (67 for 76), so I would also verify: each teacher reads the saved marks back against the answer sheets.

The symbol number is the primary key, and a database keeps its primary key in order, as an index (a B-tree), so it can go to the right record in a few steps, halving its search each time, instead of reading every row. 500,000 is about 2¹⁹, so a binary search needs only about 19 halvings, and a B-tree with hundreds of values in each node only three levels.
Searching by name with no index, the database would have to do a full table scan: read all 500,000 rows for every search. That takes far longer, and on results day, with thousands of searches a second, the website would slow down or stop. An index on the name would fix this, at the cost of some space and slightly slower inserts.

Names aren’t unique: the school has two students called Sita Shrestha (108 in 9A and 139 in 10B). Grouping by name would put both Sitas’ marks into one group and add all 12 of them together, giving one wrong total, 633. Grouping by the primary key makes one group per student, however many share a name.