460 Project 4 CREATING AN AIRLINE RESERVATION PORTAL
Creating the dtPassengerDetails Table
The dtPassengerDetails table is used for storing data pertaining to passengers who have a valid e-mail address.The table is used to make discounts available for the frequent fliers program. To create the dtPassengerDetails table, execute the following script:
CREATE TABLE [dbo].[dtPassengerDetails] (
[EMail] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [FareCollected] [int] NOT NULL ,
[TotalTimesFlown] [int] NOT NULL ) ON [PRIMARY]
GO
Creating the dtFrequentFliers Table
The dtFrequentFliers table is used to store a list of passengers eligible for the frequent fliers program. The list is retrieved from the dtPassengerDetails table on the basis of a query specified by business managers. To create the dtFrequentFliers table, run the following script:
CREATE TABLE [dbo].[dtFrequentFliers] (
[EMail] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Discount] [int] NOT NULL
) ON [PRIMARY] GO
Now that you have created all the tables, the next step is to set primary keys and specify relationships between tables. You do that in the next section.
Managing Primary Keys and Relationships
Primary keys are used to ensure that the records in a table are unique. The primary keys for all database tables are listed in Table 20-1.
DESIGNING THE APPLICATION |
Chapter 20 |
461 |
|
|
|
|
|
Table 20-1 Primary Key Fields in Tables
Table Name |
Primary Key Field |
dtUsers |
Username |
dtFltDetails |
FltNo |
dtReservations |
TicketNo |
dtCancellations |
TicketNo |
dtDepartedFlights |
TicketNo |
dtPassengerDetails |
|
dtFrequentFliers |
|
|
|
To specify primary keys for tables, run the following script:
ALTER TABLE [dbo].[dtCancellations] WITH NOCHECK ADD
CONSTRAINT [PK_dtCancellation] PRIMARY KEY CLUSTERED
(
[TicketNo] ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[dtDepartedFlights] WITH NOCHECK ADD
CONSTRAINT [PK_dtDepartedFlights] PRIMARY KEY CLUSTERED
(
[TicketNo] ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[dtFltDetails] WITH NOCHECK ADD
CONSTRAINT [PK_dtFltDetails] PRIMARY KEY CLUSTERED
(
[FltNo]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[dtFrequentFliers] WITH NOCHECK ADD
CONSTRAINT [PK_dtFrequentFlier] PRIMARY KEY CLUSTERED
(
[EMail]
) ON [PRIMARY]
GO
462 Project 4 CREATING AN AIRLINE RESERVATION PORTAL
ALTER TABLE [dbo].[dtPassengerDetails] WITH NOCHECK ADD
CONSTRAINT [PK_dtAllCustomers] PRIMARY KEY CLUSTERED
(
[EMail]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[dtReservations] WITH NOCHECK ADD
CONSTRAINT [PK_dtReservations] PRIMARY KEY CLUSTERED
(
[TicketNo] ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[dtUsers] WITH NOCHECK ADD
CONSTRAINT [PK_dtUsers] PRIMARY KEY CLUSTERED
(
[Username] ) ON [PRIMARY]
GO
After creating the tables and setting the primary keys, you need to create relationships between tables. Relationships between tables are discussed in Table 18-8 of Chapter 18. To create relationships between tables, run the following code:
ALTER TABLE [dbo].[dtCancellations] ADD
CONSTRAINT [FK_dtCancellation_dtUsers] FOREIGN KEY
(
[ProcessedBy]
) REFERENCES [dbo].[dtUsers] ( [Username]
)
GO
ALTER TABLE [dbo].[dtDepartedFlights] ADD
CONSTRAINT [FK_dtDepartedFlights_dtPassengerDetails] FOREIGN KEY
(
[EMail]
) REFERENCES [dbo].[dtPassengerDetails] ( [EMail]
) NOT FOR REPLICATION
DESIGNING THE APPLICATION |
Chapter 20 |
463 |
|
|
|
|
|
GO
ALTER TABLE [dbo].[dtDepartedFlights] nocheck constraint [FK_dtDepartedFlights_dtPassengerDetails]
GO
ALTER TABLE [dbo].[dtFltStatus] ADD
CONSTRAINT [FK_dtFlightStatus_dtFltDetails] FOREIGN KEY
(
[FltNo]
) REFERENCES [dbo].[dtFltDetails] ( [FltNo]
)
GO
ALTER TABLE [dbo].[dtFrequentFliers] ADD
CONSTRAINT [FK_dtFrequentFlier_dtAllCustomers] FOREIGN KEY
(
[EMail]
) REFERENCES [dbo].[dtPassengerDetails] ( [EMail]
)
GO
ALTER TABLE [dbo].[dtReservations] ADD
CONSTRAINT [FK_dtReservations_dtFltDetails] FOREIGN KEY
(
[FltNo]
) REFERENCES [dbo].[dtFltDetails] ( [FltNo]
),
CONSTRAINT [FK_dtReservations_dtUsers] FOREIGN KEY
(
[ReservedBy]
) REFERENCES [dbo].[dtUsers] ( [Username]
)
GO