Computer Science Homework Help

You have been hired by a warehousing company with multiple warehouse facilities in a metropolitan area 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 forklift driver, van driver, loading dock worker, inventory clerk, etc. Each warehouse facility is configured with two different work areas in which inventory may be received or shipped: receiving operations and shipping operations. Here are the requirements that you have identified after interviewing company management:

1. 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.
2. They would also like to maintain information on each temporary worker’s work shift availability in terms of days of the week (Monday through Sunday) and work shift (mornings, afternoons, and/or evenings on the specified days). Workers may specify availability for one or more shifts during those days.
3. In addition, they will need to maintain complete information on each temporary worker’s job certifications. 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.
4. Temporary workers are also allowed to specify prioritized work area location preferences for one or more warehouse locations.
5. Finally, they have stated the requirement for maintaining a complete record of each temporary worker’s assignments, including warehouse 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 318 (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 321-324 for complete 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)

Note: A Turnitin analysis (see http://turnitin.com/ (Links to an external site.) for information on this product) will be automatically applied to your assignment submission to indicate the potential degree of “originality,” which means that you have interpreted and are discussing your research results “in your own words” (paraphrasing, but with APA in-text source citations) or are using proper APA citation methods for exact quotations to include the words of others. A Turnitin analysis percentage of 15% or higher will result in your having to repeat this assignment with a single attempt to achieve a percentage less than 15% in order to receive any credit for it.

3NF Logical Data Model

3NF Logical Data Model

Criteria	Ratings	Pts
Entity 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
Primary 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
Attributes	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