Your consulting firm has been hired by the Geneva-on-the-Lake Yacht Brokerage (GLYB) to automate their business. GLYB specializes in sailing yachts of all sizes, new and used, and is one of the largest yacht brokers located on the Great Lakes. Each year GLYB buys and sells approximately 50 yachts. Boat owners who do not sail their yachts south for the winter have to pull them out of the water since coastal areas of Lake Erie can freeze for up to 2 months out of the year. The boating season for most marinas and yacht clubs on Lake Erie runs from May through October. Consequently, GLYB also offers winter storage, dry docking and de-masting facilities for 25 boats. Since sailboats less than 27' in length can be trailered, GLYB limits its facilities to boats 27' and over. The largest boat they can accommodate is 50'.

The owner of GLYB, Capt. Horatio Hornblower, is totally unfamiliar with database design and what computers can do for his business. Some brokerages and most yacht manufacturers already have a presence on the world wide web. Not to be outdone, Capt. Hornblower wants you to find out what the competition is up to and build two applications, both of which use the same database, that will put the competition to shame. The first allows potential customers to browse through GLYB's inventory of used and new sailboats and lets them leave their name, address, email address and phone numbers, if they wish, along with information on the specific kind of boat they are looking for. The second application keeps track of GLYB's inventory of yachts, as well as historical information on past inventory and owners, and manages the dry docking facilities.

Based on what he knows about the business, Capt. Hornblower gives you the following specifics on what the applications and the database are supposed to do (at a minimum):

1. Inventory/Dry Docking Facilities Management System

• present the user with a button based menu for system navigation to include:
• enter a new yacht, including all pertinent information (what is pertinent you need to find out on the web and by asking Capt. Hornblower (aka "Olli")), as well as any picture that is available
• have the ability to add any non-standard equipment to a yacht, to be displayed/entered in a separate window (i.e., block); this is a part of the previous menu item (hint: use a master/detail relation; to find out what is non-standard equipment browse the web and check to see what most new yachts have as standard features, vs. what used yachts have; also check out sailing magazines); this also means that you have to create the ability to query for a specific yacht that's in inventory
• check on everyone that has left their name and address in the customer application
• mark a yacht as sold (making it ineligible for browsing by potential customers) and attach a customer to that yacht
• enter a customer
• attach documents to a yacht (these can be word documents, excel spreadsheets, or scanned documents, i.e., OLE objects); these attached documents cannot be viewed by customers browsing the inventory
• manage dry docking facilities (to include adding a boat, checking whether there is room for a specific boat in that year (if a boat cannot be added for some reason, the record needs to be deleted; hint: maybe you want to check the length of the boat first before you let the user anything else), calculating total cost of storage, haul-out fee, de-masting fee (optional), haul-out date, put-in date, total cost, etc.)
• generate a report of all dry docking customers for a specific season, along with the specifics on their boats
• generate form letters for every customer who has left their name and address using the customer browsing application (after form letter generation you should mark that person's file to indicate that the letter has been sent in order to avoid duplicate letters)
• generate a report of all past customers that have bought a boat
• view a specific yacht with the option to print out a report on it
• when a yacht gets sold, generate a report of all items associated with that yacht (hint: this would be done with a trigger and is a part of another menu item)

1. Customer Browsing Application

This application is much simpler than the above one. All this form has to do is:

• browse all available yachts that are in the inventory
• print out a report on a yacht (this should be a button in the previous item)
• allow a user to leave his/her name, address, email, and phone number, as well as a form that indicates specific interests

1. Data Items

Based on what he knows, Capt. Hornblower has determined that the following information needs to be entered for each entity (note: this is incomplete; you need to find out additional data by doing your own research):

• Hull number
• Make
• Model
• Year
• Rigging type (find out what different types of sailboats there are; hint: sloop, ketch and cutter rig are examples)
• Length
• Displacement (in thousands of pounds)
• Sail area
• Keel type (fin, bulb and wing keels are examples)
• Engine type (Diesel/Gas, 1/2/3/4/5/6/7/8 Cylinder, Make, Model) and horsepower
• Engine serial number
• Hull speed
• Condition (on a scale of 1-10)
• Asking Price
• Sold Price
• Owner name and address
• Name of the boat
• A text field touting the features of the boat
• Proof of insurance supplied (this is a state mandate)
• Registration number (if a documented boat; undocumented boats don't need one)

• name
• address (hint: an address consists of more than just one field)
• phone
• email address
• make, type, model, length range, price range, year range, etc. of boat interested in (note: these fields are all optional)
• if interested in a specific yacht, indicate which one

In addition to the above entities, you will need a number of code tables (such as make, model, type, etc.) and, depending on how you write the application, some tables that are used for temporary storage (to be discussed in class).

1. Specifics to GLYB

• Total number of spaces available: 25
• 27' to 30' spaces: 10
• 31' to 36' spaces: 9
• 37' to 42' spaces: 4
• 43' to 50' spaces: 2

• Haul-out fee: $250 (regardless of the size of boat)
• De-masting fee (includes re-masting and mast storage): $450
• Dry dock fee: $25 per linear foot

Naturally, Capt. Hornblower expects you to develop a professional looking application (complete with company logo - the design is up to you) that will rival anything else out on the web. You, as a development team within your company, have complete freedom how to design your application and database. However, this also implies that you will need to do some research on sailing. Here are some suggestions in that respect:

• buy sailing magazines (Sail and Ocean Navigator are two good ones)
• search the web for anything to do with sailing
• visit on-line sail boat brokerages

How to do certain things will be discussed in class, along with examples of program units, triggers and queries that will accomplish some of the above tasks.