Field data types in a database table
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. A field called Age in a student table should store whole numbers like 15. Which data type fits best?
2. Which statement about a data type on a field is true?
3. A field called BirthDate should store 2009-03-14. Which data type is the best choice?
4. A field called HasPermissionSlip only needs to record yes or no. Which data type suits it?
5. Which field is the better fit for the Text data type?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1age INTEGER | APhone numbers often start with 0 and are not used for maths, so INTEGER would drop the leading 0. TEXT is the better type. | |
| 2birthdate DATE | Bname TEXT | |
| 3A field that stores words and letters, such as a student's name, should use a text type. | CA price is a number you may add up or compare, so it should use a number type such as DECIMAL, not TEXT. | |
| 4A yes/no field, such as whether a student has paid an excursion fee, should use a boolean type. | DThe age field only accepts whole numbers, so a value like 'fifteen' or 15.5 is rejected. | |
| 5phone INTEGER | EThe birthdate field only accepts a real calendar date, so '31/02/2010' or 'banana' is rejected. | |
| 6price TEXT | Fpaid BOOLEAN |
C
Definitions
data type
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
CREATE TABLE Pet (
name ____, age INTEGER
);
2.
CREATE TABLE Pet (
name TEXT, age _______
);
3.
CREATE TABLE Member (
birthday ____, name TEXT
);
4.
CREATE TABLE Member (
paid _______, name TEXT
);
5.
CREATE TABLE Contact (
phone ____, name TEXT
);
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
CREATE TABLE Student (
B
Name TEXT,
C
Age TEXT,
D
Enrolled BOOLEAN
E
);
Question 2
A
CREATE TABEL Event (
B
Title TEXT,
C
EventDate DATE,
D
Cost DECIMAL(5,2)
E
);
Question 3
A
CREATE TABLE Pet (
B
Name INTEGER,
C
Age INTEGER,
D
Vaccinated BOOLEAN
E
);
Question 4
A
CREATE TABLE Book (
B
Title TEXT,
C
Pages INTEGER,
D
Borrowed DATE
E
);
Question 5
A
INSERT INTO Pet (Name, Age)
B
VALUES ('Milo', 'three');
C
-- Age is an INTEGER field
Answer Key
Field data types in a database table
Section A: Quick Questions
Q1
A — Integer
Q2
B — It decides what kind of value the field accepts
Q3
A — Date
Q4
A — Boolean (yes/no)
Q5
B — LastName, because it stores letters like 'Nguyen'
Section B: Match the Pairs
Q1
age INTEGER → The age field only accepts whole numbers, so a value like 'fifteen' or 15.5 is rejected.
Q2
birthdate DATE → The birthdate field only accepts a real calendar date, so '31/02/2010' or 'banana' is rejected.
Q3
A field that stores words and letters, such as a student's name, should use a text type. → name TEXT
Q4
A yes/no field, such as whether a student has paid an excursion fee, should use a boolean type. → paid BOOLEAN
Q5
phone INTEGER → Phone numbers often start with 0 and are not used for maths, so INTEGER would drop the leading 0. TEXT is the better type.
Q6
price TEXT → A price is a number you may add up or compare, so it should use a number type such as DECIMAL, not TEXT.
Section C: Definitions
Q1
data type — A setting on a database field that decides what kind of value it accepts, such as text, integer, date or yes/no, so wrong entries are rejected. It is a first form of validation.Section D: Fill in the Blanks
Q1
CREATE TABLE Pet (
name TEXT, age INTEGER
);Q2
CREATE TABLE Pet (
name TEXT, age INTEGER
);Q3
CREATE TABLE Member (
birthday DATE, name TEXT
);Q4
CREATE TABLE Member (
paid BOOLEAN, name TEXT
);Q5
CREATE TABLE Contact (
phone TEXT, name TEXT
);Section E: Spot the Error
Q1
C — Age is a whole number, so the field should be INTEGER. With TEXT, entries like 'abc' would be accepted and the validation is lost.
Q2
A — TABLE is misspelt as TABEL. The line should be CREATE TABLE Event ( so the data types for each field can be defined.
Q3
B — A pet's name is made of letters, so it needs the TEXT data type. INTEGER only accepts whole numbers and would reject names like Milo.
Q4
D — Borrowed is a yes/no value, so its type should be BOOLEAN. A DATE field would only accept dates, not true or false.
Q5
B — Age is an INTEGER field, so the value must be the number 3, not the text 'three'. The database would reject the text. It should be VALUES ('Milo', 3);