The relational database ASTRONOMY is used to store data about telescopes, the companies that own the telescopes and the photographs taken by the telescopes. The database has these three tables: COMPANY(TelephoneNumber, CompanyID, CompanyName) PHOTOGRAPH(PhotoID, TelescopeID, DateTaken, TimeTaken, Elevation) TELESCOPE(TelescopeID, CompanyID, SerialNumber) Complete the following table by writing the correct answer for each item. Item Answer a suitable field for the primary key in COMPANY a candidate key in TELESCOPE the degree of relationship between TELESCOPE and PHOTOGRAPH A Database Management System (DBMS) has several features. Identify the feature that describes the relationship between data and its structure. Complete the SQL script to return the total number of telescopes owned by the company whose ID begins with HW. SELECT ( ) FROM TELESCOPE WHERE LIKE ; Write the SQL script to add one field to the table PHOTOGRAPH to store the resolution of the photograph, e.g. 1920 × 1068. The photographs are stored as bitmap images. Complete the statements about bitmap images by writing the missing words. The of a bitmap image is the number of bits that are used to store each pixel. Metadata about the image is stored in the of the file. Describe the purpose of a query processor in a DBMS.

9618_w22_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 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) Complete the entity-relationship (E-R) diagram. ACTOR FILM_FACT FILM_ACTOR A composite primary key consists of two or more attributes that together form the primary key. Explain why the table FILM_ACTOR has a composite primary key. Complete the SQL script to return the IDs of all the actors in the film with the title Cinderella. SELECT FROM FILM_ACTOR INNER JOIN ON FILM_FACT.FilmID = WHERE FILM_FACT.FilmTitle = ; Write an SQL script to count the number of films that were released in January 2022. A Database Management System (DBMS) is used to create and manipulate the database. Complete the descriptions of the features and tools found in a DBMS using the given terms. Not all terms will be used. Boolean data dictionary data redundancy field names input interface logical schema normalisation operating system output primary keys query structure A DBMS provides data management. This includes the development of a that stores information about the data stored, such as and . The uses methods, such as an E-R diagram, to show the structure of the database and its relationships. The processor allows a user to perform searches to find specific data. The DBMS also provides a developer that allows the user to create tables, forms and reports.

9618_s22_qp_12

THEORY

2022

Paper 1, Variant 2

View

A shop rents cars to customers. The shop uses a relational database to store information about the rentals. Describe two ways in which a relational database addresses the limitations of a file-based approach. Complete the table by writing the missing term or description for each database feature. Term Description An object that data is stored about. Tuple Secondary key A field in one table that is linked to a primary key in another table. The car rental database is not normalised. The current database design is: BOOKING(CarRegistration, StartDate, EndDate, CarModel, CarColour, CustomerFirstName) CUSTOMER(CustomerFirstName, CustomerLastName, EmailAddress, TelephoneNumber) Write a normalised database design for this database. All tables must be in Third Normal Form (3NF). Use the field names given and underline the primary key fields. The data is validated and verified when it is entered into the database. The car registration number must be: 1 letter, followed by 3 numbers, followed by 2 letters. For example, A123AA is valid but A12AA is invalid. One way that a registration number can be validated is by using a presence check to make sure the registration number has been entered. Describe two other ways that the car registration number can be validated. Describe two ways that the car registration number can be verified when it is entered into the database. State why the car registration number might be incorrect even after it has been validated and verified.

9618_s23_qp_13

THEORY

2023

Paper 1, Variant 3

View

A relational database, GARDEN, has the following tables: OWNER(OwnerID, FirstName, TelephoneNo, TreeID, TreePosition) TREE(TreeID, ScientificName, MaxHeight, FastGrowing) The database is not in Third Normal Form (3NF). Explain how the database can be normalised to 3NF. Write the Structured Query Language (SQL) script to add a new record in the table TREE to store the following data: Attribute Value TreeID LOW_1276 ScientificName Salix_Alba MaxHeight 30.00 FastGrowing TRUE State what is meant by a candidate key in a relational database. Describe, using an example, what is meant by a data dictionary. Describe what is meant by a logical schema.

9618_w22_qp_12

THEORY

2022

Paper 1, Variant 2

View

Raj owns houses that other people rent from him. He has a database that stores details about the people who rent houses, and the houses they rent. The database, HOUSE_RENTALS, has the following structure: CUSTOMER(CustomerID, FirstName, LastName, DateOfBirth, Email) HOUSE(HouseID, HouseNumber, Road, Town, Bedrooms, Bathrooms) RENTAL(RentalID, CustomerID, HouseID, MonthlyCost, DepositPaid) Give the definition of the following database terms, using an example from the database HOUSE_RENTALS for each definition. Term Definition and example Field Entity Foreign key Tick (3) one box to identify whether the database HOUSE_RENTALS is in Third Normal Form (3NF) or not in 3NF. Justify your choice using one or more examples from the database HOUSE_RENTALS. In 3NF Not in 3NF Justification Example data from the table RENTAL are given: RentalID CustomerID HouseID MonthlyCost DepositPaid 15B5L 1000.00 Yes 3F 687.00 No 12AB 550.00 Yes 444.50 Yes Complete the following Data Definition Language (DDL) statement to define the table RENTAL. CREATE ( RentalID INTEGER NOT NULL, CustomerID INTEGER NOT NULL, HouseID (5) NOT NULL, MonthlyCost NOT NULL, DepositPaid BOOLEAN NOT NULL, (RentalID) ); Write a Data Manipulation Language (DML) script to return the first name and last name of all customers who have not paid their deposit.

9618_s21_qp_12

THEORY

2021

Paper 1, Variant 2

View

An organisation uses a database to store data about the types of bird that people have seen. The database is managed using a Database Management System (DBMS). State what is meant by a data dictionary and give one example of an item typically found in a data dictionary. Definition Example State what is meant by data integrity and give one example of how this is implemented in a database. Definition Example The database, Birds, stores information about the types of bird and the people who have seen them. Data about each bird seen is stored with its location and data about the person who saw the bird. Database Birds has the following tables: BIRD_TYPE(BirdID, Name, Size) BIRD_SEEN(SeenID, BirdID, Date, Location, PersonID) PERSON(PersonID, FirstName, LastName, EmailAddress) Complete the table by identifying two foreign keys and the database table where each is found. Foreign key Database table The database Birds has been normalised. Draw one line from each Normal Form to the most appropriate definition. Normal Form Definition All fields are fully dependent on the primary key. There are no repeating groups of attributes. There are no partial dependencies. First Normal Form (1NF) Second Normal Form (2NF) Third Normal Form (3NF) Part of the database table BIRD_TYPE is shown: BirdID Name Size Blackbird Medium Jay Large Raven Large Robin Small The database only supports these data types: • character • varchar • Boolean • integer • real • date • time Write a Structured Query Language (SQL) script to define the table Bird_Type. The database tables are repeated here for reference: BIRD_TYPE(BirdID, Name, Size) BIRD_SEEN(SeenID, BirdID, Date, Location, PersonID) PERSON(PersonID, FirstName, LastName, EmailAddress) Complete the SQL script to return the number of birds of each size seen by the person with the ID of J_123. SELECT BIRD_TYPE.Size, (BIRD_TYPE.BirdID) AS NumberOfBirds FROM BIRD_TYPE, WHERE = "J_123" AND BIRD_TYPE.BirdID = BIRD_TYPE.Size;

9618_s23_qp_11

THEORY

2023

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

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

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

---