8.3 DDL & DML (SQL)
AS Level · 22 questions found
What this topic covers
Section titled “What this topic covers”- DDL creates/modifies structure; DML queries/maintains data; SQL is the industry standard
- DDL: CREATE DATABASE, CREATE TABLE with data types (CHARACTER, VARCHAR, BOOLEAN, INTEGER, REAL, DATE, TIME)
- ALTER TABLE; PRIMARY KEY; FOREIGN KEY…REFERENCES
- DML queries: SELECT…FROM, WHERE, ORDER BY, GROUP BY, INNER JOIN, SUM, COUNT, AVG
- DML maintenance: INSERT INTO, DELETE FROM, UPDATE
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 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;
An assessment board wants to store the marks students achieved in exams in a database named
RECORDS .
Part of the database design includes these two tables:
EXAM(ExamID, Subject, Level, TotalMarks)
EXAM_QUESTION(ExamQuestionID, ExamID, QuestionNumber, Question, MaxMark)
(a) Identify the relationship between EXAM and EXAM_QUESTION . 1 mark 3 marks
| Sample data for the ExamID | table EXAM is shown: Subject | Level | TotalMarks |
|---|---|---|---|
ExamID |
Subject |
Level |
TotalMarks |
00956124 |
Computer Science |
2 |
75 |
00956125 |
Computer Science |
3 |
120 |
00956126 |
Mathematics |
2 |
100 |
00956127 |
Mathematics |
3 |
150 |
00956128 |
Physics |
2 |
70 |
00956129 |
Physics |
3 |
80 |
Write a Structured Query Language (SQL) script to define the table EXAM .
Show mark scheme
4(a) [3 marks]
1 mark for: 1-to-many
4(b)
1 mark each: Creating table EXAM with opening and closing brackets All fields with appropriate data types and commas at end of lines ExamID as primary key Example: CREATE TABLE EXAM( ExamID varchar NOT NULL, Subject varchar, Level int, TotalMarks int, PRIMARY KEY(ExamID) );
4(c) [2 marks]
1 mark each: Altering table EXAM_QUESTION Linking ExamID to ExamID in EXAM Example. ALTER TABLE EXAM_QUESTION ADD FOREIGN KEY (ExamID) REFERENCES EXAM(ExamID);
4(d) [5 marks]
1 mark each to max 5 : STUDENT table identified with suitable Primary Key A linking table between STUDENT and EXAM with suitable Primary Key and appropriate name … that includes the Primary Key of the STUDENT table as a Foreign Key to join with STUDENT ... and includes the Primary Key of the EXAM table as a Foreign Key to join with EXAM A linking table between STUDENT and EXAM_QUESTION with suitable Primary Key and appropriate name … that includes the Primary Key of Table 2 as a Foreign Key to join with Table 2 … that stores the ExamQuestionID and the mark for that question
A theatre wants to use a database to store data about the shows that are scheduled, their customers and the seats that the customers have booked.
In the theatre:
Each show can take place on multiple dates.
Some dates can have more than one performance.
There are multiple rows of seats.
Each seat can be individually booked by its row letter and seat number, for example row E seat 2.
Part of the database design includes these tables:
SHOW(ShowID, Title, Duration)
SEAT(SeatID, RowLetter, SeatNumber)
PERFORMANCE(PerformanceID, ShowID, ShowDate, StartTime)
(a) Identify the relationship between the tables PERFORMANCE and SHOW. 1 mark
(b) Sample data for the table PERFORMANCE is shown: 4 marks
| PerformanceID | ShowID | ShowDate | StartTime |
|---|---|---|---|
| 0001 | MK12 | 5/5/2025 | 13:00 |
| 0002 | MK12 | 5/5/2025 | 19:30 |
| 0003 | MK12 | 6/5/2025 | 19:00 |
| 0004 | OP3 | 7/5/2025 | 18:30 |
| 0005 | OP3 | 8/5/2025 | 18:30 |
| 0006 | OP3 | 9/5/2025 | 13:00 |
Write a Structured Query Language (SQL) script to define the table PERFORMANCE.
(c) Write an SQL script to return the number of times each show is scheduled. For example, in the sample data in part (b), the show MK12 is scheduled three times. 4 marks
The result needs to include the show name and a suitable field name for the number of times it is scheduled.
(d) Customers give their first name, last name and email address when they are making a booking. One booking can include multiple seats. 5 marks
Describe the additional tables that will need to be included in the database and explain how these tables will be linked within the database.
Show mark scheme
4(a) [4 marks]
1 mark for: many to 1 // there are many performances of each show
4(b) [4 marks]
1 mark each for: Creating table with opening and closing brackets PERFORMANCE Setting all four attributes with appropriate data types Setting as primary key PerformanceID Setting as foreign key referencing table ShowID SHOW Example: CREATE TABLE PERFORMANCE( PerformanceID varchar NOT NULL, ShowID varchar, ShowDate Date, StartTime Time, PRIMARY KEY(PerformanceID), FOREIGN KEY(ShowID) REFERENCES SHOW(ShowID) );
4(c)
1 mark each for: Selecting of an attribute in table with suitable name COUNT PERFORMANCE clause FROM Joining tables Grouping by the title and selecting the title Example 1: SELECT SHOW.Title, Count(PERFORMANCE.PerformanceID) AS NumberOfShowings FROM PERFORMANCE, SHOW WHERE PERFORMANCE.ShowID = SHOW.ShowID GROUP BY SHOW.Title; Example 2: SELECT SHOW.Title, Count(PERFORMANCE.PerformanceID) AS NumberOfShowings FROM PERFORMANCE INNER JOIN SHOW ON PERFORMANCE.ShowID = SHOW.ShowID GROUP BY SHOW.Title;
4(d) [5 marks]
1 mark each to max 5 : e.g. CUSTOMER table identified with suitable Primary Key and appropriate name … and other suitable fields including name and email BOOKING TABLE identified with suitable Primary Key and appropriate name … that stores the Primary Key of the CUSTOMER table as a Foreign Key to join with CUSTOMER table … and stores the Primary Key of the PERFORMANCE table as a Foreign Key to join with PERFORMANCE table A linking table between Table 2 and SEAT with suitable Primary Key and appropriate name … that includes the Primary Key of Table 2 as a Foreign Key to join with Table 2 … that stores the SeatID.
A Local Area Network (LAN) uses a bus topology.
Describe how Carrier Sense Multiple Access / Collision Detection (CSMA / CD) is used in a bus network. 4 marks
Show mark scheme
8 [4 marks]
1 mark for bullet point ( max 4 ): • CSMA/CD is a protocol used to detect and prevent collisions in a bus topology • before transmitting, a device checks if the channel is busy • If the channel is busy the device waits // if the channel is free the data is sent • because there is more than one computer connected to the same transmission medium • ... two workstations can start to transmit at the same time, causing a collision • If a collision is detected by the device, transmission is aborted / a jamming signal is transmitted • both devices wait a (different) random time and then try again
The following table shows part of the instruction set for a processor. The processor has one general purpose register, the Accumulator (ACC), and an Index Register (IX).
| Instruction | Explanation | |
|---|---|---|
| Opcode | Operand | Operand |
LDM |
#n |
Immediate addressing. Load the number n to ACC |
LDD |
<address> |
Direct addressing. Load the contents of the location at the given address to ACC |
LDX |
<address> |
Indexed addressing. Form the address from + the contents of the index register. Copy the contents of this calculated address to ACC |
LDR |
#n |
Immediate addressing. Load the number n to IX |
STO |
<address> |
Store the contents of ACC at the given address |
ADD |
<address> |
Add the contents of the given address to the ACC |
ADD |
#n |
Add the denary number n to the ACC |
INC |
<register> |
Add 1 to the contents of the register (ACC or IX) |
JMP |
<address> |
Jump to the given address |
CMP |
<address> |
Compare the contents of ACC with the contents of |
CMI |
<address> |
Indirect addressing. The address to be used is at the given address. Compare the contents of ACC with the contents of this second address |
JPE |
<address> |
Following a compare instruction, jump to if the compare was True |
JPN |
<address> |
Following a compare instruction, jump to if the compare was False |
END |
Return control to the operating system | |
| can be an absolute or symbolic address # denotes a denary number, e.g. #123 B denotes a binary number, e.g. B01001101 |
can be an absolute or symbolic address # denotes a denary number, e.g. #123 B denotes a binary number, e.g. B01001101 |
can be an absolute or symbolic address # denotes a denary number, e.g. #123 B denotes a binary number, e.g. B01001101 |
Address Instruction
…
…
| currently in me d time. | emory u | using | g the trace table, stopping when line 90 is | ||||||
|---|---|---|---|---|---|---|---|---|---|
| Instruction address |
ACC | IX | Memory address | Memory address | Memory address | Memory address | Memory address | Memory address | Memory address |
| Instruction address |
ACC | IX | 100 | 101 | 102 | 103 | 110 | 111 | 112 |
0 |
0 |
112 |
4 |
1 |
4 |
0 |
|||
| 5 marks | |||||||||
 |
(b) The following table shows another part of the instruction set for the processor.
| Instruction | Explanation | |
|---|---|---|
| Opcode | Operand | Operand |
AND |
#n |
Bitwise AND operation of the contents of ACC with the operand |
AND |
<address> |
Bitwise AND operation of the contents of ACC with the contents of |
XOR |
#n |
Bitwise XOR operation of the contents of ACC with the operand |
XOR |
Bn |
Bitwise XOR operation of the contents of ACC with the binary number n |
XOR |
<address> |
Bitwise XOR operation of the contents of ACC with the contents of |
OR |
#n |
Bitwise OR operation of the contents of ACC with the operand |
OR |
<address> |
Bitwise OR operation of the contents of ACC with the contents of |
LSL |
#n |
Bits in ACC are shifted logically n places to the left. Zeros are introduced on the right-hand end |
LSR |
#n |
Bits in ACC are shifted logically n places to the right. Zeros are introduced on the left-hand end |
| can be an absolute or symbolic address # denotes a denary number, e.g. #123 B denotes a binary number, e.g. B01001101 |
can be an absolute or symbolic address # denotes a denary number, e.g. #123 B denotes a binary number, e.g. B01001101 |
can be an absolute or symbolic address # denotes a denary number, e.g. #123 B denotes a binary number, e.g. B01001101 |
| s of memory addresses 50 an | nd 51 are shown: |
|---|---|
| Memory address | Data value |
50 |
01001101 |
51 |
10001111 |
(i) The current contents of the ACC are:
0 1 0 1 0 0 1 1 Show the contents of the ACC after the execution of the following instruction.
XOR B00011111 1 mark
(ii) The current contents of the ACC are:
0 1 0 1 0 0 1 1 Show the contents of the ACC after the execution of the following instruction.
AND 50 1 mark
(iii) The current contents of the ACC are:
0 1 0 1 0 0 1 1 Show the contents of the ACC after the execution of the following instruction.
LSL #3 1 mark
(iv) The current contents of the ACC are:
0 1 0 1 0 0 1 1 Show the contents of the ACC after the execution of the following instruction.
OR 51 1 mark
(c) Write the register transfer notation for each of the stages in the fetch-execute cycle described in the table. 3 marks
| Description | Register transfer notation |
|---|---|
| Copy the address of the next instruction into the Memory Address Register. |
|
| Increment the Program Counter. | |
| Copy the contents of the Memory Data Register into the Current Instruction Register. |
Show mark scheme
7(a) [5 marks]
1 mark for each set of highlighted rows. Memory address Instruction IX ACC address 100 101 102 103 110 111 112 0 0 112 4 1 4 0 75 0 76 1 77 78 79 80 84 2 85 2 86 0 87 1 88 1 89 1 90 76 4 77 78 79 80 81 6 82 6 83 86 1 87 2 88 2 89 2 90 0100 1100
7(b)(i) [1 mark]
0100 0001
7(b)(ii) [1 mark]
1001 1000
7(b)(iii) [1 mark]
1101 1111
7(c) [3 marks]
1 mark for each correct row: Description Register transfer notation Copy the address of the next instruction into MAR [PC] the Memory Address Register. Increment the Program Counter. PC [PC] + 1 Copy the contents of the Memory Data CIR [MDR] Register into the Current Instruction Register.
(a) The diagram shows the hardware and software used to retrieve files stored on the cloud. 3 marks
Complete the following table by writing the answer for each row.
| Answer | |
|---|---|
| The name of deviceA that allows the laptop to connect to the internet |
|
| A type of cloud,X | |
| An example of an application,B, that can run on the cloud |
(b) Give one advantage and two disadvantages of transmitting data using satellites instead of copper cables. 3 marks
Advantage 1
Disadvantage 1
Disadvantage 2
(c) Local Area Networks (LANs) can be made up of several subnetworks.
(i) Give two benefits of dividing a network into subnetworks by subnetting the LAN. 2 marks
1
2
(ii) A subnet mask is used when subnetting a LAN. 2 marks 2 marks
Two devices on the LAN are located in different subnetworks.
The IP addresses and corresponding subnet masks are shown:
| Device IP address | Subnet mask |
|---|---|
10.10.12.1 |
255.0.0.0 |
192.168.12.4 |
255.255.255.0 |
Identify the following network ID and host ID.
The network ID for the device with the IP address 10.10.12.1
The host ID for the device with the IP address 192.168.12.4
8 (a) (i) Explain why some programs are distributed under an open source licence.
(ii) Explain how a programmer benefits from distributing a program under a commercial licence. 2 marks
(b) A commercial program for a vehicle repair garage includes an Artificial Intelligence (AI) 2 marks
module that can diagnose faults and suggest repairs.
Describe one economic impact the AI module may have on the garage. 9 (a) (i) Convert the unsigned binary value into hexadecimal.
10010011
Answer [1]
(ii) Convert the unsigned binary value into denary.
10010011
Answer [1]
(b) State two benefits of using Binary Coded Decimal (BCD) to represent values. 2 marks 3 marks
Benefit 1
Benefit 2 10 (a) Explain the importance of feedback in a control system. (b) (i) Identify one sensor that could be used in a car alarm system. 2 marks
Justify your choice.
Sensor
Justification
(ii) The car alarm is an example of an embedded system. 3 marks
Describe the characteristics of an embedded system.
Show mark scheme
7(a) [3 marks]
1 mark for each correct answer: The name of device A that allows Router the laptop to connect to the internet A type of cloud, X Public (cloud) An example of an application, B, Email / Graphics / Word processor / that can run on the cloud, Spreadsheet / Game / Database, etc.
7(b) [3 marks]
1 mark for a correct advantage: • not fixed to a single location • allows access in remote / rural areas 1 mark for each correct disadvantage ( max 2 ): • high latency / lag / slow to connect • more expensive than wired methods, as need extra equipment • signal is affected by bad weather • the transmission speed is slower than fixed line broadband • direct line of sight needed
7(c)(i) [2 marks]
1 mark for each bullet point ( max 2 ) Examples: • improves security • reduces congestion • allows extension of the network / devices attached • aids day-to-day management • improves performance
7(c)(ii) [2 marks]
1 mark for each correct answer: • network ID = 10 • host ID = 4
A teacher uses a relational database, MARKS, to store data about students and their test marks.
The database has the following structure:
STUDENT(StudentID, FirstName, LastName)
TEST(TestID, Description, TotalMarks)
STUDENT_TEST(StudentID, TestID, Mark)
(a) Describe the advantages of using a relational database compared to a file-based approach. 4 marks
(b) Give the highest level of Normal Form (NF) the database MARKS is in and justify your choice. 3 marks
Normal Form
Justification
(c) (i) Sample data to be stored in the table STUDENT_TEST is shown. 5 marks
| StudentID | TestID | Mark |
|---|---|---|
12 |
A1 |
50 |
12 |
P10 |
100 |
13 |
A1 |
75 |
14 |
P10 |
60 |
Write a Structured Query Language (SQL) script to create the table STUDENT_TEST .
(ii) Write a Structured Query Language (SQL) script to find the average mark of students in test A7. 3 marks
(d) The mark a student is awarded in a test will be entered into the database. This mark needs to be a whole number between 0 and the maximum number of marks for that test (inclusive). 4 marks
Explain how data validation and data verification can be used when a mark is entered.
Show mark scheme
4(a)
1 mark per bullet point to max 4 e.g. Reduced data redundancy // less repeated data …because each item of data is only stored once Maintains data consistency // improves data integrity …changes in one table will automatically update in another ... linked data cannot be entered differently in two tables Program-data independence …changes to the data do not require programs to be re-written Complex queries are easier to run Can provide different views ….so users can only see specific aspects of the database
4(b) [5 marks]
1 mark for 3NF 1 mark per bullet for justification to max 2 There are no repeated attributes // it is already in 2NF Each field is fully dependent on the corresponding primary key // no partial dependencies No transitive dependencies
4(c)(i)
1 mark per bullet point Create table, table name, opening and closing brackets and as integer StudentID Mark as Varchar TestID Primary key correctly set up Foreign keys correctly set up e.g. CREATE TABLE STUDENT_TEST ( StudentId INTEGER, TestID VARCHAR, Mark INTEGER, PRIMARY KEY(StudentID,TestID), FOREIGN KEY(TestID) REFERENCES TEST(TestID), FOREIGN KEY(StudentID) REFERENCES STUDENT(StudentID) );
4(c)(ii) [3 marks]
1 mark for each point AVG(Mark) SELECT and FROM STUDENT_TEST WHERE clause e.g. SELECT AVG(Mark) FROM STUDENT_TEST WHERE TestID = "A7";
4(d) [4 marks]
1 mark per bullet point to max 3 for validation e.g. range check to make sure it is between 0 and max marks presence check to make sure a mark is entered type check to make sure an integer value is entered 1 mark per bullet point to max 2 for verification e.g. double entry - enter the mark twice and the computer compares them visual check – manually compare the mark entered with the mark on the input document
A database, FILMS, stores information about films and actors.
Part of the database is shown:
ACTOR(ActorID, FirstName, LastName, DateOfBirth)
FILM_FACT(FilmID, FilmTitle, ReleaseDate, Category)
FILM_ACTOR(ActorID, FilmID)
(a) Complete the entity-relationship (E-R) diagram. 2 marks
ACTOR FILM_FACT
FILM_ACTOR
(b) A composite primary key consists of two or more attributes that together form the primary key. 2 marks
Explain why the table FILM_ACTOR has a composite primary key.
Show mark scheme
5(a) [2 marks]
1 mark for each correct relationship ACTOR FILM_FACT FILM_ACTOR
5(b) [4 marks]
1 mark per point Neither key uniquely identifies each tuple by itself One actor cannot appear in the same film twice so together they are unique
5(c) [3 marks]
1 mark per correct entry SELECT FILM_ACTOR.ActorID / ActorID FROM FILM_ACTOR INNER JOIN FILM_FACT ON FILM_FACT.FilmID = FILM_ACTOR.FilmID WHERE FILM_FACT.FilmTitle = "Cinderella" ;
5(d) [6 marks]
1 mark per point COUNT and correct fieldname SELECT and FROM statements, including the table name in FROM WHERE statement e.g. SELECT COUNT(FilmID) FROM FILM_FACT WHERE ReleaseDate >= #01/01/2022# AND ReleaseDate <= #31/01/2022#; // WHERE ReleaseDate BETWEEN #01/01/2022# AND #31/01/2022#; // WHERE ReleaseDate = “January 2022”;
5(e)
1 mark for each correctly completed term data dictionary field names // primary keys primary keys //field names logical schema query interface A DBMS provides data management. This includes the development of a data dictionary that stores information about the data stored, such as field names and primary keys . The logical schema uses methods such as an E-R diagram to show the structure of the database and its relationships. The query processor allows a user to perform searches to find specific data. The DBMS also provides a developer interface that allows the user to create tables, forms and reports.
A relational database, TECHNOLOGY, stores data about the staff in a company and the computer
devices used by the staff.
The database has the following tables:
STAFF(StaffID, FirstName, LastName, DateOfBirth, JobTitle)
DEVICE(DeviceID, Type, DatePurchased, StaffID)
(a) Describe the relationship between the two tables. Refer to the primary and foreign keys in your answer. 4 marks
(b) The database uses a Data Definition Language (DDL) and Data Manipulation Language (DML).
(i) Complete the SQL script to return the number of devices stored in the database for the staff member with the first name ‘Ali’ and last name ‘Khan’. 4 marks
SELECT ______ (STAFF.StaffID)
FROM
INNER JOIN DEVICE
______ STAFF.StaffID = DEVICE.StaffID
WHERE STAFF.FirstName = 'Ali'
______ STAFF.LastName = 'Khan';
(ii) The table DEVICE needs a new attribute to store whether the device has been returned by the staff member, or not. 2 marks
Write a Structured Query Language (SQL) script to insert the new attribute into the table
DEVICE .
(c) The database is in Third Normal Form (3NF). 3 marks
Complete the table by describing the three normal forms.
| Normal Form | Description |
|---|---|
| First Normal Form (1NF) | |
| Second Normal Form (2NF) | |
| Third Normal Form (3NF) |
Show mark scheme
6(a) [4 marks]
1 mark per point Primary key in STAFF… StaffID … links to foreign key in DEVICE StaffID One staff member can have many devices Each device can only be with one member of staff
6(b)(i) [2 marks]
1 mark for each correctly completed statement SELECT COUNT (STAFF.StaffID) FROM STAFF INNER JOIN DEVICE ON STAFF.StaffId = DEVICE.StaffID WHERE STAFF.FirstName = "Ali" AND STAFF.LastName = "Khan";
6(b)(ii) [3 marks]
1 mark per bullet point ALTER TABLE DEVICE appropriate field name , appropriate data type ADD e.g. ALTER TABLE DEVICE ADD Returned Boolean;
6(c)
Normal Form Description First Normal Form (1NF) No repeating groups or repeating attributes Second Normal Form All attributes must be fully dependant on (2NF) the (composite) primary key // No partial dependencies Third Normal Form (3NF) All attributes must be fully dependent on the primary key and no other attributes // no non-key dependencies // no transitive dependencies 1 mark for each correct description
Javier owns many shops that sell cars. He employs several managers who are each in charge of
one or more shops. He uses the relational database CARS to store the data about his business.
Part of the database is shown:
SHOP(ShopID, ManagerID, Address, Town, TelephoneNumber)
MANAGER(ManagerID, FirstName, LastName, DateOfBirth, Wage)
CAR(RegistrationNumber, Make, Model, NumberOfMiles, ShopID)
2 marks
| Tick () one box in | n each row to identify whether | r each field is a pr | rimary key or a for |
|---|---|---|---|
| Table | Field name | Primary key | Foreign key |
MANAGER |
ManagerID |
||
SHOP |
ManagerID |
||
CAR |
RegistrationNumber |
||
CAR |
ShopID |
(b) Describe the ways in which access rights can be used to protect the data in Javier’s database from unauthorised access. 3 marks
(c) Javier uses Data Definition Language (DDL) and Data Manipulation Language (DML)
statements in his database.
(i) Complete the following DML statements to return the number of cars for sale in each shop. 3 marks
SELECT COUNT( ______ )
FROM
______ ShopID
(ii) Complete the DML statement to include the following car in the table CAR . 2 marks
| Field | Data |
|---|---|
RegistrationNumber |
123AA |
Make |
Tiger |
Model |
Lioness |
NumberOfMiles |
10500 |
ShopID |
12BSTREET |
______ ______ CAR
______ ("123AA","Tiger","Lioness",10500,"12BSTREET")
Show mark scheme
5(a) [3 marks]
1 mark for 2 or 3 correct ticks, 2 marks for 4 correct ticks Primary Foreign Table Field name Key (PK) Key (FK) MANAGER ManagerID SHOP ManagerID CAR RegistrationNumber CAR ShopID
5(b)
1 mark per bullet point Access rights give managers / himself access to different elements • … by having different accounts / logins • … which have different access rights e.g. read only // no access / read / • write Specific views can be assigned to himself and to the managers • … e.g. managers can only see the data for their own shop(s) •
5(c)(i) [2 marks]
1 mark per correctly completed statement SELECT COUNT( ) RegistrationNumber FROM CAR ShopID GROUP BY
5(c)(ii)
1 mark for each correct statement CAR INSERT INTO ("123AA","Tiger","Lioness",10500,"12BSTREET") VALUES
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;
Javier owns many shops that sell cars. He employs several managers who are each in charge of
one or more shops. He uses the relational database CARS to store the data about his business.
Part of the database is shown:
SHOP(ShopID, ManagerID, Address, Town, TelephoneNumber)
MANAGER(ManagerID, FirstName, LastName, DateOfBirth, Wage)
CAR(RegistrationNumber, Make, Model, NumberOfMiles, ShopID)
2 marks
| Tick () one box in | n each row to identify whether | r each field is a pr | rimary key or a for |
|---|---|---|---|
| Table | Field name | Primary key | Foreign key |
MANAGER |
ManagerID |
||
SHOP |
ManagerID |
||
CAR |
RegistrationNumber |
||
CAR |
ShopID |
(b) Describe the ways in which access rights can be used to protect the data in Javier’s database from unauthorised access. 3 marks
(c) Javier uses Data Definition Language (DDL) and Data Manipulation Language (DML)
statements in his database.
(i) Complete the following DML statements to return the number of cars for sale in each shop. 3 marks
SELECT COUNT( ______ )
FROM
______ ShopID
(ii) Complete the DML statement to include the following car in the table CAR . 2 marks
| Field | Data |
|---|---|
RegistrationNumber |
123AA |
Make |
Tiger |
Model |
Lioness |
NumberOfMiles |
10500 |
ShopID |
12BSTREET |
______ ______ CAR
______ ("123AA","Tiger","Lioness",10500,"12BSTREET")
Show mark scheme
5(a) [3 marks]
1 mark for 2 or 3 correct ticks, 2 marks for 4 correct ticks Primary Foreign Table Field name Key (PK) Key (FK) MANAGER ManagerID SHOP ManagerID CAR RegistrationNumber CAR ShopID
5(b)
1 mark per bullet point Access rights give managers / himself access to different elements • … by having different accounts / logins • … which have different access rights e.g. read only // no access / read / • write Specific views can be assigned to himself and to the managers • … e.g. managers can only see the data for their own shop(s) •
5(c)(i) [2 marks]
1 mark per correctly completed statement SELECT COUNT( ) RegistrationNumber FROM CAR ShopID GROUP BY
5(c)(ii)
1 mark for each correct statement CAR INSERT INTO ("123AA","Tiger","Lioness",10500,"12BSTREET") VALUES
Bobby and Kim are discussing databases.
(a) Bobby tells Kim that a file-based approach is usually better than a relational database. 3 marks
Explain why Bobby is incorrect.
(b) Bobby has a shop that sells products to customers. His database will store data about his customers, their payment details, orders and the products he sells. Customers will have login details to access their accounts. The database will update customers’ payment and login details without keeping any historical records. 3 marks
(i) Give one example of each of the following relationships from Bobby’s database. 1 mark
one-to-one
one-to-many
many-to-many
| dentify the relatio database. | onship that c |
|---|---|
| Relationship | Tick (3) |
| one-to-one | |
| one-to-many | |
| many-to-many |
(iii) Bobby wants to name his database SHOPORDERS . 1 mark
Write a Data Definition Language (DDL) statement to define a new database with the
name SHOPORDERS .
(c) A database has a data dictionary. 3 marks 3 marks
Give three items that are stored in a data dictionary.
1
2
3
| 8 Tick (3) one box in each row to identify the logic | gate that | each state | ement des | scribes. | |
|---|---|---|---|---|---|
| Statement | AND | NAND | NOR | XOR | OR |
| The output is 1 only when both inputs are 1 | |||||
| The output is 1 only when both inputs are different | |||||
| The output is 1 only when both inputs are 0 |
Show mark scheme
7(a) [3 marks]
1 mark per bullet point to max 3 Flat-file has more data redundancy • … because the same data is stored many times // data is stored in • different tables which are linked There is program-data dependence with flat-files • ... because any changes to the structure of the data means the programs • that access that data have to be re-written Flat-file has more data inconsistency // worse data integrity • ... because duplicated data might be stored differently //…because when • data is updated in one place, it is not updated everywhere It is not easy to perform complex searches /queries • … because a new program has to be written each time • Flat files could have a lack of privacy • … as user views cannot easily be implemented •
7(b)(i) [3 marks]
1 mark for each correct example one-to-one e.g. customer to payment details // customer to login details • one-to-many e.g. customer to order • many-to-many e.g. order to product // customer to product •
7(b)(ii) [1 mark]
1 mark Relationship Tick ( ) one-to-one one-to-many many-to-many
7(b)(iii) [1 mark]
1 mark CREATE DATABASE SHOPORDERS;
7(c) [3 marks]
1 mark per item to max 3 table name • field name // attribute • data type • type of validation • Primary Key • Foreign Key • relationships •
Bobby and Kim are discussing databases.
(a) Bobby tells Kim that a file-based approach is usually better than a relational database. 3 marks
Explain why Bobby is incorrect.
(b) Bobby has a shop that sells products to customers. His database will store data about his customers, their payment details, orders and the products he sells. Customers will have login details to access their accounts. The database will update customers’ payment and login details without keeping any historical records. 3 marks
(i) Give one example of each of the following relationships from Bobby’s database. 1 mark
one-to-one
one-to-many
many-to-many
| dentify the relatio database. | onship that c |
|---|---|
| Relationship | Tick (3) |
| one-to-one | |
| one-to-many | |
| many-to-many |
(iii) Bobby wants to name his database SHOPORDERS . 1 mark
Write a Data Definition Language (DDL) statement to define a new database with the
name SHOPORDERS .
(c) A database has a data dictionary. 3 marks 3 marks
Give three items that are stored in a data dictionary.
1
2
3
| 8 Tick (3) one box in each row to identify the logic | gate that | each state | ement des | scribes. | |
|---|---|---|---|---|---|
| Statement | AND | NAND | NOR | XOR | OR |
| The output is 1 only when both inputs are 1 | |||||
| The output is 1 only when both inputs are different | |||||
| The output is 1 only when both inputs are 0 |
Show mark scheme
7(a) [3 marks]
1 mark per bullet point to max 3 Flat-file has more data redundancy • … because the same data is stored many times // data is stored in • different tables which are linked There is program-data dependence with flat-files • ... because any changes to the structure of the data means the programs • that access that data have to be re-written Flat-file has more data inconsistency // worse data integrity • ... because duplicated data might be stored differently //…because when • data is updated in one place, it is not updated everywhere It is not easy to perform complex searches /queries • … because a new program has to be written each time • Flat files could have a lack of privacy • … as user views cannot easily be implemented •
7(b)(i) [3 marks]
1 mark for each correct example one-to-one e.g. customer to payment details // customer to login details • one-to-many e.g. customer to order • many-to-many e.g. order to product // customer to product •
7(b)(ii) [1 mark]
1 mark Relationship Tick ( ) one-to-one one-to-many many-to-many
7(b)(iii) [1 mark]
1 mark CREATE DATABASE SHOPORDERS;
7(c) [3 marks]
1 mark per item to max 3 table name • field name // attribute • data type • type of validation • Primary Key • Foreign Key • relationships •