What type of relationship would you create between the tables.

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

*sempstress (primary key field – used in One-to-Many relationship with tblProjects, connecting on headmistress foreign key field of tblProjects.)
strEmpFirstName
streetlamp
longhouses
recuperate
dtmDateOfHire
strep

projects

*projectionist (primary key field connecting to the strProjectID foreign key field in tblProjectDetails.)
sempstress (foreign key field connecting to the strEmpID field in tblEmployees.)
projectionist
projectionist
completed
Westinghouse
projectionist

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.

Order Now

Last Completed Projects

topic title academic level Writer delivered

Are you looking for a similar paper or any other quality academic essay? Then look no further. Our research paper writing service is what you require. Our team of experienced writers is on standby to deliver to you an original paper as per your specified instructions with zero plagiarism guaranteed. This is the perfect way you can prepare your own unique academic paper and score the grades you deserve.

Use the order calculator below and get started! Contact our live support team for any assistance or inquiry.

[order_calculator]