normalization process

The Scenario:

You have been hired by a manufacturing company with multiple manufacturing facilities to perform the logical design of a database to manage their roster of available temporary workers and their subsequent assignments to fill workforce needs. The temporary workers are needed on a seasonal basis to fill a number of job roles such as materials management, van driver, loading/shipping dock worker, assembly line worker, packaging/shipping support, etc. Each manufacturing facility is configured with three different work areas, which are raw materials management, assembly line productions, and packaging/shipping. Here are the requirements that you have identified after interviewing company management:

  • The company would like to record and maintain complete information on each temporary worker, including full name, home address, mailing address (which may or may not differ from the home address), preferred and secondary telephone numbers, email address, date of birth, gender.
  • The manufacturing facilities all operate on a 16-hour daily basis, two 8-hour shifts a day,six days a week, Monday through Saturday. They would also like to maintain information on each temporary worker’s work shift availability in terms of days of the week (Monday through Saturday) and work shift (first shift, second shift, “bridge” shift (last 4 hours of first shift and second shift) on the specified days). Workers may specify availability for one or more shifts during those days.
  • In addition, they will need to maintain complete information on each temporary worker’s job certifications and information on certification instructors (same information as for temporary workers identified and item 1 above, information on which courses they are qualified to teach, and their teaching histories for the company). A temporary worker must be certified for a job role by completing a required training program for that role and can be certified to perform one or more job roles (see job role examples in the opening paragraph above). For job certifications, the company would like to maintain the identification of the certification, identification of the temporary worker participating in the certification training, the dates of certification training (certification training ranges from 4 to 16 hours depending on the type of certification), the certification trainer’s identification, and a pass/fail designation for the temporary worker completing the certification training. The company would also like to maintain complete information on available job role certification training program courses.
  • Temporary workers are also allowed to specify prioritized work area location preferences for one or more manufacturing locations.
  • Finally, they have stated the requirement for maintaining a complete record of each temporary worker’s assignments, including manufacturing facility location and work area assignment, date worked, shift(s) worked, and job role(s) filled by shift.

The company intends to use the completed database design to support the identification of temporary workers to fill needed assignments and to produce comprehensive reports on the use of temporary workers.

The Deliverable:

In a Microsoft Word document, provide a complete integrated set of normalized (3NF required) relations, using the format displayed in Figure 9.3e on page 316 (note that entities are in all caps, attribute names with multiple words are connected by an underline character joining each word (for example, “Student_ID”), primary keys are underlined and listed first within the attributes) in Third Normal Form (see pages 318-321 for explanatory information on the normalization process).

An Example of Normalization:

Here is a relatively simplistic example of a normalization process:

An “un-normalized” relation:

STUDENT(Last_Name, First_Name, Course_Name, Instructor_Name, Semester, Year)

There are multiple problems here: multiple students could have the same last name, course name might occur many times within the table and could be entered incorrectly or misspelled in some records, instructor name might occur many times within the table and could be entered incorrectly or misspelled in some records, semester name might occur many times within the table and could be entered incorrectly or misspelled in some records.

The “normalized” version in Third Normal Form:

STUDENT(Student_ID, Last_Name, First_Name)

INSTRUCTOR(Instructor_ID, Last_Name, First_Name)

COURSE(Course_ID, Course_Name)

SEMESTER(Semester_ID, Term, Year)

COURSES_TAKEN(Student_ID,Course_ID,Instructor_ID,Semester_ID)

Rubric

Criteria Ratings Pts
This criterion is linked to a Learning OutcomeEntity Identification
All necessary entities correctly identified and clearly named

40.0 pts

All necessary entities identified but one or more unnecessary entities included

36.0 pts

One necessary entity missing

32.0 pts

Two necessary entities missing

28.0 pts

Three or more necessary entities missing

24.0 pts

Nothing submitted

0.0 pts

40.0 pts
This criterion is linked to a Learning OutcomePrimary and foreign key Identification
All entities have correctly specified primary and foreign keys

40.0 pts

One missing or incorrect primary or foreign keys

36.0 pts

Two missing and/or incorrect primary or foreign keys

32.0 pts

Three missing and/or incorrect primary or foreign keys

28.0 pts

Four or more missing and/or incorrect primary or foreign keys

24.0 pts

Nothing submitted

0.0 pts

40.0 pts
This criterion is linked to a Learning OutcomeAttributes
Attributes completely identified for all entities

20.0 pts

No more than two missing attributes

18.0 pts

No more than three missing attributes

16.0 pts

No more than four missing attributes

14.0 pts

More than four missing attributes

12.0 pts

Nothing submitted

0.0 pts

20.0 pts
Total Points: 100.0

PS:Plz read the textbook first which i attached below. Thanks

find the cost of your paper

Asian American 3

I need support with this Asian Studies question so I can learn better. Write a review of the reading Marcus and Chen Inside Outside Chinatown Requirements: 250+   |   .doc fileATTACHMENTSmarcus_and_chen_inside_outside_chinatown.pdf

Environmental Science Question

m trying to learn for my Environmental Science class and I’m stuck. Can you help? Helpful Video on a shark field study: Turks & Caicos Islands: Field Research on Sharks (Links….

What is the command for it?

I’m working on a linux question and need a sample draft to help me understand better. What is the command for this, one line is all I need to solve….