'>

SQL: Creation complete de la base de données Vol_Avion par Script en Client_Serveur

CREATE DATABASE VOL_AVION22
go
USE VOL_AVION22
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Passager]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Passager](
    [pas#] [int] NOT NULL,
    [nom] [varchar](20) NOT NULL,
    [prenom] [varchar](20) NOT NULL,
    [ville] [varchar](25) NOT NULL,
 CONSTRAINT [PK_Passager] PRIMARY KEY NONCLUSTERED
(
    [pas#] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[avion]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[avion](
    [Av#] [smallint] NOT NULL,
    [Marque] [varchar](10) NOT NULL,
    [TypeAvion] [varchar](10) NOT NULL,
    [Capacite] [smallint] NOT NULL,
    [localisation] [varchar](25) NOT NULL,
    [DateMiseEnService] [datetime] NULL,
 CONSTRAINT [PK_avion] PRIMARY KEY NONCLUSTERED
(
    [Av#] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pilote]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[pilote](
    [pil#] [smallint] IDENTITY(1,1) NOT NULL,
    [nom] [varchar](10) NOT NULL,
    [CodePostal] [char](5) NOT NULL,
    [Ville] [char](26) NOT NULL,
    [DateNaissance] [datetime] NULL,
    [DateDebutActivite] [datetime] NULL,
    [DateFinActivite] [datetime] NULL,
    [SalaireBrut] [money] NULL CONSTRAINT [DF_pilote_SalaireBrut]  DEFAULT ((1500)),
 CONSTRAINT [PK_pilote_1__11] PRIMARY KEY CLUSTERED
(
    [pil#] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AffecteVol]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[AffecteVol](
    [passager] [int] NOT NULL,
    [vol] [smallint] NOT NULL,
    [datevol] [datetime] NOT NULL,
    [numplace] [smallint] NOT NULL,
    [prix] [money] NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vol]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[vol](
    [vol#] [smallint] NOT NULL,
    [avion] [smallint] NOT NULL,
    [pilote] [smallint] NOT NULL,
    [villedepart] [varchar](26) NOT NULL,
    [villearrivee] [varchar](26) NOT NULL,
    [heuredepart] [decimal](5, 2) NOT NULL,
    [heurearrivee] [decimal](5, 2) NOT NULL,
 CONSTRAINT [PK_vol_1__14] PRIMARY KEY CLUSTERED
(
    [vol#] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_AffecteVol_Passager]') AND parent_object_id = OBJECT_ID(N'[dbo].[AffecteVol]'))
ALTER TABLE [dbo].[AffecteVol]  WITH CHECK ADD  CONSTRAINT [FK_AffecteVol_Passager] FOREIGN KEY([passager])
REFERENCES [dbo].[Passager] ([pas#])
GO
ALTER TABLE [dbo].[AffecteVol] CHECK CONSTRAINT [FK_AffecteVol_Passager]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_AffecteVol_vol]') AND parent_object_id = OBJECT_ID(N'[dbo].[AffecteVol]'))
ALTER TABLE [dbo].[AffecteVol]  WITH CHECK ADD  CONSTRAINT [FK_AffecteVol_vol] FOREIGN KEY([vol])
REFERENCES [dbo].[vol] ([vol#])
GO
ALTER TABLE [dbo].[AffecteVol] CHECK CONSTRAINT [FK_AffecteVol_vol]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_vol_avion]') AND parent_object_id = OBJECT_ID(N'[dbo].[vol]'))
ALTER TABLE [dbo].[vol]  WITH CHECK ADD  CONSTRAINT [FK_vol_avion] FOREIGN KEY([avion])
REFERENCES [dbo].[avion] ([Av#])
GO
ALTER TABLE [dbo].[vol] CHECK CONSTRAINT [FK_vol_avion]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_vol_pilote]') AND parent_object_id = OBJECT_ID(N'[dbo].[vol]'))
ALTER TABLE [dbo].[vol]  WITH CHECK ADD  CONSTRAINT [FK_vol_pilote] FOREIGN KEY([pilote])
REFERENCES [dbo].[pilote] ([pil#])
GO
ALTER TABLE [dbo].[vol] CHECK CONSTRAINT [FK_vol_pilote]