underwrites
determined by
included in
calculated in
determines
determine
determine
practice
receive
perform
assigned to
generate
require
occurs
based on
receive
working
available
works
EmployeeSkillLevels
SkillLevelID
SkillLevel
HourlyRate
BillingRate
InsuranceId
BillingRate
Frequency
FrequencyID
Frequency
PhysicianSpecialty
SpecialtyID
SpecialtyName
Supplies
SupplyID
SupplyDescription
SupplyCharge
PaymentTypes
PaymentTypeID
PaymentType
InsuranceCompanies
InsuranceID
InsuranceCompany
AddressLine1
AddressLine2
ZipCode
Phone
Fax
Email
EmployeeTypes
EmployeeTypeID
EmployeeType
Services
ServiceID
ServiceName
ReferralServices
ReferralID
ServiceID
Contracts
ContractID
ReferralID
StartDate
EndDate
NagotiatedRate
ZipCodes
ZipCode
State
City
Visits
VisitID
Date
StartTime
EndTime
Employees
EmployeeID
MiddleName
Address
ZipCode
Phone
RankID
HourlyWage
Email
FirstName
LastName
Availability
DayofWeekID
EmployeeID
AvailabilityID
DaysofWeek
DayofWeekID
DayofWeek
Shifts
ShiftID
StartTime
EndTime
ShiftName
Patients
PatientID
Email
CellPhone
ZipCode
HomePhone
LastName
MiddleName
Referrals
ReferralID
StartDate
EndDate
PatientID
PhysicianID
Physicians
PhysicianID
FirstName
SpecialtyID
Email
PracticeID
LastName
create database HCOProject
create table services
(ServiceID int not null,
ServiceName varchar(20),
constraint pk_services primary key (serviceID))
select * from services
exec sp_help services
create table Supplies
(SupplyID int not null,
SupplyDescription varchar(20),
SupplyCharge numeric,
constraint pk_supplies primary key (SupplyID))
select * from supplies
exec sp_help supplies
create table DaysofWeek
(DayofWeekID int not null,
DayofWeek varchar(10),
constraint pk_daysofweek primary key (DayofWeekID))
select * from daysofweek
exec sp_help daysofweek
create table Shifts
(ShiftID int not null,
StartTime smalldatetime,
EndTime smalldatetime,
ShiftName varchar(10)
constraint pk_shifts primary key (shiftID))
select * from shifts
exec sp_help shifts
create table ZipCodes
(ZipCode varchar(10),
City varchar(20),
State varchar(2),
constraint pk_ZipCode primary key (ZipCode))
select * from Zipcodes
exec sp_help ZipCodes
create table PhysicianSpecialty
(SpecialtyID varchar(20),
SpecialtyName varchar(20),
constraint pk_PhysicianSpecialty primary key (SpecialtyID ))
select * from PhysicianSpecialty
exec sp_help PhysicianSpecialty
create table EmployeeSkillLevel
(SkillLevelID int not null,
SkillLevel float,
HourlyRate numeric,
constraint pk_employeeskilllevel primary key (SkillLevelID))
select * from EmployeeSkillLevel
exec sp_help EmployeeSkillLevel
create table EmployeeTypes
(EmployeeTypeID int not null,
EmployeeType varchar(20),
constraint pk_EmployeeTypes primary key (EmployeeTypeID))
select * from EmployeeTypes
exec sp_help EmployeeTypes
create table PaymentTypes
(PaymentTypeID int not null,
PaymentType varchar(10),
constraint pk_PaymentTypes primary key (PaymentTypeID))
select * from PaymentTypes
exec sp_help PaymentTypes
create table Employees
(EmployeeID int not null,
FirstName varchar (15),
LastName varchar (20),
MiddleName varchar(15),
Address varchar (30),
Phone varchar (15),
HourlyWage varchar (12),
ZipCode varchar(10) not null,
Email varchar(20),
constraint pk_employees primary key (EmployeeID))
create table Availability
(AvailabilityID int not null,
DayofweekID int not null,
EmployeeID int not null,
ShiftID int not null
constraint pk_availability primary key (availabilityid, dayofweekid, employeeid, shiftid),
constraint fk_availability_daysofweek foreign key (dayofweekid) references daysofweek,
constraint fk_availability_employees foreign key (employeeid) references employees,
constraint fk_availability_shifts foreign key (shiftid) references shifts)
select * from Availability
exec sp_help availability
create table Patients
(PatientID int not null,
Email varchar(20) null,
Cellphone varchar(10) null,
ZipCode varchar(10) not null,
HomePhone varchar(10) not null,
LastName varchar(20),
FirstName varchar(20),
MiddleName varchar(15),
constraint pk_Patients primary key (patientid))
select * from patients
exec sp_help patients
create table Visits
(VisitID int not null,
PatientID int not null,
EmployeeID int not null,
Date smalldatetime not null,
StartDate date not null,
EndDate date not null
constraint pk_visits primary key (visitid, patientid, employeeid),
constraint fk_visits_patients foreign key (patientid) references patients,
constraint fk_visits_employees foreign key (employeeid) references employees)
select * from visits
exec sp_help visits
create table InsuranceCompanies
(Insuranceid int not null,
InsuranceCompany varchar(20),
AddressLine1 varchar(20),
AddressLine2 varchar(20) null,
ZipCode varchar(10) not null,
Phone varchar(10),
Fax varchar(10) null,
Email varchar(10),
constraint pk_insurancecompanies primary key (InsuranceID))
select * from InsuranceCompanies
exec sp_help insurancecompanies
create table Frequency
(FrequencyID int not null,
Frequency varchar(10)
constraint pk_frequency primary key (frequencyID))
select * from frequency
exec sp_help frequency
create table Physicians
(PhysicianID int not null,
FirstName varchar(20),
LastName varchar(20),
Email varchar(20),
PracticeID int not null,
SpecialtyID int not null,
constraint pk_physicians primary key (PhysicianID))
select * from physicians
exec sp_help physicians
create table Referrals
(ReferralID int not null,
StartDate smalldatetime,
EndDate smalldatetime,
PatientID int not null,
PhysicianID int not null,
constraint pk_referrals primary key (referralID))
select * from referrals
exec sp_help referrals
create table Contracts
(ContractID int not null,
ReferralID int not null,
StartDate smalldatetime,
EndDate smalldatetime,
NegotiatedRate varchar(10),
constraint pk_contracts primary key (contractid))
select * from contracts
exec sp_help contracts
create table ReferralService
(ServiceID int not null,
FrequencyID int not null,
ReferralID int not null
constraint pk_referralservice primary key (serviceid, frequencyid, referralid),
constraint fk_referralservice_referral foreign key (referralid) references referrals,
constraint fk_referralservice_frequency foreign key (frequencyid) references frequency)
select * from referralservice
exec sp_help referralservice
create table BillingRate
(BillingRate varchar(10),
InsuranceID int not null,
SkilllevelID int not null,
EmployeeTypeID int not null,
constraint pk_billingrate primary key (insuranceid, skilllevelid, Employeetypeid),
constraint fk_billingrate_insurance foreign key (insuranceid) references insurancecompanies,
constraint fk_billingrate_skilllevel foreign key (skilllevelid) references employeeskilllevel,
constraint fk_billingrate_employeetype foreign key (employeetypeid) references employeetypes)
select * from billingrate
exec sp_help billingrate
Eicreate
table
BillingRate
(BillingRate
varchar(10),
InsuranceID
int
not
null,
SkilllevelID
int
not
null,
EmployeetypeID
int not
null,
constraint
pk_billingrate
primary key
(insuranceid,
skilllevelid,
Employectypeid)
constraint
#k_billingrate_insurance
foreign
key
(insuranceid)
references
insurancecompanies,
constraint fk_billingrate_skilllevel
foreign
key (skilllevelid)
references
employeeskilllevel,
constraint
fk_billingrate_employeetype
foreign
key
(employeetypeid)
references
employeetypes)
select
*
from
billingrate
exec
sp_help
billingrate
100%
>|
ES
Resuts
fl
Messages
BilingRate
Insurance!
SkillevellD
Employee
TypelD
Name
Owner
Type
Crested_datetime
1
[BilingRate
|
dbo
usertable
2018-11-26
22:29:54,120
7
Column_name
Type
Computed
Length
Prec
Scale
Nullable
TrimTraiingBlanks
FixedLenNullnSource
Collation
1
BilingRate
varchar
no
10
yes
no
yes
‘SQL_Latin1_General_CP1_CI_AS
2
wrancelD
0
va) we)
NULL
3
0 a) a)
NULL
4
ro
a) a)
NULL
Index_name
_index_descrgtion
index
_keys
1
[pkbaingrate
|
clustered,
unique,
pimary key
located
on
PRIMARY
InsurancelD.
SkilevellD.
Employee
TypelD
“constraint
type
constrant_name
delete_action
update_action
status_enabled
status
fr_replcation
constraint
keys
1
[FOREIGN
KEY
|
fk_baingrate_employeetype
No
Action
No
Action
Enabled
|
For_Replication
__EmployeeTypelD
2
REFERENCES
HCOProject
dbo
Employee
Types
(Employe.
3
FOREIGN
KEY
fk
_bilingrate_insurance
No
Action
No
Action
Enabled
Is_For_Replication
InsurancelD
4
REFERENCES
HCOProject
dbo
.InsuranceCompanies
(Insu.
5
FOREIGN
KEY
—fk_bilingrate_skillevel
No
Action
No
Action
Enabled
Is_For_Replication
‘SkillevellD
6
REFERENCES
HCOProject.dbo
Employee
SkillLevel
(SkillL.
i,
PRIMARY
K.
Pk
_bilingrate
a)
va) va)
Wa)
InsurancelD,
SkillevellD,
Employee
TypelD
