BIS 300 - Fall 2016 Professor: Chad Anderson Database Case Study Due Date: November 17 Point Value: 80 For this assessment you will use Microsoft Access to complete Case 2 in the Problem-Solving Cases in Microsoft Access & Excel book. Specifically, you will be constructing a database for a bicycle repair shop with a set of tables, queries, forms, and reports using both the instructions provided in this document and the instructions provided in the book from pages 57-61. All instructions in this document take precedence over the instructions in the book so pay close attention to the instructions provided below. Name the database with your last name (Baker) and make sure you are saving it in a location that will be accessible to you (e.g., a flash drive or the hard drive of your personal computer). You will skip Assignment 1, which asks you to design the database tables, and just use the information below to create the tables you will need for Assignment 2. When setting up the tables you will need to determine the proper data type for each field so that you will be able to apply the following metadata formatting: Customers: primary key will be CustomerID CustomerID: Field Size = 5, Input Mask is set for five numeric characters (00000) LastName: Field Size = 30 FirstName: Field Size = 20 StreetAddress: Field Size = 40 City: Field Size = 20 State: Field Size = 2, Input Mask set for two letters (LL), On the Lookup tab, Display Control is set to Combo Box, Row Source Type is set to Value List, Row Source is KY;OH;IN and Limit To List = Yes ZipCode: Field Size = 5, input mask is set for five numeric characters (00000) PhoneNumber: Field Size = 10, Input Mask is set for Phone Number BirthDate: Format = Short Date, Input Mask is set for Short Date Technicians: primary key will be TechnicianID TechnicianID: Field Size = 3, Input Mask is set for a letter followed by two numeric characters (L00) Name: Field Size = 20 LeftHanded: Format = Yes/No Services: primary key will be JobID JobID: Field Size = 2, Input Mask is set for two numeric characters (00) JobDescription: Field Size = 25 JobPrice: Format = Currency Visits: primary key will be VisitID VisitID: use the AutoNumber data type VisitDate: Format = Short Date, Input Mask is set for Short Date CustomerID: Field Size = 5, Input Mask is set for five numeric characters (00000), On the Lookup tab, Display Control is set to Combo Box, Row Source Type is set to Table/Query, Row Source is set to Customers, Bound Column = 1, Column Count = 3, Limit to List = Yes TechnicianID: Field Size = 3, Input Mask is set for a letter followed by two numeric characters (L00), On the Lookup tab, Display Control is set to Combo Box, Row Source Type is set to Table/Query, Row Source is set to Technicians, Bound Column = 1, Column Count = 2, Limit to List = Yes BIS 300 - Fall 2016 Professor: Chad Anderson VisitLineItem: primary key will be a composite key of VisitID and JobID VisitID: Format = Long Integer JobID: Field Size = 2, Input Mask is set for two numeric characters (00), On the Lookup tab, Display Control is set to Combo Box, Row Source Type is set to Table/Query, Row Source is set to RepairServices, Bound Column = 1, Column Count = 3, Column Widths = 0.5\";2\";1\" and Limit to List = Yes As in the tutorial, I would like you to define the relationships between the tables and this step should be done before entering any data into the tables. Refer back to the tutorial instructions document if you need a reminder of how to set up table relationships. Make sure to enforce Referential Integrity for all relationships. Adjust the tables in the Relationships window so all fields are visible and the relationship lines don't cross behind any of the tables. You will need to populate the tables with data. The Assignment 2A section provides guidelines for creating that data but I want you to also follow the instructions below for the order and structure of your data entry. Start by entering data for the Customers, Technicians, and Services tables based on the book's guidelines. Next create the Visits form, which is the first activity in Assignment 2B. The form will automatically include the VisitLineItem subform, if you created the relationships correctly. Once you have the Visit form properly created, use it to enter the required visit data. The form should include drop down boxes for CustomerID, TechnicianID, and JobID which will make input of the visit data easier. Each customer should visit the shop twice and have multiple services performed on each visit. Use visit dates that span the previous two months. In addition to the tables and the form discussed above, you will be creating 7 other database objects: 5 queries, and 2 reports. Use the following instructions to supplement the information provided in the book. In the Jobs by Technician Number parameter query, the prompt for the parameter value should be \"Enter TechnicianID\". In the Customers Under 25 query, the formula to calculate the current age of each customer is (Date()-[BirthDate])/365.25 You do not need to create the Increased Prices update query. Since each customer should visit the shop twice and have multiple services performed on each visit, the Total Owed by Customer report should display at least four services for each customer. The Work Report on page 61 is the last step in the assignment. Deliverables Make sure all 13 database objects are included in your database and that they are properly labeled. Submit your completed Access file through the Database Case Study submission page on Blackboard. Grading Rubric Requirements The database contains 13 properly labeled objects: 5 tables, 1 form, 5 queries, and 2 reports The Customers table is properly constructed with the field names and metadata requirements specified in the instructions document Points 8 4 BIS 300 - Fall 2016 Professor: Chad Anderson The Technicians table is properly constructed with the field names and metadata requirements specified in the instructions document The Services table is properly constructed with the field names and metadata requirements specified in the instructions document The Visits table is properly constructed with the field names and metadata requirements specified in the instructions document The Visit Line Item table is properly constructed with the field names and metadata requirements specified in the instructions document Tables are populated with data based on the guidelines specified in the book and instructions document The Visits form is constructed appropriately The Jobs by Technician Number query is constructed appropriately The Customers Under 25 query is constructed appropriately The Popular Jobs query is constructed appropriately The Total Owed by Customer query is constructed appropriately The Total Owed by Customer report is constructed appropriately The Work Report query is constructed appropriately The Work Report is constructed appropriately 4 4 4 4 12 4 4 4 4 4 8 4 8