Studyledge

Databases and SQL: Question 2

Syllabus 9.1, 9.2, 9.3

Structured 7 marks

Bramblewood Beekeeping Collective manages hives across two orchards. It plans to store details of each hive in a single database table called HIVE. A sample of the records it plans to store is shown below.

HiveID Location DateInstalled QueenPresent NumberOfFrames EstimatedHoneyKg
H01 Meadow Orchard 12 April 2024 Yes 10 18.5
H02 Meadow Orchard 12 April 2024 Yes 8 12.0
H03 Willow Field 1 May 2025 No 6 0.0
H04 Willow Field 15 June 2025 Yes 12 22.75

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

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

(c) The collective wants any new hive record to be rejected unless the value entered for NumberOfFrames is between 1 and 12 inclusive. Identify a suitable validation check for this rule, and describe how it would be applied to a value entered for NumberOfFrames. [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:

  • Location repeats: Meadow Orchard is used for both H01 and H02, and Willow Field is used for both H03 and H04.
  • DateInstalled repeats: H01 and H02 were both installed on 12 April 2024.
  • HiveID is different for every record shown (H01, H02, H03, H04), and there is no reason two different hives would ever be given the same ID.

So HiveID is the most suitable primary key.

Part (b): Choosing data types

(i) QueenPresent only ever needs to store one of two logical states (a queen is present, or she is not) so the most suitable data type is Boolean.

(ii) EstimatedHoneyKg stores values with a fractional part, such as 18.5 and 22.75, so it needs a real (decimal/floating-point) data type rather than integer, which can only store whole numbers.

Part (c): Validating NumberOfFrames

The rule “must be between 1 and 12 inclusive” is enforced with a range check. When a new value is entered for NumberOfFrames, the system compares it against the lower bound of 1 and the upper bound of 12:

  • If the value entered is less than 1, it fails the check.
  • If the value entered is more than 12, it also fails the check.
  • Only a value from 1 to 12 inclusive passes, and any value outside this range is rejected, with the user asked to enter the value again.

Final answers

  • (a) Primary key = HiveID (Location and DateInstalled both contain repeated values; HiveID does not)
  • (b) (i) QueenPresent = Boolean; (ii) EstimatedHoneyKg = Real
  • (c) Range check: reject any value entered for NumberOfFrames that is less than 1 or more than 12