Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE NP40Book;
- GO
- DROP DATABASE IF EXISTS MokeBike;
- GO
- CREATE DATABASE MokeBike;
- GO
- USE MokeBike;
- GO
- CREATE TABLE FaultType (
- FaultCode SMALLINT PRIMARY KEY,
- FaultDesc TEXT NULL,
- );
- GO
- INSERT INTO FaultType VALUES (1, 'Fault1');
- INSERT INTO FaultType VALUES (2, 'Fault2');
- INSERT INTO FaultType VALUES (3, 'Fault3');
- INSERT INTO FaultType VALUES (4, 'Fault4');
- INSERT INTO FaultType VALUES (5, 'Fault5');
- INSERT INTO FaultType VALUES (6, 'Fault6');
- INSERT INTO FaultType VALUES (7, 'Fault7');
- INSERT INTO FaultType VALUES (8, 'Fault8');
- INSERT INTO FaultType VALUES (9, 'Fault9');
- INSERT INTO FaultType VALUES (10, 'Fault10');
- INSERT INTO FaultType VALUES (11, 'Fault11');
- INSERT INTO FaultType VALUES (12, 'Fault12');
- INSERT INTO FaultType VALUES (13, 'Fault13');
- INSERT INTO FaultType VALUES (14, 'Fault14');
- INSERT INTO FaultType VALUES (15, 'Fault15');
- GO
- CREATE TABLE Member (
- MemberID BIGINT PRIMARY KEY,
- Email VARCHAR(50) NOT NULL,
- Phone CHAR(8) NOT NULL,
- RewardPts INT NOT NULL,
- CreditBalance SMALLMONEY NOT NULL,
- Deposit BIT NOT NULL DEFAULT 0,
- );
- GO
- INSERT INTO Member VALUES(1, 'member1@amail.com', '81111111', 10, 10.00, 1)
- INSERT INTO Member VALUES(2, 'member2@bmail.com', '82222222', 20, 20.00, 1)
- INSERT INTO Member VALUES(3, 'member3@gmail.com', '83333333', 30, 30.00, 1)
- INSERT INTO Member VALUES(4, 'member4@dmail.com', '84444444', 40, 40.00, 1)
- INSERT INTO Member VALUES(5, 'member5@email.com', '85555555', 50, 50.00, 1)
- INSERT INTO Member VALUES(6, 'member6@amail.com', '86666666', 60, 60.00, 1)
- INSERT INTO Member VALUES(7, 'member7@bmail.com', '87777777', 70, 70.00, 1)
- INSERT INTO Member VALUES(8, 'member8@gmail.com', '88888888', 80, 80.00, 1)
- INSERT INTO Member VALUES(9, 'member9@dmail.com', '89999999', 90, 90.00, 1)
- INSERT INTO Member VALUES(10, 'member10@email.com', '90000000', 100, 100.00, 1)
- INSERT INTO Member VALUES(11, 'member11@amail.com', '91111111', 110, 110.00, 1)
- INSERT INTO Member VALUES(12, 'member12@bmail.com', '92222222', 120, 120.00, 1)
- INSERT INTO Member VALUES(13, 'member13@gmail.com', '93333333', 130, 130.00, 1)
- INSERT INTO Member VALUES(14, 'member14@dmail.com', '94444444', 140, 140.00, 1)
- INSERT INTO Member VALUES(15, 'member15@email.com', '95555555', 150, 150.00, 1)
- GO
- CREATE TABLE BikeStop (
- BikeStopID BIGINT PRIMARY KEY,
- StopLat FLOAT NOT NULL,
- StopLong FLOAT NOT NULL,
- StopRadius FLOAT NOT NULL,
- );
- GO
- INSERT INTO BikeStop VALUES(1, 1.29027001, 103.85195901, 1);
- INSERT INTO BikeStop VALUES(2, 1.29027002, 103.85195902, 2);
- INSERT INTO BikeStop VALUES(3, 1.29027003, 103.85195903, 3);
- INSERT INTO BikeStop VALUES(4, 1.29027004, 103.85195904, 4);
- INSERT INTO BikeStop VALUES(5, 1.29027005, 103.85195905, 5);
- INSERT INTO BikeStop VALUES(6, 1.29027006, 103.85195906, 6);
- INSERT INTO BikeStop VALUES(7, 1.29027007, 103.85195907, 7);
- INSERT INTO BikeStop VALUES(8, 1.29027008, 103.85195908, 8);
- INSERT INTO BikeStop VALUES(9, 1.29027009, 103.85195909, 9);
- INSERT INTO BikeStop VALUES(10, 1.29027010, 103.85195910, 10);
- INSERT INTO BikeStop VALUES(11, 1.29027011, 103.85195911, 11);
- INSERT INTO BikeStop VALUES(12, 1.29027012, 103.85195912, 12);
- INSERT INTO BikeStop VALUES(13, 1.29027013, 103.85195913, 13);
- INSERT INTO BikeStop VALUES(14, 1.29027014, 103.85195914, 14);
- INSERT INTO BikeStop VALUES(15, 1.29027015, 103.85195915, 15);
- GO
- CREATE TABLE Bike (
- BikeID BIGINT PRIMARY KEY,
- Rate SMALLMONEY NOT NULL,
- Status VARCHAR(20) NOT NULL CHECK (Status IN ('Available', 'Damaged', 'Under Repair')),
- BikeStopID BIGINT NOT NULL FOREIGN KEY REFERENCES BikeStop,
- )
- GO
- INSERT INTO Bike VALUES (1, 0.40, 'Available', 1);
- INSERT INTO Bike VALUES (2, 0.32, 'Damaged', 2);
- INSERT INTO Bike VALUES (3, 0.40, 'Available', 3);
- INSERT INTO Bike VALUES (4, 0.40, 'Available', 4);
- INSERT INTO Bike VALUES (5, 0.40, 'Damaged', 5);
- INSERT INTO Bike VALUES (6, 0.55, 'Available', 6);
- INSERT INTO Bike VALUES (7, 0.40, 'Under Repair', 7);
- INSERT INTO Bike VALUES (8, 0.20, 'Available', 8);
- INSERT INTO Bike VALUES (9, 0.40, 'Damaged', 9);
- INSERT INTO Bike VALUES (10, 0.40, 'Available', 10);
- INSERT INTO Bike VALUES (11, 0.55, 'Available', 11);
- INSERT INTO Bike VALUES (12, 0.40, 'Damaged', 12);
- INSERT INTO Bike VALUES (13, 0.20, 'Available', 13);
- INSERT INTO Bike VALUES (14, 0.40, 'Damaged', 14);
- INSERT INTO Bike VALUES (15, 0.40, 'Available', 15);
- GO
- CREATE TABLE Promotion (
- PromoCode CHAR(6) PRIMARY KEY,
- PromoRate FLOAT NOT NULL,
- StartDate DATETIME NOT NULL,
- EndDate DATETIME NOT NULL,
- PromoDescription TEXT NULL,
- );
- GO
- INSERT INTO Promotion VALUES ('AAAAAA', 0.01, '2017-06-01', '2017-07-01', 'DescriptionA');
- INSERT INTO Promotion VALUES ('BBBBBB', 0.02, '2017-06-02', '2017-07-02', 'DescriptionB');
- INSERT INTO Promotion VALUES ('CCCCCC', 0.03, '2017-06-03', '2017-07-03', 'DescriptionC');
- INSERT INTO Promotion VALUES ('DDDDDD', 0.04, '2017-06-04', '2017-07-04', 'DescriptionD');
- INSERT INTO Promotion VALUES ('EEEEEE', 0.05, '2017-06-05', '2017-07-05', 'DescriptionE');
- INSERT INTO Promotion VALUES ('FFFFFF', 0.06, '2017-06-06', '2017-07-06', 'DescriptionF');
- INSERT INTO Promotion VALUES ('GGGGGG', 0.07, '2017-06-07', '2017-07-07', 'DescriptionG');
- INSERT INTO Promotion VALUES ('HHHHHH', 0.08, '2017-06-08', '2017-07-08', 'DescriptionH');
- INSERT INTO Promotion VALUES ('IIIIII', 0.09, '2017-06-09', '2017-07-09', 'DescriptionI');
- INSERT INTO Promotion VALUES ('JJJJJJ', 0.10, '2017-06-10', '2017-07-10', 'DescriptionJ');
- INSERT INTO Promotion VALUES ('KKKKKK', 0.11, '2017-06-11', '2017-07-11', 'DescriptionK');
- INSERT INTO Promotion VALUES ('LLLLLL', 0.12, '2017-06-12', '2017-07-12', 'DescriptionL');
- INSERT INTO Promotion VALUES ('MMMMMM', 0.13, '2017-06-13', '2017-07-13', 'DescriptionM');
- INSERT INTO Promotion VALUES ('NNNNNN', 0.14, '2017-06-14', '2017-07-14', 'DescriptionN');
- INSERT INTO Promotion VALUES ('OOOOOO', 0.15, '2017-06-15', '2017-07-15', 'DescriptionO');
- GO
- CREATE TABLE CreditCard (
- CardNo VARCHAR(19) PRIMARY KEY,
- CVV CHAR(4) NOT NULL,
- CardType VARCHAR(20) NOT NULL,
- ExpiryDate DATETIME NOT NULL,
- MemberID BIGINT NOT NULL FOREIGN KEY REFERENCES Member,
- );
- GO
- INSERT INTO CreditCard VALUES ('0101010101010101', '0101', 'Visa', '2020-09-30', 1);
- INSERT INTO CreditCard VALUES ('0202020202020202', '0202', 'Visa', '2023-11-30', 2);
- INSERT INTO CreditCard VALUES ('0303030303030303', '0303', 'MasterCard', '2022-06-30', 3);
- INSERT INTO CreditCard VALUES ('0404040404040404', '0404', 'Visa', '2019-03-31', 4);
- INSERT INTO CreditCard VALUES ('0505050505050505', '0505', 'Visa', '2020-11-30', 5);
- INSERT INTO CreditCard VALUES ('0606060606060606', '0606', 'MasterCard', '2023-08-31', 6);
- INSERT INTO CreditCard VALUES ('0707070707070707', '0707', 'Visa', '2023-03-31', 7);
- INSERT INTO CreditCard VALUES ('0808080808080808', '0808', 'Diners', '2022-08-31', 8);
- INSERT INTO CreditCard VALUES ('0909090909090909', '0909', 'Visa', '2025-02-28', 9);
- INSERT INTO CreditCard VALUES ('1010101010101010', '1010', 'Diners', '2025-06-30', 10);
- INSERT INTO CreditCard VALUES ('1111111111111111', '1111', 'MasterCard', '2024-09-30', 11);
- INSERT INTO CreditCard VALUES ('1212121212121212', '1212', 'Diners', '2023-11-30', 12);
- INSERT INTO CreditCard VALUES ('1313131313131313', '1313', 'Visa', '2022-01-31', 13);
- INSERT INTO CreditCard VALUES ('1414141414141414', '1414', 'Visa', '2025-02-28', 14);
- INSERT INTO CreditCard VALUES ('1515151515151515', '1515', 'Diners', '2020-04-30', 15);
- GO
- CREATE TABLE CreditDebitTrans (
- TransID BIGINT PRIMARY KEY,
- TransType VARCHAR(10) NOT NULL CHECK (TransType IN ('Debit', 'Topup')),
- TransAmt SMALLMONEY NOT NULL,
- TransDateTime DATETIME NOT NULL,
- CardNo VARCHAR(19) NOT NULL FOREIGN KEY REFERENCES CreditCard,
- );
- GO
- INSERT INTO CreditDebitTrans VALUES (1, 'Debit', 1.00, '2017-09-15', '0101010101010101');
- INSERT INTO CreditDebitTrans VALUES (2, 'Topup', 2.00, '2017-09-30', '0101010101010101');
- INSERT INTO CreditDebitTrans VALUES (3, 'Debit', 3.00, '2017-05-30', '0202020202020202');
- INSERT INTO CreditDebitTrans VALUES (4, 'Topup', 4.00, '2017-09-30', '0202020202020202');
- INSERT INTO CreditDebitTrans VALUES (5, 'Debit', 5.00, '2017-07-30', '0303030303030303');
- INSERT INTO CreditDebitTrans VALUES (6, 'Topup', 6.00, '2017-09-30', '0303030303030303');
- INSERT INTO CreditDebitTrans VALUES (7, 'Debit', 7.00, '2017-09-30', '0404040404040404');
- INSERT INTO CreditDebitTrans VALUES (8, 'Topup', 8.00, '2017-02-28', '0404040404040404');
- INSERT INTO CreditDebitTrans VALUES (9, 'Debit', 9.00, '2017-09-10', '0505050505050505');
- INSERT INTO CreditDebitTrans VALUES (10, 'Topup', 10.00, '2017-01-30', '0505050505050505');
- INSERT INTO CreditDebitTrans VALUES (11, 'Debit', 11.00, '2017-09-30', '0606060606060606');
- INSERT INTO CreditDebitTrans VALUES (12, 'Topup', 12.00, '2017-10-21', '0606060606060606');
- INSERT INTO CreditDebitTrans VALUES (13, 'Debit', 13.00, '2017-05-30', '0707070707070707');
- INSERT INTO CreditDebitTrans VALUES (14, 'Topup', 14.00, '2017-12-30', '0707070707070707');
- INSERT INTO CreditDebitTrans VALUES (15, 'Debit', 15.00, '2017-01-22', '0808080808080808');
- INSERT INTO CreditDebitTrans VALUES (16, 'Topup', 16.00, '2017-09-24', '0808080808080808');
- GO
- CREATE TABLE Ride (
- RideID BIGINT PRIMARY KEY,
- PtsRedeemed INT NULL,
- RidePts INT NOT NULL,
- Cost SMALLMONEY NOT NULL,
- Distance FLOAT NOT NULL,
- DateTimeStart DATETIME NOT NULL,
- DateTimeEnd DATETIME NOT NULL,
- MemberID BIGINT NOT NULL FOREIGN KEY REFERENCES Member,
- BikeID BIGINT NOT NULL FOREIGN KEY REFERENCES Bike,
- PromoCode CHAR(6) NULL FOREIGN KEY REFERENCES Promotion,
- Start BIGINT NOT NULL FOREIGN KEY REFERENCES BikeStop(BikeStopID),
- Finish BIGINT NOT NULL FOREIGN KEY REFERENCES BikeStop(BikeStopID),
- TransID BIGINT NULL FOREIGN KEY REFERENCES CreditDebitTrans,
- );
- GO
- INSERT INTO Ride VALUES (1, 50, 10, 0.10, 1.0, '2017-04-01 09:05', '2017-04-01 10:05', 1, 15, NULL, 1, 15, 1);
- INSERT INTO Ride VALUES (2, 100, 10, 0.20, 2.0, '2017-04-02 08:05', '2017-04-02 10:05', 2, 14, 'AAAAAA', 2, 14, 1);
- INSERT INTO Ride VALUES (3, NULL, 10, 0.30, 3.0, '2017-05-01 09:15', '2017-05-01 10:05', 3, 13, NULL, 3, 13, 1);
- INSERT INTO Ride VALUES (4, 100, 10, 0.40, 4.0, '2017-08-07 07:00', '2017-08-07 23:10', 4, 12, NULL, 4, 12, 1);
- INSERT INTO Ride VALUES (5, 50, 10, 0.50, 5.0, '2017-04-24 09:25', '2017-04-24 10:05', 5, 11, NULL, 5, 11, 1);
- INSERT INTO Ride VALUES (6, NULL, 10, 0.60, 6.0, '2017-05-22 13:05', '2017-05-22 16:05', 6, 10, 'CCCCCC', 6, 10, 1);
- INSERT INTO Ride VALUES (7, 100, 10, 0.70, 7.0, '2017-04-16 09:55', '2017-04-16 13:05', 7, 9, NULL, 7, 9, 1);
- INSERT INTO Ride VALUES (8, NULL, 10, 0.80, 8.0, '2017-11-18 14:00', '2017-11-18 14:05', 8, 8, 'DDDDDD', 8, 8, 1);
- INSERT INTO Ride VALUES (9, NULL, 10, 0.90, 9.0, '2017-04-28 18:35', '2017-04-28 19:05', 9, 7, NULL, 9, 7, 1);
- INSERT INTO Ride VALUES (10, 50, 10, 1.00, 10.0, '2017-12-15 16:05', '2017-12-15 16:45', 10, 6, 'EEEEEE', 10, 6, 1);
- INSERT INTO Ride VALUES (11, NULL, 10, 1.10, 11.0, '2017-04-10 09:35', '2017-04-10 10:05', 11, 5, NULL, 11, 5, 1);
- INSERT INTO Ride VALUES (12, 50, 10, 1.20, 12.0, '2017-02-21 17:00', '2017-02-21 17:25', 12, 4, NULL, 12, 4, 1);
- INSERT INTO Ride VALUES (13, NULL, 10, 1.30, 13.0, '2017-05-08 19:55', '2017-05-08 20:35', 13, 3, 'FFFFFF', 13, 3, 1);
- INSERT INTO Ride VALUES (14, NULL, 10, 1.40, 14.0, '2017-12-03 20:15', '2017-12-03 20:15', 14, 2, NULL, 14, 2, 1);
- INSERT INTO Ride VALUES (15, NULL, 10, 1.50, 15.0, '2017-03-20 09:05', '2017-03-20 10:05', 15, 1, NULL, 15, 1, 1);
- GO
- CREATE TABLE Maintenance (
- MaintID BIGINT PRIMARY KEY,
- MaintDate DATETIME NOT NULL,
- BikeID BIGINT NOT NULL FOREIGN KEY REFERENCES Bike,
- );
- GO
- INSERT INTO Maintenance VALUES(1, '2017-01-01', 1);
- INSERT INTO Maintenance VALUES(2, '2017-02-01', 2);
- INSERT INTO Maintenance VALUES(3, '2017-03-01', 3);
- INSERT INTO Maintenance VALUES(4, '2017-04-01', 4);
- INSERT INTO Maintenance VALUES(5, '2017-05-01', 5);
- INSERT INTO Maintenance VALUES(6, '2017-06-01', 6);
- INSERT INTO Maintenance VALUES(7, '2017-07-01', 7);
- INSERT INTO Maintenance VALUES(8, '2017-08-01', 8);
- INSERT INTO Maintenance VALUES(9, '2017-09-01', 9);
- INSERT INTO Maintenance VALUES(10, '2017-10-01', 10);
- INSERT INTO Maintenance VALUES(11, '2017-11-01', 11);
- INSERT INTO Maintenance VALUES(12, '2017-12-01', 12);
- INSERT INTO Maintenance VALUES(13, '2018-01-01', 13);
- INSERT INTO Maintenance VALUES(14, '2018-02-01', 14);
- INSERT INTO Maintenance VALUES(15, '2018-03-01', 15);
- GO
- CREATE TABLE Repair (
- MaintID BIGINT PRIMARY KEY FOREIGN KEY REFERENCES Maintenance,
- );
- GO
- INSERT INTO Repair VALUES(1);
- INSERT INTO Repair VALUES(2);
- INSERT INTO Repair VALUES(3);
- INSERT INTO Repair VALUES(4);
- INSERT INTO Repair VALUES(5);
- INSERT INTO Repair VALUES(6);
- INSERT INTO Repair VALUES(7);
- INSERT INTO Repair VALUES(8);
- INSERT INTO Repair VALUES(9);
- INSERT INTO Repair VALUES(10);
- INSERT INTO Repair VALUES(11);
- INSERT INTO Repair VALUES(12);
- INSERT INTO Repair VALUES(13);
- INSERT INTO Repair VALUES(14);
- INSERT INTO Repair VALUES(15);
- GO
- CREATE TABLE MaintType (
- TypeCode INT PRIMARY KEY,
- TypeDesc TEXT NULL,
- );
- GO
- INSERT INTO MaintType VALUES(1, 'Description1');
- INSERT INTO MaintType VALUES(2, 'Description2');
- INSERT INTO MaintType VALUES(3, 'Description3');
- INSERT INTO MaintType VALUES(4, 'Description4');
- INSERT INTO MaintType VALUES(5, 'Description5');
- INSERT INTO MaintType VALUES(6, 'Description6');
- INSERT INTO MaintType VALUES(7, 'Description7');
- INSERT INTO MaintType VALUES(8, 'Description8');
- INSERT INTO MaintType VALUES(9, 'Description9');
- INSERT INTO MaintType VALUES(10, 'Description10');
- INSERT INTO MaintType VALUES(11, 'Description11');
- INSERT INTO MaintType VALUES(12, 'Description12');
- INSERT INTO MaintType VALUES(13, 'Description13');
- INSERT INTO MaintType VALUES(14, 'Description14');
- INSERT INTO MaintType VALUES(15, 'Description15');
- GO
- CREATE TABLE MaintItem (
- MaintID BIGINT NOT NULL FOREIGN KEY REFERENCES Maintenance,
- SNo BIGINT NOT NULL,
- Descr TEXT NULL,
- TypeCode INT NOT NULL FOREIGN KEY REFERENCES MaintType
- CONSTRAINT PK_MaintItem PRIMARY KEY (MaintID, SNo),
- );
- GO
- INSERT INTO MaintItem VALUES(1, 1 , 'Description1', 1);
- INSERT INTO MaintItem VALUES(2, 2 , 'Description2', 2);
- INSERT INTO MaintItem VALUES(3, 3 , 'Description3', 3);
- INSERT INTO MaintItem VALUES(4, 4 , 'Description4', 4);
- INSERT INTO MaintItem VALUES(5, 5 , 'Description5', 5);
- INSERT INTO MaintItem VALUES(6, 6 , 'Description6', 6);
- INSERT INTO MaintItem VALUES(7, 7 , 'Description7', 7);
- INSERT INTO MaintItem VALUES(8, 8 , 'Description8', 8);
- INSERT INTO MaintItem VALUES(9, 9 , 'Description9', 9);
- INSERT INTO MaintItem VALUES(10, 10, 'Description10', 10);
- INSERT INTO MaintItem VALUES(11, 11, 'Description11', 11);
- INSERT INTO MaintItem VALUES(12, 12, 'Description12', 12);
- INSERT INTO MaintItem VALUES(13, 13, 'Description13', 13);
- INSERT INTO MaintItem VALUES(14, 14, 'Description14', 14);
- INSERT INTO MaintItem VALUES(15, 15, 'Description15', 15);
- GO
- CREATE TABLE Feedback (
- FBID BIGINT PRIMARY KEY,
- FBDateTime DATETIME NOT NULL,
- MemberID BIGINT NOT NULL FOREIGN KEY REFERENCES Member,
- );
- GO
- INSERT INTO Feedback VALUES(1, '2017-01-01', 1);
- INSERT INTO Feedback VALUES(2, '2017-02-01', 2);
- INSERT INTO Feedback VALUES(3, '2017-03-01', 3);
- INSERT INTO Feedback VALUES(4, '2017-04-01', 4);
- INSERT INTO Feedback VALUES(5, '2017-05-01', 5);
- INSERT INTO Feedback VALUES(6, '2017-06-01', 6);
- INSERT INTO Feedback VALUES(7, '2017-07-01', 7);
- INSERT INTO Feedback VALUES(8, '2017-08-01', 8);
- INSERT INTO Feedback VALUES(9, '2017-09-01', 9);
- INSERT INTO Feedback VALUES(10, '2017-10-01', 10);
- INSERT INTO Feedback VALUES(11, '2017-11-01', 11);
- INSERT INTO Feedback VALUES(12, '2017-12-01', 12);
- INSERT INTO Feedback VALUES(13, '2018-01-01', 13);
- INSERT INTO Feedback VALUES(14, '2018-02-01', 14);
- INSERT INTO Feedback VALUES(15, '2018-03-01', 15);
- GO
- CREATE TABLE Comment (
- FBID BIGINT PRIMARY KEY FOREIGN KEY REFERENCES Feedback,
- Message TEXT NOT NULL,
- );
- GO
- INSERT INTO Comment VALUES(1, 'Message1');
- INSERT INTO Comment VALUES(2, 'Message2');
- INSERT INTO Comment VALUES(3, 'Message3');
- INSERT INTO Comment VALUES(4, 'Message4');
- INSERT INTO Comment VALUES(5, 'Message5');
- INSERT INTO Comment VALUES(6, 'Message6');
- INSERT INTO Comment VALUES(7, 'Message7');
- INSERT INTO Comment VALUES(8, 'Message8');
- INSERT INTO Comment VALUES(9, 'Message9');
- INSERT INTO Comment VALUES(10, 'Message10');
- INSERT INTO Comment VALUES(11, 'Message11');
- INSERT INTO Comment VALUES(12, 'Message12');
- INSERT INTO Comment VALUES(13, 'Message13');
- INSERT INTO Comment VALUES(14, 'Message14');
- INSERT INTO Comment VALUES(15, 'Message15');
- GO
- CREATE TABLE Fault (
- FBID BIGINT PRIMARY KEY FOREIGN KEY REFERENCES Feedback,
- FaultCode SMALLINT NOT NULL FOREIGN KEY REFERENCES FaultType,
- MaintID BIGINT NULL FOREIGN KEY REFERENCES Repair,
- BikeID BIGINT NOT NULL FOREIGN KEY REFERENCES Bike,
- );
- GO
- INSERT INTO Fault VALUES (1, 1, 1, 1);
- INSERT INTO Fault VALUES (2, 2, 2, 2);
- INSERT INTO Fault VALUES (3, 3, 2, 2);
- INSERT INTO Fault VALUES (4, 4, 4, 4);
- INSERT INTO Fault VALUES (5, 5, 5, 5);
- INSERT INTO Fault VALUES (6, 6, 6, 6);
- INSERT INTO Fault VALUES (7, 7, 7, 7);
- INSERT INTO Fault VALUES (8, 8, 8, 8);
- INSERT INTO Fault VALUES (9, 9, 9, 9);
- INSERT INTO Fault VALUES (10, 10, 10, 10);
- INSERT INTO Fault VALUES (11, 11, 11, 11);
- INSERT INTO Fault VALUES (12, 12, 12, 12);
- INSERT INTO Fault VALUES (13, 13, 13, 13);
- INSERT INTO Fault VALUES (14, 14, 14, 14);
- INSERT INTO Fault VALUES (15, 15, 15, 15);
- GO
- CREATE TABLE Reservation (
- MemberID BIGINT FOREIGN KEY REFERENCES Member,
- BikeID BIGINT FOREIGN KEY REFERENCES Bike,
- ResStatus VARCHAR(15) NOT NULL CHECK (ResStatus IN ('Confirmed', 'Cancelled', 'NoShow')),
- DateTimeRes DATETIME NOT NULL,
- DateTimeReservedFor DATETIME NOT NULL,
- CONSTRAINT PK_Reservation PRIMARY KEY (MemberID, BikeID),
- CONSTRAINT CHK_DateTime CHECK (DateTimeRes < DateTimeReservedFor),
- )
- GO
- INSERT INTO Reservation VALUES(1, 1, 'Confirmed', '2017-01-01 08:05', '2017-01-01 09:05');
- INSERT INTO Reservation VALUES(2, 2, 'Cancelled', '2017-01-02 10:05', '2017-01-02 16:05');
- INSERT INTO Reservation VALUES(3, 3, 'NoShow', '2017-01-03 12:05', '2017-01-03 13:05');
- INSERT INTO Reservation VALUES(4, 4, 'Confirmed', '2017-01-04 13:05', '2017-01-04 15:05');
- INSERT INTO Reservation VALUES(5, 5, 'Confirmed', '2017-01-05 12:05', '2017-01-05 13:05');
- INSERT INTO Reservation VALUES(6, 6, 'Confirmed', '2017-01-06 13:05', '2017-01-06 14:05');
- INSERT INTO Reservation VALUES(7, 7, 'Confirmed', '2017-01-07 14:05', '2017-01-07 16:05');
- INSERT INTO Reservation VALUES(8, 8, 'Confirmed', '2017-01-08 12:05', '2017-01-08 16:05');
- INSERT INTO Reservation VALUES(9, 9, 'Confirmed', '2017-01-09 08:05', '2017-01-09 15:05');
- INSERT INTO Reservation VALUES(10, 10, 'Confirmed', '2017-01-10 08:05', '2017-01-10 14:05');
- INSERT INTO Reservation VALUES(11, 11, 'Confirmed', '2017-01-11 11:05', '2017-01-11 13:05');
- INSERT INTO Reservation VALUES(12, 12, 'Confirmed', '2017-01-12 12:05', '2017-01-12 13:05');
- INSERT INTO Reservation VALUES(13, 13, 'Confirmed', '2017-01-13 14:05', '2017-01-13 15:05');
- INSERT INTO Reservation VALUES(14, 14, 'Confirmed', '2017-01-14 13:05', '2017-01-14 15:05');
- INSERT INTO Reservation VALUES(15, 15, 'Confirmed', '2017-01-15 20:05', '2017-01-15 22:05');
- GO
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement