GoalRead a CSV file as a table, choose a data type for each field and a primary key for the table, and see how more than one condition narrows a search.
New words
CSV file · a plain text file in which the values of each record are separated by commasdelimiter · a symbol, such as a comma, that separates one item of data from the nextdata type · the kind of value a field holds, for example whole number, decimal number, short text, yes/no or datecriterion · a condition a record must meet to be found by a search; the plural is criteria
Explanation
Most databases can import a table from a CSV file. Each line is one record, and the comma is the delimiter between the fields. The first line usually holds the field names, so the import has to be told that it is not a record. After importing, you set the data type for each field. Use a whole number for counts and ID numbers that are not used for sums, and a decimal or currency type for prices. Use short text for names. A phone number is also text, because you never add phone numbers together and some begin with 0 or +. Finally pick the primary key. A search with several criteria finds fewer records, because a record must meet all the conditions.
A CSV file of the stall’s items. The first line holds the field names Item_ID, Item, Price and In_stock. The six lines after it are six records. The comma separates the four fields in each line. Item_ID is chosen as the primary key.
Item_ID
Item
Price
In_stock
1
Bread
2.50
Yes
2
Olives
4.20
Yes
3
Honey
6.00
No
4
Cheese
5.50
Yes
5
Jam
3.00
Yes
6
Tea
1.80
No
The same file as a table, with the data types chosen. Item_ID is a whole number and the primary key. Item is short text. Price is a decimal (currency) number. In_stock is yes/no. In the table there are 6 records and 4 fields, and the field-name line is not counted as a record.
Search
Records found
Price > 4
Olives, Honey, Cheese (3)
Price > 4 and In_stock = Yes
Olives, Cheese (2)
More criteria, fewer records. Searching for Price greater than 4 finds 3 records: Olives (4.20), Honey (6.00) and Cheese (5.50). Adding a second criterion, In_stock is Yes, leaves only 2: Olives and Cheese, because Honey is not in stock. Every extra condition can only keep the same number of records or remove some.
Class activity
Guess the record (no computer). Make eight record cards for a pretend toy library, each with a toy number, a colour, a number of pieces and a yes/no for ‘needs batteries’. Lay them face up. One player secretly picks one card. The other player asks yes/no questions about the fields, such as ‘more than 50 pieces?’, and the first player turns over every card that no longer fits. Each question is a criterion. Count how many cards remain after each question and how many questions were needed to find the card. Then write the questions you asked as one search with several conditions.
Practice
1
This CSV text will be imported into a database. State the number of records, the number of fields, the field names and the delimiter.
Explain why a Phone field is given the data type short text and not whole number.
A phone number is never used in a calculation. A whole-number field would drop a leading 0 and could not hold a plus sign or a dash. Text keeps the number exactly as it is typed.
4
In the items table, how many records are found by Price < 3, and how many by Price < 3 and In_stock = Yes? Name them.
Item_ID
Item
Price
In_stock
1
Bread
2.50
Yes
2
Olives
4.20
Yes
3
Honey
6.00
No
4
Cheese
5.50
Yes
5
Jam
3.00
Yes
6
Tea
1.80
No
Price < 3 finds 2 records: Bread (2.50) and Tea (1.80). Adding In_stock = Yes leaves 1 record: Bread, because Tea is not in stock.