Create new blank database in access and save using your name
Build and Test
The build and test process consists of:
-creating tables
– adding fields
– customizing field properties
– verifying the logic of the database
– testing field properties
– creating lookups
– establishing referential integrity
– adding data records
Getting Started
1. Create a new blank database in Access and save it using your name as the file name.
2. Set the database to Automatically Compact on Close (File |Options / Current Database).
Building the Database
Using your ERD, Table Maps, and Data Dictionary, carry out the following tasks (follow the suggested order to simplify the process):
1. Create the required tables. Table names should not contain spaces.
2. Add a Primary Key to each table. Make sure that your Primary Keys are independent of the data used for day-to-day business.
3. Include a foreign key (common field) in the tables that will link to other tables.
4. Create the attributes (fields) for each table. Field names should be fully qualified (i.e. the table name should be included as part of the field name. Field names should not contain spaces (e.g. CustomerLastName)).
5. Use at least six different data types, including at least one number field, one date and time field, and one currency field (not necessarily all in the same table).
6. Add a caption for each field where necessary and customize the properties of table fields to ensure data integrity using the following field properties at least once: format, input mask, default value, validation rule, validation text, required, index.
7. Enter at least five test records into each of your main tables (i.e. those that do not contain foreign keys) and test the field properties to make sure that your input masks, validation rules, etc. work as planned.
8. Use the Lookup Wizard to create list lookups for each field that will contain a limited set of data. For example, you might create a list of payment methods such as Visa, MasterCard, Amex, Debit, Other.
Establishing Relationships
1. Complete the entry of your text records before you complete this step. Use the Lookup Wizard to create table lookups for all Foreign Key fields. All of your table lookups should return meaningful data i.e. a customer name or a service name. Table lookups should NOT return Primary Key values. Access will automatically create the necessary relationships between tables where a table lookup has been established.
2. Use the Relationships window to enforce referential integrity and apply the Cascade Update option where appropriate – make sure that you have built all of your table lookups before working on table relationships.
Consultation
Arrange for your instructor to verify that the database logic, keys, and lookups are all functional before continuing.
Data Entry
Data records need to be added to your system in order to obtain meaningful reports. The following minimum data entry requirements should provide you with enough data. Enter your data in the order specified below.
1. 10 records for each Primary Entity (e.g. 10 Customers and 10 Services)
2. 5 Records for each Staff or Repair Entity (if applicable)
3. 100 Transactions (i.e. Join Table Records)
Submission Instructions
1. Print the mark sheet and hand in to your instructor at the beginning of class on the assignment due date. The project will not be graded unless a printed copy of the report is submitted to your instructor by the due date.
2. Upload your Access project file to your instructor’s inbox on \\labdatashares OR to the drop box in D2L as specified by your instructor.
