Create a new database and execute the code below in SQL Servers query window to create the database tables.

CREATE TABLE PhysicianSpecialties

(SpecialtyID integer,

SpecialtyName varchar(50),

CONSTRAINT PK_PhysicianSpecialties PRIMARY KEY (SpecialtyID))

go

CREATE TABLE ZipCodes

(ZipCode varchar(10),

City varchar(50),

State varchar(2),

CONSTRAINT PK_ZipCodes PRIMARY KEY (ZipCode))

go

CREATE TABLE PhysicianPractices

(PracticeID integer,

PracticeName varchar(50),

Address_Line1 varchar(50),

Address_Line2 varchar(50),

ZipCode varchar(10),

Phone varchar(14),

Fax varchar(14),

WebsiteURL varchar(50),

CONSTRAINT PK_PhysicianPractices PRIMARY KEY (PracticeID),

CONSTRAINT FK_PhysicianPractices_ZipCodes FOREIGN KEY (ZipCode) REFERENCES Zipcodes)

go

CREATE TABLE Physicians

(PhysicianID integer,

FirstName varchar(40),

LastName varchar(50),

PracticeID integer,

SpecialtyID integer,

Email varchar(50),

CONSTRAINT PK_Physicians PRIMARY KEY (PhysicianID),

CONSTRAINT FK_Physicians_Practices FOREIGN KEY (PracticeID) REFERENCES PhysicianPractices,

CONSTRAINT FK_Physicians_PhysicianSpecialities FOREIGN KEY (SpecialtyID) REFERENCES PhysicianSpecialties)

go

CREATE TABLE Patients

(PatientID integer,

FirstName varchar(50),

MiddleInitial varchar(1),

LastName varchar(50),

Address_Line1 varchar(50),

Address_Line2 varchar(50),

ZipCode varchar(10),

Phone_Home varchar(14),

Phone_Alternate varchar(14),

Email varchar(50),

CONSTRAINT PK_Patients PRIMARY KEY (PatientID))

go

CREATE TABLE Referrals

(ReferralID integer,

StartDate smalldatetime,

EndDate smalldatetime,

PatientID integer,

PhysicianID integer,

CONSTRAINT PK_Referrals PRIMARY KEY (ReferralID),

CONSTRAINT FK_Referrals_Patients FOREIGN KEY (PatientID) REFERENCES Patients,

CONSTRAINT FK_Referrals_Physicians FOREIGN KEY (PhysicianID) REFERENCES Physicians)

go

CREATE TABLE Services

(ServiceID integer,

ServiceName varchar(50),

CONSTRAINT PK_ServiceID PRIMARY KEY (ServiceID))

go

CREATE TABLE Frequencies

(FrequencyID integer,

Frequency varchar(30),

CONSTRAINT PK_Frequencies PRIMARY KEY (FrequencyID))

go

CREATE TABLE ReferralServices

(ReferralID integer,

ServiceID integer,

FrequencyID integer,

CONSTRAINT PK_ReferralServices PRIMARY KEY (ReferralID, ServiceID),

CONSTRAINT FK_ReferralServices_Referrals FOREIGN KEY (ReferralID) REFERENCES Referrals,

CONSTRAINT FK_ReferralServices_Services FOREIGN KEY (ServiceID) REFERENCES Services,

CONSTRAINT FK_ReferralServices_Frequencies FOREIGN KEY (FrequencyID) REFERENCES Frequencies)

go

CREATE TABLE PaymentTypes

(PaymentTypeID integer,

PaymentType varchar(25),

CONSTRAINT PK_PaymentTypes PRIMARY KEY (PaymentTypeID))

go

CREATE TABLE InsuranceCompanies

(InsuranceID integer,

InsuranceCompany varchar(50),

Address_Line1 varchar(50),

Address_Line2 varchar(50),

ZipCode varchar(10),

Phone varchar(15),

Fax varchar(15),

Email varchar(50),

CONSTRAINT PK_InsuranceCompanies PRIMARY KEY (InsuranceID),

CONSTRAINT FK_InsuranceCompanies_ZipCodes FOREIGN KEY (ZipCode) REFERENCES ZipCodes)

go

CREATE TABLE Contracts

(ContractID integer,

ReferralID integer,

StartDate smalldatetime,

EndDate smalldatetime,

PaymentTypeID integer,

InsuranceID integer,

NegotiatedRate float,

CONSTRAINT PK_Contracts PRIMARY KEY (ContractID),

CONSTRAINT PK_Contracts_Referrals FOREIGN KEY (ReferralID) REFERENCES Referrals,

CONSTRAINT FK_Contracts_PaymentTypes FOREIGN KEY (PaymentTypeID) REFERENCES PaymentTypes,

CONSTRAINT FK_Contracts_InsuranceCompanies FOREIGN KEY (InsuranceID) REFERENCES InsuranceCompanies)

go

CREATE TABLE EmployeeTypes

(EmployeeTypeID integer identity,

EmployeeType varchar(25),

CONSTRAINT PK_EmployeeTypes PRIMARY KEY (EmployeeTypeID))

go

CREATE TABLE EmployeeTitles

(EmployeeTitleID integer,

EmployeeTitle varchar(30),

CONSTRAINT PK_EmployeeTitles PRIMARY KEY (EmployeeTitleID))

go

CREATE TABLE EmployeeSkillLevels

(SkillLevelID integer,

SkillLevel varchar(15),

CONSTRAINT PK_EmployeeSkillLevels PRIMARY KEY (SkillLevelID))

go

CREATE TABLE BillingRates

(EmployeeTypeID integer,

SkillLevelID integer,

BillingRate float,

CONSTRAINT PK_PrimaryKey PRIMARY KEY (EmployeeTypeID, SkillLevelID),

CONSTRAINT FK_BillingRates_EmployeeTypes FOREIGN KEY (EmployeeTypeID) REFERENCES EmployeeTypes,

CONSTRAINT FK_BillingRates_EmployeeSkillLevels FOREIGN KEY (SkillLevelID) REFERENCES EmployeeSkillLevels)

go

CREATE TABLE EmployeeRanks

(RankID integer,

EmployeeTypeID integer,

TitleID integer,

SkillLevelID integer,

HourlyRate float,

Salary float,

CONSTRAINT PK_EmployeeRanks PRIMARY KEY (RankID),

CONSTRAINT FK_EmployeeRanks_EmployeeTypes FOREIGN KEY (EmployeeTypeID) REFERENCES EmployeeTypes,

CONSTRAINT FK_EmployeeRanks_EmployeeTitles FOREIGN KEY (TitleID) REFERENCES EmployeeTitles,

CONSTRAINT FK_EmployeeRanks_EmployeeSkillLevels FOREIGN KEY (SkillLevelID) REFERENCES EmployeeSkillLevels)

go

CREATE TABLE Employees

(EmployeeID integer,

FirstName varchar(30),

MiddleInitial varchar(1),

LastName varchar(50),

Address_Line1 varchar(50),

Address_Line2 varchar(50),

ZipCode varchar(10),

Phone varchar(14),

Cell_Phone varchar(14),

Email varchar(50),

RankID integer,

HourlyWage float,

Salary float,

CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID),

CONSTRAINT FK_Employees_EmployeeRanks FOREIGN KEY (RankID) REFERENCES EmployeeRanks,

CONSTRAINT FK_Employee_ZipCodes FOREIGN KEY (ZipCode) REFERENCES ZipCodes)

go

CREATE TABLE Shifts

(ShiftID integer,

ShiftName varchar(20),

StartTime time,

EndTime time,

CONSTRAINT PK_Shifts PRIMARY KEY (ShiftID))

go

CREATE TABLE DaysOfWeek

(DayOfWeekID integer,

DayOfWeek varchar(15),

CONSTRAINT PK_DaysOfWeek PRIMARY KEY (DayOfWeekID))

go

CREATE TABLE Availability

(EmployeeID integer,

WeekOf smalldatetime,

DayOfWeekID integer,

ShiftID integer,

CONSTRAINT PK_Availability PRIMARY KEY (EmployeeID, WeekOf, DayOfWeekID, ShiftID),

CONSTRAINT FK_Availability_Employees FOREIGN KEY (EmployeeID) REFERENCES Employees,

CONSTRAINT FK_Availability_DaysOfWeek FOREIGN KEY (DayOfWeekID) REFERENCES DaysOfWeek,

CONSTRAINT FK_Availability_Shifts FOREIGN KEY (ShiftID) REFERENCES Shifts)

go

CREATE TABLE MedicalSuppliers

(SupplierID integer,

SupplierName varchar(50),

Address_Line1 varchar(50),

Address_Line2 varchar(50),

ZipCode varchar(10),

Phone varchar(14),

Fax varchar(14),

Email varchar(50),

CONSTRAINT PK_MedicalSuppliers PRIMARY KEY (SupplierID),

CONSTRAINT FK_MedicalSuppliers_ZipCodes FOREIGN KEY (ZipCode) REFERENCES ZipCodes)

go

CREATE TABLE Supplies

(SupplyID integer,

SupplyDescription varchar(40),

ChargePerUnit float,

CONSTRAINT PK_Supplies PRIMARY KEY (SupplyID))

go

CREATE TABLE SupplyInventory

(SupplyID integer,

SupplierID integer,

DateReceived smalldatetime,

UnitCost float,

Quantity float,

CONSTRAINT PK_SupplyInventory PRIMARY KEY (SupplyID, SupplierID, DateReceived),

CONSTRAINT FK_SupplyInventory_Supplies FOREIGN KEY (SupplyID) REFERENCES Supplies,

CONSTRAINT FK_SupplyInventory_Suppliers FOREIGN KEY (SupplierID) REFERENCES MedicalSuppliers)

go

CREATE TABLE Visits

(VisitID integer,

DateRendered smalldatetime,

StartTime time,

EndTime time,

EmployeeID integer,

PatientID integer,

CONSTRAINT PK_Visits PRIMARY KEY (VisitID),

CONSTRAINT FK_Visits_Employees FOREIGN KEY (EmployeeID) REFERENCES Employees,

CONSTRAINT FK_Visits_Patients FOREIGN KEY (PatientID) REFERENCES Patients)

go

CREATE TABLE VisitDetails

(VisitID integer,

VisitDetailID integer,

SupplyID integer,

SupplyQuantity integer,

ServiceID integer,

Charge float,

CONSTRAINT PK_VisitDetails PRIMARY KEY (VisitID, VisitDetailID),

CONSTRAINT FK_VisitDetaiils_Supplies FOREIGN KEY (SupplyID) REFERENCES Supplies,

CONSTRAINT FK_VisitDetails_Services FOREIGN KEY (ServiceID) REFERENCES Services)

go

11. Display a list of all referrals whose start date was in 2013.	Patient first name, followed by a space, followed by patient last name (Call this whole field Patient Name), Referring Physician Last Name (call this field Physician), StartDate, EndDate Sort Order: StartDate ascending Patient First Name ascending Physician Last Name - ascending
12. Display a list of all the referrals whose start date is between October 1, 2014 and November 5, 2014	Patient first name, followed by a space, followed by patient last name (Call this whole field Patient Name), Referring Physician Last Name (call this field Physician), StartDate, EndDate Sort Order: StartDate ascending Patient First Name ascending Physician Last Name - ascending
13. Display the number of referrals given by each physician	Physician Last name, Physician First Name, number of referrals Sort Order: Physician Last Name ascending Physician First Name ascending
14. List the number of referrals in 2014 for each service requested.	Service name, number of referrals Sort order: Service name
15. Display a list of all patients requiring exercise therapy in 2013	Patient Last Name, Patient First Name Sort order: Patient last name ascending Patient first name ascending
16. Display a list of any referrals that require Insulin injections and 2x Daily is NOT listed as their frequency.	Patient Last Name, Physician Last Name, referral start date Sort order: Physician Last Name ascending Patient Last Name ascending Referral Start Date ascending
17. Display the contracts and payment methods associated with each referral	Patient Last Name, Physician Last Name, Referral Start Date, Contract Start Date, Payment Method Sort Order: Payment Method - ascending Physician Last Name ascending Patient Last Name ascending Referral Start Date ascending Contract Start Date ascending
18. Display the number of contracts whose payment method is Insurance	Number of contracts (This is a single value)
19. Display the number of contracts whose payment method is Insurance, broken out by Insurance Company	Insurance Company Name, number of contracts Sort order: Insurance company name
20. List the Employees who are Nurses	Employee First Name, followed by a space, followed by Employee Middle Initial, followed by a space, followed by Employee Last Name (call this whole field Nurses)