To demonstrate the ability to propose, implement, and query the database.

Problem:

MovinOn Inc. is a moving company that provides moving and storage services in California, Nevada, and Arizona. MoveOn 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. MoveOn'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 a 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 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 $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's 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 MoveOn, 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.

Instructions:

Define the database design necessary to meet the needs of the case.

□ 1. Use the normalization steps to identify the various entities and attributes needed to capture and automate the manual processes for the business case. Define each of the entities using the parenthetical method (PM). Each relation in your database design must be a normalized relation (3NF). Any assumptions should be stated below the database design PM. An example of the parenthetical method for a “STUDENT” relation is:

STUDENT (StudentId, StudentName, Email, Age, Major)

*Note: Attributes that comprise the primary key should be underlined and attributes that are foreign keys should be italicized.

□ 2. Use any drawing tool (e.g., MS Visio or draw.io) and create an illustration of the proposed database design model. Because relationships are critical to the database design, identify the minimum and maximum cardinalities for each of the relationships. Provide brief justifications for the cardinalities depicted in the proposed data model. *Note: In addition to minimum and maximum cardinalities, relationship lines should be illustrated according to type of relationship (e.g., identifying and non-identifying) and weak and strong entities should be differentiated as well. This is NOT the diagram from SQL Server.

□ 3. For each entity, create a matrix or table that provides the metadata for each table. This should include the field names, data types, field size, and description (if applicable). Provide a brief description for each table that explains the table’s purpose. This can be done in one sentence or two. Place the description above each table.

! 4. Implement and populate the database by using your knowledge of SQL DDL. Reference the 3NF entities from previous steps as a guide to define the properties and structure in SQL syntax. Enforce referential integrity by defining PK/FK constraints in the SQL script. Include 15 dummy records for each table. This dummy data should be well thought out as you will be using the data to run specific queries that will demonstrate that the database has resolved the issues at MovinOn.

*Note: SQL syntax is written and saved in a text editor application (e.g., Notepad) and will be submitted as a separate file. Combine both the CREATE TABLES and INSERT INTO VALUES script into one text file (.txt) and make sure the script can be executed together without any errors.

! 5. Once the database has been implemented, create a database diagram in SQL Server Management Studio (SSMS) by following these steps:

• Right-click on the Database Diagrams item under your database in the Object Explorer • Select “New Database Diagram.”

• In the “Add Table” dialog box, select all of the tables in your solution and then click “Add” button.

• When your diagram is complete, select “Copy Diagram to Clipboard” from the SSMS “Edit” menu. Paste your diagram into the document that will be the final report.

Page 5

! 6. Verify that the database has been populated by writing individual SQL statements that provide a listing of the records for each table (e.g., SELECT * FROM [TABLENAME];). Provide screenshots that illustrate the “dummy” records of each table in the final report.

□ 7. The database should be able to produce and provide a sample report with dummy data. (Hint: Fields from multiple tables should be used to develop the sample reports.) Create the SQL queries and provide the screenshots for the following:

• List the balance due to MovinOn from each customer inclusive of moving jobs and rentals for a specific month.

• List the amount due to each driver for moving jobs conducted in a specific month. Be sure to consider fees that drivers are charged based on performance ratings. • List the drivers and their corresponding job details.

• List the revenues for MovinOn in a particular month once drivers and employees have been paid out. Revenues should include both moving jobs and rentals.

• List the storage units that have not been leased and are available.

□ 8. Discuss and explain in detail the following questions:

• Why is normalization important when defining a database design?

• How do validate a proposed database design model before implementation? • What challenges did you face during this database course?

• What would be your advice to future ISDS 402 students?

□ 9. Review the final report to ensure that the report is organized in the order given above. □ 10. Save and identify the submission files according to the note below.
 
 

Transcribed Image Text:

Name: Address: mOvinOn Inc. Employee Information Form David Bowers 10124 Metropolitan Drive Seattle (206) 246-5132 City: Phone: SSN: (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: State: WA ZIP: 98117 Cell Phone: (206) 575-4321 Date of Birth: 9/12/1958 or Hourly Rate: PositionID 1 2 3 4 5 6 7 Title General Manager Warehouse Manager Administrative Assistant Accountant Maintenance Moving Assistant Information Systems A 1 WarehouselD 2 CA-1 3 NV-1 4 AZ-1 B Address #3 Industrial Park Way 8798 Warehouse Rd 54781 Hixson Pike C City Fullerton Las Vegas Phoenix CA NV AZ D State F H Phone Climate Control Security Gate E Zip 90621 6572783721 Yes 89104 7024568734 No 85005 6238972341 Yes G Yes Yes No UnitID 1 1 1 2 2 2 3 3 3 4 4 4 5 5 5 6 6 6 7 7 7 8 WarehouseID CA-1 NV-1 AZ-1 CA-1 NV-1 AZ-1 CA-1 NV-1 AZ-1 CA-1 NV-1 AZ-1 CA-1 NV-1 AZ-1 CA-1 NV-1 AZ-1 CA-1 NV-1 AZ-1 CA-1 Unit Size 8x8 12 12 x 12 12 x 12 8x8 12 x 12 x 12 8x8 9 x 12 12 x 12 8 x 12 9 x 12 12 x 18 8x8 12 x 12 12 x 18 8 x 12 12 x 12 12 x 12 8 x 10 12 x 10 12 x12 15 x 15 Rent 25.00 35.00 45.00 30.00 35.00 45.00 25.00 30.00 45.00 30.00 30.00 70.00 25.00 85.00 70.00 30.00 85.00 45.00 25.00 80.00 85.00 95.00 Name: Address: mOvinOn Inc. Employee Information Form David Bowers 10124 Metropolitan Drive Seattle (206) 246-5132 City: Phone: SSN: (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: State: WA ZIP: 98117 Cell Phone: (206) 575-4321 Date of Birth: 9/12/1958 or Hourly Rate: PositionID 1 2 3 4 5 6 7 Title General Manager Warehouse Manager Administrative Assistant Accountant Maintenance Moving Assistant Information Systems A 1 WarehouselD 2 CA-1 3 NV-1 4 AZ-1 B Address #3 Industrial Park Way 8798 Warehouse Rd 54781 Hixson Pike C City Fullerton Las Vegas Phoenix CA NV AZ D State F H Phone Climate Control Security Gate E Zip 90621 6572783721 Yes 89104 7024568734 No 85005 6238972341 Yes G Yes Yes No UnitID 1 1 1 2 2 2 3 3 3 4 4 4 5 5 5 6 6 6 7 7 7 8 WarehouseID CA-1 NV-1 AZ-1 CA-1 NV-1 AZ-1 CA-1 NV-1 AZ-1 CA-1 NV-1 AZ-1 CA-1 NV-1 AZ-1 CA-1 NV-1 AZ-1 CA-1 NV-1 AZ-1 CA-1 Unit Size 8x8 12 12 x 12 12 x 12 8x8 12 x 12 x 12 8x8 9 x 12 12 x 12 8 x 12 9 x 12 12 x 18 8x8 12 x 12 12 x 18 8 x 12 12 x 12 12 x 12 8 x 10 12 x 10 12 x12 15 x 15 Rent 25.00 35.00 45.00 30.00 35.00 45.00 25.00 30.00 45.00 30.00 30.00 70.00 25.00 85.00 70.00 30.00 85.00 45.00 25.00 80.00 85.00 95.00

Answer rating: 100% (QA)

Step ... View the full answer