A relational database, GARDEN, has the following tables: OWNER(OwnerID, FirstName, TelephoneNo, TreeID, TreePosition) TREE(TreeID, ScientificName, MaxHeight, FastGrowing)

Mark scheme

Marking points from the official mark scheme (Oct/Nov 2022). The mark or mark type is shown in brackets.

1. [1] Solution 1: removing the many-to-many relationship between Owner and Tree
2. [1] by removing TreeID and TreePosition from the Owner table
3. [1] and creating a linking table between Owner and Tree
4. [1] that contains OwnerID, TreeID and TreePosition
5. [1] (composite) primary key of the linking table should be OwnerID and TreeID // insert a named new primary key in the linking table
6. [1] Solution 2: removing the many-to-many relationship between Owner and Tree
7. [1] move TreePosition into TREE table
8. [1] put OwnerID into TREE table
9. [1] create a new table with suitable name (for the species of tree)
10. [1] containing ScientificName, MaxHeight and FastGrowing
11. [1] with ScientificName as primary key // or another suitable primary key
12. [1] INSERT INTO TREE
13. [1] VALUES () and correct values
14. [1] Values in correct order
15. [1] Option 1: INSERT INTO TREE(TreeID, ScientificName, MaxHeight, FastGrowing) VALUES('LOW_1276', 'Salix_Alba', 30.00, TRUE);
16. [1] Option 2: INSERT INTO TREE VALUES('LOW_1276', 'Salix_Alba', 30.00, TRUE);
17. [1] An attribute / field (or set of attributes / fields) that could be a primary key
18. [1] stores metadata about the database
19. [1] For example: field / attribute names
20. [1] table name
21. [1] validation rules
22. [1] data types
23. [1] primary keys // foreign keys
24. [1] relationships
25. [1] the overview of a database structure
26. [1] models the problem / situation
27. [1] by using methods such as an ER diagram
28. [1] independent of any particular DBMS