I want to have this assignment done for me as I have another paper to do and my schedule is tight
CSIS 325 Semester Project Phase II Instructions Phase II is performed in three steps. 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 (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 Step 2: Data Loading Load the data from the Phase II Data spreadsheet (located in the Phase II folder) into the tables. Note that there are 26 worksheets in this workbook that correspond to the 26 tables you created above. After your data are loaded, execute Select statements for each of the tables to display all of the data in the tables. Note that you only have to take a screenshot of the first screen that appears in your result window. However, you must capture the SELECT statement as well as the results window. Be sure to capture the number of rows returned from the query (shown below in the lower right hand corner of the results window). Paste a copy of your screenshots at the bottom of this document. Step 3: Queries Write and execute the queries below to answer each question. As in your labs, be sure to write out your query in Word and then take a screenshot of the results after execution of each query. Data loaded into tables is worth 30% of your content grade. Correct construction and execution of your queries is worth 70% of your content grade. 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 all patients who have a last name beginning with the letter “P”. Patient Last Name, followed by a comma and a space, followed by the patient’s first name. (e.g. Smith, John) Sort order: Patient Last Name - ascending select Lastname + ', ' + FirstName as PatientName from Patients where lastname like 'P%' order by lastname 2. Display a list of all patients who have an alternate/cell phone number Patient First Name, followed by a space, followed by the patient’s last name. (e.g. Melesa Poole), alternate/cell phone number Sort order: Patient First Name – ascending Patient Last Name – ascending 3. Display a list of all patients who do not have an email address. Patient First Name, followed by a space, followed by the patient’s last name. (e.g. Melesa Poole) Sort order: Patient First Name – ascending Patient Last Name – ascending 4. Display a list of all patients who live in zipcode 24551. Patient Last Name, Address1, Address2, City, State, Zip Sort order: Patient Last Name – descending 5. Display a list of all physicians whose specialty is Internal Medicine or Orthopedics Physician First Name, space, last name (call this column Physician), Specialty Sort order: Physician First Name – ascending Physician Last Name – descending 6. Display a list of all physicians, their specialties, and their practices Physician Last Name, Specialty, Practice Sort order: Physician Specialty – ascending Physician Last Name – ascending Practice – ascending 7. Display a list of all physicians whose practices are in Lynchburg Physician Last Name, Practice Name, Address, City, State, Zipcode, Phone Sort order: Zipcode – ascending Practice Name – descending Physician Last Name – ascending 8. Display the number of physicians in each specialty Specialty, number of physicians in each specialty Sort order: Specialty 9. Display the number of physicians in each practice, broken out by specialty Practice, Specialty, number of physicians in each Sort order: Practice – ascending Specialty -- ascending 10. Display the list of specialties that have no physicians assigned to them. Specialty Sort order: Specialty – ascending 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