اريد بحث عن مشروع تنظيم الرحلات

انقذوني بالسرعة الفائقة وهذا وصف المشروع:
you are required to:

 Design, implement and tune a solution to a business problem
 The solution must be implemented in SQL+. Implementation in Access, MYSQL, SQLServer or any other DBMS will not be accepted.
 If a student hands in a report, but does not demonstrate, the assignment will receive zero marks. If a student demonstrates but does not hand in a report, the assignment will receive zero marks.

Case Study

BEACONSIDE SCHOOL TOURS LTD (BST) is a rapidly developing tour company which specialises in organising school tours for secondary school pupils. The company now wishes to expand and provide tours abroad. In the first year, tours will be organised to destinations in France, Germany and Switzerland. You are required to design a database which will support the company in the first year of operations and which will be capable of expansion if the tours abroad prove successful. You are not required to deal with any of the company’s other operations and you can assume there is no need to consider legacy or network issues.

All tours will be escorted by a courier who will speak both English and any other appropriate language(s). The database must include information about the countries to be visited, including information about currency, passport regulations, and the main language spoken. Where more than one language is spoken in a country (e.g. in Switzerland, the most commonly used languages are German, French and Italian), you are required to record the 2 main languages spoken.

There is no minimum or maximum group number. You should assume that that there will be different numbers of students on different tours and design your test data accordingly. All students are charged accommodation at the student rate. It is not necessary to record next of kin details for the students, as the school is assumed to have this information, but the company wishes to record date of birth and home address. Due to problems in the past, the company creates a complete new record for every student who takes part in a tour. You can assume that you do not need to track whether a student has previously been on a tour with the company.

There is no minimum or maximum duration for a tour but you can assume that tours are never less than 3 days duration and never longer than 18 days duration. The company plans to introduce day trips in the future.

Students will be accompanied by teachers from their school. Tours are organised on a school basis and there will never be students or teachers from more than one school on the same tour. The number of teachers on a tour varies, depending on the number of students and the policy of the school. Your system must allow for different numbers of teachers on different tours. On each tour, one teacher is designated as the tour leader; other teachers on the tour will be allocated other roles such as deputy leader, teacher responsible for excursions, etc. Every teacher on a tour will have a designated role which must be recorded. Teachers will have different roles on different tours. A teacher will typically lead more than one tour and the company wishes to track this. The company also wishes to store additional information about teachers. For every teacher, the system will record contact details, mobile phone number and whether or not they have a first aid qualification. Some teachers will have extra skills, such as languages, swimming or ski-ing qualifications and these details must be recorded. Some teachers will not have any extra qualifications. The company has decided as a matter of policy, that a teacher can be associated with only 1 school. If a teacher moves to a new school, a new record would be created.

BST will be responsible for chartering coaches to take the parties to their destination and for arranging accommodation. For each tour BLT records the coach number, details of the driver and the coach company supplying the coach. You can assume that there is only 1 coach and 1 coach driver per tour. The coach company quotes a coach price for each tour which covers all travelling expenses, including driver accommodation, and the fee for the driver. You are not required to record details of ferry crossings, road tolls etc – this is all dealt with by the coach company. Tour coaches are linked to a specific company, and a specific driver is allocated to a specific coach for each tour. Drivers are free lance and are not linked to a specific company. The tour company will be responsible for paying the coach company and for paying for accommodation but other expenses are the responsibility of the teachers and are not part of your system.

The company wishes to record appropriate information about destinations such as location. Some tours may be multi centre – for example, tours to Germany typically visit Cologne and Berlin or Hannover and Berlin. Multi centre tours will stay in a different hotel/hostel at each location. A tour never stays in more than one hotel/hostel at any given location. The hotel/hostel will quote a price per night which includes breakfast and evening meal. The price per night charged to students, and the price per night charged to teachers differs.

BST will be responsible for arranging group insurance but will require confirmation from the school that each student, and each teacher on the tour has a valid passport . The company has informed schools that if this confirmation is not provided 7 days before the date of departure, the tour will not proceed. Due to problems in the past, BST requires passport confirmation from teachers for each tour.

The company does not maintain a list of approved accommodation but will build this up over time. In the meantime, the accommodation is booked as and when required. All accommodation must confirm to the following minimum requirements:

Fire Safety Certificate
Lift and disabled access
Coach Parking Space

and the details recorded must include number of beds, the charge per night, address and contact details and courier rating (this field will record the comments of the courier and will be used to decide whether a venue is suited for a particular group). Accommodation may be in a hotel, in which case BST wish to record star rating, information about catering facilities, and whether or not there is a swimming pool. Alternatively, accommodation may be in hostel in which case BST wish to record the type of hostel and whether any discount is available for membership (eg as with the International Youth Hostel Association).

For each tour, the company needs to record the start and end dates, details of all those taking part, details of coach, accommodation, courier , pick up and drop off points, whether multi-centre or not, destination .

Requirements:

1 DESIGN
(a) Develop an ERM to support BST’s activities. The ERM must include an ERD which shows entities and attributes. Include a list of the relevant domains. Identify primary and foreign keys, and show cardinality and optionality. Data must be in 3NF or higher unless it has been denormalised for performance reasons in which case an explanation must be given.

Your model should support the business requirements and you will be expected to use advanced modelling techniques
)
( Document and provide a written description and justification of your optimization strategy.
© Document all the constraints included in your system and justify and explain the constraints used and the ways in which they support the business rules. Provide a functional description of two triggers.

2 IMPLEMENTATION
(a) Implement your design and the optimization strategy. You will be expected to demonstrate tables, referential integrity, appropriate test data, appropriate constraints and appropriate optimization techniques. You will be expected to justify any mapping or denormalisation issues.
( Implement the two triggers for which you developed functional descriptions. You must be able to explain and justify the triggers to the tutor. Triggers which function but which the group cannot explain will receive zero marks. Some marks may be awarded for non functioning triggers where the group can identify the issues.

© Create and use ONE PL/SQL feature (choose from: procedure; or function or ; user defined type) to support a business requirement or enhance usability. One good quality example is sufficient but you must be able to explain why and how you would use this feature and what benefits it offers compared to other possible implementation approaches. Features which function but which the group cannot explain will receive zero marks. Some marks may be awarded for features which do not function fully but where the group can explain the issues.
(d) Create, document, and explain your test strategy.
3 QUERIES
Create the following queries – Groups must be able to explain the queries and justify the approach taken. Marks will be reduced where groups cannot explain how they have arrived at their solutions.

3(a) Create a query which shows which teachers have led more than one tour.
3( Design your test data so that some of your couriers speak languages which are not listed as language for any of the countries in your system. Create a query which shows which languages spoken by the couriers are not spoken in any of the countries in your system.
3© Create a query which shows, for each tour, which destinations are included and the arrival date for each destination. At least 1 tour must be multi-centre.

3(d) Create a query which gives the name of the coach company that has charged the lowest fee for providing a tour coach

3(e) Create a query which provides, for each tour, the following information:

The total cost of 7 nights accommodation at each of the tour destinations, for all the students on the tour.
(Hint: there are different ways of doing this but you may wish to use break and compute statements with a query.)
3(f) Create a query which shows which teachers have accompanied which tours, which school each teachers teaches at, whether or not the teacher has a first aid qualification, what the teacher’s role is on the tour and whether or not the teacher has any additional skills.
3(g) Develop one additional query of your own which provides information that would be useful for the business. Marks will be awarded depending on the technical skills shown and the relevance of the query

you_are_required_to.doc