Database Management Systems Analysis

Running head: DATABASE SYSTEMS
DATABASE SYSTEMS
Name of the Student
Name of the University
Author Note

1DATABASE SYSTEMS
Relational Schema
Client Table
Client (Client_No, Name, Address, Phone_No, Discount)
Location Table
Location (Name, Type, Description)
Tour Table
Tour (Name, Duration, Cost)
Itinerary Table
Itinerary (Tour*, Location*, Sequence_Number)
Guide Table
Guide (Guide_No, Family_Name, Given_Name, Address, DOB,
Date_Joined, Supervisor*)
Qualification Table
Qualification (Tour*, Employee*, Date_Passed)
Outing Table
Outing (Tour*, Start_Date, Guide*)
Participant Table
Participant (Tour*, Start_Date*, Client*)
Next_of_Kin Table
Next_of_Kin(Guide_No*, Kin_Number, Name, Contact_number)
Implementation
Client Table
create table Client (Client_No number(5) primary key, Name
varchar(30) not null, Address varchar(50) not null,
Phone_no number(11) unique not null, Discount number(3)
null);
Location Table

2DATABASE SYSTEMS
create table Location( Name varchar(30) primary key, Type
varchar(50) not null, Description varchar(100) not null);
Tour Table
Create table Tour (Name varchar(30) primary key, Duration
varchar(35) not null, Cost number(6,2) not null);
Itinerary Table
Create Table Itinerary (Tour varchar(30), Location
varchar(30), Sequence_Number number(10) not null, primary
key (Tour,Location), constraint fk_tour foreign key(Tour)
references Tour(Name), constraint fk_Location foreign
key(Location) references Location(Name));
Guide Table
Create Table Guide (Guide_No number(5) primary key,
Family_Name varchar(30) not null, Given_Name varchar(30)
not null, Address varchar(50) not null, DOB Date not null,
Date_Joined date not null, Supervisor number(5) null);
alter table Guide
add constraint fk_supervisor foreign key(Supervisor)
references Guide(Guide_No);
Qualification Table
Create Table Qualification (Tour varchar(30), Employee
number(5), Date_Passed date not null,
primary key(Tour,Employee), constraint fk_tour1 foreign
key(Tour) references Tour(Name),
constraint fk_Employee foreign key(Employee) references
Guide(Guide_No));
Outing Table
Create Table Outing (Tour varchar(30), Start_Date date not
null, Guide number(5) not null,

3DATABASE SYSTEMS
primary key(Tour, Start_Date), constraint fk_tour2 foreign
key(Tour) references Tour(Name),
constraint fk_guide foreign key(Guide) references
Guide(Guide_No));
Participant Table
Create Table Participant (Tour varchar(30), Start_Date
date, Client number(5),
Primary key (Tour,Start_Date,Client), constraint fk_toursd
foreign key(Tour,Start_Date) references Outing(Tour,
Start_Date),
constraint fk_client foreign key(client) references
Client(Client_no));
Next_of_Kin Table
Create table Next_of_Kin(Guide_No number(5), Kin_Number
number(5), Name varchar(30) not null, Contact_Number
number(11) unique not null,
primary key(Guide_No,Kin_Number), constraint fk_guidekin
foreign key(guide_no) references Guide(Guide_No));
Check Constraints
create or replace trigger Checkdob
before insert or update on Guide
for each row
begin
if( :new.DOB > sysdate ) then
raise_application_error( -20001, 'invalid dob' );
end if;
end;

4DATABASE SYSTEMS
create or replace trigger checkdate
before insert or update on Guide
for each row
begin
if( :new.Date_joined > sysdate ) then
raise_application_error( -20001, 'invalid date' );
end if;
end;