Create a new database and execute the code below in SQL Server's query window to create the database tables, CREATE TABLE Physician Specialties (Specialty.In integer, SpesialtyName varchar(5e), CONSTRAINT PK Physicianspecialties. PRIMARY KEY (SpecialtxIR)) go CREATE TABLE ZipCodes, (Zipcode varchar(10), City varchar(5e), State varchar(2), CONSTRAINT PK.Zipcodes PRIMARY KEY (ZipCode) go CREATE TABLE Physician Practices (Practice in integer, PracticeName varchar(50) Address Linen varchar(5e Address Line2 varchar(50) Zipcode varchar 10 Phone varchar/14), Fax Vanchan 143 WebsiteURL varchar(se) CONSTRAINT PK PhysicianPractices PRIMARY KEY PracticeID CONSTRAINT EK. PhysicianPractices zipcodes. FOREIGN KEY Zipcode REFERENCES zipcodes.) go CREATE TABLE Physicians (PhysicianIR integer, First Nane varchar(40), Last Name, varchar(5e). PracticeIn integer, Specialty ID integer, Email varchar(5e), CONSTRAINT PK Physicians. PRIMARY KEY (PhysicianIR), CONSTRAINT EK Physicians..Ccactices. FOREIGN KEY (ecasticeID) REFERENCES. PhysicianPractices. CONSTRAINT EK..Physicians. PhysicianSpecialities. FOREIGN KEY (SpecialtyID) REFERENCES PhysicianSpecialties) go CREATE TABLE Patients (Patient IR integer, FirstName varchar(50) Middle Initial varchar(1), Last Name, varchar(5e), 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 (Patient ID go CREATE TABLE Referrals (RefecclIR Integer StartDate small datetiine, EndDate salidatetime, Patienten integer, Physician IR integer, CONSTRAINT PK. Referrals PRIMARY KEY (Refeccalin). CONSTRAINT EK. Referrals. Patients FOREIGN KEY (Patient ID) REFERENCES Patients, CONSTRAINT EK Referrals Physicians FOREIGN KEY (Physician IR) REFERENCES Physicians) go CREATE TABLE Services (ServiceID integer, ServiceName varchar(50), CONSTRAINT PKServiceIN PRIMARY KEY (ServiceID)) go CREATE TABLE Frequencies (Erequens. Integer Frequency varchar(30) CONSTRAINT PKL Frequencies. PRIMARY KEY (Erequency.IR go CREATE TABLE ReferralServices (Referralin integer, Service ID integer, Frequency ID integer CONSTRAINT PK RefeccelServices PRIMARY KEY ReferralI. ServiceID CONSTRAINT EK Referralservices Referrals FOREIGN KEY (ReferralID REFERENCES Referrals CONSTRAINT EK Referralservices Services FOREIGN KEY ServiceID REFERENCES Services CONSTRAINT EK Referralservices Frequencies. FOREIGN KEY CE.requency ID REFERENCES Frequencies) go CREATE TABLE PaymentTypes. (Payment TypeID integer, Payment Type varchar(25), CONSTRAINT PK Payment Types. PRIMARY KEY (PaymentTypeID)) go CREATE TABLE InsuranceCompanies (Insurance ID integer, Insurance Company, varchar(50), Address_Line1 varchar(50), Address_line2 varchar(50), ZipCode varchar(10), Phone varchar15), Fax varchar(15). Email varchar(5e), CONSTRAINT PK Insurance Companies. PRIMARY KEY (InsuranceID CONSTRAINT EK Insurance Companies.ZipCodes FOREIGN KEY (ZipCode REFERENCES Zipcodes.) go CREATE TABLE Contracts ContractID integer efect integer StartDate smalldatetime EndDate smalldatetime Payment Type IR Integer Insurance ID integer Negotiatedrats float, CONSTRAINT PKL.cont.casts. PRIMARY KEY (Contractin), CONSTRAINT PK.contcast Referrada, FOREIGN KEY (Referral) REFERENCES Referrals, CONSTRAINT EK Contcasts Paymentines FOREIGN KEY (PaymentTypeID) REFERENCES Payusates, KCONSTRAINT EK..Cont.casts Insurance Conradies FOREIGN KEY (InsuranceIR) REFERENCES Lasurancescananies.) BO CREATE TABLE Employee Tyres EmployeeTypeID integer identity Eorleyeeltype varchar25) CONSTRAINT PL.EmployssTypes PRIMARY KEY Employee REAR go CREATE TABLE EmployeeSkilllevels (SkilllevelID integer, Skilllevel varchar(15), CONSTRAINT PK Employeeskill levels PRIMARY KEY (Skilllevel ID)) Go CREATE TABLE BillingRates (Employee TYRSID integer, Skillevel ID integer, BillingRate float, CONSTRAINT PK PrimaryKey PRIMARY KEY (EmployeeTypeID, SkilllevelID). CONSTRAINT EK BillingRates EmployeeTypes. FOREIGN KEY (Employee TypeID) REFERENCES Employee Types CONSTRAINT EK. BillingRates Employeeskilllevels. FOREIGN KEY (SkilllevelID REFERENCES Employeeskilllevels) go CREATE TABLE EmployeeRanks, (RankID integer, Employee type ID integer, Title ID integer, Skilll.exelik integer, HourlyRate float, Salary float, CONSTRAINT PK.E.n.loveeRanks. PRIMARY KEY (RankIR), CONSTRAINT EK. EmployeeRanks. Employee Iynes. FOREIGN KEY (Employee TypeID) REFERENCES Employee types. CONSTRAINT EK. En loveeRanks. EmployeeTitles. FOREIGN KEY (TitleID REFERENCES Employee titles, CONSTRAINT EK. EnnloveeRanks. Employeeskilllevels. FOREIGN KEY (SkilllevelIN) REFERENCES Employeeskilllexels) go CREATE TABLE Employees Emn Layee IR integer, FirstName varchar(30), MiddleInitial varchar(1), Last Name varchar(50) Address Line1 varchar(52), 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 (Employee ID CONSTRAINT EKLEmployees. Employee Ranks. FOREIGN KEY Rankin) REFERENCES EmployeeRanks CONSTRAINT ELLER Lovee.zipcodes. FOREIGN KEY ZinCode REFERENCES ZIRCodes RO n Focus CREATE TABLE Shifts (Shift in integer, Shift Nane, varchar(20), Startline time, EndTime, tine, CONSTRAINT PK Shifts. PRIMARY KEY (ShiftID)) go CREATE TABLE DaysofWeek (DayOfWeekin integer, Daxofbleek varchar(15), CONSTRAINT PK.RaxsQfWeek PRIMARY KEY (RayofWeek ID)) go CREATE TABLE Availability (EmployeeIR integer, Weekgf. smalldatetime, RayofWeekID integer Shiftis integer CONSTRAINT PK Availability PRIMARY KEY Employee ID. WeekQf. DayOfWeekID ShiftID). CONSTRAINT EK Availability Employees. FOREIGN KEY (EmployeeID) REFERENCES Employees. CONSTRAINT EK Availability Daysofweek FOREIGN KEY DayOfWeekID REFERENCES Raysofkeek, CONSTRAINT EK Availability. Shifts FOREIGN KEY ShiftID REFERENCES Shifts) go CREATE TABLE Medical Suppliers. (Supplier in integer, Supplier Nane varchar(50), Address Line1 varchar(50), Address Line2 varchar(50), Zincade varchar(10), Phone varchar(14), Fax varchar(14), Email varchar(50), CONSTRAINT PK MedicalSuppliecs. PRIMARY KEY (Suppliecin), CONSTRAINT EK_MedicalSuppliecs Zincodes. FOREIGN KEY (zipcode) REFERENCES Zincades.) go CREATE TABLE Supplies (Supply.IR integer, SupplyDescription varchar(40), Share pecunit float, CONSTRAINT PK Supplies PRIMARY KEY (SurlyID)) go CREATE TABLE Supply Inventory (SupplyID integer, SupplieciR Integer DateReceived smalidatetime Unit Cost float Quantity float CONSTRAINT PK SupplyInventory. PRIMARY KEY SURRIYID Suppliecin RateReceived). CONSTRAINT EK...SupplyInventory Supplies FOREIGN KEY SunRlYIR REFERENCES Supplies CONSTRAINT EK...Supplytaventory Suprllecs FOREIGN KEY SuppliecIR REFERENCES MedicalSuppliers.) CONSTRAINT EKSumn winxentory Surn Itecs. FOREIGN KEY (SunnerID) REFERENCES Medica Sunters 80 CREATE TABLE Visits (VisitID integer, DateRendered smalldatetime. Start Time time, EndTime, time, Employee ID integer Patient IQ. Integer CONSTRAINT PK Visits PRIMARY KEY Visit ID CONSTRAINT EK Visits. Employees FOREIGN KEY EmployeeIR REFERENCES Employees CONSTRAINT EK Visits Patients. FOREIGN KEY PatientID REFERENCES Patients 20 D Focus CREATE TABLE VisitRetails (Visitis integer, VisitDetailir integer, Sunnlydd integer, SupplyQuantity integer, ServiceIn integer, Charge float, CONSTRAINT PK VisitRetails. PRIMARY KEY (Visitid. VisitDetailin). CONSTRAINT EK VisitDetaiils Supplies. FOREIGN KEY (SupplyIR) REFERENCES Supplies, CONSTRAINT EK VisitDetails Services. FOREIGN KEY (ServiceID REFERENCES Services) go Write and execute queries to perform the following functions: Criteria Output Format Your Answer: Type your query in this column. The first one has been done for you. Screenshots should be pasted below this table. (Make sure the number of rows that are returned appears in the screenshot.) 1. Display a list of Patient Last Name, followed by a all patients who comma and a space, followed by the have a last name patient's first name. (e.g. Smith, John) beginning with Sort order: Patient Last Name the letter P ascending select Lastname FirstName as PatientName from Patients where lastname liike PX order by lastname, 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 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 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 Total catheters (single value) 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. 27. Display the total quantity of catheters added to inventory during 2013 28. Display the total cost of "sterile gloves - small" provided by Poole's Medical supplies during 2013. 29. Display the average cost of supplies for each supply item broken out by supplier. Total cost (single value) Supply, Supplier, Average cost per supply item Sort order: Supply -- ascending Supplier - ascending Eselect from medicalsuppliers 100% Fax Results Messages Suppler Supplier Name Address_Line1 1 Virginia Medical Supplies 2198 Old Jetty 2 Lynchburg Medical Supplies 999 Dusty Promenade 3 Pooles Medical Supplies 5894 Heather Run Address Line2 ZpCode Phone NULL 24502 NULL NULL 24503 NULL NULL 24701 NULL Email NULL NULL NULL NULL NULL NULL WN select from supplyinventory Ranks s Types Compa 25 Special ervices ventory ils 4 100 % Results Messages SupplyID Supplier 2 2 2 2 2 Unit Cost 2 5 9 7 10 Quantity 8 15 14 4 13 Date Received 10/4/2013 10/22/2013 10/30/2013 11/17/2013 11/23/2013 12/7/2013 12/15/2013 12/22/2013 19/2014 1/22/2014 221/2014 1 1 1 3 16 4 DO 10 11 10 T6 4 9 El select from employee 100 Results Message Emplo Frame Madera Usa K 2. 2 Jenny T 3 2 Sharon 4 Shen X 5 Arthur T 6 Louis NULL 7 Annie 8 3 Cheryl E 9 9 Craig 10 10 Virginia 11 11 Ruby F Last Name Address 1 New 2 ide Hire CHIP Em Adams 2630 Tung Lookout NULL 2502 4434/2120243418-750 Una Adan.com Alexander 5264 Old Blom Cloung NULL 2500 34482-204342426755 derne.com Hien 541 Moly Wynd A4 78228 1434 989 2467 (434) 2996163 Shawn Mene.com Boet 4462 Plant Ek Oo NULL 711223 44:371-5121 4684-123 Shell Beerfesh.com Bailey 6136 Golden Fox Mount A 77 24503 (434) 9354647 Nhac.com Baker 6783 Velvet River Estates NULL 24151 4347652 4341350682 Barco Bames 3545 Green Town NULL 24502 (434 2288556 430116 Raneene.com Bente 8072 Broad Hollow NULL 24561 1540 759-175 540,353-4107 Chevy Boom Campbell 5058 Harvest Moneer met NULL 24550 1540) 47.1157 (5.0 125 3665 ng Cable.com Chen 3566 Blue Roe Pie 24551 NULL 1434 52727234348676277 Vis Chenih.com 5873 Siver Mann NOLL 24562 1434686-4601044365-7736 Ruby whic.com Rakib Huy Wage 13 05 5 21 NULL 25 NULL 11 10 365 1 20 NULL 11 19 26 NULL 10 365 NULL NULL 20000 19000 NULL NIL NULL 15000 NULL 23000 NULL Como select from tmpEmployeeRanks ks apa ary 100 % TitleID 1 1 33 Results Messages RankID EmpTypeID 10 1 1 2 1 3 3 1 1 2 2 3 4 5 5 5 Skill Leveld Hourly Rate Salary 2 20 NULL 2 20.5 NULL 3 25 NULL 3 26 NULL 27 NULU 3 28.5 NULL 30 NULL 33 NULLI 20 NULL 86.5 NULUI 139 NULU 6 8 8 9 10 10 11 11 select from employeeTitles acts ayee yeeRanks yees yeeTypes nceCompa ts Eians cianSpecial Services Inventory etails 100% m Resuits Messages Employee TitleID Employee Title LPN-1 3 LPN-3 LPN-4 LPN-5 RN-1 RN 2 8 RN3 8 9 RN-4 9 10 RN-5 10 11 RN-G 11 12 RN-7 select fron Employeeskilllevels 100 % 3 Results Messages Skill Levell Skill Level 1 1 Level 1 2 Level 2 3 13 Level 3 Eselect from tmpEmployeeTypes es 100% Results Messages Employee TypeID Employee Type 1 1 Nurse 2 2 Aide 3 3 Owner 4 4 Manager 5 5 Administration 5 Step 1 Table Creation Create a new database and execute the code below in SQL Server's query window to create the database tables. CREATE TABLE PhysicianSpecialties. (Specialtxir integer, Specialty Name varchar(50). CONSTRAINT PK PhysicianSpecialties PRIMARY KEY (SpecialtyID)) go CREATE TABLE ZipCodes (Zipcode varchar(10), City varchar(5e), State varchar(2) CONSTRAINT EX.ZinCodes PRIMARY KEY (ZipCode) go CREATE TABLE PhysicianPractices. PracticeID Integer PracticeName varchar 50 Address Linen varchar.se Address Line2 varchar5e Zipcode varchar le Phone Nandhan 14 Fax Manchart. 14 WebsiteURL Vardhan 50 CONSTRAINT PK Physicianecacsices PRIMARY KEY Practiceto. CONSTRAINT KIPhysicianPractices ipcodes FOREIGN KEY ZipCode REFERENCES Zincades go CREATE TABLE Physicians (Physician IR integer, First Name varchar(40), LastName, varchar(5e), PracticeIn integer, SpecialtyID integer, Email varchar(5e), CONSTRAINT PK Physicians. PRIMARY KEY (Physician IR), CONSTRAINT EK..Physicians.Pcactices. FOREIGN KEY (ecasticeIR) REFERENCES PhysicianPractices, CONSTRAINT EK.Physicians.Physicianspecialities. FOREIGN KEY (Special VID) REFERENCES PhysicianSpecialties go CREATE TABLE Patients (Patient IR integer, First Name varchar(50) Middle oitial varchar(1), Last Name varchar(50) Address Line1 Warchar(50) Address Line2 varchar(5e ZinCode varchar(10) ebone. Home Vacchar(14) Phone Alternate vacchar(14) Email varchar(50 CONSTRAINT PK.Patients PRIMARY KEY (PatientID) go CREATE TABLE Referrals Referralin Integer StartDate smalldatetime, EndDate, smalldatetime, Patientin integer, Physicians integer, CONSTRAINT PK Refecrals PRIMARY KEY (Refeccalin), CONSTRAINT EK Refeccals Patients FOREIGN KEY (PatientID REFERENCES Patients, CONSTRAINT EK Referrals Physicians. FOREIGN KEY (PhysicianIN) REFERENCES Physicians) go CREATE TABLE Services (ServiceIn integer, ServiceName varchar(50), CONSTRAINT PK ServiceIR PRIMARY KEY (ServiceID)) go CREATE TABLE Frequencies (Frequency IR integer Frequency varchari 30). CONSTRAINT PKEceguencies. PRIMARY KEY (ErequencyID) 89 CREATE TABLE Refercal Services (Refer a integer, Service in integer Frequency.In integer, CONSTRAINT PK. RefecralServices PRIMARY KEY (Rsfercali. Scricci), CONSTRAINT EK Referralservices Referrals FOREIGN KEY (ReferralIR REFERENCES Referrals, CONSTRAINT EK RefeccalServices Services. FOREIGN KEY (SecviceIR) REFERENCES Services, CONSTRAINT EK Referralservices. Frequencies. FOREIGN KEY (EcequencYIR) REFERENCES Frequencies) go CREATE TABLE Payment Types. (PaymentTypeID integer, Payment Lyre varchar(25), CONSTRAINT PK. Payment IRS PRIMARY KEY (PaymentTypeID)) 89 CREATE TABLE Insurance Companies Insurance ID integer, InsurancesomraOY varchar(50) Address Linel varchar(50 Address Line2 varchar 50 Zipcode varchar(10) Phone varchar 15 Fax varchar 15 Email varcharise CONSTRAINT PK Inaucance Companies. PRIMARY KEY Insurance ID CONSTRAINT EK InScanceCompaniesz pedes FOREIGN KEY Zipcode REFERENCES Zipcodes go CREATE TABLE Contracts (Cantcastill integer, Referred integer, StartDate salidateti. EndDate smalldatene. Payment TYREIR integer, Insurance in integer, Nexotiatedrats float, CONSTRAINT PK..Contrasts. PRIMARY KEY (Contrastin), CONSTRAINT EK..Contracts Referrals. FOREIGN KEY (ReferralIR) REFERENCES Referrals, CONSTRAINT EK.Contcasts. Payment Types FOREIGN KEY (PaymentTYNIR) REFERENCES Paysot Tyres CONSTRAINT EK Contracts Insurance Companies FOREIGN KEY Insurance ID REFERENCES Insurance Companies CREATE TABLE EmployeeTynes (Euplovec IDCIR integer identity, Forlovelyne varchar(25), CONSTRAINY EK EnglexecJvES. PRIMARY KEY (Employee TypeID)) 80 CREATE TABLE Employee titles Employee Titleid integer, Emplovectitle varchar(30), CONSTRAINT DK. Canlovestitles PRIMARY KEY (EmployeeTitle) go CREATE TABLE Employeeskilllexsds. (skill.evelID integer, Skillevel varchar(15), CONSTRAINT PKLEmployeeskillevels. PRIMARY KEY (Skillles >> go CREATE TABLE BingRates (Employee LYRIR integer SkalevelID integer Bitling rate float CONSTRAINT PkLerinary.Kex PRIMARY KEY (EmployeeTypeID. Skilleven CONSTRAINT ERIBILIngRates Employees FOREIGN KEY EmployeeTypeID REFERENCES EmployeeTypes.. CONSTRAINT EK BLRTest Employeesklieves FOREIGN KEY SKI REFERENCES Employeeskillevels go CREATE TABLE EnloveeBanks (Bank in integer, Emelose Iyer integer, Titl integer, Skillised integer, Huolyate float, Salary float, CONSTRAINT PK. EnployeeRanks. PRIMARY KEY (RankIR), CONSTRAINT EK EmployeeRanks.Forlovelypes. FOREIGN KEY (Employee Type IR REFERENCES Employees. CONSTRAINT EK. EnplexecRanks forloveeritles. FOREIGN KEY (LitlIRREFERENCES Ear loxsetitles.. CONSTRAINT EK. EmployecRanks. Employeeskill levels. FOREIGN KEY (SkilllexelIR) REFERENCES Employeeskilllexels) 80 CREATE TABLE Employees (EmployeeIR integer, FirstName varchart.30) Middle natial varchar (1) Last Name varchar 50 Address Linel varchar(50) Address Line2 varchar(50) Zipcode varchar10) Phonel varchar(14) Cell Phone varchar14 Email varcharse Rankn integer Hourlyhage float Salary float CONSTRAINT PK Employees. PRIMARY KEY EmployeeID CONSTRAINT EKLEMRlexees EmployeeBanks. FOREIGN KEY BankID REFERENCES EmployeeRaoks. CONSTRAINT EK Employee zipcodes FOREIGN KEY ZapCode REFERENCES ZinCodes go CREATE TABLE Shifts (Shiftin integer, Shift Nang varchar(20), Start Time time, Endine tine, CONSTRAINT PK.Shifts PRIMARY KEY (Shift) go CREATE TABLE Daysofbeek (Raxo Week, integer, Dk yacchac(15), CONSTRAINT PK.DaysOfWeek PRIMARY KEY (Dave WeekID)) go CREATE TABLE Availability (EmployeeIR integer, Weckor sedatsine DayofweckIR integer Shift integer CONSTRAINT PK. Availability PRIMARY KEY (Employee ID. Weskot.. Rayofweskin. Shift IR). CONSTRAINT EK. Availability Employees FOREIGN KEY (Employee IDJ REFERENCES Employees, CONSTRAINT FK. Availability DaysofWeek FOREIGN KEY (DayofvleekID REFERENCES Daysofweck, CONSTRAINT EK. Availability, shifts FOREIGN KEY ShittiR REFERENCES Shifts) BO CREATE TABLE Medical Sulics (Supplier integer, Suppliecare varchar(5e), Address Linel varchar(50) Address Line2 varchar(5e), ZipCode varchar(10), Phone varchar(14), Fax varchar(14) Email varchar(50), CONSTRAINT PK. Medical Suppliers PRIMARY KEY (SupplierID), CONSTRAINT EK Medical Supplies Zircodes. FOREIGN KEY (ZipCode) REFERENCES ZipCodes) go CREATE TABLE Supplies (Supply IR integer, SupplyRessciption vacchar(40), Share it float, CONSTRAINT PK Supplies PRIMARY KEY (SupRIYIR)) go CREATE TABLE Supply Loventory SupplyID integer SuppliecIR integer DateReceived smalldatetime Unit cost float Quantity Float CONSTRAINT PKL Supply foventory, PRIMARY KEY (SURRIYID SuprlisciR Date eseived), CONSTRAINT EK Supplyinventory Supplies FOREIGN KEY SURRIAR REFERENCES Supplies CONSTRAINT EKISU vinventory Suppliers. FOREIGN KEY SuppliecID REFERENCES Medical Supplies go Foc CREATE TABLE Visits (VisitID integer, DateRendered smalldatetime, Start Time time, EndTime time, Employee ID integer, Patient ID integer, CONSTRAINT PK Visits PRIMARY KEY (VisitID), CONSTRAINT EK Visits Employees. FOREIGN KEY (Employee ID) REFERENCES Employees, CONSTRAINT FK Visits. Patients FOREIGN KEY (Patient ID REFERENCES Patients) go CREATE TABLE VisitDetails (Visiti integer VisitDetailID integer, SupplyID integer SupplyQuantity integer Service ID integer Charge float CONSTRAINT PK VisitDetails PRIMARY KEY VisitID VisitDetailID CONSTRAINT EK VisitDetaiils Supplies FOREIGN KEY SupplyID REFERENCES Supplies, CONSTRAINT EK VisitDetails Services FOREIGN KEY ServiceID REFERENCES Services) BO Create a new database and execute the code below in SQL Server's query Window to create the database tables. CREATE TABLE PhysicianSpecialties, (Specialtyin integer, SpecialtyName varchar(50), CONSTRAINT PK. Physicianspecialties PRIMARY KEY (SpecialtxIR)) go CREATE TABLE Zircades. (Zipcode varchar(10), City varchar(52), State varchar(2), CONSTRAINT PK.ZipCodes PRIMARY KEY (Zipcode> go CREATE TABLE PhysicianPractices (Practiceir integer, PracticeName varchar(50), Address Line1 varchar(5e) Address Line2 varchar(50) Zipcode varchan 10 Phone varchar(14) Fax yarchar 14 WebsiteURL varchar 50 CONSTRAINT PK PhysicianPractices PRIMARY KEY PracticeID CONSTRAINT EKIPhysicianfractices. Zipcodes. FOREIGN KEY zircodeREFERENCES Zincades.) go CREATE TABLE Physicians (Physiciant integer, FirstName varchar(40). Last Name varchar(50), PracticeIn integer, Specialty integer, Email varchar(50), CONSTRAINT PK. Physicians. PRIMARY KEY (PhysicianID), CONSTRAINT EK.Physicians..Practices. FOREIGN KEY (PracticeIR) REFERENCES PhysicianPractices. CONSTRAINT EK..Physicians PhysicianSpecialities. FOREIGN KEY (SpecialtVTR) REFERENCES Physicianspecialties) go CREATE TABLE Patients (Patient IR integer, FirstName varchar(se) Middleloitial varchar(1) LastName varchar(50) Address Line1 varchar(5e) Address Line2 varchar(5e ZipCode varchar(19 Phone Home varchar (14) Phone Alternate varchar 14), Email Vardhan 50 CONSTRAINT PK. Patients PRIMARY KEY (Patient ID)) go CREATE TABLE Referrals ReferralID Integer StartDate Ildateti EndDate, sallate Patient. integer, Physician in integer, CONSTRAINT PK Referrals. PRIMARY KEY (Bsfeccalin), CONSTRAINT EUReferrals. Patients. FOREIGN KEY (Patient ID) REFERENCES Patients, CONSTRAINT EK Referrals.Physicians. FOREIGN KEY (PhysicianIR) REFERENCES Physicians) go GREATE TABLE Services (Service in integer, SecriseName, varchar(50), CONSTRAINT PKSecxiCRIR. PRIMARY KEY (SecviseID)) go CREATE TABLE Frequencies Eceauss ID integer Frequency varchar(30) CONSTRAINT PKErequencies PRIMARY KEY (Eceaucosx.ID)) CREATE TABLE Referralsecxisss. (ReferralID integer Servicio integer, Frequens ID Integer CONSTRAINT KL Referralservices PRIMARY KEY (ReferralID ServiceIR CONSTRAINT EK Referralservices Referrals FOREIGN KEY (ReferralIR) REFERENCES Referrals CONSTRAINTIEK Refera services Services FOREIGN KEY ServissIR REFERENCES Services CONSTRAINT FK Refera Services Frequens SSFOREIGN KEY Frequens ID REFERENCES Frequencies) CREATE TABLE Payment Types (Payment integer, PayusatType, varchar(25), CONSTRAINT PK. Payment IYRES PRIMARY KEY (Payment IXSID)) go CREATE TABLE Insurancebomrapies. (Insurance, integer, Insurance many varchar(50), Address Linei varchar(50), Address Linez varchar(50), Zipcode varchar(10), Phone yacchar(15), Fax varchar(15), Email varchar(50) CONSTRAINT PK Insurance Companies PRIMARY KEY (Insuransen), CONSTRAINT EDUCAOCSCompanies Zircodes. FOREIGN KEY Czincode) REFERENCES Zisodes) 30 CREATE TABLE Contracts Contras ID integer RO StartDate small datetime EndDate small datetime Payment Tyne integer Insurance integen Negotiatedate float CONSTRAINIP Contacts PRIMARY KEY contrasti CONSTRAINT Pkl contracts Referrals FOREIGN KEY IRHEIR HEMERENCES Referals CONSTRAINT E contrasts Payment Types FOREIGN KEY PAYRIR REFERENCES Pays EYES CONSTRAINT EK..contrasts Iosucancecompanies. FOREIGN KEY (InsuranceID) REFERENCES Losurance companies) CREATE TABLE Employee Iynes (Employee TyreIn integer identity, Employee Tyre varchar(25), CONSTRAINT PK. Employee Tyres. PRIMARY KEY (Employee TypeID)) go CREATE TABLE EmployeeTitles (EmployeeTitleID integer, Employee Title varchar(30) CONSTRAINT PK. Employee Titles. PRIMARY KEY (EmployeeTitleID 80 CREATE TABLE Employeeskilllexels Skill Level Integer Skillevel Marcharts) CONSTRAINT PLENloveeskillevels PRIMARY KEY (Skillevel) go CREATE TABLE Billing Rates. (EmployeeTypeID integer, Skillleve ID integer, BillingRate float, CONSTRAINT PK PrimaryKey PRIMARY KEY (EmployeeTypeID. SkilllevelID) CONSTRAINT FK BillingRates EmployeeTypes FOREIGN KEY (Employee TypeID REFERENCES Employee Types. CONSTRAINT EK BillingRates Employeeskilllevels FOREIGN KEY (SkilllevelID) REFERENCES Employeeskill levels go CREATE TABLE EmployeeRanks. (RankID integer, Employee Type ID integer, Title ID integer, Skilllevel ID integer, Houyate float, Salary float, CONSTRAINT PK Employee Ranks PRIMARY KEY (RankID CONSTRAINT EK EmployeeRanks. Employee Types FOREIGN KEY (EmployeeTypeID REFERENCES Employee Types CONSTRAINT EK Employee Ranks. Employee Titles FOREIGN KEY (TitleID REFERENCES Employee Titles. CONSTRAINT FK Employee Ranks Employeeskilllevels FOREIGN KEY Skill Level ID REFERENCES Employeeskilllevels) go CREATE TABLE Employees (Employee ID integer, FirstName varchar(30), MiddleInitial varchar(1), LastName varchar(5e), Address_Line1 varchar(50), Address_Line2 varchar(50), ZipCode varchar(10), Phone varchar(14), Cell Phone varchar(14), Email varchar(50), RankID integer, Hourlyware Float, Salary Float, CONSTRAINT PK Employees. PRIMARY KEY (EmployeeID), CONSTRAINT EK Employees. EmployeeRanks FOREIGN KEY (RankID REFERENCES EmployeeRanks CONSTRAINT FK Employee ZipCodes FOREIGN KEY (ZipCode) REFERENCES ZipCodes) go CREATE TABLE Shifts (Shift ID integer, Shift Name varchar(20), StartTime time, EndTime time, CONSTRAINT PK Shifts PRIMARY KEY (ShiftID)) go CREATE TABLE DaysOfWeek (DayOfWeek ID integer, Daxikleik varchar(15), CONSTRAINT PK DaysOfWeek PRIMARY KEY (DayOfWeek ID)) go CREATE TABLE Availability Employee ID integer, Weekof small datetime. DayOfWeekID integer ShiftID integen CONSTRAINT PK Availability PRIMARY KEY (Employee ID Weekof. DayOfWeekID. ShiftID), CONSTRAINT FKI Availability Employees FOREIGN KEY Employee ID REFERENCES Employees, CONSTRAINT FLAvailability DaysOfWeek FOREIGN KEY DayOfWeekID REFERENCES DaysOfWeek CONSTRAINT FK Availability. Shifts FOREIGN KEY ShiftID REFERENCES Shifts go CREATE TABLE Medical Suppliers (Supplierin integer, SuppliecNanse varchar(5e), Address Linel varchar(5e), AddressLine2 varchar(5e), Zircode varchar(10), Phone varchar(14), Fax varchar(14), Email varchar(5e), CONSTRAINT PK Medical Suppliers PRIMARY KEY (Suppliecin), CONSTRAINT EK Medical Suppliers Zarcodes. FOREIGN KEY (Zincode) REFERENCES Zincades.) 80 CREATE TABLE Supplies (Supply integer, SupplyResscirtien varchar(40), Shareeerlinit float, CONSTRAINT PK Supplies PRIMARY KEY (SupplyIR>> 80 CREATE TABLE Supply Inventory SuRRIVIR integen SURRKLECID integer DateReceived adhdattias Unitcast float Quantity float: CONSTRAINT PK SuraxInventory PRIMARY KEY Supply Suppliecin. Rats Received CONSTRAINT EK SupplyInventory Supplies FOREIGN KEY SupplyID REFERENCES Supplies CONSTRAINT EK Suplynxentory Suppliers FOREIGN KEY SuppliCIR REFERENCES Medical Suppliers 20 CREATE TABLE Visits (VisitID integer, DateRendered smalldatetime, Start Time time, EndTime time, Employee ID integer, Patient ID integer, CONSTRAINT PK Visits PRIMARY KEY (Visit ID) CONSTRAINT FK Visits Employees FOREIGN KEY (EmployeeID) REFERENCES Employees, CONSTRAINT FK Visits Patients FOREIGN KEY (Patient ID REFERENCES Patients) go CREATE TABLE VisitDetails VisitID integer, VisitDetailID integer SupplyID integer SupplyQuantity integer Service ID integer, Charge float CONSTRAINT PKI VisitDetails PRIMARY KEY VisitID VisitDetailID CONSTRAINT KIVisitDetaliis Supplies FOREIGN KEY SupplyID REFERENCES Supplies, CONSTRAINT KIVisitDetails Services FOREIGN KEY ServiceID REFERENCES Services) go Create a new database and execute the code below in SQL Server's query window to create the database tables, CREATE TABLE Physician Specialties (Specialty.In integer, SpesialtyName varchar(5e), CONSTRAINT PK Physicianspecialties. PRIMARY KEY (SpecialtxIR)) go CREATE TABLE ZipCodes, (Zipcode varchar(10), City varchar(5e), State varchar(2), CONSTRAINT PK.Zipcodes PRIMARY KEY (ZipCode) go CREATE TABLE Physician Practices (Practice in integer, PracticeName varchar(50) Address Linen varchar(5e Address Line2 varchar(50) Zipcode varchar 10 Phone varchar/14), Fax Vanchan 143 WebsiteURL varchar(se) CONSTRAINT PK PhysicianPractices PRIMARY KEY PracticeID CONSTRAINT EK. PhysicianPractices zipcodes. FOREIGN KEY Zipcode REFERENCES zipcodes.) go CREATE TABLE Physicians (PhysicianIR integer, First Nane varchar(40), Last Name, varchar(5e). PracticeIn integer, Specialty ID integer, Email varchar(5e), CONSTRAINT PK Physicians. PRIMARY KEY (PhysicianIR), CONSTRAINT EK Physicians..Ccactices. FOREIGN KEY (ecasticeID) REFERENCES. PhysicianPractices. CONSTRAINT EK..Physicians. PhysicianSpecialities. FOREIGN KEY (SpecialtyID) REFERENCES PhysicianSpecialties) go CREATE TABLE Patients (Patient IR integer, FirstName varchar(50) Middle Initial varchar(1), Last Name, varchar(5e), 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 (Patient ID go CREATE TABLE Referrals (RefecclIR Integer StartDate small datetiine, EndDate salidatetime, Patienten integer, Physician IR integer, CONSTRAINT PK. Referrals PRIMARY KEY (Refeccalin). CONSTRAINT EK. Referrals. Patients FOREIGN KEY (Patient ID) REFERENCES Patients, CONSTRAINT EK Referrals Physicians FOREIGN KEY (Physician IR) REFERENCES Physicians) go CREATE TABLE Services (ServiceID integer, ServiceName varchar(50), CONSTRAINT PKServiceIN PRIMARY KEY (ServiceID)) go CREATE TABLE Frequencies (Erequens. Integer Frequency varchar(30) CONSTRAINT PKL Frequencies. PRIMARY KEY (Erequency.IR go CREATE TABLE ReferralServices (Referralin integer, Service ID integer, Frequency ID integer CONSTRAINT PK RefeccelServices PRIMARY KEY ReferralI. ServiceID CONSTRAINT EK Referralservices Referrals FOREIGN KEY (ReferralID REFERENCES Referrals CONSTRAINT EK Referralservices Services FOREIGN KEY ServiceID REFERENCES Services CONSTRAINT EK Referralservices Frequencies. FOREIGN KEY CE.requency ID REFERENCES Frequencies) go CREATE TABLE PaymentTypes. (Payment TypeID integer, Payment Type varchar(25), CONSTRAINT PK Payment Types. PRIMARY KEY (PaymentTypeID)) go CREATE TABLE InsuranceCompanies (Insurance ID integer, Insurance Company, varchar(50), Address_Line1 varchar(50), Address_line2 varchar(50), ZipCode varchar(10), Phone varchar15), Fax varchar(15). Email varchar(5e), CONSTRAINT PK Insurance Companies. PRIMARY KEY (InsuranceID CONSTRAINT EK Insurance Companies.ZipCodes FOREIGN KEY (ZipCode REFERENCES Zipcodes.) go CREATE TABLE Contracts ContractID integer efect integer StartDate smalldatetime EndDate smalldatetime Payment Type IR Integer Insurance ID integer Negotiatedrats float, CONSTRAINT PKL.cont.casts. PRIMARY KEY (Contractin), CONSTRAINT PK.contcast Referrada, FOREIGN KEY (Referral) REFERENCES Referrals, CONSTRAINT EK Contcasts Paymentines FOREIGN KEY (PaymentTypeID) REFERENCES Payusates, KCONSTRAINT EK..Cont.casts Insurance Conradies FOREIGN KEY (InsuranceIR) REFERENCES Lasurancescananies.) BO CREATE TABLE Employee Tyres EmployeeTypeID integer identity Eorleyeeltype varchar25) CONSTRAINT PL.EmployssTypes PRIMARY KEY Employee REAR go CREATE TABLE EmployeeSkilllevels (SkilllevelID integer, Skilllevel varchar(15), CONSTRAINT PK Employeeskill levels PRIMARY KEY (Skilllevel ID)) Go CREATE TABLE BillingRates (Employee TYRSID integer, Skillevel ID integer, BillingRate float, CONSTRAINT PK PrimaryKey PRIMARY KEY (EmployeeTypeID, SkilllevelID). CONSTRAINT EK BillingRates EmployeeTypes. FOREIGN KEY (Employee TypeID) REFERENCES Employee Types CONSTRAINT EK. BillingRates Employeeskilllevels. FOREIGN KEY (SkilllevelID REFERENCES Employeeskilllevels) go CREATE TABLE EmployeeRanks, (RankID integer, Employee type ID integer, Title ID integer, Skilll.exelik integer, HourlyRate float, Salary float, CONSTRAINT PK.E.n.loveeRanks. PRIMARY KEY (RankIR), CONSTRAINT EK. EmployeeRanks. Employee Iynes. FOREIGN KEY (Employee TypeID) REFERENCES Employee types. CONSTRAINT EK. En loveeRanks. EmployeeTitles. FOREIGN KEY (TitleID REFERENCES Employee titles, CONSTRAINT EK. EnnloveeRanks. Employeeskilllevels. FOREIGN KEY (SkilllevelIN) REFERENCES Employeeskilllexels) go CREATE TABLE Employees Emn Layee IR integer, FirstName varchar(30), MiddleInitial varchar(1), Last Name varchar(50) Address Line1 varchar(52), 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 (Employee ID CONSTRAINT EKLEmployees. Employee Ranks. FOREIGN KEY Rankin) REFERENCES EmployeeRanks CONSTRAINT ELLER Lovee.zipcodes. FOREIGN KEY ZinCode REFERENCES ZIRCodes RO n Focus CREATE TABLE Shifts (Shift in integer, Shift Nane, varchar(20), Startline time, EndTime, tine, CONSTRAINT PK Shifts. PRIMARY KEY (ShiftID)) go CREATE TABLE DaysofWeek (DayOfWeekin integer, Daxofbleek varchar(15), CONSTRAINT PK.RaxsQfWeek PRIMARY KEY (RayofWeek ID)) go CREATE TABLE Availability (EmployeeIR integer, Weekgf. smalldatetime, RayofWeekID integer Shiftis integer CONSTRAINT PK Availability PRIMARY KEY Employee ID. WeekQf. DayOfWeekID ShiftID). CONSTRAINT EK Availability Employees. FOREIGN KEY (EmployeeID) REFERENCES Employees. CONSTRAINT EK Availability Daysofweek FOREIGN KEY DayOfWeekID REFERENCES Raysofkeek, CONSTRAINT EK Availability. Shifts FOREIGN KEY ShiftID REFERENCES Shifts) go CREATE TABLE Medical Suppliers. (Supplier in integer, Supplier Nane varchar(50), Address Line1 varchar(50), Address Line2 varchar(50), Zincade varchar(10), Phone varchar(14), Fax varchar(14), Email varchar(50), CONSTRAINT PK MedicalSuppliecs. PRIMARY KEY (Suppliecin), CONSTRAINT EK_MedicalSuppliecs Zincodes. FOREIGN KEY (zipcode) REFERENCES Zincades.) go CREATE TABLE Supplies (Supply.IR integer, SupplyDescription varchar(40), Share pecunit float, CONSTRAINT PK Supplies PRIMARY KEY (SurlyID)) go CREATE TABLE Supply Inventory (SupplyID integer, SupplieciR Integer DateReceived smalidatetime Unit Cost float Quantity float CONSTRAINT PK SupplyInventory. PRIMARY KEY SURRIYID Suppliecin RateReceived). CONSTRAINT EK...SupplyInventory Supplies FOREIGN KEY SunRlYIR REFERENCES Supplies CONSTRAINT EK...Supplytaventory Suprllecs FOREIGN KEY SuppliecIR REFERENCES MedicalSuppliers.) CONSTRAINT EKSumn winxentory Surn Itecs. FOREIGN KEY (SunnerID) REFERENCES Medica Sunters 80 CREATE TABLE Visits (VisitID integer, DateRendered smalldatetime. Start Time time, EndTime, time, Employee ID integer Patient IQ. Integer CONSTRAINT PK Visits PRIMARY KEY Visit ID CONSTRAINT EK Visits. Employees FOREIGN KEY EmployeeIR REFERENCES Employees CONSTRAINT EK Visits Patients. FOREIGN KEY PatientID REFERENCES Patients 20 D Focus CREATE TABLE VisitRetails (Visitis integer, VisitDetailir integer, Sunnlydd integer, SupplyQuantity integer, ServiceIn integer, Charge float, CONSTRAINT PK VisitRetails. PRIMARY KEY (Visitid. VisitDetailin). CONSTRAINT EK VisitDetaiils Supplies. FOREIGN KEY (SupplyIR) REFERENCES Supplies, CONSTRAINT EK VisitDetails Services. FOREIGN KEY (ServiceID REFERENCES Services) go Write and execute queries to perform the following functions: Criteria Output Format Your Answer: Type your query in this column. The first one has been done for you. Screenshots should be pasted below this table. (Make sure the number of rows that are returned appears in the screenshot.) 1. Display a list of Patient Last Name, followed by a all patients who comma and a space, followed by the have a last name patient's first name. (e.g. Smith, John) beginning with Sort order: Patient Last Name the letter P ascending select Lastname FirstName as PatientName from Patients where lastname liike PX order by lastname, 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 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 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 Total catheters (single value) 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. 27. Display the total quantity of catheters added to inventory during 2013 28. Display the total cost of "sterile gloves - small" provided by Poole's Medical supplies during 2013. 29. Display the average cost of supplies for each supply item broken out by supplier. Total cost (single value) Supply, Supplier, Average cost per supply item Sort order: Supply -- ascending Supplier - ascending Eselect from medicalsuppliers 100% Fax Results Messages Suppler Supplier Name Address_Line1 1 Virginia Medical Supplies 2198 Old Jetty 2 Lynchburg Medical Supplies 999 Dusty Promenade 3 Pooles Medical Supplies 5894 Heather Run Address Line2 ZpCode Phone NULL 24502 NULL NULL 24503 NULL NULL 24701 NULL Email NULL NULL NULL NULL NULL NULL WN select from supplyinventory Ranks s Types Compa 25 Special ervices ventory ils 4 100 % Results Messages SupplyID Supplier 2 2 2 2 2 Unit Cost 2 5 9 7 10 Quantity 8 15 14 4 13 Date Received 10/4/2013 10/22/2013 10/30/2013 11/17/2013 11/23/2013 12/7/2013 12/15/2013 12/22/2013 19/2014 1/22/2014 221/2014 1 1 1 3 16 4 DO 10 11 10 T6 4 9 El select from employee 100 Results Message Emplo Frame Madera Usa K 2. 2 Jenny T 3 2 Sharon 4 Shen X 5 Arthur T 6 Louis NULL 7 Annie 8 3 Cheryl E 9 9 Craig 10 10 Virginia 11 11 Ruby F Last Name Address 1 New 2 ide Hire CHIP Em Adams 2630 Tung Lookout NULL 2502 4434/2120243418-750 Una Adan.com Alexander 5264 Old Blom Cloung NULL 2500 34482-204342426755 derne.com Hien 541 Moly Wynd A4 78228 1434 989 2467 (434) 2996163 Shawn Mene.com Boet 4462 Plant Ek Oo NULL 711223 44:371-5121 4684-123 Shell Beerfesh.com Bailey 6136 Golden Fox Mount A 77 24503 (434) 9354647 Nhac.com Baker 6783 Velvet River Estates NULL 24151 4347652 4341350682 Barco Bames 3545 Green Town NULL 24502 (434 2288556 430116 Raneene.com Bente 8072 Broad Hollow NULL 24561 1540 759-175 540,353-4107 Chevy Boom Campbell 5058 Harvest Moneer met NULL 24550 1540) 47.1157 (5.0 125 3665 ng Cable.com Chen 3566 Blue Roe Pie 24551 NULL 1434 52727234348676277 Vis Chenih.com 5873 Siver Mann NOLL 24562 1434686-4601044365-7736 Ruby whic.com Rakib Huy Wage 13 05 5 21 NULL 25 NULL 11 10 365 1 20 NULL 11 19 26 NULL 10 365 NULL NULL 20000 19000 NULL NIL NULL 15000 NULL 23000 NULL Como select from tmpEmployeeRanks ks apa ary 100 % TitleID 1 1 33 Results Messages RankID EmpTypeID 10 1 1 2 1 3 3 1 1 2 2 3 4 5 5 5 Skill Leveld Hourly Rate Salary 2 20 NULL 2 20.5 NULL 3 25 NULL 3 26 NULL 27 NULU 3 28.5 NULL 30 NULL 33 NULLI 20 NULL 86.5 NULUI 139 NULU 6 8 8 9 10 10 11 11 select from employeeTitles acts ayee yeeRanks yees yeeTypes nceCompa ts Eians cianSpecial Services Inventory etails 100% m Resuits Messages Employee TitleID Employee Title LPN-1 3 LPN-3 LPN-4 LPN-5 RN-1 RN 2 8 RN3 8 9 RN-4 9 10 RN-5 10 11 RN-G 11 12 RN-7 select fron Employeeskilllevels 100 % 3 Results Messages Skill Levell Skill Level 1 1 Level 1 2 Level 2 3 13 Level 3 Eselect from tmpEmployeeTypes es 100% Results Messages Employee TypeID Employee Type 1 1 Nurse 2 2 Aide 3 3 Owner 4 4 Manager 5 5 Administration 5 Step 1 Table Creation Create a new database and execute the code below in SQL Server's query window to create the database tables. CREATE TABLE PhysicianSpecialties. (Specialtxir integer, Specialty Name varchar(50). CONSTRAINT PK PhysicianSpecialties PRIMARY KEY (SpecialtyID)) go CREATE TABLE ZipCodes (Zipcode varchar(10), City varchar(5e), State varchar(2) CONSTRAINT EX.ZinCodes PRIMARY KEY (ZipCode) go CREATE TABLE PhysicianPractices. PracticeID Integer PracticeName varchar 50 Address Linen varchar.se Address Line2 varchar5e Zipcode varchar le Phone Nandhan 14 Fax Manchart. 14 WebsiteURL Vardhan 50 CONSTRAINT PK Physicianecacsices PRIMARY KEY Practiceto. CONSTRAINT KIPhysicianPractices ipcodes FOREIGN KEY ZipCode REFERENCES Zincades go CREATE TABLE Physicians (Physician IR integer, First Name varchar(40), LastName, varchar(5e), PracticeIn integer, SpecialtyID integer, Email varchar(5e), CONSTRAINT PK Physicians. PRIMARY KEY (Physician IR), CONSTRAINT EK..Physicians.Pcactices. FOREIGN KEY (ecasticeIR) REFERENCES PhysicianPractices, CONSTRAINT EK.Physicians.Physicianspecialities. FOREIGN KEY (Special VID) REFERENCES PhysicianSpecialties go CREATE TABLE Patients (Patient IR integer, First Name varchar(50) Middle oitial varchar(1), Last Name varchar(50) Address Line1 Warchar(50) Address Line2 varchar(5e ZinCode varchar(10) ebone. Home Vacchar(14) Phone Alternate vacchar(14) Email varchar(50 CONSTRAINT PK.Patients PRIMARY KEY (PatientID) go CREATE TABLE Referrals Referralin Integer StartDate smalldatetime, EndDate, smalldatetime, Patientin integer, Physicians integer, CONSTRAINT PK Refecrals PRIMARY KEY (Refeccalin), CONSTRAINT EK Refeccals Patients FOREIGN KEY (PatientID REFERENCES Patients, CONSTRAINT EK Referrals Physicians. FOREIGN KEY (PhysicianIN) REFERENCES Physicians) go CREATE TABLE Services (ServiceIn integer, ServiceName varchar(50), CONSTRAINT PK ServiceIR PRIMARY KEY (ServiceID)) go CREATE TABLE Frequencies (Frequency IR integer Frequency varchari 30). CONSTRAINT PKEceguencies. PRIMARY KEY (ErequencyID) 89 CREATE TABLE Refercal Services (Refer a integer, Service in integer Frequency.In integer, CONSTRAINT PK. RefecralServices PRIMARY KEY (Rsfercali. Scricci), CONSTRAINT EK Referralservices Referrals FOREIGN KEY (ReferralIR REFERENCES Referrals, CONSTRAINT EK RefeccalServices Services. FOREIGN KEY (SecviceIR) REFERENCES Services, CONSTRAINT EK Referralservices. Frequencies. FOREIGN KEY (EcequencYIR) REFERENCES Frequencies) go CREATE TABLE Payment Types. (PaymentTypeID integer, Payment Lyre varchar(25), CONSTRAINT PK. Payment IRS PRIMARY KEY (PaymentTypeID)) 89 CREATE TABLE Insurance Companies Insurance ID integer, InsurancesomraOY varchar(50) Address Linel varchar(50 Address Line2 varchar 50 Zipcode varchar(10) Phone varchar 15 Fax varchar 15 Email varcharise CONSTRAINT PK Inaucance Companies. PRIMARY KEY Insurance ID CONSTRAINT EK InScanceCompaniesz pedes FOREIGN KEY Zipcode REFERENCES Zipcodes go CREATE TABLE Contracts (Cantcastill integer, Referred integer, StartDate salidateti. EndDate smalldatene. Payment TYREIR integer, Insurance in integer, Nexotiatedrats float, CONSTRAINT PK..Contrasts. PRIMARY KEY (Contrastin), CONSTRAINT EK..Contracts Referrals. FOREIGN KEY (ReferralIR) REFERENCES Referrals, CONSTRAINT EK.Contcasts. Payment Types FOREIGN KEY (PaymentTYNIR) REFERENCES Paysot Tyres CONSTRAINT EK Contracts Insurance Companies FOREIGN KEY Insurance ID REFERENCES Insurance Companies CREATE TABLE EmployeeTynes (Euplovec IDCIR integer identity, Forlovelyne varchar(25), CONSTRAINY EK EnglexecJvES. PRIMARY KEY (Employee TypeID)) 80 CREATE TABLE Employee titles Employee Titleid integer, Emplovectitle varchar(30), CONSTRAINT DK. Canlovestitles PRIMARY KEY (EmployeeTitle) go CREATE TABLE Employeeskilllexsds. (skill.evelID integer, Skillevel varchar(15), CONSTRAINT PKLEmployeeskillevels. PRIMARY KEY (Skillles >> go CREATE TABLE BingRates (Employee LYRIR integer SkalevelID integer Bitling rate float CONSTRAINT PkLerinary.Kex PRIMARY KEY (EmployeeTypeID. Skilleven CONSTRAINT ERIBILIngRates Employees FOREIGN KEY EmployeeTypeID REFERENCES EmployeeTypes.. CONSTRAINT EK BLRTest Employeesklieves FOREIGN KEY SKI REFERENCES Employeeskillevels go CREATE TABLE EnloveeBanks (Bank in integer, Emelose Iyer integer, Titl integer, Skillised integer, Huolyate float, Salary float, CONSTRAINT PK. EnployeeRanks. PRIMARY KEY (RankIR), CONSTRAINT EK EmployeeRanks.Forlovelypes. FOREIGN KEY (Employee Type IR REFERENCES Employees. CONSTRAINT EK. EnplexecRanks forloveeritles. FOREIGN KEY (LitlIRREFERENCES Ear loxsetitles.. CONSTRAINT EK. EmployecRanks. Employeeskill levels. FOREIGN KEY (SkilllexelIR) REFERENCES Employeeskilllexels) 80 CREATE TABLE Employees (EmployeeIR integer, FirstName varchart.30) Middle natial varchar (1) Last Name varchar 50 Address Linel varchar(50) Address Line2 varchar(50) Zipcode varchar10) Phonel varchar(14) Cell Phone varchar14 Email varcharse Rankn integer Hourlyhage float Salary float CONSTRAINT PK Employees. PRIMARY KEY EmployeeID CONSTRAINT EKLEMRlexees EmployeeBanks. FOREIGN KEY BankID REFERENCES EmployeeRaoks. CONSTRAINT EK Employee zipcodes FOREIGN KEY ZapCode REFERENCES ZinCodes go CREATE TABLE Shifts (Shiftin integer, Shift Nang varchar(20), Start Time time, Endine tine, CONSTRAINT PK.Shifts PRIMARY KEY (Shift) go CREATE TABLE Daysofbeek (Raxo Week, integer, Dk yacchac(15), CONSTRAINT PK.DaysOfWeek PRIMARY KEY (Dave WeekID)) go CREATE TABLE Availability (EmployeeIR integer, Weckor sedatsine DayofweckIR integer Shift integer CONSTRAINT PK. Availability PRIMARY KEY (Employee ID. Weskot.. Rayofweskin. Shift IR). CONSTRAINT EK. Availability Employees FOREIGN KEY (Employee IDJ REFERENCES Employees, CONSTRAINT FK. Availability DaysofWeek FOREIGN KEY (DayofvleekID REFERENCES Daysofweck, CONSTRAINT EK. Availability, shifts FOREIGN KEY ShittiR REFERENCES Shifts) BO CREATE TABLE Medical Sulics (Supplier integer, Suppliecare varchar(5e), Address Linel varchar(50) Address Line2 varchar(5e), ZipCode varchar(10), Phone varchar(14), Fax varchar(14) Email varchar(50), CONSTRAINT PK. Medical Suppliers PRIMARY KEY (SupplierID), CONSTRAINT EK Medical Supplies Zircodes. FOREIGN KEY (ZipCode) REFERENCES ZipCodes) go CREATE TABLE Supplies (Supply IR integer, SupplyRessciption vacchar(40), Share it float, CONSTRAINT PK Supplies PRIMARY KEY (SupRIYIR)) go CREATE TABLE Supply Loventory SupplyID integer SuppliecIR integer DateReceived smalldatetime Unit cost float Quantity Float CONSTRAINT PKL Supply foventory, PRIMARY KEY (SURRIYID SuprlisciR Date eseived), CONSTRAINT EK Supplyinventory Supplies FOREIGN KEY SURRIAR REFERENCES Supplies CONSTRAINT EKISU vinventory Suppliers. FOREIGN KEY SuppliecID REFERENCES Medical Supplies go Foc CREATE TABLE Visits (VisitID integer, DateRendered smalldatetime, Start Time time, EndTime time, Employee ID integer, Patient ID integer, CONSTRAINT PK Visits PRIMARY KEY (VisitID), CONSTRAINT EK Visits Employees. FOREIGN KEY (Employee ID) REFERENCES Employees, CONSTRAINT FK Visits. Patients FOREIGN KEY (Patient ID REFERENCES Patients) go CREATE TABLE VisitDetails (Visiti integer VisitDetailID integer, SupplyID integer SupplyQuantity integer Service ID integer Charge float CONSTRAINT PK VisitDetails PRIMARY KEY VisitID VisitDetailID CONSTRAINT EK VisitDetaiils Supplies FOREIGN KEY SupplyID REFERENCES Supplies, CONSTRAINT EK VisitDetails Services FOREIGN KEY ServiceID REFERENCES Services) BO Create a new database and execute the code below in SQL Server's query Window to create the database tables. CREATE TABLE PhysicianSpecialties, (Specialtyin integer, SpecialtyName varchar(50), CONSTRAINT PK. Physicianspecialties PRIMARY KEY (SpecialtxIR)) go CREATE TABLE Zircades. (Zipcode varchar(10), City varchar(52), State varchar(2), CONSTRAINT PK.ZipCodes PRIMARY KEY (Zipcode> go CREATE TABLE PhysicianPractices (Practiceir integer, PracticeName varchar(50), Address Line1 varchar(5e) Address Line2 varchar(50) Zipcode varchan 10 Phone varchar(14) Fax yarchar 14 WebsiteURL varchar 50 CONSTRAINT PK PhysicianPractices PRIMARY KEY PracticeID CONSTRAINT EKIPhysicianfractices. Zipcodes. FOREIGN KEY zircodeREFERENCES Zincades.) go CREATE TABLE Physicians (Physiciant integer, FirstName varchar(40). Last Name varchar(50), PracticeIn integer, Specialty integer, Email varchar(50), CONSTRAINT PK. Physicians. PRIMARY KEY (PhysicianID), CONSTRAINT EK.Physicians..Practices. FOREIGN KEY (PracticeIR) REFERENCES PhysicianPractices. CONSTRAINT EK..Physicians PhysicianSpecialities. FOREIGN KEY (SpecialtVTR) REFERENCES Physicianspecialties) go CREATE TABLE Patients (Patient IR integer, FirstName varchar(se) Middleloitial varchar(1) LastName varchar(50) Address Line1 varchar(5e) Address Line2 varchar(5e ZipCode varchar(19 Phone Home varchar (14) Phone Alternate varchar 14), Email Vardhan 50 CONSTRAINT PK. Patients PRIMARY KEY (Patient ID)) go CREATE TABLE Referrals ReferralID Integer StartDate Ildateti EndDate, sallate Patient. integer, Physician in integer, CONSTRAINT PK Referrals. PRIMARY KEY (Bsfeccalin), CONSTRAINT EUReferrals. Patients. FOREIGN KEY (Patient ID) REFERENCES Patients, CONSTRAINT EK Referrals.Physicians. FOREIGN KEY (PhysicianIR) REFERENCES Physicians) go GREATE TABLE Services (Service in integer, SecriseName, varchar(50), CONSTRAINT PKSecxiCRIR. PRIMARY KEY (SecviseID)) go CREATE TABLE Frequencies Eceauss ID integer Frequency varchar(30) CONSTRAINT PKErequencies PRIMARY KEY (Eceaucosx.ID)) CREATE TABLE Referralsecxisss. (ReferralID integer Servicio integer, Frequens ID Integer CONSTRAINT KL Referralservices PRIMARY KEY (ReferralID ServiceIR CONSTRAINT EK Referralservices Referrals FOREIGN KEY (ReferralIR) REFERENCES Referrals CONSTRAINTIEK Refera services Services FOREIGN KEY ServissIR REFERENCES Services CONSTRAINT FK Refera Services Frequens SSFOREIGN KEY Frequens ID REFERENCES Frequencies) CREATE TABLE Payment Types (Payment integer, PayusatType, varchar(25), CONSTRAINT PK. Payment IYRES PRIMARY KEY (Payment IXSID)) go CREATE TABLE Insurancebomrapies. (Insurance, integer, Insurance many varchar(50), Address Linei varchar(50), Address Linez varchar(50), Zipcode varchar(10), Phone yacchar(15), Fax varchar(15), Email varchar(50) CONSTRAINT PK Insurance Companies PRIMARY KEY (Insuransen), CONSTRAINT EDUCAOCSCompanies Zircodes. FOREIGN KEY Czincode) REFERENCES Zisodes) 30 CREATE TABLE Contracts Contras ID integer RO StartDate small datetime EndDate small datetime Payment Tyne integer Insurance integen Negotiatedate float CONSTRAINIP Contacts PRIMARY KEY contrasti CONSTRAINT Pkl contracts Referrals FOREIGN KEY IRHEIR HEMERENCES Referals CONSTRAINT E contrasts Payment Types FOREIGN KEY PAYRIR REFERENCES Pays EYES CONSTRAINT EK..contrasts Iosucancecompanies. FOREIGN KEY (InsuranceID) REFERENCES Losurance companies) CREATE TABLE Employee Iynes (Employee TyreIn integer identity, Employee Tyre varchar(25), CONSTRAINT PK. Employee Tyres. PRIMARY KEY (Employee TypeID)) go CREATE TABLE EmployeeTitles (EmployeeTitleID integer, Employee Title varchar(30) CONSTRAINT PK. Employee Titles. PRIMARY KEY (EmployeeTitleID 80 CREATE TABLE Employeeskilllexels Skill Level Integer Skillevel Marcharts) CONSTRAINT PLENloveeskillevels PRIMARY KEY (Skillevel) go CREATE TABLE Billing Rates. (EmployeeTypeID integer, Skillleve ID integer, BillingRate float, CONSTRAINT PK PrimaryKey PRIMARY KEY (EmployeeTypeID. SkilllevelID) CONSTRAINT FK BillingRates EmployeeTypes FOREIGN KEY (Employee TypeID REFERENCES Employee Types. CONSTRAINT EK BillingRates Employeeskilllevels FOREIGN KEY (SkilllevelID) REFERENCES Employeeskill levels go CREATE TABLE EmployeeRanks. (RankID integer, Employee Type ID integer, Title ID integer, Skilllevel ID integer, Houyate float, Salary float, CONSTRAINT PK Employee Ranks PRIMARY KEY (RankID CONSTRAINT EK EmployeeRanks. Employee Types FOREIGN KEY (EmployeeTypeID REFERENCES Employee Types CONSTRAINT EK Employee Ranks. Employee Titles FOREIGN KEY (TitleID REFERENCES Employee Titles. CONSTRAINT FK Employee Ranks Employeeskilllevels FOREIGN KEY Skill Level ID REFERENCES Employeeskilllevels) go CREATE TABLE Employees (Employee ID integer, FirstName varchar(30), MiddleInitial varchar(1), LastName varchar(5e), Address_Line1 varchar(50), Address_Line2 varchar(50), ZipCode varchar(10), Phone varchar(14), Cell Phone varchar(14), Email varchar(50), RankID integer, Hourlyware Float, Salary Float, CONSTRAINT PK Employees. PRIMARY KEY (EmployeeID), CONSTRAINT EK Employees. EmployeeRanks FOREIGN KEY (RankID REFERENCES EmployeeRanks CONSTRAINT FK Employee ZipCodes FOREIGN KEY (ZipCode) REFERENCES ZipCodes) go CREATE TABLE Shifts (Shift ID integer, Shift Name varchar(20), StartTime time, EndTime time, CONSTRAINT PK Shifts PRIMARY KEY (ShiftID)) go CREATE TABLE DaysOfWeek (DayOfWeek ID integer, Daxikleik varchar(15), CONSTRAINT PK DaysOfWeek PRIMARY KEY (DayOfWeek ID)) go CREATE TABLE Availability Employee ID integer, Weekof small datetime. DayOfWeekID integer ShiftID integen CONSTRAINT PK Availability PRIMARY KEY (Employee ID Weekof. DayOfWeekID. ShiftID), CONSTRAINT FKI Availability Employees FOREIGN KEY Employee ID REFERENCES Employees, CONSTRAINT FLAvailability DaysOfWeek FOREIGN KEY DayOfWeekID REFERENCES DaysOfWeek CONSTRAINT FK Availability. Shifts FOREIGN KEY ShiftID REFERENCES Shifts go CREATE TABLE Medical Suppliers (Supplierin integer, SuppliecNanse varchar(5e), Address Linel varchar(5e), AddressLine2 varchar(5e), Zircode varchar(10), Phone varchar(14), Fax varchar(14), Email varchar(5e), CONSTRAINT PK Medical Suppliers PRIMARY KEY (Suppliecin), CONSTRAINT EK Medical Suppliers Zarcodes. FOREIGN KEY (Zincode) REFERENCES Zincades.) 80 CREATE TABLE Supplies (Supply integer, SupplyResscirtien varchar(40), Shareeerlinit float, CONSTRAINT PK Supplies PRIMARY KEY (SupplyIR>> 80 CREATE TABLE Supply Inventory SuRRIVIR integen SURRKLECID integer DateReceived adhdattias Unitcast float Quantity float: CONSTRAINT PK SuraxInventory PRIMARY KEY Supply Suppliecin. Rats Received CONSTRAINT EK SupplyInventory Supplies FOREIGN KEY SupplyID REFERENCES Supplies CONSTRAINT EK Suplynxentory Suppliers FOREIGN KEY SuppliCIR REFERENCES Medical Suppliers 20 CREATE TABLE Visits (VisitID integer, DateRendered smalldatetime, Start Time time, EndTime time, Employee ID integer, Patient ID integer, CONSTRAINT PK Visits PRIMARY KEY (Visit ID) CONSTRAINT FK Visits Employees FOREIGN KEY (EmployeeID) REFERENCES Employees, CONSTRAINT FK Visits Patients FOREIGN KEY (Patient ID REFERENCES Patients) go CREATE TABLE VisitDetails VisitID integer, VisitDetailID integer SupplyID integer SupplyQuantity integer Service ID integer, Charge float CONSTRAINT PKI VisitDetails PRIMARY KEY VisitID VisitDetailID CONSTRAINT KIVisitDetaliis Supplies FOREIGN KEY SupplyID REFERENCES Supplies, CONSTRAINT KIVisitDetails Services FOREIGN KEY ServiceID REFERENCES Services) go