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
Related questions
Question
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
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
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 7 steps with 1 images
