cameroon gce advanced level 2024 engineering science 3

Here is the extracted text from the database exam sheet (image_6fbee5.jpg):

SECTION A: DATABASE (Nina’s Shop) (20 marks)

The relation R below keeps records of customers and the items they buy from Nina’s shop.

R =	CustomerID	CustomerName	ItemCode	ItemName	UnitPrice	Quantity	TotalPrice	TDate
	C001	Ebai Paul	IT001	Rice	200	3	600	09/09/2021
	C001	Ebai Paul	IT002	Beans	250	2	500	09/09/2021
	C001	Ebai Paul	IT003	Corn	175	2	350	10/10/2021
	C002	Eyere Esther	IT003	Corn	175	5	875	11/12/2021

Relation R has the following attributes with names of obvious meanings; the primary key field is underlined:

R(CustomerID, CustomerName, ItemCode, ItemName, UnitPrice, Quantity, TotalPrice, TDate)

Task1: Database Design (6 marks)

(i) In a fully normalised relational database the data in the relation R is stored in the relations: Customer, Item, and Customer_Item. Customer_Item holds details about items bought by customers. Complete each of these relations making sure that their primary key attribute(s) is /are underlined.

Customer(……………………………………………………………………………………………………………………………….

…………………………………………………………………………………………………………………………………..) (1 mark)

Item(…………………………………………………………………………………………………………………………………….

…………………………………………………………………………………………………………………………………..) (1 mark)

Customer_Item(……………………………………………………………………………………………………………………….

…………………………………………………………………………………………………………………………………..) (2 marks)

(ii) Complete the entity-relationship diagram below for the entities Customer, Item and Customer_Item

Customer		Item

Customer_Item

Task2: Database Implementation (6 marks)

In your favourite Relational Database Management System (RDBMS), use SQL statements to implement the following.

(i) Create a database called NinashopDB. Write down the SQL statement used. (1 mark)

……………………………………………………………………………………………………………………………………………………

……………………………………………………………………………………………………………………………………………………

(ii) In NinashopDB, create the tables Customer, Item, and Customer_Item. Write down the SQL statement used to create the Customer_Item table. (3 marks)

00/0795/3

Go on to the next page