University Database Assignment: Monument Travel Database System Design

This assignment presents a comprehensive database solution for Monument Travel, a company specializing in guided tours. The solution includes the creation of several relational tables: Client, Location, Tour, Itinerary, Guide, Qualification, Outing, Participant, and Next_of_Kin. Each table is defined with specific attributes, data types, and primary keys. Foreign key constraints are implemented to enforce relationships between tables, such as the relationship between tours and locations in the Itinerary table or the supervisor relationship within the Guide table. Furthermore, the solution incorporates check constraints and triggers to ensure data integrity, including validation for dates of birth and dates joined. The provided SQL code demonstrates the creation of these tables, the definition of constraints, and the implementation of triggers to manage data validation rules. This assignment showcases the practical application of database design principles in a real-world scenario.

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;