Designing a table structure

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. A student wants to store a person's name in a database table. Which design follows 'one fact per field'?

A Two fields: FirstName and LastName

B One field: FullName holding 'Mia Chen'

2. Which is the better field name for a table about school pets?

A PetName

B stuff

3. You are planning a field called Age that will store whole numbers like 12 and 13. Which data type should you choose?

A Number (integer)

B Date

4. A design table lists each field with its data type. Which row is correct for a field storing a student's birthday?

A DateOfBirth | Yes/No

B DateOfBirth | Date

5. Which field design breaks the rule 'one fact per field'?

A Phone: '0412 345 678'

B Contact: 'Sam, 0412 345 678'

Match the Pairs

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

Left column		Right column
1FirstName (Text), LastName (Text)		ATwo facts are crammed into one field. First name and last name should be separate fields, FirstName and LastName.
2Age (Number)		BField: BirthDate, Type: Date
3Each field should hold only one fact, so a full name is split into two fields		CTwo separate fields, each holding one fact about a person
4A design table lists the field name and the data type for each field		DFirstName \| LastName
5Name (Text) = "Mia Chen"		EA field named Age that only stores numbers
6Field: Height, Type: Text		FThe data type does not suit the data. Height is measured, so its type should be Number, not Text.

Definitions

table design

Fill in the Blanks

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

Field: FirstName, Type: ____

Field: ________, Type: Text

Field: Age, Type: ______

Field: BirthDate, Type: ____

Table: Students Field: FirstName, Type: Text Field: Score, ____: Number

Spot the Error

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

Question 1

A Table: Students

B FirstName and LastName - Text

C LastName - Text

D Age - Number

Question 2

A Table: Pets

B PetID - Number

C Name - Text

D Age - Text

Question 3

A Title, Author - Text

B Author - Text

C Pages - Number

Question 4

A Table: Games

B Score - Yes/No

C Played - Date

D Won - Yes/No

Question 5

A Table: Movies

B Title - Text

C Rating - Number

D Stuff - Date

Answer Key

Designing a table structure

Section A: Quick Questions

A — Two fields: FirstName and LastName

A — PetName

A — Number (integer)

B — DateOfBirth | Date

B — Contact: 'Sam, 0412 345 678'

Section B: Match the Pairs

FirstName (Text), LastName (Text) → Two separate fields, each holding one fact about a person

Age (Number) → A field named Age that only stores numbers

Each field should hold only one fact, so a full name is split into two fields → FirstName | LastName

A design table lists the field name and the data type for each field → Field: BirthDate, Type: Date

Name (Text) = "Mia Chen" → Two facts are crammed into one field. First name and last name should be separate fields, FirstName and LastName.

Field: Height, Type: Text → The data type does not suit the data. Height is measured, so its type should be Number, not Text.

Section C: Definitions

table design — Planning a table by listing a clear field name and data type for each piece of data, with one fact per field, such as FirstName and LastName as two fields.

Section D: Fill in the Blanks

Field: FirstName, Type: Text

Field: LastName, Type: Text

Field: Age, Type: Number

Field: BirthDate, Type: Date

Table: Students
Field: FirstName, Type: Text
Field: Score, Type: Number

Section E: Spot the Error

B — This field holds two facts (first name and last name). Table design uses one fact per field, so it should be split into FirstName - Text and LastName - Text.

D — Age is a number you may want to compare or calculate with, so its data type should be Number, not Text.

A — This field squashes two facts, the title and the author, into one. Each fact needs its own field: Title - Text and Author - Text.

B — A score is a count that can be added up or compared, so it needs the Number type. Yes/No only suits true or false facts like Won.

D — The field name is vague. A clear field name such as ReleaseDate tells everyone exactly what fact is stored.