8.2 Database Management Systems (DBMS)
AS Level · 14 questions found
What this topic covers
Section titled “What this topic covers”- DBMS features: data dictionary, data modelling, logical schema, data integrity, security/backup, access rights
- DBMS tools: developer interface and query processor
Past paper questions
Section titled “Past paper questions”A relational database, WORKEXPERIENCE, stores data about students and the companies where they complete work experience.
Students can complete multiple work experience placements but can only complete one placement at a time.
Students can complete more than one placement at the same company.
Part of the database is shown:
STUDENT(StudentID, FirstName, TelephoneNumber, UniversityName)
PLACEMENT( PlacementID, StudentID, CompanyID, StartDate, EndDate, Complete)
COMPANY(CompanyID, CompanyName, MaxStudentsPerPlacement)
(a) Complete the entity-relationship (E-R) diagram for the database WORKEXPERIENCE. 2 marks
STUDENT PLACEMENT
COMPANY
(b) Explain why the database WORKEXPERIENCE is in Third Normal Form (3NF). 2 marks
(c) Some example data from the PLACEMENT table is shown:
| PlacementID | StudentID | CompanyID | StartDate | EndDate | Complete |
|---|---|---|---|---|---|
| PC001 | LDEA01 | MSCM | 01/01/2023 | 12/07/2023 | TRUE |
| PC002 | LDEA01 | MSCM | 01/04/2024 | 08/05/2024 | FALSE |
| PC003 | ALAU02 | NEAM | 09/03/2020 | 10/03/2021 | FALSE |
| PC004 | LOLI75 | GOUZ | 07/06/2018 | 11/09/2018 | TRUE |
(i) Write a Structured Query Language (SQL) script to delete all placements that have been completed. 2 marks
(ii) Write an SQL script to return the total number of placements completed by the student with ID LDEA01 at the company with ID NEAM. The total should be given an appropriate name. 4 marks
(d) The database tables are repeated here: 3 marks
STUDENT(StudentID, FirstName, TelephoneNumber, UniversityName)
PLACEMENT( PlacementID, StudentID, CompanyID, StartDate, EndDate, Complete)
COMPANY(CompanyID, CompanyName, MaxStudentsPerPlacement)
Explain what is meant by referential integrity, and how it applies to this database.
Show mark scheme
2(a) [2 marks]
1 mark for each correct relationship, max 2 marks
2(b) [2 marks]
1 mark per bullet point, max 2 marks • There are no repeating groups of attributes • There are no many-to-many relationships • There are no partial key dependencies // no non-key dependencies // no transitive dependencies
2(c)(i) [2 marks]
1 mark per bullet point, max 2 marks • DELETE FROM and correct table • Correct condition Example answer: DELETE FROM PLACEMENT WHERE Complete = TRUE;
2(c)(ii) [4 marks]
1 mark per bullet point, max 4 marks • SELECT COUNT any appropriate field • AS and an appropriate name • FROM and correct table and WHERE and one correct condition • Two AND clauses with the other two correct conditions Example answer: SELECT COUNT (CompanyID) AS TotalPlacements FROM PLACEMENT WHERE CompanyID = "NEAM" AND StudentID = "LDEA01" AND Complete = TRUE;
2(d) [3 marks]
1 mark per bullet point, max 3 marks Generic mark points (max 2) • Referential integrity ensures that related data is consistent • Referential integrity ensures that every foreign key has a corresponding primary key • Referential integrity provides for cascading update / delete • Referential integrity ensures that if a primary key is deleted or modified all linked records in foreign table will be deleted or modified // Referential integrity stops "orphaned records"—records in a table that point to an entry in another table that no longer exists Specific mark points (max 2) for good examples e.g. • CompanyID is a foreign key in PLACEMENT table and is dependent on the primary key CompanyID in COMPANY table • If a record is deleted from the STUDENT table, then all records with the same StudentID will be deleted from the PLACEMENT table • If a CompanyID is modified in the company table, all records with that CompanyID in the placement table will also be modified
A relational database, SHIPPING, stores data about the ships in a company and the containers that are carried on the ships.
The database has the following tables:
CONTAINER(ContainerID, Type, Weight, OwnerName, ShipID)
SHIP(ShipID, Type, Capacity, ShipName)
(a) Describe the relationship between the two tables. Refer to the primary and foreign keys in your answer. 2 marks
(b) The table CONTAINER needs an additional field to store the data for the last inspection date. 2 marks
Write a Structured Query Language (SQL) script to add one field to the table CONTAINER to store the date of last inspection of the container, for example 08/07/2019.
(c) Write an SQL script to return the number of containers stored in the database for the ship with the name Caledonia. 4 marks
(d) Describe the purpose of a developer interface in a Database Management System (DBMS). 2 marks
Show mark scheme
4(a) [2 marks]
1 mark per bullet point, max 2 marks • The relationship between SHIP and CONTAINER is one-to-many (1:M) • The primary key ShipID in the SHIP table is linked to the foreign key ShipID in the CONTAINER table
4(b) [2 marks]
1 mark per bullet point, max 2 marks • ALTER TABLE statement • Including the additional field with a suitable field name and suitable field type Example answer ALTER TABLE CONTAINER ADD InspectionDate DATE;
4(c) [4 marks]
1 mark per bullet point, max 4 marks • SELECT COUNT statement • Using the correct tables • Joining the tables • The condition for the name Example Answer 1: SELECT COUNT(ContainerID) FROM CONTAINER, SHIP WHERE CONTAINER.ShipID = SHIP.ShipID AND ShipName = "Caledonia"; Example Answer 2: SELECT COUNT(ContainerID) FROM CONTAINER INNER JOIN SHIP ON CONTAINER.ShipID = SHIP.ShipID WHERE ShipName = "Caledonia";
4(d) [2 marks]
1 mark per bullet point, max 2 marks • To allow the user to create / modify / delete tables // maintain the database • To allow the user to set up / modify relationships • To allow the user to create a form for data input • To allow a user to add tools to a form // for example, drop-down boxes / buttons etc. • To allow a user to design a report (to show the output in an organised manner) • To allow a user to add a menu to enable a choice of different actions • To allow a user to inspect the database contents and metadata • ... by creating and/or running different SQL queries
A company creates a relational database to store data about its customers.
The database, REVIEWS, stores data about the customers, products, complaints and staff.
The database has four tables:
CUSTOMER(CustomerID, CustomerFirstName, CustomerLastName, CustomerEmail)
PRODUCT(ProductID, ProductName, ProductDetail, Price, Rating)
COMPLAINT(ComplaintID, ProductID, CustomerID, ComplaintDetails, StaffID)
STAFF(StaffID, StaffFirstName, StaffLastName, Department, RemoteWorker)
(a) Complete the entity‑relationship (E‑R) diagram for the database REVIEWS. 3 marks
CUSTOMER PRODUCT
COMPLAINT STAFF
(b) Some example data from the STAFF table is shown. 4 marks
| StaffID | StaffFirstName | StaffLastName | Department | RemoteWorker |
|---|---|---|---|---|
| 1 | Ralph | Jura | E | Yes |
| 2 | Luca | Emcee | A | Yes |
| 3 | Darwin | Acula | F | No |
Write a Structured Query Language (SQL) script to define the table STAFF.
(c) Products are given a rating between 1 and 10 inclusive. 5 marks
Write an SQL script to return only the ProductID, ProductName and ComplaintDetails for all products with a rating of 5 or less. The results need to be displayed in descending order of rating.
(d) A Database Management System (DBMS) is used to maintain and manage the database. 4 marks
Describe two ways in which the DBMS can be used to ensure the security of the customer data.
1
2
Question 6 starts on the next page.
Show mark scheme
5(a) [3 marks]
1 mark for each correct relationship, max 3 marks
5(b) [4 marks]
1 mark per bullet point, max 4 marks • Create table with opening and closing brackets and commas separating the attributes • Appropriate data types for StaffFirstName, StaffLastName and Department • Appropriate data types for StaffID and RemoteWorker • Primary key correctly defined Example Answer 1: CREATE TABLE STAFF( StaffID INTEGER, StaffFirstName VARCHAR, StaffLastName VARCHAR, Department CHAR, RemoteWorker BOOLEAN, PRIMARY KEY (StaffID) ); Example Answer 2: CREATE TABLE STAFF( StaffID INTEGER NOT NULL PRIMARY KEY, StaffFirstName VARCHAR, StaffLastName VARCHAR, Department CHAR, RemoteWorker BOOLEAN );
5(c) [5 marks]
1 mark per bullet point, max 5 marks • SELECT and the correct attributes • FROM and the correct tables • Tables joined correctly • Correct condition for the rating • Correct ORDER BY clause Example Answer 1 SELECT PRODUCT.ProductID, ProductName, ComplaintDetails FROM PRODUCT, COMPLAINT WHERE PRODUCT.ProductID = COMPLAINT.ProductID AND Rating <=5 ORDER BY Rating DESC; Example Answer 2 SELECT PRODUCT.ProductID, ProductName, ComplaintDetails FROM PRODUCT INNER JOIN COMPLAINT ON PRODUCT.ProductID = COMPLAINT.ProductID WHERE Rating <=5 ORDER BY Rating DESC;
5(d) [4 marks]
One mark for identification of method one mark for corresponding description max 4 marks • Authentication methods / passwords / biometrics / 2-factor authentication can be implemented • ... which prevents unauthorised access to the customer’s data • Access rights / privileges can be set • ... so that only those with correct permissions can read / edit customer’s data • Regular backups can be scheduled • ... so that a second copy of the customer’s data is available in case of loss/damage • The data can be encrypted • ... so that the customer’s data cannot be understood by anyone who gains unauthorized access • Different views can be created • ... so that not everyone can see the customer’s data
A company builds and sells furniture to customers. The company stores data about customers, their payment cards and their furniture orders in a database.
The database, FURNITURE, has the following tables:
CUSTOMER(CustomerID, Name, Phone)
CUSTOMER_CARD_DATA(CardID, CustomerID, CardType, CardNumber, EndDate)
ORDER(OrderID, CustomerID, TotalCost, Paid, OrderDate, Complete)
ORDER_ITEM(OrderItemID, OrderID, Type, Height, Width, Depth, Details)
The primary keys are underlined in each table.
The attribute Complete in the table ORDER stores the Boolean value TRUE if the order has been built and FALSE if the order has not been built.
(a) Complete the entity‑relationship (E‑R) diagram for the database. 3 marks
CUSTOMER CUSTOMER_CARD_DATA
ORDER ORDER_ITEM
(b) Identify one attribute in the table CUSTOMER_CARD_DATA that could be a candidate key. 1 mark
(c) Identify two tables in the database that contain one or more foreign keys. 2 marks
Give one attribute that is a foreign key in each table.
| Table | Foreign key | |
|---|---|---|
| 1 | ||
| 2 |
(d) Explain the reasons why the data in the table ORDER_ITEM cannot be stored in the table ORDER 3 marks
(e) Write an Structured Query Language (SQL) script to output the customer ID, the customer’s name and the total cost of the customer’s orders that have not been paid. 4 marks
The output of the total cost must have an appropriate title.
Show mark scheme
5(a) [3 marks]
1 mark for each relationship. Max 2 if any extra
5(b) [1 mark]
1 mark for: CardNumber
5(c) [2 marks]
1 mark per row to max 2 Table Foreign key ORDER_ITEM OrderID ORDER CustomerID CUSTOMER_CARD_DATA CustomerID
5(d) [3 marks]
1 mark each to max 3 e.g. • Each order would only be able to have one item • or the database would not be normalised • it would not be in 1NF • due to repeated groups of attributes • in the ORDER table
5(e) [4 marks]
1 mark each • Selecting the customer ID, customer name and sum of TotalCost with appropriate identifier • FROM clause with suitable join of tables (ON or WHERE) • ORDER.Paid = FALSE condition (with correct key word) • GROUP BY condition e.g. SELECT CUSTOMER.CustomerID, CUSTOMER.Name, Sum(ORDER.TotalCost) AS TotalOwed FROM CUSTOMER INNER JOIN ORDER ON CUSTOMER.CustomerID = ORDER.CustomerID WHERE ORDER.Paid = FALSE GROUP BY CUSTOMER.CustomerID; Alternative JOIN Statement SELECT CUSTOMER.CustomerID, CUSTOMER.Name, Sum(ORDER.TotalCost) AS TotalOwed FROM CUSTOMER,ORDER WHERE CUSTOMER.CustomerID = ORDER.CustomerID AND ORDER.Paid = FALSE GROUP BY CUSTOMER.CustomerID;
An online game has a database that stores data about users and the characters each user creates in the game. Each user can create multiple characters and purchase multiple items for each character.
The normalised database has the following design:
USER(Username, Password, DateOfBirth)
CHARACTER(CharacterName, CharacterID, Username, Level, Money)
ITEM(ItemName, MinimumLevel, Cost)
CHARACTER_ITEM(CharacterID, ItemName)
(a) Explain the purpose of the table CHARACTER_ITEM in the database. 2 marks
(b) Underline the attribute, or attributes, that form the primary key in each of the tables. 2 marks
USER(Username, Password, DateOfBirth)
CHARACTER(CharacterName, CharacterID, Username, Level, Money)
ITEM(ItemName, MinimumLevel, Cost)
CHARACTER_ITEM(CharacterID, ItemName)
(c) A Database Management System (DBMS) provides data security.
(i) Identify two methods the DBMS can use to protect the data in the table USER from unauthorised access. 4 marks
Explain how each method protects the data.
Method 1
Explanation
Method 2
Explanation
(ii) The DBMS also supports data integrity. 2 marks
Give two ways that a DBMS can support data integrity.
1
2
(d) (i) Write a Structured Query Language (SQL) script to count the number of items purchased by the user with the username "KAT123". 3 marks
(ii) The following changes need to be made to the character with the ID "0002": 3 marks
level changed to 3
money changed to 10000.00
Write an SQL script to change the character’s data.
Show mark scheme
5(a) [2 marks]
1 mark each to max 2 • To remove the many-to-many relationship • between CHARACTER and ITEM tables • To allow each character to have many items // to allow each item to be purchased for many characters • by creating a linking table • between the characters and the items purchased for each one
5(b) [2 marks]
1 mark for the three single Primary Keys 1 mark for the composite key USER(Username, Password, DateOfBirth) CHARACTER(CharacterName, CharacterID, Username, Level, Money) ITEM(ItemName, MinimumLevel, Cost) CHARACTER_ITEM(CharacterID, ItemName)
5(c)(i) [4 marks]
1 mark for method 1 mark for matching explanation e.g. • Access rights • appropriate permissions for the table USER are needed to read or edit the data • A password for the database or the USER table • prevents users without the password from accessing the data • Encrypting the database • stops users without the decryption key from decoding / understanding the data • Views • users can be given a view to the database that does not include the data in the table USER
5(c)(ii) [2 marks]
1 mark each to max 2 e.g. • Validation • Enforce referential integrity • Cascade update / delete • Ensuring the database is normalised
5(d)(i) [3 marks]
1 mark for each point • Select COUNT • FROM (using correct tables) and joining • WHERE (or AND) condition for correct username Example 1: SELECT COUNT(ItemName) FROM CHARACTER_ITEM INNER JOIN CHARACTER ON CHARACTER.CharacterID = CHARACTER_ITEM.CharacterID WHERE CHARACTER.Username = "KAT123"; Example 2: SELECT COUNT(ItemName) FROM CHARACTER_ITEM, CHARACTER WHERE CHARACTER.CharacterID = CHARACTER_ITEM.CharacterID AND CHARACTER.Username = "KAT123";
5(d)(ii) [3 marks]
1 mark for each point • UPDATE character • SET level and money • WHERE condition e.g. UPDATE CHARACTER SET Level = 3, Money = 10000.00 WHERE CharacterID = "0002";
A shop sells pens to customers. Customers place an order with the shop and collect the items the next day. The shop uses a database to store the information about the orders.
The database contains the following tables:
CUSTOMER(CustomerID, CustomerName, Email)
ORDER(OrderID, CustomerID, Date, Collected)
ORDER_PRODUCT(OrderID, ProductID, Quantity)
PRODUCT(ProductID, ProductName, QuantityInBox, Cost, SupplierID)
SUPPLIER(SupplierID, SupplierName, SupplierEmail)
The primary keys are underlined.
(a) Complete the entity-relationship (E-R) diagram for the database. 4 marks
CUSTOMER ORDER
SUPPLIER ORDER_PRODUCT
PRODUCT
(b) A new product needs to be entered into the database. The product has the ID 002323, the product name ‘Blue ball point 2mm’, there are 50 in a box, the product costs $5.00 and the supplier has the ID SFX223. 4 marks
Write a Structured Query Language (SQL) script to enter the new product into the table PRODUCT.
(c) The attribute Collected in the table ORDER stores the Boolean value TRUE if the order has been collected and FALSE if the order has not been collected. 4 marks
Write an SQL script to return the customer name for each customer that has orders they have not collected. Include the number of orders each customer has not collected with an appropriate title.
An example output might be:
| CustomerName | NotCollected |
|---|---|
| Jack Wright | 2 |
| Lin Cho | 1 |
| Santaya Yui | 1 |
(d) The shop uses a Database Management System (DBMS).
(i) One feature provided by a DBMS is a data dictionary. 3 marks
The data dictionary stores the attribute names, table names, foreign keys and primary keys in the database.
Identify three other items stored in a data dictionary.
1
2
3
(ii) The DBMS provides a developer interface. 3 marks
Explain how a database designer can make use of the developer interface.
Show mark scheme
6(a) [4 marks]
1 mark for each correct relationship ORDER CUSTOMER ORDER_PRODUCT SUPPLIER PRODUCT
6(b) [4 marks]
1 mark each • INSERT INTO PRODUCT • VALUES with opening and closing brackets • Inserting ProductID, ProductName and SupplierID correctly as strings including quotation marks into correct fields • Inserting quantity and cost correctly into correct fields Example 1: INSERT INTO PRODUCT VALUES ("002323", "Blue ball point 2 mm", 50, 5.00, "SFX223"); Example 2: INSERT INTO PRODUCT (ProductID, ProductName, QuantityInBox, Cost, SupplierID) VALUES ("002323", "Blue ball point 2 mm", 50, 5.00, "SFX223");
6(c) [4 marks]
1 mark each • Selection of customer name and counting any field from ORDER as an appropriate identifier • Joining tables ORDER and CUSTOMER • AND (or WHERE) clause: Collected = FALSE • Grouping by customer ID or customer name Example 1: SELECT CustomerName, COUNT(OrderID) AS NotCollected FROM ORDER, CUSTOMER WHERE ORDER.CustomerID = CUSTOMER.CustomerID AND Collected = FALSE GROUP BY CUSTOMER.CustomerID Example 2: SELECT CustomerName, COUNT(OrderID) AS NotCollected FROM ORDER INNER JOIN CUSTOMER ON ORDER.CustomerID = CUSTOMER.CustomerID WHERE Collected = FALSE GROUP BY CUSTOMER.CustomerID
6(d)(i) [3 marks]
1 mark each to max 3 e.g. • Relationships • Views • Data types • Validation rules
6(d)(ii) [3 marks]
1 mark each to max 3 e.g. • To create / modify / delete database objects • To create a form for data input • To add tools to a form • for example, drop-down boxes / buttons etc. • To design a report to show the output in an organised manner • To add a menu to enable users to choose different actions / run different queries
A shop repairs electronic devices, for example mobile phones and tablet computers. The shop owner stores the data about the repairs using a file-based approach.
(a) Give one limitation of using a file-based approach to store the data and explain how a relational database addresses this limitation. 3 marks
Limitation
Explanation
(b) The shop owner creates a relational database called FIXIT.
The database stores data about the customers and the devices for repair.
Some devices need new parts that are ordered from suppliers.
The database FIXIT is designed to include the following tables:
PART(PartID, Description, Price, SupplierID)
CUSTOMER(CustomerID, FirstName, LastName, ContactNumber)
REPAIR(RepairNumber, StartDate, EndDate, CustomerID, Device)
REPAIR_PART(PartID, RepairNumber, Quantity)
(i) Complete the entity-relationship (E-R) diagram for the given tables.
REPAIR_PART
PART
REPAIR CUSTOMER 3 marks
Show mark scheme
2(a) [3 marks]
1 mark for limitation and 2 marks for corresponding explanation e.g. Data redundancy // data duplication • Separate linked tables are used • Data items are stored once so reduces data duplication / data redundancy Data inconsistency / Poor data integrity • Data changed once in one place will automatically update elsewhere • Linked data cannot be entered differently in two tables • Referential integrity can be enforced The data structure used depends on the application • Changes to the data structure are managed by the DBMS • … and queries are not dependent on the structure of the data • … and changes to the data do not require programs to be re-written
2(b)(i) [3 marks]
1 mark for each correct relation
2(b)(ii) [5 marks]
1 mark for each bullet point • REPAIR_PART Create table with opening and closing brackets, all statements inside the brackets • PartID RepairNumber and as varchar (or equivalent) • Quantity as integer • At least 1 appropriate constraint • Dual primary key For example: CREATE TABLE REPAIR_PART( PartID VARCHAR(20) NOT NULL, RepairNumber VARCHAR(4) NOT NULL, Quantity INT NOT NULL, PRIMARY KEY (PartID, RepairNumber) );
2(b)(iii) [3 marks]
1 mark for each bullet point • AmountDue Select SUM of • From the correct table and one correct condition • Second correct condition SELECT SUM(AmountDue) FROM INVOICE WHERE SupplierID = "JK675" AND Paid = FALSE;
2(c) [3 marks]
1 mark for each correct definition Database Term Definition All duplicate entries of data between tables are Referential Integrity consistent // all foreign keys are matched to an appropriate primary key A field that could be a primary key but is not // an Candidate Key attribute or smallest set of attributes in a table where no tuple has the same value A row / record in a table // one instance of an entity in Tuple a table
A company uses a relational database to store data about its customers, employees and the individual repair jobs that customers have booked.
(a) Explain the benefits of using a relational database instead of a file-based approach. 3 marks
(b) The company decides which employees will work on each repair job. An employee can log into the database to access information about their repair jobs.
The database is normalised and includes these tables:
CUSTOMER stores personal data about each customer
EMPLOYEE stores personal data about each employee
LOGIN_DATA stores the username and password for each employee
JOB stores the data about each repair job
JOB_EMPLOYEE stores the employees that are working on each repair job.
(i) Identify each relationship between the database tables and explain how each relationship can be implemented in the normalised database. 6 marks
Show mark scheme
6(a) [3 marks]
1 mark each to max 3 e.g. • There is reduced data redundancy // less repeated data • … because each item of data is only stored once • Data consistency is maintained // Data integrity is improved • … changes in one table will automatically update in another • ... linked data cannot be entered differently in two tables • Program-data independence is ensured • … changes to the data do not require programs to be re-written // queries are not dependent on the structure of the data • Complex queries are easier to run • Different views can be provided • …. so users can only see specific aspects of the database • Multiple concurrent access is possible • ... through record locking
6(b)(i) [6 marks]
1 mark each to max 6 • CUSTOMER JOB to is 1 to many • CUSTOMER JOB … implemented by Primary Key in is Foreign Key in • EMPLOYEE LOGIN_DATA to is 1 to 1 • EMPLOYEE … implemented by Primary Key in is Foreign Key in LOGIN_DATA • JOB JOB_EMPLOYEE to is 1 to many • JOB … implemented by Primary Key in is Foreign Key in JOB_EMPLOYEE • EMPLOYEE JOB_EMPLOYEE to is 1 to many • EMPLOYEE … implemented by Primary Key in is Foreign Key in JOB_EMPLOYEE
6(b)(ii) [3 marks]
1 mark each • Select SUM of Amount • From the correct table and one correct condition • Remaining correct condition Example: SELECT SUM(Amount) FROM INVOICE WHERE Paid = "Y" AND DateSent >= #01/01/2023# AND DateSent <= #31/12/2023#
A company makes ice cream and sells it to shops.
The ice cream is made in batches: a large quantity of one type and flavour of ice cream that is then split into smaller quantities for sale.
The company’s owner has designed a relational database, ICECREAM, to store data about their ice cream and customers.
Some of the tables in the database are given. The database is not normalised.
BATCH(BatchID, Type, Flavour, Size, SellingPrice, EndDate)
CUSTOMER(CustomerID, CompanyName, EmailAddress, TelephoneNumber)
SALE(SaleID, BatchID, CustomerID, Quantity, Date)
(a) Identify two foreign keys in the table SALE and the table that each foreign key references. 2 marks
Foreign key 1
Table name 1
Foreign key 2
Table name 2
(b) Write an SQL script to return the total quantity of ice cream sold to the customer with the ID of 0034E in the year 2023. 3 marks
Show mark scheme
4(a) [2 marks]
1 mark each: • BatchID BATCH Foreign key: , table • CustomerID CUSTOMER Foreign key: , table
4(b) [3 marks]
1 mark each: • SELECT SUM(Quantity) • FROM SALE WHERE and one correct condition • AND with remainder correct conditions e.g. SELECT SUM(Quantity) FROM SALE WHERE CustomerID = "0034E" AND Date >= #01/01/2023# AND Date <= #31/12/2023#;
4(c)(i) [5 marks]
1 mark for each bullet point • BATCH Create table with opening and closing brackets, all statements within brackets • BatchID Type Flavour , and as varchar or equivalent • …with suitable constraint(s) • Size SellingPrice EndDate as decimal, as currency, as date (or equivalent) • BatchID Primary key identified as e.g. CREATE TABLE BATCH( BatchID VARCHAR(6) NOT NULL, Type VARCHAR (20) NOT NULL, Flavour VARCHAR (20) NOT NULL, Size FLOAT, SellingPrice CURRENCY, EndDate DATE, PRIMARY KEY(BatchID) );
4(c)(ii) [4 marks]
1 mark for each bullet point each ( max 4 ) e.g. • Ice cream table with an appropriate name • … containing type, flavour, size, selling price • … with suitable primary key • BATCH … foreign key identified in that links to the primary key in ICECREAM Example table definitions – not example answer: BATCH(BatchID, IceCreamID, EndDate) ICE_CREAM(IceCreamID, Type, Flavour, Size, SellingPrice)
4(d) [2 marks]
1 mark for each definition Entity: • A real-life object that is represented as a table Attribute: • An item of data about an entity
4(e) [3 marks]
1 mark for each bullet point each ( max 3 ) • Referential integrity is enforced • … such as cascade update/delete // if the data is changed in one place it is updated in every other place • ... and ensures each foreign key has a corresponding primary key
A company is developing a website that will allow users to create an account and then play a quiz every day. The data about the users and the quizzes are stored in a database.
A user must select a unique username and enter a valid email address to create an account. All users must be over the age of 16. A new quiz is given to the users every day. Each quiz is stored in its own text file.
The database stores the filename of each quiz and the date it can be played. The user gets a score for each quiz they complete, which is stored in the database. The scores are used to give each user a rating, for example Gold.
(a) Create a 3-table design for this database normalised to Third Normal Form (3NF). 6 marks
Give your table design in the format:
TableName(PrimaryKey, Field1, Field2, …)
(b) The company is using a Database Management System (DBMS) to set up the database. 4 marks
Describe what is meant by the following DBMS features:
Data dictionary
Logical schema
(c) The company has another database, FARMING, for a different game.
The database FARMING has a table named EVENT which is shown with some sample data.
| PlayerID | EventID | Category | Points |
|---|---|---|---|
000123 |
3 |
Build |
100 |
000124 |
1 |
Grow |
36 |
000123 |
4 |
Grow |
22 |
000123 |
7 |
Create |
158 |
000125 |
3 |
Grow |
85 |
000125 |
4 |
Build |
69 |
(i) The database FARMING has a second table created named PLAYER that has the primary key PlayerID . 2 marks
The field PlayerID in EVENT needs to be set up as a foreign key to link to PlayerID in
PLAYER .
Write a Structured Query Language (SQL) script to change the table definition for EVENT
to link the foreign key to PLAYER .
(ii) Write an SQL script to return the number of events that each player has completed. 3 marks
Show mark scheme
6(a) [4 marks]
1 mark each: User table with the username as the Primary Key … containing at least email address, date of birth / age and rating Quiz table with Quiz ID or date or file name as the Primary Key. … containing at least the other field(s) not used as the PK A joining table with an appropriate name including at least fields for user identification, quiz identification and score … with an appropriate Primary Key ... and Foreign Keys matching the Primary Keys of the other two tables USER(Username, Email, DateOfBirth, Rating) QUIZ(QuizID, Date, Filename) USER_QUIZ(Username, QuizID, Score)
6(b) [2 marks]
1 mark each to max 2 for data dictionary and max 2 for logical schema: Data dictionary: Data about the data in the database // metadata Identifies the characteristics of the data that will be stored Appropriate example e.g. field names, table name, validation rules, data types, primary / foreign keys, relationships etc. Logical schema: Conceptual design Platform/database independent overview of the database Is used to design the physical structure Appropriate example e.g. Design of entities / E-R diagram / views
6(c)(i)
1 mark for each correct clause: Alter table EVENT Adding foreign key as PlayerID referencing correct table ALTER TABLE EVENT ADD FOREIGN KEY(PlayerID)REFERENCES PLAYER(PlayerID);
6(c)(ii) [3 marks]
1 mark each: Selecting PlayerID from EVENT Counting EventID Grouping by the PlayerID Example: SELECT PlayerID, COUNT(EventID) FROM EVENT GROUP BY PlayerID;
(a) State what is meant by the following terms in a relational database model. 3 marks
Entity
Primary key
Referential integrity
(b) Authentication is one method a Database Management System (DBMS) can use to improve the security of a database. 4 marks
Describe other methods that a DBMS can use to improve the security of a database.
Show mark scheme
2(a) [3 marks]
1 mark for each term ( max 3 ) Entity: • An object about which data can be stored Primary key: • The unique attribute / combination of attributes used to identify the record / tuple Referential integrity: • Makes sure that if data is changed in one place the change is reflected in all related records - cascading update/delete • Makes sure that data that does not exist cannot be referenced • Ensures that every foreign key has a corresponding primary key // A logical dependency of a foreign key on a primary key • Ensures that the data in the database is consistent / up to date • Prevents records from being added/deleted/modified incorrectly • Makes sure any queries return accurate and complete results
2(b) [4 marks]
1 mark for each bullet point ( max 4 ) Max 2 if no descriptions • Backup / recovery procedures • … automatically takes copies of the database and store off site on a regular basis / weekly, etc. • ... so that the data can be recovered if lost • Use of access rights • … some users are given different access permissions to different tables • ... read/write, read only, full access, etc. • Views • … different users are able to see different parts of the database • ... only see what users need to see // by example • Record and table locking • … prevents simultaneous access to data • ... so updates are not lost // data is not overwritten • Encryption • ... the data is turned into ciphertext • ... so it cannot be understood without a decryption key
2(c) [4 marks]
1 mark for each bullet point ( max 4 ) • Identify repeating groups of attributes ... • ... Subject and SubjectCode • Ensure each field is atomic • ... StudentName should be split into e.g. FirstName and LastName • Identify the primary key for the table
An organisation uses a database to store data about the types of bird that people have seen.
(a) The database is managed using a Database Management System (DBMS).
(i) State what is meant by a data dictionary and give one example of an item typically found in a data dictionary. 2 marks
Definition
Example
(ii) State what is meant by data integrity and give one example of how this is implemented in a database. 2 marks
Definition
Example
Show mark scheme
2(a)(i)
1 mark for definition: Data about the data in the database // data about the structure of the database // metadata for a database 1 mark for a suitable example Examples: table names data types field names
2(a)(ii) [2 marks]
1 mark for definition Methods of making sure the data is consistent 1 mark for example Examples: Enforcing referential integrity If data in one table is deleted/edited all tables are updated // cascading update/delete Validation/verification rules
2(b)(i) [2 marks]
1 mark for each field name and table Foreign key Database table BirdID BIRD_SEEN PersonID BIRD_SEEN
2(b)(ii) [1 mark]
1 mark for all 3 correct lines Normal Form Definition First Normal Form All fields are fully (1NF) dependent on the primary key. Second Normal There are no repeating Form (2NF) groups of attributes. Third Normal Form There are no partial (3NF) dependencies.
2(b)(iii) [5 marks]
1 mark each CREATE TABLE start and end bracket Bird ID as CHAR/VARCHAR Name and size as VARCHAR/CHAR Bird ID as primary key Example answer: CREATE TABLE BIRD_TYPE( BirdID CHAR(4) NOT NULL, Name VARCHAR(9), Size VARCHAR(6), PRIMARY KEY (BirdID) );
2(b)(iv)
1 mark for each correctly completed space SELECT BIRD_TYPE.Size, COUNT (BIRD_TYPE.BirdID) AS NumberOfBirds FROM BIRD_TYPE, BIRD _ SEEN WHERE BIRD _ SEEN.PersonID = "J_123" AND BIRD_TYPE.BirdID = BIRD _ SEEN.BirdID GROUP BY BIRD_TYPE.Size;
A horse riding school uses a database, Lessons, to store data about lesson bookings.
This database is created and managed using a Database Management System (DBMS).
(a) The table contains names and descriptions of DBMS features and tools. 4 marks
Complete the table by writing down the missing names and descriptions.
| Name | Description |
|---|---|
| Data dictionary | ______ |
| Query processor | ______ |
| A model of a database that is not specific to one DBMS. | |
| A software tool that allows the user to create items such as tables, forms and reports. |
(b) Explain the reasons why referential integrity is important in a database. 3 marks
Show mark scheme
2(a) [4 marks]
1 mark for each correct feature or description Feature Description Data dictionary Data about the data in the database // data about the structure of the database // metadata for a database Query processor Software that allows the user to enter criteria, then finds and returns the appropriate result // software that processes and executes queries written in SQL Logical schema A model of a database that is not specific to one DBMS Developer interface A software tool that allows the user to create items such as tables, forms and reports
2(b) [3 marks]
1 mark each to max 3 Referential Integrity makes sure data is consistent Referential Integrity makes sure all data is up-to-date Referential integrity ensures that every foreign key has a corresponding primary key Referential Integrity prevents records from being added / deleted / modified incorrectly Referential Integrity makes sure that if data is changed in one place the change is reflected in all related records Referential Integrity makes sure any queries return accurate and complete results
2(c)(i) [2 marks]
1 mark each to max 2 Presence check to make sure that the (rider level) is entered Look-up / Existence check to make sure the rider level is only Beginner, Intermediate or Advanced Length check to make sure the rider level entered is either 8 or 12 characters Type check to make sure the rider level is alphanumeric
2(c)(ii) [4 marks]
1 mark each SELECT field Name FROM table HORSE WHERE with Intermediate / Beginner OR with Beginner / Intermediate Example answer: SELECT Name FROM HORSE WHERE HorseLevel = "Intermediate" OR HorseLevel = "Beginner";
2(c)(iii) [4 marks]
1 mark each SUM should be COUNT // SELECT COUNT(STUDENT.RiderLevel) The WHERE statement needs the table names before each field name // WHERE STUDENT.StudentID = LESSON.StudentID The OR should be AND // AND Date = #09/09/2023# Beginner is missing the speech marks // STUDENT.RiderLevel = "Beginner";
A shop sells plants to customers. The shop manager has a relational database to keep track of the sales.
The database, PLANTSALES, has the following structure:
PLANT(PlantName, QuantityInStock, Cost)
CUSTOMER(CustomerID, FirstName, LastName, Address, Email)
PURCHASE(PurchaseID, CustomerID)
PURCHASE_ITEM(PurchaseID, PlantName, Quantity)
(a) The database is normalised.
(i) The table lists the following three stages of normalisation: 2 marks
The first stage is from a database that is not normalised (0NF) to First Normal Form (1NF).
The second stage is from 1NF to Second Normal Form (2NF).
The third stage is from 2NF to Third Normal Form (3NF).
| Tick () one box in each row to identify | the appropriate stage for each task. | ||
|---|---|---|---|
| Task | Normalisation stage | Normalisation stage | Normalisation stage |
| Task | 0NF to 1NF | 1NF to 2NF | 2NF to 3NF |
| Remove any partial key dependencies | |||
| Remove any repeating groups of attributes | |||
| Remove any non-key dependencies |
(ii) Draw an entity-relationship (E-R) diagram for the database PLANTSALES . 3 marks
PLANT
CUSTOMER
PURCHASE_ITEM PURCHASE
(b) The shop manager uses a Database Management System (DBMS). 3 marks
Describe the purpose and contents of the data dictionary in the DBMS.
(c) The shop manager uses both Data Definition Language (DDL) and Data Manipulation Language (DML) statements to create and search the database.
(i) Complete the DML statements to return the total number of items purchased with the purchase ID of 3011A. 4 marks
SELECT SUM( ______ )
FROM
WHERE ______ = ______ ;
(ii) Write DDL statements to include a field in the table PURCHASE to store the date of the order. 3 marks
Show mark scheme
6(a)(i) [2 marks]
1 mark for 1 tick in the correct place 2 marks for all 3 ticks correct Normalisation stage Task 0NF to 1NF to 2NF to 1NF 2NF 3NF Remove any partial key dependencies Remove any repeating groups of attributes Remove any non-key dependencies
6(a)(ii) [3 marks]
1 mark for each correct relationship PLANT CUSTOMER PURCHASE_ITEM PURCHASE
6(b) [4 marks]
1 mark for description of purpose Stores metadata about the database • 1 mark for each example of contents to max 2 e.g. field / attribute names • table name • validation rules • data types • primary keys // foreign keys • relationships •
6(c)(i) [3 marks]
1 mark for each correctly completed space SELECT SUM( ) Quantity FROM PURCHASE_ITEM WHERE = " ; PurchaseID 3011A"
6(c)(ii)
1 mark per bullet point ALTER TABLE PURCHASE • ADD OrderDate • Suitable data type, e.g. DATE • ALTER TABLE PURCHASE ADD OrderDate DATE;