Identify two tables in the database that contain one or more foreign keys.
Give one attribute that is a foreign key in each table.
| Table | Foreign key | |
|---|---|---|
| 1 | ||
| 2 |
Explain the reasons why the data in the table ORDER_ITEM cannot be stored in the table ORDER.
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.
The output of the total cost must have an appropriate title.
Explain the purpose of the table CHARACTER_ITEM in the database.
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
.............................................................................................................................................
Underline the attribute, or attributes, that form the primary key in each of the tables.
USER(Username, Password, DateOfBirth)
CHARACTER(CharacterName, CharacterID, Username, Level, Money)
ITEM(ItemName, MinimumLevel, Cost)
CHARACTER_ITEM(CharacterID, ItemName)
Identify two methods the DBMS can use to protect the data in the table USER from unauthorised access.
Explain how each method protects the data.
Method 1 ...........................................................................................................................
Explanation .......................................................................................................................
...........................................................................................................................................
...........................................................................................................................................
Method 2 ...........................................................................................................................
Explanation .......................................................................................................................
...........................................................................................................................................
...........................................................................................................................................
The DBMS also supports data integrity.
Give two ways that a DBMS can support data integrity.
1 ........................................................................................................................................
...........................................................................................................................................
2 ........................................................................................................................................
...........................................................................................................................................
Write a Structured Query Language (SQL) script to count the number of items purchased by the user with the username "KAT123".
...........................................................................................................................................
...........................................................................................................................................
...........................................................................................................................................
...........................................................................................................................................
...........................................................................................................................................
...........................................................................................................................................
.....................................................................................................................................
The following changes need to be made to the character with the ID "0002":
• level changed to 3
• money changed to 10000.00
Write an SQL script to change the character’s data.
...........................................................................................................................................
...........................................................................................................................................
...........................................................................................................................................
...........................................................................................................................................
...........................................................................................................................................
.....................................................................................................................................
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.
Write a Structured Query Language (SQL) script to enter the new product into the table PRODUCT.
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
.............................................................................................................................................
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.
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 |
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
.............................................................................................................................................
One feature provided by a DBMS is a data dictionary.
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 ........................................................................................................................................
The DBMS provides a developer interface.
Explain how a database designer can make use of the developer interface.
...........................................................................................................................................
...........................................................................................................................................
...........................................................................................................................................
...........................................................................................................................................
...........................................................................................................................................
.....................................................................................................................................
Explain why the database WORKEXPERIENCE is in Third Normal Form (3NF).
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
.............................................................................................................................................
Write a Structured Query Language (SQL) script to delete all placements that have been completed.
...........................................................................................................................................
...........................................................................................................................................
...........................................................................................................................................
.....................................................................................................................................
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.
...........................................................................................................................................
...........................................................................................................................................
...........................................................................................................................................
...........................................................................................................................................
...........................................................................................................................................
.....................................................................................................................................
The database tables are repeated here:
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.
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
.............................................................................................................................................
Describe the relationship between the two tables. Refer to the primary and foreign keys in your answer.
The table CONTAINER needs an additional field to store the data for the last inspection date.
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.
Write an SQL script to return the number of containers stored in the database for the ship with the name Caledonia.
Some example data from the STAFF table is shown.
| 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.
Products are given a rating between 1 and 10 inclusive.
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.
A Database Management System (DBMS) is used to maintain and manage the database.
Describe two ways in which the DBMS can be used to ensure the security of the customer data.
Create a 3-table design for this database normalised to Third Normal Form (3NF).
Give your table design in the format:
TableName(PrimaryKey, Field1, Field2, …)
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
.............................................................................................................................................
The company is using a Database Management System (DBMS) to set up the database.
Describe what is meant by the following DBMS features:
Data dictionary ..........................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
Logical schema .........................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
The database FARMING has a second table created named PLAYER that has the primary key PlayerID.
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.
...........................................................................................................................................
...........................................................................................................................................
...........................................................................................................................................
.....................................................................................................................................
Write an SQL script to return the number of events that each player has completed.
...........................................................................................................................................
...........................................................................................................................................
...........................................................................................................................................
...........................................................................................................................................
...........................................................................................................................................
.....................................................................................................................................
Identify the relationship between EXAM and EXAM_QUESTION.
...................................................................................................................................................
.............................................................................................................................................
Sample data for the table EXAM is shown:
| 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.
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
.............................................................................................................................................
The table EXAM_QUESTION has been created but the foreign key has not been linked.
Write an SQL script to update EXAM_QUESTION and link the foreign key to EXAM.
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
.............................................................................................................................................
The database also needs to store data about the students, the exams the students have taken and the marks the students achieved in each question of each exam.
Describe the additional tables that will need to be included in the database and explain how all the tables in the database will be linked.
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
.............................................................................................................................................
Sample data for the table PERFORMANCE is shown:
| 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.
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.
The result needs to include the show name and a suitable field name for the number of times it is scheduled.
Customers give their first name, last name and email address when they are making a booking. One booking can include multiple seats.
Describe the additional tables that will need to be included in the database and explain how these tables will be linked within the database.
Give one limitation of using a file-based approach to store the data and explain how a relational database addresses this limitation.
The table shows sample data for the table REPAIR_PART.
| PartID | RepairNumber | Quantity |
|---|---|---|
| ACD128SA | 0022 | 3 |
| PPOR543DWW | 0022 | 1 |
| TR453 | 0023 | 1 |
| PPOR543DWW | 0023 | 2 |
| WED5 | 0024 | 5 |
Write a Structured Query Language (SQL) script to define the table REPAIR_PART.
Include constraints (restrictions) on the data that can be entered into each field where appropriate.
Suppliers send invoices to the company for the parts that are used. A new table, INVOICE, stores the data about each invoice and whether it has been paid or not.
The design for the table INVOICE is shown:
INVOICE(InvoiceID, SupplierID, AmountDue, Paid, DatePaid)
The table shows sample data for the table INVOICE.
| InvoiceID | SupplierID | AmountDue | Paid | DatePaid |
|---|---|---|---|---|
| 000001 | JK675 | 22.50 | TRUE | 01/01/2024 |
| 000002 | WR443 | 358.99 | FALSE | |
| 000003 | JK675 | 10.21 | FALSE |
Write an SQL script to return the total amount due to the supplier with the ID of JK675 for all the invoices that have not currently been paid.
Complete the table by writing a definition for each of the database terms.
| Term | Definition |
|---|---|
| Referential integrity | |
| Candidate key | |
| Tuple |