Task 1 Introduction: Clasic travel agency is travel

Task 1

 

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.

We Will Write a Custom Essay Specifically
For You For Only $13.90/page!


order now

 

1.  
Entitity relationship model

It is fully nosrmalized from of  ERD model is shown.

Figure 1: Entity
relationship model.

2.   
 

Contribute
with finished ERD diagram

 

Normalization has actually 5 steps. But
for this assignment 3 rules has been used.

Briefly described of this 3 step

 

 1st
normal form

 Each
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.

 

 

 

 

 

 

 

 

 

 

3.

Data
Dictionary

Here produce a data dictionary for the entity relationship
model with all attributes with data types also identifying primary key and
foreign key.

Tables are shown below.

 

 Customer details

 Figure 1: Customer details table.

 

  Loyalty level

  Figure 2: Loyalty level table.

 

  Booking

  Figure 3: Booking table.

 

 

  Staff

  Figure 4: Staff table.

 

 

 

 

 

  Flight

  Figure 5: Flight table.

 

  Airlines

  Figure 6: Airlines
table.

 

  Holliday package

Figure 7: Holliday
package.

  Hotel

 Figure 8: Hotel table.

 

  Resort

  Figure 9: Resort table.

 

   Board Basis

  Figure 10: Board basis table.

 

 

 

 

 

  Tour operator

Figure 11: Tour
operator table.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Task
2

Data entry
and data manipulation.

1.

All
normalized table in SQL.Screen shot shows the database creation process.

 

a.

Figure 1: Creating
Database

 

b.

Figure 2: Use
that created database.

 

c.

Figure 3: Creating
loyalty scheme table.

 

 

 

 

d.

Figure 4: Creating
customer details table

 

e.

Figure 5: Creating
Staff table.

 

f.

Figure 6: Creating
airlines table.

 

 

 

 

g.

Figure 7: Creating
flight table

 

h.

Figure 8: Creating
resort table

 

i.

Figure 9:
Creating tour operator table.

 

 

 

 

j.

Figure 10: Creating
hotel table

 

k.

Figure 11: Creating
holliday package

 

 

 

 

 

 

 

 

 

l.

Figure 12: Creating
booking table.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2.

a.

 

Figure 13: Insert
customer details into customer table.

Figure 14: Showing
Customer details table.

 

b.

Figure 15: Insert
loyalty level into Loyalty scheme.

 

Figure 16: Show
Table loyalty scheme

 

 

3

a.

Figure 17: Insert
resort name into resort table

Figure 18:  shows resort table.

 

b.

Figure 19: Insert
hotel name, quality into hotel table

 

.Figure
20: shows hotel table

 

4.

a.

Figure 21: Insert
into board basis table

Figure 22: shows
board basis table.

 

b.

Figure 23:  Insert operator address company name phone
number into operator table

Figure 24: show
operator table.

 

c.

Figure 25: Insert
packages into holliday package

Figure 26:  shows the holliday table.

 

5.

a.

Figure 27: Here
tour operator table shows for helping other table.

 

b.

Figure 28: Insert
airline name into airlines and shows airline table.

 

 

c.

 

Figure 29: Inserting
flight number time destination etc into flight table and show flight table.

 

6.

a.

Figure 30: Insert
staff name into staff table.

 

b.

 

Figure 31: Insert
books all details into books details.

 

 

7.

Figure 32: Checking
a query by its id and first name and its surname.

 

 

8.

Figure 33: Query
with selecting 2 number package.

 

 

9.

Figure 34: Query
with count hotel code.

 

 

 

10.

Figure 35: Selecting
hotel name which price for all package under 300 pound.

 

 

 

 

 

 

 

 

 

 

11.

Figure 36: Selecting
books which are written by kate smith.

12.

Figure 37: Updating
query with flight departure.

13.

Figure 38: Updating
package price.

 

 

 14.

Figure 39: Updating
query from loyalty level.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Task
-3

 

 

 

 

Conclusion:
dfd