eventdb.sql

use master
GO
CREATE DATABASE ShowmanHouse
--Creating Filegroups, Log Files and assigning sizes, maximun sizes and filegrowth
ON PRIMARY(
	NAME='Showman_Primary',
	FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Showman_prm.mdf',
	SIZE=5MB, MAXSIZE= 10MB, FILEGROWTH= 1MB
),
FILEGROUP Showman_Grp1(
	NAME='Showman_FG1',
	FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Showman_FG1.ndf',
	SIZE= 1MB, MAXSIZE= 10MB, FILEGROWTH=1MB
),
FILEGROUP Showman_Grp2(
	NAME= 'Showman_FG2',
	FILENAME= 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Showman_FG2.ndf',
	SIZE= 1MB, MAXSIZE=10MB, FILEGROWTH=1MB
),
FILEGROUP Showman_Grp3(
	NAME='Showman_FG3',
	FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Showman_FG3.ndf',
	SIZE= 1MB, MAXSIZE= 10MB, FILEGROWTH= 1MB
)
LOG ON(
	NAME= 'ShowmanProject_log',
	FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Showman_log.ldf',
	SIZE= 1MB, MAXSIZE= 10MB, FILEGROWTH=1MB
)
GO
USE ShowmanHouse
GO
--adding files to the database
ALTER DATABASE ShowmanHouse add file(	
	NAME='Showman_Grp1',
	FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\ShowFG1.ndf',
	SIZE= 1MB, MAXSIZE= 10MB, FILEGROWTH=1MB
) to filegroup Showman_Grp1
GO
--READ MORE ON FILE GROUPS
ALTER DATABASE ShowmanHouse add file(	
	NAME='Showmann_FG2',
	FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Show_FG2.ndf',
	SIZE= 1MB, MAXSIZE= 10MB, FILEGROWTH=1MB
) to filegroup Showman_Grp2
GO
ALTER DATABASE ShowmanHouse add file(	
	NAME='Show_FG3',
	FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Show_FG3.ndf',
	SIZE= 1MB, MAXSIZE= 10MB, FILEGROWTH=1MB
) to filegroup Showman_Grp3
GO
---
--Creating Schemas
CREATE SCHEMA Management
GO
CREATE SCHEMA Eventss
GO
CREATE SCHEMA HumanResources
GO
--PRIMARY KEY:has a column or combination of columns that contain values that uniquely identify each row in the table.
--FOREIGN KEY:A foreign key is a column or combination of columns that is 
--used to establish and enforce a link between the data in two tables to control the data that can be stored in the foreign key table
--Creating tables for the Database
CREATE TABLE Eventss.EventType(
	EventTypeID int NOT NULL identity(1,1) primary key,
	_Description varchar(50) NOT NULL,
	ChargePerPerson decimal(10,2), CONSTRAINT CHK_Person check(ChargePerPerson > 0)
	);
GO
select * from Eventss.EventType
GO,
	_Address varchar(200) NOT NULL,
	City varchar(20) NOT NULL,
	_State varchar(20) NOT NULL,
	Phone char(15) NOT NULL, CONSTRAINT CHK_PhoneNo1 check (Phone like ('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))
	);
CREATE TABLE Eventss.Customer(
	CustomerID int NOT NULL identity(1,1) primary key,
	Name varchar(50) NOT NULL
select * from Eventss.Customer
GO
CREATE TABLE HumanResources.Employee(
	EmployeeID int NOT NULL identity(1,1) primary key,
	FirstName varchar (50) NOT NULL,
	LastName varchar(50) NOT NULL,
	Title varchar(30),check (Title in('Executive','Senior Executive','Management Trainee','Event Manager','	Senior Executive Manager')),
	_Address varchar(200) NOT NULL,
	Phone char(15) NOT NULL, CONSTRAINT CHK_PhoneNo2 check (Phone like ('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))
);
GO
CREATE TABLE Management.PaymentMethods(
	PaymentMethodID int NOT NULL identity(1,1) primary key,
	_Description varchar(15), CONSTRAINT CHK_PaymentDescription check(_Description in('cash','cheque','credit card'))
	);
GO
select * from Management.PaymentMethods
GO
insert into Management.PaymentMethods(_Description)
			values('Cash'),
				  ('Credit card'),
				  ('Cheque')
GO
CREATE TABLE Management.Eventss(
	EventID int NOT NULL identity(1,1) primary key,
	EventTypeID int foreign key references Eventss.EventType(EventTypeID),
	CustomerID int foreign key references Eventss.Customer(CustomerID),
	EmployeeID int foreign key references HumanResources.Employee(EmployeeID),
	EventName varchar(100),	
	StartDateOfEvent date NOT NULL, check (StartDateOfEvent < EndDateOfEvent), check(StartDateOfEvent > GETDATE()),
	EndDateOfEvent date NOT NULL, check(EndDateOfEvent > GETDATE()),
	StaffRequired int NOT NULL,
	NoOfPeople int NOT NULL, check(NoOfPeople >= 50),
);
GO 
CREATE TABLE Management.Payments(
	PaymentID int NOT NULL identity(1,1) primary key,
	EventID int foreign key references Management.Eventss(EventID),
	PaymentDate date,
	PaymentMethodID int foreign key references Management.PaymentMethods(PaymentMethodID),
	check((PaymentMethodID= 4 and CreditcardNumber IS NOT NULL AND CreditcardCCV IS NOT NULL AND CreditcardExpiryDate IS NOT NULL AND ChequeNumber IS NULL) or
	(PaymentMethodID= 5 and CreditcardNumber IS NULL AND CreditcardCCV IS NULL  AND CreditcardExpiryDate IS NULL AND ChequeNumber IS NOT NULL) or
	(PaymentMethodID= 3 and CreditcardNumber IS NULL AND CreditcardCCV IS NULL AND CreditcardExpiryDate IS NULL AND ChequeNumber IS NULL)),
	CreditcardNumber int, check(CreditcardNumber like('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')),
	CreditcardCCV int, check(CreditcardCCV like('[0-9][0-9][0-9]')),
	CreditcardExpiryDate date, check(CreditcardExpiryDate > GETDATE()),
	ChequeNumber int,
	PaymentAmount money 
);

/*insert into Management.Payments(EventID, PaymentDate, PaymentMethodID, 
CreditcardNumber, CreditcardCCV, CreditcardExpiryDate, ChequeNumber, PaymentAmount)
	values(19,'2020-01-01', 4, null, null, null, null, null),
		  (20,'2020-02-05', 3, null, null, null, null, null),
		  (22,'2020-01-20', 3, null, null, null, null, null),
		  (25,'2020-03-20', 3, null, null, null, null, null),
		  (26,'2020-03-20', 3, null, null, null, null, null)
GO*/
select * from Management.Payments
GO
--CREATING TRIGGER TO CALCULATE CHARGE PER PERSON
CREATE TRIGGER Payment ON Management.Payments
FOR INSERT, UPDATE 
AS
	BEGIN
	DECLARE @charge int
	declare @NOP int 
	select @charge = ChargePerPerson from Eventss.EventType
	select @NOP= NoOfPeople from Management.Eventss
		UPDATE Management.Payments
		SET PaymentAmount = @charge * @NOP
		FROM Management.Payments p
			 JOIN inserted i
			 ON p.PaymentID = i.PaymentID
END
GO
-- drop trigger Payment
--CREATING TRIGGERS FOR DATE CHECK
CREATE TRIGGER DateCheck ON Management.Payments
FOR INSERT, UPDATE
AS
	BEGIN
	DECLARE @SD date
	DECLARE @PD date
	SELECT @SD= StartDateOfEvent from Management.Eventss
	SELECT @PD= PaymentDate from Management.Payments

	IF (@PD > @SD)
	BEGIN
		PRINT 'The payment date you entered must be less than the Start date '
	END
	ROLLBACK
	end
GO
-- drop trigger DateCheck
CREATE TRIGGER DateCheck2 ON Management.Payments
FOR INSERT, UPDATE
AS
	BEGIN
	DECLARE @CD date
	DECLARE @PD date
	SELECT @CD= GETDATE()
	SELECT @PD= PaymentDate from Management.Payments

	IF (@PD < @CD)
	BEGIN
		PRINT 'The payment date you entered can not be less than Todays date '
	END
	end
GO
--CREATING INDEXES
CREATE INDEX DateSpeed on Management.Payments
(PaymentDate)
GO

CREATE NONCLUSTERED INDEX EventDetailsSpeed on Management.Eventss
(StartDateOfEvent Asc, EndDateOfEvent Asc, StaffRequired Asc, NoOfPeople Asc)
GO

CREATE INDEX StaffNumber on Management.Eventss
(StaffRequired) where StaffRequired > 25;
GO

--INSERTING VALUES INTO VARIOUS TABLES
insert into Eventss.EventType(_Description, ChargePerPerson) 
			values('Wedding',200),
				  ('Wedding Anniversary',300),
				  ('Ball',2000),
				  ('Childrens Birthday Party',100),
				  ('Adults Birthday Party', 400),
				  ('Bash',600),
				  ('Naming Ceremony',450),
				  ('Wedding Reception', 1500)
select * from Eventss.EventType
GO

insert into Eventss.Customer(Name, _Address, City, _State, Phone)
			values('Mr. Ben Ayokatu','NO.2, Buku street,Rammi Estate','Abuja','FCT','080123884776782'),
				  ('Mr. John Bayo','NO.2344, Bala street,Rammi Estate','Abuja','FCT','080123884776781'),
				  ('Mr. Mike Fumdigidi','NO.22, Lala street, Odummi Estate','Abuja','FCT','080123884776784'),
				  ('Mr. Israel Okosun','NO33, Ladi street, Ororo Estate','Abuja','FCT','080123884776783'),
				  ('Mr. Micheal Wilson','NO.3455, Barca street, Chelsea Estate','Benin City','Edo','080123884776780'),
				  ('Mrs. Talatu Gudu','NO.1, Nana street, kuku Estate','Benin City','Edo','080123884776786'),
				  ('Mr. Francis Bugudu','NO.56, Salamanda street, Beside Finance Quaters','Benin City','Edo','080123884776787'),
				  ('Mr. Mike Adenuga','NO.21, Salamanda street, Beside Finance Quaters','Benin City','Edo','080323884776787'),
				  ('Mr. Shade Awal','NO.2, Sally street, Beside Main Road','Benin City','Edo','080123884776787'),
				  ('Mr. Dudu Osun','NO.90, lily street, Manny Estate','Asaba','Delta','080333884776787'),
				  ('Mrs. Jane Bala','NO.200, lala street, Manny Estate','Asaba','Delta','080333884776890'),
				  ('Mr. Bila Owambe','NO.670, Hanger street, Manny Estate','Asaba','Delta','080333884776787'),
				  ('Mrs. Bisong Dauda','NO.90, 5th  Avenue','Asaba','Delta','080421884776787'),
				  ('Mrs. Bola Kaba','NO.3, 4th Avenue','Gombe','Gombe','080123884776787'),
				  ('Mrs. Bolatito Timi','NO.45, 78th  Avenue','Lokoja','Kogi','080551884776787'),
				  ('Mrs. Habis Usman','NO.50, 4th  Avenue','Enugu','Enugu','080421889776787'),
				  ('Mr. Kunle Gift','NO.10, 2nd  Avenue','Makurdi','Benue','080221884776787'),
				  ('Mrs. Shade Salami','NO.3, 10th  Avenue','Calabar','Cross River','080905884776787'),
				  ('Mrs. Jane Mark','NO.87, 22nd  Avenue','Yola','Adamawa','080421884776787')
				  go
				  
select * from Eventss.Customer
GO

insert into HumanResources.Employee(FirstName, LastName, Title, _Address, Phone)
			values('Mr James','Brown','Senior Executive',' NO.2 4th Avenue','090331190671396'),
				  ('Mrs Janet','West','Event Manager',' NO.4 6th Avenue','090876390671396'),
				  ('Mr Thomas','Paterson','Event Manager',' NO.9 8th Avenue','090331199051396'),
				  ('Mrs Elizabeth','Idakwoji','Event Manager',' NO.45 11th Avenue','090442290671396'),
				  ('Mrs Janet','East','Senior Executive',' NO.2 6th Avenue','090331160671396'),
				  ('Mrs Ann','Mike','Event Manager',' NO.22 9th Avenue','080431190671396'),
				  ('Mrs Annabel','Orji','Executive',' NO.21 4th Avenue','090331109371396'),
				  ('Mr Ben','Wilson','Executive',' NO.23 2nd Avenue','090331190980396'),
				  ('Mr Charles','Ololo','Management Trainee',' NO.1 4th Avenue','090221190671396'),
				  ('Mr. Bolu','Ayeni','Senior Executive',' NO.100 20th Avenue','090901190671396'),
				  ('Mr Israel','Okosun','Senior Executive',' NO.200 7t h Avenue','090221190671200'),
				  ('Mr Tolu','Adebesin','Event Manager',' NO.201 9th Avenue','090121190671396'),
				  ('Mr. Tima','Olu','Management Trainee',' NO.208 21st Avenue','090221188671396'),
				  ('Mr Besil','Omomo','Executive',' NO.302 44th Avenue','081221190671396'),
				  ('Mrs Ali','Bala','Senior Executive',' NO.11 5th Avenue','090220190671396'),
				  ('Mr Laolu','Kpakos','Management Trainee',' NO.400 36th Avenue','090221900671396'),
			      ('Mr Francis','Bina','Executive','NO.420 35th Avenue','090781900671396'),
				  ('Mrs Ian','Wright','Executive',' NO.3500 26th Avenue','090221760671396'),
				  ('Mrs Mary','Bisong','Management Trainee',' NO.20 36th Avenue','090541900671396'),
				  ('Mr. Awal','Benjamin','Senior Executive',' NO.55 42nd Avenue','090221900671396'),
				  ('Mr John','Cena','Management Trainee',' NO.203 1st Avenue','090231900661396'),
				  ('Mr Ede','Okosun','Event Manager',' NO.210 1st Avenue','090441900661396'),
				  ('Mr Mike','Adenuga','Event Manager',' NO.244 1st Avenue','090231900661396'),
				  ('Mrs Obioma','Ekwere','Event Manager',' NO.273 17th Avenue','090231911661396'),
				  ('Mr Eze','Uwom','Event Manager',' NO.113 10th Avenue','090211900661396'),
				  ('Mrs Oyi','Mudu','Event Manager',' NO.1 2nd Avenue','090239900661396'),
				  ('Mrs Angela','Mudu','Management Trainee',' NO.43 2nd Avenue','090239989661396'),
				  ('Mr Francis','Benjamin','Senior Executive',' NO.77 3rd Avenue','090119900661396'),
				  ('Mrs Idara','Madukpi','Management Trainee',' NO.65 42nd Avenue','090239909861396'),
				  ('Mrs Ene','Ohigana','Event Manager',' NO.32 1st Avenue','090123300661396'),
				  ('Mrs Ann','Okpa','Senior Executive',' NO.1 2nd Avenue','090239900661396'),
				  ('Mr Ogbonna','William','Event Manager',' NO.56 4th Avenue','090255900661396'),
				  ('Mr Richard','Ben','Senior Executive',' NO.201 90th Avenue','080239900661390'),
				  ('Mr Raheem','Sterling','Management Trainee',' NO.9 23rd Avenue','090239907681396'),
				  ('Mr Divock','Origi','Event Manager',' NO.123 3rd Avenue','090238300661396'),
				  ('Mr Mohammed','Salah','Executive',' NO.204 4th Avenue','090892000661396'),
				  ('Mr Abraham','Tammy','Event Manager',' NO.210 43rd Avenue','090129900661396'),
				  ('Mrs Annabel','Gwen','Senior Executive',' NO.1 2nd Avenue','090239900661396'),
				  ('Mrs Jackie','Grath','Senior Executive',' NO.1 2nd Avenue','090239900661396')

select * from HumanResources.Employee 
GO
--select * from HumanResources.Employee where Title='Event Manager'
insert into Management.Eventss(EventTypeID, CustomerID, EmployeeID, EventName, StartDateOfEvent, EndDateOfEvent, StaffRequired, NoOfPeople)
 			values(1, 4, 12, 'Mr. Bolus Wedding', '2020-01-01','2020-01-02', 6, 200),
				  (2, 1, 13, 'Mr Bens Wedding Anniversary', '2020-01-10','2020-01-11',100 ,200 ),
				  (2, 2, 14, 'Mr Johns Wedding Anniversary', '2020-03-10','2020-03-11',50 ,130 ),
				  (8, 3, 15, 'Mr Mikes Ball', '2020-03-10','2020-03-11', 20 ,51 ),
				  (5, 4, 16, 'Mr Israel Okosuns Birthday Party', '2020-08-24','2020-08-28',80 ,77 ),
				  (5, 5, 17, 'Mr Micheal Birthday Party', '2020-02-10','2020-02-11',70 ,70 ), 
				  (5, 6, 18, 'Mrs Talatus Birthday Party', '2020-09-22','2020-09-23',100 ,159 ),
				  (3, 7, 19, 'Mr Francis Ball', '2020-10-21','2020-10-22',62 ,3200),
				  (2, 8, 20, 'Mr Mikes Wedding Anniversary', '2020-05-27','2020-05-28',56 ,123 ),
				  (7, 9, 21, 'Mr Shade Childs Naming Ceremony', '2020-04-13','2020-04-14',72 ,230 ),
				  (3, 10, 50, 'Mr Dudus Ball', '2020-09-20','2020-09-21', 100 ,210 ),
				  (3, 11, 49, 'Mrs Janes Ball ', '2020-05-10','2020-05-11',500 ,1000 ),
				  (3, 12, 48, 'Mr Bilas Ball ', '2020-11-05','2020-11-06', 400 ,1200 ),
				  (5, 13, 47, 'Mrs Bisongs Childs Birthday Party ', '2020-02-18','2020-02-19',20 ,60 ),
				  (5, 14, 46, 'Mrs Bola Childs Birthday Party ', '2020-03-08','2020-03-09',10 ,72 ),
				  (5, 15, 45, 'Mrs Bolatitos Childs Birthday Party ', '2020-12-18','2020-12-19',20 ,60 ),
				  (1, 16, 44, 'Mrs Habibs Wedding', '2020-04-01','2020-04-02',200 ,500 ),
				  (1, 17, 43, 'Mr Kunles Wedding', '2020-05-06','2020-05-07',100 ,450 ),
				  (1, 18, 42, 'Mrs Shades Wedding', '2020-10-11','2020-10-12',700 ,5000 ),
				  (1, 19, 41, 'Mrs Janes Wedding', '2020-11-11','2020-11-12',200 ,750 )
GO	  
select * from Management.Eventss
/*update Management.Eventss
set StaffRequired=25
where EventID= 6
*/
GO
select * from Management.Eventss
order by StartDateOfEvent DESC
go

--Performing JOIN to link tables
select a.Name, a._Address, a.Phone, b.EventName, b.StartDateOfEvent, b.EndDateOfEvent
from Eventss.Customer a
JOIN Management.Eventss b
on a.CustomerID=b.CustomerID
GO
/*		PRIMARY DATA FILE
	Primary data file contains the starftup information for the database and point to the other files in the database.
	User data and objects can be stored in this file or in secondary data files. Every database has one primary data file. 
	The recommended file name extension for primary data files is .mdf
		SECONDARY DATA FILE	
	Secondary data files are optional, are user-defined, and store user data. 
	Secondary files can be used to spread data across multiple disks by putting each file on a different disk drive. 
	Additionally, if a database exceeds the maximun size for a single Windows file, you can use secondary data files so the database can continue to grow.
	The recommended file name extension for transaction log file is .ndf
		Transactional Log
	The transaction log files hold the log information that is used to recover the database.
	 There must be at least one log file for each databse. The recommended file name extension for transaction log file is .ldf
*/

SELECT * from sys.filegroups
exec sp_help 'Show_FG3'