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

31. Display a list of all the visits that occurred from March 20, 2014 to March 25, 2014 (including March 20 and March 25)	DateRendered, Patient Last Name, Employee Last Name, Start Time, End time Sort order: DateRendered ascending Patient Last Name ascending Employee Last Name ascending Start Time ascending
32. List the total charges for the visit that occurred on 2/12/2014 for Helen Ramirez that was provided by Laura White.	Total charges (single value)
33. List the number of patients who received insulin injections during 2014 (Note this is the number of unique patients who ever received insulin injections not the number of visits in which insulin injections were provided).	Total number of patients (single value)
34. List the total number of 4 self-adhesive bandages that were used in 2014	Total number of 4 self-adhesive bandages (single value)
35. List the average charge per visit per month in 2013 broken out by months	Month, average cost per visit Sort order: month number - ascending
36. Provide a unique list of patients who received visits for feeding from November 1, 2014 until the current date.	Patient Last Name, Patient First Name Sort order: Patient Last Name ascending Patient First Name ascending