Study Guide Instructor view

Designing a Database Table

Teaching notes for Lesson 6.1, with controls for presenting it.

Keep this page on your own screen. Share only the lesson window. The lesson window holds no teaching notes, so sharing it cannot show them.

Presenting

  1. Open the lesson in its own window and share that window, not this one.
  2. Use Next and Previous here, or the lesson’s own, to move through the lesson. Next reveals one thing at a time: the next idea, or the answer to the question on screen, then the next step. Previous takes the last reveal back. The line below says what is on screen and what Next will show.
  3. Reveal hints and answers when the class is ready, and reset a demonstration before using it again.

The controls need JavaScript and a browser that supports BroadcastChannel. Without them, open the lesson in presentation mode and use its own controls.

The two windows talk to each other only inside this browser, on this device. Nothing is sent to a server, and no account is needed. A second device (a phone or another tablet) cannot follow the lesson window yet.

Lesson plan

Level
Intermediate
Time
About 65 minutes; the steps below add up to 65

Learners start from a paper register they can picture, discover that a grid with one row per swimmer and one column per detail is a table, and meet the vocabulary only after they have described it themselves. They then choose data types by asking what kind of value a field holds, find why a name cannot identify a record, and define a whole table from storage requirements with validation. The lesson ends with learners designing a table of their own.

Objectives

  • Explain what a database, a table, a record and a field are, and count them in a given table
  • Choose a suitable data type for a field: text/alphanumeric, character, Boolean, integer, real or date/time
  • Explain why a phone number is stored as text and not as a number
  • Explain the purpose of a primary key and choose a suitable one for a given table
  • Define a single-table database from a set of storage requirements, with a data type and a validation check for each field

Before the lesson

  • Mini whiteboards or paper for the first think question and for the design task at the end.
  • Optional: a printed copy of the swimmers’ table so learners can circle a record and a field.
  • A reminder of the programming data types (integer, real, string, char, Boolean) for learners who have not met them.

Steps

  1. Introduction Opening

    On screen: the lesson’s title, module and number; Next adds its objectives and key terms, as the lesson page lists them; Next again begins the first step.

    Objectives on screen

    • Explain what a database, a table, a record and a field are, and count them in a given table
    • Choose a suitable data type for a field: text/alphanumeric, character, Boolean, integer, real or date/time
    • Explain why a phone number is stored as text and not as a number
    • Explain the purpose of a primary key and choose a suitable one for a given table
    • Define a single-table database from a set of storage requirements, with a data type and a validation check for each field

    Purpose: Learners start from a paper register they can picture, discover that a grid with one row per swimmer and one column per detail is a table, and meet the vocabulary only after they have described it themselves. They then choose data types by asking what kind of value a field holds, find why a name cannot identify a record, and define a whole table from storage requirements with validation. The lesson ends with learners designing a table of their own.

  2. 1. A register on paper Think 5 min

    Purpose: Start from a problem learners can picture: finding who has not paid in a paper register. The grid idea should come from them.

    Ask

    • What would the coach need to record about each swimmer?
    • How could every swimmer be described in the same way?

    Responses to expect

    • Name, year group, time, date joined, whether paid
    • Rows for swimmers, columns for details

    Misconceptions to challenge

    • A learner may believe: “A free-form note for each swimmer is enough.” Respond: Ask how the coach would find all unpaid swimmers quickly. Uniform details make that possible.

    On the board or with the pen

    • Draw an empty grid and add the learners’ details as column headings.

    Checking understanding: Each learner writes their list of details on a whiteboard and compares with a partner.

    Depending on the answers

    • If learners list only names: ask what the coach needs to find out on a Friday, and add those details

    In the lesson window

    • Think it through: The coach wants to replace the register with something a computer can search in seconds. What details would she need to record about each swimmer, and how could she lay them out so that every swimmer is described in the same way?
  3. 2. Rows and columns Discover 8 min

    Purpose: Learners describe rows and columns in their own words before the terms table, record and field are given.

    Ask

    • What does one row describe? One column?
    • How many records and fields are there?

    Responses to expect

    • A row is one swimmer; a column is one kind of detail
    • 5 records, 7 fields

    Misconceptions to challenge

    • A learner may believe: “The top row of field names is a record.” Respond: The top row describes the columns. Count only rows that describe a swimmer.
    • A learner may believe: “A record is a column.” Respond: Ask which one describes one swimmer.

    On the board or with the pen

    • Circle one row in one colour and one column in another and label them.

    Checking understanding: The counting check separates records from fields.

    Depending on the answers

    • If many swap records and fields: ask learners to point at a record with a finger and at a field with a hand, then recount

    In the lesson window

    • Think it through: What does one whole row of the grid describe? And what does one whole column describe?
    • Check your understanding: How many records and how many fields does the swimmers’ table hold?
  4. 3. What kind of value does a field hold? Predict 8 min

    Purpose: Learners predict what goes wrong with a whole-number type for a time, then meet the six data types as answers to a real problem.

    Ask

    • What goes wrong if the time is a whole number?
    • Which type fits House, and why?

    Responses to expect

    • The fractional part is lost
    • Character: always exactly one character

    Misconceptions to challenge

    • A learner may believe: “Any letter or word is a string, so character is never needed.” Respond: Character is more precise when every value is exactly one character.

    Checking understanding: The House question checks that learners match a type to the values in the field.

    Depending on the answers

    • If learners choose integer for a letter field: ask what arithmetic they could do on a house

    Support

    • Give a card for each of the six types and have learners place the example values next to them.

    In the lesson window

    • Think it through: Suppose the coach chose a data type that can hold only whole numbers for Best50m. What would go wrong when she typed in 41.3?
    • Check your understanding: The House field holds a single letter: R, B or G. Which data type is the best fit?
  5. 4. A number that is not a number Discover 7 min

    Purpose: Challenge the belief that anything made of digits is a number, using a phone number with a leading zero.

    Ask

    • What happens to the 0 at the front of 07700 900123 if it is stored as a whole number?
    • Would you ever add two phone numbers together?

    Responses to expect

    • The 0 is lost
    • No, so it is text

    Misconceptions to challenge

    • A learner may believe: “Digits mean integer.” Respond: Ask whether the value is a quantity to calculate with. Phone numbers, postcodes and house numbers are labels.
    • A learner may believe: “A long number is real.” Respond: Real numbers have a fractional part; a phone number is not a measurement.

    Checking understanding: The bus-route table shows who can choose all six types unaided.

    Depending on the answers

    • If several confuse seats and price: ask which can be half and which has pence

    Stretch

    • Which type would you give a postcode, and why?

    In the lesson window

    • Apply it: Which data type should the ParentPhone field have, and why?
  6. 5. Telling records apart Discover 8 min

    Purpose: Learners discover the need for a primary key from two swimmers with the same name, then judge which field could be one.

    Ask

    • How could the computer find the right Sam Khan?
    • Why is a name not a good key?

    Responses to expect

    • Give each swimmer a number that nobody else has
    • Two people can share a name

    Misconceptions to challenge

    • A learner may believe: “A date is unique enough.” Respond: Several records can share a date, and a new member could join on the same day.
    • A learner may believe: “The most useful field is the key.” Respond: A key identifies exactly one record; usefulness is not the test.

    On the board or with the pen

    • Write the three qualities: unique, always present, stable.

    Checking understanding: The two key questions test the idea in two different tables.

    Depending on the answers

    • If learners choose DateJoined: show two records with the same date and ask which would be found

    In the lesson window

    • Think it through: Two swimmers in the table are called Sam Khan. How could the computer, or the coach, find the right record without any doubt? What would you add to the table?
    • Check your understanding: Which field of the swimmers’ table is the most suitable primary key?
    • Apply it: A school’s lost-property office stores each found item in a table with the fields ItemNumber, Description, Colour and DateFound. Which field should be the primary key?
  7. 6. Defining a table from storage requirements Explain 9 min

    Purpose: Show the reasoning for defining a table from storage requirements, then let learners do it for a new context.

    Ask

    • Why does each field need a data type?
    • What makes ItemNumber a suitable key?

    Responses to expect

    • So the computer can store, sort and compare it
    • It is different for every item

    Misconceptions to challenge

    • A learner may believe: “Data types are chosen by what the value looks like.” Respond: Choose by what kind of value it is and whether it is calculated with.

    Checking understanding: The music-room question asks for five data types in order.

    Depending on the answers

    • If learners call a date text: ask how the computer could sort text dates into order

    Support

    • Provide a sentence frame: ‘This field holds …, so its data type is …’.

    In the lesson window

    • Apply it: A music department (fictional) wants a table of the instruments it lends to pupils. It must record an instrument number (a whole number), the name of the instrument, its condition as one letter (G for good, F for fair, R for repair), the date it was bought and its value in pounds and pence. Which data types fit those fields, in the order they are listed?
  8. 7. Stopping unreasonable data Experiment 10 min

    Purpose: Learners suggest what a computer could do about an impossible year group, then meet validation and four checks, and its limit.

    Ask

    • What could the computer do with a year group of 79?
    • Why does a range check not catch a year group of 8 for a Year 9 pupil?

    Responses to expect

    • Refuse it and ask again
    • Validation tests whether data is allowed, not whether it is true

    Misconceptions to challenge

    • A learner may believe: “Validated data must be correct.” Respond: A plausible mistake passes validation. Only checking against the source catches it.
    • A learner may believe: “A range check works on text lengths.” Respond: A length check counts characters; a range check compares values.

    Checking understanding: The check-matching table and the two application questions.

    Depending on the answers

    • If learners confuse length and range: ask whether the rule counts characters or compares sizes

    In the lesson window

    • Think it through: The club only takes pupils in Years 7 to 11. What could the computer do when the coach types 79 as a year group? What could it do if she leaves a name blank?
    • Apply it: The coach types Sam Khan’s year group as 8, but Sam is really in Year 9. The range check for 7 to 11 is in place. What happens?
    • Apply it: A new field, LockerCode, must always be exactly four characters long. Which check refuses “27”?
  9. 8. Design one yourself Consolidate 10 min

    Purpose: Learners explain the primary key in their own words and design a table for a school canteen.

    Ask

    • Why does a table need a primary key?
    • Which field would you choose as the key, and which validation check would you use?

    Responses to expect

    • A key identifies exactly one record
    • An ID field; a range check for price, a presence check for name

    Misconceptions to challenge

    • A learner may believe: “Any unique-looking field is a key.” Respond: It must be unique for every record, always present and stable.

    Checking understanding: The design task shows data types, key and validation together.

    Depending on the answers

    • If learners use a name as a key: ask what happens with two meals of the same name

    Stretch

    • Add a second table for orders. What would you need to link the two?

    In the lesson window

    • Think it through: In your own words, explain why a database table needs a primary key, using an example that is not the swimming club.
    • Think it through: A school canteen (fictional) wants a table of the meals it sells. Write down the fields you would use, a data type for each one, which field is the primary key, and one validation check.
    • Apply it: In the canteen table, the field MealCode must always hold exactly three characters, such as V12. A user types V1. Which validation check refuses it?
  10. Lesson recap Closing

    On screen: the lesson’s objectives again; Next adds the closing question, which asks the class to explain each one in their own words without looking back. It says nothing about what anyone has learnt: use the answers to decide what to revisit.

    Points to listen for

    • A database stores data in tables: each row is a record and each column is a field.
    • Choose a data type by the kind of value and whether it is calculated with: a phone number is text.
    • A primary key is different for every record and usually an ID, never a name.
    • Validation refuses unreasonable data but cannot say whether data is correct.

Consolidation

  • A database stores data in tables: each row is a record and each column is a field.
  • Choose a data type by the kind of value and whether it is calculated with: a phone number is text.
  • A primary key is different for every record and usually an ID, never a name.
  • Validation refuses unreasonable data but cannot say whether data is correct.