Defining a single-table database from storage requirements

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. A library wants to store each book's title, author and whether it is on loan. Which field list is the best start for the table?

A BookID, Title, Author, OnLoan

B Books, Library, Shelf, Reading

2. A pet shop table has the field Price (e.g. 12.50). Which data type fits best?

A Boolean

B Real (floating point)

3. A school table stores whether a student has paid for a trip (yes or no). Which data type should the field Paid use?

A Boolean

B Integer

4. Which field is the best primary key for a table of school students?

A StudentID, because every student has a different one

B LastName, because every student has one

5. A pet shop table has the field DateOfBirth. Which data type should it have?

A Text

B Date/time

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1BookID \| Text \| Primary key		AA field called DateDue stores a date and a presence check makes sure it is never left empty
2DateDue \| Date/Time \| Presence check		BA pet's name is made of letters, so it should be Text, not Integer. It is also a poor primary key because two pets can share the same name. A field such as PetID would be better.
3A pet shop table needs a field to store whether each animal has been vaccinated, which only has two possible values		CVaccinated \| Boolean
4A school table stores each student's height in metres, such as 1.62, so the field needs a data type that allows decimal places		DA field called BookID holds text and is the primary key, so every book in the library has its own unique ID
5PetName \| Integer \| Primary key		EThe table structure has no primary key. A field such as BookID with a unique value for each record should be added and marked as the primary key.
6Fields: Title, Author (no primary key)		FHeight \| Real

Definitions

table structure

Fill in the Blanks

Complete each line of code by filling in the missing parts.

Table: Pet PetID | _______ | Primary key Name | Text | Presence check

Table: Book Title | Text | Presence check Price | ____ | Range check 0 to 100

Table: Student StudentID | Text | ___________ Name | Text | Presence check

Table: Pet Vaccinated | _______ | Yes or No PetID | Integer | Primary key

Table: School YearGroup | Integer | _____ check 7 to 13 StudentID | Text | Primary key

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A Table: Pet (4 fields)

B PetID | Integer | Primary key

C Name | Text

D Born | Date

Question 2

A Table: Book (3 fields)

B BookID | Boolean | Primary key

C Title | Text

D OnLoan | Boolean

Question 3

A Table: Student (4 fields)

B ID | Integer | Primary key

C Name | Text

D Year | Text | Range 7-13

E Paid | Boolean

Question 4

A Table: Stock (4 fields)

B ItemCode | Text | Primary key

C Price | Integer | Range 0-500

D Quantity | Integer

E Name | Text

Question 5

A Need: ID, name, age

B ID | Integer | Primary key

C Name | Text

D Joined | Date

Answer Key

Defining a single-table database from storage requirements

Section A: Quick Questions

A — BookID, Title, Author, OnLoan

B — Real (floating point)

A — Boolean

A — StudentID, because every student has a different one

B — Date/time

Section B: Match the Pairs

BookID | Text | Primary key → A field called BookID holds text and is the primary key, so every book in the library has its own unique ID

DateDue | Date/Time | Presence check → A field called DateDue stores a date and a presence check makes sure it is never left empty

A pet shop table needs a field to store whether each animal has been vaccinated, which only has two possible values → Vaccinated | Boolean

A school table stores each student's height in metres, such as 1.62, so the field needs a data type that allows decimal places → Height | Real

PetName | Integer | Primary key → A pet's name is made of letters, so it should be Text, not Integer. It is also a poor primary key because two pets can share the same name. A field such as PetID would be better.

Fields: Title, Author (no primary key) → The table structure has no primary key. A field such as BookID with a unique value for each record should be added and marked as the primary key.

Section C: Definitions

table structure — The plan for one table: list each field the scenario needs, give it a data type and any validation, and choose a primary key so the table can hold every record.

Section D: Fill in the Blanks

Table: Pet
PetID | Integer | Primary key
Name | Text | Presence check

Table: Book
Title | Text | Presence check
Price | Real | Range check 0 to 100

Table: Student
StudentID | Text | Primary key
Name | Text | Presence check

Table: Pet
Vaccinated | Boolean | Yes or No
PetID | Integer | Primary key

Table: School
YearGroup | Integer | Range check 7 to 13
StudentID | Text | Primary key

Section E: Spot the Error

A — The table lists only three fields: PetID, Name and Born. The header should say (3 fields) so it matches the field lines.

B — A Boolean can only hold two values (True/False), so it cannot give each book a unique ID. BookID should be Integer (or Text).

D — A range check compares numbers, so the Year field must be Integer, not Text. The line should be Year | Integer | Range 7-13.

C — Prices have pence (for example 4.99), so they need a Real (floating point) type, not Integer. It should be Price | Real | Range 0-500.

A — The scenario line should list the same three fields the table stores: ID, name and the date joined. The Joined field would otherwise have no requirement behind it and the table would be wrong.