Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Task 3 - Entity - Relationship Modelling 1 ( 5 marks ) You have been hired to design a database system for an online toy

Task 3- Entity-Relationship Modelling 1(5 marks)
You have been hired to design a database system for an online toy store. The database must keep track of
the customers, suppliers, products, product categories, and orders. You have the following information
about the way the store operates:
Customer details must be recorded. This includes a customer number, first name, last name, email address
and password.
The store wishes to implement a "referral system" to reward customers who tell others about the
store. Therefore, customer details should also include a "referrer" column, which will contain the
customer number of the customer who referred them, if applicable (not all customers are referred by
someone).
Details of item suppliers must be recorded (i.e. the sources of the items that they sell). This includes a
supplier ID number, business name, phone number and website URL.
Details of products must be recorded. This includes an item ID number, name, description, price (how much
the store sells it for), cost (how much it costs the store to buy) and stock (how many of the item the store
currently has available).
The store keeps track of the supplier of each product. Products only have one supplier.
A list of item categories must be recorded, and the database must keep track of which items are in which
categories. All items are in at least one category, but can be in several of them.
The only category details required are a category ID number and a category name.
Details of orders made by customers must be recorded. This includes an invoice ID number, the date/time of
the order, a delivery address and a billing address, as well as the customer ID number of the customer who
made the order.
For each order, the database must record details of ordered products (i.e. which products were included in
the order) and the quantity ordered. Each order must contain at least one item.
Based on the details above, you are required to draw both a logical E-R diagram for this database and then a
corresponding physical E-R diagram. Clearly show all cardinality, primary and foreign keys, attributes and relationships
as appropriate.
Adhere to the distinctions between logical and physical E-R diagrams defined in Lecture 3.
Use enhanced E-R model notation where/if appropriate, and remember to state your assumptions.
image text in transcribed

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Database Principles Programming And Performance

Authors: Patrick O'Neil

1st Edition

1558603921, 978-1558603929

More Books

Students also viewed these Databases questions