Tiny Tots, Inc. (TTI) is a national chain of stores selling the latest fashions in children's clothing. TTI has been experiencing rapid growth and plans to double in size in the next 2 years. To help m anage this growth and better track its revenues and expenses, TTI needs a relational database that will satisfy the following requirements.

1. For each of its stores, TTI keeps track of the store's ID (C2), name (C 15), state (C2), size in squa re feet (D4, 0), region code (C1) and region name (C9), monthly rent (D4, 0), the percent of sales that are paid by credit card (D4, 0), and the IDs (C2), names (C20) and commission percentages (D2, 0) of all the salespeople working at the store. Each sal esperson can work at only one of TTI's stores. In addition, TTI keeps track of the store type (C2) and general description of the story type (C12); i.e. mall store or strip center store.

2. For each piece of clothing that TTI sells in its stores, TTI kee ps track of the style number (C6), the name (C10) and address (C 15) of the vendor who supplies the style to TTI and the cost of the style -- what TTI paid the vendor per garment (D5, 2). In addition, each piece of clothing is assigned a specific departme nt -- size ranges like infants, toddlers, etc. (C12), a description (C20), and a retail price (D5, 2). TTI frequently buys the same style in multiple colors. TTI also tracks the style's gross profit margin -- the difference between retail and cost divide d by retail (D5,2). Each style is supplied by only one vendor, but a vendor may supply several different styles. Each store does not necessarily sell every style - regional differences in sales patterns dictate different stores carry different mixes of styles.

3. Each week (D1, 0), TTI tabulates and keeps the items sold; i.e. TTI records the number of units sold (D2, 0) by each salesperson for each style of clothing, the total dollar sales generated (D8,2) by the salesperson, and the commission TTI paid on those sales (D5,2).

4. The database must be able to handle the following queries:

a. For each style, list the style number, description, department name, vendor ID, vendor name, cost and retail. Sort the listing in style number, starting with the highest number.

b. For each boys' style (departments 00, 20 & 40), list the style number, description, and vendor name. Display the listing in alphabetical order by vendor name.

c. For each store, list the store ID, store name, state, region name, monthly rent, and re nt per square foot. Display the listing in alphabetical order by store name.

d. List the store ID, store name and monthly rent of all stores with monthly rent higher than the chain's average monthly rent. Display the listing from the store with the highest rent to the lowest.

e. For each region, list the region code, the number of mall stores and the number of strip center stores in the region. Display the listing in reverse alphabetical order by region code.

f. For each week, list the week number, the number of units sold and the total dollar sales for that week (number of garments sold * retail).

g. For each salesperson, list the sales ID, name, total dollar sales for the first month (weeks 1 - 4), and the total commission earned by the salesperson. Sort the listi ng in order from highest to lowest monthly sales.

h. For each style, list the department name, style number, style description, cost, retail, and gross profit margin (the difference between retail and cost divided by retail - expressed as a percentage, i.e. 4 7.38%). Display the listing in alphabetical order by department name.

i. For each vendor, list the vendor name, average cost, average retail, and average gross profit margin of styles provided by the vendor. Display the listing in alphabetical order by vendor name.

j. List the sales ID, name and total monthly dollar sales for each salesperson whose monthly sales were higher than the average salesperson's monthly sales. Display the listing in alphabetical order by salesperson name.

k. For each vendor that supplies T TI with more than 5 styles, list the vendors name, and average cost and average retail of the vendor's styles. Display the listing in alphabetical order by vendor name.

l. For each department, list the department number and total dollar sales. Display the listing in descending order by department number. m. For each store type, list the description, number of stores of that type and the average rent for that store type.

n. For the store with the highest net profit, list the store id, store name, total sales, tota l commission, rent, credit card fees, and net profit. (A stores net profit is computed as its total sales less cost of goods sold less monthly rent less commission owed to sales staff less credit card fees owed. TTI pays the credit card companies 3% of all credit card sales, so credit card fees owed is calculated as the stores total sales * the stores credit percent * 3%).

o. For the region with the highest net profit, list the region code, region description, net profit and the percent of overall corpora te profits represented by the region. (Round the percent to the nearest tenth percent).

p. For all sales staff who earned at least $50 in commission for the month, list the salespersons id, name, commission earned and the percentage of the chains total com mission. (Round the percent to the nearest tenth of a percent.) Display the listing starting with the best salesperson (most commission earned).

q. For all styles with at least $500 in sales and a gross margin of at least 46%, list the style number, descrip tion, vendor name, cost, retail, and gross margin. Display the listing in ascending order by retail. (Profit margin is the difference between retail and cost divided by retail expressed as a percentage.)

r. For the two infant departments combined, list the total infant profits and the percent of the chains profits represented by the infant departments rounded to the nearest hundredth of a percent. (Department profits are calculated as the total dollar sales less cost of goods sold less commission less cred it fees).

Deliverables for this project:

1. Given the description of the case and considering possible queries, as described in section 4, design an appropriate database for this project.

a. Definition of all the needed tables.

b. A data dictionary

c. An E-R diagram, include all connectivities and cardinalities

d. A detailed description of the normalization process, all tables should be in 3rd NF.

e. SQL commands to create the needed tables.

f. Run the create command and enter at least 3 data points in each table that you have.

g. An executive summary of your design process. Include a justification for any additional assumptions that you have made.