1. Theory Fundamentals
A section of Computer Science, 9608
Listing 10 of 304 questions
A company runs activity courses. It is creating a relational database to store details of the courses it runs. The database has five tables: EMPLOYEE(EmployeeID, FirstName, LastName, Role, Language) BOOKING(BookingID, CustomerID, CourseID) CUSTOMER(CustomerID, FirstName, LastName) COURSE(CourseID, Title, Level, Date) COURSE_EMPLOYEE(CourseID, EmployeeID) Complete the entity relationship (E-R) diagram for the database. COURSE BOOKING CUSTOMER COURSE_EMPLOYEE EMPLOYEE Describe what is meant by referential integrity. Give an example from the CUSTOMER and BOOKING tables in your answer. A Data Definition Language (DDL) is used to create the structure of the database. One item that can be created is the database. Identify three other items that can be created in the database using the DDL. Part of the EMPLOYEE table is shown. EmployeeID FirstName LastName Role Language Jasmine Chen Leader French Kenton Archer Leader English Michael Roux Cook French Conrad Slavorski Leader Russian Write a Data Manipulation Language (DML) statement to return the first name and last name of all employees, who are leaders, and speak either French or English.
9608_s21_qp_11
THEORY
2021
Paper 1, Variant 1
View
A charity has a relational database, CHARITY_PROJECT. The database has three tables to store information about the charity’s employees, the projects and the project teams. EMPLOYEE(EmployeeID, FirstName, LastName, Salary, DOB) PROJECT(ProjectID, Name, Location, ProjectStartDate) PROJECT_TEAM(EmployeeID, ProjectID, JoinedProjectDate) Complete the entity relationship (E-R) diagram to show the relationships between these tables. EMPLOYEE PROJECT PROJECT_TEAM Explain how primary and foreign keys are used to link the tables in CHARITY_PROJECT. The database needs to store the gender of each employee (either M for Male or F for Female). Write a Data Definition Language (DDL) statement to add the attribute Gender to the EMPLOYEE table. Part of the EMPLOYEE table is shown: EmployeeID FirstName LastName Salary DOB Gender Jasmine Chen 25 000 25/12/2000 F Kenton Archer 20 000 01/04/1993 M Michael Roux 10 000 10/03/1990 M Conrad Slavorski 15 000 30/03/1989 M Each employee is paid a salary in dollars ($). Write a Data Manipulation Language (DML) statement to return the first name, last name and salary of all employees that are paid more than $17 500.
9608_s21_qp_13
THEORY
2021
Paper 1, Variant 3
View
An employment agency keeps records of its contracts with employers and workers in a relational database. These are some of the tables in the database: EMPLOYER(EmpId, EmpName, EmpAddress, EmpPhoneNumber) WORKER(WkId, WkFirstName, WkLastName, WkAddress, WkPhoneNumber) CONTRACT_TYPE(ConTypeId, ConName) CONTRACT(ConId, ConTypeId, EmpId, WkId, RefConNumber) Complete the entity-relationship (E-R) diagram for this part of the database. EMPLOYER WORKER CONTRACT CONTRACT_TYPE Write a Data Definition Language (DDL) statement to change the CONTRACT table to remove the attribute RefConNumber. The following table has examples of DDL and Data Manipulation Language (DML) statements. Tick (✓) one box in each row to identify whether each statement is an example of DML or DDL. Statement DML DDL ADD PRIMARY KEY ALTER TABLE SELECT FROM INNER JOIN CREATE DATABASE The field WkPhoneNumber cannot be empty and must have a maximum of 14 characters. Describe two ways the field WkPhoneNumber can be validated. State what is meant by a candidate key. Describe what is meant by a secondary key. The database is stored on a magnetic hard disk. Describe the basic internal layout and operation of a magnetic hard disk drive.
9608_w21_qp_12
THEORY
2021
Paper 1, Variant 2
View
A veterinary surgery cares for sick animals. The surgery has a file-based database that stores data about the pets, their owners, and appointments made with the surgery. The surgery wants to upgrade to a relational database. Explain the reasons why the surgery should upgrade their database. The design for the surgery database, SURGERY, is: PET(PetID, OwnerFirstName, OwnerLastName, PetName, PetBreed, PetDateOfBirth, TelephoneNumber) APPOINTMENT(AppointmentID, Date, Time, StaffID, PetID) Give one reason why the database design for SURGERY is not in Third Normal Form (3NF). The database needs to be normalised to 3NF. A pet may have more than one owner and an owner may have more than one pet. The appointment table does not need to change and has been repeated below. Give the name and attributes of three additional tables in 3NF. Identify the primary keyin each table. APPOINTMENT(AppointmentID, Date, Time, StaffID, PetID) Table 1 Table 2 Table 3 Part of the table APPOINTMENT is shown. The veterinary surgery uses Data Manipulation Language (DML) statements to search for appointments. AppointmentID Date Time StaffID PetID 02/02/2021 12:40 JK1 20CF 02/02/2021 12:40 PP2 10DT 02/02/2021 12:50 JK1 9RR 02/02/2021 13:00 JK1 7MR Identify the industry standard language that provides both DML and Data Definition Language (DDL) statements. Write a DDL statement to update the table APPOINTMENT and define AppointmentID as the primary key. Complete the DML script to display the times and Pet IDs of all appointments on 02/02/2021 with staff ID of ‘JK1’, in descending order of time. SELECT , FROM APPOINTMENT WHERE AND ORDER BY Time ; New pet owners complete a paper-based form to register their pets at the surgery. Describe two verification checks that can be carried out when the data from the paper- based form is entered into the database. Appointments can be booked between 09:00 and 16:50 on Monday to Friday. Describe the ways in which the appointment date and time can be validated to make sure they are reasonable. The surgery has five computers that can all access the database. A copy of the database is stored centrally. Complete the description of this type of network model by filling in the missing terms. The model has one that stores all the data for the surgery. The other computers are . When a user requests data, a request is sent to the . The surgery wants to keep all data secure. The surgery network is not connected to the Internet. Identify two authentication techniques the surgery could use to restrict access to the data.
9608_w20_qp_11
THEORY
2020
Paper 1, Variant 1
View
Questions Discovered
304