Question

In: Computer Science

SDS operates stores in the following cities: Melbourne, Ballarat, Geelong, Sydney, Newcastle, Brisbane, Adelaide and Perth....

SDS operates stores in the following cities: Melbourne, Ballarat, Geelong, Sydney, Newcastle, Brisbane, Adelaide and Perth. Stores are referenced by store number. SDS also keeps store name, street, city, state, postcode, telephone, and manager's 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, postcode 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 is 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 a 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 its products table in its 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.
  1. The date on which the most recent complaint has been made. The date itself will suffice.
  2. A list of all complaints still open. Displaying complaint number will be sufficient.
  3. 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.
  4. A list of all products involved in customer complaints. Display the product id and name, sort this using the product name.
  5. A total of the cost price of all products replaced. Displaying the total amount will be sufficient.
  6. A list of all customers with more than 4 complaints. The customer id and name should be displayed.
  7. 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.
  8. 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.

Solutions

Expert Solution

Below is the ER-diagram

Below is the Relational Model

All the above tables are in 3NF form.

SQL queries (working in SQL server) for above requirements :
An alphabetically sorted list of all customers who have made a complaint. Only customer number and name are required.
select customerId, CONCAT(firstName, " ", lastname) as name
from CUSTOMER
where customerId in (select distinct(customerId) from COMPLAINT)
order by CONCAT(first, " ", lastname);

A more complete customer list sorted by customer id. It should contain customer id, name, address and all available phone numbers.

select b.cutomerId,
CONCAT(b.firstName, " ", b.lastname) as name,
CONCAT(streetAddress, " ", town, " ", state, " ', postCode) as address,
(select STRING_AGG([contact], ', ') from CUSTOMER_CONTACT a where a.customerId = b.customerId) as contact
from CUSTOMER b
where b.customerId (select distinct(customerId) from COMPLAINT)
order by b.customerId

  1. The date on which the most recent complaint has been made. The date itself will suffice.
    select max(openingDate) from COMPLAINT;
  2. A list of all complaints still open. Displaying complaint number will be sufficient.
    select identifier from COMPLAINT where closeDate is null;
  3. 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.
    select identifier, customerId, openingDate, type
    from COMPLAINT
    order by type.
  4. A list of all products involved in customer complaints. Display the product id and name, sort this using the product name.
    select productId, name from PRODUCT
    where productId in (select distinct(productId) from PRODUCT_COMPLAINT)
    order by name;
  5. A total of the cost price of all products replaced. Displaying the total amount will be sufficient.
    select sum(cost) from (
    select a.itemCount *  b.unitCost as cost
    from PRODUCT_COMPLAINT a, PRODUCT b
    where a.productId = b.productId and a.replacementDate < SYSDATE)
  6. A list of all customers with more than 4 complaints. The customer id and name should be displayed.
    select customerId, CONCAT(firstName, " ", lastName) as name
    from CUSTOMER
    where customerId in (select customerId from COMPLAINT group by customerId having count(*)>4)
  7. 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.
    select a.department, count(*)
    from EMPLOYEE a, EMPLOYEE_COMPLAINT b
    where a.employeeId=b.employeeId
    group by a.department
  8. 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.
    select a.customerId, concat(b.firstName, " ", b.lastName) as name, a.lastContactDate, a.contactTypeUsed
    from COMPLAINT a, CUSTOMER b
    where a.customerId=b.customerId
    order by a.lastContactDate desc

Have a nice day :)


Related Solutions

Which of the following statements regarding the characteristics of house prices in Sydney and Melbourne is...
Which of the following statements regarding the characteristics of house prices in Sydney and Melbourne is INCORRECT? Select one: a. Long-run returns from property investment are comparable to returns from the stock market. b. The equity beta of property price indices is low, meaning a low correlation between house prices and stock market movements. c. The low equity beta of property price indices suggests the property investment can be a good diversification strategy for stock market investors. d. In recent...
City Bagel operates four bagel stores in New York. The owner has provided the following budgeted...
City Bagel operates four bagel stores in New York. The owner has provided the following budgeted data for next year. Revenue $11,419,000 Fixed Costs $3,308,000 Variable Costs (depends on the # of bagels sold) $7,820,000 For each of the following scenarios, determine the dollar impact on City Bagel. Consider each scenario independently. Do not enter dollar signs or commas in the input boxes. Round all answers to the nearest whole number. Enter all values as positive values. Do not use...
ABC Inc, a Canadian retailer which operates more than 1,000 stores in Canada, reported the following...
ABC Inc, a Canadian retailer which operates more than 1,000 stores in Canada, reported the following balances as at December 31, 2019: 7% Par $100 convertible bonds, issued at par                                          $     250,000       3,000 call options, each entitled to purchase 1 common share Cumulative Preferred shares, 36,000 convertible shares outstanding      $     960,000 Common shares, 112,500 shares issued and outstanding                             2,880,000 Contributed surplus on repurchase of common shares                                      31,200 Retained earnings                                                                                         1,032,000 ABC Inc. applies IFRS. The company also informed you details related to the following transactions during 2020:...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT