undefined

Create logical schema using Oracle DBMS on School of ITEC Teaching Server on above ERD You are to:

• create required tables with attributes corresponding to the ERD;

• specify the primary key and foreign keys (if such exist) for each table;

• implement additional integrity constraints described below;

• create a file holding CREATE TABLE statements and execute the statements from the

  file using @ SQLPLUS command;

• record your session on Oracle 11g DBMS using SQLPlus spool command;

• You are NOT allowed to use ALTER TABLE statements to add integrity constraints. All

  constraints are to be defined in CREATE TABLE statements.

• The following additional integrity constraints MUST be implemented:

  1. When a record about a tour operator is removed from the database, the records about Airlines associated with this tour operator must also be removed from the database.

  2. When a record about a tour operators representative who supervised other representatives is removed from the database, the records about subordinate representatives must remain in the database.

  3. Domain of attribute Category (Room entity set) contains only three values: standard, luxury and executive.

  4. Domain of attribute Fee (Service entity set) contains numbers between 0 and 200.

  5. Domain of attribute Nbed (Room entity set) contains integer numbers 1, 2, and 3.

Website Category Title Bnum SID Fee Aname BView Phone Building Service Airline V N Has Sdate Deals Is_booked Rnum Nbed 1 TOID 1 Room VID Vdate Tour Operator Cname Category 1 Phone Is_accommodated Visit Mtype S Employs L GID K 1 Is_served Gname 1 Rname Guest RID Phone Address Restaurant 1 Q Representative Is_assigned Theme Principal Regular RestID Rtype Rname Credit Card Status Y Reports_to