create

CREATE TABLE Reservation
    (
     coachNumber INTEGER NOT NULL ,
     seatNumber INTEGER NOT NULL ,
     reservationDate DATETIME NOT NULL ,
     paidDate DATETIME ,
     canceledDate DATETIME ,
     idTrainRide INTEGER NOT NULL ,
     idUser INTEGER ,
     CONSTRAINT Reservation_PK PRIMARY KEY NONCLUSTERED (coachNumber, seatNumber, idTrainRide)
    )
GO

CREATE TABLE Staff
    (
     idStaff INTEGER NOT NULL ,
     fname VARCHAR (30) NOT NULL ,
     lname VARCHAR (30) NOT NULL ,
     position CHAR (1) NOT NULL ,
     jobStart DATETIME NOT NULL ,
     jobEnd DATETIME ,
     salary INTEGER NOT NULL ,
     CONSTRAINT Staff_PK PRIMARY KEY NONCLUSTERED (idStaff)
    )
GO

CREATE TABLE Station
    (
     idStation INTEGER NOT NULL ,
     name VARCHAR (60) NOT NULL ,
     CONSTRAINT Station_PK PRIMARY KEY NONCLUSTERED (idStation)
    )
GO

CREATE TABLE Train
    (
     idTrain INTEGER NOT NULL ,
     name VARCHAR (20) NOT NULL ,
     coachCount INTEGER NOT NULL ,
     price FLOAT NOT NULL ,
     startTime TIME NOT NULL ,
     endTime TIME NOT NULL ,
     idStation INTEGER ,
     idStation1 INTEGER ,
     CONSTRAINT Train_PK PRIMARY KEY NONCLUSTERED (idTrain)
    )
GO


CREATE TABLE TrainCrew
    (
     idStaff INTEGER NOT NULL ,
     idTrainRide INTEGER NOT NULL ,
     CONSTRAINT PK_TrainCrew PRIMARY KEY NONCLUSTERED (idStaff, idTrainRide)
    )
GO


CREATE TABLE TrainRide
    (
     idTrainRide INTEGER NOT NULL ,
     rideDate DATE NOT NULL ,
     idTrain INTEGER ,
     CONSTRAINT TrainRide_PK PRIMARY KEY NONCLUSTERED (idTrainRide)
    )
GO


CREATE TABLE [User]
    (
     idUser INTEGER NOT NULL ,
     email VARCHAR (30) NOT NULL ,
     fname VARCHAR (40) ,
     lname VARCHAR (40) ,
     password VARCHAR (12) NOT NULL ,
     CONSTRAINT User_PK PRIMARY KEY NONCLUSTERED (idUser)
    )
GO


ALTER TABLE TrainCrew
    ADD CONSTRAINT Relation_1 FOREIGN KEY
    (
     idStaff
    )
    REFERENCES Staff
    (
     idStaff
    )
GO

ALTER TABLE TrainCrew
    ADD CONSTRAINT Relation_3 FOREIGN KEY
    (
     idTrainRide
    )
    REFERENCES TrainRide
    (
     idTrainRide
    )
GO

ALTER TABLE TrainRide
    ADD CONSTRAINT Relation_4 FOREIGN KEY
    (
     idTrain
    )
    REFERENCES Train
    (
     idTrain
    )
GO

ALTER TABLE Reservation
    ADD CONSTRAINT Relation_5 FOREIGN KEY
    (
     idTrainRide
    )
    REFERENCES TrainRide
    (
     idTrainRide
    )
GO

ALTER TABLE Reservation
    ADD CONSTRAINT Relation_6 FOREIGN KEY
    (
     idUser
    )
    REFERENCES [User]
    (
     idUser
    )
GO

ALTER TABLE Train
    ADD CONSTRAINT Relation_7 FOREIGN KEY
    (
     idStation
    )
    REFERENCES Station
    (
     idStation
    )
GO

ALTER TABLE Train
    ADD CONSTRAINT Relation_8 FOREIGN KEY
    (
     idStation1
    )
    REFERENCES Station
    (
     idStation
    )
GO