G061 - Entity Relationship Diagram questions
1. / A book company uses a relational database to store data about the books, customers and orders.The entities and attributes in the relational database are:
CUSTOMER (CustID, Name, Address)
ORDER (Order_Code, CustID, Date, BookID, Quantity)
BOOK (BookID, Description, Price)
The primary key of the entity BOOK is BookID
Create an Entity Relationship Diagram (ERD) for the above scenario identifying the degree of the relationship between the entities and the primary and foreign keys. / [4]
2514 January 2004 Q2
2. / A garden centre keeps records of its customers in a database
The entities and attributes in the relational database are:
CUSTOMER (Cust_ID, Forename, Surname, Telephone Number)
ORDER(Order_Code, Cust_ID, Date, Plant_ID, Quantity)
PLANT(Plant_ID, Description, Price)
The primary key of the entity PLANT is Plant_ID.
Create an Entity Relationship Diagram (ERD) for the above scenario identifying the degree of the relationship between the entities and the primary and foreign keys. / [4]
2514 January 2005 Q4
3. / A wholesale pet food company, Food 4 Pets, sells its goods to independent pet shops throughout the UK.
Food 4 Pets has purchased an off-the-shelf relational database to hold data.
The entities and attributes in the relational database are:
CUSTOMER (Cust_ID, Name, Address)
PRODUCT (Product_Code, Supplier_ID, Description, Price)
SUPPLIER (Supplier_ID, Name, Address)
ORDER (Order_Code, Cust_ID, Date, Product_Code, Quantity)
Create an Entity Relationship Diagram (ERD) for the above scenario identifying the degree of the relationship between the entities and the primary and foreign keys. / [6]
2514 June 2006 Q1
4. / A business has recently started selling fish food and pond accessories by mail order.
The business currently keeps manual records of regular customers. The records are to be transferred to a database.
The entities and attributes that will be used in the database are:
CUSTOMER (Cust_ID, Surname, Forename, Telephone Number)
ORDER (Order_Code, Cust_ID, Date, Product_ID, Quantity)
PRODUCT (Product_ID, Description, Price)
The primary key of the entity PRODUCT is Product_ID.
Create an Entity Relationship Diagram (ERD) for the above scenario identifying the degree of the relationship between the entities and the primary and foreign keys. / [4]
2514 June 2007 Q2
5. / A new company, Beautiful Bird Tables (BBT, has been set up. The company makes and sells bird tables.
BBT holds records of the orders that shops place for bird tables in a relational database.
The entities and relationships in the relational database are:
SHOP(Shop_ID, Contact_Forename, Contact_Surname, Street, Town, Telephone_Number)
ORDER(Order_Code, Shop_ID, Stock_ID, Date, Quantity)
BIRDTABLE(Stock_ID, Description, Price, Delivery_Cost)
Create an Entity Relationship Diagram (ERD) for the above scenario identifying the degree of the relationship between the entities and the primary and foreign keys. / [4]
2514 June 2008 Q6
6. / A company manufactures electronic tills for use in shops.
The company has decided to write a database to store information about its sales.
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
Draw the Entity Relationship Diagram (ERD) making sure that the degree of each relationship is identified. / [6]
G061 January 2010 Q4
7. / As guests check-in at a hotel, they are currently registered on a database.
Three of the entities in the database are CUSTOMER, BOOKING and ROOM.
Draw an entity relationship diagram for this part of the database and state where a foreign key would be found. / [6]
G063 June 2010 Q7
8. / A restaurant books tables for customers using a database. The restaurant has more than one table.
Resolve the many to many relationship between TABLE and CUSTOMER. / [2]
G063 January 2011 Q6
9. / Miss Jones runs a landscaping garden company and wants to set up a database.
Four of the entities in the database will be CUSTOMER, SERVICE, JOB and JOBCARD.
CUSTOMERS: are people who own the gardens.
SERVICES:are the different tasks that the company can perform;
e.g. mowing the lawn or weeding.
JOBS:are where an appointment has been booked for a customer.
JOBCARDS:are the details of the services for a particular job.
Create an Entity Relationship Diagram (ERD) for the above scenario identifying the degree of the relationship between the entities and the primary and foreign keys. / [5]
G061 January 2011 Q4
10. / A mail order company stores details on customers, products, suppliers and orders in its database.
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]
G061 January 2012 Q5
11. / A hospital uses several different databases.
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 Entity Relationship Diagram (ERD) for the above scenario identifying the degree of the relationship between the entities and the primary and foreign keys. / [5]
G061 June 2012 Q8
12. / An art gallery 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 the art gallery’s requirements. / [2]
(b) / Create an entity relationship diagram for the relational database identifying the degree of the relationships between the entities. / [4]
G061 January 2013 Q10
13. / Greenfingers is a gardening business which provides mowing and weeding services to customers. The business uses a database to handle its appointments system.
Four of the entities in the completed database are:
- stock (for example, plants and fencing. Each stock item is supplied by one supplier.)
- suppliers (companies or individuals who supply the stock. An individual supplier can provide different items of stock.)
- customers (individuals who employ Greenfingers to provide gardening services)
- invoices (costs for each job, including stock used, completed for a customer by its employees)
G061 June 2015 Q8