use Master
Go
if exists
	(Select [name] from sys.Databases
       Where [name]='ClinicalDrugStudy')
Begin
drop database ClinicalDrugStudy
End

Create Database ClinicalDrugStudy
Go
use ClinicalDrugStudy
Go

Create Table Ethnicity
(
  EthnicityID int identity(1,1) primary Key,
  ethName nvarchar(50) not null
)
GO
Create table Patient
(
  PatientID int identity(1000,1) primary key,
  patLastName nvarchar(255) not null,
  patFirstName nvarchar(255) null,
  patAddress nvarchar(255) not null,
  patCity nvarchar(255) not null,
  patState nchar(2) not null,
  patZipCode nchar(10) not null,
  patPhone nchar(10) not null,
  patEmail nvarchar(255) null,
  patGender nchar(1) null,
  EthnicityID int Foreign Key references Ethnicity(EthnicityID),
  patBirthDate datetime null,
  patStartDate datetime null,
  patEndDate datetime null
)
Go

Create table MedicalHistory
(
  PatientID int primary key,
  constraint FK_Patient01 foreign key (PatientID)
	references Patient(PatientID),
  mdhHeartdisease bit,
  mdhKidneyDisease bit,
  mdhDiabetes bit,
  mdhLiverProblems bit,
  mdhDepressionMedications bit,
  Explanations nvarchar(max) null
)
GO
Create table PatientGroup
(
   PatientID int,
   GroupId char(1),
   Constraint PK_PatientGroup Primary Key(PatientID,GroupID),
   Constraint FK_Patient02 Foreign Key(PatientID)
	References Patient(PatientID)
)
GO
Create table Doctor
(
  DoctorID int identity(1,1) primary Key,
  docLastName nvarchar(255)not null,
  docFirstName nvarchar(255) null,
  docAddress nvarchar(255) not null,
  docCity nvarchar(255) not null,
  docState nchar(2) not null,
  docZipCode nchar(10) not null
)
GO
Create table DoctorContact
(
  DoctorContactID int identity(1,1) primary Key,
  DoctorID int Foreign key references Doctor(DoctorID),
  drcContactType nvarchar(50) not null,
  drcContactinfo nvarchar(255) not null
)
Go
Create table DoctorPatient
(
   DoctorID int Foreign Key references Doctor(doctorID),
   patientID int foreign Key references Patient(PatientID),
   Constraint PK_DoctorPatient primary Key(DoctorID, PatientID),
   drpAssignmentDate datetime not null

)
Go
Create Table Appointment
(
  AppointmentID int identity(1,1) primary key,
  DoctorID int foreign key references Doctor(DoctorID),
  PatientID int foreign key references Patient(PatientID),
  aptDate DateTime not null,
  aptKept bit
)

Go

Create xml Schema collection notesSchema
AS 
'<?xml version="1.0"?>

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
targetNamespace="http://www.soundlakehospital.org/doctornotes" 
xmlns:nts="http://www.soundlakehospital.org/doctornotes" 
elementFormDefault="qualified">

<xsd:element name="doctornotes">
 <xsd:complexType>
    <xsd:sequence>
	<xsd:element name="narrative" minOccurs="1" maxOccurs="1"/>
        <xsd:element name="concerns">
	  <xsd:complexType>
	    <xsd:sequence>
		<xsd:element name="sideeffects" minOccurs="1" maxOccurs="unbounded"/>
                <xsd:element name="depression" minOccurs="1" maxOccurs="unbounded" />
		<xsd:element name="health" minOccurs="1" maxOccurs="unbounded"/>
		<xsd:element name="other" minOccurs="1" maxOccurs="unbounded" />
	   </xsd:sequence>
	 </xsd:complexType>
	</xsd:element>
        <xsd:element name="directions" minOccurs="1" maxOccurs="unbounded" />
   </xsd:sequence>
 </xsd:complexType>
</xsd:element>
</xsd:schema>'
Go

Create table AppointmentDetail
(
  AppointmentDetailID int identity (1,1) Primary Key,
  AppointmentID int Foreign key references Appointment(AppointmentID),
  apdHeartRate int null,
  apdTemperature decimal(4,1) null,
  apdBloodPressureDiastolic int null,
  apdBloodPressureSystolic int null,
  apdLiverEnzymes decimal(5,2) null,
  apdBloodSugar int null, 
  apdNotes xml(notesSchema) null
)