Homework, The proprietors of the Tom Wiley Department Store

The proprietors of the Tom Wiley Department Store have approached you and asked if you could design a database to help them manage the complaints of their customers. They would like you to create a conceptual data model of their business information requirements and would like to see how the model could be used to provide the answers to some commonly requested queries and reports. ABC has provided the following information that it believes may be relevant to your task.
Tom Wiley operates stores in the following cities: Melbourne, Ballarat, Geelong, Sydney, Newcastle, Brisbane, Adelaide and Perth. Stores are referenced by store number. Tom Wiley also keeps store name, street, city, state, postcode, telephone, and managers details. Each store is assigned a supervising store where major customer complaints are referred, training is conducted, and server applications and help desk functions are located. The supervising store currently supervises stores within its own state boundaries but this is not necessarily going to continue in the future.
When a customer rings with a complaint, details of the complaint are to be recorded. The details include the date the complaint was made, the employee id of the person recording the complaint, the customers first name, last name, street address, town, state, post code and phone numbers. A customer may provide the employee taking the call with more than one type of phone number and include mobile, fax, home and office numbers. A complaint may be one of three different types. It may be related to the store, one or more employees or product(s) of the store. If a complaint is about the store a short description of the complaint is recorded and it is referred to the manager. If the complaint is about employees of the store, the ID(s) of the employees involved in the complaint are recorded, along with a short description of the problem. If the complaint is about products, the product id, the number of items and a short description are recorded. A customer may be involved in number of complaints. Currently complaints are provided with individual identification numbers. Once a complaint has been resolved, the date that the complaint was closed is recorded.
Whilst the complaint is open all contact with the customer is recorded. Details of the date, time, the employee making contact, the type of contact (phone, fax, email or personal visit) and a short description of the contact are all recorded.
If the complaint is about a product, a replacement is normally provided. The store likes to keep track of all of the products replaced and the date the items were sent to the customer.
The store would also like you to interface your database with their products table in their inventory database. The products table has fields including ProductId, ProductName, ProductDescription, ProductUnitCost.
You are required to design (using an E-R diagram) an entity-relationship model of the problem, convert the model into a relational model, assess the normal form of each schema and write SQL queries that will answer the following queries.
1. An alphabetically sorted list of all customers who have made a complaint. Only customer number and name are required.
2. A more complete customer list sorted by customer id. It should contain customer id, name, address and all available phone numbers.
3. The date on which the most recent complaint has been made. The date itself will suffice.
4. A list of all complaints still open. Displaying complaint number will be sufficient.
5. A list of all complaints sorted by the type of the complaint. Displaying the complaint identification number, the customer id, the date the complaint was made and the type of complaint will be sufficient.
6. A list of all products involved in the customer complaints. Display the product id and name, sort this using the product name.
7. A total of the cost price of all products replaced. Displaying the total amount will be sufficient.
8. A list of all customers with more than 4 complaints. The customer id and name should be displayed.
9. A list showing the total number of complaints made about employees in each department. Displaying the department id and the total number of complaints is sufficient.
10. A customer list for all complaints still open that shows when the customer was last contacted. The customer id, name, last date of contact and type should be displayed, the list should be listed in descending date order.
What to submit
1. An entity relation diagram that models the problem which includes:
a. all entities, relationships (including names) and attributes that are relevant,
b. primary keys identified,
c. include cardinality (one / many) and participation (optional / mandatory) symbols,
d. assumptions you have made, e.g. how you arrived at the cardinality / participation for those not mentioned or clear in the business description, etc.
The E-R should be completed using the standards of this unit.
2. Relational data structures that translates your E-R diagram which includes:
a. relation names,
b. attribute names,
c. primary keys identified
d. For each relation show the level of Normalisation achieved, and for any not to Third Normal Form, explain why.
The data structures should be shown using the standards of this unit.
3. A relational database schema that translates your relational data structures which includes:
a. table names,
b. column names and field types
c. primary keys identified
4. A hardcopy of the data in your database (developed using MySQL), this should contain sufficient data in eachtable to demonstrate that your queries work.
5. An electronic copy of your database, with all records saved in the database. id _firstname_lastname.sql
Example 123456_Sunam_Pradhan.sql
6. Hardcopy of each query and the result of the query (screen capture). You should submit all design work, program documentation, and relevant sample screen shots of your implementation.

Order a unique copy of this paper
(550 words)

Approximate price: $22

Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

We value our customers and so we ensure that what we do is 100% original..
With us you are guaranteed of quality work done by our qualified experts.Your information and everything that you do with us is kept completely confidential.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

The Product ordered is guaranteed to be original. Orders are checked by the most advanced anti-plagiarism software in the market to assure that the Product is 100% original. The Company has a zero tolerance policy for plagiarism.

Read more

Free-revision policy

The Free Revision policy is a courtesy service that the Company provides to help ensure Customer’s total satisfaction with the completed Order. To receive free revision the Company requires that the Customer provide the request within fourteen (14) days from the first completion date and within a period of thirty (30) days for dissertations.

Read more

Privacy policy

The Company is committed to protect the privacy of the Customer and it will never resell or share any of Customer’s personal information, including credit card data, with any third party. All the online transactions are processed through the secure and reliable online payment systems.

Read more

Fair-cooperation guarantee

By placing an order with us, you agree to the service we provide. We will endear to do all that it takes to deliver a comprehensive paper as per your requirements. We also count on your cooperation to ensure that we deliver on this mandate.

Read more

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
The price is based on these factors:
Academic level
Number of pages