Crap

-- --------------------------------------------------------------------------------
-- Name: Jamie Cappel
-- Class: IT-111-001
-- Abstract: Golfathon
-- --------------------------------------------------------------------------------

-- --------------------------------------------------------------------------------
-- Options
-- --------------------------------------------------------------------------------
USE joe;     -- Get out of the master database
SET NOCOUNT ON; -- Report only errors

-- --------------------------------------------------------------------------------
-- Drop Tables
-- --------------------------------------------------------------------------------

--Dependant Tables in Cascading Dependancy
DROP TABLE TEventCorporateSponsers
DROP TABLE TEventSponsers
DROP TABLE TEventTeams
DROP TABLE TEvents

DROP TABLE TSponserships
DROP TABLE TSponsers
DROP TABLE TCorporateSponserships
DROP TABLE TCorporateSponsers

DROP TABLE TTeamPlayers
DROP TABLE TPlayers
DROP TABLE TTeams

--Basic Tables
DROP TABLE TStates
DROP TABLE TShirtSizes
DROP TABLE TGenders
DROP TABLE TTypesOfTeams
DROP TABLE TLevelsOfTeams
DROP TABLE TPayStatuses
DROP TABLE TPayTypes
DROP TABLE TAffiliations

-- --------------------------------------------------------------------------------
-- Create Tables
-- --------------------------------------------------------------------------------
CREATE TABLE TPlayers
(
	intPlayerID				INTEGER			NOT NULL
	,strFirstName			VARCHAR(50)		NOT NULL
	,strLastName			VARCHAR(50)		NOT NULL
	,strAddress				VARCHAR(50)		NOT NULL
	,intStateID				INTEGER			NOT NULL
	,strCity				VARCHAR(50)		NOT NULL
	,strZip					VARCHAR(50)		NOT NULL
	,strPhoneNumber			VARCHAR(50)		NOT NULL
	,strEmail				VARCHAR(50)		NOT NULL
	,intShirtSizeID			INTEGER			NOT NULL
	,CONSTRAINT TPlayers_PK PRIMARY KEY (intPlayerID)
)

CREATE TABLE TEvents
(
	intEventID				INTEGER			NOT NULL
	,dtmDate				DATETIME		NOT NULL
	,CONSTRAINT TEvents_PK PRIMARY KEY (intEventID)
)

CREATE TABLE TEventTeams
(
	intEventID				INTEGER			NOT NULL
	,intTeamID				INTEGER			NOT NULL
	,strReasonForJoining	VARCHAR(50)		NOT NULL
	,CONSTRAINT TEventTeams_PK PRIMARY KEY (intEventID)
)

CREATE TABLE TEventSponsers
(
	intEventID				INTEGER			NOT NULL
	,intSponserID			INTEGER			NOT NULL
	,intAffiliationID		INTEGER			NOT NULL
	,monAmount				MONEY			NOT NULL
	,CONSTRAINT TEventSponsers_PK PRIMARY KEY (intEventID)
)

CREATE TABLE TEventCorporateSponsers
(
	intEventID				INTEGER			NOT NULL
	,intSponserID			INTEGER			NOT NULL
	,monAmount				MONEY			NOT NULL
	,CONSTRAINT TEventCorporateSponsers_PK PRIMARY KEY (intEventID)
)

CREATE TABLE TStates -- Data
(
	intStateID 				INTEGER			NOT NULL
	,strName				VARCHAR(50)		NOT NULL
	,CONSTRAINT TStates_PK PRIMARY KEY (intStateID)
)

CREATE TABLE TShirtSizes -- Data
(
	intShirtSizeID 			INTEGER			NOT NULL
	,strShirtSizeDesc		VARCHAR(50)		NOT NULL
	,CONSTRAINT TShirtSizes_PK PRIMARY KEY (intShirtSizeID)
)

CREATE TABLE TSponsers
(
	intSponserID 			INTEGER			NOT NULL
	,strFirstName			VARCHAR(50)		NOT NULL
	,strLastName			VARCHAR(50)		NOT NULL
	,strCity				VARCHAR(50)		NOT NULL
	,intStateID 			INTEGER			NOT NULL
	,intZip 				INTEGER			NOT NULL
	,strPhoneNumber			VARCHAR(50)		NOT NULL
	,strEmail				VARCHAR(50)		NOT NULL
	,CONSTRAINT TSponsers_PK PRIMARY KEY (intSponserID)
)

CREATE TABLE TAffiliations -- Data
(
	intAffiliationID		INTEGER			NOT NULL
	,strDesc				VARCHAR(50)		NOT NULL
	,CONSTRAINT TAffiliations_PK PRIMARY KEY (intAffiliationID)
)

CREATE TABLE TSponserships
(
	intSponserID 			INTEGER			NOT NULL
	,intSponsershipIndex 	INTEGER			NOT NULL
	,intStatusID 			INTEGER			NOT NULL
	,intTypeID 				INTEGER			NOT NULL
	,monAmount 				MONEY			NOT NULL
	,dtmDate 				DATETIME		NOT NULL
	,CONSTRAINT TSponserships_PK PRIMARY KEY (intSponserID, intSponsershipIndex)
)

CREATE TABLE TCorporateSponsers
(
	intSponserID 			INTEGER			NOT NULL
	,strName				VARCHAR(50)		NOT NULL
	,strCity				VARCHAR(50)		NOT NULL
	,intStateID 			INTEGER			NOT NULL
	,intZip 				INTEGER			NOT NULL
	,strPhoneNumber			VARCHAR(50)		NOT NULL
	,strEmail				VARCHAR(50)		NOT NULL
	,CONSTRAINT TCorporateSponsers_PK PRIMARY KEY (intSponserID)
)

CREATE TABLE TCorporateSponserships
(
	intSponserID 			INTEGER			NOT NULL
	,intSponsershipIndex 	INTEGER			NOT NULL
	,intStatusID 			INTEGER			NOT NULL
	,intTypeID 				INTEGER			NOT NULL
	,monAmount 				MONEY			NOT NULL
	,dtmDate 				DATETIME		NOT NULL
	,CONSTRAINT TCorporateSponserships_PK PRIMARY KEY (intSponserID, intSponsershipIndex)
)


CREATE TABLE TPayStatuses -- Data
(
	intStatusID				INTEGER			NOT NULL
	,strStatus				VARCHAR(50)		NOT NULL
	,CONSTRAINT TPayStatuses_PK PRIMARY KEY (intStatusID)
)

CREATE TABLE TPayTypes -- Data
(
	intTypeID				INTEGER			NOT NULL
	,strName				VARCHAR(50)		NOT NULL
	,CONSTRAINT TPayTypes_PK PRIMARY KEY (intTypeID)
)

CREATE TABLE TTeams
(
	intTeamID				INTEGER			NOT NULL
	,strName				VARCHAR(50)		NOT NULL
	,intTypeID				INTEGER			NOT NULL
	,intLevelID				INTEGER			NOT NULL
	,intGenderID			INTEGER			NOT NULL
	,CONSTRAINT TTeams_PK PRIMARY KEY (intTeamID)
)

CREATE TABLE TTeamPlayers
(
	intTeamID				INTEGER			NOT NULL
	,intPlayerID			INTEGER			NOT NULL
	,CONSTRAINT TTeamPlayers_PK PRIMARY KEY (intTeamID, intPlayerID)
)

CREATE TABLE TGenders -- Data
(
	intGenderID				INTEGER			NOT NULL
	,strName				VARCHAR(50)		NOT NULL
	,CONSTRAINT TGenders_PK PRIMARY KEY (intGenderID)
)

CREATE TABLE TTypesOfTeams -- Data
(
	intTypeID 				INTEGER			NOT NULL
	,strName				VARCHAR(50)		NOT NULL
	,CONSTRAINT TTypesOfTeams_PK PRIMARY KEY (intTypeID)
)

CREATE TABLE TLevelsOfTeams -- Data
(
	intLevelID 				INTEGER			NOT NULL
	,strName				VARCHAR(50)		NOT NULL
	,CONSTRAINT TLevelsOfTeams_PK PRIMARY KEY (intLevelID)
)

-- --------------------------------------------------------------------------------
-- 1) Identify and Create Foreign Keys
-- --------------------------------------------------------------------------------
-- ALTER TABLE <child table name> ADD CONSTRAINT <constraint name>
--  FOREIGN KEY ( <child column(s)> ) REFERENCES <parent table name> ( <parent table column(s)> )
--
-- #	Child						Parent						Column(s)
-- -	-----						------						---------
-- 1	TPlayers					TStates						intStateID
-- 2	TPlayers					TShirtSizes					intShirtSizeID
-- 3	TSponsers					TStates						intStateID
-- 4	TSponserships				TSponsers					intSponserID

-- 5	TSponserships				TPayTypes					intTypeID
-- 6	TSponserships				TPayStatuses				intStatusID
-- 7	TCorporateSponserships		TCorporateSponsers			intSponserID
-- 8	TCorporateSponserships		TPayTypes					intTypeID

-- 9	TCorporateSponserships		TPayStatuses				intStatusID
-- 10	TTeams						TGenders					intGenderID
-- 11	TTeams						TLevelsOfTeams				intLevelID
-- 12	TTeams						TTypesOfTeams				intTypeID

-- 14	TTeamPlayers				TPlayers					intPlayerID
-- 15	TTeamPlayers				TTeams						intTeamID
-- 16

-- 17	TEventTeams					TEvents						intEventID
-- 18	TEventSponsers				TEvents						intEventID
-- 19	TEventTeams					TTeams						intTeamID
-- 20	TEventSponsers				TSponsers					intSponserID

-- 21	TEventCorporateSponsers		TCorporateSponsers			intSponserID
-- 22	TEventCorporateSponsers		TEvents						intEventID

-- 1
ALTER TABLE TPlayers ADD CONSTRAINT TPlayers_TStates_FK
	FOREIGN KEY ( intStateID ) REFERENCES TStates ( intStateID )

-- 2
ALTER TABLE TPlayers ADD CONSTRAINT TPlayers_TShirtSizes_FK
	FOREIGN KEY ( intShirtSizeID ) REFERENCES TShirtSizes ( intShirtSizeID )

-- 3
ALTER TABLE TSponsers ADD CONSTRAINT TSponsers_TStates_FK
	FOREIGN KEY ( intStateID ) REFERENCES TStates ( intStateID )

-- 4
ALTER TABLE TSponserships ADD CONSTRAINT TSponserships_TSponsers_FK
	FOREIGN KEY ( intSponserID ) REFERENCES TSponsers ( intSponserID )


-- 5
ALTER TABLE TSponserships ADD CONSTRAINT TSponserships_TPayTypes_FK
	FOREIGN KEY ( intTypeID ) REFERENCES TPayTypes ( intTypeID )

-- 6
ALTER TABLE TSponserships ADD CONSTRAINT TSponserships_TPayStatuses_FK
	FOREIGN KEY ( intStatusID ) REFERENCES TPayStatuses ( intStatusID )

-- 7
ALTER TABLE TCorporateSponserships ADD CONSTRAINT TCorporateSponserships_TCorporateSponsers_FK
	FOREIGN KEY ( intSponserID) REFERENCES TCorporateSponsers ( intSponserID )

-- 8
ALTER TABLE TCorporateSponserships ADD CONSTRAINT TCorporateSponserships_TPayTypes_FK
	FOREIGN KEY ( intTypeID ) REFERENCES TPayTypes ( intTypeID )


-- 9
ALTER TABLE TCorporateSponserships ADD CONSTRAINT TCorporateSponserships_TPayStatuses_FK
	FOREIGN KEY ( intStatusID ) REFERENCES TPayStatuses ( intStatusID )

-- 10
ALTER TABLE TTeams ADD CONSTRAINT TTeams_TGenders_FK
	FOREIGN KEY ( intGenderID ) REFERENCES TGenders ( intGenderID )

-- 11
ALTER TABLE TTeams ADD CONSTRAINT TTeams_TLevelsOfTeams_FK
	FOREIGN KEY ( intLevelID ) REFERENCES TLevelsOfTeams ( intLevelID )

-- 12
ALTER TABLE TTeams ADD CONSTRAINT TTeams_TTypesOfTeams_FK
	FOREIGN KEY ( intTypeID ) REFERENCES TTypesOfTeams ( intTypeID )


-- 13


-- 14
ALTER TABLE TTeamPlayers ADD CONSTRAINT TTeamPlayers_TPlayers_FK
	FOREIGN KEY ( intPlayerID ) REFERENCES TPlayers ( intPlayerID )

-- 15
ALTER TABLE TTeamPlayers ADD CONSTRAINT TTeamPlayers_TTeams_FK
	FOREIGN KEY ( intTeamID ) REFERENCES TTeams ( intTeamID )

-- 16



-- 17
ALTER TABLE TEventTeams ADD CONSTRAINT TEventTeams_TEvents_FK
	FOREIGN KEY ( intEventID ) REFERENCES TEvents ( intEventID )

-- 18
ALTER TABLE TEventSponsers ADD CONSTRAINT TEventSponsers_TEvents_FK
	FOREIGN KEY ( intEventID ) REFERENCES TEvents ( intEventID )

-- 19
ALTER TABLE TEventTeams ADD CONSTRAINT TEventTeams_TTeams_FK
	FOREIGN KEY ( intTeamID ) REFERENCES TTeams ( intTeamID )

-- 20
ALTER TABLE TEventSponsers ADD CONSTRAINT TEventSponsers_TSponsers_FK
	FOREIGN KEY ( intSponserID ) REFERENCES TSponsers ( intSponserID )


-- 21
ALTER TABLE TEventCorporateSponsers ADD CONSTRAINT TEventCorporateSponsers_TCorporateSponsers_FK
	FOREIGN KEY ( intSponserID ) REFERENCES TCorporateSponsers ( intSponserID )

-- 22
ALTER TABLE TEventCorporateSponsers ADD CONSTRAINT TEventCorporateSponsers_TEvents_FK
	FOREIGN KEY ( intEventID ) REFERENCES TEvents ( intEventID )

-- 23
ALTER TABLE TEventSponsers ADD CONSTRAINT TEventSponsers_TAffiliations_FK
	FOREIGN KEY ( intAffiliationID ) REFERENCES TAffiliations ( intAffiliationID )

-- --------------------------------------------------------------------------------
-- 2) Create Data
-- --------------------------------------------------------------------------------

-- Basic Data ---------------------------------------------------------------------
INSERT INTO TShirtSizes ( intShirtSizeID, strShirtSizeDesc )
VALUES	 ( 1, 'Mens Small' )
		,( 2, 'Mens Medium' )
		,( 3, 'Mens Large' )
		,( 4, 'Mens XLarge' )
		,( 5, 'Womens Small' )
		,( 6, 'Womens Medium' )
		,( 7, 'Womens Large' )
		,( 8, 'Womens XLarge' )

INSERT INTO TStates ( intStateID, strName )
VALUES	 ( 1, 'Ohio' )
		,( 2, 'Kentucky' )
		,( 3, 'Indiana' )

INSERT INTO TGenders ( intGenderID, strName )
VALUES	 ( 1, 'Male' )
		,( 2, 'Female' )

INSERT INTO TLevelsOfTeams ( intLevelID, strName )
VALUES	 ( 1, 'Freshman' )
		,( 2, 'Junior Varsity' )
		,( 3, 'Varsity' )

INSERT INTO TTypesOfTeams (  intTypeID, strName )
VALUES	 ( 1, 'Basketball' )
		,( 2, 'Baseball' )
		,( 3, 'Football' )
		,( 4, 'Volleyball' )
		,( 5, 'Soccer' )
		,( 6, 'Cross Country' )
		,( 7, 'Track' )
		,( 8, 'Softball' )
		,( 9, 'Golf' )
		,( 10, 'Swimming' )

INSERT INTO TPayStatuses ( intStatusID, strStatus)
VALUES	 ( 1, 'Unpaid' )
		,( 2, 'Paid' )

INSERT INTO TPayTypes ( intTypeID, strName )
VALUES	 ( 1, 'Check' )
		,( 2, 'Cash' )
		,( 3, 'Credit Card' )

INSERT INTO TAffiliations ( intAffiliationID, strDesc )
VALUES	 ( 1, 'Friend')
		, ( 2, 'Parent')
		, ( 3, 'Coach')

--Dependant Data-------------------------------------------------------------------

--Teams and Player Data
INSERT INTO TTeams(intTeamID, strName, intTypeID, intLevelID, intGenderID)
VALUES	 (1, 'The B-Ball Players', 1, 1, 1)
		,(2, 'The Golfers', 9, 2, 2)

INSERT INTO TPlayers(intPlayerID, strFirstName, strLastName, strAddress, strCity, intStateID, strZip, strPhoneNumber, strEmail, intShirtSizeID)
VALUES	 ( 1, 'Bob', 'Nields', '8741 Rosebrook Drive', 'Florence', 2, '41042', '8597602063', 'bnields@gmail.com', 4)
		,( 2, 'Jay', 'Graue', '1111 SHDHS Drive', 'Florence', 2, '41042', '8597602222', 'jgraue@gmail.com', 4)
		,( 3, 'Mary', 'Beimesch', '4444 Tobertge Drive', 'Hebron', 2, '41012', '8597603333', 'mb@gmail.com', 4)
		,( 4, 'Tony', 'Hardan', '2222 Track', 'Ft. Thomas', 2, '41018', '8592222063', 'thardon@gmail.com', 4)
		,( 5, 'Iwana', 'Bucks', '2222 Track', 'Ft. Thomas', 2, '41018', '8592222063', 'thardon@gmail.com', 4)
		,( 6, 'Jef', 'Hardan', '2222 Track', 'Ft. Thomas', 2, '41018', '8592222063', 'thardon@gmail.com', 4)
		,( 7, 'Kevin', 'Bucks', '2222 Track', 'Ft. Thomas', 2, '41018', '8592222063', 'thardon@gmail.com', 4)

INSERT INTO TTeamPlayers(intTeamID, intPlayerID)
VALUES	 (2, 1)
		,(2, 2)
		,(2, 3)
		,(2, 4)
		,(2, 5)
		,(1, 6)
		,(1, 7)

--Events and Sponserships Data
INSERT INTO TSponsers (intSponserID, strFirstName, strLastName, strCity, intStateID, intZip, strPhoneNumber, strEmail)
VALUES	 (1, 'Joe', 'Sponserman', 'Cincinnati',  1,'23232', '555 555 5554', 'yy@ioio.com')
		,(2, 'Boen', 'Sponserman', 'Cincinnati', 1, '23232', '555 555 5554', 'yy@ioio.com')
		,(3, 'Blub', 'Sponserman', 'Cincinnati', 1, '23232', '555 555 5554', 'yy@ioio.com')
		,(4, 'Sub', 'Sponserman', 'Cincinnati', 1, '23232', '555 555 5554', 'yy@ioio.com')
		,(5, 'Kevin', 'Sponserman', 'Cincinnati', 1, '23232', '555 555 5554', 'yy@ioio.com')
		,(6, 'Jake', 'Sponserman', 'Cincinnati', 1, '23232', '555 555 5554', 'yy@ioio.com')
		,(7, 'Jacob', 'Sponserman', 'Cincinnati', 1, '23232', '555 555 5554', 'yy@ioio.com')
		,(8, 'Blart', 'Sponserman', 'Cincinnati', 1, '23232', '555 555 5554', 'yy@ioio.com')
		,(9, 'Bob', 'Sponserman', 'Cincinnati', 1, '23232', '555 555 5554', 'yy@ioio.com')
		,(10, 'Blartman', 'Sponserman', 'Cincinnati',1, '23232', '555 555 5554', 'yy@ioio.com')
		,(11, 'Joseph', 'Sponserman', 'Cincinnati', 1, '23232', '555 555 5554', 'yy@ioio.com')
		,(12, 'Maria', 'Sponserman', 'Cincinnati', 1, '23232', '555 555 5554', 'yy@ioio.com')

INSERT INTO TCorporateSponsers(intSponserID, strName, strCity, intStateID, intZip, strPhoneNumber, strEmail)
VALUES	 (1, 'Spiderman', 'Cincinnati', 1, '23232', '555 555 5554', 'yy@ioio.com')
		,(2, 'Superman', 'Cincinnati', 1, '23232', '555 555 5554', 'yy@ioio.com')
		,(3, 'Batman', 'Cincinnati', 1, '23232', '555 555 5554', 'yy@ioio.com')
		,(4, 'Wonder Woman', 'Cincinnati', 1, '23232', '555 555 5554', 'yy@ioio.com')
		,(5, 'The one Alien guy from the Justice League', 'Cincinnati', 1, '23232', '555 555 5554', 'yy@ioio.com')

INSERT INTO TEvents ( intEventID, dtmDate )
VALUES	 (1, '2015')
		,(2, '2016')

--INSERT INTO TEventTeams( intEventID, intTeamID, strReasonForJoining )
--VALUES	 (1, )

--INSERT INTO TEventSponsers(intEventID, intSponserID, intAffiliationID, monAmount)
--VALUES	 ()