Design a Database

Design a database using the following information. Using Microsoft Visio 2013, create a complete E-R Diagram in Crow’s Foot notation (including entity names, attributes, primary keys and foreign keys) that can be implemented, and then implement it in Microsoft Access. Your completed assignment will have two files submitted: the Crow’s Foot ER diagram and the Microsoft Access database file (an .accdb file).

My Closet is a small online company that sells dresses. All customer orders to My Closet are ordered from the dressmaker at the time of sale.

My Closet can order dresses from multiple dressmakers. Every dressmaker produces one or more different styles of dresses. Every style is produced by only one dressmaker. Every style has one or more colors of dresses. Every dress color made is belongs to only one style. Every dressmaker is identified by a dressmaker code. The dressmaker name, address, email, phone number, and account number are kept for every dressmaker. Every dress style is identified by a style code. The style name and price are kept for every style. Every color is identified by a color code. The color name and the available sizes for that color are kept for every color.

note:use APA format

computer

Order Description
part 1

Amanda has another set of orders for you to enter on the Customer Orders worksheet. In
addition to calculating the charge for creating the DVDs, Amanda also wants to include
the cost of shipping in the total charged to each customer.
1. Open a blank workbook, and then save the workbook as Order Report.
2. Rename the Sheet1 worksheet as Documentation, and then enter the following data into the specified cells:
Cell A1: RipCity Digital
Cell A3: Author Cell B3: your name
Cell A4: Date Cell B4: =today()
Cell A5: Purpose Cell B5: To track customer orders for RipCity Digital
3. Insert a new worksheet (plus sign to right of previous worksheet), and rename the new Sheet2 worksheet as Customer Orders.
4. On the Customer Orders worksheet, enter the following data into the specified cells:
Cell A1: RipCity Digital
Cell A3: Customer Orders Report
Cell A4: March 27 to April 17, 2015
5. In cells A5 through H10, enter the column titles and data from Figure_One. Wrap text accordingly.
Figure_One Date LastName FirstName Address Phone DVDs Price per DVDFigure_One Date LastName FirstName Address Phone DVDs Price per DVD Shipping Charge
3/27/2015
Flemming
Doris
25 Lee St. Bedford, VA 24523
(540) 555-5681
7
$18.29
$7.49
4/4/2015
Ortiz
Thomas
28 Ridge Ln. Newfane, VT 05345
(802) 555-7710
13
$16.55
$9.89
4/8/2015
Dexter
Kay
150 Main St. Greenbelt, MD 20770
(301) 555-8823
25
$15.79
$7.23
4/9/2015
Sisk
Norman
250 East Maple Ln. Cranston, RI 02910
(401) 555-3350
15
$16.55
$10.55
4/17/2015
Romano
June
207 Jackston Ave. Westport, IN 47283
(812) 555-2681
22
$15.79
$13.956.
6 Set the width of column A to 10 characters, columns B and C to 12 characters, column D to
20 characters, and columns E, G, and H to 16 characters. (Select column, Home tab, Cells
group, Format, Column Width)
7. Autofit all rows in the worksheet to the cell contents. (Select row, Home, Cells group,
Format, AutoFit Row Height)
8. In cell I5, enter Total Charge. In cell I6, insert a formula that calculates the total charge
for the first customer (the number of DVDs multiplied by the price per DVD and then added
to the shipping charge). Increase the width of column I to 11 characters.
9. Copy the formula in cell I6 and paste it into the range I7:I10.
10. In cell E11, enter Total DVDs. In cell F11, use the SUM function to calculate the total
number of DVDs created for all customers. In cell I11, use the AutoSum feature to insert
the SUM function to calculate the total charges for all of the customer orders.
11. Use Edit mode to make the following corrections:
a. In cell D6, change the street address from ?25 Lee St.? to 2500 Lee St.
b. In cell F9, change the number of DVDs from ?15? to 17.
c. In cell H8, change the shipping charge from ?$7.23? to $8.23.
12. Use the Find and Replace commands to replace all occurrences of ?St.? with Street, ?Ln.?
with Lane, and ?Ave.? with Avenue.
13. Fill color cell range A5:I5 to a color of your choosing. Surround each cell (all cells) with a
border.
14. Change the page layout of the Customer Orders worksheet to print in landscape orientation
on a single page.
15. Return the view of the Customer Orders worksheet to Normal view, save your changes to
the Order Report workbook, and then save the current workbook as Revised Report.
16. Remove Kay Dexter?s order from the Customer Orders worksheet.
17. Add the following order directly after the order placed by June Romano: date 4/22/2015;
name Patrick Crawford; address 200 Valley View Road, Rome, GA 30161; phone (706)
555-0998; DVDs 14; price per DVD $16.55; shipping charge $12.45.
18. Use Edit mode to change the ending date of the report in cell A4 from ?April 17? to April
22.
19. Save the workbook and preview it for correctness.
20. Submit all saved workbooks to the instructor?s gradebook via Blackboard. No emails of
homework allowed/accepted

part 2
Altac Bicycles Deborah York is a financial consultant for Altac Bicycles, an online seller of bicycles and bicycle equipment based in Silver City, New Mexico. She has entered some financial information in an Excel workbook for an income statement she is preparing for the company. You will enter the remaining data and formulas.
1. Open the Altac workbook, and then save the workbook as Altac Bicycles.
2. Insert three new rows at the top of the Sheet1 worksheet, and then enter the following text on two lines within cell A1: (In cell A1, enter the first line of text, and then hold down the Alt key and press the enter key, and then enter next line of text.)
Altac Bicycles
Income Statement*
3. In cell A2, enter For the Years Ended December 31, 2012 through December 31, 2014.
4. In the range C6:E7, enter the following net sales and cost of sales figures: 2014 2013 2012
Net Sales 13,520 10,981 9,034
Cost of Sales 4,140 3,960 3,011
5. In the range C11 :E14, enter the following expense figures (expand the column widths as necessary to show the text and data values):
2014 2013 2012
Salaries and Wages 1,632 1,481 1,392
Sales and Marketing 2,631 2,112 1,840
Administrative 521 410 375
Research and Development 501 404 281
6. In the nonadjacent range C18:E18;C20:E20;C24:E24, enter the following values for Other Income, Income Taxes, and Shares, pressing the Enter or Tab key to navigate from cell to cell in the selected range:
2014 2013 2012
Other Income 341 302 239
Income Taxes 1,225 1,008 821
Shares 3,621 3,001 2,844
7. In the range C8:E8, enter a formula to calculate the gross margin for each year, where the gross margin is equal to the net sales minus the cost of sales.
8. In the range C15:E15, enter the SUM function to calculate the total operating expenses for each year, where the total operating expenses equal the sum of the four expense categories.
9. In the range C17:E17, enter a formula to calculate the operating income for each year, where operating income is equal to the gross margin minus the total operating expenses.
10. In the range C19:E19, enter a formula to calculate the pretax income for each year, where pretax income is equal to the operating income plus other income.
11. In the range C22:E22, enter a formula to calculate the company’s net income for each year, where net income is equal to the pretax income minus income taxes.
12. In the range C25:E25, enter a formula to calculate the earnings per share for each year, where
earnings per share is equal to the net income divided by the number of shares.
13. Use the spelling checker to correct and replace any spelling errors in the worksheet. Ignore the
spelling of “ALtac.”
14. In cell A18, use Edit mode to capitalize the word “income.”
15. Increase the width of column A to 18 characters and increase the width of column B to 25
characters. Autofit the height of row 1.
16. Rename the Sheet1 worksheet as Income Statement.
17. Add a new worksheet, Sheet2, and rename the Sheet2 worksheet as Documentation and move it to
the beginning of the workbook.
18. In the Documentation worksheet, enter the following text and values:
Cell A1: Altac Bicycles
Cell A3: Author
Cell A4: Date
Cell A5: Purpose
Cell B3: your name
Cell B4: the current date
Cell B5: Income statement for Altac Bicycles for 2012 through 2014
19. Save the workbook, preview the workbook and make sure each worksheet in portrait orientation fits
on one page.
20. Close the workbook, and then submit the finished workbook via Blackboard to the instructor?s
gradebook.
Year 2014 2013 2012
Sales
Net Sales
Cost of Sales
Gross Margin

Expenses
Salaries and Wages
Sales and Marketing
Administratibe
Research and Development
Total Operating Expenses

Operating Income
Other income
Pre-tax Income
Income taxes

Net income

Shares
Eanings per share

computer

Order Description
part 1

Amanda has another set of orders for you to enter on the Customer Orders worksheet. In
addition to calculating the charge for creating the DVDs, Amanda also wants to include
the cost of shipping in the total charged to each customer.
1. Open a blank workbook, and then save the workbook as Order Report.
2. Rename the Sheet1 worksheet as Documentation, and then enter the following data into the specified cells:
Cell A1: RipCity Digital
Cell A3: Author Cell B3: your name
Cell A4: Date Cell B4: =today()
Cell A5: Purpose Cell B5: To track customer orders for RipCity Digital
3. Insert a new worksheet (plus sign to right of previous worksheet), and rename the new Sheet2 worksheet as Customer Orders.
4. On the Customer Orders worksheet, enter the following data into the specified cells:
Cell A1: RipCity Digital
Cell A3: Customer Orders Report
Cell A4: March 27 to April 17, 2015
5. In cells A5 through H10, enter the column titles and data from Figure_One. Wrap text accordingly.
Figure_One Date LastName FirstName Address Phone DVDs Price per DVDFigure_One Date LastName FirstName Address Phone DVDs Price per DVD Shipping Charge
3/27/2015
Flemming
Doris
25 Lee St. Bedford, VA 24523
(540) 555-5681
7
$18.29
$7.49
4/4/2015
Ortiz
Thomas
28 Ridge Ln. Newfane, VT 05345
(802) 555-7710
13
$16.55
$9.89
4/8/2015
Dexter
Kay
150 Main St. Greenbelt, MD 20770
(301) 555-8823
25
$15.79
$7.23
4/9/2015
Sisk
Norman
250 East Maple Ln. Cranston, RI 02910
(401) 555-3350
15
$16.55
$10.55
4/17/2015
Romano
June
207 Jackston Ave. Westport, IN 47283
(812) 555-2681
22
$15.79
$13.956.
6 Set the width of column A to 10 characters, columns B and C to 12 characters, column D to
20 characters, and columns E, G, and H to 16 characters. (Select column, Home tab, Cells
group, Format, Column Width)
7. Autofit all rows in the worksheet to the cell contents. (Select row, Home, Cells group,
Format, AutoFit Row Height)
8. In cell I5, enter Total Charge. In cell I6, insert a formula that calculates the total charge
for the first customer (the number of DVDs multiplied by the price per DVD and then added
to the shipping charge). Increase the width of column I to 11 characters.
9. Copy the formula in cell I6 and paste it into the range I7:I10.
10. In cell E11, enter Total DVDs. In cell F11, use the SUM function to calculate the total
number of DVDs created for all customers. In cell I11, use the AutoSum feature to insert
the SUM function to calculate the total charges for all of the customer orders.
11. Use Edit mode to make the following corrections:
a. In cell D6, change the street address from ?25 Lee St.? to 2500 Lee St.
b. In cell F9, change the number of DVDs from ?15? to 17.
c. In cell H8, change the shipping charge from ?$7.23? to $8.23.
12. Use the Find and Replace commands to replace all occurrences of ?St.? with Street, ?Ln.?
with Lane, and ?Ave.? with Avenue.
13. Fill color cell range A5:I5 to a color of your choosing. Surround each cell (all cells) with a
border.
14. Change the page layout of the Customer Orders worksheet to print in landscape orientation
on a single page.
15. Return the view of the Customer Orders worksheet to Normal view, save your changes to
the Order Report workbook, and then save the current workbook as Revised Report.
16. Remove Kay Dexter?s order from the Customer Orders worksheet.
17. Add the following order directly after the order placed by June Romano: date 4/22/2015;
name Patrick Crawford; address 200 Valley View Road, Rome, GA 30161; phone (706)
555-0998; DVDs 14; price per DVD $16.55; shipping charge $12.45.
18. Use Edit mode to change the ending date of the report in cell A4 from ?April 17? to April
22.
19. Save the workbook and preview it for correctness.
20. Submit all saved workbooks to the instructor?s gradebook via Blackboard. No emails of
homework allowed/accepted

part 2
Altac Bicycles Deborah York is a financial consultant for Altac Bicycles, an online seller of bicycles and bicycle equipment based in Silver City, New Mexico. She has entered some financial information in an Excel workbook for an income statement she is preparing for the company. You will enter the remaining data and formulas.
1. Open the Altac workbook, and then save the workbook as Altac Bicycles.
2. Insert three new rows at the top of the Sheet1 worksheet, and then enter the following text on two lines within cell A1: (In cell A1, enter the first line of text, and then hold down the Alt key and press the enter key, and then enter next line of text.)
Altac Bicycles
Income Statement*
3. In cell A2, enter For the Years Ended December 31, 2012 through December 31, 2014.
4. In the range C6:E7, enter the following net sales and cost of sales figures: 2014 2013 2012
Net Sales 13,520 10,981 9,034
Cost of Sales 4,140 3,960 3,011
5. In the range C11 :E14, enter the following expense figures (expand the column widths as necessary to show the text and data values):
2014 2013 2012
Salaries and Wages 1,632 1,481 1,392
Sales and Marketing 2,631 2,112 1,840
Administrative 521 410 375
Research and Development 501 404 281
6. In the nonadjacent range C18:E18;C20:E20;C24:E24, enter the following values for Other Income, Income Taxes, and Shares, pressing the Enter or Tab key to navigate from cell to cell in the selected range:
2014 2013 2012
Other Income 341 302 239
Income Taxes 1,225 1,008 821
Shares 3,621 3,001 2,844
7. In the range C8:E8, enter a formula to calculate the gross margin for each year, where the gross margin is equal to the net sales minus the cost of sales.
8. In the range C15:E15, enter the SUM function to calculate the total operating expenses for each year, where the total operating expenses equal the sum of the four expense categories.
9. In the range C17:E17, enter a formula to calculate the operating income for each year, where operating income is equal to the gross margin minus the total operating expenses.
10. In the range C19:E19, enter a formula to calculate the pretax income for each year, where pretax income is equal to the operating income plus other income.
11. In the range C22:E22, enter a formula to calculate the company’s net income for each year, where net income is equal to the pretax income minus income taxes.
12. In the range C25:E25, enter a formula to calculate the earnings per share for each year, where
earnings per share is equal to the net income divided by the number of shares.
13. Use the spelling checker to correct and replace any spelling errors in the worksheet. Ignore the
spelling of “ALtac.”
14. In cell A18, use Edit mode to capitalize the word “income.”
15. Increase the width of column A to 18 characters and increase the width of column B to 25
characters. Autofit the height of row 1.
16. Rename the Sheet1 worksheet as Income Statement.
17. Add a new worksheet, Sheet2, and rename the Sheet2 worksheet as Documentation and move it to
the beginning of the workbook.
18. In the Documentation worksheet, enter the following text and values:
Cell A1: Altac Bicycles
Cell A3: Author
Cell A4: Date
Cell A5: Purpose
Cell B3: your name
Cell B4: the current date
Cell B5: Income statement for Altac Bicycles for 2012 through 2014
19. Save the workbook, preview the workbook and make sure each worksheet in portrait orientation fits
on one page.
20. Close the workbook, and then submit the finished workbook via Blackboard to the instructor?s
gradebook.
Year 2014 2013 2012
Sales
Net Sales
Cost of Sales
Gross Margin

Expenses
Salaries and Wages
Sales and Marketing
Administratibe
Research and Development
Total Operating Expenses

Operating Income
Other income
Pre-tax Income
Income taxes

Net income

Shares
Eanings per share