In: Computer Science
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.
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
Have a nice day :)