Page 1
Individual Final Project
Create A Database System
Purpose:
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. 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.
Page 2
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
Page 3
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.
Page 4
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 .
Page 5
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 which comprise the primary key should be underlined and attributes that are foreign
keys should be italized.
□ 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 6
! 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.
**Note: The final deliverable will consist of three files. The submission will consist of the
following three files:
1. A .pdf file that merges the above sections into one file (except step 4, which is the
DDL script).
2. The DDL SQL script as a .txt file. This file should include both CREATE TABLES
and INSERT INTO VALUES syntax.
3. The SQL syntax (from step 7 above) with the necessary queries to demonstrate the
functionality of the database. This file should be a .txt file.
