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

A company uses a relational database, EMPLOYEES, to store data about its employees and departments. The company uses a Database Management System (DBMS). The DBMS has a data dictionary. Describe what the data dictionary stores. The DBMS has a query processor. Describe the purpose of a query processor. Relationships are created between tables using primary and foreign keys. Describe the role of a primary and a foreign key in database relationships. In the company: • An employee can be a manager. • A department can have several managers and several employees. • An employee can only belong to one department. The EMPLOYEES database has three tables: EMPLOYEE_DATA( EmployeeID, FirstName, LastName, DateOfBirth, Gender, DepartmentNumber) DEPARTMENT(DepartmentNumber, DepartmentName) DEPARTMENT_MANAGER(DepartmentNumber, EmployeeID, role) Complete the entity-relationship (E-R) diagram for the EMPLOYEES database. EMPLOYEE_DATA DEPARTMENT DEPARTMENT_MANAGER Give three reasons why the EMPLOYEES database is fully normalised. Part of the EMPLOYEE_DATA table is shown. EmployeeID FirstName LastName DateOfBirth Gender DepartmentNumber 156FJEK Harvey Kim 12/05/1984 Male S1 558RRKL Catriona Moore 03/03/1978 Female F2 388LMDV Oscar Ciao 01/01/1987 Male F2 Write a Data Definition Language (DDL) statement to create the EMPLOYEES database. Write a DDL statement to define the table EMPLOYEE_DATA, and declare EmployeeID as the primary key. Write a Data Manipulation Language (DML) statement to return the first name and last name of all female employees in the department named Finance.

9608_s19_qp_13

THEORY

2019

Paper 1, Variant 3

View

Sheila creates a relational database for her hotel using a Database Management System (DBMS). Draw one line from each database term to its most appropriate description. Database Term Description Attribute Primary key Foreign key Entity A field in one table that links to a primary key in another table A collection of records and fields The type of data that is being stored A unique identifier for each tuple A data item, represented as a field within a table The concept or object in the system that we want to model and store information about Identify three tasks that Sheila can perform using the DBMS developer interface. Sheila creates the database HOTEL with the following table structure: ROOM(RoomNumber, RoomType) BOOKING(BookingID, RoomNumber, CustomerID, StartDate) CUSTOMER(CustomerID, FirstName, LastName, Address, Tel_Num) The following table shows some sample data for the table ROOM. RoomNumber RoomType Standard Double Executive Standard Complete the Data Definition Language (DDL) statement to create the table ROOM. TABLE ROOM( RoomNumber Integer, RoomType , (RoomNumber) ); Room number 5 is a Double room. Complete the Data Manipulation Language (DML) statement to add the details for room number 5 to the table ROOM. INSERT ROOM VALUES( ); The table BOOKING needs an additional field to store the number of nights (for example, 3) a customer is staying. Write a Data Definition Language (DDL) statement to add the new field to the table BOOKING.

9608_s20_qp_13

THEORY

2020

Paper 1, Variant 3

View

A clinic is staffed by several doctors. The clinic serves thousands of patients. Each day and at any one time, there is only one doctor in the clinic available for appointments. The clinic stores patient, doctor and appointment data in a relational database. Underline the primary key for each table in the following suggested table designs. PATIENT(PatientID, PatientName, Address, Gender) DOCTOR(DoctorID, Gender, Qualification) APPOINTMENT(AppointmentDate, AppointmentTime, DoctorID, PatientID) Complete the following entity-relationship (E-R) diagram for this design. The doctors are concerned that many patients make appointments but do not attend them. Describe the changes to the table designs that could be made to store this information. The doctors are about to set up a new clinic in the neighbouring village, SITE-B. The original location is identified as SITE-A. A new table is designed to store the ID of the doctor who is able to work at each site. DOCTOR-AVAILABILITY(DoctorID, Site) Five entries stored in the table are: DoctorID Site SITE-A SITE-A SITE-B SITE-B SITE-B State what this data shows about the availability of the doctor with the ID of 098. Opening a new clinic in the neighbouring village will not require any additional table for storing appointments. It will need a change to the existing appointment table design. Show the revised APPOINTMENT table. APPOINTMENT( ) The doctor with the ID of 117 has recently been allocated a new DoctorID of 017. Write an SQL script to update this doctor’s record in the database. UPDATE SET WHERE Describe why this update could cause problems with the existing data stored. Write an SQL script to display the date and time of all appointments made by the patient with the PatientID of 556.

9608_w17_qp_11

THEORY

2017

Paper 1, Variant 1

View

---