KitPlanes Gets a New Database

(1)

MASTER’S PROJECT

KitPlanes Gets a New

Database

The implementation of a newly designed

database with new GUI design

Rachael Lyon And Christiane Wruck 12/6/2011

(2)

1 | P a g e

About the Project

Brief Summary

We designed a small, but complex database for a company called KitPlanes. KitPlanes had an extremely old, inefficient database in place with additional complications regarding hosting of the database server. Because they are a small company and the economic environment is tight, we were told that KitPlanes was unwilling to spend money to remedy their poor database. We offered to create a database for the company as our Master’s project and we weren’t paid for our work. Learning About Buy-In

Offering a service for free was a mistake. Because it was being billed as ‘just a Master’s Project’, we had a difficult time getting the information we required in order to make the database a true success. For instance, when we began the project, we asked for some data definitions, but were told that no one had time to provide us with answers. Over the next several months, we were able to glean bits of information from the company, but most of the information they gave us just lead to more questions. With no other alternative, we were forced to research this very small, but

complicated subject matter to begin to understand how the database could be designed more optimally. After several months of research and re-designs with very limited help from KitPlanes employees, we learned that KitPlanes had hired an external consultant to give them an idea of how much it would cost to have their database and website updated. The external consultant required KitPlanes to complete a spreadsheet with data definitions. This spreadsheet was almost identical to the one we had requested they complete, many months prior. They completed it overnight (and thankfully shared it with us). We were able to gain a much better perspective of the information as it pertained to KitPlanes through this spreadsheet, but were also made aware that the database would need to hold much more data than we originally anticipated.

Once we realized that KitPlanes wasn’t going to implement our solution, we decided to work from the recent data definitions and original goals discussed at the onset of the project. The scope of their database began to change once the outside consultant was hired and we were forced to move forward as our own deadlines for our project loomed. This is also one of the reasons why we did not fully implement the graphical user interface (GUI). We worked extensively with a PHP developer to ensure that our communication with the developer was clear and adequate, but asking them to complete the extensive code for free seemed inappropriate. Although we were not required by our advisor, Dr. Fang, to complete a GUI, we felt that the process of working with a developer and understanding how the database design would be accessed by the user were important things to learn.

(4)

3 | P a g e

About the Company

KitPlanes is a leading publisher of reader-focused magazines, newsletters, books, websites and electronic media, including information on products related to general homebuilt aviation. The mission is to provide readers with information that's timely, trustworthy and relevant. KitPlanes produces a monthly magazine as well as online news articles. Their website provides a supplier directory, external links, books, an event calendar, classifieds and links to support groups. KitPlanes provides a space for a community of people who build planes.

KitPlanes are the sole keepers of information for this very niche market of homebuilt airplanes, rotorcraft, ultra-lights, and other aircraft. Their online tool is intended to search their database of all available information in this small market. Their four, annual magazine buyer’s guides provide a brief snapshot of all current, pertinent aircraft available and are also generated from their database.

The Information that KitPlanes holds is not vast, but it is detailed. Each aircraft has a tremendous number of possible attributes. The complexity of the KitPlanes database, therefore, does not lie in a tremendous number of aircraft records, but in the complexity of each record.

User Requirements

The current 10 year-old KitPlanes database barely supports an online search mechanism and four annual printed buyer’s guides: Kits, Plans/Built, Rotorcraft and Engine. The search results for the online tool are extraordinarily inaccurate and the labor involved in extracting information from the database for the buyer’s guides is tremendous. Entering new data into the system currently occurs only four times a year, due to the laborious process required. There have been numerous quick-fix attempts to increase the accuracy of search results, but the lack of constraints present when entering information into the database is the most obvious culprit for the poor search results.

(5)

4 | P a g e

The KitPlanes website and SQL database are hosted by a relative of the owner of KitPlanes. This relative charges exorbitant fees for even the simplest of modifications. This is one of the many reasons why the database has not been updated over the past decade. The chief editor of the

KitPlanes magazine, Mary Bernard, has been able to work with their contracted web designer, Omar Filipovic, to work around using the relative’s server for some things (like their blog,

http://kitplanes2.com/blog/). It is currently cheaper for KitPlanes to pay their web designer to create a second KitPlanes website (kitplanes2.com) which he has seamlessly designed and linked to the existing website, than it is to pay the relative to make adjustments to the website.

When it is time to begin the publication of one of the annual buyer’s guides, Mary contacts Omar who extracts the entire database (in the form of an Excel spreadsheet) from the MySQL server which is owned and housed by the relative mentioned above. Mary then extracts the information that is pertinent for the particular buyer’s guide. Her task is to then modify the useful information, add any new information or updates and then attempt to move the information into InDesign. When she’s finished, she has to return the original spreadsheet to Omar, with any additions and/or modifications. This is no small task, as every column in the spreadsheet is duplicated (one of the funny ways they’ve tried to make the online search tool operate more effectively). Finally, Omar re-uploads the information back onto the SQL server.

(6)

5 | P a g e

Ideally, Mary would like the database to accomplish the following as inexpensively as possible:

1) Provide more accurate results for the online search tool, 2) Allow modifications and additions to be made more easily, 3) Make the Buyer’s Guide process less time consuming, and 4) Avoid relying on the relative’s resources.

Our solutions to each of these goals:

1) Tighter controls on data entry and normalized database design will allow for more accurate results when using the online search tool.

2) Because there are very few KitPlanes employees and those employees are spread out throughout the country, we are electing to design a web-based database with a user friendly graphical user interface (GUI),

3) Creating customized queries and exports for each of the buyer’s guides will allow the team to export directly into InDesign and other formats.

4) A GoDaddy.com account will provide webhosting and a SQL server for $5/month.

Note: Full implementation of the GUI was beyond the scope of our proposed Master’s project, but we were interested in learning from the process of designing one. We consulted with a PHP

developer to get a good understanding of what would be required to achieve our intended outcome (for more on this, please see Designing the GUI). For the purpose of the project, the SQL server was developed on a virtual machine provided by the University.

Designing the New Database

The existing database had two tables: Manufacturer and Airplane. The Airplane table contained 1067 records and 90 columns (attributes). Many of these attributes were duplicates or contained too much information, were merely calculations of other fields, or needed to be split into multiple attributes (for example: 80/50-115 becomes three separate columns regarding

Horsepower: Low Horsepower, High Horsepower and Actual Horsepower). The information in this table was extremely inconsistent and required quite a bit of labor to clean manually and using Ruby on Rails.

The Manufacturer table contains 439 records and 20 columns. Several of these columns were entirely useless or contained poorly recorded information. Similar to the Airplane table, the Manufacturer table also contained combined information, like city, state and zip code all entered in the same field.

Gaining an understanding of the information was one very long step. As mentioned earlier, (see Learning about Buy-In) we were lucky to receive a document full of data definitions

(although much later in the process than we would have liked) that allowed us to not only gain a better understanding of the information we had, but also allowed us to gain a better understanding of the information as it pertains to KitPlanes operations.

A Brief Description

Despite having only two tables in the current database, the information it contained was deceptively complicated. Each aircraft is included in one of the three aircraft buyer’s guides: Kits,

(7)

6 | P a g e

Plans/Built, or Rotorcraft. The aircraft and engines are each made by a manufacturer, and most (but not all) come with an engine (gliders will never have an engine, for instance). Some engines are sold alone and some are sold with an aircraft. The buyer’s guides provide an abbreviated summary of each record while the online search tool references additional attributes for each product. The Engine buyer’s guide includes much more information about an engine than do the other buyer’s guides.

Manufacturer Entity

Manufacturers may make one or more aircraft and/or engines. Each aircraft is only

manufactured by one manufacturer. Each engine is manufactured by only one manufacturer. In our design we wanted each manufacturer to be identified as a manufacturer of engines, aircraft or both. Additionally, we wanted to provide a place for the users of the database to leave notes for other database users to see, and a place for users to include notes that would be visible to online users. Manufacturers are identified by a Manufacturer ID and phone numbers may be a multi-valued attribute, but are not necessary. Other attributes include: Manufacturer Name, Street, City, State, Country, Fax, Email, Website and Zip Code.

Aircraft Entity

Aircraft are each made by one manufacturer. An aircraft has one of three different LSA ratings and one of four different types of landing gear. There are currently four types of materials that are used to make all aircraft, but we have left room for additional materials to be added in the future. Multiple materials can be used. Some aircraft have a “Quick Build” option which means there is less assembly time required, but the cost is more. Not all aircraft have this option. All aircraft have costs associated with them and can include cost of the plans, kit and estimates for the highest

and lowest amounts of money that could be spent (depending on accessories included). The physical dimensions of each aircraft include empty weight, gross weight, length, height, cabin width and seating arrangements. As mentioned earlier, an aircraft may or may not have an engine, and those without an engine have a few unique attributes. Similarly, those without wings have a few unique attributes. In our design of the database, we first require an aircraft to be put into one of the three guides: Rotorcraft, Kit or Plans/Built. Rotorcrafts do not have wings, and therefore require these unique attributes to be included: Disk Area and Disk Span. Kit and Plans/Built are winged aircraft. Winged aircraft that do not include an engine are called Sailplanes and Motorgliders. These aircraft include three unique attributes: LD, Aspect Ratio, and Minimum Sink. Winged aircraft also have a wing design. Wing design includes five different variables, and can be multi-valued. Additional Wing details are wing span, area, fold and stall speed. Similar to the manufacturer entity discussed earlier, we wanted to provide a place for the users of the database to leave notes for other database users to see, and a place for users to include notes

Wing Design Landing Gear Materials LSA

High Tricycle Wood _CompliantLSA

Low Tailwheel Fabric ELSA

Mid Tricycle, fixed Tube Ultralight

Canard Floats Composite

(8)

7 | P a g e

that would be visible to online users. Finally, other aircraft attributes include: aircraft name, maximum speed, cruise speed, range, fuel capacity, climb rate, service ceiling, number built, hours to build, take-off distance, landing distance, image and whether or not the aircraft is currently in production. An aircraft that is no longer in production is still included in the KitPlanes database and is still searchable using the online tool.

Engine Entity

A small percentage of engines in the database will be included in the Engine Buyer’s Guide. These engines may contain more attributes than engines that are not included in the buyer’s guide. All engines will require one of five fuel types, and like the aircraft and manufacturer examples, we wanted to provide a place for the users of the database to leave notes for other database users to see, and a place for users to include notes that would be visible to online users. Other attributes that all engines may have include: Actual Horsepower, Low-end Horsepower, High-end Horsepower, displacement, fuel burn, weight, price, image and whether or not the engine is currently in production. Attributes that are only included in engines that are included in the Engine Buyer’s Guide include: Model name, base price, dry weight, number of cylinders, number delivered, and availability. The following are attributes that can only be one of a few options: induction, cylinder design, the type of engine, configuration, cooling, prop and prop drive (see below).

Induction Cylinder

Design Type Configuration Cooling Prop

Prop Drive

Fuel Type Carb Angle Four-Stroke Horizontally

Opposed

Air-Cooled Fixed Pitch Direct 87 UL Injection Parallel Two-Stroke Inline

Water-Cooled

Constant Speed

Gear

reduction 100LL

Electric Radial

Ground-Adjustable Belt reduction Mogas Auto-Conversion V Jet A Diesel

(9)

8 | P a g e

Conceptual Data Model (ER Diagram)

Our first entity-relationship diagram was quite simple. As mentioned, early in the process, we did not receive enough information to gain much of an understanding of the information contained in the database. And we were not made aware of the fourth buyer’s guide, the Engine Buyer’s Guide until 6 weeks before our project was due. Here is a copy of our first ER diagram:

Slowly, as we progressed in our knowledge and understanding of the industry for which we were creating this database, we began to break things down a bit.

(10)

9 | P a g e

Ultimately, we learned new tools for creating ER diagrams and learned more from the data

definitions that we finally received and created this, using Microsoft Visio (see also attached files).

This new database design is quite obviously more complicated than the original. The data

definitions and some help from a couple mentors (John Desha and Michelle Ashton) helped us move from our intermediate design, to a fully developed final design.

The design is relatively self-explanatory except for a few things. The most important of these is the “Guide Type”. Above, you’ll notice that “Guide Type” is between the “Aircraft table” and “Aircraft type”. That may seem counter intuitive and not normalized. However, it is an appropriate implementation of the way KitPlanes views this data. KitPlanes views the most important

distinction, for these aircraft, to be which Buyer’s Guide they are included in. In this case, “Aircraft Type” is being used to distinguish between winged aircraft and rotorcraft. A rotorcraft would use the “Disk” table’s information, but a winged aircraft would not. This implementation allows us to say “If it’s a rotorcraft, look for a Disk ID, if it’s not, look for a Winged ID”. Additionally, there are aircraft that require distinct data, but do not necessarily belong to one Buyer’s Guide. Sailplanes and Motorgliders are a unique type of aircraft. They could belong to one of two of the “Guide Types”, meaning they could be in either the Plans/Built or the Kit Buyer’s guide.

Another non-self-explanatory section of this ER diagram is the Engine/Engine Guide Info. The “Engine” table contains data that is commonly included in the three aircraft buyer’s guides (Rotorcraft, Kit, Plans/Built). “Engine Guide Info” contains additional information that would be included in the Engine Buyer’s Guide. Therefore, the information included in the “Engine” table is the information that would be included in both the aircraft buyer’s guides and the Engine Buyer’s Guide.

(11)

10 | P a g e

Logical Database Design

Our first attempt at a logical database design was based on the first (and most simple) ER diagram above. This was the design that we implemented in Oracle. We also implemented, and listed below, some queries in Oracle.

Our second attempt at a logical database design was based on the last (and most complex) ER diagram above. We also implemented common queries that would be used for exports using the GUI.

ORACLE Logical Design

Manufacturer (Manufacturer ID, M_Name, street, city, state, country, website, email, fax) M_Phone (Manufacturer ID, Phone)

Foreign key Manufacturer ID reference Manufacturer

Plane (Design ID, Manufacturer ID, Engine ID, Design Name, Max Speed, Cruise Speed, LD, Minsink, Aspect Ratio, Range, Climb Rate, Take off_D, Landing_D, Service Ceiling, Empty Weight, Gross Weight, Useful Weight, Height, Length, Wingspan, Wing Area, Fuel Capacity, Wingfold, Seats, Gear, Hours to Build, Built, Info Packet, Cost of Plans, Kit Cost, QB Cost, Plan Cost, LDA, Current, Cabin Width, QB Build Time, Build Cost-Low, Build Cost-High)

Foreign key manufacturer ID reference manufacturer Foreign key Engine ID reference Engine

P_Wing (Design_ID, Wing Design)

Foreign Key Design_ID reference Plane P_Materials (Design_ID, Materials)

Foreign key Design ID reference Plane

Engine (Engine ID, Actual Horsepower, Low-End Horsepower, High-End Horsepower) Rotorcraft (R_Design_ID, Disk Area, Disk Span)

Foreign key R_Design_ID reference Plane Plans/Built (P_Design_ID)

Foreign key R_Design_ID reference Plane Kit (K_Design_ID)

(12)

11 | P a g e

ORACLE Queries:

Select * from Plane,Kit where Plane.Design_ID=Kit.K_Design_ID;

Select * from Plane,Rotorcraft where Plane.Design_ID=Rotorcraft.R_Design_ID; Select * from Plane,Plans_Build where Plane.Design_ID=Plans_Build.P_Design_ID; Select * from Manufacturer natural join M_Phone;

Select Design_ID, count(QB_Cost) from Plane where QB_Cost<='15000' group by Design_ID; Select * from Plane,Kit where Plane.Design_ID=Kit.K_Design_ID and Hour_Build<'150'; Select * from Manufacturer where state='NY';

Views (Just for Fun!)

We have created a few views which will allow the KitPlanes staff to to easily see the data for their three annually printed buyer’s guides. Each view references different tables and results in a unique set of data for each buyer’s guide. The views have not yet been implemented.

Create view Plans_Build as

Select M_Name, Design_Name, Cruise_Speed, Range, Climb_rate, Takeoff_D, Landing_D, Engine_ID, Actual_Horsepower, Low_End, High_End, Fuel_Capacity, Empty_Weight, Gross_Weight, Length, Wingspan, Wingarea, Seats, Cabin_Width, Gear, Materials, Hour_Build, Current, Kit_Cost, Build_Cost_Low, Build_Cost_High, LSA, Website, Phone

From Manufacturer, M_phone, P_Materials, Engine, Plane, Plans_Build Where Manufacturer.manufacturer_ID=M_Phone.manufacturer_ID and

plane.Design_ID=P_Materials.Design_ID and Plane.Engine_ID=Engine.Engine_ID and Plane.Manufacturer_ID=Manufacturer.Manufactuer_ID and

Plans_Build.P_Design_ID=Plane.Design_ID; Create view Rotorcraft as

Select M_Name, Design_Name, Cruise_Speed, Range, Climb_rate, Takeoff_D, Landing_D, Engine_ID, Actual_Horsepower, Low_End, High_End, Fuel_Capacity, Empty_Weight, Gross_Weight,

Useful_Weight, Length, Disk_Area, Disk_Span, Seats, Cabin_Width, Gear, Materials, Hour_Build, Current, Kit_Cost, Build_Cost_Low, Build_Cost_High, QB_Cost, Plan_Cost, LSA, Website, Phone From Manufacturer, M_phone, P_Materials, Engine, Plane, Rotorcraft

Where Manufacturer.manufacturer_ID=M_Phone.manufacturer_ID and

plane.Design_ID=P_Materials.Design_ID and Plane.Engine_ID=Engine.Engine_ID and Plane.Manufacturer_ID=Manufacturer.Manufactuer_ID and

Plane.Design_ID=Rotorcraft.R_Design_ID; Create view Kits as

Select M_Name, Design_Name, Cruise_Speed, Range, Climb_rate, Takeoff_D, Landing_D, Engine_ID, Actual_Horsepower, Low_End, High_End, Fuel_Capacity, Empty_Weight, Gross_Weight, Length, Wingspan, Wingarea, Wing_Design, Seats, Cabin_Width, Gear, Materials, Hour_Build, Current, Kit_Cost, Build_Cost_Low, Build_Cost_High, LSA, Website, Phone

From Manufacturer, M_phone, P_Materials, Engine, Plane, Wing_Design, Kit Where Manufacturer.manufacturer_ID=M_Phone.manufacturer_ID and

plane.Design_ID=P_Materials.Design_ID and Plane.Engine_ID=Engine.Engine_ID and

Plane.Manufacturer_ID=Manufacturer.Manufactuer_ID and Plane.Design_ID=P_Wing.Design_ID and Kit.K_Design_ID=Plane.Design_ID;

(13)

12 | P a g e

Below, please find the Logical database design which corresponds with the most complex ER diagram listed above and the Implementation in MySQL. Following the tables, we have included the queries that would be used for common exports implemented by the GUI design.

MySQL Logical Design

create table aircraft notes (aircraft_notes_id int(5) null, aircraft_id int(5), aircraft_notes varchar(255), aircraft_notes_type varchar(25), primary key (notes_id), foreign key (aircraft_id) references aircraft (aircraft_id));

create table lsa (lsa_id int(5) null, lsa_definition varchar(25), primary key (lsa_id));

create table landing_gear (landing_gear_id int(5) null, landing_gear_description varchar(25), primary key (landing_gear_id));

create table quick_build (quick_build_id int(5) null, quick_build_cost decimal(10,2), primary key (quick_build_id));

create table cost (cost_id int(5) null, plan_cost decimal(10,2), kit_cost decimal(10,2), build_cost_high decimal(10,2), build_cost_low decimal(10,2), primary key (cost_id));

create table materials(materials_id int(5) null, materials_description varchar(255), primary key (materials_id));

create table aircraft_materials(aircraft_materials_id int(5) null, materials_id int(5), primary key (aircraft_materials_id), foreign key (materials_id) references materials (materials_id));

create table aircraft(aircraft_id int(5) null, engine_id int(5), aircraft_materials_id int(5),

physical_dimensions_id int(5), cost_id int(5), quick_build_id int(5), landing_gear_id int(5), aircraft_name varchar(100), max_speed int(5), cruise_speed int(5), range int(5), fuel_capacity int(5), climb_rate int(10), service_ceiling int(10), built int(5), hours_to_build int(5), current enum(yes, no), takeoff int(5), landing int(5), guide_id int(5), lsa_id int(5), image varchar(50), primary key (aircraft_id), foreign key (engine_id) references engine (engine_id), foreign key (aircraft_materials_id) references aircraft_materials

(aircraft_materials_id), foreign key (physical_dimensions_id) references physical_dimensions

(physical_dimensions_id), foreign key (cost_id) references cost (cost_id), foreign key (quick_build_id) references quick_build (quick_build_id), foreign key (landing_gear_id) references landing_gear

(landing_gear_id), foreign key (guide_id) references guide (guide_id), foreign key (lsa_id) references lsa (lsa_id));

(14)

13 | P a g e

create table physical_dimensions(physical_dimensions_id int(5) null , empty int(5), gross int(5), length float(4,0), height int(5), seat int(2), cabin_width int(5));

create table manufacturer(manufacturer_id int(5) null , manufacturer_type_id int(5), manufacturer_name varchar(255) not null, street varchar(100), city varchar(100), state varchar(100), country varchar(150), fax int(13), email varchar(200), website varchar(255), zip varchar(20), primary key (manufacturer_id), foreign key (manufacturer_type_id) references manufacturer_type (manufacturer_type_id));

create table manufacturer_type(manufacturer_type_id int(5) null, aircraft_id int(5), manufacturer_type varchar(100), primary key (manufacturer_type_id), foreign key (aircraft_id) references aircraft

(aircraft_id));

create table guide_type(guide_id int(5) null, aircraft_type_id int(5), engine_id int(5), primary key

(guide_id), foreign key (aircraft_type_id) references aircraft_type (aircraft_type_id), foreign key (engine_id) references engine (engine_id));

create table aircraft_type(aircraft_type_id int(5) null, wing_id int(5), disk_id int(5), aircraft_description varchar(255));

create table winged(wing_id int(5) null, wing_design_id int(5), span float(4), area float(4), fold enum(yes,no), stall_speed int(3), sg_id int(5), primary key (wing_id), foreign key (wing_design_id) references winged (wing_design_id), foreign key (sg_id) references sailplanes_gliders (sg_id)); create table wing_design(wing_design_id int(5) null, wing_description varchar(50), primary key (wing_design_id));

create table sailplanes_gliders(sg_id int(5) null, ld float(4), aspect_ratio varchar(50), minimum_sink float(4), primary key (sg_id));

create table disk(disk_id int(5) null, disk_area float(4), disk_span float(4), primary key (disk_id)); create table engine(engine_id int(5) null, actual_horsepower int(4), low_end_horsepower int(4), high_end_horsepower int(4), manufacturer varchar(50), displacement int(4), fuel_burn decimal(4,2), weight int(4), price decimal(5,2), image varchar(50), current enum(yes,no), comments varchar(255), admin_comments text, engine_guide_info_id int(5), primary key (engine_id), foreign key (fuel_type_id) references fuel_type (fuel_type_id), foreign key (engine_guide_info_id) references engine_guide_info (engine_guide_info_id));

create table phone(phone_id int(5) null, manufacturer_id int(5), phone_type varchar(50), phone_digits int(13), primary key (phone_id), foreign key (manufacturer_id) references manufacturer

(manufacturer_id));

create table fuel_type(fuel_type_id int(5) null, fuel_description varchar(50), primary key (fuel_type_id)); create table prop(prop_id int(5) null, prop_description varchar(50), primary key (prop_id));

(15)

14 | P a g e

create table prop_drive(prop_drive_id int(5) null, prop_drive_description varchar(50), primary key (prop_drive_id));

create table cooling(cooling_id int(5) null, cooling_description varchar(50), primary key (cooling_id)); create table configuration(configuration_id int(5) null, configuration_type varchar(50), primary key (configuration_id));

create table type(type_id int(5) null, type_description varchar(50), primary key (type_id)); create table induction(induction_id int(5) null, induction_description varchar(50), primary key (induction_id));

create table cylinder_design(cylinder_design_id int(5) null, cylinder_design_description varchar(50), primary key (cylinder_design_id));

create table engine_guide_info(engine_guide_info_id int(5) null, prop_drive_id int(5), cooling_id int(5), configuration_id int(5), type_id int(5), cylinder_design_id int(5), induction_id int(5), prop_id int(5), model varchar(100), base_price decimal(10,2), dry_weight int(4), number_cylinders int(2), no_delivered int(10), availability varchar(100), primary key (engine_guide_info_id), foreign key (prop_drive_id) references prop_drive (prop_drive_id), foreign key (cooling_id) references cooling (cooling_id), foreign key (configuration_id) references configuration (configuration_id), foreign key (type_id) references type (type_id), foreign key (cylinder_design_id) references cylinder_design (cylinder_design_id), foreign key (induction_id) references induction (induction_id), foreign key (prop_id) references prop (prop_id)); create table manufacturer notes (manufacturer_notes_id int(5) null, manufacturer_id int(5),

manufacturer_notes varchar(255), manufacturer_notes_type varchar(25), primary key (notes_id), foreign key (manufacturer_id) references manufacturer (manufacturer_id));

create table engine notes (engine_notes_id int(5) null, engine_id int(5), engine_notes varchar(255), engine_notes_type varchar(25), primary key (notes_id), foreign key (engine_id) references engine (engine_id));

(16)

15 | P a g e

Engine Buyer’s Guide Query: Lycoming AND Clone Engines:

select model, base_price, induction_description, dry_weight, cylinder_design_description, prop_description, no_delivered, availability

from engine_guide_info, induction, cylinder_design, prop

where engine_guide_info.induction_id=induction.induction_id And

engine_guide_info.cylinder_design_id=cylinder_design.cylinder_design_id AND engine_guide_info.prop_id=prop.prop_id

group by model; Continental:

select model, base_price, induction_description, dry_weight, prop_description, no_delivered, availability from engine_guide_info, induction, cylinder_design, prop

where engine_guide_info.induction_id=induction.induction_id And engine_guide_info.prop_id=prop.prop_id

group by model; Radials:

select model, base_price, induction_description, dry_weight, type_description,

cylinder_design_description, prop_description, prop_drive_description, no_delivered, availability from engine_guide_info, induction, cylinder_design, prop, prop_drive, type

where engine_guide_info.induction_id=induction.induction_id And

engine_guide_info.cylinder_design_id=cylinder_design.cylinder_design_id AND

engine_guide_info.prop_id=prop.prop_id AND engine_guide_info.type_id=type.type_id AND engine_guide_info.prop_drive_id=prop_drive.prop_drive_id

group by model;

Small Inline and Opposed Engines:

select model, base_price, configuration_type, number_cylinders, type_description, cooling_description, induction_description, dry_weight, prop_drive_description, no_delivered, availability

from engine_guide_info, configuration, type, cooling, induction, prop_drive where engine_guide_info.configuration_id=configuration.configuration_id AND

engine_guide_info.type_id=type.type_id AND engine_guide_info.cooling_id=cooling.cooling_id AND engine_guide_info.induction_id=induction.induction_id AND

engine_guide_info.prop_drive_id=prop_drive.prop_drive_id group by model;

(17)

16 | P a g e

Kit Buyer’s Guide Query:

select aircraft_name, cruise_speed, stall_speed, a_range, climb_rate, takeoff, landing, model, actual_horsepower, low_end_horsepower, high_end_horsepower, fuel_burn, empty, gross, length, span, area, seat, cabin_width, landing_gear_description, materials_description, hours_to_build, built, kit_cost, build_cost_low, build_cost_high, quick_build_id, lsa_definition, website, phone_type

from aircraft, guide_type, aircraft_type, winged, engine, engine_guide_info,

physical_dimensions, landing_gear, aircraft_materials, materials, cost, lsa, manufacturer_type, manufacturer, phone

where aircraft.engine_id=engine.engine_id AND

engine.engine_guide_info_id=engine_guide_info.engine_guide_info_id AND aircraft.physical_dimensions_id=physical_dimensions.physical_dimensions_id AND aircraft.guide_id=guide_type.guide_id AND guide_type.aircraft_type_id=aircraft_type.aircraft_type_id AND aircraft_type.wing_id=winged.wing_id AND aircraft.landing_gear_id=landing_gear.landing_gear_id AND aircraft.aircraft_materials_id=aircraft_materials.aircraft_materials_id AND aircraft_materials.materials_id=materials.materials_id AND aircraft.cost_id=cost.cost_id AND aircraft.lsa_id=lsa.lsa_id AND aircraft.aircraft_id=manufacturer_type.aircraft_id AND manufacturer_type.manufacturer_type_id=manufacturer.manufacturer_type_id AND manufacturer.manufacturer_id=phone.manufacturer_id AND guide_type.guide_description= 'kit'; Plans/Built Buyer’s Guide Query:

Select aircraft_name, cruise_speed, stall_speed, a_range, climb_rate, takeoff, landing, model, actual_horsepower, low_end_horsepower, high_end_horsepower, fuel_burn, empty, gross, length, span, area, seat, cabin_width, landing_gear_description, materials_description, hours_to_build, built, plan_cost, build_cost_low, build_cost_high, phone_type, email from aircraft, guide_type, aircraft_type, winged, engine, engine_guide_info,

physical_dimensions, landing_gear, aircraft_materials, materials, cost, manufacturer_type, manufacturer, phone where aircraft.engine_id=engine.engine_id AND

engine.engine_guide_info_id=engine_guide_info.engine_guide_info_id AND aircraft.physical_dimensions_id=physical_dimensions.physical_dimensions_id AND aircraft.guide_id=guide_type.guide_id AND guide_type.aircraft_type_id=aircraft_type.aircraft_type_id AND aircraft_type.wing_id=winged.wing_id AND aircraft.landing_gear_id=landing_gear.landing_gear_id AND aircraft.aircraft_materials_id=aircraft_materials.aircraft_materials_id AND aircraft_materials.materials_id=materials.materials_id AND aircraft.cost_id=cost.cost_id AND aircraft.aircraft_id=manufacturer_type.aircraft_id AND manufacturer_type.manufacturer_type_id=manufacturer.manufacturer_type_id AND manufacturer.manufacturer_id=phone.manufacturer_id AND guide_type.guide_description= 'plans';

(18)

17 | P a g e

Engine Manufacturer Query:

select manufacturer_name from manufacturer, manufacturer_type where

manufacturer.manufacturer_type_id=manufacturer_type.manufacturer_type_id AND manufacturer_type = 'engine';

All Engine Query:

Select model from engine_guide_info; All Aircraft Query:

Select aircraft_name from aircraft; Rotorcraft Query:

select aircraft_name, cruise_speed, a_range, climb_rate, takeoff, landing, model,

actual_horsepower, low_end_horsepower, high_end_horsepower, fuel_burn, empty, gross, length, disk_span, disk_area, seat, cabin_width, landing_gear_description, Materials_description,

hours_to_build, built, plan_cost, kit_cost, build_cost_high, build_cost_low, quick_build_id,

lsa_definition, website, phone_typefrom aircraft, engine, engine_guide_info, physical_dimensions, guide_type, aircraft_type, disk, landing_gear, aircraft_materials, materials, cost, lsa,

manufacturer_type, manufacturer, phone where aircraft.engine_id=engine.engine_id AND

engine.engine_guide_info_id=engine_guide_info.engine_guide_info_id AND aircraft.physical_dimensions_id=physical_dimensions.physical_dimensions_id AND aircraft.guide_id=guide_type.guide_id AND guide_type.aircraft_type_id=aircraft_type.aircraft_type_id AND aircraft_type.disk_id=disk.disk_id AND aircraft.landing_gear_id=landing_gear.landing_gear_id AND aircraft.aircraft_materials_id=aircraft_materials.aircraft_materials_id AND aircraft_materials.materials_id=materials.materials_id AND aircraft.cost_id=cost.cost_id AND aircraft.lsa_id=lsa.lsa_id AND aircraft.aircraft_id=manufacturer_type.aircraft_id AND manufacturer_type.manufacturer_type_id=manufacturer.manufacturer_type_id AND manufacturer.manufacturer_id=phone.manufacturer_id AND guide_type.guide_description = 'rotorcraft';

Aircraft Manufacturer Query:

select manufacturer_name from manufacturer, manufacturer_type where

manufacturer.manufacturer_type_id=manufacturer_type.manufacturer_type_id AND manufacturer_type = 'aircraft';

All Manufacturer Query:

Select * from manufacturer, phone where manufacturer.manufacturer_id=phone.manufacturer_id group by manufacturer_name;

(19)

18 | P a g e

Cleaning the Data

The data we received was a mess. The original data allowed for almost any value to be input and that created lots of data inconsistences. Many attributes had values that made no sense and included special characters. To begin cleaning the data we knew we would need a little help. We recruited a programmer to get rid of special characters and lowercase everything. Before we could begin the cleaning we needed to assign each column a number so it was easier to manipulate. After assigning numbers to the 90 columns we started to manipulate the columns we are keeping from the plane table for the new database (shown on next pages). We followed the same steps for the manufacturer table (shown on next pages). Unfortunately, the ruby couldn’t do all the data cleaning so we moved to excel next. Using excel we separated the two tables into the many tables from the new database. All the formatting for SQL was done in excel with single quote marks around the varchar attributes, commas after each attribute, and insert syntax with parenthesis’s and semicolon.

Starting with aircraft I began by creating a primary key using the row function in excel. Next was the engine ID but in order to create the engine table the fuel_type and engine_guide_type tables needed primary keys. The engine_guide_type table needs foreign keys from induction,

cylinder_design, type, configuration, cooling, prop_drive, prop tables, which are all list tables with a few specific values. After creating the list tables associated with engine_guide_info we needed to insert all the foreign keys from the list tables by putting the ID’s from the tables in the place of the current attributes. With the engine_guide_info table complete we can take the engine_guide_info_id to the engine table and create the list table fuel_type. The fuel_type table will have an id included in the engine table and the current engine value fuel_type will be replaced with an id. The attributes from engine needed a primary key generated and taking the columns from the original plane table. Now the aircraft table is ready to have the engine_id so the next attribute is the

aircraft_materials_id. First, we need to start with the materials table by listing all the materials available to make the aircrafts. Next, the aircraft_materials table joined materials id to materials for each plane. Twenty-eight combinations of materials needed to be put into the aircraft_materials table. The aircraft_materials_id replaced the column of materials in the aircraft table. The next step was to take the physical dimensions attributes empty weight, gross weight, length, height, seat, cabin width to the physical dimensions table and replace the physical dimensions id in the aircraft table. Then move the attributes plan cost, kit cost, build cost high, build cost low into the cost table and insert the cost id into the aircraft table. The quick build table contains quick build cost and quick build time with an quick build id; the quick build id will also be moved to the aircraft table. The landing gear attribute is going to be moved to the landing gear table with the landing gear id in the aircraft table. The LSA table will have the description with the LSA id in the aircraft table. The notes were also taken out of the aircraft table and replaced with a notes id. The last attribute is the guide id in order to create the guide table--- but in order to create that we need the wing design, winged, sailplanes gliders, aircraft type, and disk. Starting with wing design the wing design id is put into the winged table. Next the sailplanes gliders table with aspect ratio, minimum sink and the SG id in the winged table. The winged table id was put into the aircraft type table. The disk area and disk span attributes are put into the disk table with the disk id in the aircraft type table. The aircraft type id is in the guide type table which also contain engine id and guide description. The

(20)

19 | P a g e

last three tables were the phone table, manufacturer, and manufacturer type tables. The phone table contains the phone numbers of the manufacturer’s with the phone id in the manufacturer table. The manufacturer tables contained lots of data inconsistencies just the addresses were repeated in multiple fields. This table took the longest to complete as the address needed to be fixed by hand because there was no consistent pattern. In addition the ruby also verified websites and email address fields. The manufacturer type table needed to be completed before

manufacturer as the manufacturer type id is in the manufacturer table. The manufacturer type table contained both the engine id and aircraft id. Now the manufacturer table is complete with the manufacturer type id. Any data with missing values or inaccurate information was left out as we do not have the expertise to complete this information. Below are screen shots from the excel files we worked with.

(21)

20 | P a g e

Ruby For Aircraft Table require 'csv' DETAIL_ID = 0 DESIGN_NUMBER = 1 DESIGN_NAME = 2 YEARS = 3 MANUFACTURER_ID = 4 MANUFACTURER_NAME = 5 COMMENT_1 = 6 MAX_SPEED = 7 CRUISE_SPEED = 8 RANGE = 9 CLIMB_RATE = 10 ASPECT_RATIO = 11 LD = 12 MINSINK = 13 TAKEOFF_DISTANCE_1 = 14 TAKEOFF_DISTANCE_2 = 15 LANDING_DISTANCE_1 = 16 LANDING_DISTANCE_2 = 17 SERVICE_CEILING = 18 ENGINE_1 = 19 ENGINE_2 = 20 HORSEPOWER = 21 EMPTY_WEIGHT = 22 GROSS_WEIGHT = 23 USEFUL_WEIGHT = 24 HEIGHT = 25 LENGTH = 26 WINGSPAN = 27 WING_AREA = 28 FUEL_CAPACITY = 29 WNGDSN_1 = 30 WNGDSN_2 = 31 WNGFOLD = 32 NUMBER_OF_SEATS = 33 GEAR_1 = 34 MTRLS_1 = 35 HOURS_TO_BUILD = 36 BUILT = 37 INFO = 38 PLANS = 39 KIT_1 = 40 KIT_2 = 41 ULTRALIGHT = 42 REPLICA = 43 ROTOR = 44 TWIN = 45 AMPHIBIAN = 46 PLANS_ONLY = 47 PUSHER = 48 GLIDERS = 49 MOTORGLIDER = 50 STALL_SPEED = 51 PARACHUTE = 52 TRIKE = 53 AEROBATIC = 54 RACING = 55 SEATS_1 = 56 SEATS_2 = 57 SEATS_3 = 58 SEATS_4 = 59 SEATS_OVER_4 = 60 TANDEM = 61 DEVELOPMENT = 62 KIT_COST = 63 PLAN_COST = 64 ENGINE = 65 NUMBER_BUILT = 66 WING_DESIGN = 67 LANDING_GEAR = 68 MATERIALS = 69 DEC = 70 RTR = 71 DEV = 72 JAN = 73 FEB = 74 LSA = 75 CURRENT = 76 CABIN_WIDTH = 77 INCLUDES_ENGINE = 78 QB_PLANS = 79 BUILD_TIME_QB = 80 BUILD_COST_LOW = 81 BUILD_COST_HIGH = 82 COUNTRY_OF_MANUFACTURE = 83 SLSA_COST = 84 ELSA_COST = 85 FAA_OFFICE = 86 LSAS_CERTIFIED_US = 87 ELSA_BUILD_TIME = 88 SLSA = 89 TEST_FIELD = 90 class String def remove_special_char! self.gsub!(/[^\d\w\s]/, '') end def truncate_spaces! self.gsub!(/\s+/, ' ') end end skip_rows = [321] CSV.open('/Users/jason/Desktop/r achael/aircraft_out.csv', 'wb') do end end skip_rows = [321] CSV.open('/Users/jason/Desktop/r achael/aircraft_out.csv', 'wb') do |output| first_row = true row = 0 CSV.foreach('/Users/jason/Desktop /rachael/aircraft.csv') do |input| if first_row first_row = false output << input else input.each_with_index do |cell, index| unless cell.nil? cell.strip! case index when DESIGN_NAME, COMMENT_1, ENGINE_1, GEAR_1, WNGDSN_1 cell.downcase! cell.remove_special_char! cell.truncate_spaces! when MAX_SPEED, CRUISE_SPEED, RANGE, CLIMB_RATE,TAKEOFF_DISTANCE_ 1,TAKEOFF_DISTANCE_2,LANDING_ DISTANCE_2,LANDING_DISTANCE_ 1, SERVICE_CEILING, HORSEPOWER, EMPTY_WEIGHT, GROSS_WEIGHT, USEFUL_WEIGHT,HOURS_TO_BUIL D,BUILT,KIT_1,KIT_2 cell.strip! cell.gsub!(/^$?/, '') cell = cell[/^\d+/] end end end output << input row += 1 end end end

(22)

21 | P a g e

Ruby For Manufacturer Table require 'csv' DETAIL_ID = 0 MANUFACTURER_ID = 1 MANUFACTURER_NAME = 2 RECEIVED = 3 STREET = 4 CITY = 5 STATE = 6 ZIP = 7 COUNTRY = 8 COUNTRY_1 = 9 PHONE_1 = 10 FAX = 11 PHONE_2 = 12 WEB = 13 EMAIL = 14 DEC = 15 ROTOR = 16 DEVELOP = 17 JAN = 18 FEB = 19 class String def remove_special_char! self.gsub!(/[^\d\w\s]/, '') end def truncate_spaces! self.gsub!(/\s+/, ' ') end end skip_rows = [321] CSV.open('/Users/jason/Desktop/r achael/manufacturer_out.csv', 'wb') do |output| first_row = true row = 0 CSV.foreach('/Users/jason/Desktop /rachael/manufacturer.csv') do |input| if first_row first_row = false output << input else input.each_with_index do |cell, index| unless cell.nil? cell.strip! case index output << input else input.each_with_index do |cell, index| unless cell.nil? cell.strip! case index when MANUFACTURER_ID, STREET, CITY, STATE

cell.remove_special_char! cell.truncate_spaces! when ZIP if cell.match(/^\d{5}-\d{4}/) cell = cell[/^\d{5}/] end cell.gsub!(/\s+/, '') when CITY cell = cell[/^.+(?=,)/] when PHONE_1, PHONE_2, FAX cell.gsub!(/[^\d]/, '') when EMAIL unless cell.match(/[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}/) cell = '' end when WEB cell.gsub!('"', '') if cell.match(/^http:\/\//) cell.gsub(/^http:\/\//, '') elsif cell.match(/^https:\/\//) cell.gsub(/^https:\/\//, '') end end end end output << input row += 1 end end end

(23)

22 | P a g e

Implementation in Oracle

As we mentioned above, our Oracle implementation was much simpler than our MySQL implementation. There are far fewer tables, with many more attributes. Please find the screenshots below.

(24)

(25)

24 | P a g e

(26)

25 | P a g e

(27)

26 | P a g e

Export Queries

Plans/Built Buyer’s Guide Export Query:

Select aircraft_name, cruise_speed, stall_speed, a_range, climb_rate, takeoff, landing, model,

actual_horsepower, low_end_horsepower, high_end_horsepower, fuel_burn, empty, gross, length, span, area, seat, cabin_width, landing_gear_description, materials_description, hours_to_build, built, plan_cost, build_cost_low, build_cost_high, phone_type, email

into outfile 'c://users/rachael/Documents/capstone/Plans.csv' fields terminated by ',' enclosed by '"' escaped by '\\' lines terminated by '\n'

from aircraft, guide_type, aircraft_type, winged, engine, engine_guide_info, physical_dimensions, landing_gear, aircraft_materials, materials, cost, manufacturer_type, manufacturer, phone where aircraft.engine_id=engine.engine_id AND

engine.engine_guide_info_id=engine_guide_info.engine_guide_info_id AND aircraft.physical_dimensions_id=physical_dimensions.physical_dimensions_id AND aircraft.guide_id=guide_type.guide_id AND guide_type.aircraft_type_id=aircraft_type.aircraft_type_id AND aircraft_type.wing_id=winged.wing_id AND aircraft.landing_gear_id=landing_gear.landing_gear_id AND aircraft.aircraft_materials_id=aircraft_materials.aircraft_materials_id AND aircraft_materials.materials_id=materials.materials_id AND aircraft.cost_id=cost.cost_id AND aircraft.aircraft_id=manufacturer_type.aircraft_id AND manufacturer_type.manufacturer_type_id=manufacturer.manufacturer_type_id AND manufacturer.manufacturer_id=phone.manufacturer_id AND guide_type.guide_description= 'plans'; Export to CSV File Query:

select * into outfile 'c://users/rachael/Documents/capstone/results.csv'

(28)

27 | P a g e

Kit Buyer’s Guide Export Query:

select aircraft_name, cruise_speed, stall_speed, a_range, climb_rate, takeoff, landing, model, actual_horsepower, low_end_horsepower, high_end_horsepower, fuel_burn, empty, gross, length, span, area, seat, cabin_width, landing_gear_description, materials_description, hours_to_build, built, kit_cost, build_cost_low, build_cost_high, quick_build_id, lsa_definition, website, phone_type

into outfile 'c://users/rachael/Documents/capstone/kit.csv' fields terminated by ',' enclosed by '"' escaped by '\\' lines terminated by '\n'

from aircraft, guide_type, aircraft_type, winged, engine, engine_guide_info,

physical_dimensions, landing_gear, aircraft_materials, materials, cost, lsa, manufacturer_type, manufacturer, phone

where aircraft.engine_id=engine.engine_id AND

engine.engine_guide_info_id=engine_guide_info.engine_guide_info_id AND aircraft.physical_dimensions_id=physical_dimensions.physical_dimensions_id AND aircraft.guide_id=guide_type.guide_id AND guide_type.aircraft_type_id=aircraft_type.aircraft_type_id AND aircraft_type.wing_id=winged.wing_id AND aircraft.landing_gear_id=landing_gear.landing_gear_id AND aircraft.aircraft_materials_id=aircraft_materials.aircraft_materials_id AND aircraft_materials.materials_id=materials.materials_id AND aircraft.cost_id=cost.cost_id AND aircraft.lsa_id=lsa.lsa_id AND aircraft.aircraft_id=manufacturer_type.aircraft_id AND manufacturer_type.manufacturer_type_id=manufacturer.manufacturer_type_id AND manufacturer.manufacturer_id=phone.manufacturer_id AND guide_type.guide_description= 'kit';

Rotorcraft Export Query:

select aircraft_name, cruise_speed, a_range, climb_rate, takeoff, landing, model,

actual_horsepower, low_end_horsepower, high_end_horsepower, fuel_burn, empty, gross, length, disk_span, disk_area, seat, cabin_width, landing_gear_description, Materials_description,

hours_to_build, built, plan_cost, kit_cost, build_cost_high, build_cost_low, quick_build_id, lsa_definition, website, phone_type

into outfile 'c://users/rachael/Documents/capstone/rotorcraft.csv' fields terminated by ',' enclosed by '"' escaped by '\\' lines terminated by '\n'

from aircraft, engine, engine_guide_info, physical_dimensions, guide_type, aircraft_type, disk, landing_gear, aircraft_materials, materials, cost, lsa, manufacturer_type, manufacturer, phone where aircraft.engine_id=engine.engine_id AND

engine.engine_guide_info_id=engine_guide_info.engine_guide_info_id AND aircraft.physical_dimensions_id=physical_dimensions.physical_dimensions_id AND aircraft.guide_id=guide_type.guide_id AND guide_type.aircraft_type_id=aircraft_type.aircraft_type_id AND aircraft_type.disk_id=disk.disk_id AND aircraft.landing_gear_id=landing_gear.landing_gear_id AND aircraft.aircraft_materials_id=aircraft_materials.aircraft_materials_id AND aircraft_materials.materials_id=materials.materials_id AND aircraft.cost_id=cost.cost_id AND aircraft.lsa_id=lsa.lsa_id AND aircraft.aircraft_id=manufacturer_type.aircraft_id AND manufacturer_type.manufacturer_type_id=manufacturer.manufacturer_type_id AND manufacturer.manufacturer_id=phone.manufacturer_id AND guide_type.guide_description = 'rotorcraft';

(29)

28 | P a g e

Designing the GUI

As mentioned earlier, we were not expected to implement a GUI, but were interested in learning from the process of working with a PHP developer and observing how the design of the database would be utilized. We began by using a new wire frame tool called Balsamiq Mockups (www.balsamiq.com) and creating an activity diagram for each of the buttons. When we met with the PHP developer, he requested that we instead number each of the buttons and provide him with a list of what each should do. Trusting the developer, we created four different spreadsheets to guide the developer: a button break down, a page break down, an input break down and a drop-down break drop-down. We also created an Activity Diagram to provide guidance regarding general navigation through the GUI design. Cross-referencing the screenshots of the wire-framed GUI and using the Activity diagram, the PHP developer was extremely impressed. He indicated that we could easily complete the project from the references we provided.

 A screen shot of the Activity diagram is below and the original Visio file is also being submitted with this document.

 The Wire Frame screen shots begin on the next page

 All of the break down spreadsheets follow the Wire Frame screen shots o Pink= Page

o Blue= Drop Down o Green= Input o Yellow= Button

It is also worth mentioning that we spoke with the PHP developer about SQL injections. Because there is no real way to prevent SQL injections in MySQL, he indicated that there are very few PHP developers that would not automatically guard against this.

(30)

29 | P a g e Wire Frame

(31)

30 | P a g e

(32)

31 | P a g e

(33)

(34)

(35)

(36)

(37)

(38)

(39)

(40)

(41)

(42)

(43)

(44)

(45)

(46)

(47)

(48)

(49)

(50)

(51)

(52)

51 | P a g e

Page Break Down Page

Number Page or Window Title Page Notes

Aircraft Search Used to search the aircraft table.

Aircraft Results Used to display the results of a search issued by the Aircraft _{Search Page (1).}

Aircraft Edit

Is essentially the same page as Aircraft Search (1), but instead of a search (58) button, there is an Edit Mode (60) button. Displays aircraft data that exist in the database. All fields are disabled until Edit Mode (60) is clicked. When Edit Mode is clicked, Aircraft Type (1) is enabled. Selecting ‘Kit’ or ‘Plans/Built’ grays out the Rotorcraft Disks box. Selecting ‘Rotorcraft’, grays out the Wing Design, Wing Info

and Sailplanes and Motorgliders boxes. If ‘No Engine’ (3) is selected, then the Engine drop down (16) is disabled. Aircraft ID is auto-generated and always disabled (except when searching).

Aircraft Add

Is essentially the same page as Aircraft Search (1) and Aircraft Edit (2), but instead of a search (58) button or an Edit Mode button (60), there is an Add (62) button. Aircraft ID is auto-generated and always disabled (except when searching).

Aircraft “What’s This” Explains that Aircraft Type (1) must be selected before _{adding any additional information.} Engine Search Used to search the Engine table.

Engine Results Used to display the results of a search issued by the Engine _{Search Page (6).}

Engine Edit

Is essentially the same page as ‘Engine Search’ (6), but instead of a ‘search’ (51) button, there is an ‘Edit Mode’ (56) button. Displays engine data that exist in the database. All fields are disabled until ‘Edit Mode’ (56) is clicked. When ‘Edit Mode’ is clicked, ‘Engine Type’ (45) is enabled. Both may be checked. If Aircraft is checked, then the Aircraft Drop Down (17) and corresponding Add button (70) are enabled. If Aircraft is selected, the required fields are Model Name (62), Manufacturer (15), and Horsepower Actual (51). If Engine is selected, Base Price (50) is

required, as well. If both are selected, Engine trumps Aircraft. Engine ID is auto-generated and always disabled (except when searching).

Engine Add

Is essentially the same page as Engine Search (6) and Engine Edit (7), but instead of a search (51) button or an Edit Mode button (56), there is an Add (57) button. Engine ID is auto-generated and always disabled (except when searching).

(53)

52 | P a g e

Engine “What’s This” Explains that Engine Type (45) must be selected before adding any additional information.

Manufacturer Search Used to search the Manufacturer table.

Manufacturer Results Used to display the results of a search issued by the _{Manufacturer Search Page (11).}

Manufacturer Edit

Is essentially the same page as ‘Manufacturer Search’ (11), but instead of a ‘search’ (43) button, there is an ‘Edit Mode’ (45) button. Displays manufacturer data that exists in the database. All fields are disabled until ‘Edit Mode’ (45) is clicked.

Manufacturer Add

Is essentially the same page as ‘Manufacturer Search’ (11) and Manufacturer Edit (13), but instead of a ‘search’ (43) button or an Edit Mode button (45), there is an ‘Add’ (48) button.

Manufacturer “What’s This?”

Explains why Manufacturer Type (63) must be selected before adding any additional information. Is brought up by clicking (47) on pages (11), (13) or (14).

Home

A landing spot after log in. Provides links to all Buyer’s guides exports (7, 8, 9, 10), and other general exports (11, 12, 13, 14, 15), as well as links to search pages (4, 5, 6), an opportunity to log out (72), and a link to the KitPlanes homepage (3).

Invalid Credentials

When an invalid email or password is entered into the log in page (31), this window comes up and an email is sent to the db admin.

Forgot Password

When a user clicks the Forgot Password button (2) on the Log In page (31), this window is brought up. It provides an input (79), for the user to submit (17) a request to the db admin to send a new password to the user (this process will be encrypted and the db admin will not be able to see the password).

Kit Buyer’s Guide Export

This window is brought up after the user clicks on Kit BG (7) on the home page (16). It offers exports of the Kit Query in the form of an Excel Spreadsheet (18), InDesign (19), PDF (20), CSV (21), Text (22).

Engine Buyer’s Guide Export

This window is brought up after the user clicks on Engine BG (10) on the home page (16). It offers exports of the Kit Query in the form of an Excel Spreadsheet (23), InDesign (24), PDF (25), CSV (26), Text (27).

Plans/Built Buyer’s Guide Export

This window is brought up after the user clicks on Plans BG (8) on the home page (16). It offers exports of the Kit Query in the form of an Excel Spreadsheet (28), InDesign (29), PDF (30), CSV (31), Text (32).

Rotorcraft Buyer’s Guide Export

This window is brought up after the user clicks on

Rotorcraft BG (9) on the home page (16). It offers exports of the Kit Query in the form of an Excel Spreadsheet (33), InDesign (34), PDF (35), CSV (36), Text (37).

(54)

53 | P a g e

Engine Export Export is in Excel format only. Brought up by clicking All _{Engines (11) on the home page (16).} Aircraft Export Export is in Excel format only. Brought up by clicking All _{Aircraft (12) on the home page (16).} Manufacturer Export Export is in Excel format only. Brought up by clicking All _{Manufacturers (13) on the home page (16).} Engine Manufacturer

Export

Export is in Excel format only. Brought up by clicking All Engine Manufacturers (14) on the home page (16). Aircraft Manufacturer

Export

Export is in Excel format only. Brought up by clicking All Aircraft Manufacturers (15) on the home page (16). Manufacturer Help A pop-up window that is brought up when the user clicks _{on (67).}

Engine Help A pop-up window that is brought up when the user clicks _{on (68).} Aircraft Help A pop-up window that is brought up when the user clicks _{on (69).}

Log In

A user ID and password must be entered into (77) and (78), respectively. Then the Log in button (1) can be pressed. If credentials are valid, then the home page comes up (16). If they are invalid, then the Invalid Credentials window (17) comes up and an email is sent to the db admin. There are also 2 links to the KitPlanes homepage (3- www.kitplanes.com), and a Forgot Password link (2) that brings up the Forgot Password window (18).

Are You Sure? (Log Out)

This window is brought up anytime the Log out button (72) is clicked in order to check if the user does indeed wish to log out. Yes (74) exits the user and returns them to the Log in Page (31). No (73) allows the user to stay on the same page.

Unused Unused

Additional Information Required (Aircraft)

After ‘Finished’ (46) or ‘Add’ (62) are clicked on Aircraft Edit (3) or Aircraft Add (4), respectively, this window will pop up if required fields are not completed.

Additional Information Required (Engine)

After ‘Finished’ (50) or ‘Add’ (57) are clicked on Engine Edit (8) or Engine Add (9), respectively, this window will pop up if required fields are not completed.

Additional Information Required

(Manufacturer)

After ‘Finished’ (74) or ‘Add’ (48) are clicked on Engine Edit (13) or Engine Add (14), respectively, this window will pop up if required fields are not completed.

(55)

54 | P a g e

Button Break Down

Button

Page

Button

Number

Button

Name

Description

Destination

Page

Log In

Logs user in using username/password. Allows access rights based on credentials. If successful, brings up the home page. (If not, brings up “Invalid Credentials”, 17)

Forgot

Password Brings up the “Forgot Password” window (16), (1), (2), (3), (4), (31), (6), (7), (8), (9), (11), (12), (13), (14) Kitplanes

Home links to www.kitplanes.com none

(16), (1), (2), (3), (4), (6), (8),

(9), (11), (13), (14)

Aircraft Search Goes to Aircraft Search Page (16), (1), (3),

(4), (6), (7), (8), (9), (11), (13),

(14)

Engine Search Goes to Engine Search Page (16), (1), (3),

(4), (6), (8), (9), (11), (12), (13),

(14)

Manufacturer

Search Goes to Manufacturer Search Page Kit BG Kit Buyer’s Guide Export window Plans BG Plans Buyers Guide Export window Rotorcraft BG Rotorcraft Buyer’s Guide Export window

Engine BG Engine Buyer’s Guide Export window All Engines Are you sure? Engine Export Window

(Excel Export)

All Aircraft Are you sure? Aircraft Export Window _{(Excel Export)} All

Manufacturers

Are you sure? Manufacturer Export Window (Excel Export)

Engine Manufacturers

Are you sure? Engine Manufacturer Export Window (Excel Export)

Aircraft Manufacturers

Are you sure? Aircraft Manufacturer Export Window (Excel Export)

(56)

55 | P a g e

Invalid Credentials

Sends an alert to the DBA that a failed attempt to log in was made

Submit Sends an email to the DBA with the user id _{of the person who forgot their password} Excel

Spreadsheet (Kit)

Generates an export of the Kit Query formatted for Excel. No images, obviously. InDesign (Kit) Generates an export of the Kit Query _{formatted for InDesign. Images included.}

PDF (Kit) Generates an export of the Kit Query formatted as a PDF. No images, obviously. CSV (Kit)

Generates an export of the Kit Query formatted as a CSV file. No images, obviously.

Text (Kit)

Generates an export of the Kit Query formatted as a Text file. No images, obviously.

Excel Spreadsheet

(Engine)

Generates an export of the Engine Query

formatted for Excel. No images, obviously. none InDesign

(Engine)

Generates an export of the Engine Query

formatted for InDesign. Images included. None PDF (Engine) Generates an export of the Engine Query

formatted as a PDF. No images, obviously. None CSV (Engine)

Generates an export of the Engine Query formatted as a CSV file. No images, obviously.

None

Text (Engine)

Generates an export of the Engine Query formatted as a Text file. No images, obviously.

None Excel

Spreadsheet (Plans/Built)

Generates an export of the Plans/Built Query formatted for Excel. No images, obviously.

None InDesign

(Plans/Built)

Generates an export of the Plans/Built Query formatted for InDesign. Images included.

None PDF

(Plans/Built)

Generates an export of the Plans/Built Query formatted as a PDF. No images, obviously.

None CSV

(Plans/Built)

Generates an export of the Plans/Built Query formatted as a CSV file. No images, obviously.

None Text

(Plans/Built)

Generates an export of the Plans/Built Query formatted as a Text file. No images, obviously.

(57)

56 | P a g e

Excel Spreadsheet (Rotorcraft)

Generates an export of the Rotorcraft Query

formatted for Excel. No images, obviously. None InDesign

(Rotorcraft)

formatted for InDesign. Images included. none PDF

(Rotorcraft)

formatted as a PDF. No images, obviously. None CSV

(Rotorcraft)

Generates an export of the Rotorcraft Query formatted as a CSV file. No images,

obviously.

None Text

(Rotorcraft)

Generates an export of the Rotorcraft Query formatted as a Text file. No images,

obviously.

None Engine Export Exports all Engines records in the db as an _{Excel Spreadsheet.} None Aircraft Export Exports all Aircraft records in the db as an _{Excel Spreadsheet.} None

Manufacturer Export

Exports all Manufacturer records in the db

as an Excel Spreadsheet. None

Engine Manufacturer

Export

Exports all Manufacturers of Engines in the

db as an Excel Spreadsheet. None

Aircraft Manufacturer

Export

Exports all Manufacturers of Aircraft in the

db as an Excel Spreadsheet. None

Search (Manufacturer)

Searches the Manufacturer table for a match to the entered search criteria. Brings up search results on the Manufacturer results page (12).

(2), (7), (12) Export Generates Excel export of records on the

page none

Edit Mode (Manufacturer)

Allows information to be entered on the Manufacturer Edit page. First only enables Manufacturer Type (63). Once a selection has been made, then the rest of the form is enabled.

none

Finished (Aircraft)

Saves the information on the page and updates the “Last Edited By” field (40) and (42). If required information is not filled in, (35) pops up alerting them to which are fields are required. If all required

information is present, all fields are again grayed out until Edit Mode (60) is pressed again.

(3) or (35)

(58)

57 | P a g e

Add (manufacturer)

Adds a new Manufacturer record to the Manufacturer table. Checks to make sure required fields are completed. If all required fields are filled in, clicking this button will gray everything out and it will shift into the edit page (13). If required fields are missing, the Additional Information Required window (37) will pop up and the record will not be added until those fields are completed.

(13) or (37)

Add New Manufacturer

Links to the Add Page (14) for a new Manufacturer record

Finished (Engine)

Saves the information on the page and updates the “Last Edited By” field (40) and (42). If required information is not filled in, (36) pops up alerting them to which are fields are required. If all required

information is present, all fields are again grayed out until Edit Mode (56) is pressed again.

(8) or (36)

Search (Engine)

Searches the Engine table for a match to the entered search criteria. Brings up search results on the Engine Results page (7). Add New

Engine

Links to the Add Page (9) for a new Engine record

(2), (7) Engine Name Links to the Edit page for that Engine record (8)

(2), (7), (12) Manufacturer

Name

Links to the Edit page for that Manufacturer record (13)

(2), (7) Aircraft Name Links to the Edit page for that Aircraft record (3)

Edit Mode (Engine)

First enables the Engine Type choice (45). After one is selected, the rest of the form can be used. If Aircraft is selected, the required fields are Model (62) Name, Manufacturer (15), and Horsepower Actual (51). If Engine is selected, Base Price (50) is required, as well. If both are selected, Engine trumps Aircraft.

none

Add (Engine)

Adds a new Engine record to the Engine table. Checks to make sure required fields are completed. If all required fields are filled in, clicking this button will gray everything out and it will shift into the edit page (8). If required fields are missing, the Additional Information Required window (36) will pop up and the record will not be

KitPlanes Gets a New Database