Databases: Test

These test style questions will only cover the content that was not covered at GCSE. However that content is still examinable. For full coverage you should also complete the GCSE Databases test which contains further assessable material for this unit that was originally covered in the GCSE course.

Exam style questions

Download a pdf version of the test

Use the space below each question or a pen and paper to write your answer. When complete click the button for the answer and mark scheme.

NOTE: Answers typed into the browser will not be retained if you leave the page or refresh

Questions

A school has a cleaning staff of 10 that work on a rota. On a shift a staff member will clean several rooms. Because they are on a rota they don't always clean the same rooms every time. This means that more than one cleaner may clean each room. Draw an E-R diagram to show the relationship between cleaners and rooms. (3 marks)


1 mark for an E-R diagram and 1 mark for each correct connection

A company makes a variety of products. Each product is made up of many parts and each part may be used in many products. Create a link table to link the product and parts table below so they can record how many of each part is used in each product. (3 marks)
tblProducts(ProductID, ProductName, UnitType)
tblParts(PartID, PartName, SupplierID, UnitType)


tblProdParts(ProductID, PartID, Quantity)
1 mark for creating an appropriately named table ; 1 mark for a dual key of ProductID and PartID; 1 mark for including a field for quantity

Describe what happens what happens during normalisation to reach each normal form (3 marks)


To reach 1st normal form all repeating attributes or groups of attributes must be removed from our tables. (1) To reach 2nd normal form all partial key dependencies must also be removed. (1) To reach 3rd normal form all non-key dependencies must be removed. (1)

Explain what data duplication and data inconsistency are and how they affect databases that have not been normalised. (4 marks)


Data duplication is where te same piece of data is stored in more than one place in the database. (1) This leads to the database taking up more storage space. (1)
Data inconsistency comes as a result of data duplication and is where the data stored in each place is slightly different (1) e.g. a misspelled surname in one location. (1)

Explain what is meant by a partial key dependency (2 marks)


This is where a field in a table with a composite primary key (1) realtes only to one part of the primary key. (1)

A database contains a table tblEmployees(EmployeeID, Forename, Surname, Department)
Write SQL that will add a new record to this table for Phillip Fry who is employee number 123 and works in the shipping department. (2 marks)


INSERT INTO tblEmployees
VALUES (123, "Phillip", "Fry", "Shipping")
1 mark per correct line.

A database contains a table tblEmployees(EmployeeID, Forename, Surname, Department)
Write SQL to update the surname of employee 101 Theresa Good who has gotten married and changed her last name to Green (3 marks)


UPDATE tblEmployees
SET Surname = "Green"
WHERE EmployeeID = 101
1 mark per correct line. The where condition must reference the employee ID not the surname as that may not be unique.

A database contains a table tblEmployees(EmployeeID, Forename, Surname, Department). Wrie SQL to remove employee 125 Ben Rodriguez who worked in the shipping department from the database because he has resigned (2 marks)


DELETE FROM tblEmployees
WHERE EmployeeID = 125
1 mark per line

A database contains a table tblEmployees(EmployeeID, Forename, Surname, Department). Write SQL to output a list of all staff members and their department in alphabetcal order for staff from either the Sales or Marketing department (5 marks)


SELECT Forename, Surname, Department
FROM tblEmployees
WHERE Department = "Sales"
OR Department = "Marketing"
ORDER BY Surname
1 mark per line. Lines 3 and 4 may be replaced by WHERE Department IN ("Sales", "Marketing") which should also be worth 2 marks

A database contains the following tables amongst others:
tblOrder(OrderID, OrderDate, CustomerID)
tblOrderLines(OrderID, PartID, Quantity)
tblCustomers(CustomerID, CustomerName)
Write SQL to produce a list of all customers who bought a product containing a faulty part with ID 436


SELECT tblOrder.CustomerID, tblCustomer.CustomerName
FROM tblOrder, tblOrderLines, tblCustomers
WHERE tblOrder.CustomerID = tblCustomer.CustomerID
AND tblOrder.OrderID = tblOrderLines.OrderID
AND tblOrderLines.PartID = 436
1 mark per line.

© All materials created by and copyright S.Goff