ACCESS DB homework

This week you'll have a few things to do.

Review reading material from the last two weeks.

Analyze 2 database design situations

You're going to start analyzing some database situations, to make decisions as to how you would set them up. This is the type of work that we need to do all the time in Access looking at a situation that demands a database, and then deciding what needs to go into the database and how the fields need to be distributed in order to accomplish the objectives of the database.

Take a look at the following two situations, and decide how you would set these databases up. What would your tables be called, and which fields would go into each table? Use the Leszynski Naming Convention for all object names. Which fields would be defined as the Primary key field in each table? Which fields would be defined as Foreign Key fields? What type of relationship would you create between the tables?

Use either Microsoft Word or Excel to indicate how the different tables would be defined.For example, you could do something like the following:

tblEmployees

*strEmpID (primary key field - used in One-to-Many relationship with tblProjects, connecting on thestrEmpID foreign key field of tblProjects.)

strEmpFirstName

strEmpLastName

sngHours

curPayRate

dtmDateOfHire

strDept

tblProjects

*strProjectNumber (primary key field connecting to the strProjectID foreign key field in tblProjectDetails.)

strEmpID (foreign key field connecting to the strEmpID field in tblEmployees.)

dtmProjectStartDate

dtmProjectEndDate

ysnCompleted

sngHoursSpent

curCostOfProject

Or, you could do it in graphic format, as in the following example that was prepared in Excel:

Regardless of the method you choose to present your database design, you need to focus primarily on two things:

How you would divide the fields into different tables so that all tables are fully normalized. This means no redundancy. Think about this one.

How you would join the various tables, through relationships, and what type of relationship you propose between tables. Keep in mind the important fact that no single table can (or should) be left unrelated to other tables.

You want to avoid redundancy here, so keep that in mind as you consider the division and distribution of fields.

Database Design Situations

How Would You Set These Databases Up?

Example #1

Situation

You want to keep track of the following employee information:

Name
ID#
Address
City
State
ZIP
Home phone
Hire date
Department code
Number of hours they work
Hourly pay rate
Health Plan (1 = Insurance, 2 = Insurance with Dental, 3 = Single HMO, and 4 = Family HMO)
Weekly cost of health plan
Parking lot to which the employee is assigned
Parking lot rate

What Would You Do?

How would you divide these fields in order to avoid redundancy? Lay out the different tables that you would propose creating, with the fields that would be in each of the tables. Be sure to use theLeszynski Naming Convention for all object names.

Which fields would be defined as the Primary and Foreign key fields in each table?

What type of relationship would you create between the tables? Which fields would be joined to which other fields?

Example #2

Situation

You work in a Veterinarian's office and need to organize the data on pets that you treat. In your database, you want to keep track of the following information:

Customer information:

Name
Address
City
State
ZIP
Phone #
Year of first visit
Date of last visit
Discount
Outstanding balance

Pet information:

Name
Type of animal
Breed
Date of birth
Gender
Neutered/Spayed?
Length of animal
Weight of animal
Last visit date
Current vaccinations
Picture of animal
Comments on animal
Date of visit
Follow-up visit date
Total amount billed for visit
Payment type
Date paid
Amount paid
Taxable?
Tax Rate
Invoiced?
Details on the individual visits:

Type of visit
Treatment code
Medication code and price
Details on all medications and visits:

Medication name
Medication price
Treatment name
Treatment price
What Would You Do?

How would you divide the data? Again, lay out the different tables that you would propose creating, with the fields that would be in each of the tables. Be sure to use the Leszynski Naming Convention for all object names.

Which fields would be defined as the Primary and Foreign key fields in each table?

What type of relationship would you create between the tables? Which fields would be joined to which other fields?

What To Do With Your Answers:

Now that you've thought about how you would design these databases and you've addressed the issues of data division and distribution, normalization of tables, key fields, and relationships, I'd like you to provide me with a summary of your decisions. Lay it all out using either of the two methods described above (i.e., text or graphic, Word or Excel), and submit it ithrough the Assignment Link.

Your design can be indicated in either a Word or an Excel document (I do not want you to use Access), whichever makes more sense to you or whichever application you're more comfortable with. Just make sure it is crystal clear to me how you would divide the data for these databases and the relationships that you would create.