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

21. Display the average hourly wage for all employees who are aides.	Average hourly wage (single value)
22. Display the average hourly wage for all hourly employees broken out by level.	Skill level, average wage Sort order: Skill Level
23. Display the total salary for all salaried employees.	Total salaries (single value)
24. Display the number of employees assigned to each rank.	RankID, Employee Type, Skill Level, Employee Title, number of employees Sort Order: RankID ascending Employee type ascending Skill Level ascending Employee Title ascending
25. Display a list of Employees who are nurses and were available to work on Sunday evenings during the week of 11/2/2014	Employee Last Name, Employee First Name Sort order: Last Name ascending First Name ascending
26. Display a list of Employees who were available to work during morning shifts during the week of 11/2/2014 and had a skill level of level 3.	Employee Last Name, Employee First Name, Employee Type, Employee Title Sort order: Employee Type ascending Employee Title ascending Employee Last Name ascending Employee First Name ascending
27. Display the total quantity of catheters added to inventory during 2013.	Total catheters (single value)
28. Display the total cost of sterile gloves small provided by Pooles Medical supplies during 2013.	Total cost (single value)
29. Display the average cost of supplies for each supply item broken out by supplier.	Supply, Supplier, Average cost per supply item Sort order: Supply ascending Supplier ascending
30. Display the total cost of all items purchased from suppliers broken out by supplier.	Supplier, Total cost of all items provided by supplier Sort order: Supplier ascending