LMS知識社群歷程檔案Login
Position: 陳秀榮 > 未分類
54
by 陳秀榮 2017-10-06 15:17:06, Reply(0), Views(268)
use Test
CREATE TABLE DRIVER(
IDCardNumber Char(10) NOT NULL,
Gender Char(15) NULL,
Age Int NULL,
CONSTRAINT DRIVERPK PRIMARY KEY(IDCardNumber),
CONSTRAINT DRIVERAK1 UNIQUE(IDCardNumber)
);
CREATE TABLE VEHICLE(
LicensePlate Char(10) NOT NULL,
Years Int NULL,
CarType Char(15) NULL,
IDCardNumber Char(10) NOT NULL,
CONSTRAINT VEHICLEPK PRIMARY KEY(LicensePlate),
CONSTRAINT VEHICLEAK1 UNIQUE(LicensePlate),
CONSTRAINT DRIVERFK FOREIGN KEY(IDCardNumber)
REFERENCES DRIVER(IDCardNumber)
ON UPDATE NO ACTION
ON DELETE NO ACTION

);


CREATE TABLE DynamicInformation(
DLicensePlate Char(10) NOT NULL,
TheExistingStation Char(15) NOT NULL,
CurrentRoute Char(5) NOT NULL,
LastStop Char(15) NOT NULL,
LastStopTime Char(10) NOT NULL,
LicensePlate Char(10) NOT NULL,
CONSTRAINT DynamicInformationPK PRIMARY KEY(DLicensePlate,TheExistingStation,CurrentRoute,LastStop),
CONSTRAINT VEHICLEFK FOREIGN KEY(LicensePlate)
REFERENCES VEHICLE(LicensePlate)
ON UPDATE CASCADE
ON DELETE CASCADE
);


CREATE TABLE StationInformation(
StationNumbering Char(15) NOT NULL,
StationName Char(15) NOT NULL,
StationDirection Char(15) NOT NULL,
DLicensePlate Char(10) NOT NULL,
TheExistingStation Char(15) NOT NULL,
CurrentRoute Char(5) NOT NULL,
LastStop Char(15) NOT NULL,
CONSTRAINT StationInformation PRIMARY KEY(StationNumbering),
CONSTRAINT StationInformationAK1 UNIQUE(StationNumbering),
CONSTRAINT DynamicInformationFK FOREIGN KEY (DLicensePlate)
REFERENCES DynamicInformation (DLicensePlate)
ON UPDATE CASCADE
ON DELETE CASCADE ,
CONSTRAINT DynamicInformationFK FOREIGN KEY(TheExistingStation)
REFERENCES DynamicInformation (TheExistingStation)
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT DynamicInformationFK FOREIGN KEY(CurrentRoute)
REFERENCES DynamicInformation (CurrentRoute)
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT DynamicInformationFK FOREIGN KEY(LastStop)
REFERENCES DynamicInformation (LastStop)
ON UPDATE CASCADE
ON DELETE CASCADE
);


CREATE TABLE ROUTE(
Route Char(5) NOT NULL,
DockedStation Char(50) NOT NULL,
RLastStop Char(15) NOT NULL,
RNextStop Char(15) NOT NULL,
LicensePlate Char(10) NOT NULL,
StationNumbering Char(15) NOT NULL,
CONSTRAINT ROUTEPK PRIMARY KEY(Route,DockedStation),
CONSTRAINT VEHICLEFK FOREIGN KEY(LicensePlate)
REFERENCES VEHICLE(LicensePlate)
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT StationInformationFK FOREIGN KEY(StationNumbering)
REFERENCES StationInformation(StationNumbering)
ON UPDATE NO ACTION
ON DELETE NO ACTION
);

Attachments:
1. 測試.c (1 KB)
Reply