Advanced level 2023 south west regional mock computer science 3

Task 1:Normalisation (6 marks)
i. One functional dependency that exists amongst the fields in Table 1 is:

| StudcntID
Write down all other functional dependencies that exist amongst the fields in Table 1.

7

BookIDJ

— jReturnDate i J

ii. Table 1 is not in Second Normal Form (2NF). It can be converted into 2NF by decomposing it into three relationsStudents. Books and Loans which are of obvious meanings.

In your answer booklet, represent the three relations Students^ Books and Loans in 2NF using the format:
table nameifield1,field!,fieldX..J,wherefieldl(underlined)istheprimarykey. (3marks

Task 2: Implementation (14 marks)
i. In your favourite database management system (DBMS), create a database named Library which contains the

relations Students, Books and Loans as defined in Task 1. Write down the SQL statement you have used to create the database. (3 marks)

ii. In your answer booklet, wprite down an SQL statement to create the Students relation from Task 1. (3 marks)

iii. Create relationships between the relations in the Library database. Show the degree of each relationship.
Do a screenshot of the relationship diagram, save it as Relationship and then print a copy or draw the relationship diagram in your answer booklet . (2 marks)

iv. Populate the tables in the Library database with the data from Table 1. Print each of the tables in Datasheet view. (3 marks)