Create a Microsoft Access Database

AB Database Assignment 1 –FOR STUDENTS TO COMPLETE

First: create the initial database:

1. Follow the instructions in the walkthrough beginning in section 1.6 through 1.9 of the AB Database Materials Part 1 and create the first three tables of the Adventure Bikes sales database described there. You will work on this database again for assignment 2, so don’t lose it. When you open your database ‘Enable Content’ when asked so that you can complete your work. Your table and attribute names should apply the ‘Database Rules to Remember’ from the walkthrough, e.g.,

a. Consistency

b. no spaces in object names

c. upper and lower case characters

d. meaningful names

Then add new components:

2. Create an EmployeeClassifications table with a primary key field and a description field (both fields are attributes of employee classification). Now switch to datasheet view and enter rows (or records) for Salaried, Hourly, and Contracted employee categories, using the first letter of the category name as the primary key value, e.g., use ‘H’ as the key value for ‘Hourly’. Confused? Read the walk through, and especially section 1.7 where we created the table for categorizing term codes.

3. Create an Employees table to the database with an AutoNumber EmployeeID as the primary key plus first and last name fields. Also add a field for the employee type using the same data type as the primary key of your employee classification table.

4. Enter at least five sample employees using names of your choice. To help illustrate the system’s functionality, assign at least one employee to each of the three employee categories.

5. Add a foreign key constraint (relationship) so that the database’s referential integrity functions will enforce mutually exclusive employee classifications. Confused? Review how TermsCode worked in the walk through.

6. In the SalesInvoices table, add a new field to hold a foreign key to reference the Employees table. Add a foreign key constraint (relationship) to connect the SalesInvoices table to the Employees table. This applies the Object and Transaction design pattern, recording which employee entered each invoice. In datasheet view, make the first two invoices entered by the same employee, and the third by a different employee.

Then: document your work – this is what you will turn in

Make a word document for your database assignment submission. For Database Assignment Part 1 include the following:

1. Provide illuminating examples explaining which of the tables you added is an Object table and which is a Category table - that’s two paragraphs. Remember that an illuminating example includes a definition of the concept (what is a category table in one paragraph and what is an object in the other) a specific example (what is your table name) and a sentence or two connecting your example to the definition. These all should be in paragraph form. If you are not clear on what is being asked of you here go back and reread sections 1.1 and 1.2 of the AB Database Materials document.

2. Create a database diagram with only the five tables we have worked with so far: that includes lists of customers, invoices, terms code, employees, and employee classifications. To do this simply use the Windows snipping tool to capture the relationships you created using the Access ribbon command Database Tools / Relationships. Ensure that your diagram is neat and shows all the required fields and relationships. Be sure that the relationships are properly formed – you can tell by how the lines between tables are labelled. Paste a snipped image of your diagram into your document.

3. Paste screen shots of the data from all the tables in your database.

4. Also include screen shots of each table’s design. That is, open the table and switch to Design View by clicking the triangle in datasheet view. Use the Windows snipping tool to select enough of the table design to include all the fields and their data types.

5. Submit the Word compatible document AND also the actual database file (the file with the .accdb extension). Both of these files should be named, e.g., AB Database.docx and AB Database.accdb

Be sure to save your database and associated documents on a flash drive or on a cloud storage site in case you need to go back to it later in subsequent assignments.

Part 1 Assignment Hints and Grading Rubric (100 pts)

· Your table and attribute names should apply the ‘Database Rules to Remember’ from the walkthrough.

· This exercise called upon you to create foreign key relationships. Be sure they are properly formed. Check the symbols on the lines in your database diagram. Do they look like the ones in the provided tables?

· Make up whatever names you want for the employees (data) – your names should be polite.

· Don’t forget meaningful captions on the diagram and tables of data.

Grading Rubric

Item (points)	Good Submission	Poor Submission	Points Awarded
Database components (60 pts)	· All five tables are included · All tables contain proper relationships with primary keys and foreign keys · Cardinality is defined, e.g., one-to-many, etc. · Referential integrity is being enforced · Naming convention followed · Naming consistency	· One or more tables is missing · Relationships (foreign keys) are missing or incorrect · Relationships not defined · Referential integrity not enforced · Names contain spaces or do not use upper and lower case for readability · Table and/or field names are inconsistent	60
Document Writing Quality (10 pts)	· Writing quality: exceptional submissions receive full credit · Definitions · Sentences explicitly connect the examples to the definition	· Writing quality: imprecise or poor writing may be deducted · Definitions missing or incorrect · Missing or poor connection of examples to the definition	10
Data diagrams (15 pts)	· Diagram is neat and nicely arranged · All needed data items are included · Foreign key connections are being displayed	· Diagram has obscured table/attribute names, too much white space, or is sized too small · Missing data items · Foreign key fields missing/incorrect · Foreign key connections not properly formed · Diagram missing caption or caption does not add value	15
Sample Data (10 pts)	· Data nicely illustrates the table’s function · All values from the category tables are used in related tables · Enough data was entered (enough rows and attribute values) · Chosen values are polite and as required · Actual .accdb database file was submitted	· Tables are pasted as images or missing · Data fails to illustrates the function · Less than three values from the category table used in another tables · Not enough data was entered (enough rows and attribute values) · Chosen values are in poor taste or do not follow requirements · Actual .accdb data file is not included	10
Instructor Discretion (5 pts)			5
Total			100

AB Database Assignment 1.docx 10/16/18 Page 3