MIC FINAL Project: Personal Database Creation

REQUIREMENTS

Summary

I'll be grading your work based on what you upload to the CANVAS tool before 12:30pm on Thursday, August 21. Name your file exactly like I require (below) or you will lose points. This assignment will focus on building a basic database with the requirements listed below, using MS Access 2013. You MAY NOT use other versions. of MS ACCESS.

Create a simple relational database for a small business (see choices below). Upload is DUE before 12:30pm on Thursday, August 21. 120 points. There will be absolutely NO late finals accepted, don't even ask.

GENERAL REQUIREMENTS

CHOOSE 1 (one) product/service subject for your Final Project relational database from THIS list, because these will be the "products" that you create in the database:

  • Books OR
  • Music OR
  • Computers OR
  • Arts & crafts OR
  • Personal services
  • DO NOT DO/COPY/REPURPOSE ANY OF THE DATABASES FROM THE BOOK. Zero Points.

Using this information, you need to create your own normalized, organized, and security conscious relational database with AT LEAST:

  • 5 (five) information storage tables
  • All Needed relationships
  • 5 (five) different kinds of queries
  • 2 (two) forms - one of which will also have a subform
  • 1 (one) output report

NOTE: You need to attend the classes during all of Week 7 and Tuesday of Week 8, since I will use class time to demonstrate how to start an example basic database from scratch.

SPECIFIC INSTRUCTIONS

  • GET STARTED right away, don't procrastinate.
  • Use whatever method you need to interpret and work with these instructions: a print-out, a flow-chart, your own re-written step's, sketches, etc.
  • Use your book, the class notes, the On-Your-Own Tasks and Microsoft lessions, and questions to Google. These give you all the info you need to successfully complete and have fun with this assignment.
  • Remember that, even though this IS your final project, you don't need to let it stress you out or get you down. It is not any more complex than what we have already covered, and you can have fun doing this with what you have learned.
  • Final note: DON'T PANIC!!

GETTING STARTED

  • Download THIS STARTER FILE of an Excel table with some starter names and addresses, so that you do not have to make up names for customers, clients, shippers, employees, suppliers, or whoever.
  • Open Excel 2013. and review the STARTER FILE.
  • Use MS Access 2013 to create a NEW BLANK database - DO NOT USE a template, and do not use any part of the Gardening or any other existing database.
  • Save and name your file finalfirstinitiallastname.accdb. Example: finallbothell.accdb.
  • Carefully plan how to break information provided in the Excel STARTER FILE into normalized customer, client, employee, shipper, and/or other "people" tables to start building a basic working relational database.
    • Use paper and pencil if you like to draw out your ideas.
    • You can either import the Excel tables into Access, or you can print them out to use as a reference to re-type the information into Access yourself.

RE: INFORMATION / FORMATS

  • Refer to Lesson 2 for support information.
  • Use text data type for all text-related stuff, like names, unique UserID codes, descriptions, phone numbers, etc.
  • Use currency data type for money-related stuff so that you can add amounts.
  • Use number data type for things you might like to add up, like quantities.
  • Use autonumber data type only once per table for the unique ID field.
  • Use date/time data type for any dates you decide to list
  • Restrict the amount of data in table fields by changing the Table Design View's Field Size.
  • Use Input Mask for things like a phone number format, specific date and currency formats, etc.
  • Check out Validation Rules in Chapter 6 to see how to validate specific types of data, like email addresses.

RE: TABLES

  • Refer to Lesson 2 for support information.
  • You need to create a minimum of 5 (five) well-designed and normalized tables for your relational database.You will likely need more than 5.
  • You should focus on normalizing tables by organizing required information together into specific tables. Recommendations:
    • Customers (or Clients) table, using at least 60 names from the STARTER FILE
    • Employees (or Suppliers, or AccountReps) table, using at least 15 names from the STARTER FILE
    • Products (or Services) table, (make up at least 24 different Products (or Services) and related costs, supply numbers, reorder levels, etc.
    • Products (or Services) Categories table, (make up at least 4 Categories, with clear descriptions)
    • Orders table (make up dates ordered or requested, shipping costs, shipped or completed dates, etc. Make up at least 40 different orders/transactions.
    • Order Details table (make up quantities (if products), discounts and/or taxed amount, unit and/or packaged pricing, etc.
    • Consider also adding a Shippers (or Suppliers) table, for whoever outside of your "company" provides these services. If you do, use at least 10 names from the Excel STARTER FILE
  • Use One-word column names, like LastName, ProductID, etc.
  • Make sure that you are NOT needing to re-type dumplications of the same unique information in more than one table, such as listing the same products in Products and Categories tables.
    • Instead, use look-up tables as needed.
  • Use Autonumbering for a single unique identifier field for each table, like the CustomerID, OrderID, etc.
  • Be sure that your unique identifier field in each table is assigned a Primary Key.

RE: RELATIONSHIPS

  • Refer to Lesson 2 for support information.
  • Create ALL 1-to-many relationships as needed between appropriate tables in your database, each with Referential Integrity selected. Even if you do not do all possible queries, be sure to set up ALL possible relationships. You likely will have several of these relationships.

RE: QUERIES

  • Refer to Lesson 3 for support information.
  • You need to create at least 5 (five) working Queries for your database.
  • Create one Single-table query, such as a list of your customers' names and the city they come from.
  • Create one Multiple-table query for Customer Orders.
  • Create one Action Update query to Update your prices by a specific dollar amount in whichever table makes sense for this query.
  • Create one query to Summarize data in whichever table makes sense for this query.
  • Create one other query of your choice - a Find Duplicates query, or a Delete Query, or a Calculations query, a Sorting Query, etc.

RE: FORMS

  • Refer to Lesson 4 for support information.
  • You need to create at least 2 (two) working Forms for your database.
  • Create a Columnar Form for your Customers table so that I can easily enter new customers into the database.
    • Choose a Theme, color scheme, and font scheme you like for your form.
    • Add a small image to the Header of your form.
    • Add a Date Control to the Footer of your form.
    • Organize the information in your form so that it clusters nicely.
    • Set the Tab Index so that I can tab through all the Input Fields from the beginning to end.
  • Create a SECOND form: a Categories form, with a Products (or Services) subform, for your database. Use the SAME theme, color scheme, and font scheme that you chose for your forms.
  • For 5 points extra credit, create a Welcome Page/Forms Navigation Form that opens when your database it opened.

RE: REPORTS

  • Refer to Lesson 5 for support information.
  • You need to create at least 1 (one) working Report for your database.
  • Create a Sales by Category report for your database.
  • Make the report Landscape.
  • Use the SAME theme, color scheme, and font scheme that you chose for your forms.
  • Set the margins to Narrow.
  • Add a graphic to the Report Header of your report near the Report's name.
  • Add a Page Number Control to the Page Footer of your report.
  • Add a Sum to the Report Footer to add up the totals of one of the columnthat can be added up.
  • Organize the information in your report so that it lays out nicely.

RE: SECURITY AND FINALIZING

  • Refer to Lesson 6 for support information.
  • Back-up your database and Compact & Repair it (to help reduce the file size).

RE: FINAL CLASS ATTENDANCE

  • Our Final class hours take place during Finals Hours in Room #3174, on Thursday, August 21, 10:30am-12:30pm.
  • You are NOT REQUIRED to attend the Final class session on Thursday, because I am not giving a final exam or requiring you to present your database to the class.
  • I WILL open the room, and I will close it around 11:30am if no one is there.
  • IF you DO come, you should have your work completed for submission.
  • This is NOT a full work session for last-minute finishing or sudden questions about database concepts, fixing problems, or having me teach things.

ASSIGNMENT SUBMISSION FORMAT:

When you are finished with your Database Project,

  • Upload BEFORE 12:30pm on Thursday, August 21.
  • Log in to the CANVAS tool, and in your MIC120 class, click the link for Final Project.
  • At the Upload location, upload your Access assignment file.
  • BE SURE you upload the .ACCDB file only, NOT the .laccdb file.
  • Verify that the correct file appears after you finish uploading.

Tips

  • Use your book as a refresher.
  • Save your document frequently.
  • Finish and upload BEFORE 12:30 pm on Thursday, August 21. I WILL NOT accept late uploads or assignments for any reason. NO!

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

CHECKLIST

  • Does your work include ALL the assignment requirements?
  • Have you double-checked that your assignment has all the requirements I listed in this assignment?
  • Have you uploaded your assignment BEFORE 12:30PM THURSDAY, AUGUST 21?
  • Have you emailed me at least 1 full day before the due date if you had serious problems completing the assignment?
BACK TO TOP