Luggage simulation: appendix DB script

CREATE TABLE [AIRPORT] (
  [AIRPORTCODE] [char] (3)  NOT NULL ,
  [COUNTRYCODE] [varchar] (5)  NOT NULL ,
  [AIRPORTNAME] [varchar] (50)  NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [DESTINATIONCHECKPOINT] (
  [AIRPORTCODE] [char] (3)  NOT NULL ,
  [CHECKPOINTCODE] [char] (2)  NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [FLIGHT] (
  [AIRLINECODE] [varchar] (5)  NOT NULL ,
  [FLIGHTNO] [numeric](6, 0) NOT NULL ,
  [FLIGHTDATE] [datetime] NOT NULL ,
  [EXPECTEDGATE] [varchar] (3)  NOT NULL ,
  [PLANEBRAND] [varchar] (20)  NOT NULL ,
  [PLANEMODEL] [varchar] (20)  NOT NULL ,
  [AIRPORTCODE] [char] (3)  NOT NULL ,
  [REALGATE] [varchar] (3)  NOT NULL ,
  [EXPECTBOARDINGTIME] [datetime] NOT NULL ,
  [REALBOARDINGTIME] [varchar] (5)  NULL ,
  [EXPECTDEPARTURETIME] [datetime] NOT NULL ,
  [REALDEPARTURETIME] [varchar] (5)  NULL 
) ON [PRIMARY]
GO

CREATE TABLE [LUGGAGE] (
  [LUGGAGENO] [int] IDENTITY (1, 1) NOT NULL ,
  [PASSENGERNO] [int] NOT NULL ,
  [WEIGHT] [float] NOT NULL ,
  [CHECKINTIME] [datetime] NOT NULL ,
  [CHECKOUTTIME] [datetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [LUGGAGECHECKPOINT] (
  [CHECKPOINTCODE] [char] (2)  NOT NULL ,
  [LUGGAGENO] [int] NOT NULL ,
  [CHECKINTIME] [datetime] NULL ,
  [CHECKRESULT] [smallint] NULL ,
  [CHECKOUTTIME] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [PASSENGER] (
  [PASSENGERNO] [int] IDENTITY (1, 1) NOT NULL ,
  [CUSTOMERNO] [int] NOT NULL ,
  [COUNTRYCODE] [varchar] (5)  NOT NULL ,
  [AIRLINECODE] [varchar] (5)  NOT NULL ,
  [FLIGHTNO] [numeric](6, 0) NOT NULL ,
  [FLIGHTDATE] [datetime] NOT NULL ,
  [CLASSCODE] [char] (1)  NOT NULL ,
  [TITLE] [varchar] (3)  NOT NULL ,
  [FIRSTNAME] [varchar] (20)  NOT NULL ,
  [LASTNAME] [varchar] (20)  NOT NULL ,
  [PASSPORTNUM] [varchar] (10)  NOT NULL ,
  [BIRTHDAY] [datetime] NOT NULL ,
  [GENDER] [char] (1)  NOT NULL ,
  [PAIDPRICE] [numeric](8, 2) NOT NULL ,
  [PAIDTAX] [numeric](8, 2) NOT NULL ,
  [PAIDFEE] [numeric](8, 2) NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [AIRPORT] ADD 
   PRIMARY KEY  CLUSTERED 
  (
    [AIRPORTCODE]
  )  ON [PRIMARY] 
GO

ALTER TABLE [DESTINATIONCHECKPOINT] ADD 
   PRIMARY KEY  CLUSTERED 
  (
    [AIRPORTCODE],
    [CHECKPOINTCODE]
  )  ON [PRIMARY] 
GO

ALTER TABLE [FLIGHT] ADD 
  CONSTRAINT [PK__FLIGHT] PRIMARY KEY  CLUSTERED 
  (
    [AIRLINECODE],
    [FLIGHTNO],
    [FLIGHTDATE]
  )  ON [PRIMARY] 
GO

ALTER TABLE [LUGGAGE] ADD 
  CONSTRAINT [PK__LUGGAGE] PRIMARY KEY  CLUSTERED 
  (
    [LUGGAGENO]
  )  ON [PRIMARY] ,
  CONSTRAINT [CK_Check_luggage_time] CHECK ([checkintime] < [checkouttime])
GO

ALTER TABLE [LUGGAGECHECKPOINT] ADD 
  CONSTRAINT [PK_LUGGAGECHECKPOINT] PRIMARY KEY  CLUSTERED 
  (
    [CHECKPOINTCODE],
    [LUGGAGENO]
  )  ON [PRIMARY] 
GO

ALTER TABLE [PASSENGER] ADD 
  CONSTRAINT [PK__PASSENGER] PRIMARY KEY  CLUSTERED 
  (
    [PASSENGERNO]
  )  ON [PRIMARY] ,
  CONSTRAINT [CK_Check_pass_birthday] CHECK ([birthday] <= [flightdate]),
  CONSTRAINT [CK_Check_pass_classcode] CHECK ([classcode] = 'B' or [classcode] = 'E'),
  CONSTRAINT [CK_Check_pass_gender] CHECK ([gender] = 'F' or [gender] = 'M'),
  CONSTRAINT [CK_Check_pass_title] CHECK ([title] = 'Mrs' or ([title] = 'Ms' or [title] = 'Mr'))
GO

ALTER TABLE [DESTINATIONCHECKPOINT] ADD 
  CONSTRAINT [FK_DESTINATIONCHECKPOINT_AIRPORT] FOREIGN KEY 
  (
    [AIRPORTCODE]
  ) REFERENCES [AIRPORT] (
    [AIRPORTCODE]
  )
GO

ALTER TABLE [FLIGHT] ADD 
  CONSTRAINT [FK_FLIGHT_AIRPORT] FOREIGN KEY 
  (
    [AIRPORTCODE]
  ) REFERENCES [AIRPORT] (
    [AIRPORTCODE]
  )
GO

ALTER TABLE [LUGGAGE] ADD 
  CONSTRAINT [FK__LUGGAGE__PASSENGER] FOREIGN KEY 
  (
    [PASSENGERNO]
  ) REFERENCES [PASSENGER] (
    [PASSENGERNO]
  )
GO

ALTER TABLE [LUGGAGECHECKPOINT] ADD 
  CONSTRAINT [FK__LUGGAGE__CHECKPOINT] FOREIGN KEY 
  (
    [LUGGAGENO]
  ) REFERENCES [LUGGAGE] (
    [LUGGAGENO]
  )
GO

ALTER TABLE [PASSENGER] ADD 
  CONSTRAINT [FK_PASSENGER_FLIGHT] FOREIGN KEY 
  (
    [AIRLINECODE],
    [FLIGHTNO],
    [FLIGHTDATE]
  ) REFERENCES [FLIGHT] (
    [AIRLINECODE],
    [FLIGHTNO],
    [FLIGHTDATE]
  )
GO

CREATE TRIGGER O_InsertLuggageCheckpoint ON LUGGAGE
AFTER  INSERT
AS
BEGIN

  declare @CHECKPOINTCODE char(2), @LUGGAGENO int
  
  DECLARE Checkpoints_Cursor CURSOR FOR
  select  dc.checkpointcode, i.luggageno
  from inserted i, passenger p, flight f,destinationcheckpoint dc
  where i.passengerno=p.passengerno
    and p.airlinecode=f.airlinecode
    and p.flightno=f.flightno
    and p.flightdate=f.flightdate
    and f.airportcode=dc.airportcode
  OPEN Checkpoints_Cursor

  FETCH NEXT FROM Checkpoints_Cursor INTO  @CHECKPOINTCODE, @LUGGAGENO
  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF @CHECKPOINTCODE = 'C1'
    BEGIN
      INSERT INTO LUGGAGECHECKPOINT(CHECKPOINTCODE, LUGGAGENO, CHECKINTIME)
      VALUES( @CHECKPOINTCODE, @LUGGAGENO, GETDATE())
    END
    ELSE
    BEGIN 
      INSERT INTO LUGGAGECHECKPOINT(CHECKPOINTCODE, LUGGAGENO)
      VALUES( @CHECKPOINTCODE, @LUGGAGENO)
    END
       FETCH NEXT FROM Checkpoints_Cursor INTO @CHECKPOINTCODE, @LUGGAGENO
  END
  CLOSE Checkpoints_Cursor
  DEALLOCATE Checkpoints_Cursor

END

GO

CREATE TRIGGER UpdateLuggageCheckouttime ON LuggageCheckpoint
AFTER  UPDATE
AS
BEGIN
  declare @checkouttime datetime
  declare @luggageno int
  select @luggageno = luggageno from inserted
  -- to see if this luggage pass all checks
  if ((select count(*) from luggagecheckpoint lc where lc.luggageno = @luggageno and lc.checkouttime is null) = 0)
  begin
    -- update table Luggage
    select @checkouttime = checkouttime 
      from luggagecheckpoint
      where luggageno=@luggageno and 
        checkouttime >= all (select checkouttime from luggagecheckpoint where luggageno=@luggageno) 
      order by checkpointcode desc
    update LUGGAGE set CHECKOUTTIME=@checkouttime where luggageno=@luggageno
  end
END