Assignment 1: Database Design Report

DESCRIPTION OF THE APPLICATION

Tour Management system

A company hosts tours for the different tourists. On each day, there is single tour and there are different types of the tours. Some of the tours are more popular than the others. Each tour has some price and each tour is lead by the expert of the company. The expert acts as the leader of each tour. Each tour has some price. Each customer of the company is assigned a unique customer id. The details of the customer such as name, address and phone are recorded. There are several participants for the each tour. Each participant is given a unique identification number. The amount paid by the participant is also recorded.

Some events are hosted in each tour. A single event is hosted on each day. The date of the particular event is recoded. The expert of the particular tour hosts the event. Each expert has its unique expert identification. The name and contact of the expert is recorded by the system. For each tour there is unique tour code. Each tour is assigned some name and expert to lead the tour. In front of each tour, the description of the tour is provided and price for that particular tour is recorded. There is one type of brochure for each tour which is identified by the brochure identification number. The brochures are sent to various customers thorough emails.

UML diagram of Tour Management System

Relational Schema of the Tour Management System

  • CUSTOMER ( CID ,NAME ,ADDRESS, CONTACT);

Pk( CID);

  • EXPERT (EID,FIRSTNAME ,LASTNAME ,CONTACT)

Pk( EID);

  • TOUR(TOURCODE , TOURNAME , DESCRIPTION, PRICE ,EID);

Pk( TOURCODE);

FK( EID);

  • BROCHURE ( BID, TOURCODE, CID)

Pk( BID);

FK (TOURCODE);

FK (CID);

  • EVENTTOUR(EVENTDATE, TOURCODE , ID)

Pk( EVENTDATE, TOURCODE);

FK( TOURCODE);

  • PARTICIPANT (PID , AMOUNTPAID , TOURCODE )

PK( PID);

FK( TOURCODE);

CREATION OF TABLES

  • CREATE TABLE CUSTOMER( CID Number(10) PRIMARY KEY, NAME VARCHAR(40), ADDRESS VARCHAR(50), CONTACT NUMBER(10));
  • CREATE TABLE EXPERT(EID NUMBER(4) PRIMARY KEY,FIRSTNAME VARCHAR(50), LASTNAME VARCHAR(50), CONTACT NUMBER(10));
  • CREATE TABLE TOUR(TOURCODE NUMBER(4) PRIMARY KEY, TOURNAME VARCHAR(60), DESCRIPTION VARCHAR(90), PRICE NUMBER(7), EID NUMBER(7),

FOREIGN KEY EID REFERENCES EXPERT (EID));

  • CREATE TABLE BROCHURE( BID NUMBER(4), TOURCODE NUMBER(4), CID NUMBER(4),

FOREIGN KEY (TOURCODE) REFERENCES TOUR(TOURCODE),

FOREIGN KEY (CID) REFERENCES CUSTOMER(CID) );

  • CREATE TABLE EVENTTOUR(EVENTDATE DATE, TOURCODE NUMBER(4),EID NUMBER(4),CONSTRAINT PK_EVENTTOUR1 PRIMARY KEY (EVENTDATE,TOURCODE),

FOREIGN KEY (TOURCODE) REFERENCES TOUR(TOURCODE),

FOREIGN KEY (EID) REFERENCES EXPERT (EID));

  • CREATE TABLE PARTICIPANTS (PID NUMBER(4) PRIMARY KEY, AMOUNTPAID NUMBER(4),TOURCODE NUMBER(4),

FOREIGN KEY (TOURCODE) REFERENCES TOUR (TOURCODE)));

INSERTION OF DATA

  • INSERT INTO CUSTOMER VALUES(‘123′,’JONE WARREN’, ’10 BLUFF RD’, 92672277);
  • INSERT INTO CUSTOMER VALUES(‘215′,’HERKE RIDE’, ’10 WHITE ST’, 92176688);
  • INSERT INTO EXPERT VALUES(1, ‘SUED’,’ DAVIESON’, 9214-8922);
  • INSERT INTO EXPERT VALUES(2, ‘GEOFFI’,’ MILLARI’, 9214-8911);
  • INSERT INTO TOUR VALUES(‘101’, ‘WINE APPRECIATION TOUR’,’ A GREAT WINETOUR FOR TOURISTS’,1300,1);
  • INSERT INTO TOUR VALUES(‘102’, ‘RED WINE TOUR’, ‘SAMPLE BRILLIANT RED WINE VARIETIES FROM THE FAMOUS PLACES’,700, 2);
  • INSERT INTO BROCHURE VALUES(2001, 102, 123 );
  • INSERT INTO BROCHURE VALUES( 2020, 101, 215 );
  • INSERT INTO EVENTTOUR VALUES( DATE’2020-11-01′, 101,1);
  • INSERT INTO EVENTTOUR VALUES( DATE’2020-12-08′,102, 2 );
  • INSERT INTO PARTICIPANT VALUES(1009′ ,1300,101);
  • INSERT INTO PARTICIPANT VALUES(1001 ,700,101);

Leave a Reply

Your email address will not be published. Required fields are marked *