Link the tables
Answers are for parents and teachers.
1
A garden club keeps one table with these columns: Visit_ID, Member, Member_phone, Plot. Member Tara appears in four rows. Describe one problem that this causes.
The phone number of Tara is stored four times (data redundancy). If it changes, the helper must correct four rows, and missing one causes an update anomaly. Deleting her only visit would delete her phone number too (a deletion anomaly).
2
Split the garden club’s table into two tables. Name each table, list its fields, and mark the primary key and the foreign key.
| Visit_ID | Member | Member_phone | Plot |
|---|---|---|---|
| 1 | Tara | 555-0410 | Herb bed |
| 2 | Joe | 555-0433 | Pond side |
| 3 | Tara | 555-0410 | Bean rows |
| 4 | Tara | 555-0410 | Herb bed |
Members table: Member_ID (primary key), Member, Member_phone. Visits table: Visit_ID (primary key), Plot, Member_ID (foreign key). Member_ID links the tables, and each phone number is stored once.
3
A Pets table has the fields Owner_ID, Owner_name and Pet_name. Which field is the best primary key, and why? Write the data type of each field.
Owner_ID is the best primary key because it is different for every record. Names can repeat. Data types: Owner_ID whole number, Owner_name short text, Pet_name short text.
4
Study the CSV text. State the number of records and fields, and write the data type of Price and of Hot.
Csv
1Drink,Price,Hot 2Tea,1.20,Yes 3Juice,2.00,No 4Cocoa,1.80,Yes 5Water,0.90,No
There are 4 records and 3 fields. Price is a decimal (currency) type. Hot is yes/no. The first line (the field names) is not a record.
5
In this table, how many records match Price > 1.50, and how many match Price > 1.50 and Hot = Yes? Name them.
| Drink | Price | Hot |
|---|---|---|
| Tea | 1.20 | Yes |
| Juice | 2.00 | No |
| Cocoa | 1.80 | Yes |
| Water | 0.90 | No |
Price > 1.50 matches 2 records: Juice (2.00) and Cocoa (1.80). Adding Hot = Yes leaves only Cocoa, so 1 record.