2023 This week you ll have a few things to do Review reading material | Assignment Collections

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

1. Review reading material from the last two weeks.
2. Analyze 2 database design situations

This week 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:

<font >

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

1. How you would divide the fields into different tables so that all tables are fully normalized. This means no redundancy. Think about this one.
   
2. 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?

1. 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.
   
2. Which fields would be defined as the Primary and Foreign key fields in each table?
   
3. 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?

1. 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.
   
2. Which fields would be defined as the Primary and Foreign key fields in each table?
   
3. 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.