High Fashion Inc.

High Fashion Inc.
Project Milestone #1
High Fashion Inc. (HFI) is a retailer that offers a variety of high-quality, fashion products for men, women and children. Since the beginning of the company in 2000, HFI managed to create a brand name synonymous with a distinct style which reflects luxury and excellence. With its classic and timeless collections, HFI operates twenty stores throughout the US and draws a sophisticated clientele that also includes famous Hollywood stars such as Jodie Foster, Brad Pitt, and Meryl Streep, to name a few. Dr. Virginia Ilie, the CEO of HFI recently recognized the need for the company to modernize its operations and create a centralized database in order to better keep track of the sales and product offerings for its twenty US stores. The new database will also help HFI expand its market presence and product offerings in the future. As a first step in that direction, Dr. Virginia has decided to hire your team as consultants, to design and implement a centralized database that keeps track of current business operations. The database is to be implemented in M.S. SQL Server. The detailed requirements for the database are presented below.
As a starting point in the requirements determination, Dr. Virginia informed you the database needs to keep track of HFI’s nationwide stores and inventory. For each of its twenty stores, HFI needs to keep track of the store number, store address and phone number together with a tax rate associated with each store. Each store has its own product inventory. As such, HFI’s database should be able to track the product inventory for each of its stores. While HFI has the same inventory price for all products, the quantity in stock available for each product in each store may vary. In other words, HFI store #1 may have 10 leather handbags while HFI store #12 may only have 5 leather handbags available in stock. The database should keep track of the information about the product inventory including a unique identification number for each product, a style number together with the product name, description, color, size and an inventory price. The system should also record the product category for each product (men, women or children).
Although all its products are designed in the US by local designers, in order to save on cost, HFI uses various manufacturers in China, India, Romania and Hong Kong (in addition to its US manufacturers). Basic information about the manufacturers should be stored in the database including company name, address, country and contact info such as phone and email address. While it is possible for a manufacturer to supply multiple products to HFI, each product should be tracked to one manufacturer only.
Customers can make many purchases (sales). Basic information about each customer (such as name, address, phone and email) is to be collected at the time of sale. This information will be further used by HFI for marketing purposes. For each sale, a unique identification number, a receipt number and the register number the sale has been recorded at together with the date of the sale (and the time of the sale, e.g. 1:45PM), the tax rate and method of payment (cash, credit card or check) should be stored (Note: the database will not store the actual credit card number if payment is done via credit card). Each sale should also record any discount code and discount percent which may apply to that sale. Furthermore, as HFI desires to have an integrated database, the system should be able to track which sale is made at which store and which items were purchased as part of that sale. The selling price and the quantity of each item sold should also be recorded for each product sold in a store at the time of sale (see table #1).
Table #1
Store Number Sale Items Selling price Quantity sold
1 STR0650 HFI Flagrance $89 1
1 STR0650 HFI Jacket $499 1
12 VCT877 HFI Swimwear $149 1
14 BVT899 HFI Handbag $639 1
14 BVT899 HFI Swimwear $199 1
14 BVT899 HFI Bottoms $149 1
HFI uses sales associates who work in each of the twenty stores. Basic information about each sale associate needs to be recorded such as associate’s name, address, phone number and salary. Naturally, each sale associate can only work in one store. Each sale associate provides help to customers in the store and as such, they may be credited for making multiple sales. Furthermore, some of the sales associates are store managers and supervise the other sales associates. Each manager can supervise many sales associates in the store, however each sales associate has one manager only. The database should record these relationships.
HFI uses a rather aggressive marketing strategy to keep close touch with its customers. As such, almost every month, HFI customers are sent various coupons announcing a sale. A coupon number, coupon type (mail or email) and a date should be stored for each marketing promotion. Note that for each coupon sent to each customer there is an associated discount code and a discount percent. Customers may be sent multiple such coupons throughout the year. Note that these coupons are generic in nature, in other words, the same coupon may be sent to multiple customers.
Other important requirements for the database revolve around customer returns. At present, HFI finds it very difficult to monitor the returns to each of its stores. There are two types of returns that may take place: returns with receipt and returns without receipt. For all returns, a date and a reason for the return must be stored. For the returns without a receipt, a voucher number must also be stored, as the customer will be given a store credit rather than money back.
For each return with receipt, HFI needs to be able to monitor the original sale information. and each item to be returned). Table #2 provides an example of how data are to be stored. Table #2
Return with receipt Sales Item
Return ID1 Sale ID1 Handbag
Return ID1 Sale ID1 Flagrance
Return ID2 Sale ID2 Swimwear
Return ID3 Sale ID3 Watch
For each return without receipt, HFI needs to gather customer information and the items to be returned. The amount for each item returned (at the current selling price) and the tax rate should also be stored. Table #3 provides an example of how data are to be stored.
Table #3
Return without receipt Customer Item Amount Tax rate
Return ID4 Joe Smith Handbag $255 9%
Return ID4 Joe Smith Flagrance $99 7.5%
Return ID5 Marsha Brown Swimwear $149 8%
Return ID6 Trish John Watch $399 $31.92
Problem Requirements:
Your job as consultants is to assist Dr. Virginia to create a data model (ERD) to represent the data she needs to keep track of in a relational format. Your ERDs (should use a professional diagramming tool such as MS. VISIO) must include all required entities, attributes and relationships. Be sure to properly indicate primary keys for each entity as well as all minimum and maximum cardinalities together with any attributes on relationships. You must also resolve any composite and multivalued attributes.
Submission Instructions:
• You will prepare 2 enhanced ERDs: (1) ERD#1 which shows all entities, attributes and relationships (including the M:N relationships), and (2) ERD#2 which is the same as ERD#1 but it contains the resolved M:N relationships into associate entities using the rules discussed in class and in your text.
• Each one will submit the following:
1. One M.S. VISIO file containing: (a) the group ERD #1 (with all M:N relationships) and (b) group ERD#2 (resolving all M:N relationships).
2. One M.S. Word file containing; (a) group ERD#1 that includes all original M:N relationships and (b) group ERD #2 that resolves all M:N relationships into associative entities.
Note: in the Word file, both ERDs should be included as images (print screens are not acceptable).
For all ERDs you submit, please be sure to…
• Resolve any composite and multivalued attributes.
• List ALL assumptions that you have made (that augment but do not violate given requirements).
• Use M.S. Visio (and the stencil). All ERDs should be professionally prepared (this includes alignment on the page, lines not crossing, and general diagram appearance.
• Follow all naming conventions discussed in class for naming entities, attributes and relationships.
Order Now

Calculate a fair price for your paper

Such a cheap price for your free time and healthy sleep

1650 words
-
-
Place an order within a couple of minutes.
Get guaranteed assistance and 100% confidentiality.
Total price: $78
WeCreativez WhatsApp Support
Our customer support team is here to answer your questions. Ask us anything!
👋 Hi, how can I help?