/*records of table manager*/
INSERT [manager] ([manager_id], [m_name], [m_sex], [m_age], [m_number]) VALUES (1, N'Susan', N'female', 45, 5432)
INSERT [manager] ([manager_id], [m_name], [m_sex], [m_age], [m_number]) VALUES (2, N'Lili', N'man', 40, 67890)
INSERT [manager] ([manager_id], [m_name], [m_sex], [m_age], [m_number]) VALUES (3, N'Koko', N'man', 50, 76543)
INSERT [manager] ([manager_id], [m_name], [m_sex], [m_age], [m_number]) VALUES (4, N'Nana', N'female', 47, 65432)

/*records of table client*/
INSERT [client] ([client_id], [c_name], [c_sex], [c_age], [c_number], [c_address]) VALUES (1, N'lina', N'female    ', N'30        ', 65432, N'The north 2')
INSERT [client] ([client_id], [c_name], [c_sex], [c_age], [c_number], [c_address]) VALUES (2, N'zhangsan', N'man       ', N'28        ', 876543, N'The north 3')
INSERT [client] ([client_id], [c_name], [c_sex], [c_age], [c_number], [c_address]) VALUES (3, N'lisi', N'female    ', N'29        ', 98765, N'The north 4')
INSERT [client] ([client_id], [c_name], [c_sex], [c_age], [c_number], [c_address]) VALUES (4, N'koko', N'female    ', N'31        ', 537987, N'The north 5')
INSERT [client] ([client_id], [c_name], [c_sex], [c_age], [c_number], [c_address]) VALUES (5, N'Harley', N'man       ', N'45        ', 765433, N'Chengbei Sixth Road')
INSERT [client] ([client_id], [c_name], [c_sex], [c_age], [c_number], [c_address]) VALUES (6, N'Hedda', N'female    ', N'40        ', 543232, N'Chengbei 7  Road')
INSERT [client] ([client_id], [c_name], [c_sex], [c_age], [c_number], [c_address]) VALUES (7, N'Neil', N'man       ', N'33        ', 543223, N'Chengbei 8  Road')

/*records of table store*/
INSERT [store] ([store_id], [s_name], [s_address], [manager_id]) VALUES (1, N'Nandian', N'South Street', 1)
INSERT [store] ([store_id], [s_name], [s_address], [manager_id]) VALUES (2, N'Beidian', N'North Street', 2)
INSERT [store] ([store_id], [s_name], [s_address], [manager_id]) VALUES (3, N'Dongdian', N'East Street', 3)
INSERT [store] ([store_id], [s_name], [s_address], [manager_id]) VALUES (4, N'Xidian', N'West Street', 4)

/*records of table service_staff */
INSERT [service_staff] ([service_staff_id], [v_name], [v_sex], [v_age], [v_number], [store_id]) VALUES (1, N'Candice', N'female', 33, 23975, 1)
INSERT [service_staff] ([service_staff_id], [v_name], [v_sex], [v_age], [v_number], [store_id]) VALUES (2, N'Carl', N'man', 30, 54321, 2)
INSERT [service_staff] ([service_staff_id], [v_name], [v_sex], [v_age], [v_number], [store_id]) VALUES (3, N'Cathy', N'man', 31, 76432, 3)
INSERT [service_staff] ([service_staff_id], [v_name], [v_sex], [v_age], [v_number], [store_id]) VALUES (4, N'Chasel', N'female', 29, 64322, 4)

/*records of table sell_staff */
INSERT [sell_staff] ([sell_staff_id], [se_name], [se_sex], [se_age], [se_number], [store_id]) VALUES (1, N'Dada', N'female', 29, 876432, 1)
INSERT [sell_staff] ([sell_staff_id], [se_name], [se_sex], [se_age], [se_number], [store_id]) VALUES (2, N'Aldrich', N'man', 34, 543245, 2)
INSERT [sell_staff] ([sell_staff_id], [se_name], [se_sex], [se_age], [se_number], [store_id]) VALUES (3, N'Jacquelyn', N'man', 28, 986435, 3)
INSERT [sell_staff] ([sell_staff_id], [se_name], [se_sex], [se_age], [se_number], [store_id]) VALUES (4, N'Adolph', N'man', 30, 753677, 4)

/*records of table repair_staff */
INSERT [repair_staff] ([repair_staff_id], [r_name], [r_sex], [r_age], [r_number], [store_id]) VALUES (1, N'Huangli', N'female', 29, 4321543, 1)
INSERT [repair_staff] ([repair_staff_id], [r_name], [r_sex], [r_age], [r_number], [store_id]) VALUES (2, N'Huangfei', N'man', 22, 2345, 2)
INSERT [repair_staff] ([repair_staff_id], [r_name], [r_sex], [r_age], [r_number], [store_id]) VALUES (3, N'Ligou', N'man', 23, 56789, 3)
INSERT [repair_staff] ([repair_staff_id], [r_name], [r_sex], [r_age], [r_number], [store_id]) VALUES (4, N'Lida', N'female', 27, 76543, 4)

/*records of table repair_butt */
INSERT [repair_butt] ([client_id], [repair_staff_id], [content], [transaction_time], [collection_time]) VALUES (1, 1, N'repair tires', N'2022-3-2', N'2022-3-4')
INSERT [repair_butt] ([client_id], [repair_staff_id], [content], [transaction_time], [collection_time]) VALUES (1, 2, N'Repair handlebar', N'2022-3-6', N'2022-3-10')
INSERT [repair_butt] ([client_id], [repair_staff_id], [content], [transaction_time], [collection_time]) VALUES (1, 3, N'repair car seat', N'2022-2-28', N'2022-3-4')
INSERT [repair_butt] ([client_id], [repair_staff_id], [content], [transaction_time], [collection_time]) VALUES (1, 4, N'wheel repair', N'2022-3-1', N'2022-3-4')

/*records of table sell_butt */
INSERT [sell_butt] ([sell_staff_id], [client_id]) VALUES (1, 1)
INSERT [sell_butt] ([sell_staff_id], [client_id]) VALUES (2, 3)
INSERT [sell_butt] ([sell_staff_id], [client_id]) VALUES (2, 4)
INSERT [sell_butt] ([sell_staff_id], [client_id]) VALUES (2, 5)

/*records of table service_butt */
INSERT [service_butt] ([service_staff_id], [client_id]) VALUES (1, 1)
INSERT [service_butt] ([service_staff_id], [client_id]) VALUES (1, 4)
INSERT [service_butt] ([service_staff_id], [client_id]) VALUES (2, 2)
INSERT [service_butt] ([service_staff_id], [client_id]) VALUES (3, 4)

/*records of table bicycle */
INSERT [bicycle] ([bicycle_id], [b_name], [b_status], [b_warehouse_time], [store_id], [sell_staff_id], [client_id], [sale_time]) VALUES (1, N'che1', N'can rent', N'2020-3-1', 1, 1, 1, N'2022-3-6')
INSERT [bicycle] ([bicycle_id], [b_name], [b_status], [b_warehouse_time], [store_id], [sell_staff_id], [client_id], [sale_time]) VALUES (2, N'che2', N'can rent', N'2021-3-2', 2, 2, 3, N'2022-2-1')
INSERT [bicycle] ([bicycle_id], [b_name], [b_status], [b_warehouse_time], [store_id], [sell_staff_id], [client_id], [sale_time]) VALUES (3, N'che3', N'can rent', N'2021-2-4', 3, 2, 4, N'2022-2-13')
INSERT [bicycle] ([bicycle_id], [b_name], [b_status], [b_warehouse_time], [store_id], [sell_staff_id], [client_id], [sale_time]) VALUES (4, N'che4', N'not rent', N'2021-4-3', 4, 2, 5, N'2022-2-15')
INSERT [bicycle] ([bicycle_id], [b_name], [b_status], [b_warehouse_time], [store_id], [sell_staff_id], [client_id], [sale_time]) VALUES (5, N'che5', N'can rent', N'2021-3-4', 1, NULL, NULL, NULL)
INSERT [bicycle] ([bicycle_id], [b_name], [b_status], [b_warehouse_time], [store_id], [sell_staff_id], [client_id], [sale_time]) VALUES (6, N'che6', N'can rent', N'2021-4-2', 2, NULL, NULL, NULL)
INSERT [bicycle] ([bicycle_id], [b_name], [b_status], [b_warehouse_time], [store_id], [sell_staff_id], [client_id], [sale_time]) VALUES (7, N'che7', N'can rent', N'2021-3-8', 3, NULL, NULL, NULL)
INSERT [bicycle] ([bicycle_id], [b_name], [b_status], [b_warehouse_time], [store_id], [sell_staff_id], [client_id], [sale_time]) VALUES (8, N'che8', N'can rent', N'2021-4-9', 4, NULL, NULL, NULL)

INSERT [service_rent_bicycle] ([service_staff_id], [bicycle_id]) VALUES (1, 1)
INSERT [service_rent_bicycle] ([service_staff_id], [bicycle_id]) VALUES (1, 2)
INSERT [service_rent_bicycle] ([service_staff_id], [bicycle_id]) VALUES (1, 3)
INSERT [service_rent_bicycle] ([service_staff_id], [bicycle_id]) VALUES (1, 5)

INSERT [client_rent_bicycle] ([client_id], [bicycle_id], [rental_start], [rental_end]) VALUES (1, 1, N'2021-3-4', N'2021-3-6')
INSERT [client_rent_bicycle] ([client_id], [bicycle_id], [rental_start], [rental_end]) VALUES (2, 2, N'2021-3-8', N'2021-3-10')
INSERT [client_rent_bicycle] ([client_id], [bicycle_id], [rental_start], [rental_end]) VALUES (3, 3, N'2021-4-2', N'2021-4-10')
INSERT [client_rent_bicycle] ([client_id], [bicycle_id], [rental_start], [rental_end]) VALUES (5, 5, N'2021-4-5', N'2021-4-20')


/*SQL code to demonstrate evidence of testing carried out to validate the constraints*/
INSERT [sell_staff] ([sell_staff_id], [se_name], [se_sex], [se_age], [se_number], [store_id]) VALUES (6, N'Dali', N'girl', 30, 876782, 1)
/*error message*/
INSERT statement conflicts with CHECK constraint "check4". The conflict occurred in database "store", table "dbo.sell_staff", column 'se_sex'.
Statement terminated.

/*SQL code to demonstrate evidence of transaction rollback and commit operations*/

/*a.	Sometimes bicycle repair bookings are recorded but the details of the specific return date(s) as requested by customers are not registered in the system.*/
BEGIN TRAN   /*The statement produces an error, and only the T-SQL statement that produces the error is rolled back*/
INSERT [repair_butt] ([client_id], [repair_staff_id], [content], [transaction_time], [collection_time]) VALUES (1, 4, N'wheel repair', N'2022-3-1', N'2022-3-4')
INSERT [repair_butt] ([client_id], [repair_staff_id], [content], [transaction_time], [collection_time]) VALUES (1, 5, N'wheel repair', N'2022-3-1', N'')  /*No return date, statement produces error*/
select * from repair_butt
COMMIT TRAN

/*b.	Customers are able to book bicycle hire times that are not registered as possible schedules available.*/
BEGIN TRAN /*The statement produces an error, and only the T-SQL statement that produces the error is rolled back*/
UPDATE bicycle SET service_staff_id='3', WHERE bicycle_id=4   /*Statement error occurred, customer booked a bike that is not registered for rent*/
select * from repair_butt
COMMIT TRAN