Database Concepts (Cambridge (CIE) A Level Computer Science): Exam Questions

A company uses a relational database to store data about its customers, employees and the individual repair jobs that customers have booked.

Explain the benefits of using a relational database instead of a file-based approach.

The company decides which employees will work on each repair job. An employee can log into the database to access information about their repair jobs.

The database is normalised and includes these tables:

• CUSTOMER stores personal data about each customer

• EMPLOYEE stores personal data about each employee

• LOGIN_DATA stores the username and password for each employee

• JOB stores the data about each repair job

• JOB_EMPLOYEE stores the employees that are working on each repair job.

Identify each relationship between the database tables and explain how each relationship can be implemented in the normalised database.

A company makes ice cream and sells it to shops.

The ice cream is made in batches: a large quantity of one type and flavour of ice cream that is then split into smaller quantities for sale.

The company’s owner has designed a relational database, ICECREAM, to store data about their ice cream and customers.

Some of the tables in the database are given. The database is not normalised.

BATCH(BatchID, Type, Flavour, Size, SellingPrice, EndDate)

CUSTOMER(CustomerID, CompanyName, EmailAddress, TelephoneNumber)

SALE(SaleID, BatchID, CustomerID, Quantity, Date)

Identify two foreign keys in the table SALE and the table that each foreign key references.

Foreign key 1 ........................................................................................

Table name 1 .........................................................................................

Foreign key 2 ........................................................................................

Table name 2........................................................................................

The table BATCH is not normalised.

Normalise the database table BATCH.

Write the table definitions for your new tables.

Identify any primary and foreign keys in your tables.

Do not change or include the tables CUSTOMER and SALE.

Complete the following table by defining each database term.

A company is developing a website that will allow users to create an account and then play a quiz every day. The data about the users and the quizzes are stored in a database.

A user must select a unique username and enter a valid email address to create an account. All users must be over the age of 16. A new quiz is given to the users every day. Each quiz is stored in its own text file.

The database stores the filename of each quiz and the date it can be played. The user gets a score for each quiz they complete, which is stored in the database. The scores are used to give each user a rating, for example Gold.

Create a 3-table design for this database normalised to Third Normal Form (3NF).

Give your table design in the format:

TableName(PrimaryKey, Field1, Field2, …)

An assessment board wants to store the marks students achieved in exams in a database named RECORDS.

Part of the database design includes these two tables:

EXAM(ExamID, Subject, Level, TotalMarks)

EXAM_QUESTION(ExamQuestionID, ExamID, QuestionNumber, Question, MaxMark)

Identify the relationship between EXAM and EXAM_QUESTION.

The database also needs to store data about the students, the exams the students have taken and the marks the students achieved in each question of each exam.

Describe the additional tables that will need to be included in the database and explain how all the tables in the database will be linked.

A theatre wants to use a database to store data about the shows that are scheduled, their customers and the seats that the customers have booked.

In the theatre:

• Each show can take place on multiple dates.

• Some dates can have more than one performance.

• There are multiple rows of seats.

• Each seat can be individually booked by its row letter and seat number, for example row E seat 2.

Part of the database design includes these tables:

SHOW(ShowID, Title, Duration)

SEAT(SeatID, RowLetter, SeatNumber)

PERFORMANCE(PerformanceID, ShowID, ShowDate, StartTime)

Identify the relationship between the tables PERFORMANCE and SHOW.

Customers give their first name, last name and email address when they are making a booking. One booking can include multiple seats.

Describe the additional tables that will need to be included in the database and explain how these tables will be linked within the database.

A shop manager has designed a relational database to store customer orders.

The database will have the following tables:

CUSTOMER(CustomerID, FirstName, LastName, Town)

SHOP_ORDER(OrderNo, CustomerID, OrderDate)

SUPPLIER(SupplierID, EmailAddress, TelephoneNumber)

ITEM(ItemNumber, SupplierID, Description, Price)

ORDER_ITEM(ItemNumber, OrderNo, Quantity)

Complete the entity-relationship (E-R) diagram for the relational database.

Identify three advantages of a relational database compared to a file-based approach.

State what is meant by the following terms in a relational database model.

Entity .......................................................................................................................

Primary key ..............................................................................................................

Referential integrity .............................................................................................

The following database table is not normalised.

Explain how to modify the table to put it into First Normal Form (1NF).

The company uses a database, COURSES, to store data about the courses and their tutors.

Each course starts at different times of the year and may have a different tutor. The database has the following structure:

COURSE_INFORMATION(CourseID, Description, Cost)

TUTOR(TutorID, TelephoneNumber, EmailAddress, TutorName)

COURSE_SCHEDULE(CourseID, DateStarted, TutorID)

Complete the entity‑relationship (E‑R) diagram for the database COURSES.

A college library uses a relational database to store data about its members and the books they borrow.

The database design includes these tables:

MEMBER(MemberID, FirstName, LastName, YearGroup)

MEMBER_CARD(CardNumber, MemberID, DateIssued)

LOAN(LoanID, MemberID, BookCopyID, DateBorrowed)

Each member is given exactly one library card. Each library card belongs to exactly one member.

A member can make many loans. Each loan is made by one member.

Complete the entity-relationship (E-R) table by writing the degree of each relationship.

A sports centre uses a relational database to store data about its coaches, its members and the fitness classes that it runs.

• Each fitness class is led by one coach. A coach can lead many fitness classes.

• Each coach has one set of login details for the computer system at the centre. Each set of login details belongs to one coach.

• A member can book many fitness classes. A fitness class can be booked by many members.

Complete the entity-relationship (E-R) table by writing the degree of each relationship.

A conference organiser uses a relational database to store data about the speakers, the sessions and the delegates who attend the conference.

• Each session is given by one speaker. A speaker can give many sessions.

• Each session takes place in one room. A room is used for many sessions.

• A delegate can attend many sessions. A session can be attended by many delegates.

The organiser wants a design in which no relationship is many-to-many, so one extra entity must be added.

Complete the entity-relationship (E-R) table for this design.

Read each row from left to right. Choose a suitable name for the entity that you add and write that same name in both empty entity cells.

A driving school stores its data using a file-based approach.

The office keeps a file of learner details, including each learner's name and telephone number.

Each instructor keeps a separate file of the lessons they have taught. This file also holds each learner's name and telephone number.

Identify two limitations of storing the data in this way.

1 .............................................................................................................................

2 .............................................................................................................................

A medical clinic stores its data using a file-based approach.

• The reception desk keeps an appointments file. It holds each patient's name, telephone number and appointment time.

• The nursing team keeps a separate treatments file. It also holds each patient's name and telephone number.

A patient tells the reception desk that they have a new telephone number. The receptionist changes the number in the appointments file.

Explain why the file-based approach has now caused data inconsistency.

A bus company stores data about its journeys using a file-based approach. Each journey is held in a data file as a record with a fixed structure. A separate program is used to produce each of the reports the company needs.

The company now wants to do both of the following:

• add a new data item to each journey record, to store the number of passengers carried

• answer a one-off question about how many journeys ran late on one particular day

Explain two further limitations of the file-based approach that these two requirements show.

Give the limitation and explain it in each case.

A sports club stores all of its data in one flat file database.

(i) State what is meant by a flat file database.

.................................................................................................................................

(ii) State one problem that storing the data in this way causes for the club.

.................................................................................................................................

The table gives three statements about databases.

Complete the table by placing one tick in each row to show whether the statement is true of a flat file database or of a relational database.

A sports club stores data about its members and the events it organises.

Two tables in the club's database are:

MEMBER(MemberID, FirstName, LastName, DateJoined)

EVENT(EventID, EventName, EventDate)

A member can enter many events. An event can be entered by many members. The club also records the position each member finished in each event.

Give the definition of the one additional table that is needed so that the design is normalised.

Use the format TableName(PrimaryKey, Field1, Field2, ...) and state the primary key and any foreign keys of your table.

A swimming club stores all of its data in one flat file table, MEMBER.

The club replaces the flat file with a relational database that has these two tables:

COACH(CoachID, CoachName, CoachPhone)

MEMBER(MemberID, MemberName, CoachID)

Identify the fault in the flat file table and explain how the relational design prevents this type of fault.

A driving school stores data about its instructors and the lessons it gives.

• Each instructor has an instructor number, a first name, a last name and one mobile number.

• Each lesson has a lesson number, a date and a start time.

• Each lesson is given by exactly one instructor. An instructor gives many lessons.

Write a normalised database design for this data.

Use the format TableName(PrimaryKey, Field1, Field2, ...) and state the primary key and any foreign keys in each table.

State what is meant by each of the following terms in a relational database.

Secondary key ...............................................................................................................

.................................................................................................................................

Foreign key ....................................................................................................................

.................................................................................................................................

A language school wants a database to store data about its students, its courses and its enrolments.

• Each student has a student number, a first name, a last name and one email address.

• Each course has a course code, a language, a level and a fee. Two courses that teach the same language at the same level can have different fees.

• A student can enrol on many courses. A course can have many students.

• For each enrolment the school stores the date the student enrolled and the final grade.

Write a database design for this data. All tables must be in Third Normal Form (3NF).

Use the format TableName(PrimaryKey, Field1, Field2, ...) and state the primary key and any foreign keys in each table.

A college is designing a table STUDENT to store data about its students. The table has these fields:

STUDENT(StudentID, NationalIDNumber, EmailAddress, FirstName, LastName, DateOfBirth)

The college has not yet chosen a primary key for this table.

• Every student is given a StudentID that no other student has.

• Every student has a NationalIDNumber that no other person has.

• Every student has an EmailAddress that no other student has.

• Two students may share a FirstName, a LastName or a DateOfBirth.

Identify two candidate keys in the table STUDENT and explain the difference between a candidate key and a primary key.

State what is meant by a database table being in First Normal Form (1NF).

A delivery company stores data in the following table. The table is not normalised.

Explain how this table should be modified so that it is in First Normal Form (1NF).

A sports league uses a relational database with these two tables:

TEAM(TeamID, TeamName, HomeGround)

PLAYER(PlayerID, PlayerName, TeamID)

TeamID in the table PLAYER is a foreign key that links to the primary key of the table TEAM.

The database enforces referential integrity.

A user attempts each of these two operations:

• add a new row to PLAYER with a TeamID of T99, when no row in TEAM has that TeamID

• delete the row in TEAM with a TeamID of T04, when rows in PLAYER still have a TeamID of T04

Explain what referential integrity means and how it deals with each of these two operations.

An online shop stores data about its orders in the following table. The table is not normalised.

Complete the table below to show the same data in First Normal Form (1NF).

State the primary key of the completed table.

State the two conditions that a database table must meet to be in Second Normal Form (2NF).

A hotel database contains the following table, which is in First Normal Form (1NF).

ROOM_STAY(RoomNumber, StayDate, RoomType, GuestID)

The primary key is the composite key RoomNumber and StayDate.

Some sample data is shown.

Explain why the table ROOM_STAY is not in Second Normal Form (2NF).

A logistics company uses a database that contains the following two tables. Both tables are in First Normal Form (1NF).

DELIVERY(DeliveryID, DriverID, DriverName, DeliveryDate)

The primary key of DELIVERY is DeliveryID.

STOCK(WarehouseID, PartID, QuantityHeld, WarehouseCity)

The primary key of STOCK is the composite key WarehouseID and PartID. WarehouseCity is the city in which the warehouse is located.

Explain, for each table, whether it is in Second Normal Form (2NF).

A college stores data about its students and courses in a relational database. The database designer says that every table in the database is now in Third Normal Form (3NF).

Describe two characteristics that each of these tables must have.

A college database contains the following table, which is in Second Normal Form (2NF).

STUDENT_RECORD(StudentID, FirstName, LastName, TutorID, TutorRoom)

The primary key is StudentID. TutorRoom is the room in which a student's tutor teaches.

Explain why the table STUDENT_RECORD is not in Third Normal Form (3NF).

A cinema uses a database that contains the following two tables. Both tables are in Second Normal Form (2NF).

SCREENING(ScreeningID, FilmID, FilmTitle, ScreenNumber, StartTime)

TICKET(TicketID, ScreeningID, SeatCode, PricePaid)

The primary key of SCREENING is ScreeningID and the primary key of TICKET is TicketID. The price paid is recorded separately for every ticket sold.

Explain which one of these tables is not in Third Normal Form (3NF), and write a corrected design so that the whole database is in 3NF.