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

A company takes customer service for its clients very seriously. The client • The client names are unique. A visit • The company arranges a date for a visit to gather feedback from a client. • A visit to a client never takes more than one day. • Over time, the client receives many visits. Staff (Interviewers) • One or more staff attend the visit. • If there is more than one staff member visiting, each performs a separate interview. Interviews • Each interview is classified as either ‘general’ or by some specialism, for example, marketing, customer service or sales. • A report is produced for each interview, InterviewText. • Each interview is conducted by a single staff member. The client, visit, staff and interview data will be stored in a relational database. Underline the primary key for each table in the following suggested table designs. STAFF(StaffID, StaffName, Department) CLIENT(ClientName, Address, Town) VISIT(ClientName, VisitDate) INTERVIEW(ClientName, VisitDate, StaffID, SpecialistFocus, InterviewText) For each of the pairs of entities, A, B and C, draw the relationship between the two entities. A CLIENT VISIT B VISIT INTERVIEW C INTERVIEW STAFF The company decides to produce a visit report, VisitReportText, for each visit made. This text will be produced from the one or more interview texts obtained at the visit. State how one or more of the given table designs can be changed to add this attribute. Client ABC Holdings are now trading under the name of Albright Holdings. Write an SQL script to update this client’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 Staff ID of each member of staff who performed an interview when they visited New Age Toys on 13/10/2016. At present, all interviews are performed in the UK. Many clients now operate in other countries in Europe. The company wants to perform interviews with the client’s staff in other countries. Not all interview staff are willing to travel outside of the UK. State how one or more of the table designs should be revised to store this information.

9608_w17_qp_12

THEORY

2017

Paper 1, Variant 2

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_13

THEORY

2017

Paper 1, Variant 3

View

Some shops belong to the Rainbow Retail buying group. They buy their goods from one or more suppliers. Each shop has: • a unique shop ID • a single retail specialism (for example, food, electrical, garden). Each supplier has: • a unique supplier ID • a similar single specialism recorded. Rainbow Retail creates a relational database to record data about the shops and their suppliers. The entity-relationship (E-R) diagram for the relationship between the SHOP and SUPPLIER tables is shown. SHOP SUPPLIER Explain what the degree of relationship is between the entities SHOP and SUPPLIER. The database design is as follows: SHOP(ShopID, ShopName, Location, RetailSpecialism) SUPPLIER(SupplierID, SupplierName, ContactPerson, RetailSpecialism) SHOP-SUPPLIER(ShopID, SupplierID) The SHOP–SUPPLIER table stores the suppliers that each shop has previously used. Primary keys are not shown. Label the entities and draw the relationships to complete the revised E-R diagram. SUPPLIER Complete the following table to show for each database table: • the primary key • the foreign key(if any): ° Each table may contain none, one or more foreign key. ° For a table with no foreign key, write ‘None’. • an explanation for the use of any foreign key. Table Primary key Foreign key(if any) Explanation SHOP SUPPLIER SHOP–SUPPLIER The database designer has implemented SUPPLIER.ContactPerson as a secondary key. Describe the reason for this. Write an SQL query to display the shop ID and location of all shops with a ‘GROCERY’ specialism. The existing shop with ID 8765 has just used the existing supplier SUP89 for the first time. Write an SQL script to add this data to the database.

9608_s17_qp_12

THEORY

2017

Paper 1, Variant 2

View

A movie theatre has a relational database that stores the movie schedule, and information about the movies. The theatre has several screens that play movies at the same time. The database has three tables to store information about the movies, the screens and the movie schedule. MOVIE(MovieID, Title, Length, Rating) SCREEN(ScreenNumber, NumberSeats) MOVIESCHEDULE(ScheduleID, MovieID, ScreenNumber, Time) Complete the entity-relationship (E-R) diagram to show the relationships between these tables. MOVIE SCREEN MOVIESCHEDULE Explain how primary and foreign keys are used to link the tables in the movie theatre database. The database needs to store the name of the company that produced each movie, for example, Rocking Movies. Write an SQL script to add the attribute ProductionCompany to the MOVIE table. Write an SQL script to display the title and rating of all movies scheduled to play on screen number 3.

9608_w18_qp_11

THEORY

2018

Paper 1, Variant 1

View

An airline company uses a relational database to store data about passengers and flights. Part of the database is shown. PASSENGER(PassengerID, FirstName, LastName) FLIGHT(FlightID, FlightDate, FlightTime) PASSENGER_LIST(FlightID, PassengerID, SeatNo) Complete the entity-relationship (E-R) diagram to show the relationships between the given tables. PASSENGER FLIGHT PASSENGER_LIST The following is example data for the table FLIGHT: FlightID FlightDate FlightTime MO126 05/05/21 09:00 GK6708 06/09/21 00:30 BA0897 08/12/21 15:30 Write Data Definition Language (DDL) statements to define the table FLIGHT.

9608_w21_qp_11

THEORY

2021

Paper 1, Variant 1

View

A hospital is divided into two areas, Area A and Area B. Each area has several wards. All the ward names are different. A number of nurses are based in Area A. These nurses always work on the same ward. Each nurse has a unique Nurse ID of STRING data type. A-NURSE A-WARD Describe the relationship shown above. A relational database is created to store the ward and nurse data. The two table designs for Area A are: A-WARD(WardName, NumberOfBeds) A-NURSE(NurseID, FirstName, FamilyName, ) Complete the design for the A-NURSE table. Explain how the relationship in part is implemented. In Area B of the hospital, there are a number of wards and a number of nurses. Each Area B ward has a specialism. Each Area B nurse has a specialism. A nurse can be asked to work in any of the Area B wards where their specialism matches with the ward specialism. The relationship for Area B of the hospital is: B-NURSE B-WARD Explain what the degree of relationship is between the entities B-NURSE and B-WARD. The design for the Area B data is as follows: B-NURSE(NurseID, FirstName, FamilyName, Specialism) B-WARD(WardName, NumberOfBeds, Specialism) B-WARD-NURSE( ) Complete the attributes for the third table. Underline its primary key. Draw the relationships on the entity-relationship (E-R) diagram. B-NURSE B-WARD B-WARD-NURSE Use the table designs in part . Write an SQL query to display the Nurse ID and family name for all Area B nurses with a specialism of ‘THEATRE’. Fatima Woo is an Area B nurse with the nurse ID of 076. She has recently married, and her new family name is Chi. Write an SQL command to update her record. UPDATE SET WHERE

9608_s17_qp_11

THEORY

2017

Paper 1, Variant 1

View

A hospital is divided into two areas, Area A and Area B. Each area has several wards. All the ward names are different. A number of nurses are based in Area A. These nurses always work on the same ward. Each nurse has a unique Nurse ID of STRING data type. A-NURSE A-WARD Describe the relationship shown above. A relational database is created to store the ward and nurse data. The two table designs for Area A are: A-WARD(WardName, NumberOfBeds) A-NURSE(NurseID, FirstName, FamilyName, ) Complete the design for the A-NURSE table. Explain how the relationship in part is implemented. In Area B of the hospital, there are a number of wards and a number of nurses. Each Area B ward has a specialism. Each Area B nurse has a specialism. A nurse can be asked to work in any of the Area B wards where their specialism matches with the ward specialism. The relationship for Area B of the hospital is: B-NURSE B-WARD Explain what the degree of relationship is between the entities B-NURSE and B-WARD. The design for the Area B data is as follows: B-NURSE(NurseID, FirstName, FamilyName, Specialism) B-WARD(WardName, NumberOfBeds, Specialism) B-WARD-NURSE( ) Complete the attributes for the third table. Underline its primary key. Draw the relationships on the entity-relationship (E-R) diagram. B-NURSE B-WARD B-WARD-NURSE Use the table designs in part . Write an SQL query to display the Nurse ID and family name for all Area B nurses with a specialism of ‘THEATRE’. Fatima Woo is an Area B nurse with the nurse ID of 076. She has recently married, and her new family name is Chi. Write an SQL command to update her record. UPDATE SET WHERE

9608_s17_qp_13

THEORY

2017

Paper 1, Variant 3

View

---