A school stores a large amount of data. This includes student attendance, qualification, and contact details. The school’s software uses a file-based approach to store this data. The school is considering changing to a DBMS. State what DBMS stands for. Describe two ways in which the Database Administrator (DBA) could use the DBMS software to ensure the security of the student data. A feature of the DBMS software is a query processor. Describe how the school secretary could use this software. The DBMS has replaced software that used a file-based approach with a relational database. Describe how using a relational database has overcome the previous problems associated with a file-based approach. The database design has three tables to store the classes that students attend. STUDENT(StudentID, FirstName, LastName, Year, TutorGroup) CLASS(ClassID, Subject) CLASS-GROUP(StudentID, ClassID) Primary keys are not shown. There is a one-to-many relationship between CLASS and CLASS–GROUP. Describe how this relationship is implemented. Describe the relationship between CLASS-GROUP and STUDENT. Write an SQL script to display the StudentID and FirstName of all students who are in the tutor group 10B. Display the list in alphabetical order of LastName. Write an SQL script to display the LastName of all students who attend the class whose ClassID is CS1.

9608_s16_qp_11

THEORY

2016

Paper 1, Variant 1

View

A school stores a large amount of data. This includes student attendance, qualification, and contact details. The school’s software uses a file-based approach to store this data. The school is considering changing to a DBMS. State what DBMS stands for. Describe two ways in which the Database Administrator (DBA) could use the DBMS software to ensure the security of the student data. A feature of the DBMS software is a query processor. Describe how the school secretary could use this software. The DBMS has replaced software that used a file-based approach with a relational database. Describe how using a relational database has overcome the previous problems associated with a file-based approach. The database design has three tables to store the classes that students attend. STUDENT(StudentID, FirstName, LastName, Year, TutorGroup) CLASS(ClassID, Subject) CLASS-GROUP(StudentID, ClassID) Primary keys are not shown. There is a one-to-many relationship between CLASS and CLASS–GROUP. Describe how this relationship is implemented. Describe the relationship between CLASS-GROUP and STUDENT. Write an SQL script to display the StudentID and FirstName of all students who are in the tutor group 10B. Display the list in alphabetical order of LastName. Write an SQL script to display the LastName of all students who attend the class whose ClassID is CS1.

9608_s16_qp_12

THEORY

2016

Paper 1, Variant 2

View

A software company produces software and distributes it under different software licences. Four descriptions of software licences are given. Write the type of software licence that best fits each description. Use a different type of licence for each description. 1. The software can be legally used, only after a fee has been paid. Licence type 2. The source code comes with the software. If the software is modified, the edited source code must be released under the same conditions as the original software. Licence type 3. The software is free for a trial period and then a fee is requested, or expected, if the user wants to continue to use the software. Licence type 4. The source code comes with the software. The software is free to be downloaded, edited, and distributed, possibly without restriction. Licence type The software company stores information about customers and the software licences they have purchased. The company considers a file-based approach for the storage and retrieval of data. Give three limitations of a file-based approach to store the data. The software company decides to use a database to overcome the limitations of a file-based system. Some of these limitations are addressed through the logical schema. Name and describe two levels of the schema of a database. Name 1 Description Name 2 Description The database has the following tables: CUSTOMER(CustomerID, CompanyName) SOFTWARE(SoftwareID, SoftwareName, OperatingSystem, Description) LICENCE( LicenceID, CustomerID, SoftwareID, DateOfPurchase, LicenceType, Cost, ExpiryDate) Identify the type of relationship that exists between the tables CUSTOMER and LICENCE. Describe how the relationship is created between the tables CUSTOMER and LICENCE. The company needs a list of all software licences that have an expiry date on or before 31/12/2019. Write an SQL query to return the fields CustomerID, SoftwareID, LicenceType, Cost and ExpiryDate for all licences that expire on, or before 31/12/2019. Group the output by CustomerID, and in ascending order of cost.

9608_s19_qp_11

THEORY

2019

Paper 1, Variant 1

View

Moheem is creating a relational database to store data about his customers. Moheem has been told a relational database addresses some of the limitations of a file-based approach by reducing data redundancy. State what is meant by the term data redundancy. Explain how a relational database can help to reduce data redundancy. Moheem uses a Database Management System (DBMS) to ensure the security and integrity of the data. Explain the difference between security and integrity. Name and describe two security features provided by a DBMS. Feature 1 Feature 2 The DBMS provides software tools for the database developer. Fill in the names of the missing software tools in the following statements. A allows a developer to extract data from a database. A enables a developer to create user-friendly forms and reports.

9608_s19_qp_12

THEORY

2019

Paper 1, Variant 2

View

K2 Mountain Guiding is a company that runs courses teaching people how to climb mountains. The company uses a file-based approach to store and retrieve data. Describe three drawbacks of a file-based approach compared to a relational database. Drawback 1 Drawback 2 Drawback 3 Each course has a destination and a trip date, and is run by a guide. Customers can book a place on one or more courses. The following table shows part of the stored file: Customer name Customer date of birth Guide Destination Trip date Jay Patel 10/10/1976 Artem Elbrus 06/03/2023 Paul Schloss 10/04/1999 Kenton K2 01/03/2022 Mina Wang 03/03/2000 Kenton K2 01/03/2022 Paul Schloss 10/04/1999 Safia Mont Blanc 07/07/2024 Jay Patel 10/10/1976 Safia K2 04/04/2023 The company has decided to create a relational database to replace the current file-based approach. Identify three reasons why the data in this table is not in First Normal Form (1NF). The table, GUIDE_TABLE, is created. Each guide has a unique guide ID. Part of the table GUIDE_TABLE is shown: GuideID Guide DateOfBirth Gender Artem 07/03/1992 M Kenton 08/04/1984 M Safia 10/10/1999 F Write a Data Definition Language (DDL) statement to define the table GUIDE_TABLE.

9608_s21_qp_12

THEORY

2021

Paper 1, Variant 2

View

A teacher uses a relational database, RESULTS, to store data about her students and their test results. Describe the benefits to the teacher of using a relational database instead of a file-based approach. The teacher sets up the RESULTS database using a Database Management System (DBMS). Explain the ways in which the developer interface of a DBMS will help the teacher set up the database. The DBMS creates a data dictionary for the RESULTS database. Identify three items that will be included in the data dictionary. The RESULTS database has the following structure: STUDENT(StudentID, FirstName, LastName, Class, TargetGrade) TEST(TestID, Topic, MaxMarks) STUDENT_TEST(StudentID, TestID, Mark) Complete the following table by giving one example of each database term from the database RESULTS. Give both the field name and the corresponding table name. Database term Field name Table name Primary key Foreign key Attribute Tick (✓) one box to identify whether the database RESULTS is in 1NF, 2NF or 3NF. Justify your choice. 1NF 2NF 3NF Justification: Complete the Data Manipulation Language (DML) script to display the Student ID, mark and maximum marks for all tests with the topic of ‘Programming’. SELECT StudentID, Mark, FROM STUDENT_TEST, Topic = "Programming" AND = ; The teacher wants to implement validation to make sure that all data entered into the database RESULTS are reasonable. Name three different methods of data validation that can be used in the RESULTS database. Describe how each method will limit the data that can be entered in this database. Method 1 Description Method 2 Description Method 3 Description The teacher stores the database on the desktop computer in her classroom. Explain why it is important to keep the database secure. Explain the ways in which the teacher can use data backup and disk mirroring to limit the amount of data lost in the event of hardware failure. Data backup Disk mirroring

9608_w20_qp_12

THEORY

2020

Paper 1, Variant 2

View

A database has been designed to store data about salespersons and the products they have sold. The following facts help to define the structure of the database: • each salesperson works in a particular shop • each salesperson has a unique first name • each shop has one or more salespersons • each product which is sold is manufactured by one company only • each salesperson can sell any of the products • the number of products that each salesperson has sold is recorded The table ShopSales was the first attempt at designing the database. FirstName Shop ProductName NoOfProducts Manufacturer Nick TX television set refrigerator digital camera SKC WP HKC Sean BH hair dryer electric shaver WG BG John TX television set mobile phone digital camera toaster SKC ARC HKC GK State why the table is not in First Normal Form (1NF). The database design is changed to: SalesPerson (FirstName, Shop) SalesProducts (FirstName, ProductName, NoOfProducts, Manufacturer) Using the data given in the first attempt table (ShopSales), show how these data are now stored in the revised table designs. Table: SalesPerson FirstName Shop Table: SalesProducts FirstName ProductName NoOfProducts Manufacturer A relationship between the two tables has been implemented. Explain how this has been done. Explain why the SalesProducts table is not in Third Normal Form (3NF). Write the table definitions to give the database in 3NF.

9608_s15_qp_11

THEORY

2015

Paper 1, Variant 1

View

A database has been designed to store data about salespersons and the products they have sold. The following facts help to define the structure of the database: • each salesperson works in a particular shop • each salesperson has a unique first name • each shop has one or more salespersons • each product which is sold is manufactured by one company only • each salesperson can sell any of the products • the number of products that each salesperson has sold is recorded The table ShopSales was the first attempt at designing the database. FirstName Shop ProductName NoOfProducts Manufacturer Nick TX television set refrigerator digital camera SKC WP HKC Sean BH hair dryer electric shaver WG BG John TX television set mobile phone digital camera toaster SKC ARC HKC GK State why the table is not in First Normal Form (1NF). The database design is changed to: SalesPerson (FirstName, Shop) SalesProducts (FirstName, ProductName, NoOfProducts, Manufacturer) Using the data given in the first attempt table (ShopSales), show how these data are now stored in the revised table designs. Table: SalesPerson FirstName Shop Table: SalesProducts FirstName ProductName NoOfProducts Manufacturer A relationship between the two tables has been implemented. Explain how this has been done. Explain why the SalesProducts table is not in Third Normal Form (3NF). Write the table definitions to give the database in 3NF.

9608_s15_qp_12

THEORY

2015

Paper 1, Variant 2

View

A Database Management System (DBMS) provides the following features. Draw a line to match each feature with its description. Feature Description Data dictionary Data security Data integrity A file or table containing all the details of the database design Data design features to ensure the validity of data in the database A model of what the database will look like, although it may not be stored in this way Methods of protecting the data including the uses of passwords and different access rights for different users of the database A school stores a large amount of data that includes student attendance, qualification and contact details. The school is setting up a relational database to store these data. The school needs to safeguard against any data loss. Describe three factors to consider when planning a backup procedure for the data. Justify your decisions. The database design has three tables to store the qualifications and grades each student has attained. The following is a sample of the data from each table. STUDENT StudentID FirstName LastName Tutor 001AT Ahmad Tan 11A 003JL Jane Li 11B 011HJ Heather Jones 10A QUALIFICATION QualCode Level Subject CS1 IGCSE Computer Science MT9 IGCSE Maths SC12 IGCSE Science STUDENT-QUALIFICATION QualCode StudentID Grade DateOfAward SC12 011HJ A 31/8/2014 SC12 003JL C 31/8/2014 CS1 003JL B 31/8/2014 Draw an Entity-Relationship (E-R) diagram to show the relationships between these three tables. State the type of relationship that exists between STUDENT and STUDENT-QUALIFICATION. Describe how the relationship between QUALIFICATION and STUDENT-QUALIFICATION is implemented. The database will store each student’s date of birth. Write an SQL script to add a date of birth attribute to the appropriate table. Write an SQL script to display the StudentID, Grade and DateOfAward for the QualCode value of SC12. Write an SQL script to display the FirstName and LastName and QualCode for all STUDENT-QUALIFICATIONs for which the Grade value is A.

9608_s16_qp_13

THEORY

2016

Paper 1, Variant 3

View

A driving school teaches people how to drive cars. The school has a relational database, DRIVING_SCHOOL, to store information about instructors, students, lessons and the cars used by instructors. INSTRUCTOR(InstructorID, FirstName, LastName, DateOfBirth, Level) CAR(Registration, Make, Model, EngineSize) INSTRUCTOR_CAR(InstructorID, Registration) STUDENT(StudentID, FirstName, LastName, DateOfBirth, Address1) LESSON(LessonID, StudentID, InstructorID, LessonDate, LessonTime) Give two benefits to the driving school of using a relational database instead of a flat file. Complete the entity-relationship diagram for the database DRIVING_SCHOOL. INSTRUCTOR INSTRUCTOR_CAR LESSON CAR STUDENT The table shows some sample data for the table INSTRUCTOR. InstructorID FirstName LastName DateOfBirth Level Ins01 Jayden Han 05/06/1974 Ins02 Freda Choi 06/02/1978 Ins03 Kelly Kim 01/12/1966 Ins04 Santana Thompson 09/09/1985 Complete the Data Definition Language (DDL) statement to create the table INSTRUCTOR. ………………………………………………………………………… TABLE INSTRUCTOR( InstructorID VARCHAR(5), FirstName VARCHAR(15), LastName VARCHAR(15), DateOfBirth DATE, Level ………………………………………………………………………… , ………………………………………………………………………… (InstructorID) ); The table STUDENT needs an additional field to store the student’s telephone number, for example 012-3456. Write a Data Definition Language (DDL) statement to add the new field to the table STUDENT. Write a Data Manipulation Language (DML) statement to return the date and time of all future lessons booked with the instructor whose InstructorID is Ins01.

9608_s20_qp_11

THEORY

2020

Paper 1, Variant 1

View

---