Use Master
Go
if exists
	(Select "Name" from Sys.Databases
		Where "Name"='DentalOffice')
Begin
Drop Database DentalOffice
End
Go
Create database DentalOffice
Go
Use DentalOffice
Go
Create Table CareGiverType
( 
	CareGiverTypeKey int identity(1,1) primary Key,
	CaregiverTypeName nvarchar(20)
)
Go
Create Table CareGiver
(
	CareGiverKey int identity(1,1) primary key,
	CareGiverLastName nvarchar(50) not null,
	CareGiverFirstName nvarchar(50),
	CareGiverTypeKey int Foreign Key references CareGiverType(CareGiverTypeKey)
)
Go
Create Table Patient
(
	PatientKey int identity(1,1) Primary Key,
	PatientLastName nvarchar(50) not null,
	PatientFirstName nvarchar(50),
	PatientPhone nchar(13) not null
)
Go
Create Table Insurance
(
	InsuranceKey int identity(1,1) Primary Key,
	InsuranceName nvarchar(100) not null
)
Go
Create Table PatientInsurance
(
	PatientKey int,
	InsuranceKey int,
	Primary key (PatientKey, InsuranceKey)
)
Go
Create table DentalService
(
	DentalServiceKey int identity(1,1) primary Key,
	DentalServiceName nvarchar(200) not null,
	DentalServicePrice money not null
)
Go
Create Table Appointment
(
	AppointmentKey int identity(1,1) primary key,
	AppointmentDatetime DateTime not null,
	PatientKey int foreign key references Patient(PatientKey)
)
Go
Create Table AppointmentCareGiver
(
	CareGiverKey int,
	AppointmentKey int,
	Primary Key (CaregiverKey, AppointmentKey)

)
Go
Create Table AppointmentService
(
	AppointmentServiceKey int identity(1,1) primary key,
	AppointmentKey int Foreign key references Appointment(AppointmentKey),
	DentalServiceKey int Foreign Key references DentalService(DentalServiceKey)
)
Insert into CareGiverType(CareGiverTypeName)
Values('Dentist')
Insert into CareGiverType(CareGiverTypeName)
Values('Hygenist')
Insert into CareGiverType(CareGiverTypeName)
Values('Assistant')

Insert into CareGiver (CareGiverLastName, CareGiverFirstName, CareGiverTypeKey)
Values('Sanderson', 'Aimee', 1)
Insert into CareGiver (CareGiverLastName, CareGiverFirstName, CareGiverTypeKey)
Values('Lewis', 'Arnold', 2)
Insert into CareGiver (CareGiverLastName, CareGiverFirstName, CareGiverTypeKey)
Values('Pound', 'Joyce', 3)
Insert into CareGiver (CareGiverLastName, CareGiverFirstName, CareGiverTypeKey)
Values('Masters', 'Thomas', 1)
Insert into CareGiver (CareGiverLastName, CareGiverFirstName, CareGiverTypeKey)
Values('Brown', 'Susan', 3)
Insert into CareGiver (CareGiverLastName, CareGiverFirstName, CareGiverTypeKey)
Values('Patterson', 'Jill', 3)
Insert into CareGiver (CareGiverLastName, CareGiverFirstName, CareGiverTypeKey)
Values('Taylor', 'Earl', 1)
Insert into CareGiver (CareGiverLastName, CareGiverFirstName, CareGiverTypeKey)
Values('Jackson', 'Brenda', 2)

Insert into Patient(PatientLastName, PatientFirstName, PatientPhone)
Values('Smiley','Rhonda','2065551234')
Insert into Patient(PatientLastName, PatientFirstName, PatientPhone)
Values('Able','Lester','2065555432')
Insert into Patient(PatientLastName, PatientFirstName, PatientPhone)
Values('Farmer','Lisa','2065559392')
Insert into Patient(PatientLastName, PatientFirstName, PatientPhone)
Values('Pauly','Dennis','2065553256')
Insert into Patient(PatientLastName, PatientFirstName, PatientPhone)
Values('Erickson','Tracy','2065550912')
Insert into Patient(PatientLastName, PatientFirstName, PatientPhone)
Values('Bates','Brandon','2065552964')
Insert into Patient(PatientLastName, PatientFirstName, PatientPhone)
Values('Thomas','Faye','2065553187')
Insert into Patient(PatientLastName, PatientFirstName, PatientPhone)
Values('Carlson','Robert','2065553256')
Insert into Patient(PatientLastName, PatientFirstName, PatientPhone)
Values('Daniels','Laura','2065553232')
Insert into Patient(PatientLastName, PatientFirstName, PatientPhone)
Values('Green','Bill','2065553239')
Insert into Patient(PatientLastName, PatientFirstName, PatientPhone)
Values('Wise','Kelly','2065550192')
Insert into Patient(PatientLastName, PatientFirstName, PatientPhone)
Values('Patten','Laura','2065558765')

Insert into Insurance (InsuranceName)
Values('Group Health')
Insert into Insurance (InsuranceName)
Values('State Dental')
Insert into Insurance (InsuranceName)
Values('Blue Cross')
Insert into Insurance (InsuranceName)
Values('Premium Health')
Insert into Insurance (InsuranceName)
Values('Sepica Supplimental')
Insert into Insurance (InsuranceName)
Values('Self')


Insert into PatientInsurance(PatientKey, InsuranceKey)
Values(1,1)
Insert into PatientInsurance(PatientKey, InsuranceKey)
Values(1,5)
Insert into PatientInsurance(PatientKey, InsuranceKey)
Values(2,1)
Insert into PatientInsurance(PatientKey, InsuranceKey)
Values(3,6)
Insert into PatientInsurance(PatientKey, InsuranceKey)
Values(4,1)
Insert into PatientInsurance(PatientKey, InsuranceKey)
Values(5,2)
Insert into PatientInsurance(PatientKey, InsuranceKey)
Values(6,2)
Insert into PatientInsurance(PatientKey, InsuranceKey)
Values(6,5)
Insert into PatientInsurance(PatientKey, InsuranceKey)
Values(7,3)
Insert into PatientInsurance(PatientKey, InsuranceKey)
Values(8,3)
Insert into PatientInsurance(PatientKey, InsuranceKey)
Values(9,2)
Insert into PatientInsurance(PatientKey, InsuranceKey)
Values(10,6)
Insert into PatientInsurance(PatientKey, InsuranceKey)
Values(11,1)
Insert into PatientInsurance(PatientKey, InsuranceKey)
Values(12,4)


Insert into DentalService (DentalServiceName, DentalServicePrice)
Values('Cavity repair',300.50)
Insert into DentalService (DentalServiceName, DentalServicePrice)
Values('Cleaning',215.75)
Insert into DentalService (DentalServiceName, DentalServicePrice)
Values('root canal',850.00)
Insert into DentalService (DentalServiceName, DentalServicePrice)
Values('crown',766.65)
Insert into DentalService (DentalServiceName, DentalServicePrice)
Values('extraction',250.99)
Insert into DentalService (DentalServiceName, DentalServicePrice)
Values('bridge',1855.33)
Insert into DentalService (DentalServiceName, DentalServicePrice)
Values('Checkup',210.00)


Insert into Appointment(AppointmentDatetime, patientKey)
Values('4/3/2009 10:00 AM',2)
Insert into AppointmentCareGiver(AppointmentKey,CareGiverKey)
Values(1,1)
Insert into AppointmentCareGiver(AppointmentKey,CareGiverKey)
Values(1,5)
Insert into AppointmentService(AppointmentKey,DentalServiceKey)
Values(1,1)

Insert into Appointment(AppointmentDatetime, patientKey)
Values('4/3/2009 11:00 AM',6)
Insert into AppointmentCareGiver(AppointmentKey,CareGiverKey)
Values(2,1)
Insert into AppointmentCareGiver(AppointmentKey,CareGiverKey)
Values(2,5)
Insert into AppointmentService(AppointmentKey,DentalServiceKey)
Values(2,1)

Insert into Appointment(AppointmentDatetime, patientKey)
Values('4/3/2009 1:00 PM',4)
Insert into AppointmentCareGiver(AppointmentKey,CareGiverKey)
Values(3,1)
Insert into AppointmentCareGiver(AppointmentKey,CareGiverKey)
Values(3,2)
Insert into AppointmentService(AppointmentKey,DentalServiceKey)
Values(3,6)
Insert into AppointmentService(AppointmentKey,DentalServiceKey)
Values(3,2)

Insert into Appointment(AppointmentDatetime, patientKey)
Values('4/3/2009 9:00 AM',8)
Insert into AppointmentCareGiver(AppointmentKey,CareGiverKey)
Values(4,4)
Insert into AppointmentCareGiver(AppointmentKey,CareGiverKey)
Values(4,6)
Insert into AppointmentService(AppointmentKey,DentalServiceKey)
Values(4,5)

Insert into Appointment(AppointmentDatetime, patientKey)
Values('4/3/2009 1:00 PM',3)
Insert into AppointmentCareGiver(AppointmentKey,CareGiverKey)
Values(5,5)
Insert into AppointmentCareGiver(AppointmentKey,CareGiverKey)
Values(5,6)
Insert into AppointmentService(AppointmentKey,DentalServiceKey)
Values(5,4)

Insert into Appointment(AppointmentDatetime, patientKey)
Values('4/3/2009 1:00 PM',9)
Insert into AppointmentCareGiver(AppointmentKey,CareGiverKey)
Values(6,7)
Insert into AppointmentCareGiver(AppointmentKey,CareGiverKey)
Values(6,8)
Insert into AppointmentService(AppointmentKey,DentalServiceKey)
Values(6,7)
Insert into AppointmentService(AppointmentKey,DentalServiceKey)
Values(6,2)

Insert into Appointment(AppointmentDatetime, patientKey)
Values('4/4/2009 10:00 AM',10)
Insert into AppointmentCareGiver(AppointmentKey,CareGiverKey)
Values(7,1)
Insert into AppointmentCareGiver(AppointmentKey,CareGiverKey)
Values(7,2)
Insert into AppointmentService(AppointmentKey,DentalServiceKey)
Values(7,6)
Insert into AppointmentService(AppointmentKey,DentalServiceKey)
Values(7,2)

Insert into Appointment(AppointmentDatetime, patientKey)
Values('4/4/2009 10:00 AM',11)
Insert into AppointmentCareGiver(AppointmentKey,CareGiverKey)
Values(8,5)
Insert into AppointmentCareGiver(AppointmentKey,CareGiverKey)
Values(8,6)
Insert into AppointmentService(AppointmentKey,DentalServiceKey)
Values(8,4)

Insert into Appointment(AppointmentDatetime, patientKey)
Values('4/4/2009 11:00 AM',9)
Insert into AppointmentCareGiver(AppointmentKey,CareGiverKey)
Values(9,7)
Insert into AppointmentCareGiver(AppointmentKey,CareGiverKey)
Values(9,8)
Insert into AppointmentService(AppointmentKey,DentalServiceKey)
Values(9,6)


Insert into Appointment(AppointmentDatetime, patientKey)
Values('4/4/2009 2:00 PM',10)
Insert into AppointmentCareGiver(AppointmentKey,CareGiverKey)
Values(10,1)
Insert into AppointmentCareGiver(AppointmentKey,CareGiverKey)
Values(10,2)
Insert into AppointmentService(AppointmentKey,DentalServiceKey)
Values(10,3)