Use SQL Query Analyzer to work with EventManagement database and create a SQL script file follow those steps:
- Create 3 tables and insert data as follows:
Customer (Customer list)
CusID (int) CusName (Varchar(50)) CusAge (Smallint) CusGender (Bit) 1 Truong Gia Binh 45 1 2 Vuong Vu Thang 22 Null 3 Trieu Tran Duc 20 1 4 Tran Ngoc Oanh 19 0
Event (Event list)
EvtID (int) EvtName (varchar(50) EvtType (tinyint) EvtDate (DateTime) 1 Hacker VN 1 2005-01-13 2 TTVN 2 2004-12-12 3 SVHN 3 2005-01-01
CustomerEvent (Describe attendance of Event. Example: the first row of following table denote “Truong Gia Binh has attended in Hacker VN event”)
CusID (Int) EvtID (Int) cesClass (Int) 1 1 1 1 2 1 2 1 2 2 3 2 3 1 1 3 2 2 3 3 3
Constraints: (Student must use ‘Alter Table’ statement to Apply them)
– Apply the Default Constraint to the EvtType column of Event table with value is 1.
– Apply the Primary Key Constraint for CustID of Customer table, EvtID of Event table and the Composite Primary Key Constraint for the CusID and EvtID of the CustomerEvent table.
– Create a References Constraint between Customer and CustomerEvent table on CusID named ‘Contr1’, and a References Constraint between Event and CustomerEvent table on EvtID named ‘Contr2’.
- Display total number of customers that has attended TTVN event.
- Display total number of attendance customer for each event as following:
Event Name Total Hacker VN 3 TTVN 2 SVHN 2
- Display Customer list as following:
Name Age Gender Truong Gia Binh 45 Male Vuong Vu Thang 22 Nil Trieu Tran Duc 20 Male Tran Ngoc Oanh 19 Female
- Create a view named ‘vwAttendanceList’ as following: (the list must sorted by Event Name, if not student will only get ½ total mark)
Event Name Customer Name Hacker VN Truong Gia Binh Hacker VN Vuong Vu Thang Hacker VN Trieu Tran Duc SVHN Vuong Vu Thang SVHN Trieu Tran Duc TTVN Truong Gia Binh TTVN Trieu Tran Duc
- Modify the view named ‘vwAttendanceList’ such that you can create an index on it. (Hint: Add ‘With SchemaBinding’ option and remove some statement such as ‘Top’, ‘Order by’,…)
- Create an index named ‘indAttendanceList’ on the (‘Event Name’,’Customer Name’) column of the ‘vwAttendanceList’ view.
- Display name of the customer that has number of attendance in all events is greatest.
(Notes: The correct result will be: ‘Trieu Tran Duc’).
- Drop References Constraint between Event and CustomerEvent table.
- Create an Delete Trigger named ‘tgDeleteAll’ on the Event table such that if you delete a event, all relative row on CustomerEvent table will be deleted.
- Create a stored procedure named ‘spDropCustomer’ that accept an integer number as parameter. The stored procedure will delete all customer who has the number of attendance in all events is less than the parameter.
(Notes: You must ensure that this action will completely.)