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.