Bài Project Quatrer 2 (NIIT) Showmanhouse

PROJECT REPORT March 21st, 2011 Showman House Batch: B110020 (Quarter 2) Coordinator: Quách Văn Hoàng Name of developer: Lữ Thiện Tính (Leader) Trịnh Ngọc Hằng Hà Đoàn Hữu Khánh Đặng Ngọc Hồng Yến Project : Case Study 2 Showman House Batch Code : B110020 Start Date : February 21st, 2011 End Date:March 21st, 2011 Name of the Coordinator : Mr. Quách Văn Hoàng Names of Developers : Group 2 Lữ Thiện Tính Trịnh Ngọc Hằng Hà Đoàn Hữu Khánh Đặng Ngọc Hồng Yến CERTIFICATE This is to certify that this report titled CreateMyDb,Inc. embodies the original work done by Group2 in partial fulfillment of their course requirement at NIIT. Coordinator: Quách Văn Hoàng Acknowledgement We have benefited a lot from the feedback and suggestions given to us By Mr. Quach Van Hoang and other faculty members, and the machine room coordinator. SYSTEM ANALYSIS Background Showman house là công ty quản lý sự kiện lớn ở New York. Công ty tổ chức nhiều loại sự kiện suốt năm. Những loại sự kiện bao gồm trình diễn thời trang, gặp gỡ người nổi tiếng, tán gẫu, trình diễn âm nhạc, những cuộc triễn lãm, hội chợ và những buổi từ thiện. Existing System Showman house tổ chức nhiều loại sự kiện khách nhau. Chi tiết của những loại sự kiện được lưu trữ trong tập tin Event Files. Tập tin này chứa đựng mã số của các loại sự kiện và những loại sự kiện tương ứng. Những khách hàng mà mong muốn tổ chức sự kiện cần cung cấp chi tiết về tính chất của sự kiện mà họ muốn tổ chức. Cùng với việc cung cấp chi tiết sự kiện, họ cũng có thể thanh toán cho sự kiện này. Việc thanh toán cho sự kiện được chia ra các phần, theo dõi chi phí kế hoạch, được chứa đựng trong tập tin Fee Schedules. Tập tin Fee Shedules duy trì chi tiết như ID lịch phí, ID sự kiện, phí mô tả và số lượng phí. Người tham dự cần phải trả các phần thanh toán, trong hoặc trước ngày sự kiện. Tất cả những chi tiết liên quan đến sự kiện như mã sự kiện, tên sự kiện, loại mã sự kiện, vị trí của sự kiện, ngày bắt đầu, ngày kết thúc, mô tả sự kiện, số người và các nhân viên được yêu cầu cho sự kiện được lưu trữ trong tập tin Events. Chi tiết thanh toán bao gồm số lượng thanh toán, ngày thanh toán, phương pháp thanh toán ID và mô tả phương pháp thanh toán cũng được lưu trữ trong tập tin Events. Chi tiết người tham dự như ID người tham dự, tên người tham dự, và địa chỉ được lưu trữ trong tập tin Attendees. Mỗi sự kiện ở Showman House được quản lý bởi nhân viên. Chi tiết nhân viên như ID nhân viên, tên riêng, tên họ, chức vụ và điện thoại được lưu trữ trong file Employee Envisioned System Sự quản lý của Showman House đã nhận ra rằng thật là khó khăn để bảo trì nhiều dữ liệu bằng tay.Vì vậy, họ đã quyết định tin học hóa toàn bộ hệ thống quản lý sự kiện. Blue Moon Computers đã được giao nhiệm vụ tin học hóa hệ thống. Đội dự án cần thực hiện các nhiệm vụ: Xác định những thực thể tham gia. Xác định những thuộc tính mà xác định hoàn thành thực thể. Vẽ sơ đồ E/R để biểu thị mối quan hệ giữa các loại thực thể. Ánh xạ sơ đồ E/R thành bảng. Chuẩn hóa bảng tới dạng chuẩn 3NF. Xác định khóa chính và khóa ngoại trong bảng. Vẽ sơ đồ biểu diễn mối quan hệ giữa các bảng. SCHEMATIC DIAGRAM OF THE DATABASE CREATE DATABASE create database ShowmanHouse go use ShowmanHouse Table : HumanResources.Employees create schema HumanResoure go create table HumanResoure.Employees (EmployeeID char(3) constraint pkEmployeeID primary key (EmployeeID), FirstName varchar (15) not null, LastName varchar (15) not null, Address varchar (30) not null, Phone varchar(20) constraint ckphone check (Phone Like('[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]')) NOT NULL) -----------------------create procedure Auto_EmpID------------------------- create procedure Auto_EmpID @FirstName varchar(15), @LastName varchar(15), @Address varchar(30), @Phone varchar(20) as DECLARE @EmployeeID char (3) IF EXISTS (SELECT EmployeeID FROM HumanResoure.Employees WHERE EmployeeID ='E01') BEGIN SELECT @EmployeeID = MAX(RIGHT(EmployeeID,2)) FROM HumanResoure.Employees SELECT @EmployeeID = CASE WHEN @EmployeeID >=0 AND @EmployeeID <9 THEN 'E0' + CONVERT (CHAR(3),@EmployeeID + 1) WHEN @EmployeeID >=9 AND @EmployeeID <99 THEN 'E'+CONVERT (CHAR(3),@EmployeeID + 1) END END ELSE Set @EmployeeID ='E01' INSERT HumanResoure.Employees VALUES (@EmployeeID,@FirstName,@LastName,@Address,@phone) RETURN GO exec Auto_EmpID 'Lu Thien','Tinh','Long Xuyen','0763-858-888' exec Auto_EmpID 'Doan Huu','Khanh','Long Xuyen','0763-855-888' exec Auto_EmpID 'Dang Ngoc Hong','Yen','Chau Doc','0763-858-748' exec Auto_EmpID 'Trinh Ngoc Hang','Ha','Chau Doc','0763-858-222' exec Auto_EmpID 'Nguyen Ha','Vi','Cho Moi','0763-858-666' select * from HumanResoure.Employees TABLES DESIGN TABLES DESIGN 2.Table : Management.Events create schema Management go create table Events.Customers (CustomersID char(3) constraint pkCustomersID primary key (CustomersID), Name char(10) not null, Address varchar(30) not null, City char(15) not null, State char(20) not null, Phone char(20) constraint ckphone check (Phone Like('[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]')) NOT NULL) ---------------------------create procedure Customer------------------------ create procedure auto_CustomersID @Name char(10), @Address varchar(30), @City char(15), @State char(20), @Phone char(20) as declare @CustomersID char (3) IF EXISTS (SELECT CustomersID FROM Events.Customers WHERE CustomersID ='V01') BEGIN SELECT @CustomersID = MAX(RIGHT(CustomersID,2)) FROM Events.Customers SELECT @CustomersID = CASE WHEN @CustomersID >=0 AND @CustomersID <9 THEN 'V0' + CONVERT (CHAR(3),@CustomersID + 1) WHEN @CustomersID >=9 AND @CustomersID <99 THEN 'V'+CONVERT (CHAR(3),@CustomersID + 1) END END Else Set @CustomersID ='V01' INSERT Events.Customers VALUES (@CustomersID ,@Name,@Address,@City,@State,@Phone) Return GO exec auto_CustomersID'Duy Tam', '145 Tran Hung Dao', 'Long Xuyen', 'An Giang', '0763-152-578' exec auto_CustomersID 'Truc Phuong', '11 Nguyen Du', 'Long Xuyen', 'An Giang', '0763-152-333' exec auto_CustomersID 'Thanh Luan', '12 Nguyn Thai Hoc', 'Long Xuyen', 'An Giang', '0763-152-666' exec auto_CustomersID 'Minh Thong', '04 Le Loi', 'Long Xuyen', 'An Giang', '0763-145-578' exec auto_CustomersID'Van Hieu', '67 Le Loi', 'Long Xuyen', 'An Giang', '0763-152-123' SELECT * FROM Events.Customers TABLES DESIGN 3.Table : Events.EventTyp create schema Events go create table Events.EventTypes (EventTypeID char(3) constraint pkEventTypeID primary key (EventTypeID), Decription varchar (30) not null, ChargePerPerson char(20) Constraint CKChargePerPerson check (ChargePerPerson>0)) ----------------------create procedure auto_EventTypeID------------------- Create procedure auto_EventTypeID @Decription varchar(30), @ChargePerPerson char(20) as declare @EventTypeID char(3) If Exists (select EventTypeID from Events.EventTypes where EventTypeID ='T01') begin select @EventTypeID = Max(Right(EventTypeID,2)) from Events.EventTypes select @EventTypeID = Case when @EventTypeID >=0 and @EventTypeID <9 Then 'T0' + Convert (Char(3),@EventtypeID + 1) When @EventTypeID >=9 and @EventTypeID <99 Then 'T' + Convert (char(3),@EventTypeID + 1) end end Else Set @EventTypeID ='T01' Insert Events.EventTypes Values (@EventTypeID,@Decription,@ChargePerPerson) Return Go -----------------select & insert data into Events.Eventtypes------------- exec auto_EventTypeID 'check', '100' exec auto_EventTypeID 'credit card', '400' exec auto_EventTypeID 'cash', '100' exec auto_EventTypeID 'cash', '50' exec auto_EventTypeID 'credit card', '200' Select * from Events.EventTypes TABLES DESIGN 4.Table : Managenment.PaymentMethod Create table Management.PaymentMethod (PaymentMethodID char (3) constraint pkPaymentMethodID primary key (PaymentMethodID), Description char (30) Constraint ckdec Check ( Description in ('Cash','Check','Credit Card')) Not null,) ---------------------create procedure Auto_PaymentMethodID----------------- Create procedure Auto_PaymentMethodID @Description char (30) as declare @PaymentMethodID char(3) If Exists (select PaymentMethodID from Management.PaymentMethod where PaymentMethodID ='M01') begin select @PaymentMethodID = Max(Right(PaymentMethodID,2)) from Management.PaymentMethod select @PaymentMethodID = Case when @PaymentMethodID >=0 and @PaymentMethodID <9 Then 'M0' + Convert (Char(3),@PaymentMethodID + 1) When @PaymentMethodID >=9 and @PaymentMethodID <99 Then 'M' + Convert (Char(3),@PaymentMethodID + 1) end end Else Set @PaymentMethodID ='M01' Insert Management.PaymentMethod Values (@PaymentMethodID,@Description) Return Go exec auto_paymentMethodID 'cash' exec auto_paymentMethodID 'check' exec auto_paymentMethodID 'credit card' exec auto_paymentMethodID 'cash' exec auto_paymentMethodID 'check' select * from Management.PaymentMethod TABLES DESIGN 5.Table : Management.Events create table Management.Events (EventID char (3) constraint pkEventID primary key (EventID), EventName varchar (12) not null, StartDate varchar (10) not null, EndDate varchar (10) not null, Location varchar (35) not null, NoOfPeople varchar (30) not null, StaffRequired varchar (30) not null, EventTypeID char(3) constraint fkEventTypeID foreign key references Events.EventTypes not null, CustomersID char(3) constraint fkCustomerID foreign key references Events.Customers not null, EmployeeID char(3) constraint fkEmployeeID foreign key references HumanResoure.Employees not null) --------------------create procedure Auto_EventID--------------------------------- create procedure Auto_EventID @EventName varchar(12), @StartDate varchar(10), @EndDate varchar(10), @Location varchar(35), @NoOfPeople varchar(30), @StaffRequired varchar(30), @EventTypeID char(3), @CustomersID char(3), @EmployeeID char(3) as DECLARE @EventID char (3) IF EXISTS (SELECT EventID FROM Management.Events WHERE EventID ='V01') BEGIN SELECT @EventID = MAX(RIGHT(EventID,2)) FROM Management.Events SELECT @EventID = CASE WHEN @EventID >=0 AND @EventID <9 THEN 'V0' + CONVERT (CHAR(3),@EventID + 1) WHEN @EventID >=9 AND @EventID <99 THEN 'V' + CONVERT (CHAR(3),@EventID + 1) END END ELSE Set @EventID ='V01' INSERT Management.Events VALUES (@EventID,@EventName,@StartDate,@EndDate,@Location,@NoOfPeople, @StaffRequired,@EventTypeID,@CustomersID,@EmployeeID) RETURN ----------------------------------trigger 1--------------------------------- create trigger trg_Event on Management.Events for insert,update as begin declare @StaffRequired varchar(30) select @StaffRequired=StaffRequired from inserted if (@StaffRequired < 0) begin print 'Yeu cau StaffRequired khong duoc nho hon 0' rollback transaction end return end go ----------------------------------trigger2---------------------------------- create trigger trg_Event2 on management.events for insert,update as begin declare @enddate datetime declare @startdate datetime select @enddate=enddate,@startdate=startdate from inserted if(@enddate<@startdate) begin print 'startdate phai nho hon enddate' rollback transaction end end go --------------------------------------trigger3------------------------------ create trigger trg_Event3 on Management.Events for insert,update as begin declare @enddate datetime declare @startdate datetime select @enddate=enddate from inserted select @startdate=startdate from inserted if (@startdate<=getdate() or @enddate<=getdate()) begin print 'startdate va enddate phai lon hon currentdate' rollback transaction end end go ------------------------------------trigger4-------------------------------- create trigger trg_Event4 on Management.Events for insert,update as begin declare @NoOfPeople varchar(30) select @NoOfPeople=NoOfPeople from inserted if(@NoOfPeople<=50) begin print 'NoOfPeople phai lon hon hoac bang 50' rollback transaction end return end ----------------------insert data Management.Events------------------------- exec Auto_EventID 'wedding','2011-4-26','2011-4-28','NH Dong Xuyen',100,10,'T01','V01','E01' exec Auto_EventID 'Fashion','2011-4-27','2011-4-28','San Van Dong AG',500,50,'T02','V02','E02' exec Auto_EventID 'Musicial','2011-4-28','2011-4-29','Nha Van Hoa',200,5,'T03','V03','E03' exec Auto_EventID 'Birthday','2011-4-29','2011-4-30','351/17 - LXAG',60,2,'T04','V04','E04' exec Auto_EventID 'Party','2011-4-27','2011-4-28','NH Hoa Binh',70,1,'T05','V05','E05' select * from Management.Events TABLES DESIGN 6.Table : Management.Paymens Create table Management.Payments ( PaymentID varchar (3) constraint pkPaymentID primary key not null, EventID char(3) constraint fkEventID foreign key references Management.Events, PaymentAmount money not null, PaymentDate datetime not null, PaymentMethodID char(3) constraint fkPaymentMethodID foreign key references Management.PaymentMethod not null, CreditCardNumber varchar(5) constraint chkcreditcardnumber check(CreditCardNumber like('[0-9][0-9]-[0-9][0-9]')), CreditCardExpdate datetime constraint chkCreditCardExpdate check (CreditCardExpdate>getdate())not null, ChequeNo varchar(10) ) go -----------------------create procedure Auto_PaymentID----------------------- Create Procedure Auto_PaymentID @EventID char(3), @PaymentAmount money, @PaymentDate datetime, @PaymentMethodID char(3), @CreditCardNumber varchar(5), @CreditCardExpdate datetime, @ChequeNo varchar(10) AS Declare @PaymentID varchar (3) If Exists (select PaymentID from Management.Payments Where PaymentID = 'P01') Begin Select @PaymentID = Max(Right(PaymentID,2)) FROM Management.Payments Select @PaymentID = Case When @PaymentID >=0 AND @PaymentID <9 THEN 'P0' + CONVERT (Char(3),@PaymentID + 1) When @PaymentID >=9 AND @PaymentID <99 THEN 'P' + CONVERT (Char(3),@PaymentID + 1) End End Else Set @PaymentID ='P01' Insert Management.Payments Values (@PaymentID,@EventID,@PaymentAmount ,@PaymentDate,@PaymentMethodID,@CreditCardNumber,@CreditCardExpdate,@ChequeNo ) Return Go ------------------------create Trigger 1------------------------------------- Create Trigger trg_ManagementPayments On Management.Payments For insert,update As Begin Declare @PaymentDate datetime Declare @StartDate datetime Select @PaymentDate=PaymentDate From inserted Select @StartDate=StartDate From Management.Events If(@PaymentDate>@StartDate) Begin Print 'paymentdate phai nho hon hoac bang StartDate' Rollback transaction End End Go -------------------------------create Trigger 2------------------------------ Create trigger trg_ManagementPayments2 On Management.Payments For insert,update As Begin Declare @PaymentDate datetime select @PaymentDate=PaymentDate From inserted If(@PaymentDate <getdate()) Begin Print 'PaymentDate khong duoc nho hon currentdate' Rollback transaction End End Go ------------------------------create Trigger 3------------------------------- Create trigger trg_ManagementPayments3 On Management.Payments For insert, update As Begin Declare @PaymentMethodid char(3) Declare @ChequeNo varchar(10) Select @PaymentMethodid=PaymentMethodid,@ChequeNo=chequeno from inserted if(@PaymentMethodid='M01') Begin If(@chequeno is null or @chequeno is null) Begin Print'nhap chequeno' Rollback transaction End End Else Begin If(@ChequeNo is not null) Begin Update Management.Payments set ChequeNo=null End End End Go -----------------------------create Trigger 4-------------------------------- create trigger trg_PaymentAmount4 On Management.Payments For insert, update As Begin Declare @PaymentAmount money Declare @Chargeperperson money Declare @NoofPeople int Declare @Tam int Select @paymentamount=paymentamount , @ChargePerPerson=chargeperperson, @noofpeople=noofpeople, @Tam=@Chargeperperson*@NoofPeople From Events.EventTypes E Join Management.Events M On E.eventtypeid=M.EventTypeID Join inserted I On M.Eventid=I.EventID If (@PaymentAmount!=@Tam) Begin Print 'paymentamount=chargeperperson*noofpeople' Rollback transaction End End Go -----------------insert data table Management.Payments----------------------- Select * from Management.Payments Exec Auto_PaymentID 'V01',10000,'2011-4-15','M01','20-11','2012-10-11','01' Exec Auto_PaymentID 'V02',200000,'2011-4-16','M02','20-11','2012-10-12','02' Exec Auto_PaymentID 'V03',20000,'2011-4-17','M03','20-11','2012-10-13','03' Exec Auto_PaymentID 'V04',3000,'2011-4-18','M04','20-11','2012-10-14','04' Exec Auto_PaymentID 'V05',14000,'2011-4-19','M05','20-11','2012-10-15','05' RELATIONSHIPS CREATE INDEX -------------Trich xuat 1 Chi tiet khach hanh co su kien voi mot ngay rieng------ CREATE PROC PrcDetaiDateCust @Date varchar(25) AS Begin IF EXISTS (Select StartDate From Management.Events Where StartDate=@Date) BEGIN Select a.CustomersID,a.Name,a.Address, a.City , a.State,a.Phone From[Events].Customers a JOIN Management.Events e On a.CustomersID=e.CustomersID Where e.StartDate=@Date end Else Print 'Nhap vao ngay khong co trong CSDL .Nhap lai theo yyy-mm-dd! :)' End drop proc PrcDetaiDateCust select * from Management.Events ----------------------------------nhap du lieu dung------------------------------ EXEC PrcDetaiDateCust '2011-4-26' ---------------------------------nhap du lieu sai-------------------------------- EXEC PrcDetaiDateCust '2011-4-20' --------------------Trich xuat chi tiet cac su kien cho thanh toan--------------- Create view EventPaymentPendingDetails As Select pa.PaymentDate, e.EventID, e.EventName, e.EventTypeID, e.Location, e.StartDate, e.EndDate, e.StaffRequired, e.EmployeeID,e.NoOfPeople,pa.paymentAmount From Management.Events e JOIN Management.Payments pa On e.EventID=pa.EventID select * from EventPaymentPendingDetails ------Hien thi chi tiet tat ca cac su kien co (staff required) lon hon 25--------- Create view event_safff25 As Select * From Management.Events Where StaffRequired > 25 select * from event_safff25 CREATE LOGIN -------------------------------Create logins name--------------------------- Use ShowmanHouse1 Go Create LOGIN Willam with PASSWORD ='123456' Create LOGIN Sam with PASSWORD ='123456' Create LOGIN Chris with PASSWORD ='123456' Create LOGIN Sara with PASSWORD ='123456' --------------------------------Create USER--------------------------------- Create USER Willam For login Willam Create USER Sam For login Sam Create USER Chris For login Chris Create USER Sara For login Sara ---------------------------Tao nhom phan quyen------------------------------ Use ShowmanHouse1 Go SP_ADDROLE 'administrator' SP_ADDROLE 'developers' -------------------------------Nhom phan quyen------------------------------ SP_ADDROLEMEMBER 'administrator','Chris' SP_ADDROLEMEMBER 'developers','Willam' SP_ADDROLEMEMBER 'developers','Sam' SP_ADDROLEMEMBER 'developers','Sara' --------------------------------Cap quyen----------------------------------- GRANT ALL TO administrator GRANT SELECT , INSERT ,UPDATE,DELETE,EXECUTE,REFERENCES,CREATE VIEW,CREATE TABLE,CREATE RULE,CREATE PROCEDURE,CREATE FUNCTION TO developers BACKUP DATABASE BACKUP DATABASE ShowmanHouse TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\OLAP\Data\ShowmanHouse.bak'