Create scripts

User table

 CREATE TABLE User(
  UserID INT NOT NULL,
  Username varchar(255) NOT NULL,
  Password varchar(255) NOT NULL,
  Role varchar(255) NULL,
  PRIMARY KEY (UserID));

UserTabs table

CREATE TABLE UserTabs (
  UserID INT NOT NULL,
  TabID INT NOT NULL,
  PRIMARY KEY (UserID, TabID));

DriverRaces table

CREATE TABLE DriverRaces (
  RaceID INT NOT NULL,
  DriverID INT NOT NULL,
  RoundID INT NOT NULL,
  PRIMARY KEY (RaceID, DriverID, RoundID)
);

Driver table

CREATE TABLE Driver (
  DriverID INT NOT NULL,
  Drivername varchar(255) NOT NULL,
  PRIMARY KEY (DriverID));

Rounds table

CREATE TABLE Rounds (
  RoundNumber INT NOT NULL,
  RaceID INT NOT NULL,
  TimestampBegin DATETIME,
  TimestampEnd DATETIME,
  PRIMARY KEY (RoundNumber, RaceID)
  );

TabGraphs table

CREATE TABLE TabGraphs (
  TabID INT NOT NULL,
  GraphID INT NOT NULL,
  PRIMARY KEY (TabID, GraphID)
  );

Tabs table

  CREATE TABLE Tabs (
  TabID INT NOT NULL,
  TabName varchar(255) NOT NULL,
  RaceID INT NOT NULL,
  PRIMARY KEY (TabID)
  );

Graphs table

CREATE TABLE Graphs(
  GraphID INT NOT NULL,
  Type varchar(255) NOT NULL,
  SensorID INT NOT NULL,
  PRIMARY KEY (GraphID));

Sensors table

CREATE TABLE Sensors(
  SensorID INT NOT NULL,
  SensorName varchar(255) NOT NULL,
  PRIMARY KEY (SensorID));

WheelspeedSensor table

CREATE TABLE WheelspeedSensor(
  WheelspeedSensorID INT NOT NULL,
  Timestamp DATETIME NULL,
  RPM float NULL,
  PRIMARY KEY (WheelspeedSensorID));

Foreign key scripts

DriverRaces DriverID to Driver DriverID 

  Alter TABLE DriverRaces
  ADD CONSTRAINT FK_DriversRacesID FOREIGN KEY (DriverID) REFERENCES Driver(DriverID);

DriverRaces RoundID to Rounds RoundNumber

Alter TABLE DriverRaces
ADD CONSTRAINT FK_RoundDriverRaces FOREIGN KEY (RoundID) REFERENCES Rounds(RoundNumber);

DriverRaces RaceID to Race RaceID

Alter TABLE DriverRaces
ADD CONSTRAINT FK_RaceDriversID FOREIGN KEY (RaceID) REFERENCES Race(RaceID);

Tabs RaceID to Race RaceID

ALTER TABLE Tabs
ADD CONSTRAINT fk_TabsRaceID FOREIGN KEY (RaceID) REFERENCES Race(RaceID);

UserTabs UserID to User UserID

ALTER TABLE UserTabs
ADD CONSTRAINT fk_UserTabsID FOREIGN KEY (UserID) REFERENCES User(UserID);

TabGraphs TabID to Tab TabID

ALTER TABLE Tabs
ADD CONSTRAINT fk_GraphsTabID FOREIGN KEY (TabID) REFERENCES Tab(TabID);

TabGraphs GraphID to Graphs GraphID

ALTER TABLE TabGraphs
ADD CONSTRAINT fk_TabGraphsID FOREIGN KEY (GraphID) REFERENCES Graphs(GraphID);

Graphs SensorID to Sensors SensorID

ALTER TABLE Graphs
ADD CONSTRAINT fk_GraphsSensorID FOREIGN KEY (SensorID) REFERENCES Sensors(SensorID);



  • No labels