Create Database and Queries for Ski Instructors

Create Database and Queries for Ski Instructors

Study the ‘Ski-Fun Excursions’ case study carefully and then submit the following:

(1) An Entity-Relationship model, including attribute list.

You will have to make judgements as to what level of detail is appropriate. 

(2) Normalise (to third normal form) the Course-Details form and the Customer Booking form separately. Show all the steps clearly. Now consolidate the entities by eliminating redundancies and then produce a final list of entities. 

(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. 

(4) Write a business report for Janice Smith which includes:

  • An explanation of your system;
  • The direct benefits to Janice 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. 

Ski-Fun Excursions – Case Study

Ski-Fun Excursions is a fifteen-year-old company that offers cross-country skiing instruction and sponsors cross-country tours in the Swedish Alps Mountain Range. It also sponsors back country Excursions in mountain ranges around the world. Ski-Fun is owned and managed by Janice Smith.

Ski-Fun’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-Fun conducts twelve beginners ski courses, six intermediate ski courses, and one advanced back country Excursion. Also, Ski-Fun operates eight tours over the winter.

Janice 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 Janice’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 Swedish 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-Fun 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. Ski-Fun

Excursions – Case Study

Ski-Fun Excursions is a fifteen-year-old company that offers cross-country skiing instruction and sponsors cross-country tours in the Swedish Alps Mountain Range. It also sponsors back country Excursions in mountain ranges around the world. Ski-Fun is owned and managed by Janice Smith.

Ski-Fun’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-Fun conducts twelve beginners ski courses, six intermediate ski courses, and one advanced back country Excursion. Also, Ski-Fun operates eight tours over the winter.

Janice 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 Janice’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 Swedish 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-Fun 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-Fun on a fixed price-per-person contract basis. Ski-Fun 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 Excursions

The back country Excursion consists of a group of eight to ten advanced/expert skiers and two instructors. The duration of the Excursion 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-Fun. Janice tries to plan these trips so that Ski-Fun nets about £12,500 on the trip after all direct expenses.

Ski-Fun rents all the necessary skiing equipment for introductory students. Intermediate and advanced students are expected to provide their own equipment. Ski-Fun 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 Excursions, Ski-Fun operates eight five-day touring trips in the Swedish 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-Fun 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-Fun 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-Fun 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 Janice that Ski-Fun begin to sell skis, boots, poles, jackets, sweaters, and other paraphernalia to attendees. Ski-Fun was buying such equipment at wholesales prices anyway, and the supplier suggested that Ski-Fun become a full-scale retail outlet.

Over the years, the operation has grown into a small business. Janice 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-Fun is not billed for the merchandise for a month to six weeks after it is received. In this time, Janice 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 officemanager prepares checks to vendors. He keeps an informal inventory of items on hand and sold. Janice 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, Janice 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.

Janice 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-Fun facilities and begins marketing promotion for the coming year. There is never time to improve her record keeping, marketing, and financial management systems.

Janice 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-Fun 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.

Janice is not all satisfied with the marketing for the intermediate and Excursion 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-Fun 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 Janice 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.

Solution

1) An Entity-Relationship model, including attribute list. You will have to make judgements as to what level of detail is appropriate.

Customer       

C_Sno C_FName C_LName C_ph.Number C_Proficiency S_Sno(FK)

Course

S_Sno Course Type C_Name Beginning_date End_date Staff#(Fk)
   

Employee

Staff# E_Name E_JobType E_ph.Number E_Address E_Postalcode S_Sno(FK)

Booking

Reference_number Date Payment C_Sno(F_key)

Store

Id Address

Items

Item_Id Description Brand price size

CheckOut

CheckOut_id Storeid Customer_id date SubTotal
         

(2) Normalise (to third normal form) the Course-Details form and the Customer Booking form separately. Show all the steps clearly. Now consolidate the entities by eliminating redundancies and then produce a final list of entities.

Course details form contains informations from above four tables(customer, employee, Booking, Course) we need make this relations into third normal form which is

1)  A table is in 2nd normal form.

2) No Non-prime attribute should be in transitively dependent on candidate key

For example if we took the

1) R2(B,F)

             B → F (this is the relation)

Candidate key is  B.Non prime attribute is F.

From the relation we can say that this is in 3NF.

2) R1(A,B,C,D,E)

A  →  BCDE, BC →  ADE, D →  E

In this three we can see that D → E are two non-prime attributes which having dependency we will make this relation in to 2 relations then we make into third normal form which will make database consistent with relation to each column.

If we look at the Course-Information Form we can see the 3 table which are Course, Employee and Customer. These 3 are violates third normal form to make this into third normal form firstly we look at the Course table

Course(Course#,Course type, Beginning date, End date) this violates 3NF because Course Type  Beginning date, End date. To make this into third normal form we will break the Course  into 3 relations.

course# Course Type Beginning Date End Date
1278P Intermediate 12-JAN-2016 17-JAN-2016
1279P Intermediate 20-JAN-2018 28-JAN-2018
course# Beginning Date End Date
1278P 12-JAN-2016 17-JAN-2016
1279P 20-JAN-2018 28-JAN-2018

 

Course# Course Type
1278P Intermediate
1279P Intermediate

 

Course Type Beginning Date End Date
Intermediate 12-JAN-2016 17-JAN-2016
Intermediate 20-JAN-2018 28-JAN-2018

Customer(Booking#,Name,Phone,Proficiency,Payment) with  primary key as  Booking# which violates the third normal form.

Because  Name, Phone → Proficiency.

Name, Phone and Proficiency are three non-prime attributes but we can derive trasivity dependent on this three. Make this table as third normal form we will split the relation in three table with consistency.

R1(Booking#,Name,Phone,Payment),

R2(Booking#, Proficiency,Payment),

R3(Name,Phone,Proficiency).

Booking# Name Phone Proficiency Payment
R170 Jack Douglas 0208 441-6503 B Y
R170 Susie Douglas 0208 441-6503 I Y
T212 Kim Beverly 0207 066-9328 I N
Booking# Name Phone Payment
R170 Jack Douglas 0208 441-6503 Y
R170 Susie Douglas 0208 441-6503 Y
T212 Kim Beverly 0207 066-9328 N
Booking# Proficiency Payment
R170 B Y
R170 I Y
T212 I N
Name Phone Proficiency
Jack Douglas 0208 441-6503 B
Susie Douglas 0208 441-6503 I
Kim Beverly 0207 066-9328 I

Staff(Staff#,Name, Job type, Contact-phone) with primary key as Staff# which violates the 3NF.

Name, Contact-Phone and Job Type are three non-prime attributes but we can derive trasivity dependent on this three. Make this table as third normal form we will split the relation in three table with consistency.

R1(Staff#,Name,Contact-phone),

R2(Staff#,Job type),

R3(Name, Job type, Contact-phone).

Staff# Name Job Type Contact-Phone
709 Carlo Fette I 33-754-190
324 Luigi Marc G 33-609-233
Staff# Name Contact-Phone
709 Carlo Fette 33-754-190
324 Luigi Marc 33-609-233
Staff# Job Type
709 I
324 G
Name Contact-Phone Job Type
Carlo Fette 33-754-190 I
Luigi Marc 33-609-233 G

If we look at the Customer-Booking Form we can see the 4 table which are Booking, Employee Customer and Party. Only Course violate the 3NF which having the attributes as Course(Course#,Course type, Beginning date, End date)

This is also made into 3 relations as showed in the above question. Which are

R1(Course#, Beginning Date, End Date)

R2(Course#, Course Type)

R3(Course Type, Beginning Date, End Date)

Which will make this table into 3NF.

3) Implement and submit adequate documentation for the following:

1)Table : Customer

CREATE TABLE Customer

( C_Sno number(10) NOT NULL,

C_FNamevarchar2(50) NOT NULL,

C_LNamevarchar2(50) NOT NULL,

C_Proficiencyvarchar2(50) ,

C_ph.Numbernumber(10),

CONSTRAINT employees_pk PRIMARY KEY(C_Sno)); 

2) Table : Employee

CREATE TABLE Employees

( Staff# number(10) NOT NULL,

E_Namevarchar2(50) NOT NULL,

E_JobTypevarchar(20) NOT NULL,

E_ph.Numbernumber(10),

E_Addressvarchar(20) NOT NULL,

E_Postalcodenumber(6),

CONSTRAINT employees_pk PRIMARY KEY (staff#),

CONSTRAINT fk_Course

FOREIGN KEY (S_Sno(FK))

REFERENCES Course(S_Sno));

3) Table : Course

CREATE TABLE Course

( S_Sno number(10) NOT NULL,

C_Namevarchar2(50) NOT NULL,

CourseTypevarchar(20) NOT NULL,

Beginning_datedd-mm-yyyy,

End_datedd-mm-yyyy,

CONSTRAINT employees_pk PRIMARY KEY (S_Sno),

CONSTRAINT fk_Course

FOREIGN KEY (C_Sno(FK))

REFERENCES Customer(C_Sno));

4)Table : Booking

CREATE TABLE Booking

( Reference_number number(10) NOT NULL,

Date varchar2(50) NOT NULL,

Payment varchar2(50) NOT NULL,

CONSTRAINT booking_pk PRIMARY KEY(Reference_number));

CONSTRAINT fk_Booking

FOREIGN KEY (C_Sno(F_Kkey)),

REFERENCES Customer(S_Sno));  

  1. b) Implement the following functionalities

F1 – create a new customer and enrol him on a course.

Customer(C_Sno, C_Name,C_JobType,C_ph.Number,C_Proficiency)

C_No→  C_FName, C_LName,C_JobType,C_ph.Number,C_Proficiency

C_Name ,C_ph.Number → C_Proficiency

S_SnoC_Name, CourseType, Beginning_date, End_date

S_Sno → C_Sno

Explanation :-

Functional dependencies should properties follow main there properties Reflexivity , Augmentation and Transitivity above 5 function follow this conditions in that C_no is primary key for the Customer and S_Sno is the primary key for the Course. To create a new customer and enrol him on a course we maintain the functional dependencies S_Sno → C_Sno.

F2 – create a family of four and enrol them all on the same course.

Insert to Customer (C_Sno, C_FName,C_LName,C_ph.Number,C_Proficiency)                          values (1,Alex,Hales,1234567890,Student);

Insert to Customer (C_Sno, C_FName,C_LName,C_ph.Number,C_Proficiency)                          values (2,root,Hales,1234667891,Software);

Insert to Customer (C_Sno, C_FName,C_LName,C_ph.Number,C_Proficiency)                          values (3,morgan,Hales,3434567890,Student);

Insert to Customer (C_Sno, C_FName,C_LName,C_ph.Number,C_Proficiency)                          values (4,billy,Hales,12345675690,Student);

Customer

C_Sno C_FName C_ph.Number C_LName C_Proficiency
1 Alex 1234567890 Hales Student
2 morgan 1234667891 Hales Student
3 Root 1234667891 Hales Software
4 billy 12345675690 Hales Student

Course

S_Sno C_Name CourseType Beginning_date End_date
1234 ski-fun intermediate 15-08-2017 27-08-2017

If take the customer with the table with all in same family will have all the Last name in my case

SELECT C_LName FROM Customer

SET S_Sno = ‘1234’

WHERE C_LName = ‘Hales’; 

F3 – ability to change course information

If any student want change the course information we will update the Course id corresponding to the Customer table this can be viewed in the Customer table.

S_Sn0→  Course Type, C_Name, Beginning Date, End Date

Staff# → E_Name, E_JobType, E_ph.Number, E_Address, E_Postalcode

C_Sno → C_FName,  C_LName, C_ph.Number,  C_Proficiency,S_Sno

UPDATE customers

SET S_Sno = ‘1234’

WHERE customer_id = 2;

F4 – ability to remove retiring staff from the system

Let make retiring age is 60. Our system will remove all the employees whose having age more than 60.  This cannot be done in our system because we are not given the age our the employees which are not participating in the any event but if the given we just remove the corresponding row in our employee table whose having the age more than 60 

  1. c) Develop the following queries

Q1) for a particular course, a list of all students currently enrolled,followed by a count of these students.

SELECT * FROM Customer AS C

INNER JOIN Course AS S

ONC.C_Sno = S.C_Sno

GROUP BYC.C_Sno, C.C_name HAVING COUNT(*) = (SELECT COUNT(*) FROM Courses’

Q2 – for a member of staff, list his schedule.

SELECT * FROM Employee AS E

INNER JOIN Course AS C

ONE.S_Sno = C.S_Sno

GROUP BY E.S_Sno, E.E_name ;

Q3 – for a particular customer, show his booking details.

SELECTC.C_Sno, C.C_Name,

FROM customer C

LEFT JOIN booking B ONC.C_Sno = B.C_Sno

GROUP BYC.C_Sno;

Q4 – Find the total number of customer in Intermediate Course?

SELECT count(*)

FROM Customers`

WHERE Coursetype=’intermediate’;

Q5 – A Customer cannot add more than two courses at a time?

This query cannot be done because integrity constraints and assertions only affect the content of the table, not how the content is manipulated.

4) Business Report

   Introduction: 

The purpose of  this report is to give Ski-Fun Excursions idea how the database maintain will benefit over with paperwork.

Procedure

Make the table according to the ER diagram. Main tables are Customer , Employee, Item, Payment Information , Booking and Course which give the clear idea about how the database will work. Designing , Implementation and maintenance of the Database will cost us very less and much efficient.

The table look like

The attributes in the table are followed by the explanation given in the third normal form which will make our system consistent and error-free.

The relation between the customer, employee, Booking and course will provide clear idea about how many student are enrolled in the class. It will give information about which instructor is assigned to which course following same way in the tour guide no need maintain cards corresponding to who is available for the course we can just search course name it will provide all information about the number of students enrolled, instructor for particular course and booking details no need to cross check the information.

Insert ,  Update and Remove of the data is very easy no paperwork is required

Just one command is enough but some basic idea is needed that can be done by technical team no need to worry.

course# Course Type Beginning Date End Date
1278P Intermediate 12-JAN-2016 17-JAN-2016
1279P Intermediate 20-JAN-2018 28-JAN-2018
Staff# Name Job Type Contact-Phone
709 Carlo Fette I 33-754-190
324 Luigi Marc G 33-609-233
Booking# Name Phone Payment
R170 Jack Douglas 0208 441-6503 Y
R170 Susie Douglas 0208 441-6503 Y
T212 Kim Beverly 0207 066-9328 N

Findings:

We made a sample database with oracle as our workbench and analysed the data which given accurate results the sample queries and functionalities are

Functional dependencies:

create a new customer and enrol him on a course.

create a family of four and enrol them all on the same course.

ability to change course information.

ability to remove retiring staff from the system.

Queries :

  1. for a particular course, a list of all students currently enrolled, followed by a count of these students.
  2. for a member of staff, list his schedule.
  3. for a particular customer, show his booking details.
  4. Find the total number of customer in Intermediate Course.

Our system given very accurate result for this quires store and Booking of the course are done offline need some some improvement in store related queries. To make this work just need deploy this system into server that more than 2 days work with our technical team and maintenance also done by our technical team.

Conclusion

This project eliminates the heavy paperwork and gives good and efficient database system .now onwards no need of calling every client and ask for enrolling course just need one post with good website. This benefit company with high profits.