Creating the Database Schema

In this section, you will learn about the steps to create the database schema. You can use either SQL Server Enterprise Manager or Query Analyzer to create the database structure. If you choose SQL Server Enterprise Manager, you can use the MMC (Microsoft Management Console) based interface to graphically design your application. However, if you choose Query Analyzer, you need to specify SQL (structured query language) statements to design database tables and manage relationships.

In this section, I will examine how to create the database structure by using Query Analyzer.

Creating Database Tables

To use Query Analyzer for creating databases, you need to open Query Analyzer and connect to the SQL Server on which you want to create the database. The steps to open Query Analyzer and connect to a database are given as follows:

1.Click on Start. The Start menu will appear.

2.From the Programs menu, select Programs and then select Microsoft SQL Server.

3.From the submenu of Microsoft SQL Server, select Query Analyzer. The SQL Query Analyzer window will open.

4.In the SQL Query Analyzer window, the Connect to SQL Server dialog box appears by default. If it does not appear, select the Connect option from the File menu.

5.The Connect to SQL Server dialog box is shown in Figure 20-2. In this dialog box, select the name of the SQL Server from the SQL Server list and specify the username and password to log on to the database.

6.Click on OK to connect to the database.

456 Project 4 CREATING AN AIRLINE RESERVATION PORTAL

FIGURE 20-2 The Connect to SQL Se rver dialog box

After connecting to the SQL Server, you need to create the SkyShark Airlines database before you can create the database tables. To create the SkyShark Airlines database, run the following SQL script in Query Analyzer:

CREATE DATABASE SkyShark

GO

USE DATABASE SkyShark

GO

In the preceding SQL statements, I have created a SkyShark database and have changed the database context to SkyShark. All tables that I create now will be created in the SkyShark database. I now begin creating tables in this database.

CAUTION

While executing all SQL statements that follow in this section, ensure that the current database is specified as SkyShark. If this is not the case, all database tables will be created in the Master database.

Creating the dtUsers Table

The dtUsers table is used to store the username, password, and role of all users having access to the SkyShark Airlines application. To create the dtUsers table, execute the SQL script given as follows:

CREATE TABLE [dbo].[dtUsers] (

[Username] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Password] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Role] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [PasswordChanged] [bit] NULL

) ON [PRIMARY] GO

In the preceding statements, the dtUsers table is created and the Username, Password, Role, and Password fields are added to the table.

Creating the dtFltDetails Table

The dtFltDetails table is used to store details of all flights by SkyShark Airlines. The script to generate the dtFltDetails table is given as follows:

CREATE TABLE [dbo].[dtFltDetails] (

[FltNo] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Origin] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Destination] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Deptime] [datetime] NOT NULL ,

[Arrtime] [datetime] NOT NULL ,

[AircraftType] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [SeatsExec] [int] NOT NULL ,

[SeatsBn] [int] NOT NULL , [FareExec] [int] NOT NULL , [FareBn] [int] NOT NULL , [LaunchDate] [datetime] NOT NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO

Creating the dtCancellations Table

All cancellations made in the dtReservations table are recorded in the dtCancellations table.The query for creating the dtCancellations table is given as follows:

CREATE TABLE [dbo].[dtCancellations] (

[TicketNo] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Refund] [int] NOT NULL ,

[ProcessedBy] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [CancellationDate] [datetime] NOT NULL

) ON [PRIMARY] GO

Creating the dtDepartedFlights Table

The dtDepartedFlights table is similar to the dtReservations table. After flight departure, data pertaining to the flight is moved from the dtReservations table to the dtDepartedFlights table. The script that generates the dtDepartedFlights table is given as follows:

CREATE TABLE [dbo].[dtDepartedFlights] (

[TicketNo] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [FltNo] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [DateOfJourney] [datetime] NOT NULL ,

[ClassOfRes] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Name] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [EMail] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Fare] [int] NOT NULL , [Status] [int] NOT NULL ,

[ReservedBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [DateOfRes] [datetime] NOT NULL ,

[TicketConfirmed] [bit] NULL ) ON [PRIMARY]

GO