Studyledge

Databases and SQL: Question 7

Syllabus 9.1, 9.2, 9.3

Structured 7 marks

Sunnyvale Tutoring Centre books private lessons and stores details of each one in a single database table called LESSON. A sample of the records it plans to store is shown below. SubjectCode always stores a two-letter code, such as MA for Mathematics or EN for English.

LessonID StudentName SubjectCode DurationMins Attended
L01 Kofi Mensah MA 45 True
L02 Ines Duarte EN 60 True
L03 Kofi Mensah SC 30 False
L04 Ravi Desai MA 45 True

(a) Using the data shown, identify the most suitable field to use as the primary key for the LESSON table. Justify your choice by referring to the table. [2]

(b) State the most suitable basic data type for each of these fields: (i) SubjectCode (ii) Attended [2]

(c) The centre wants any new lesson record to be rejected unless the value entered for SubjectCode is exactly two characters long. Identify a suitable validation check for this rule, and describe how it would be applied to a value entered for SubjectCode. [3]

Show worked solution Hide worked solution

Worked solution

Part (a): Choosing the primary key

A primary key must hold a value that is guaranteed to be different for every record. Looking at the sample data:

  • StudentName repeats: Kofi Mensah is used for both L01 and L03.
  • SubjectCode repeats: MA is used for both L01 and L04.
  • DurationMins repeats too: 45 is shared by L01 and L04.
  • LessonID is different for every record shown (L01, L02, L03, L04), and there is no reason two different lessons would ever be given the same ID.

So LessonID is the most suitable primary key.

Part (b): Choosing data types

(i) SubjectCode always stores two letters, such as MA, EN or SC. A character data type can only hold a single letter, so it is not suitable here. The correct data type is text (string), which can hold a sequence of more than one character.

(ii) Attended only ever needs to store one of two logical states (the student attended, or they did not) so the most suitable data type is Boolean.

Part (c): Validating SubjectCode

The rule “must be exactly two characters long” is enforced with a length check. When a new value is entered for SubjectCode, the system counts how many characters it contains:

  • If the value entered has fewer than two characters, it fails the check.
  • If the value entered has more than two characters, it also fails the check.
  • Only a value with exactly two characters passes, and any value of a different length is rejected, with the user asked to enter the value again.

Final answers

  • (a) Primary key = LessonID (StudentName, SubjectCode and DurationMins all contain repeated values; LessonID does not)
  • (b) (i) SubjectCode = Text (string); (ii) Attended = Boolean
  • (c) Length check: reject any value entered for SubjectCode that does not contain exactly 2 characters