Study the ‘Ski-Italia Expeditions’ case study carefully and then submit the following:
(1) An Entity-Relationship model, including attribute list.
(2) Normalise (to third normal form) the Course-Details form and the Customer Booking form separately.

(3) Implement and submit adequate documentation for the following:
(a) Create and populate just those database tables which correspond to the normalised entities found in part (2). Submit Oracle scripts for each table, showing its description and its contents.
(b) Implement the following functionalities:
F1 – create a new customer and enrol him on a course F2 – create a family of four and enrol them all on the same course F3 – ability to change course information F4 – ability to remove retiring staff from the system (c) Develop the following queries:
Q1 – for a particular course, a list of all students currently enrolled,        followed by a count of these students. Q2 – for a member of staff, list his schedule. Q3 – for a particular customer, show his booking details.(d) Develop two additional queries (Q4 and Q5) which you judge will be useful in this context. Give a clear textual description of what each of your two queries is supposed to show. Use your judgement as to what information the queries should contain and how they should be formatted. (60%) (4) Write a business report for Susan Holder which includes:
• An explanation of your system;• The direct benefits to Susan of your database;• Future possible enhancements to the database system, with timescales;• Your recommendations on how her business can make effective use of management information. (20%)

Ski-Italia Expeditions – Case Study
Ski-Italia Expeditions is a fifteen-year-old company that offers cross-country skiing instruction and sponsors cross-country tours in the Italian Alps Mountain Range. It also sponsors back country expeditions in mountain ranges around the world. Ski-Italia is owned and managed by Susan Holder.
Ski-Italia’s business is highly seasonal. The first courses and tours begin in mid-October and the season is over by mid-April. During this period of time, Ski-Italia conducts twelve beginners ski courses, six intermediate ski courses, and one advanced back country expedition. Also, Ski-Italia operates eight tours over the winter.
Susan likes to keep the staff very lean. She employs a secretary/office manager, cook/maintenance personnel, and a number of ski instructors and tour guides. The instructors and guides are paid a standard daily rate for the days they work. The instructors are all excellent skiers and can teach any of the courses. The tour guides trade off directing cross-country tours and pulling sledges that carry equipment and supplies for the courses and the tours. There are also a number of apprentice ski instructors who are unpaid but who receive room and board when they are helping with courses and tours. The cook/maintenance staff works in the base camp. In Susan’s simple filing system, she has a sheet for each employee with their name, national insurance number, address, postcode, and phone number. During the season she uses three-by-five-inch cards to keep track of which instructor, apprentice, or tour guide is on which job. On the card she writes the person’s name, the number of the course or tour he or she is working, and the date. She revises the cards when people change jobs.

Beginner’s Ski Courses
The beginner’s ski course consists of five days of instruction followed by a four-day trip skiing hut-to-hut in the Italian Alps. The students live and eat at the lodge during the instructional phase. On the touring portion of the course, students stay in huts along groomed trails; food is provided and prepared by the Ski-Italia staff.Each introductory course is limited to ten students. Except in unusual circumstances, such as not having enough snow to ski on, all classes are within 90 percent of capacity.
Tour guides pull food and supplies to the huts on two sledges. The tour guides help the instructors set up the huts as well as serve as cooks. The introductory courses are staffed by two paid instructors, one unpaid apprentice instructor, and two tour guides. Food and lodging are provided to all instructors, tour guides, and apprentices for both phases of the course.

Intermediate Ski Course
The intermediate courses consist of five days of skiing on different slopes of two mountains. Students in these courses live in a rustic resort located near the mountains. All meals are provided by the resort. Food and lodging are provided by the resort to Ski-Italia on a fixed price-per-person contract basis. Ski-Italia includes the cost of food and lodging in the package price it offers its customers.
Intermediate courses are limited to eight students and are staffed by one paid, instructor and one unpaid apprentice instructor. Again, both paid and unpaid instructors are provided lodging and food during the course.

Back country Expeditions
The back country expedition consists of a group of eight to ten advanced/expert skiers and two instructors. The duration of the expedition varies from year to year but generally involves a two-to-three week trip down a mountain on another continent. Food is provided for the planned number of days on the mountain. Hotels and transportation in the destination country are normally included in the course cost, although the specific policy depends on the country and varies from year to year. All travel, hotel, and food costs for the two instructors are paid by Ski-Italia. Susan tries to plan these trips so that Ski-Italia nets about £12,500 on the trip after all direct expenses.
Ski-Italia rents all the necessary skiing equipment for introductory students. Intermediate and advanced students are expected to provide their own equipment. Ski-Italia will rent skis, boots, and poles to intermediate and advanced students, if necessary. Students are required to provide all personal clothing and camping equipment, including sleeping bags, tents, mattresses, and so on.

Cross-Country Tours
In addition to the skiing courses and expeditions, Ski-Italia operates eight five-day touring trips in the Italian Alps. The tours are a combination of trial and back country skiing. The first and the last nights are spent in huts and the middle two nights are spent snow camping. Each trip consists of six customers, two paid tour guides, and one apprentice tour guide who helps to set up camp and cook meals.Although the tours and skiing courses are separately operated, they are scheduled to be on the mountain at the same time so that, in an emergency, the ski and tour staff personnel could support one another. To facilitate co-operation between these trips, tour guides occasionally serve as apprentice ski instructors and ski instructors occasionally operate as tour guides.Ski-Italia rents all necessary touring equipment to the customers. As with the ski classes, personal camping equipment and clothing is provided by customers.

The Ski Store
Ski-Italia operates a small skiing store in a shed to the lodge. It is a small-scale operation that grew out of the need to provide inexpensive but necessary items (sunscreen, retainers for eyeglasses, hats, gloves, etc.) to participants during the courses. Invariably, someone would forget such an item and ask one of the staff members to pick it up during a grocery shopping trip in town (twenty-seven miles away). Ski-Italia began offering such items for sale as a way of dealing with this irritating but real need.
Several years ago, a salesperson for one of the suppliers of the skiing equipment used in the introductory class introduced the idea to Susan that Ski-Italia begin to sell skis, boots, poles, jackets, sweaters, and other paraphernalia to attendees. Ski-Italia was buying such equipment at wholesales prices anyway, and the supplier suggested that Ski-Italia become a full-scale retail outlet.
Over the years, the operation has grown into a small business. Susan sells equipment at an average of 25 percent mark-up. She estimates she sells about £905,000 (retail prices) worth of equipment on direct expenses of about £415,000. Often Ski-Italia is not billed for the merchandise for a month to six weeks after it is received. In this time, Susan hopes to sell a substantial portion of it. She rolls inventory that is unsold by the season’s end into the equipment inventory for next year’s beginner’s classes. Unfortunately, pilferage is a problem. She estimates that she loses 15 percent of her inventory to theft. One year she even lost a pair of skis to theft!
As with the rest of this business, record keeping is minimal. She verifies deliveries against purchase orders and checks invoices against sales orders. The office manager prepares checks to vendors. He keeps an informal inventory of items on hand and sold. Susan would like to know which customer has bought what type of equipment. She’s positive the retail business could be more profitable with some targeted marketing.
Overall, Susan operates her business very informally. She has been in business for fifteen years and knows about what to spend for each course on equipment, supplies, food, and other expenses. She also has an intuitive sense of her personnel costs. At the end of the year, she totals her revenue, subtracts expenses, and determines what she had earned. She hopes to clear around £1890,000 before taxes for the year (this includes her salary). In the past five years, her actual profit has ranged from a low of £513,700 to a high of £907,500.
Susan is not at all satisfied with this arrangement. She knows that her record keeping is minimal and that she runs her business haphazardly. She senses there are opportunities for increasing her profit margin, but she never has time to develop a system that would help her do this. By the end of the season, she is usually so exhausted that she takes six weeks off. Then, she repairs Ski-Italia facilities and begins marketing promotion for the coming year. There is never time to improve her record keeping, marketing, and financial management systems.

Susan is satisfied with the effectiveness of marketing for the introductory ski courses and the tour trips. Each season she is able to fill almost all of the slots available, and she often has a waiting list. Ski-Italia cannot expand the size or number of courses and trips, because the company cannot obtain more permits from the Forest Service to put more people on the mountain.
Susan is not all satisfied with the marketing for the intermediate and expedition courses, however. She believes that she has a marketing gold mine in the customers who have completed the basic course. When she has the time to call one of these customers, she is almost always able to sell them an intermediate course. It’s as if the customers are waiting to be called, to be reminded of the good experience they had, and to enrol in another class.
In spite of this opportunity, Ski-Italia does almost nothing with the list of prior students. In some years, she is able to send out a few Christmas cards, but this is done informally and without regard to any marketing strategy.
Although Susan tends to deal with paperwork in a haphazard manner, she finds that a form (see Form 1) which shows details of each course’s bookings is particularly useful. Prior to the course taking place, the form is used to append bookings as they are made. When the instructors prepare for the course, they are given this form for reference.The Customer Booking form (see Form 2) is also useful for making party or individual bookings.