مساعده في فهم المطلوب بهذا البروجكت

We need to build a database for  “Holy places visiting (Campaign)”. The campaign consists of booking a bus plus a hotel. A bus campaign system, which needs to model the following: Bus Stations/stops, Tracks, connecting stations. You can assume for simplicity that only one track exists between any two stations. All the tracks put together form a graph. Buss, with an ID , type and a name. Bus schedules recording what time a bus passes through each station/stop on its route. For the other hand, Hotel/Hostels consists of their Types (one star, two stars,…), number of passengers (single, double,…..), and numbers of staying days in each place.

You can assume for simplicity that for each bus, for each stop on its route,  you store Time in,  Time out (same as time in if it does not stop), and a sequence number so the stations/stops in the route of a bus can be ordered by sequence number.

Visitors booking consisting of

1-      bus, date, from-station, to-station, coach, seat and passenger name.

2-      Hotel/hostels Name, Type, Check-in date, Check-out date and any extra-information you may need.

3-      The total cost for the campaign.

PHASE I:

A.    Conceptual Part:

Draw an EER according to your requirements that:

·         Identify Entities.

·         Identify Relationship.

·         Identify Attributes.

·         Show the Cardinality and Multiplicity

B.     Logical Part:

1.      Map your ER/EER model to a relational database schema diagram. Submit this schema (with key and referential integrity constraints indicated in the usual way).

2.      Normalize your schema to the third normal form and show all the normalization details.

PHASE II: Physical Part (Implementation)

A.    Schema Implementation:

Use the Oracle (Oracle Database 11g Express Edition) as the DBMS for implementing your project.

1.       Use appropriate naming conventions for all of your tables and attributes

2.       Write  SQL  DDL  statements  to  create  database,  tables  and  all  other  structures.

Primary key and foreign keys must be defined as appropriate.

3.       Define attributes by adding data type for each attribute in addition to specifying if NULL is permitted, or its value is UNIQUE.

4.       Explain where and how referential integrity constraints have been incorporated.

1.       Insert at least 5 rows into each table (unless you have cardinality constraints).

2.       The data values should be reasonable.

B.     Query implementations:

Write different queries, give SQL translations of them, and  indicate their implementation and solutions. Create Data Queries as follows:

1.       List 2 different delete queries related to your tables.

2.       List 2 different update queries related to your tables.

1.    List 2 simple select queries related to your tables.

2.    List 2 nested queries related to your tables.

3.    List 2 simple join queries related to your tables.

4.    List 2 simple retrieval queries using group by, having clause, and aggregation functions related to your tables.

List 2 different views, give SQL translations of them, and indicate their implementation and solutions.