CSIS 325 D03- Use the Number of Rows in the Solution Column to Check the Results of your Queries Semester Project Phase Instructions LU, US

Looking for Plagiarism free Answers for your US, UK, Singapore college/ university Assignments.

University Liberty University(LU)
Subject CSIS 325 D03

CSIS 325 Semester Project Phase IIInstructions

Step 1:  Table Creation

Create a new database and execute the code below in SQL Server’s query window to create the database tables.
CREATETABLEPhysicianSpecialties
(SpecialtyIDinteger,
SpecialtyNamevarchar(50),
CONSTRAINTPK_PhysicianSpecialtiesPRIMARYKEY (SpecialtyID))
go

CREATETABLEZipCodes
(ZipCodevarchar(10),
City varchar(50),
Statevarchar(2),
CONSTRAINTPK_ZipCodesPRIMARYKEY (ZipCode))
go

CREATETABLEPhysicianPractices
(PracticeIDinteger,
PracticeNamevarchar(50),
Address_Line1 varchar(50),
Address_Line2 varchar(50),
ZipCodevarchar(10),
Phone varchar(14),
Fax varchar(14),
WebsiteURLvarchar(50),
CONSTRAINTPK_PhysicianPracticesPRIMARYKEY (PracticeID),
CONSTRAINTFK_PhysicianPractices_ZipCodesFOREIGNKEY (ZipCode)REFERENCESZipcodes)
go

CREATETABLE Physicians
(PhysicianIDinteger,
FirstName varchar(40),
LastNamevarchar(50),
PracticeIDinteger,
SpecialtyIDinteger,
Email varchar(50),
CONSTRAINTPK_PhysiciansPRIMARYKEY (PhysicianID),
CONSTRAINTFK_Physicians_PracticesFOREIGNKEY (PracticeID)REFERENCESPhysicianPractices,
CONSTRAINTFK_Physicians_PhysicianSpecialitiesFOREIGNKEY (SpecialtyID)REFERENCESPhysicianSpecialties)
go

Hire a Professional Essay & Assignment Writer for completing your Academic Assessments

CREATETABLE Patients
(PatientIDinteger,
FirstName varchar(50),
MiddleInitialvarchar(1),
LastNamevarchar(50),
Address_Line1 varchar(50),
Address_Line2 varchar(50),
ZipCodevarchar(10),
Phone_Homevarchar(14),
Phone_Alternatevarchar(14),
Email varchar(50),
CONSTRAINTPK_PatientsPRIMARYKEY (PatientID))
go

CREATETABLE Referrals
(ReferralIDinteger,
StartDate smalldatetime,
EndDatesmalldatetime,
PatientIDinteger,
PhysicianIDinteger,
CONSTRAINTPK_ReferralsPRIMARYKEY (ReferralID),
CONSTRAINTFK_Referrals_PatientsFOREIGNKEY (PatientID)REFERENCES Patients,
CONSTRAINTFK_Referrals_PhysiciansFOREIGNKEY (PhysicianID)REFERENCES Physicians)
go

CREATETABLEServices
(ServiceIDinteger,
ServiceNamevarchar(50),
CONSTRAINTPK_ServiceIDPRIMARYKEY (ServiceID))
go

CREATETABLE Frequencies
(FrequencyIDinteger,
Frequency varchar(30),
CONSTRAINTPK_FrequenciesPRIMARYKEY (FrequencyID))
go

CREATETABLEReferralServices
(ReferralIDinteger,
ServiceIDinteger,
FrequencyIDinteger,
CONSTRAINTPK_ReferralServicesPRIMARYKEY (ReferralID,ServiceID),
CONSTRAINTFK_ReferralServices_ReferralsFOREIGNKEY (ReferralID)REFERENCES Referrals,
CONSTRAINTFK_ReferralServices_ServicesFOREIGNKEY (ServiceID)REFERENCESServices,
CONSTRAINTFK_ReferralServices_FrequenciesFOREIGNKEY (FrequencyID)REFERENCES Frequencies)
go

CREATETABLEPaymentTypes
(PaymentTypeIDinteger,
PaymentTypevarchar(25),
CONSTRAINTPK_PaymentTypesPRIMARYKEY (PaymentTypeID))
go

CREATETABLEInsuranceCompanies
(InsuranceIDinteger,
InsuranceCompanyvarchar(50),
Address_Line1 varchar(50),
Address_Line2 varchar(50),
ZipCodevarchar(10),
Phone varchar(15),
Fax varchar(15),
Email varchar(50),
CONSTRAINTPK_InsuranceCompaniesPRIMARYKEY (InsuranceID),
CONSTRAINTFK_InsuranceCompanies_ZipCodesFOREIGNKEY (ZipCode)REFERENCESZipCodes)
go

CREATETABLE Contracts
(ContractIDinteger,
ReferralID integer,
StatDate smalldatetime,
EndDatesmalldatetime,
PaymentTypeIDinteger,
InsuranceIDinteger,
NegotiatedRatefloat,
CONSTRAINTPK_ContractsPRIMARYKEY (ContractID),
CONSTRAINTPK_Contracts_ReferralsFOREIGN KEY (ReferralID) REFERENCES Referrals,
CONSTRAINTFK_Contracts_PaymentTypesFOREIGNKEY (PaymentTypeID)REFERENCESPaymentTypes,
CONSTRAINTFK_Contracts_InsuranceCompaniesFOREIGNKEY (InsuranceID)REFERENCESInsuranceCompanies)
go

CREATETABLEEmployeeTypes
(EmployeeTypeIDintegeridentity,
EmployeeTypevarchar(25),
CONSTRAINTPK_EmployeeTypesPRIMARYKEY (EmployeeTypeID))
go

CREATETABLEEmployeeTitles
(EmployeeTitleIDinteger,
EmployeeTitlevarchar(30),
CONSTRAINTPK_EmployeeTitlesPRIMARYKEY (EmployeeTitleID))
go

CREATETABLEEmployeeSkillLevels
(SkillLevelIDinteger,
SkillLevelvarchar(15),
CONSTRAINTPK_EmployeeSkillLevelsPRIMARYKEY (SkillLevelID))
go

CREATETABLEBillingRates
(EmployeeTypeIDinteger,
SkillLevelIDinteger,
BillingRatefloat,
CONSTRAINTPK_PrimaryKeyPRIMARYKEY (EmployeeTypeID,SkillLevelID),
CONSTRAINTFK_BillingRates_EmployeeTypesFOREIGNKEY (EmployeeTypeID)REFERENCESEmployeeTypes,
CONSTRAINTFK_BillingRates_EmployeeSkillLevelsFOREIGNKEY (SkillLevelID)REFERENCESEmployeeSkillLevels)
go

CREATETABLEEmployeeRanks
(RankIDinteger,
EmployeeTypeIDinteger,
TitleIDinteger,
SkillLevelIDinteger,
HourlyRate float,
Salary float,
CONSTRAINTPK_EmployeeRanksPRIMARYKEY (RankID),
CONSTRAINTFK_EmployeeRanks_EmployeeTypesFOREIGNKEY (EmployeeTypeID)REFERENCESEmployeeTypes,
CONSTRAINTFK_EmployeeRanks_EmployeeTitlesFOREIGNKEY (TitleID)REFERENCESEmployeeTitles,
CONSTRAINTFK_EmployeeRanks_EmployeeSkillLevelsFOREIGNKEY (SkillLevelID)REFERENCESEmployeeSkillLevels)
go

 

CREATETABLE Employees
(EmployeeIDinteger,
FirstName varchar(30),
MiddleInitialvarchar(1),
LastNamevarchar(50),
Address_Line1 varchar(50),
Address_Line2 varchar(50),
ZipCodevarchar(10),
Phone varchar(14),
Cell_Phonevarchar(14),
Email varchar(50),
RankIDinteger,
HourlyWagefloat,
Salary float,

CONSTRAINTPK_EmployeesPRIMARYKEY (EmployeeID),
CONSTRAINTFK_Employees_EmployeeRanksFOREIGNKEY (RankID)REFERENCESEmployeeRanks,
CONSTRAINTFK_Employee_ZipCodesFOREIGNKEY (ZipCode)REFERENCESZipCodes)
go

Hire a writer to get plagiarism free assignment answers of this question

CREATETABLE Shifts
(ShiftIDinteger,
ShiftNamevarchar(20),
StartTimetime,
EndTimetime,
CONSTRAINTPK_ShiftsPRIMARYKEY (ShiftID))
go

CREATETABLEDaysOfWeek
(DayOfWeekIDinteger,
DayOfWeekvarchar(15),
CONSTRAINTPK_DaysOfWeekPRIMARYKEY (DayOfWeekID))
go

CREATETABLEAvailability
(EmployeeIDinteger,
WeekOfsmalldatetime,
DayOfWeekIDinteger,
ShiftIDinteger,
CONSTRAINTPK_AvailabilityPRIMARYKEY (EmployeeID,WeekOf,DayOfWeekID,ShiftID),
CONSTRAINTFK_Availability_EmployeesFOREIGNKEY (EmployeeID)REFERENCES Employees,
CONSTRAINTFK_Availability_DaysOfWeekFOREIGNKEY (DayOfWeekID)REFERENCESDaysOfWeek,
CONSTRAINTFK_Availability_ShiftsFOREIGNKEY (ShiftID)REFERENCES Shifts)
go

 

CREATETABLEMedicalSuppliers
(SupplierIDinteger,
SupplierNamevarchar(50),
Address_Line1 varchar(50),
Address_Line2 varchar(50),
ZipCodevarchar(10),
Phone varchar(14),
Fax varchar(14),
Email varchar(50),
CONSTRAINTPK_MedicalSuppliersPRIMARYKEY (SupplierID),
CONSTRAINTFK_MedicalSuppliers_ZipCodesFOREIGNKEY (ZipCode)REFERENCESZipCodes)
go

CREATETABLE Supplies
(SupplyIDinteger
SupplyDescriptionvarchar(40),
ChargePerUnit float,
CONSTRAINTPK_SuppliesPRIMARYKEY (SupplyID))
go

CREATETABLESupplyInventory
(SupplyIDinteger,
SupplierIDinteger
DateReceivedsmalldatetime,
UnitCostfloat,
Quantity float,
CONSTRAINTPK_SupplyInventoryPRIMARYKEY (SupplyID,SupplierID,DateReceived),
CONSTRAINTFK_SupplyInventory_SuppliesFOREIGNKEY (SupplyID)REFERENCES Supplies,
CONSTRAINTFK_SupplyInventory_SuppliersFOREIGNKEY (SupplierID)REFERENCESMedicalSuppliers)
go

CREATETABLE Visits
(VisitIDinteger,
DateRenderedsmalldatetime,
StartTimetime,
EndTimetime,
EmployeeID integer,
PatientIDinteger,
CONSTRAINTPK_VisitsPRIMARYKEY (VisitID),
CONSTRAINTFK_Visits_EmployeesFOREIGNKEY (EmployeeID)REFERENCES Employees,
CONSTRAINTFK_Visits_PatientsFOREIGNKEY (PatientID)REFERENCES Patients)
go

CREATETABLEVisitDetails
(VisitIDinteger,
VisitDetailIDinteger,
SupplyIDinteger,
SupplyQuantityinteger,
ServiceIDinteger,
Charge float,
CONSTRAINTPK_VisitDetailsPRIMARYKEY (VisitID,VisitDetailID),
CONSTRAINTFK_VisitDetaiils_SuppliesFOREIGNKEY (SupplyID)REFERENCES Supplies,
CONSTRAINTFK_VisitDetails_ServicesFOREIGNKEY (ServiceID)REFERENCESServices)
go

CSIS 325 – Phase 2Benchmarks
Use the number of rows in the solution column to check the results of your queries.

CriteriaOutput FormatSolution
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

 

 

44 rows

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

 

 

319 rows

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

 

 

143 rows

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

 

 

36 rows

5. Display a list of all physicians whose specialty is Internal Medicine or OrthopedicsPhysician First Name, space, last name (call this column Physician), Specialty

Sort order:  Physician First Name – ascending

Physician Last Name – descending

 

 

36 rows

6. Display a list of all physicians, their specialties, and their practicesPhysician Last Name, Specialty, Practice

Sort order:  Physician Specialty  – ascending

Physician Last Name – ascending

Practice – ascending

 

 

 

158 rows

7. Display a list of all physicians whose practices are in LynchburgPhysician Last Name, Practice Name, Address, City, State, Zipcode, Phone

Sort order:   Zipcode – ascending

Practice Name – descending

Physician Last Name – ascending

 

79 rows

8. Display the number of physicians in each specialtySpecialty, number of physicians in each specialty

Sort order: Specialty

 

26 rows

9. Display the number of physicians in each practice, broken out by specialtyPractice, Specialty, number of physicians in each

Sort order: Practice – ascending

Specialty — ascending

 

49 rows

10. Display the list of specialties that have no physicians assigned to them.Specialty

Sort order:  Specialty – ascending

 

 

5 rows

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

 

 

400 rows

12. Display a list of all the referrals whose start date is between October 1, 2014 and November 5, 2014Patient 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

 

 

52 rows

13. Display the number of referrals given by each physicianPhysician Last name, Physician First Name, number of referrals

Sort Order: Physician Last Name – ascending

Physician First Name – ascending

 

 

 

158 rows

14. List the number of referrals in 2014 for each service requested.Service name, number of referrals

Sort order:  Service name

 

15 rows

15. Display a list of all patients requiring exercise therapy in 2013Patient Last Name, Patient First Name

Sort order:  Patient last name – ascending

Patient first name – ascending

 

52 rows

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

 

10 rows

17. Display the contracts and payment methods associated with each referralPatient 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

 

825 rows

18. Display the number of contracts whose payment method is InsuranceNumber of contracts (This is a single value) 

Result = 273

19. Display the number of contracts whose payment method is Insurance, broken out by Insurance CompanyInsurance Company Name, number of contracts

Sort order:  Insurance company name

 

5 rows

20. List the Employees who are NursesEmployee 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”) 

34 rows

21. Display the average hourly wage for all employees who are aides.Average hourly wage (single value) 

Result = 10.8571428571429

22. Display the average hourly wage for all hourly employees broken out by level.Skill level, average wage

Sort order:  Skill Level

 

Results:

Level 1  10.3

Level 2  16.8571428571429

Level 3  33.5

23. Display the total salary for all salaried employees.Total salaries (single value) 

Result = 291000

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

 

19 rows

25. Display a list of Employees who are nurses and were available to work on Sunday evenings during the week of 11/2/2014Employee Last Name, Employee First Name

Sort order:  Last Name – ascending

First Name – ascending

 

10 rows

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

 

26 rows

27. Display the total quantity of catheters added to inventory during 2013.Total catheters (single value) 

Result = 283

28. Display the total cost of “sterile gloves – small” provided by Poole’s Medical supplies during 2013.Total cost (single value) 

Result = 1662

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

 

 

 

22 rows

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

 

Results:

Lynchburg Medical Supplies                11961

Poole’s Medical Supplies                15712

Virginia Medical Supplies                11506

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

 

472 rows

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)

 

 

Result = 99

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) 

Result = 58

34. List the total number of 4” self-adhesive bandages that were used in 2014Total number of 4” self-adhesive bandages (single value) 

Result = 3453

35. List the average charge per visit per month in 2013 broken out by monthsMonth, average cost per visit

Sort order:  month number – ascending

 

 

12 rows

36. Provide a unique list of patients who received visits for feeding on November 1, 2014, until the current date.Patient Last Name, Patient First Name

Sort order:  Patient Last Name – ascending

Patient First Name – ascending

 

29 rows

Take professional academic assistance & Get 100% Plagiarism free papers

 

Get Help By Expert

Are you searching for Cheap Assignment Help? Or you want Premium Assignment Help? Relax, we will give you a helping hand we are the ultimate online portal where you can buy custom assignment solutions easily. Scholars from all over the world order our Premium Assignment Help in order to score top grades in their exams. Hire professional assignment writing experts who provide exquisite assignment help to scholars at cheap prices.

Looking for Plagiarism free Answers for your US, UK, Singapore college/ university Assignments.

Get Support Instantly
info@studentsassignmenthelp.com
Quick Connect