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

A software development company has a relational database, SOFTWARE_MANAGEMENT. The database stores details of the customers who have purchased software, as well as the software and licences that customers have purchased. The SOFTWARE_MANAGEMENT database has the following tables: CUSTOMER_DETAILS(CustomerID, CompanyName, Address1, Address2, City) SOFTWARE_PURCHASED( SoftwareName, SoftwareDescription, CustomerID, LicenceType, LicenceCost, RenewalDate) Explain why this database is not in Third Normal Form (3NF). Refer to the tables in your answer. Do not attempt to normalise the tables. Give an example from the database SOFTWARE_MANAGEMENT for each of the following database terms. Term Example Entity Foreign key Attribute The company also develops computer games. They extend the relational database SOFTWARE_MANAGEMENT by adding a new table. The new table, GAME_DEVELOPMENT, stores details about the games and the software development teams creating them. The table shows example data in GAME_DEVELOPMENT. GameName Genre TeamNumber DevelopmentStage ManagerID Bunny Hop Platform Analysis 23KP Fried Eggs Retro Programming stage 1 9RTU Create‑a‑game Action Acceptance testing 11TF Complete the Data Definition Language (DDL) statement to create the table GAME_DEVELOPMENT. CREATE ………………………………………………………… ………………………………………………………… ( GameName VarChar, Genre VarChar, ………………………………………………………… ………………………………………………………… , DevelopmentStage VarChar, ManagerID VarChar, ………………………………………………………… (GameName) ); Another table, PRODUCT_MANAGER, is created. PRODUCT_MANAGER(ManagerID, FirstName, LastName) Complete the Data Manipulation Language (DML) statement to return the game name, genre and team number of all games managed by the product manager with the first name ‘James’ and the last name ‘Fitz’. ………………………………………………………… GameName, Genre, TeamNumber FROM GAME_DEVELOPMENT, PRODUCT_MANAGER WHERE PRODUCT_MANAGER.FirstName = "James" AND PRODUCT_MANAGER.LastName = "Fitz" AND ………………………………………………………………………………………………… = …………………………………………………………………………………………………… ;

9608_s20_qp_12

THEORY

2020

Paper 1, Variant 2

View

Five descriptions and seven relational database terms are shown below. Draw a line to link each description to its correct database term. Description Database term Any object, person or thing about which it is possible to store data Dataset organised in rows and columns; the columns form the structure and the rows form the content Any attribute or combination of attributes that can act as a unique key Attributein a table that link to the primary key in another table to form a relationship Attribute or combination of attributes that is used to uniquely identify a record Secondary key Candidate key Entity Foreign key Primary key Table Tuple Explain what is meant by referential integrity.

9608_w16_qp_11

THEORY

2016

Paper 1, Variant 1

View

A health club offers classes to its members. A member needs to book into each class in advance. The health club employs a programmer to update the class booking system. The programmer has to decide how to store the records. The choice is between using a relational database or a file-based approach. Give three reasons why the programmer should use a relational database. The programmer decides to use three tables: MEMBER, BOOKING and CLASS. Complete the Entity-Relationship (E-R) diagram to show the relationships between these tables. MEMBER CLASS BOOKING The CLASS table has primary key ClassID and stores the following data: ClassID Description StartDate ClassTime NoOfSessions AdultsOnly DAY01 Yoga beginners 12/01/2016 11:00 TRUE EVE02 Yoga beginners 12/01/2016 19:00 FALSE DAY16 Circuits 30/06/2016 10:30 FALSE Write an SQL script to create the CLASS table.

9608_w16_qp_12

THEORY

2016

Paper 1, Variant 2

View

Five descriptions and seven relational database terms are shown below. Draw a line to link each description to its correct database term. Description Database term Any object, person or thing about which it is possible to store data Dataset organised in rows and columns; the columns form the structure and the rows form the content Any attribute or combination of attributes that can act as a unique key Attributein a table that link to the primary key in another table to form a relationship Attribute or combination of attributes that is used to uniquely identify a record Secondary key Candidate key Entity Foreign key Primary key Table Tuple Explain what is meant by referential integrity.

9608_w16_qp_13

THEORY

2016

Paper 1, Variant 3

View

A company uses a relational database. The company stores data about its customers and their bank details. Explain the ways in which the database can be set up to only allow certain people to see the bank details. The database contains the following tables that store data about suppliers and stock. SUPPLIER(SupId, SupName, SupAddress, SupPhone, SupContactName) STOCK(StockId, SupId, StockName, StockImage, NumberInStock) Write Data Manipulation Language (DML) statements to return the supplier name, phone number and contact name, and the number of items in stock for the item with the stock ID of D930. The stock price needs to be included in the stock table. $10.55 and $299.99 are two examples of stock prices. Write Data Definition Language (DDL) statements to insert the attribute StockPrice into the table STOCK. Three examples of items stored in the data dictionary of a relational database are: Item Purpose Field name to store the names of fields so they cannot be repeated in a table Primary key to uniquely identify each record in a table Validation rule to ensure that data entered is sensible Identify three other examples of items stored in a data dictionary and give the purpose of each. Item 1 Purpose Item 2 Purpose Item 3 Purpose Draw one line from each database term to its matching description. Database term Description An attribute that could be a primary key but is not selected to be a primary key Logical schema An attribute in one table that is a primary key in another table Referential integrity The overview of a database structure Query processor An attribute or set of attributes that uniquely identifies each tuple Primary key Data between linked tables is consistent Secondary key Changing data to see what would happen in different scenarios The part of the DBMS that allows a user to search for data

9608_w21_qp_13

THEORY

2021

Paper 1, Variant 3

View

A social media website has a relational database, WEBDATA, that stores the site’s information. The database has three tables to store users’ details, and details of the images and text that they post. USER(UserName, FirstName, SecondName, DateOfBirth) PHOTO(PhotoID, UserName, Comment, UploadDate) TEXTPOST(PostID, UserName, DateOfPost, TheText) Explain how the relationship between the tables USER and PHOTO has been implemented. Draw the entity-relationship (E-R) diagram to show the relationships between the three tables. A database administrator decides to enforce referential integrity. Use an example from the database WEBDATA to explain what is meant by referential integrity. The database has been normalised to Third Normal Form (3NF). Define the three stages of database normalisation. 1NF 2NF 3NF The following shows sample data from the USER table. UserName FirstName SecondName DateOfBirth gem123 John Smith 01/01/1995 purpleSky Muhammed Ali 23/02/1956 OpenWindow Sunny Amir 03/03/1997 bluebird127 Raziya Bello 04/03/1982 Write an SQL script to create the USER table. The database administrator needs to alter the USER table. A new field, Country, needs to be added. Write an SQL script to add the field Country to the USER table.

9608_s18_qp_12

THEORY

2018

Paper 1, Variant 2

View

A company writes applications for smartphones. The company has a relational database, PURPLEGAME, which stores the information for one of its online game apps. The database has three tables to store player’s details, dates when they have logged into the app and in-app purchase details. LOGIN(LoginID, PlayerID, Date) PURCHASE(PurchaseID, PlayerID, PurchaseDate, Cost) PLAYER(PlayerID, PlayerName, SkillLevel) Draw the entity-relationship (E-R) diagram to show the relationships between the three tables. The database manager is concerned about data integrity. State what is meant by data integrity. Give an example of how the manager can ensure data integrity in the PURPLEGAME database. The database designer states that the PURPLEGAME database is in Third Normal Form (3NF). Tick (3) one box to indicate whether this statement is true or false. True False Justify your choice. The following table shows some sample data for the PLAYER table. PlayerID PlayerName SkillLevel fly918 Kylie elephant11 Mehrdad candy22 Suzi greenGrass Jason Write an SQL script to create the PLAYER table. The table, PLAYER, needs to be altered. A new field, DateOfBirth, needs to be added. Write an SQL script to add the DateOfBirth field to the PLAYER table.

9608_s18_qp_13

THEORY

2018

Paper 1, Variant 3

View

---