MskProdComputing
Back to the topic

The mark scheme starts on a new page, so you can print pages one and two for a student and keep the last page yourself. Everything prints in black on white whichever theme you are using.

Python · A Level 13

Relational Databases, Normalisation and SQL

Section A: 10 multiple choice questions, 10 marks. Section B: 5 written questions, 20 marks. Total 30 marks.

Name

Class

Date

Mark  / 30

Section A

Circle the letter of the correct answer. One mark each.

  1. 1What is the main problem with storing all the data in a single flat file?1
    1. AThe file cannot store more than one type of data
    2. BThe file cannot be backed up while the program is running
    3. CThe data can only be read by one person at a time
    4. DThe same facts are repeated, which can lead to inconsistent data
  2. 2What is a foreign key?1
    1. AAn attribute that holds the primary key of a record in another table
    2. BAn attribute that is unique in its own table
    3. CAn attribute that holds a password for the database
    4. DAn attribute that can be left empty in every record
  3. 3What does first normal form require?1
    1. AEvery table is linked to at least two other tables
    2. BEvery cell holds a single value, with no repeating groups, and there is a primary key
    3. CNo non key attribute depends on another non key attribute
    4. DEvery non key attribute depends on the whole of the primary key
  4. 4What does second normal form remove?1
    1. APartial dependencies on part of a composite key
    2. BForeign keys that refer to other tables
    3. CTransitive dependencies between non key attributes
    4. DRepeating groups of columns
  5. 5What does third normal form remove?1
    1. AColumns that hold numbers
    2. BTransitive dependencies between non key attributes
    3. CPartial dependencies on a composite key
    4. DRecords that have no foreign key
  6. 6Which clause filters the groups produced by GROUP BY, after aggregates are calculated?1
    1. AJOIN
    2. BHAVING
    3. CORDER BY
    4. DWHERE
  7. 7What is the purpose of a JOIN in SQL?1
    1. ATo combine rows from two tables where their key values match
    2. BTo add a new column to a table
    3. CTo delete rows that are not used
    4. DTo copy a table into a second database
  8. 8What does atomicity mean for a database transaction?1
    1. AIt runs faster than any other operation
    2. BIt can only affect a single table
    3. CAll of its operations happen, or none of them do
    4. DIt cannot be undone once it has started
  9. 9What does referential integrity ensure?1
    1. AEvery column has a different data type
    2. BEvery table has at least ten records
    3. CEvery primary key is a whole number
    4. DEvery foreign key value matches an existing primary key
  10. 10What is the effect of adding an index to a column?1
    1. AThe column is removed from the table
    2. BSearches on that column are slower, but writes are faster and less storage is used
    3. CSearches on that column are faster, but writes are slower and more storage is used
    4. DThe column can no longer contain duplicate values

Section B

Answer in the space provided. The number of marks tells you how many separate points to make.

  1. 1StateState three problems that can occur when data is stored in a flat file database.3
  2. 2NormaliseThe table shows the data held by a small online shop in a single table. The primary key is the pair OrderID and ProductID. Normalise the data to third normal form. State the name, attributes and primary key of each table that you create.6
    OrderIDProductIDOrderDateCustomerIDCustomerNameAddressProductNamePriceQuantity
    101P103/05C7A. Khan4 High StKeyboard25.001
    101P203/05C7A. Khan4 High StMouse12.502
    102P104/05C9B. Ali9 Mill RdKeyboard25.003
  3. 3WriteA database has the tables Customer(CustomerID, Name, City) and CustomerOrder(OrderID, CustomerID, Total). Write an SQL query that displays the Name of each customer and the sum of the Total of all their orders, but only for customers whose orders add up to more than 100.4
  4. 4ExplainExplain what is meant by referential integrity, and give an example of how it protects a database.3
  5. 5ExplainA bank transfer takes money from one account and adds it to another using two SQL updates. Explain how using a transaction protects the data if the system fails after the first update, with reference to atomicity and durability.4

Mark scheme

Section A

1D2A3B4A5B6B7A8C9D10C

Section B

1 State three problems that can occur when data is stored in a flat file database. 3

One mark for each of the following, to a maximum of 3.

  • Data is duplicated, which wastes storage space
  • The same data may be stored differently in different places, so it is inconsistent
  • A change to a repeated item must be made in many places, which is slow or may be missed
  • It is difficult to add or remove data without affecting other data

Model answer. Data is repeated in many records, which wastes storage space. The repeated data can become inconsistent, if an item is changed in one record but not in the others. A change to repeated data has to be made in every place it appears, which is slow and easy to get wrong. Deleting a record can also lose other facts that were held only in that record.

2 The table shows the data held by a small online shop in a single table. The primary key is the pair OrderID and ProductID. Normalise the data to third normal form. State the name, attributes and primary key of each table that you create. 6

One mark for each of the following, to a maximum of 6.

  • A Customer table with CustomerID as the primary key, holding the name and address
  • A Product table with ProductID as the primary key, holding the name and price
  • An Order table with OrderID as the primary key, holding the date
  • The Order table has CustomerID as a foreign key to the Customer table
  • An OrderLine table, which links orders and products, with Quantity
  • OrderLine has a composite primary key of OrderID and ProductID, which are both foreign keys

Model answer. Customer details depend only on the customer, so they go in a Customer table: CustomerID (primary key), CustomerName, Address. Product details depend only on the product: Product table with ProductID (primary key), ProductName, Price. Order details depend only on the order: Order table with OrderID (primary key), OrderDate and CustomerID, which is a foreign key to the Customer table. The quantity depends on both the order and the product, so an OrderLine table holds OrderID, ProductID and Quantity, and its primary key is the pair OrderID and ProductID together, each of which is also a foreign key.

3 A database has the tables Customer(CustomerID, Name, City) and CustomerOrder(OrderID, CustomerID, Total). Write an SQL query that displays the Name of each customer and the sum of the Total of all their orders, but only for customers whose orders add up to more than 100. 4

One mark for each of the following, to a maximum of 4.

  • SELECT the name and SUM of the Total
  • FROM the two tables, joined ON the matching CustomerID
  • GROUP BY the customer
  • HAVING SUM(Total) > 100, not WHERE

Model answer.

SELECT Customer.Name, SUM(CustomerOrder.Total)
FROM Customer
JOIN CustomerOrder ON Customer.CustomerID = CustomerOrder.CustomerID
GROUP BY Customer.CustomerID
HAVING SUM(CustomerOrder.Total) > 100

The two tables are joined on the matching CustomerID, the orders are grouped by customer so that SUM gives one total for each, and HAVING keeps only those groups whose total is over 100. WHERE could not be used because the total does not exist until after the grouping.

4 Explain what is meant by referential integrity, and give an example of how it protects a database. 3

One mark for each of the following, to a maximum of 3.

  • Every foreign key value must match the primary key of an existing record in the table it refers to
  • The database refuses an action that would break this rule
  • For example, an order cannot be added for a customer who does not exist, or a customer cannot be deleted while they still have orders

Model answer. Referential integrity means that every foreign key value must match the primary key of a record that really exists in the table it refers to. The database refuses any action that would break the rule. For example, it will not allow an order to be recorded for a customer number that is not in the Customer table, and it will not allow a customer to be deleted while orders that refer to them still exist, so there are never orders left pointing at nothing.

5 A bank transfer takes money from one account and adds it to another using two SQL updates. Explain how using a transaction protects the data if the system fails after the first update, with reference to atomicity and durability. 4

One mark for each of the following, to a maximum of 4.

  • A transaction treats both updates as a single unit
  • Atomicity means all of the updates happen or none do
  • After a failure the first update is rolled back, so no money is lost
  • Durability means that once the transaction is committed, the changes are permanent even if the system then fails

Model answer. A transaction treats the two updates as a single unit. Atomicity means that either both updates are carried out or neither is, so if the system fails after the first update the transaction is rolled back and the first update is undone, which means no money is lost or created. Durability means that once the transaction has been committed the changes are permanent, and they will still be there even if the system fails immediately afterwards.