Location
Availability
Availability
ZipCodes
Zip
PatientID
NurseID
AideID
ReferralID
BilledTo
UsedFor
Inventory
InventoryID
NurseID
AidID
PaymentsID
PatientID
MedicareB_ID
MediciadID
Private_PayID
Priv_InsID
ContractID
Insures
Insures
AgreesTo
Private_Pay
Private_PayID
PatientID
ContractID
AidID
NurseID
ReferralID
PaymentID
InventoryID
AgreesTo
Private_Ins
Priv_InsID
ContractID
NurseID
AidID
ReferralID
PatientID
PaymentID
InventoryID
Medicaid
MedicaidID
NurseID
ReferralID
PaymentID
AidID
PatientID
DirectCostID
IndirectCostID
ContractID
InventoryID
MedicareB
MedicareB_ID
ReferralID
PaymentID
NurseID
AidID
DirectCostID
IndirectCostID
InventoryID
PatientID
ContractID
RelatedTo
IndirectCosts
IndirectID
Utilities
StaffSalary
Rent
OfficeSupplies
MedicareB_ID
MedicaidID
BilledTo
DirectCosts
DirectCostID
InventoryID
PatientID
NurseID
AidID
ContractID
PaymentID
MedicaidID
MedicareB_ID
Binds
Contract
ContractID
ReferralID
PatientID
PaymentID
StartDate
EndDate
Refers
Doctor
DoctorID
ReferralID
PaysFor
Aids
Aids
AidID
FirstName
LastName
Address
Zip
Phone
Birthdate
ReferralID
PatientID
InventoryID
PaymentID
AidRate
AidStipendRate
Availability
SSN
ReferralFor
Patient
PatientID
ReferralID
NurseID
AidID
FirstName
LastName
Address
Zip
PaymentID
DirectCostID
IndiectCostID
InventoryID
Birthdate
Phone
SSN
MedicareB_ID
Medicaid
Private_PayID
Priv_InsID
NumbVisits
ContractID
AssignedTo
Nurse
NurseID
Nurse
FirstName
LastName
Address
Zip
Phone
Birthdate
ReferralID
AidID
PatientID
NurseRate
NurseStipendRate
Availability
SSN
InventoryID
PaysWith
Payments
PaymentID
ReferralID
NurseID
AidID
MedicareB_ID
MediciaidID
Private_PayID
Private_InsID
NurseRate
AidRate
AidStipendRate
NurseStipendRate
Referrals
ReferralID
DoctorID
ReferralType
InventoryID
ReferallDesc
NumbVisits
PatientID
PaymentID
NurseID
AidID
Zip
create table Doctors
ERD for HCO
{4g
S01
Query1.sql-
LAPTOP-NSNULZAU.HCO2
(LAPTOP-NSNULZAU\cash
america
(
File
Fait
View
Query
Project
Debug
Tools
Window
Help
Ge GS
td
|
NewQuey
Oy
ob 6B
|
&
ESii|[Hcon___-|
-
Microsoft
SQL
Server
Management
Studio
alo-e-a-Slal>
4
/QGO)
= 2
Connect
3¢ 34
5
7
215
[@
LAPTOP-NSNUL3AU
(SQL
Server
12.
0
=
Databases
select
*
trom
Doctors
exec
sp_help
Doctors
Properties
Current
connection
parameters
Ei
aN
Connection
failur
Elapsed
time
_00:00:00.453
P
Type
here
to
search
fe)
=
Finish
time
11/25/2019
2:12:50
PM
Name
LAPTOP-NSNULBAU
Rowsretumed
11
Starttime
—_11/25/201921249
EM
State
Open
Connection
name
LAPTOP-NSNUL3AU
(Uf
Gi
dbo.IndirectCosts
v
i
dbo.inventory
eK
5
P
Connection
elaps:
00:00:00.453
i
dbo:Mectcsid
Connection
finish
1125/2018
21250
PM
I
dbo
Mesicaret
i
Renits
[iy
Messages
Connection
rows
1
1
dbo.Nurses
DoctoriD
Referral)
a)
Connection
start
t
11/25/2019
2:12:49
PM
Gl
dbo
Patients
to
J]
Connection
state
Open
S
epemens
2
{2
2
Display
name
LAPTOP-NSNUL3AU
Gi
dboPrivate
Pay
Loginname
—_LAPTOP-NSNUL3AU\caf
Gi
dbo
Referrals
Name
Owner
Type
Created_datetime
‘Server
name
LAPTOP-NSNUL3AU
1
Doctors
dbo
usertable
2019-11-22
14:48:51.397
Serverversion
12.0.6108
Column_name
Type
Computed
Length
Prec
Scale
Nuilable
TrimTrailingBlanks
FixedLenNuillnSource
Collation
=e
Facing
52
1
DoctorlD
int
ono
4
wm
0
nO.
(v/a)
a)
‘NULL
2
Referali”
int
no 4
1
0m
wo)
ve)
NULL
Mentity
Seed
Increment
—_Not
For
Replication
1
No
identity
column
defined.
NULL NULL
‘NULL
v
pName
Ee
:
The
name
ofthe
connection.
(DoctorID integer,
ReferralID integer not null,
constraint pk_doctor primary key (DoctorID),
create table ZipCodes
(Zip varchar(5),
PatientID integer,
NurseID integer,
AideID integer,
ReferralID integer,
constraint pk_zipcode primary key (Zip)
constraint fk_patient_zip foreign key (PatientID) references Patients,
constraint fk_nurse_zip foreign key (NurseID) references Nurses,
constraint fk_aide_zip foreign key (AideID) references Aides)
create table Referrals
(ReferralID integer,
DoctorID integer not null,
ReferralType varchar(30) not null,
InventoryID integer,
ReferralDesc varchar(200) not null,
NumbVisits varchar(200) not null,
PatientID integer not null,
PaymentID integer not null,
NurseID integer not null,
AidID integer not null,
constraint pk_referral primary key (ReferralID),
constraint fk_doctor foreign key (DoctorID) references Doctors,
constraint fk_nurse_referrals foreign key (NurseID) references Nurses,
constraint fk_aid_referrals foreign key (AidID) references Aides)
alter table Referrals
add constraint fk_payment_referrals foreign key (PaymentID) references Payments
alter table Referrals
add constraint fk_patient_referrals foreign key (PatientID) references Patients
alter table Referrals
add constraint fk_nurse_referrals foreign key (NurseID) references Nurses
alter table Referrals
add constraint fk_aid_referrals foreign key (AidID) references Aides
alter table Referrals
add constraint fk_inventory_referrals foreign key (InventoryID) references
Inventory
3
SL
Query!
sql
-
LAPTOP-NSNUL3AU.HCO2
(LAPTOP-NSNUL3AU\
cash
america
(52)
File
Edit
View
Query
Project
Debug
Tools
Window
Help
Pialy
~
G
ed
h|
New
Query
(iy
ty eh
fa
|
&
-
Microsoft
SQL
Server
Management
Studio
- x
lo
-e-
2-3)
>
[23/43
63/43|
= 2
Connect
37
Bm
7
(1S
1
from
Referral:
=]
Current
connection
parameters
:
select
* from
Referrals
A
=)
[@
LAPTOP-NSNUL3AU
(SQL
Server
12.
exec
sp_help
Referrals
nS
Connection
filun
Elapsed
time
00:0000656
Finish
time
11/25/2019
21422
PM
Name
LAPTOP-NSNUL3AU
Rows
retuned
28
Start
time
11/25/2019
21421
PM
State
Open
Connection
name
LAPTOP-NSNUL3AU
(U
i
dbo.IndirectCosts
v
GD
dbonventory
mE
.
Connection elaps00:0000.656
©
dboMedicaid
a &
Connection
finish
11/25/2019
2:14:22
PM
Gi
dbo.MedicareB
Results
‘Messages
Connection rows 28
3
Sees
a4
1
‘Skiled
Nuse
1
Wound
Care
Upto 90
1 1 1 1
©
Connection
start
t
11/25/2019
2:14:21
PM
5
abePayments
5 5 2
Home
Heath
Ad
1
Bathing
Astarce
Every
other
day 2
1 1
2
Connection
state
Open
5
dbo
Private
Ins
6 6 2
Skiled
Nuse
3
Change
catheter.
Weekiy
1
2 3 2
Display
name
LAPTOP-NSNUL3AU
a
‘doo
Private
Poy
7 5
oa
Login
name
LAPTOP-NSNUL3AU\
ca}
“
1.
ame
Owner
Type
Created
datetime
‘Server
name
LAPTOP-NSNUL3AU
BD
dbo
Referrals
1
[Refenais”|
dbo
usertable
2019-11-22
14:57.09.393
5,
tn.06108
S
aes
\ i
server
version
Views
Column_name
Type
Computed
Length
Prec
Scale
Nullable
TiinTralingBlanks
FxedLentlullnSource
Colton
a
Session
Tracing
ID
‘Synonyms
1
[ReferaliD,
int
no 4
10
0m0
inva)
va)
NULL
PID
52
Programmability
2
“DectoriD
int
no 4
1
0
0
(n/a)
(va)
NULL
Service
Broker
3.
ReferaiType
va...
0 0
yes
no
yes
sal
Storage
4
nventoyiD
int
no 4
10
0
yes
rv)
va)
NULL,
Security
ILS
Feterabese
va.
no 200
yes
no
yes
sal
+
Pere
cof
the
connect
P
Type
here
to
search
fe)
=
create table Patients
(PatientID integer,
FirstName varchar(20) not null,
LastName varchar(30) not null,
SSN varchar(11) not null,
Birthdate date not null,
Address varchar(30),
Zip varchar(5) not null,
Phone varchar(10) not null,
ReferralID integer not null,
ContractID integer not null,
PaymentID integer not null,
NurseID integer not null,
AidID integer not null,
NumbVisits varchar(200) not null,
InventoryID integer,
MedicareB_ID integer,
MedicaidID integer,
Private_PayIS integer,
Private_InsID integer,
DirectCostID integer,
IndirectCostID integer,
constraint pk_patient primary key (PatientID),
constraint fk_referral foreign key (ReferralID) references Referrals,
constraint fk_zip_patient foreign key (Zip) references ZipCodes,
constraint fk_nurse_patient foreign key (NurseID) references Nurses)
alter table Patients
add constraint fk_patients_aids foreign key (AidID) references Aides
alter table Patients
add constraint fk_patients_medicare foreign key (MedicareB_ID) references
MedicareB
alter table Patients
add constraint fk_patients_medicaid foreign key (MedicaidID) references Medicaid
alter table Patients
add constraint fk_patients_priv foreign key (Private_InsID) references
Private_Ins
alter table Patients
add constraint fk_patients_priv_pay foreign key (Private_PayID) references
Private_Pay
alter table Patients
add constraint fk_patients_inventory foreign key (InventoryID) references
Inventory
alter table Patients
add constraint fk_patients_contract foreign key (ContractID) references Contracts
(Ag
Phases
-
LAPTOP-NSNUL3AU.HCO2
(LAPTOP-NSNUL3AU\cash
america
(2
File
Edit
View
Query
Project
Debug
Tools
Window
Help
New
Query
Ly
GB
i
|
4
4
GO
a-Gad|
PSei|fucon_-|
Connect
37
32
¥
215
&
(@
LAPTOP-NSNUL3AU
(SQL
Server
12.0.6
©
Databases
BP
Type
here
to
search
w/o
Microsoft
SQL
Server
Management
Studia
-0-&-
[al
>
select
*
from
Patients
exec
sp_help
Patients
PatentIO.
FirstName
Laetlame
SSN
Bithdste
Adress
7
doe
Smith
558697895
1946-11-20
123
Oak
St,
Sel, LA
2
"
Sue.
‘Smith
1258795698
1932-04-01
456
M_K
BLVD,
Slidell,
LA
3 Bob
Smth_-—«=«5689674582.
1961-08-15
456
M.K
BLVD.
Sel, LA
Nene
Owner
Type
Crested_detetine
[Patents]
dbo
usertable
2018-1122
15.2441
640
Column_name
Type
‘Computed
Length
Prec
Scale
Nullable
TrimTrailingBlanks
PatientiD
|
int
no 4
1
0 no
va)
“FirstName
varchar
no 20
yes
‘70.
LaaiName
varchar
no 30
yes
no
SSN
varchar
no
"
yes
no
Bithdste
date
no 3
1
0
yes
wa)
Adress
varchar
no 30
yes
no
varchar
no 5
yes
no
varchar
no
10
yes
no
£
|
NULL NULL NULL
iat
[3
14}
©
Es
70461
9957894587
1
70460 9954225946 3
Zp
Phone
ReferallD
ConiractiD
PaymentiD
NurselD
Adi
2
1
2 3
1
2 2
1
3 3
1
2
Colton
NULL
‘SQL_Latin1_Generel_CP1_CI_AS
‘SQL_Latin1_Generel_CP1_C|_AS
‘SQL_Latin1_General_CP1_C|_AS
NULL
‘SQL_Latin1_Generel_CP1_CI_AS
‘SQL_Latin1_Generel_CP1_C|_AS
‘SQL_Latin1_Generel_CP1_C|_AS
ED
wi
‘Numi
Visits
0
Approved
for
30
a
mont
‘Approved
for
30
vistis
2
Approved
for
30
vistis
2
>
frye
Os
48
PM
ena
alter table Patients
add constraint fk_patients_payment foreign key (PaymentID) references Payments
create table Nurses
(NurseID integer,
FirstName varchar(20) not null,
LastName varchar(30) not null,
SSN varchar(11) not null,
Phone varchar(10) not null,
Address varchar(30) not null,
Zip varchar(5) not null,
Birthdate date not null,
NurseRate decimal (10,2) not null,
NurseStipEndRate decimal (10,2),
ReferralID integer,
PatientID integer,
InventoryID integer,
Availability varchar(200) not null,
constraint pk_nurse primary key (NurseID),
constraint fk_zip_nurse foreign key (Zip) references ZipCodes,
constraint fk_patient_nurse foreign key (PatientID) references Patients,
constraint fk_referral_nurse foreign key (ReferralID) references Referrals)
I could not get any other nurse data entered no matter how I manipulated the data
and constraints after the first entry
Error: Msg 8152, Level 16, State 14, Line 3
String or binary data would be truncated.
The statement has been terminated.
create table Payments
(PaymentID integer,
ReferralID integer not null,
NurseID integer not null,
AidID integer not null,
MedicareB_ID integer,
MedicaidID integer,
Private_PayID integer,
Private_InsID integer,
NurseRate decimal (10,2) not null,
NurseStipEndRate decimal (10,2),
AidRate decimal (10,2) not null,
AidStipEndRate decimal (10,2),
Constraint pk_payment primary key (PaymentID),
constraint fk_referral_payment foreign key (ReferralID) references Referrals,
constraint fk_nurse_payment foreign key (NurseID) references Nurses,
constraint fk_aid_payment foreign key (AidID) references Aides)
alter table Payments
add constraint fk_medicare_payments foreign key (MedicareB_ID) references
MedicareB
alter table Payments
add constraint fk_medicaid_payments foreign key (MedicaidID) references Medicaid
alter table Payments
add constraint fk_priv_payments foreign key (Private_PayID) references
Private_Pay
alter table Payments
add constraint fk_priv_ins_payments foreign key (Private_InsID) references
Private_Ins
3
SL
Query!
sql
-
LAPTOP-NSNUL3AU.HCO2
(LAPTOP-NSNUL3AU\
cash
america
(52)
File
Edit
View
Query
Project
Debug
Tools
Window
Help
Pialy
~
G
ed
h|
New
Query
(iy
ty eh
fa
|
&
-
Microsoft
SQL
Server
Management
Studio
[9
~
co
-
6-5
|
ih]
>
[2°49
43963(3|
= 2
Connecty
34
BJ
mw
TF
2)
5
‘Current
connection
parameters
+
©
[B
LAPTOP-NSNULSAU
GOL
Sererf2.0
select
*
fron
Rayment
aes
Connection
failun
Elapsed
time
00:00:00.593
Finish
time
11/25/20193:11:18
PM
Name
LAPTOP-NSNUL3AU
Rows
etumed
27
Start
time
11/25/2019
3:11:18
PM
State
Open
Connection
name
LAPTOP-NSNUL3AU
(Lf
Gi
dbo.indirectCosts
v
i
dbo.inventory
a
“connection
elaps:
00:00:00.593
dbo
Medicaid
Connection
finish
11/25/2019
31:18
PM
5
dbo
eine
Sy Pesto iy
Wesnos
Connastion vows 27
Gl
dbo.Nurses
PaymertID
ReferallD
NuselD AdID
MedicareB
ID
MedcaidID
Private
PaylD
Private_InsiD
AD
Connection
start
11/25/2019
3:11:18
PM
F
abo
Patients
1a
a
!
iii
1 1 1
Connection
state
Open
S
epemens
202
2 2
202
2 2 2
Display
name
LAPTOP-NSNUL3AU
evate
203
3 3
203
3 3 3
Login
name
_LAPTOP-NSNUL3AU\cd
Gi
dboPrivate
Pay
Fl
dboReferrals
Name
Owner
Type:
Created
_datetime
Servername
LAPTOP-NSNUL3AU
Gi
dboZipCodes
1
|
Payments
dbo
usertable
2019-11-25
11:46:48,597
Serverversion
12.0.6108
Views
Golumn_name
Type
Computed
Length
Prec
Scale
Nullable
TimTraiingBlanks
FxedLenNullnSource
Collation
epson
Tecra
»
so
‘Synonyms
1
(Paymentid
|
int
no 4
1
0 no (na)
va)
NULL
Programmebility
2
ReferaiD
int
no 4
1
0
yes
trv)
wo)
NULL
Service
Broker
3
NurselD
it
no 4
10
0
yes
trva)
wo)
NULL
Secony
4
NdiD
it
no 4
10
0
yes
trva)
wo)
NULL y
[Name
vp
oo
a
oe
The
name
of
the
connection.
< >
P
Type
here
to
search
fe)
=
create table Aides
(AidID integer,
FirstName varchar(20) not null,
LastName varchar(30) not null,
SSN varchar(11) not null,
Phone varchar(10) not null,
Address varchar(30) not null,
Zip varchar(5) not null,
Birthdate date not null,
AideRate decimal (10,2) not null,
AidStipEndRate decimal (10,2),
ReferralID integer,
PatientID integer,
InventoryID integer,
Availability varchar(200) not null,
constraint pk_aid primary key (AidID),
constraint fk_zip_aid foreign key (Zip) references ZipCodes,
constraint fk_patient_aid foreign key (PatientID) references Patients,
constraint fk_referral_aid foreign key (ReferralID) references Referrals)
I could not get the 3rd person’s information added for anything I kept getting
this error no matter how I changed the input and removed constraints and foreign
keys
Msg 8152, Level 16, State 14, Line 3
String or binary data would be truncated.
The statement has been terminated.
create table Contracts
(ContractID integer,
ReferralID integer not null,
PatientID integer not null,
PaymentID integer not null,
StartDate date not null,
EndDate date not null,
constraint pk_contract primary key (ContractID),
create table MedicareB
(MedicareB_ID integer,
PatientID integer not null,
ReferralID integer not null,
ContractID integer not null,
PaymentID integer not null,
NurseID integer not null,
AidID integer not null,
InventoryID integer,
DirectCostID integer not null,
IndirectCostID integer not null,
constraint pk_medicare primary key (MedicareB_ID),
create table Medicaid
(MedicaidID integer,
PatientID integer not null,
ReferralID integer not null,
ContractID integer not null,
PaymentID integer not null,
NurseID integer not null,
AidID integer not null,
DirectCostID integer not null,
IndirectCostID integer not null,
constraint pk_medicaid primary key (MedicaidID),
create table Private_Ins
(Priv_InsID integer,
PatientID integer not null,
ReferralID integer not null,
ContractID integer not null,
PaymentID integer not null,
NurseID integer not null,
AidID integer not null,
InventoryID integer,
constraint pk_private primary key(Priv_InsID),
constraint fk_patient_priv foreign key (PatientID) references Patients,
constraint fk_referral_priv foreign key (ReferralID) references Referrals,
constraint fk_contract_priv foreign key (ContractID) references Contracts,
constraint fk_payment_priv foreign key (PaymentID) references Payments,
constraint fk_nurse_priv foreign key (NurseID) references Nurses,
constraint fk_aid_priv foreign key (AidID) references Aides)
create table Private_Pay
(Priv_PayID integer,
PatientID integer not null,
ReferralID integer not null,
ContractID integer not null,
PaymentID integer not null,
NurseID integer not null,
AidID integer not null,
InventoryID integer,
constraint pk_private_pay primary key(Priv_PayID),
constraint fk_patient_priv_ins foreign key (PatientID) references Patients,
constraint fk_referral_priv_ins foreign key (ReferralID) references Referrals,
constraint fk_contract_priv_ins foreign key (ContractID) references Contracts,
constraint fk_payment_priv_ins foreign key (PaymentID) references Payments,
constraint fk_nurse_priv_ins foreign key (NurseID) references Nurses,
constraint fk_aid_priv_ins foreign key (AidID) references Aides)
create table Inventory
(InventoryID integer,
PatientID integer not null,
ContractID integer not null,
PaymentID integer not null,
NurseID integer not null,
AidID integer not null,
MedicareB_ID integer,
MedicaidID integer,
Private_PayID integer,
Priv_InsID integer,
constraint pk_inventory primary key(inventoryID),
constraint fk_patient_inventory foreign key (PatientID) references Patients,
constraint fk_contract_inventory foreign key (ContractID) references Contracts,
constraint fk_payment_inventory foreign key (PaymentID) references Payments,
constraint fk_nurse_inventory foreign key (NurseID) references Nurses,
constraint fk_aid_inventory foreign key (AidID) references Aides,
constraint fk_medicare_inventory foreign key (MedicareB_ID) references MedicareB,
constraint fk_medicaid_inventory foreign key (MedicaidID) references Medicaid,
constraint fk_priv_pay_inventory foreign key (Private_PayID) references
Private_Pay,
constraint fk_priv_ins_inventory foreign key (Priv_InsID) references Private_Ins
create table DirectCosts
(DirectCostID integer,
PatientID integer not null,
ContractID integer not null,
PaymentID integer not null,
NurseID integer not null,
AidID integer not null,
InventoryID integer,
MedicareB_ID integer,
MedicaidID integer,
constraint pk_direct primary key(DirectCostID),
constraint fk_patient_direct foreign key (PatientID) references Patients,
constraint fk_contract_direct foreign key (ContractID) references Contracts,
constraint fk_payment_direct foreign key (PaymentID) references Payments,
constraint fk_nurse_direct foreign key (NurseID) references Nurses,
constraint fk_aid_direct foreign key (AidID) references Aides,
constraint fk_medicare_direct foreign key (MedicareB_ID) references MedicareB,
constraint fk_medicaid_direct foreign key (MedicaidID) references Medicaid,
constraint fk_inventory_direct foreign key (InventoryID) references Inventory)
create table IndirectCosts
(IndirectCostID integer,
PatientID integer not null,
ContractID integer not null,
PaymentID integer not null,
NurseID integer not null,
AidID integer not null,
InventoryID integer,
MedicareB_ID integer,
MedicaidID integer,
constraint pk_indirect primary key(IndirectCostID),
constraint fk_patient_indirect foreign key (PatientID) references Patients,
constraint fk_contract_indirect foreign key (ContractID) references Contracts,
constraint fk_payment_indirect foreign key (PaymentID) references Payments,
constraint fk_nurse_indirect foreign key (NurseID) references Nurses,
constraint fk_aid_indirect foreign key (AidID) references Aides,
constraint fk_medicare_indirect foreign key (MedicareB_ID) references MedicareB,
constraint fk_medicaid_indirect foreign key (MedicaidID) references Medicaid,
constraint fk_inventory_indirect foreign key (InventoryID) references Inventory)
