A-LevelComputer ScienceDatabases9618/12 · May/June 2021 · Q116 Marks

Raj owns houses that other people rent from him. He has a database that stores details about the people who rent houses, and the houses they rent. The database, HOUSE_RENTALS, has the following structure: CUSTOMER (CustomerID, FirstName, LastName, DateOfBirth, Email) HOUSE (HouseID, HouseNumber, Road, Town, Bedrooms, Bathrooms) RENTAL(RentalID, CustomerID, HouseID, MonthlyCost, DepositPaid)

(a)Give the definition of the following database terms, using an example from the database HOUSE_RENTALS for each definition. Term Field Entity Foreign key Definition and example[6]
(b)Tick (✔) one box to identify whether the database HOUSE_RENTALS is in Third Normal Form (3NF) or not in 3NF. Justify your choice using one or more examples from the database HOUSE_RENTALS. In 3NF Not in 3NF Justification[2]
(c)(i)Complete the following Data Definition Language (DDL) statement to define the table RENTAL. CREATE ( RentalID INTEGER NOT NULL, CustomerID INTEGER NOT NULL, HouseID (5) NOT NULL, MonthlyCost NOT NULL, DepositPaid BOOLEAN NOT NULL, (RentalID) );[4]
(c)(ii)Write a Data Manipulation Language (DML) script to return the first name and last name of all customers who have not paid their deposit.[4]

Mark scheme

Marking points from the official mark scheme (May/June 2021). The mark or mark type is shown in brackets.

[1] (a) Field: A column/attribute in a table
[1] (a) Field example: e.g. CustomerID in the table CUSTOMER
[1] (a) Entity: Anything that data can be stored about
[1] (a) Entity example: e.g. A customer or a house
[1] (a) Foreign Key: A field in one table that is linked to a Primary Key in another table
[1] (a) Foreign Key example: e.g. CustomerID/HouseID in table RENTAL
[1] (b) All fields in all tables are dependant fully on the PK and on no other fields
[1] (b) for example all fields in Customer table are fully dependent on CustomerID
[1] (c)(i) HouseID VARCHAR (5)
[1] (c)(i) MonthlyCost REAL/CURRENCY
[1] (c)(i) PRIMARY KEY(RentalID)
[1] (c)(ii) Select FirstName and LastName
[1] (c)(ii) From both tables
[1] (c)(ii) Where DepositPaid = No
[1] (c)(ii) Joining tables (either AND, or INNER JOIN)

On Oracle Prep you can write or photograph your own answer and have it marked by AI against this mark scheme, with feedback on what you missed.