5b Identify Database Features And ERD***

Question 0

The company has decided to write a database to store information about its sales. 4 The entities that will be used in the database are:
CUSTOMER: A list of the customers that have purchased tills from them
TILL: A list of tills that is produced by the company
SALESPERSON: The member of the company who was responsible for the sale ORDER: The details of the orders placed by the company
ORDER_LINES: The details of the individual tills that make up each order
(a) Draw the Entity Relationship Diagram (ERD) making sure that the degree of each relationship is identified. (6)

Answer 0

Look at mark scheme Jan 2010 Question 4a
CUSTOMER – ORDER – SALESPERSON (1) TILL – ORDER_LINES - ORDER (1)
CUSTOMER – ORDER 1:M (1) SALESPERSON – ORDER 1:M (1) ORDER – ORDER_LINES 1:M (1) TILL – ORDER_LINES 1:M (1)

Question 1

Two of the entities in the database are CUSTOMER and BOOKING.
What is the relationship between CUSTOMER and BOOKING? (1)

Answer 1

One - Many

Question 2

Four of the entities in the database will be CUSTOMER, SERVICE, JOB and JOBCARD. (b) (i) What is the degree of the relationship between CUSTOMER and JOB?[1]
(ii) What is the degree of the relationship between SERVICE and JOBCARD?. [1]

Answer 2

(i) One – Many (1).

ii) Many - One (1

Question 3

(e) The database contains the following entities:
CUSTOMER: Stores details of customers who place orders.
PRODUCT: Stores details of individual products.
ORDER_DETAILS: Stores details of the orders.
ORDER_CONTENTS: Stores details of the products ordered by the customer for each order.
Define the relationships between the following entities: CUSTOMER
PRODUCT
ORDER_DETAILS
ORDER_CONTENTS (3)

Answer 3

Customer to Order _Details 1:M (1) Product to Order_Contents 1:M (1) Order_Details to Order_Contents: 1:M (1)

Question 4

The casualty department at the hospital stores details on patients, details of their visit and, if
necessary, x-rays taken which are stored as an image.
Create an ERD for the above scenario identifying the degree of the relationship between the entities and the primary and foreign keys. (5)

Answer 4

Patient – Details_of_Visit – 1:M Details_of_Visit - X-ray: 1:M
Identification of Primary Key for Patient, Visit/Details_of_Visit and X-ray: All 3 – 2 marks, 2 – 1 mark
Identification of 2 Foreign Keys in Visit/Details_of_Visit – 1 mark.

Question 5

EJO requires a relational database to be developed that will:
• store details of artists and their works;
• store details of customers;
• store details of sales.
(a) Two of the entities are CUSTOMER and ARTIST.
Identify two other entities that would be needed to create the database to meet EJO’s
requirements. (2)

Answer 5

SALES, WORK, PURCHASERS

Question 6

16
10 EJO requires a relational database to be developed that will:
• store details of artists and their works;
• store details of customers;
• store details of sales.
(b) Create an entity relationship diagram for the relational database identifying the degree of the
relationships between the entities. (4)

Answer 6

Links:
CUSTOMER – SALE SALE – WORK WORK - ARTIST
All correct – 2 marks Two correct: 1 mark
Relationships: CUSTOMER 1–M SALE SALE 1– 1 WORK WORK M – 1 ARTIST