Introduction: Clasic travel
agency is travel agency that has been established 30 years ago. But they are
not storing their customer details. Now they want to make database management
system to keep their customer details. In this assignment full database
management system have been descriebed.
Entitity relationship model
It is fully nosrmalized from of ERD model is shown.
Figure 1: Entity
with finished ERD diagram
Normalization has actually 5 steps. But
for this assignment 3 rules has been used.
Briefly described of this 3 step
cell to be single values in each column of a table, every entry in a column are
same type and every rows will be uniquely identified. It means that to add
unique ID or add more columns to make unique.
2nd normal form
In this step all attributes (non-key-columns)
depend on the key. It established relation among table. In 2nd
normal form every non-key columns are dependent on primary key of table’s.
3rd normal form
All columns can be determined only by the
key in the table and no other column. It means that all columns depend directly
on primary key (PK). By depending on primary key every columns will depends on
another column. In database it is called as transitive dependency.
Entity relationship diagram (ERD) is used
for modeling every data. Without normalization ERD cannot be possible.
Normalization helps to make ERD.
For normalization we can divide data into
individual table. In this assignment after normalization can see that data are
dividing into many tables which tell that which data depends on which primary
key and Primary key goes another table for understanding database.
Here produce a data dictionary for the entity relationship
model with all attributes with data types also identifying primary key and
Tables are shown below.
Figure 1: Customer details table.
Figure 2: Loyalty level table.
Figure 3: Booking table.
Figure 4: Staff table.
Figure 5: Flight table.
Figure 6: Airlines
Figure 7: Holliday
Figure 8: Hotel table.
Figure 9: Resort table.
Figure 10: Board basis table.
Figure 11: Tour
and data manipulation.
normalized table in SQL.Screen shot shows the database creation process.
Figure 1: Creating
Figure 2: Use
that created database.
Figure 3: Creating
loyalty scheme table.
Figure 4: Creating
customer details table
Figure 5: Creating
Figure 6: Creating
Figure 7: Creating
Figure 8: Creating
Creating tour operator table.
Figure 10: Creating
Figure 11: Creating
Figure 12: Creating
Figure 13: Insert
customer details into customer table.
Figure 14: Showing
Customer details table.
Figure 15: Insert
loyalty level into Loyalty scheme.
Figure 16: Show
Table loyalty scheme
Figure 17: Insert
resort name into resort table
Figure 18: shows resort table.
Figure 19: Insert
hotel name, quality into hotel table
20: shows hotel table
Figure 21: Insert
into board basis table
Figure 22: shows
board basis table.
Figure 23: Insert operator address company name phone
number into operator table
Figure 24: show
Figure 25: Insert
packages into holliday package
Figure 26: shows the holliday table.
Figure 27: Here
tour operator table shows for helping other table.
Figure 28: Insert
airline name into airlines and shows airline table.
Figure 29: Inserting
flight number time destination etc into flight table and show flight table.
Figure 30: Insert
staff name into staff table.
Figure 31: Insert
books all details into books details.
Figure 32: Checking
a query by its id and first name and its surname.
Figure 33: Query
with selecting 2 number package.
Figure 34: Query
with count hotel code.
Figure 35: Selecting
hotel name which price for all package under 300 pound.
Figure 36: Selecting
books which are written by kate smith.
Figure 37: Updating
query with flight departure.
Figure 38: Updating
Figure 39: Updating
query from loyalty level.