8.3. Data Definition Language (DDL) and Data Manipulation Language (DML)
A subsection of Computer Science, 9618, through 8. Databases
Listing 6 of 6 questions
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) Tick () one box in each row to identify whether each field is a primary key or a foreign key. Table Field name Primary key Foreign key MANAGER ManagerID SHOP ManagerID CAR RegistrationNumber CAR ShopID Describe the ways in which access rights can be used to protect the data in Javier’s database from unauthorised access. Javier uses Data Definition Language (DDL) and Data Manipulation Language (DML) statements in his database. Complete the following DML statements to return the number of cars for sale in each shop. SELECT COUNT( ) FROM ShopID Complete the DML statement to include the following car in the table CAR. Field Data RegistrationNumber 123AA Make Tiger Model Lioness NumberOfMiles ShopID 12BSTREET CAR ("123AA","Tiger","Lioness",10500,"12BSTREET")
9618_w21_qp_11
THEORY
2021
Paper 1, Variant 1
View
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) The database is normalised. The table lists the following three stages of normalisation: • 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 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 Draw an entity-relationship (E-R) diagram for the database PLANTSALES. PLANT PURCHASE_ITEM PURCHASE CUSTOMER The shop manager uses a Database Management System (DBMS). Describe the purpose and contents of the data dictionary in the DBMS. The shop manager uses both Data Definition Language (DDL) and Data Manipulation Language (DML) statements to create and search the database. Complete the DML statements to return the total number of items purchased with the purchase ID of 3011A. SELECT SUM( ) FROM WHERE = ; Write DDL statements to include a field in the table PURCHASE to store the date of the order.
9618_w21_qp_12
THEORY
2021
Paper 1, Variant 2
View
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) Tick () one box in each row to identify whether each field is a primary key or a foreign key. Table Field name Primary key Foreign key MANAGER ManagerID SHOP ManagerID CAR RegistrationNumber CAR ShopID Describe the ways in which access rights can be used to protect the data in Javier’s database from unauthorised access. Javier uses Data Definition Language (DDL) and Data Manipulation Language (DML) statements in his database. Complete the following DML statements to return the number of cars for sale in each shop. SELECT COUNT( ) FROM ShopID Complete the DML statement to include the following car in the table CAR. Field Data RegistrationNumber 123AA Make Tiger Model Lioness NumberOfMiles ShopID 12BSTREET CAR ("123AA","Tiger","Lioness",10500,"12BSTREET")
9618_w21_qp_13
THEORY
2021
Paper 1, Variant 3
View
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) Describe the relationship between the two tables. Refer to the primary and foreign keys in your answer. The database uses a Data Definition Language (DDL) and Data Manipulation Language (DML). 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’. SELECT (STAFF.StaffID) FROM INNER JOIN DEVICE STAFF.StaffID = DEVICE.StaffID WHERE STAFF.FirstName = 'Ali' STAFF.LastName = 'Khan'; The table DEVICE needs a new attribute to store whether the device has been returned by the staff member, or not. Write a Structured Query Language (SQL) script to insert the new attribute into the table DEVICE. The database is in Third Normal Form (3NF). Complete the table by describing the three normal forms. Normal Form Description First Normal Form (1NF) Second Normal Form (2NF) Third Normal Form (3NF)
9618_s22_qp_13
THEORY
2022
Paper 1, Variant 3
View
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) Describe the advantages of using a relational database compared to a file-based approach. Give the highest level of Normal Form (NF) the database MARKS is in and justify your choice. Normal Form Justification Sample data to be stored in the table STUDENT_TEST is shown. StudentID TestID Mark A1 P10 A1 P10 Write a Structured Query Language (SQL) script to create the table STUDENT_TEST. Write a Structured Query Language (SQL) script to find the average mark of students in test A7. 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 . Explain how data validation and data verification can be used when a mark is entered.
9618_s22_qp_11
THEORY
2022
Paper 1, Variant 1
View
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). The table contains names and descriptions of DBMS features and tools. 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. Explain the reasons why referential integrity is important in a database. The database Lessons has the following tables: HORSE(HorseID, Name, Height, Age, HorseLevel) STUDENT(StudentID, FirstName, LastName, RiderLevel, PreferredHorseID) LESSON(LessonID, Date, Time, StudentID, HorseID, LessonContent) Dates in this database are stored in the format #DD/MM/YYYY#. The fields RiderLevel and HorseLevel can only have the values: Beginner, Intermediate or Advanced. Describe two methods of validating the field RiderLevel. Write a Structured Query Language (SQL) script to return the names of all the horses that have the horse level intermediate or beginner. The following SQL script should return the number of riders that have the rider level beginner and have a lesson booked on 09/09/2023. SELECT SUM(STUDENT.RiderLevel) AS NumberOfRiders FROM STUDENT, LESSON WHERE StudentID = StudentID OR Date = #09/09/2023# AND STUDENT.RiderLevel = Beginner; There are four errors in the script. Identify and correct each error.
9618_s23_qp_12
THEORY
2023
Paper 1, Variant 2
View
Questions Discovered
6