Homework Help is Here – Start Your Trial Now!
Engineering
AI and Machine Learning
Database Design and Business Applications Development se Study 1 – The following exercise allows you to conceptualize a database Using the following text, describe the Problem you are trying to solve. Determine which ormation is necessary to create a database (you must follow the Steps in Designing a Database vered in this course). Outline the database deciding which information will be saved in each entity. ce you finish analysis and design, implement the MovinOn database using MySQL. se Problem for this assignment MovinOn Inc. is a Moving and Storage Company based in the northwestern United States. Having own from a start-up venture consisting of one vehicle and one warehouse in Oregon, the company is rgeoning into other states and is outgrowing its paper-based recordkeeping system. The CEO of ovinOn Inc., David Bowers, hired an information system manager to design a database to manage spreadsheets and paper forms. However, with a merger in the company's future, David needs to expand his system to manage data better. David recently hired Robert Iko, an information systems specialist, to recommend and implement a new plan for managing the company's data. Robert's first task is to understand the current system and its limitations by talking extensively with David about data management and user needs. David explains that the office in each state accepts reservations for moving and storage services by completing a form that includes the customer's name, address, phone number, and the 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 the forms are stored in the on-site offices at the warehouse from which they were purchased. Unfortunately, David 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 must copy the customer's record and send it to the second warehouse so that it is on file at the second location. David wants the new system to be 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. Each warehouse has its own manager, office staff, and moving assistants. Drivers are contract employees and can work for any warehouse. David wants the new system to manage employee data, including personal information, salary information, and work performance. In addition to managing personnel data, David 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

Database Design and Business Applications Development se Study 1 – The following exercise allows you to conceptualize a database Using the following text, describe the Problem you are trying to solve. Determine which ormation is necessary to create a database (you must follow the Steps in Designing a Database vered in this course). Outline the database deciding which information will be saved in each entity. ce you finish analysis and design, implement the MovinOn database using MySQL. se Problem for this assignment MovinOn Inc. is a Moving and Storage Company based in the northwestern United States. Having own from a start-up venture consisting of one vehicle and one warehouse in Oregon, the company is rgeoning into other states and is outgrowing its paper-based recordkeeping system. The CEO of ovinOn Inc., David Bowers, hired an information system manager to design a database to manage spreadsheets and paper forms. However, with a merger in the company's future, David needs to expand his system to manage data better. David recently hired Robert Iko, an information systems specialist, to recommend and implement a new plan for managing the company's data. Robert's first task is to understand the current system and its limitations by talking extensively with David about data management and user needs. David explains that the office in each state accepts reservations for moving and storage services by completing a form that includes the customer's name, address, phone number, and the 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 the forms are stored in the on-site offices at the warehouse from which they were purchased. Unfortunately, David 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 must copy the customer's record and send it to the second warehouse so that it is on file at the second location. David wants the new system to be 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. Each warehouse has its own manager, office staff, and moving assistants. Drivers are contract employees and can work for any warehouse. David wants the new system to manage employee data, including personal information, salary information, and work performance. In addition to managing personnel data, David 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

icon
Related questions
Question
Database Design and Business Applications Development Case Study 1 - The following exercise allows you to conceptualize a database Using the following text, describe the Problem you are trying to solve. Determine which information is necessary to create a database (you must follow the Steps in Designing a Database covered in this course). Outline the database deciding which information will be saved in each entity. Once you finish analysis and design, implement the MovinOn database using MySQL. Case Problem for this assignment Movin On Inc. is a Moving and Storage Company based in the northwestern United States. Having grown from a start-up venture consisting of one vehicle and one warehouse in Oregon, the company is burgeoning into other states and is outgrowing its paper-based recordkeeping system. The CEO of MovinOn Inc., David Bowers, hired an information system manager to design a database to manage employee, driver, customer, and order data. Your assistance is required to design the objects in the database so that the present inefficient system is replaced, and the new system is reliable upon implementation. You will also assist in securing the database so that the company's data is not accidentally or maliciously deleted. Creating the database design for MovinOn Inc. MovinOn Inc. is a moving company that provides moving and storage services in Washington, Oregon. and Wyoming. 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 1995 with a single truck and a single warehouse in Oregon. Due to a very satisfied clientele, the company has grown over the years a much larger business. Currently, MovinOn has one warehouse in each state it services and is working on a merger with another company that offers similar services in different service areas. When the merger is complete, MovinOn will acquire additional storage warehouses, trucks, and employees and will expand its operations into different states. Last Updated: May 15, 2023 David Bowers is the CEO of MovinOn. In the past, David managed the business using a combination of spreadsheets and paper forms. However, with a merger in the company's future, David needs to expand his system to manage data better. David recently hired Robert Iko, an information systems specialist, to recommend and implement a new plan for managing the company's data. Robert's first task is to understand the current system and its limitations by talking extensively with David about data management and user needs. David explains that the office in each state accepts reservations for moving and storage services by completing a form that includes the customer's name, address, phone number, and the 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 the forms are stored in the on-site offices at the warehouse from which they were purchased. Unfortunately, David 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 Movin On employee must copy the customer's record and send it to the second warehouse so that it is on file at the second location. David wants the new system to be 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. Each warehouse has its own manager, office staff, and moving assistants. Drivers are contract employees and can work for any warehouse. David wants the new system to manage employee data, including personal informatic salary information, and work performance In addition to managing personnel data, David 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 van used, actual mileage, and actual weight. Any request constitutes a "job" in the lingo of the company - a job must include all the pertinent data. including information about the customer, the originating location, the destination location, and the estimated mileage and weight of the load. Robert gathered a collection of documents during the discovery 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 entities in your design. Remember that you will first begin the discovery and planning phases of creating a database for the MovinOn. You will use the documents that Robert provides to develop a database design. After completing the database design, Robert will review it and provide feedback that you will use to create the database. Complete the following: 1. Robert gave you the form shown in Figure 1, which collects data about employees. In addition to storing the data shown in Figure 1, Robert also needs to identify the warehouse in which the employee works. On paper, design an employee entity and any other necessary entities based on
Transcribed Image Text:Database Design and Business Applications Development Case Study 1 - The following exercise allows you to conceptualize a database Using the following text, describe the Problem you are trying to solve. Determine which information is necessary to create a database (you must follow the Steps in Designing a Database covered in this course). Outline the database deciding which information will be saved in each entity. Once you finish analysis and design, implement the MovinOn database using MySQL. Case Problem for this assignment Movin On Inc. is a Moving and Storage Company based in the northwestern United States. Having grown from a start-up venture consisting of one vehicle and one warehouse in Oregon, the company is burgeoning into other states and is outgrowing its paper-based recordkeeping system. The CEO of MovinOn Inc., David Bowers, hired an information system manager to design a database to manage employee, driver, customer, and order data. Your assistance is required to design the objects in the database so that the present inefficient system is replaced, and the new system is reliable upon implementation. You will also assist in securing the database so that the company's data is not accidentally or maliciously deleted. Creating the database design for MovinOn Inc. MovinOn Inc. is a moving company that provides moving and storage services in Washington, Oregon. and Wyoming. 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 1995 with a single truck and a single warehouse in Oregon. Due to a very satisfied clientele, the company has grown over the years a much larger business. Currently, MovinOn has one warehouse in each state it services and is working on a merger with another company that offers similar services in different service areas. When the merger is complete, MovinOn will acquire additional storage warehouses, trucks, and employees and will expand its operations into different states. Last Updated: May 15, 2023 David Bowers is the CEO of MovinOn. In the past, David managed the business using a combination of spreadsheets and paper forms. However, with a merger in the company's future, David needs to expand his system to manage data better. David recently hired Robert Iko, an information systems specialist, to recommend and implement a new plan for managing the company's data. Robert's first task is to understand the current system and its limitations by talking extensively with David about data management and user needs. David explains that the office in each state accepts reservations for moving and storage services by completing a form that includes the customer's name, address, phone number, and the 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 the forms are stored in the on-site offices at the warehouse from which they were purchased. Unfortunately, David 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 Movin On employee must copy the customer's record and send it to the second warehouse so that it is on file at the second location. David wants the new system to be 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. Each warehouse has its own manager, office staff, and moving assistants. Drivers are contract employees and can work for any warehouse. David wants the new system to manage employee data, including personal informatic salary information, and work performance In addition to managing personnel data, David 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 van used, actual mileage, and actual weight. Any request constitutes a "job" in the lingo of the company - a job must include all the pertinent data. including information about the customer, the originating location, the destination location, and the estimated mileage and weight of the load. Robert gathered a collection of documents during the discovery 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 entities in your design. Remember that you will first begin the discovery and planning phases of creating a database for the MovinOn. You will use the documents that Robert provides to develop a database design. After completing the database design, Robert will review it and provide feedback that you will use to create the database. Complete the following: 1. Robert gave you the form shown in Figure 1, which collects data about employees. In addition to storing the data shown in Figure 1, Robert also needs to identify the warehouse in which the employee works. On paper, design an employee entity and any other necessary entities based on
this form. The design for all entities that you create should include attribute names, data types, attribute properties (as necessary), and attribute description. Remember that each entity must have a primary key attribute. 2. The database must manage data about drivers, who are hired on a contract basis. Design a entity that stores information about drivers. The entity should include the same information stored for employees, except for an indication about the warehouse in which the driver works, in addition to storing the following additional information: ● Drivers are not paid an hourly rate or salary; they are paid based on the number of miles driven for any job. The payments for a job are determined by multiplying the rate per mile by the number of miles for the job. ● MovinOn rates drivers based on their safety records, successful on-time deliveries, and other factors. The company's 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. (You do not need to worry about how MovinOn assigns ratings to drivers, you only need to store the driver's rating.) Figure 1: Employees Information Form Name: Address: mOvinOn Inc. Employee Information Form David Bowers 10124 Metropolitan Drive City: Seattle State: WA ZIP: 98117 Phone: (206) 246-5132 154-00-3785 Cell Phone: (206) 575-4321 Date of Birth: 9/12/1958 SSN: (The following information to be filled out by MovinOn human resources manager) Hire Date: 1/22/1998 Termination Date: General Manager Position: Annual Salary: $72,000 Date of last personnel review: Notes about this employee: or Hourly Rate: 3. Design an entity that stores data about the trucks and vans owned by MovinOn. Each vehicle has a unique identification number appearing on the vehicle in the format TRK-001 for trucks or VAN-009 for vans. David wants to store the vehicle's license plate number, number of axles, and color. 4. Design an entity that stores data about warehouses using the data shown in Figure 2. The warehouse identification number is the two-letter state abbreviation in which the warehouse is Last Updated: May 15, 2023 located followed by a dash and then a number. For example, the warehouse in Wyoming is WY-1. Figure 2: Data about warehouses 25- File % J Clipboard a A1 Insert Cab BIU 1 Warehouse ID Address 2 OR-1 3 WA-1 4 WY-L Formulas Data 60- -11-AA === *- 3 - A === - Warehouse ID City State CR #3 Industrial Park Way Portland Seattle 8798 Warehouse Rd WA 54781 Hixson Pike Jackson Hole WY 6 10 11 Alignment 12 13 14 20 Warehouseaha Microsoft Excel * 19 20 Zip 97212 98121 83001 Figure 3: Data about storage units 21 22 23 24 25 14 A B 1 Unit ID Warehouse ID Unit Size Rent 1 CR-1 2 8x8 3 1 WA-1 4 1 WY-1 2 CR-1 2 WA-1 2 WY-1 3 OR-1 jong 5. Currently, information about storage units is stored in an Excel workbook; a portion of this data is shown in Figure 3. Use this information to help you design an entity that manages data about the storage units. General 3 WA-1 Mag 3 WY-1 4 OR-1 Number 4 WA-1 con 4 WY-1 5 OR-1 5 WA-1 WA-1 5 WY-1 Swf-1 6 OR-1 6 WA-1 www 6 WY-1 owne 7 CR-1 7 WA-1 7 WY-1 On 8 CR-1 28 29 Phone Climate Controlled Security Gate 503-551-2432 No 206-324-2312 Yes 307-541-3571 Yes 8 WA-1 8 WY-1 Storage Units 12 x 12 12 x 12 8 x 12 12 x 12 L Conditional Format Cell Formatting as Table Styles - Styles 12 x 12 8x8 6x5 9 x 12 DALE 12 x 12 8 x 12 9 x 12 Fee 12 x 18 462 12 x 12 442 44 12 x 18 8 x 12 12 x 12 12 x 12 ka 8 x 10 ok.com 12 x 10 * A 40 12 x 12 15 x 15 12 x 10 12 x 8 25.00 35.00 45.00 30.00 30.00 53.00 35.00 45.00 45.00 25.00 20:00 30.00 www 45.00 30.00 www 30.00 70.00 25.00 www 85.00 www. 70.00 10:00 30.00 www.w 85.00 45.00 25.00 Conve 80.00 85.00 95.00 No 80.00 35.00 Inter- Delete 3 Formal Cell: Sort & Find & 2Fiter-Select- Eating 6. You also need to manage data to indicate which customer rents which unit. David wants to store the date the lease started and ended on each unit in each warehouse. For current customers, the ending lease date will be null. Design an entity that manages data about unit rentals.
Transcribed Image Text:this form. The design for all entities that you create should include attribute names, data types, attribute properties (as necessary), and attribute description. Remember that each entity must have a primary key attribute. 2. The database must manage data about drivers, who are hired on a contract basis. Design a entity that stores information about drivers. The entity should include the same information stored for employees, except for an indication about the warehouse in which the driver works, in addition to storing the following additional information: ● Drivers are not paid an hourly rate or salary; they are paid based on the number of miles driven for any job. The payments for a job are determined by multiplying the rate per mile by the number of miles for the job. ● MovinOn rates drivers based on their safety records, successful on-time deliveries, and other factors. The company's 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. (You do not need to worry about how MovinOn assigns ratings to drivers, you only need to store the driver's rating.) Figure 1: Employees Information Form Name: Address: mOvinOn Inc. Employee Information Form David Bowers 10124 Metropolitan Drive City: Seattle State: WA ZIP: 98117 Phone: (206) 246-5132 154-00-3785 Cell Phone: (206) 575-4321 Date of Birth: 9/12/1958 SSN: (The following information to be filled out by MovinOn human resources manager) Hire Date: 1/22/1998 Termination Date: General Manager Position: Annual Salary: $72,000 Date of last personnel review: Notes about this employee: or Hourly Rate: 3. Design an entity that stores data about the trucks and vans owned by MovinOn. Each vehicle has a unique identification number appearing on the vehicle in the format TRK-001 for trucks or VAN-009 for vans. David wants to store the vehicle's license plate number, number of axles, and color. 4. Design an entity that stores data about warehouses using the data shown in Figure 2. The warehouse identification number is the two-letter state abbreviation in which the warehouse is Last Updated: May 15, 2023 located followed by a dash and then a number. For example, the warehouse in Wyoming is WY-1. Figure 2: Data about warehouses 25- File % J Clipboard a A1 Insert Cab BIU 1 Warehouse ID Address 2 OR-1 3 WA-1 4 WY-L Formulas Data 60- -11-AA === *- 3 - A === - Warehouse ID City State CR #3 Industrial Park Way Portland Seattle 8798 Warehouse Rd WA 54781 Hixson Pike Jackson Hole WY 6 10 11 Alignment 12 13 14 20 Warehouseaha Microsoft Excel * 19 20 Zip 97212 98121 83001 Figure 3: Data about storage units 21 22 23 24 25 14 A B 1 Unit ID Warehouse ID Unit Size Rent 1 CR-1 2 8x8 3 1 WA-1 4 1 WY-1 2 CR-1 2 WA-1 2 WY-1 3 OR-1 jong 5. Currently, information about storage units is stored in an Excel workbook; a portion of this data is shown in Figure 3. Use this information to help you design an entity that manages data about the storage units. General 3 WA-1 Mag 3 WY-1 4 OR-1 Number 4 WA-1 con 4 WY-1 5 OR-1 5 WA-1 WA-1 5 WY-1 Swf-1 6 OR-1 6 WA-1 www 6 WY-1 owne 7 CR-1 7 WA-1 7 WY-1 On 8 CR-1 28 29 Phone Climate Controlled Security Gate 503-551-2432 No 206-324-2312 Yes 307-541-3571 Yes 8 WA-1 8 WY-1 Storage Units 12 x 12 12 x 12 8 x 12 12 x 12 L Conditional Format Cell Formatting as Table Styles - Styles 12 x 12 8x8 6x5 9 x 12 DALE 12 x 12 8 x 12 9 x 12 Fee 12 x 18 462 12 x 12 442 44 12 x 18 8 x 12 12 x 12 12 x 12 ka 8 x 10 ok.com 12 x 10 * A 40 12 x 12 15 x 15 12 x 10 12 x 8 25.00 35.00 45.00 30.00 30.00 53.00 35.00 45.00 45.00 25.00 20:00 30.00 www 45.00 30.00 www 30.00 70.00 25.00 www 85.00 www. 70.00 10:00 30.00 www.w 85.00 45.00 25.00 Conve 80.00 85.00 95.00 No 80.00 35.00 Inter- Delete 3 Formal Cell: Sort & Find & 2Fiter-Select- Eating 6. You also need to manage data to indicate which customer rents which unit. David wants to store the date the lease started and ended on each unit in each warehouse. For current customers, the ending lease date will be null. Design an entity that manages data about unit rentals.
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 7 steps with 1 images

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Similar questions