I need help building my parenthetical method/tables for the problem below.

 

I currently have these:

• Employee (EmployeeID, FName, LName, Address, City, State, ZIP, Phone, CellPhone, SSN, DOB, HireDate, TerminationDate, Position, Salary, HourlyRate, LastReviewDate, Notes)
• HR_Employee (HR_EmployeeID, EmployeeID, WarehouseID, HireDate, TerminationDate, Salary, HourlyRate, LatestReviewDate, Notes)
• EmployeePosition (PositionID, PositionName)
• Driver (DriverID, FName, LName, Address, City, State, Zip, Phone, OutstandingBalance)
• DriverInfo ( DriverInfo,DriverID¸ SafteyRating, OnTimeDeliveries, FeeCostPercentage, Notes)
• Customer (CustomerID, Company, ContactName, Address, City, State, Zip, Phone

The prompt is as follows:

MovinOn Inc. is a moving company that provides moving and storage services in California, Nevada, and
Arizona. MovinOn provides a truck, driver, and one or more moving assistants to move residential and
commercial items from one location to another within the defined coverage area. In addition to moving
services, the company provides temporary and long-term storage in its warehouses. MovinOn's customers are
commercial and residential. Some of the storage warehouses are climatically controlled for customers who
need to store items that are sensitive to extreme temperatures.

The business started in 2015 with a single truck and single warehouse in California. Due to a very satisfied
clientele, the company has grown over the years into a much larger business. Currently, the company has one
warehouse in each state it services and is working on a merger with another company that offers similar services
in different areas. When the merger is complete, MovinOn will acquire additional storage warehouses, trucks,
and employees, which will expand its operations into different states.

James Lopez the CEO of MovinOn. In the past, James managed the business using a combination of
spreadsheets and paper forms. However, with a merger in the company's future, James needs to expand his
system to manage the data better. James recently hired you, an information systems specialist, to recommend
and implement a new plan for managing the company's data.

As an IS professional, your first task is to understand the current system and its limitations by talking
extensively with James about data management and user needs. James explains that the office in each state
accepts reservations for moving and storage services by completing a job order form that includes the
customer's information and job's details. Jobs that involve trucking items from one location to another or from
an outside location to a storage unit in a warehouse are maintained in a filing cabinet that is organized by
customer name. Leases for storage space are stored alphabetically in a separate filing cabinet for each
warehouse. All of the forms are stored in the on-site offices at the warehouse from which they were purchased.
Unfortunately, James admits that forms are often lost or misplaced and sometimes contain inaccurate or missing
data. In addition, when a customer requires the services of another warehouse, a MovinOn employee has to
copy the customer's record and send it to the second warehouse to that it is on the file at the second location.
James wants the new system to be capable of sharing capable of sharing data between the three warehouses
and any warehouses that the company acquires in the future so that it is easy for the company to share and
maintain data.

In addition to managing personnel data, James also wants to use the new system to manage information about
drivers, including their personal information and driving records. The system also needs to store information
about the trucks and vans that MovinOn owns and operates.
Finally, the system must maintain data about customers who utilize moving and storage services. Some
customers might require storage in more than one location. When there is a request for services, the requests
are recorded on forms. In addition to the job order form, a job detail form is created that shows the details about
the job such as the driver, the vehicle used, actual mileage, and actual weight.
James gathered a collection of documents during the discovery and planning phase that will help you design
the database. You need to be certain that every data item in the existing documents is also represented in the
tables in your design. James uses the form in Figure 1 to collect data about employees.
Figure 1: Employee Information Form

Along with the data gathered in Figure 1, employees are given a specific role or position within the company.
 

Figure 2 denotes the various positions at MovinOn.
Figure 2: Employee Positions

Furthermore, James needs the system to identify the warehouse in where employees work. Because the
company makes use of contracted drivers, the system should include similar information for drivers as
employees. Yet, drivers are not assigned to a warehouse nor are they paid hourly or salary. Instead, drivers
are paid based on the number of miles driven for any job, which is paid at a $2.50 per mile. Furthermore,
drivers are rated based on safety records, successful on-time deliveries, and other factors. The rating system
uses the values A, B, C, D, and F to rate drivers, with A being the highest rating and F being the lowest rating.
The criteria for the rating of drivers are handled by management and is used to determine whether drivers earn
100% of delivery cost. If drivers do not have an A rating, drivers are charged a fee on the total amount of job
cost for lower ratings. The fee cost for a rating of B is 5%, C is 15%, D is 20%, and F is 50%
 

As a moving service company, the system needs to consider the vehicles (e.g., trucks and vans) owned by
MovinOn that are used for any job order. Vehicles are usually identified uniquely by using the prefix TRK for
trucks (e.g., TRK-001) and VAN for vans (e.g., VAN-009). James would like to make sure that the vehicle's
license plate number, number of axles, and color are being stored.
 

Figure 3 illustrates the spreadsheet file that contains the information about the three warehouses. A two-letter
state abbreviation in which the warehouse is located followed by a dash and a number is used as the
warehouse unique identifier (e.g., CA-1).
 

Figure 3: MovinOn Warehouses

In Figure 4, a portion of the information stored about the storage units in an Excel worksheet is illustrated.
The new system needs to be able to manage data about the storage units.
 

Figure 4: MovinOn Storage Units

With storage units, it is important to manage data that captures which customer rents which unit. Usually,
unit rentals will indicate when the lease starts and ends as part of the customer agreement. For current
customers, the ending lease date will either be null or a future date.

At MovinOn, the data pertaining to moving jobs is acquired in two steps. When a customer requests a job, the
administrative assistant from the warehouse fills out the form shown in Figure 5. This form is the "job order."
Because the "job order" is for customers, James needs data about customers to be stored as well. This should
consider the following: company name (for commercial customers only), the job contact's name, relevant
mailing information, and contact numbers.
 

Because of the business process to keep track of moving jobs, the system will need to consider the "job details"
in order to ensure that the database stores relevant data. Thus, the assignment of vehicles to drivers for
moving jobs, the customers served on specific jobs, and the actual weight and actual mileage of the job will
need to be noted. Drivers would be conducting multiple jobs over time, as well as, repeating customers may
contract MovinOn for multiple jobs.
 

Figure 5: MovinOn Job Order Information Form

Because you have been hired to provide a business solution to MovinOn, Inc., you will need to develop a
database design that considers all of the needs of the business discussed in the previous narrative. It is important
to think about the business problem, draw out the database design using paper and pencil, be specific about the
metadata for each table (e.g., field names, data types, field sizes, and field descriptions) , notate validation rules
and default values, and consider all of the forms and data provided in the figures above. Before implementation,
it is crucial that the database design has been checked carefully to ensure that the database is well formed and
will meet the business needs.

Name: Address: mOvinOn Inc. Employee Information Form David Bowers 10124 Metropolitan Drive Seattle (206) 246-5132 City: State: WA ZIP: 98117 Phone: Cell Phone: (206) 575-4321 SSN: Date of Birth: 9/12/1958 (The following information to be filled out by MovinOn human resources manager) Hire Date: 1/22/1998 Termination Date: General Manager 154-00-3785 Position: Annual Salary: $72,000 Date of last personnel review: Notes about this employee: or Hourly Rate: