--Drop Database MagazineSubscription
--create the database no options used
Create Database MagazineSubscription
Go
--use the database
Use MagazineSubscription

/*Create the tables with constraints
This needs to be done in a certain order
The primary key tables, the one side of
a relation must be done before the many
side of a relation */

Create table Magazine
(
  MagID int identity(1,1),
  MagName varchar(100) not null,
  MagType char(10) not null default 'Monthly',
  Constraint PK_Magazine Primary Key(MagID),
  
)

Create table SubscriptionType
(
	SubscriptTypeID int identity(1,1),
    SubscriptTypeName char(15),
    Constraint PK_SubscriptionType Primary Key (SubscriptTypeID)
)

Create table MagazineDetail
(
	MagDetID int Identity(1,1),
	MagID int not null,
    SubscriptTypeID int not null,
	SubscriptionPrice money,
    Constraint PK_MagazineDetails Primary Key(MagDetID),
    Constraint FK1_MagazineDetails Foreign key(MagID)
		References Magazine(magID),
    Constraint FK2_MagazineDetails Foreign Key(SubscriptTypeID)
		References SubscriptionType(subscriptTypeID)
)

Create Table Customer
(
	CustID int identity(1,1),
	CustLastName varchar(30) not null,
	CustFirstName varchar(25)null,
	CustAddress varchar(100) not null,
	CustCity varchar(50) not null,
	CustState char(2) not null,
	CustZipcode char(11) not null,
	CustPhone char(10),
	Constraint PK_Customer Primary Key (custID)
)

Create Table Subscription
(
	SubscriptionID int identity(1,1),
	CustID int not null,
	MagDetID int not null,
	SubscriptionStart DateTime not null,
	SubscriptionEnd Datetime,
    Constraint PK_Subscription Primary Key (subscriptionID),
	Constraint FK1_Subscription Foreign Key (CustID)
		References Customer(custID),
	Constraint FK2_Subscription Foreign Key(MagDetID)
		References MagazineDetail(MagDetID)
)

--insert into magazine

Insert into Magazine(MagName, MagType)
Values('Procastinators Anonymous','Monthly')

Insert into Magazine(MagName, MagType)
Values('IT Toys','Monthly')

Insert into Magazine(MagName, MagType)
Values('FireEaters Quarterly','Quarterly')

Insert into Magazine(MagName, MagType)
Values('Waste Not Want Not, A hoarders guide','Monthly')

Insert into Magazine(MagName, MagType)
Values('SQL Server','Monthly')

Insert into Magazine(MagName, MagType)
Values('Extreme Programming','Monthly')

Insert into Magazine(MagName, MagType)
Values('Insurance Actualization algorythms','Quarterly')

Insert into Magazine(MagName, MagType)
Values('Doctor Who Magazine','Weekly')

Insert into Magazine(MagName, MagType)
Values('XBox Anonymous','Monthly')

Insert into Magazine(MagName, MagType)
Values('Beer bottle Target Practice','Quarterly')


--Inserts for subscription types

Insert into SubscriptionType (subscriptTypeName)
Values('Six Month')

Insert into SubscriptionType (subscriptTypeName)
Values('Yearly')

Insert into SubscriptionType (subscriptTypeName)
Values('Three Month')

Insert into SubscriptionType (subscriptTypeName)
Values('Two Year')

Insert into SubscriptionType (subscriptTypeName)
Values('five Year')

Insert into SubscriptionType (subscriptTypeName)
Values('Three Year')

--Inserts into MagazineDetail

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(1, 1, 23.99)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(1, 2, 38.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(1, 5, 74.29)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(2, 1, 35.99)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(2, 2, 52.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(2, 4, 77.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(2, 5, 99.29)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(3, 2, 40.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(3, 5, 200.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(4, 1, 25.00)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(4, 3, 15.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(4, 4, 40.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(4, 5, 75.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(4, 6, 55.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(5, 2, 38.75)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(5, 6, 78.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(6, 2, 40.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(6, 6, 99.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(7, 5, 134.99)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(8, 1, 15.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(8, 2, 21.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(8, 3, 8.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(8, 4, 35.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(9, 2, 38.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(10, 5, 78.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(10, 6, 58.50)

--Inserts into Customer

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Jordan','Mary','2002 South Mercer Street','Seattle','WA',
'98190','2065558828')

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Danner','Thomas','100 Boardwalk South','Seattle','WA',
'98190','2065551001')

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Terrance','Sarah','202 Rt 3','Bellevue','WA',
'98120','3605550128')

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Camlin','Lenny','Somewhere Ave','Olympia','WA',
'98199','2535551010')

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Sanderson','Lewis','101 Elsewhere Avenue','Seattle','WA',
'98190','2065550987')

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Able','Tina','1000 West Blv','Bellingham','WA',
'98180','3605552020')

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Evans','Karl','11 North Hill street','Tacoma','WA',
'98100','2535558998')

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Zukof','Bob','WaterFront Blvd.','Bellevue','WA',
'98120','3605552435')

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Martinez','Patrick','EastLake Blvd.','Seattle','WA',
'98220','2065553679')

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Patterson','Lisa','1010 Binary Drive','Redmond','WA',
'98130','3605551100')

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Franklin','Bernice','222 ITC Road','Olympia','WA',
'98199','3605552221')

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Kim','Susan','111 Martin Luther King Way','Seattle','WA',
'98122','2065550742')

--Insert into subscription

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (1, 4, '1/15/2006', '7/15/2006')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (1, 14, '1/15/2006', '1/15/2007')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (2, 7, '1/15/2006', '1/15/2007')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (3, 8, '1/2/2006', '1/01/2011')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (4, 11, '2/01/2006', '2/01/2008')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (4, 4, '2/01/2006', '9/01/2006')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (5, 16, '1/05/2006', '1/05/2007')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (6, 5, '2/15/2006', '2/15/2008')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (6, 12, '2/15/2006', '2/15/2011')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (6, 18, '2/15/2006', '2/15/2011')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (7, 18, '3/01/2006', '3/01/2011')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (8, 20, '3/01/2006', '3/01/2007')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (9, 22, '3/10/2006', '3/10/2007')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (10, 4, '3/15/2006', '10/15/2006')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (10, 23, '3/15/2006', '3/15/2011')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (11, 11, '3/20/2006', '3/20/2008')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (12, 17, '4/01/2006', '4/01/2011')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (1, 18, '4/01/2006', '4/01/2011')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (4, 4, '4/15/2006', '10/15/2006')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (6, 24, '4/15/2006', '4/15/2009')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (10, 18, '4/15/2006', '4/15/2011')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (3, 14, '4/15/2006', '4/15/2007')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (12, 22, '4/15/2006', '4/15/2007')