Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE master
- --Delete database if it exists--
- IF EXISTS(select * from sys.databases where name='BikeRentalSystem')
- DROP DATABASE BikeRentalSystem
- GO
- CREATE DATABASE BikeRentalSystem
- GO
- USE BikeRentalSystem
- --Delete tables if they exist--
- if exists(select * from sysobjects where id = object_id('CreditDebitTrans'))
- drop table CreditDebitTrans
- go
- if exists(select * from sysobjects where id = object_id('Ride'))
- drop table Ride
- go
- if exists(select * from sysobjects where id = object_id('BikeReservation'))
- drop table BikeReservation
- go
- if exists(select * from sysobjects where id = object_id('Fault'))
- drop table Fault
- go
- if exists(select * from sysobjects where id = object_id('Comment'))
- drop table Comment
- go
- if exists(select * from sysobjects where id = object_id('Feedback'))
- drop table Feedback
- go
- if exists(select * from sysobjects where id = object_id('Member'))
- drop table Member
- go
- if exists(select * from sysobjects where id = object_id('Bike'))
- drop table Bike
- go
- if exists(select * from sysobjects where id = object_id('BikeStop'))
- drop table BikeStop
- go
- if exists(select * from sysobjects where id = object_id('Promotion'))
- drop table Promotion
- go
- if exists(select * from sysobjects where id = object_id('FaultType'))
- drop table FaultType
- go
- if exists(select * from sysobjects where id = object_id('MaintItem'))
- drop table MaintItem
- go
- if exists(select * from sysobjects where id = object_id('MaintType'))
- drop table MaintType
- go
- if exists(select * from sysobjects where id = object_id('Repair'))
- drop table Repair
- go
- if exists(select * from sysobjects where id = object_id('Maintenance'))
- drop table Maintenance
- go
- if exists(select * from sysobjects where id = object_id('CreditCard'))
- drop table CreditCard
- go
- --Creating tables--
- Create table Member
- (
- MemberID char(6) NOT NULL,
- Name varchar(30) NOT NULL,
- Email varchar(40) NULL,
- Phone char(9) NOT NULL,
- RewardPts smallint NOT NULL DEFAULT(0),
- Deposit money NOT NULL DEFAULT(50),
- CreditBalance money NOT NULL DEFAULT(0),
- DateJoin date NOT NULL DEFAULT(GETDATE()),
- CONSTRAINT Pk_Member PRIMARY KEY(MemberID),
- --CONSTRAINT Chk_Phone CHECK(Phone like '^[689]\d{8}$'),
- --CONSTRAINT Chk_Email CHECK(Email like '^[\w\d]+@\D+(\.\D+)?(.com)(\.\D+)?$')
- )
- Create table BikeStop
- (
- BikeStopID char(2) NOT NULL,
- StopLat decimal(7,6) NOT NULL,
- StopLong decimal(9,6) NOT NULL,
- StopRadius smallint NOT NULL,
- CONSTRAINT Pk_BikeStop PRIMARY KEY(BikeStopID),
- CONSTRAINT Chk_StopRadius CHECK(StopRadius between 5 and 25)
- )
- Create table Bike
- (
- BikeID char(4) NOT NULL,
- Rate smallint NOT NULL,
- Status varchar(2) NOT NULL CHECK(Status IN ('W', 'NR', 'R')),
- --LastServicedDate datetime NOT NULL,
- --BikeStopID char(2) NOT NULL,
- CONSTRAINT Pk_Bike PRIMARY KEY(BikeID)
- --CONSTRAINT Fk_Bike_BikeStopID FOREIGN KEY (BikeStopID) REFERENCES BikeStop(BikeStopID)
- )
- Create table BikeReservation
- (
- ReservationID char(4) NOT NULL,
- MemberID char(6) NOT NULL,
- BikeID char(4) NOT NULL,
- ResStatus char(1) NOT NULL,
- DateTimeRes datetime NOT NULL DEFAULT(GETDATE()),
- DateTimeReservedFor datetime NOT NULL,
- CONSTRAINT Pk_ReservationID PRIMARY KEY(ReservationID),
- CONSTRAINT Fk_BikeReservation_MemberID FOREIGN KEY(MemberID) REFERENCES Member(MemberID),
- CONSTRAINT Fk_BikeReservation_BikeID FOREIGN KEY(BikeID) REFERENCES Bike(BikeID),
- CONSTRAINT Chk_ResStatus CHECK(ResStatus IN ('O', 'F')),
- CONSTRAINT Chk_DateTimeReservedFor CHECK(DateTimeReservedFor >= DateTimeRes)
- )
- Create table Promotion
- (
- PromoCode varchar(10) NOT NULL,
- PromoDesc varchar(50) NULL,
- PromoRate decimal(3,2) NOT NULL,
- StartDate date NOT NULL,
- EndDate date NOT NULL,
- CONSTRAINT Pk_Promotion PRIMARY KEY(PromoCode),
- CONSTRAINT Chk_StartDate CHECK(StartDate <= GETDATE()),
- CONSTRAINT Chk_EndDate CHECK(EndDate >= StartDate)
- )
- Create table Ride
- (
- RideID char(6) NOT NULL,
- DateTimeStart datetime NOT NULL,
- DateTimeEnd datetime NULL,
- Duration as DATEDIFF (minute, DateTimeStart, DateTimeEnd),
- RidePts Int NOT NULL DEFAULT(0),
- Distance Decimal(5,3) NULL,
- PtsRedeemed Int NOT NULL DEFAULT(0),
- Cost smallmoney NULL,
- MemberID char(6) NULL,
- BikeID char(4) NULL,
- BikeStopID char(2) NULL,
- PromoCode varchar(10) NULL,
- CONSTRAINT Pk_Ride PRIMARY KEY(RideID),
- CONSTRAINT Fk_Ride_MemberID FOREIGN KEY(MemberID) REFERENCES Member(MemberID),
- CONSTRAINT Fk_Ride_BikeID FOREIGN KEY(BikeID) REFERENCES Bike(BikeID),
- CONSTRAINT Fk_Ride_BikeStopID FOREIGN KEY(BikeStopID) REFERENCES BikeStop(BikeStopID),
- CONSTRAINT Fk_Ride_PromoCode FOREIGN KEY(PromoCode) REFERENCES Promotion(PromoCode),
- CONSTRAINT Chk_PtsRedeemed CHECK(PtsRedeemed % 50 = 0)
- )
- Create table Feedback
- (
- FBID char(5) NOT NULL,
- FBDateTime datetime NOT NULL DEFAULT(GETDATE()),
- MemberID char(6) NOT NULL,
- CONSTRAINT Pk_Feedback PRIMARY KEY(FBID),
- CONSTRAINT Fk_Feedback_MemberID FOREIGN KEY(MemberID) REFERENCES Member(MemberID)
- )
- Create table FaultType
- (
- FaultCode char(4) NOT NULL,
- FaultDesc varchar(500) NOT NULL,
- CONSTRAINT Pk_FaultType PRIMARY KEY(FaultCode),
- )
- Create table Maintenance
- (
- MaintID char(5) NOT NULL,
- MaintDate date NOT NULL,
- BikeID char(4) NOT NULL,
- CONSTRAINT Pk_Maintenance PRIMARY KEY(MaintID),
- CONSTRAINT Chk_MaintDate CHECK(MaintDate <= GETDATE()),
- CONSTRAINT Fk_Maintenance_BikeID FOREIGN KEY(BikeID) REFERENCES Bike(BikeID)
- )
- Create table Repair
- (
- MaintID char(5) NOT NULL,
- MaintDate date NOT NULL,
- BikeID char(4) NOT NULL,
- CONSTRAINT Pk_Repair PRIMARY KEY(MaintID),
- CONSTRAINT Fk_Repair_MaintID FOREIGN KEY(MaintID) REFERENCES Maintenance(MaintID),
- CONSTRAINT Chk_Repair_MaintDate CHECK(MaintDate <= GETDATE()),
- CONSTRAINT Fk_Repair_BikeID FOREIGN KEY(BikeID) REFERENCES Bike(BikeID)
- )
- Create table MaintType
- (
- TypeCode char(5) NOT NULL,
- TypeDesc varchar(50) NOT NULL,
- CONSTRAINT Pk_MaintType PRIMARY KEY (TypeCode)
- )
- Create table MaintItem
- (
- SNo char(2) NOT NULL,
- MaintID char(5) NOT NULL,
- MaintDesc varchar(100) NOT NULL,
- TypeCode char(5) NULL,
- CONSTRAINT Pk_MaintItem PRIMARY KEY (SNo, MaintID),
- CONSTRAINT Fk_MaintItem_MaintID FOREIGN KEY (MaintID) REFERENCES Maintenance (MaintID) ON DELETE CASCADE,
- CONSTRAINT Fk_MaintItem_TypeCode FOREIGN KEY (TypeCode) REFERENCES MaintType (TypeCode)
- )
- Create table Fault
- (
- FBID char(5) NOT NULL,
- FBDateTime varchar(40) NOT NULL,
- FaultCat varchar(15) NOT NULL,
- MemberID char(6) NOT NULL,
- FaultCode char(4) NULL,
- BikeID char(4) NOT NULL,
- MaintID char(5) NOT NULL,
- CONSTRAINT Pk_Fault PRIMARY KEY (FBID),
- CONSTRAINT FK_Fault_FBID FOREIGN KEY(FBID) REFERENCES Feedback(FBID),
- CONSTRAINT Fk_Fault_MemberID FOREIGN KEY (MemberID) REFERENCES Member (MemberID),
- CONSTRAINT Fk_Fault_FaultCode FOREIGN KEY(FaultCode) REFERENCES FaultType(FaultCode),
- CONSTRAINT Fk_Fault_BikeID FOREIGN KEY(BikeID) REFERENCES Bike(BikeID),
- CONSTRAINT Fk_Fault_MaintID FOREIGN KEY(MaintID) REFERENCES Maintenance(MaintID),
- CONSTRAINT Chk_Fault_FaultCat CHECK(FaultCat IN ('Tire', 'Pedal', 'Seat', 'Basket', 'Lock', 'Frame', 'Others'))
- )
- Create table Comment
- (
- FBID char(5) NOT NULL,
- FBDateTime datetime NOT NULL,
- FBMessage varchar(1000) NULL,
- MemberID char(6) NOT NULL,
- CONSTRAINT Pk_Comment PRIMARY KEY(FBID),
- CONSTRAINT Fk_Comment_FBID FOREIGN KEY(FBID) REFERENCES Feedback(FBID),
- CONSTRAINT Fk_Comment_MemberID FOREIGN KEY (MemberID) REFERENCES Member(MemberID),
- CONSTRAINT Chk_FBDateTime CHECK(FBDateTime <= GETDATE())
- )
- Create table CreditCard
- (
- CardNo char(6) NOT NULL,
- CardType varchar(30) NOT NULL,
- ExpiryDate date NOT NULL,
- CVV char(3) NOT NULL,
- MemberID char(6) NOT NULL
- CONSTRAINT Pk_CreditCard PRIMARY KEY(CardNo),
- CONSTRAINT Chk_ExpiryDate CHECK(ExpiryDate >= GETDATE()),
- CONSTRAINT Fk_MemberID FOREIGN KEY (MemberID) REFERENCES Member(MemberID),
- )
- Create table CreditDebitTrans
- (
- TransID char(6) NOT NULL,
- TransAmt smallmoney NOT NULL,
- TransType varchar(6) NOT NULL,
- TransDateTime datetime NOT NULL default(GETDATE()),
- CardNo char(6) NOT NULL,
- RideID char(6) NOT NULL,
- CONSTRAINT Pk_CreditDebitTrans PRIMARY KEY(TransID),
- CONSTRAINT Fk_CreditDebitTrans_CardNo FOREIGN KEY(CardNo) REFERENCES CreditCard(CardNo),
- CONSTRAINT Fk_CreditDebitTrans_RideID FOREIGN KEY(RideID) REFERENCES Ride(RideID),
- CONSTRAINT Chk_TransType CHECK(TransType in ('Credit','Debit'))
- )
- --Inserting values--
- insert into Bike(BikeID, Rate, Status) VALUES ('1AAA', 0.5, 'W')
- insert into Bike(BikeID, Rate, Status) VALUES ('2BBB', 0.5, 'W')
- insert into Bike(BikeID, Rate, Status) VALUES ('3CCC', 0.5, 'W')
- insert into Bike(BikeID, Rate, Status) VALUES ('4DDD', 0.5, 'R')
- insert into Bike(BikeID, Rate, Status) VALUES ('5EEE', 0.5, 'W')
- insert into Bike(BikeID, Rate, Status) VALUES ('6FFF', 0.5, 'W')
- insert into Bike(BikeID, Rate, Status) VALUES ('7GGG', 0.5, 'NR')
- insert into Bike(BikeID, Rate, Status) VALUES ('8HHH', 0.5, 'W')
- insert into Bike(BikeID, Rate, Status) VALUES ('9III', 0.5, 'W')
- insert into Bike(BikeID, Rate, Status) VALUES ('JJJ1', 0.5, 'W')
- insert into Bike(BikeID, Rate, Status) VALUES ('KKK2', 0.5, 'W')
- insert into Bike(BikeID, Rate, Status) VALUES ('LLL3', 0.5, 'NR')
- insert into Bike(BikeID, Rate, Status) VALUES ('MMM4', 0.5, 'W')
- insert into Bike(BikeID, Rate, Status) VALUES ('NNN5', 0.5, 'W')
- insert into Bike(BikeID, Rate, Status) VALUES ('OOO6', 0.5, 'W')
- insert into Member (MemberID, Name, Email, Phone, Deposit, CreditBalance, DateJoin) values ('11111B','John Bruce', 'jhonbruce@gmail.com', '90000001', 100, 200, '2017-08-01')
- insert into Member values ('22222C','Tom Harrison', 'tomharrison@gmail.com', '90000002', 15, 60, 50, '2017-01-03')
- insert into Member (MemberID, Name, Email, Phone, Deposit, CreditBalance, DateJoin) values ('33333D','Zachary Tan', 'zacharytan@gmail.com', '90000003', 80, 200, '2016-12-01')
- insert into Member (MemberID, Name, Email, Phone, RewardPts, CreditBalance, DateJoin) values ('44444E','Tammy Lee', 'tammylee@gmail.com', '90000004', 24, 200, '2016-05-01')
- insert into Member values ('55555F','Monica Cheng', 'monicacheng@gmail.com', '90000005', 29, 140, 350, '2015-10-15')
- insert into Member values ('66666G','Javier Tang', 'javiertang@gmail.com', '90000006', 33, 120, 400, '2015-06-01')
- insert into Member (MemberID, Name, Email, Phone, RewardPts) values ('77777H','Shaun Jacob', 'shaunjacob@gmail.com', '90000007', 38)
- insert into Member values ('88888I','William Johnson', 'willianjohnson@gmail.com', '90000008', 42, 150, 25, '2014-09-09')
- insert into Member (MemberID, Name, Email, Phone, RewardPts, Deposit, DateJoin) values ('99999J','James Bruce', 'jamesbruce@gmail.com', '90000009', 45, 170, '2013-06-09')
- insert into Member (MemberID, Name, Email, Phone, Deposit, DateJoin) values ('AAAAA1','Wayne', 'xxxwayne999@gmail.com', '80000000', 200, '2012-07-22')
- insert into Member (MemberID, Name, Email, Phone, RewardPts) values ('BBBBB2','Jennifer Pang', 'jenniferpang@gmail.com', '80000001', 51)
- insert into Member (MemberID, Name, Email, Phone, RewardPts, DateJoin) values ('CCCCC3','Ethan Bradberry', 'ethanbradberry@gmail.com', '80000002', 55, '2011-11-11')
- insert into Member values ('DDDDD4','Daniel Lim', 'daniellim@gmail.com', '80000003', 69, 170, 46, '2010-10-10')
- insert into Member (MemberID, Name, Email, Phone, CreditBalance, DateJoin) values ('EEEEE5','Lucas Paul', 'lucaspaul@gmail.com', '80000004', 100, '2009-09-09')
- insert into Member values ('FFFFF6', 'Jack Lee', 'jacklee99@yahoo.com', '80000005', 1, 55, 31, '2008-08-08')
- insert into Member values ('GGGGG7', 'Mary Tan', 'marytan@yahoo.com', '80000006', 10, 60, 12, '2017-10-18')
- insert into Member values ('HHHHH8', 'Tom Everson', 'tomeve@yahoo.com', '80000007', 12, 56, 15, '2017-08-28')
- insert into Member values ('IIIII9', 'Claudia Koh', 'claudia13@yahoo.com', '80000008', 21, 53, 35, '2008-09-17')
- insert into Member (MemberID, Name, Email, Phone, RewardPts, Deposit, DateJoin) values ('JJJJJ1', 'Nicholas Paris', 'parisnicalo@yahoo.com', '80000009', 34, 55, '2016-02-25')
- insert into Member (MemberID, Name, Email, Phone, RewardPts, CreditBalance, DateJoin) values ('KKKKK2', 'Jackson Yi', 'yi08@yahoo.com', '80000010', 56, 2, '2016-03-26')
- insert into BikeReservation values ('0001','55555F', '4DDD', 'F', '2011-04-04 04:04:04', '2011-04-04 04:34:04')
- insert into BikeReservation values ('0002','44444E', '5EEE', 'F', '2011-05-05 05:05:05', '2011-05-05 05:15:05')
- insert into BikeReservation values ('0003','77777H', '3CCC', 'F', '2010-03-03 03:03:03', '2010-03-03 03:23:03')
- insert into BikeReservation values ('0004','88888I', '9III', 'O', '2018-01-11 09:09:09', '2018-01-11 17:10:09')
- insert into BikeReservation values ('0005','CCCCC3', 'LLL3', 'O', '2018-01-10 11:11:11', '2018-01-10 11:15:20')
- insert into BikeReservation values ('0006','FFFFF6', '1AAA', 'F', '2008-01-01 01:01:01', '2008-01-01 01:11:01')
- insert into BikeReservation values ('0007','AAAAA1', 'NNN5', 'F', '2017-02-03 13:13:13', '2017-02-03 13:23:13')
- insert into BikeReservation values ('0008','EEEEE5', 'KKK2', 'F', '2014-11-11 10:10:10', '2014-11-11 10:20:10')
- insert into BikeReservation values ('0009','DDDDD4', 'OOO6', 'F', '2017-03-04 14:14:14', '2017-03-04 14:54:14')
- insert into BikeReservation values ('0010','55555F', '6FFF', 'O', '2018-01-10 06:06:06', '2018-01-11 18:16:06')
- insert into BikeReservation values ('0011','99999J', 'JJJ1', 'F', '2014-10-10 09:09:10', '2014-10-10 09:09:20')
- insert into BikeReservation values ('0012','66666G', '7GGG', 'F', '2013-07-07 07:07:07', '2013-07-07 07:27:07')
- insert into BikeReservation values ('0013','77777H', '8HHH', 'F', '2013-08-08 08:08:08', '2013-08-08 08:28:08')
- insert into BikeReservation values ('0014','BBBBB2', 'MMM4', 'F', '2017-01-02 12:12:12', '2017-01-02 12:15:12')
- insert into BikeReservation values ('0015','11111B', '2BBB', 'F', '2009-02-02 02:02:02', '2009-02-02 02:12:02')
- insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AA',1.335512, 103.962087, 25)
- insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AB',1.340790, 103.950886, 20)
- insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AC',1.345273, 103.939234, 15)
- insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AD',1.334727, 103.764290, 10)
- insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AE',1.359096, 103.893723, 5)
- insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AF',1.366304, 103.947624, 25)
- insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AG',1.446274, 103.823685, 10)
- insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AH',1.394791, 103.906769, 15)
- insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AI',1.312760, 103.767380, 25)
- insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AJ',1.327176, 103.688416, 25)
- insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AK',1.444214, 103.788666, 20)
- insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AL',1.392389, 103.904709, 10)
- insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AM',1.381405, 103.890633, 5)
- insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AN',1.377630, 103.879646, 5)
- insert into BikeStop(BikeStopID, StopLat, StopLong, StopRadius) values ('AO',1.328892, 103.744720, 25)
- insert into Promotion values ('Promo01', 'Get 5% off of total price of the ride', 0.05, '2008-01-01', '2008-03-04')
- insert into Promotion values ('Promo02', 'Get 25% off of total price of the ride', 0.25, '2008-04-11', '2008-06-19')
- insert into Promotion values ('Promo03', 'Get 30% off of total price of the ride', 0.30, '2008-08-01', '2018-12-09')
- insert into Promotion values ('Promo04', 'Get 20% off of total price of the ride', 0.20, '2009-02-13', '2009-04-01')
- insert into Promotion values ('Promo05', 'Get 10% off of total price of the ride', 0.10, '2009-06-01', '2009-08-20')
- insert into Promotion values ('Promo06', 'Get 30% off of total price of the ride', 0.30, '2010-01-01', '2010-02-01')
- insert into Promotion values ('Promo07', 'Get 60% off of total price of the ride', 0.60, '2010-02-28', '2010-06-30')
- insert into Promotion values ('Promo08', 'Get 80% off of total price of the ride', 0.80, '2011-08-21', '2011-09-01')
- insert into Promotion values ('Promo09', 'Get 90% off of total price of the ride', 0.90, '2011-08-10', '2011-12-14')
- insert into Promotion values ('Promo10', 'Get 10% off of total price of the ride', 0.10, '2011-10-15', '2011-12-16')
- insert into Promotion values ('Promo11', 'Get 15% off of total price of the ride', 0.15, '2012-06-23', '2012-12-31')
- insert into Promotion values ('Promo12', 'Get 33% off of total price of the ride', 0.33, '2013-02-11', '2013-04-01')
- insert into Promotion values ('Promo13', 'Get 25% off of total price of the ride', 0.25, '2014-01-05', '2014-08-07')
- insert into Promotion values ('Promo14', 'Get 55% off of total price of the ride', 0.55, '2015-11-03', '2016-03-18')
- insert into Promotion values ('Promo15', 'Get 75% off of total price of the ride', 0.75, '2017-06-20', '2018-01-02')
- INSERT INTO Ride VALUES('1AAAAA', '2008-01-01 01:11:01', '2008-01-01 01:31:01', 20, 5.00, 0, 0.50, 'FFFFF6', '1AAA', 'AA', 'Promo01')
- INSERT INTO Ride VALUES('1BBBBB', '2008-03-04 13:01:01', '2008-03-04 13:31:01', 30, 6.50, 0, 0.50, '11111B', '2BBB', 'AC', 'Promo02')
- INSERT INTO Ride VALUES('1CCCCC', '2009-04-05 13:30:00', '2009-04-05 14:10:00', 40, 7.00, 0, 0.50, 'IIIII9', '3CCC', 'AD', Null)
- INSERT INTO Ride VALUES('1DDDDD', '2010-05-06 14:20:00', '2010-05-06 14:10:00', 50, 8.50, 50, 0.50, '33333D', '4DDD', 'AE', 'Promo07')
- INSERT INTO Ride VALUES('1EEEEE', '2010-09-01 16:35:00', '2010-09-01 17:30:00', 55, 9.00, 50, 0.50, '44444E', '1AAA', 'AG', 'Promo08')
- INSERT INTO Ride VALUES('1FFFFF', '2011-04-04 04:34:04', '2011-04-04 05:34:04', 60, 10.00, 50, 0.50, '55555F', '5EEE', 'AH', 'Promo10')
- INSERT INTO Ride VALUES('1GGGGG', '2013-08-08 08:28:08', '2013-08-08 08:43:08', 15, 4.50, 0, 0.50, 'GGGGG7', '7GGG', 'AJ', Null)
- INSERT INTO Ride VALUES('1HHHHH', '2011-07-07 09:00:00', '2011-07-07 09:10:00', 10, 4.00, 0, 0.50, '77777H', '9III', 'AK', 'Promo08')
- INSERT INTO Ride VALUES('1IIIII', '2012-06-06 06:16:06', '2012-06-06 01:46:06', 30, 6.35, 0, 0.50, '88888I', 'LLL3', 'AM', 'Promo11')
- INSERT INTO Ride VALUES('1JJJJJ', '2014-10-10 09:09:20', '2014-10-10 09:49:46', 40, 7.10, 0, 0.50, '99999J', 'JJJ1', 'AO', 'Promo08')
- INSERT INTO Ride VALUES('1KKKKK', '2013-01-01 01:01:01', '2013-01-01 02:01:01', 60, 10.10, 50, 0.50, 'AAAAA1', 'OOO6', 'AN', 'Promo09')
- INSERT INTO Ride VALUES('1LLLLL', '2014-01-01 01:01:01', '2014-01-01 01:51:01', 50, 9.45, 50, 0.50, 'BBBBB2', 'NNN5', 'AM', 'Promo13')
- INSERT INTO Ride VALUES('1MMMMM', '2015-01-01 01:01:01', '2015-01-01 03:01:01', 120, 21.10, 100, 1.40, 'HHHHH8', '8HHH', 'AA', Null)
- INSERT INTO Ride VALUES('1NNNNN', '2017-03-04 14:54:14', '2017-03-04 15:19:14', 25, 6.00, 0, 0.50, 'DDDDD4', 'OOO6', 'AB', Null)
- INSERT INTO Ride VALUES('1OOOOO', '2014-11-11 10:20:10', '2014-11-11 10:30:10', 10, 4.25, 0, 0.50, 'EEEEE5', 'KKK2', 'AC', Null)
- INSERT INTO Ride VALUES('1PPPPP', '2013-01-01 01:01:01', '2013-01-01 02:01:01', 60, 10.10, 50, 0.50, 'AAAAA1', 'OOO6', 'AN', Null)
- INSERT INTO Ride VALUES('1QQQQQ', '2014-01-01 01:01:01', '2014-01-01 01:51:01', 50, 9.45, 50, 0.50, 'GGGGG7', 'NNN5', 'AM', 'Promo13')
- INSERT INTO Feedback VALUES ('00001', '2017-03-04 00:14:14', 'FFFFF6')
- INSERT INTO Feedback VALUES ('00002', '2010-01-07 12:59:12', '22222C')
- INSERT INTO Feedback VALUES ('00003', '2016-12-14 02:49:45', '11111B')
- INSERT INTO Feedback VALUES ('00004', '2014-02-24 18:17:10', '33333D')
- INSERT INTO Feedback VALUES ('00005', '2015-02-15 19:37:20', 'FFFFF6')
- INSERT INTO Feedback VALUES ('00006', '2013-11-25 20:18:30', 'AAAAA1')
- INSERT INTO Feedback VALUES ('00007', '2017-05-05 11:58:40', '44444E')
- INSERT INTO Feedback VALUES ('00008', '2012-07-03 22:28:50', '66666G')
- INSERT INTO Feedback VALUES ('00009', '2012-04-30 19:54:12', '77777H')
- INSERT INTO Feedback VALUES ('00010', '2011-03-29 10:34:14', 'FFFFF6')
- INSERT INTO Feedback VALUES ('00011', '2017-11-19 03:45:17', '55555F')
- INSERT INTO Feedback VALUES ('00012', '2016-05-01 04:12:56', '55555F')
- INSERT INTO Feedback VALUES ('00013', '2011-01-03 11:27:45', '11111B')
- INSERT INTO Feedback VALUES ('00014', '2015-01-14 07:17:34', 'EEEEE5')
- INSERT INTO Feedback VALUES ('00015', '2012-04-02 09:09:21', 'BBBBB2')
- INSERT INTO Feedback VALUES('10001','2017-02-10','HHHHH8')
- INSERT INTO Feedback VALUES('20002','2017-05-13','CCCCC3')
- INSERT INTO Feedback VALUES('30003','2016-03-26','GGGGG7')
- INSERT INTO Feedback VALUES('40004','2016-07-17','66666G')
- INSERT INTO Feedback VALUES('50005','2015-07-06','AAAAA1')
- INSERT INTO Feedback VALUES('60006','2015-10-09','HHHHH8')
- INSERT INTO Feedback VALUES('70007','2014-01-19','CCCCC3')
- INSERT INTO Feedback VALUES('80008','2014-04-03','99999J')
- INSERT INTO Feedback VALUES('90009','2013-02-20','JJJJJ1')
- INSERT INTO Feedback VALUES('11011','2013-07-21','HHHHH8')
- INSERT INTO Feedback VALUES('22022','2012-08-26','BBBBB2')
- INSERT INTO Feedback VALUES('33033','2012-02-14','IIIII9')
- INSERT INTO Feedback VALUES('44044','2011-03-18','HHHHH8')
- INSERT INTO Feedback VALUES('55055','2011-06-22','IIIII9')
- INSERT INTO Feedback VALUES('66066','2010-04-22','CCCCC3')
- INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00001', '2017-03-04 00:14:14', 'Message01', 'FFFFF6')
- INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00002', '2010-01-07 12:59:12', 'Message02', '22222C')
- INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00003', '2016-12-14 02:49:45', 'Message03', '11111B')
- INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00004', '2014-02-24 18:17:10', 'Message04', '33333D')
- INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00005', '2015-02-15 19:37:20', 'Message05', 'FFFFF6')
- INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00006', '2013-11-25 20:18:30', 'Message06', 'AAAAA1')
- INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00007', '2017-08-05 11:58:40', 'Message07', '44444E')
- INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00008', '2012-07-03 22:28:50', 'Message08', '66666G')
- INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00009', '2012-04-30 19:54:12', 'Message09', '77777H')
- INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00010', '2011-03-29 10:34:14', 'Message10', 'FFFFF6')
- INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00011', '2017-11-19 03:45:17', 'Message11', '55555F')
- INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00012', '2016-12-01 04:12:56', 'Message12', '55555F')
- INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00013', '2011-01-03 11:27:45', 'Message13', '11111B')
- INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00014', '2015-01-14 07:17:34', 'Message14', 'EEEEE5')
- INSERT INTO Comment(FBID, FBDateTime, FBMessage, MemberID) VALUES('00015', '2012-04-02 09:09:21', 'Message15', 'BBBBB2')
- INSERT INTO FaultType VALUES('TI-1','Tires are punctured')
- INSERT INTO FaultType VALUES('TI-2','Tires are worn out')
- INSERT INTO FaultType VALUES('TI-3','Tires misshaped')
- INSERT INTO FaultType VALUES('BA-1','A part of the basket is broken off')
- INSERT INTO FaultType VALUES('BA-2','Basket has been misshaped by force')
- INSERT INTO FaultType VALUES('BA-3','Basket is almost / already detached from the rest of the bike' )
- INSERT INTO FaultType VALUES('SE-1','Seat covering is torn off (or a portion of it)')
- INSERT INTO FaultType VALUES('SE-2','Seat is physically damaged')
- INSERT INTO FaultType VALUES('LO-1','Lock is broken, completely insecure')
- INSERT INTO FaultType VALUES('LO-2','Lock does not work')
- INSERT INTO FaultType VALUES('PE-1','Pedal(s) is / are loose')
- INSERT INTO FaultType VALUES('PE-2','Pedal(s) is / are missing')
- INSERT INTO FaultType VALUES('FR-1','Frame is dented in')
- INSERT INTO FaultType VALUES('FR-2','Paint on frame has been scraped off')
- INSERT INTO FaultType VALUES('OO-1','Bicycle is unlocked')
- INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0001', '2010-06-01', '1AAA')
- INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0002', '2009-02-02', '2BBB')
- INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0003', '2010-03-03', '3CCC')
- INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0004', '2012-04-15', '4DDD')
- INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0005', '2011-05-05', '5EEE')
- INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0006', '2015-12-19', 'JJJ1')
- INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0007', '2013-07-07', '1AAA')
- INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0008', '2014-04-01', '9III')
- INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0009', '2014-03-19', 'KKK2')
- INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0010', '2016-10-10', '1AAA')
- INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0011', '2017-03-29', 'OOO6')
- INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0012', '2010-12-12', 'KKK2')
- INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0013', '2017-05-01', 'NNN5')
- INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0014', '2011-02-02', 'MMM4')
- INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0015', '2012-03-03', '8HHH')
- INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0016', '2013-07-30', '6FFF')
- INSERT INTO Maintenance(MaintID, MaintDate, BikeID) VALUES ('A0017', '2011-05-25', '3CCC')
- INSERT INTO Fault VALUES('10001','2017-02-10','tire', 'HHHHH8','TI-1','OOO6', 'A0015')
- INSERT INTO Fault VALUES('20002','2017-05-13','basket', 'CCCCC3','BA-1','NNN5', 'A0014')
- INSERT INTO Fault VALUES('30003','2016-03-26', 'lock', 'GGGGG7','TI-2','MMM4', 'A0013')
- INSERT INTO Fault VALUES('40004','2016-07-17', 'others', '66666G','SE-1','LLL3', 'A0012')
- INSERT INTO Fault VALUES('50005','2015-07-06', 'pedal', 'AAAAA1','LO-1','KKK2', 'A0011')
- INSERT INTO Fault VALUES('60006','2015-10-09', 'frame', 'HHHHH8','LO-2','JJJ1', 'A0010')
- INSERT INTO Fault VALUES('70007','2014-01-19', 'basket', 'CCCCC3','BA-2','9III', 'A0009')
- INSERT INTO Fault VALUES('80008','2014-04-03', 'tire', '99999J','PE-1','8HHH', 'A0008')
- INSERT INTO Fault VALUES('90009','2013-02-20', 'frame', 'JJJJJ1','OO-1','7GGG', 'A0007')
- INSERT INTO Fault VALUES('11011','2013-07-21', 'basket', 'HHHHH8','SE-2','6FFF', 'A0006')
- INSERT INTO Fault VALUES('22022','2012-08-26', 'pedal', 'BBBBB2','TI-3','5EEE', 'A0005')
- INSERT INTO Fault VALUES('33033','2012-02-14', 'tire', 'IIIII9','FR-2','4DDD', 'A0004')
- INSERT INTO Fault VALUES('44044','2011-03-18', 'others', 'HHHHH8','PE-2','3CCC', 'A0003')
- INSERT INTO Fault VALUES('55055','2011-06-22', 'lock', 'IIIII9','BA-3','2BBB', 'A0002')
- INSERT INTO Fault VALUES('66066','2010-04-22', 'basket', 'CCCCC3','FR-1','1AAA', 'A0001')
- INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA01', 'TDesc01')
- INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA02', 'TDesc02')
- INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA03', 'TDesc03')
- INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA04', 'TDesc04')
- INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA05', 'TDesc05')
- INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA06', 'TDesc06')
- INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA07', 'TDesc07')
- INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA08', 'TDesc08')
- INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA09', 'TDesc09')
- INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA10', 'TDesc10')
- INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA11', 'TDesc11')
- INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA12', 'TDesc12')
- INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA13', 'TDesc13')
- INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA14', 'TDesc14')
- INSERT INTO MaintType(TypeCode, TypeDesc) VALUES ('AAA15', 'TDesc15')
- INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('01', 'A0015', 'Mdesc01', 'AAA15')
- INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('02', 'A0014', 'Mdesc04', 'AAA14')
- INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('03', 'A0013', 'Mdesc05', 'AAA13')
- INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('04', 'A0012', 'Mdesc06', 'AAA12')
- INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('05', 'A0011', 'Mdesc07', 'AAA11')
- INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('06', 'A0010', 'Mdesc02', 'AAA10')
- INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('07', 'A0009', 'Mdesc03', 'AAA09')
- INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('08', 'A0008', 'Mdesc02', 'AAA08')
- INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('09', 'A0007', 'Mdesc01', 'AAA07')
- INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('10', 'A0006', 'Mdesc05', 'AAA06')
- INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('11', 'A0005', 'Mdesc08', 'AAA05')
- INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('12', 'A0004', 'Mdesc10', 'AAA04')
- INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('13', 'A0003', 'Mdesc06', 'AAA03')
- INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('14', 'A0002', 'Mdesc04', 'AAA02')
- INSERT INTO MaintItem(SNo, MaintID, MaintDesc, TypeCode) VALUES ('15', 'A0001', 'Mdesc07', 'AAA01')
- INSERT INTO CreditCard VALUES('Card01', 'Visa', '2019-01-01', '000', 'FFFFF6')
- INSERT INTO CreditCard VALUES('Card02', 'Master', '2020-01-01', '001', '66666G')
- INSERT INTO CreditCard VALUES('Card03', 'Diners', '2021-01-01', '002', 'FFFFF6')
- INSERT INTO CreditCard VALUES('Card04', 'Master', '2021-01-01', '003', 'BBBBB2')
- INSERT INTO CreditCard VALUES('Card05', 'Express', '2022-01-01', '004', '22222C')
- INSERT INTO CreditCard VALUES('Card06', 'Chase', '2022-01-01', '005', '77777H')
- INSERT INTO CreditCard VALUES('Card07', 'Visa', '2022-01-01', '006', 'EEEEE5')
- INSERT INTO CreditCard VALUES('Card08', 'Visa', '2022-01-01', '007', '11111B')
- INSERT INTO CreditCard VALUES('Card09', 'Diners', '2022-01-01', '008', '55555F')
- INSERT INTO CreditCard VALUES('Card10', 'Express', '2022-01-01', '009', '11111B')
- INSERT INTO CreditCard VALUES('Card11', 'Visa', '2022-01-01', '010', '55555F')
- INSERT INTO CreditCard VALUES('Card12', 'Visa', '2022-01-01', '011', '44444E')
- INSERT INTO CreditCard VALUES('Card13', 'Visa', '2019-01-01', '012', 'FFFFF6')
- INSERT INTO CreditCard VALUES('Card14', 'Master', '2019-01-01', '013', '33333D')
- INSERT INTO CreditCard VALUES('Card15', 'Chase', '2019-01-01', '014', 'AAAAA1')
- INSERT INTO CreditDebitTrans VALUES ('A00001', '1.50', 'Debit', '2018-01-01 01:31:01', 'card01', '1AAAAA')
- INSERT INTO CreditDebitTrans VALUES ('A00002', '1.00', 'Credit', '2008-03-04 13:31:01', 'card11', '1BBBBB')
- INSERT INTO CreditDebitTrans VALUES ('A00003', '0.50', 'Debit', '2009-04-05 14:10:00', 'card03', '1CCCCC')
- INSERT INTO CreditDebitTrans VALUES ('A00004', '2.00', 'Credit', '2017-09-06 14:10:00', 'card08', '1DDDDD')
- INSERT INTO CreditDebitTrans VALUES ('A00005', '1.50', 'Debit', '2017-09-01 17:30:00', 'card02', '1EEEEE')
- INSERT INTO CreditDebitTrans VALUES ('A00006', '1.00', 'Credit', '2011-04-04 05:34:04', 'card10', '1FFFFF')
- INSERT INTO CreditDebitTrans VALUES ('A00007', '1.50', 'Debit', '2013-08-08 08:43:08', 'card01', '1GGGGG')
- INSERT INTO CreditDebitTrans VALUES ('A00008', '2.00', 'Credit', '2011-07-07 09:10:00', 'card05', '1HHHHH')
- INSERT INTO CreditDebitTrans VALUES ('A00009', '1.00', 'Debit', '2012-06-06 01:46:06', 'card06', '1IIIII')
- INSERT INTO CreditDebitTrans VALUES ('A00010', '0.50', 'Credit', '2014-10-10 09:49:46', 'card07', '1JJJJJ')
- INSERT INTO CreditDebitTrans VALUES ('A00011', '0.50', 'Debit', '2013-01-01 02:01:01', 'card08', '1KKKKK')
- INSERT INTO CreditDebitTrans VALUES ('A00012', '0.50', 'Credit', '2013-01-01 02:01:01', 'card09', '1LLLLL')
- INSERT INTO CreditDebitTrans VALUES ('A00013', '2.50', 'Debit', '2017-01-01 03:01:01', 'card10', '1MMMMM')
- INSERT INTO CreditDebitTrans VALUES ('A00014', '1.50', 'Credit', '2017-11-04 15:19:14', 'card14', '1NNNNN')
- INSERT INTO CreditDebitTrans VALUES ('A00015', '0.50', 'Debit', '2014-11-11 10:30:10', 'card13', '1OOOOO')
- INSERT INTO CreditDebitTrans VALUES ('A00016', '1.50', 'Debit', '2018-02-20 02:01:01', 'card15', '1PPPPP')
- INSERT INTO CreditDebitTrans VALUES ('A00017', '2.50', 'Credit', '2014-01-01 01:51:01', 'card07', '1QQQQQ')
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement