You will need to maintain a list of all fashion products carried. For each store, you will need to…
Transcribed Image Text:
You will need to maintain a list of all fashion products carried. For each
store, you will need to maintain the inventory of all products. For each
product, you track a list price, but for now, you require the price to be the
same across all stores. You dream of someday opening stores across
Canada and know the price will vary across Canada, but for now, you want
to focus on the minimum viable product and build the necessary scalability
into the database. You do anticipate opening more stores throughout
You also have an old customer database where you track customers. Their
purchases are tracked via invoices, including the quantity, list price,
percentage discount, and price paid for each item. At the time of sale, the
list price matches the list price for the product, but the product list price
can change; whereas the invoice list price remains the same.
Unfortunately, this database has been corrupted and you need to start
from the beginning.
Product are grouped into product lines (formal and casual), although you
anticipate adding wedding clothes in the future.
Products are further classified into different categories (for example shirt,
shoes, belts etc.).
Each product may belong to only one category.
Additionally, products are associated with specific tags to help identify
sales opportunities. For instance, shirts may be associated with the tags
like “spring”, “3 summer”, “fall”, “winter”, “wool”, “synthetice”, etc. Additional Detail
While not all products are available at all stores, there is a large overlap in
In addition to the invoice percentage discount for each item, there may be an
overall discount applied to a complete invoice.
For each customer, an address and list of phone numbers are tracked. Previously
you only allowed customers to enter a home and mobile number, but you found
many customers are giving an alternate mobile number and you have decided the
new database should support as many phone numbers as customers choose to
give. At least one phone number is required for easy lookup.
For small quick purchases, without a discount, there is a “guest” customer that
is associated with the purchase. This customer is associated with the phone
Stores are identified by a short name (e.g. “Stephen Avenue” is short for and a
full name of “Calgary – Stephen Avenue”, or “Mountain” for “Canmore –
Mountain”). It is important to understand what products are sold at which
Although somewhat redundant based on the list price and percentage discount,
the actual sell price and total invoice amount are both stored in the database fo
easy querying later.
Products are sold either by item, or size or weight. You can’t be sure that
you won’t allow additional methods (such as length for measuring rope) in the
future, so you have decided to create a table to allow for new methods to b
Assume there is no sales tax on any of the items. ASSIGNMENT
Create an Entity Relationship
Diagram (ERD) for your fashion shop
situation. Include the cardinality of
relationships and make sure to
define the primary (PK) keys (s),
foreign (FK) key(s), composite (CK)
keys and which attributes are
involved in each relationship. All
bridging tables should make use of
a composite key. Many-to-Many
relationships may not be used, and
your final product should be in 3rd
Answer rating: 100% (QA)
I m unable to directly create an Entity Relationship Diagram ERD because I cant generate images However I can describe how to create an ERD for your fashion shop situation based on the information giv
View the full answer