| Date | May 2018 | Marks available | 2 | Reference code | 18M.2.SL.TZ0.1 |
| Level | SL | Paper | 2 | Time zone | no time zone |
| Command term | State | Question number | 1 | Adapted from | N/A |
Question
Galaxy Bank is a US based bank with many banks (branches) across the country. Galaxy Bank uses a relational database to support its operations.
Each branch has many customers and each customer may take out a number of loans.
Some of the data in the LOANS table is shown below.
The underlined attribute indicates the primary key.
LOANS
Two other tables in the database are identified below:
- The ACCOUNTS table contains the account details of the customers.
- The CUSTOMERS table contains the contact details of each customer.
The underlined attribute indicates the primary key in each table.
ACCOUNTS
CUSTOMERS
Security is the top priority for Galaxy Bank.
Construct the entity relationship diagram (ERD) that shows the relationship between the bank branch, the customers and their loans.
With reference to the table LOANS, state the output of the following query:
SELECT LOANS.Loan_ID, LOANS.Customer_ID, LOANS.Amount, LOANS.Type
FROM LOANS
WHERE (LOANS.Amount > 600000) AND ((LOANS.Type = "Home") OR
(LOANS.Type = "Venture"));
Identify the steps to create a query to find the names of customers whose account balance is greater than $300 000.
Explain how controlling access rights contributes to the security of the Galaxy Bank database.
Explain how row locking ensures the consistency of the data in the Galaxy Bank database.
Markscheme
Bank, Customers and Loan correctly positioned;
for EACH 1 to n relationship / for “has customers” and “has loans” (accept words to this effect);
Award [1] for any two correct rows, [2] for all three rows correct.
Award up to [4 max].
Answers may include:
Step 1: From the table CUSTOMERS,ACCOUNTS;
Step 2: Choose Family_name attribute;
Step 3: For a condition of the Customer_ID matching in both the CUSTOMERS and ACCOUNTS table;
Step 4: And also satisfying the condition of the Balance > 300 000;
SELECT CUSTOMERS.Family_name, ACCOUNTS_Balance
FROM CUSTOMERS INNER JOIN ACCOUNTS
ON CUSTOMERS.Customer_ID = ACCOUNTS,Customer_ID
WHERE ACCOUNTS.Balance > 300 000
Award [1] for identifying the FamilyName.
Award [1] for checking that Customer_ID matches.
Award [1] for identifying the use of both CUSTOMERS and ACCOUNTS.
Award [1] for balance check of greater than 300 000.
Note: SQL is not required.
Award up to [3 max].
Access control;
This means that different users will have different levels of access to the Galaxy Bank database;
Therefore customers will only be able to access the information that they require to carry out their transactions;
Whereas other users such as the DBA will have access to more of the database;
Ensuring that the sensitive information within the Galaxy Bank database is secure from unauthorized access, editing etc.;
Note: Do not accept responses pertaining to how access rights are granted.
Award up to [3 max].
Row locking prevents two or more database users from updating the same data at the same time;
When a row is locked it means that another database session cannot update that data until the lock is released (which unlocks the data and allows other database users to update that data;
If the database as a whole is locked, then only one database session can apply any updates; Locks are done using statements;
