A2Z Salon Inc. (A2Z), a beauty care organization, currently has ten stores in a metro area. It caters to the beauty and hair care needs of both female and male clients. In addition, each salon sells beauty products such as shampoos, conditioners, cosmetics, fragrances, etc. Each salon employs qualified beauticians with relevant beauty certifications from trade schools. During a typical business day, each A2Z salon serves at least 10 customers per beautician. A customer generally sets up an appointment ahead with a beautician, but walk-ins without prior appointments are also allowed depending upon customer traffic. During a visit a customer could order several services such as hair styling, hair color, pedicure, manicure, hair shampoo, etc. and/or buy some beauty products. The business has been growing rapidly over the past year, and the CEO/owner Jane Weber wants to install a new information system for managing customer ordering and payment processing across salons.

A2Z Salon Inc. offers an optional membership to clients for a fixed annual fee (currently priced at $25). Members are offered discount prices on all products and services. Members also are sent promotions on a regular basis. For all customers, their styling preferences are stored in the system so that beautician could understand the needs and preferences of a customer during salon visits. Each salon accepts customer payments in terms of cash, credit card or check.

Create the following below:

List of Entities: All required entities should be identified including any associative entities identified for resolving M:N relationships (10 total)

Business Rules: Rules that involve the above mentioned entities (two for each relationship on the ERD) "2 rules for each entity"

ER Diagram: This diagram should resolve all M: N relationships. This should clearly identify the major attributes of various entity sets along with Primary/Foreign keys. Show appropriate connectivity. Use MS Visio for creating ERD.

Implementation in SQL Server: Create 10 tables and Insert at least five rows into each of the created tables using SQL statements (You can make up the data).Turn in the SQL script used both for creating tables and adding data.

SQL Queries: At least 10 queries for generating management information using joins (involving at least two tables in each query and three tables in three of the queries), aggregate functions, “group by” functions etc. should be turned in along with screenshot printout of your results. Please don’t forget to indicate the management information expected from each query. Also, you should try to write queries to pull interesting information so that all the tables implemented in your database are covered in your queries.

Answer rating: 100% (QA)

List of Entities 1 Customer 2 Beautician 3 Salon 4 Product 5 Service 6 Appointment 7 Membership 8 Payment 9 Promotion 10 Preference Business Rules 1 A customer must be associated with a salon 2 A cust... View the full answer