database project part 2 for RELIABLE PAPERS

BUS 145 – Database Project – Part 2

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.

Mark Sheet for Database Build and Test

Name: _______________________________________________________

Database Construction:

Database automatically compacts on close............................................................ /1

Tables:

Table Names ........................................................................................................ /1

Primary and Foreign Keys ...................................................................................... /4

Fields:

Fully Qualified Field Names ................................................................................... /2

Data Types (6) ........................................................................................................ /3

Field Sizes ........................................................................................................... /3

Field Properties (at least two examples of each property) ................................... /8

  Format

  Input Mask

  Caption

  Default Value

  Validation Rule

  Validation Text

  Required

  Index

Lookups and Relationships:

List Lookups (one example) .................................................................................. /1

Foreign Key Lookups (i.e. Combo Boxes created using the lookup wizard) ......... /5

Relationships established with referential integrity enforced ............................ /2

Data Entry:

100 Transactions .................................................................................................. /10

10 Records for each Primary Entity (Customers, Services) .................................... /5

Additional Records (e.g. Staff, Repairs, Additional Transactions, etc.) ................. /5

TOTAL ................................................................................................. /50