How to fix an SQL design

Question

I'm doing the study of a medical software. This software asks the patients questions about their symptoms and from them it can determine the possible pathologies. My study involves comparing the symptoms and pathologies found by the software with those from the hospital.

In order to make my work easier, I decided to enter the data in a database made with javadb on netbeans 8.2.

But it seems like that I did something wrong since my statement doesn't work.

I thank you in advance anybody who would take the time to help me.

SQL design:

Create table Patients(
nip varchar(32) primary key,
sexe varchar(8) not null,
age int not null,
dateArrivee timestamp not null,
constraint ck_sexe check(sexe='Male' or sexe='Female'),
constraint ck_age check (age>=0)
);

Create table Symptoms(
symptomID int primary key generated always as identity (start with 1,                         
increment by 1),
nip varchar(32) not null,
symptom varchar(64),
origin varchar(16) not null,
foreign key (nip) references Patients(nip),
constraint ck_origin check (origin='SOFTWARE' or origin='HOSPITAL')
);

Create table Pathologies(
pathologyID int primary key generated always as identity (start with 1,     
increment by 1),
nip varchar(32) not null,
pathology varchar(64),
origin varchar(16) not null,
foreign key (nip) references Patients(nip),
constraint ck_origin check (origin='SOFTWARE' or origin='HOSPITAL')
);

Values entered:

Insert into Patients values ('001','Male', 25, '2019-05-27 14:00:00');

Insert into Patients values ('002', 'Female', 30, '2019-05-26 15:00:00');

Insert into Symptoms values (, '001', 'Headache', 'SOFTWARE');

Insert into Pathologies values (,'001', 'Fever', 'SOFTWARE');

Insert into Symptoms values (,'001', 'Stomache', 'HOSTPITAL');

Insert into Pathologies values (, '001', 'Gastro-enteritis', 'HOSPITAL');

Insert into Symptoms values(,'002', 'Headache', 'SOFTWARE');

Insert into Pathologies values (,'002', 'Unknow', 'SOFTWARE');

SQL statement:

Select * 
from (Patients inner join
      Symptoms
      on Patients.nip = Symptoms.nip
     ) inner join
     Pathologies
    on Symptoms.nip = Pathologies.nip
 where Symptoms.origin = 'MEDVIR' and 
       Pathologies.origin = 'MEDVIR';

So sorry I forgot to put the errors I'm getting.

SQL design:

First I have an error concerning the auto_incrementation, even thought this was the good method. It says that the syntax is incorrect near the 'generated'.

Values entered:

Here I have an error concerning the a wrong syntax near the coma (',').

SQL statement:

Lastly I have an error saying that the object 'Patients' is unavaible.

You have many statements in your question. Which one doesn't work? Also, explain what "doesn't work" really means. If you get an error, include the error. — Gordon Linoff, May 27 '19 at 12:42
`Insert into Symptoms values (, '001', 'Headache', 'SOFTWARE');` This looks like bad syntax; remove that leading comma. Applies to all the rules this affects. — Martin, May 27 '19 at 12:46
It looks like a very bad approach to have a Primary Key as a VARCHAR column. Why do you not have a numerical ID primary key? — Martin, May 27 '19 at 12:48
@Martin I didn't know that, thanks I'll make the change as soon as possible. — Uriel, May 27 '19 at 13:42
Please in code questions give a [mcve]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) Read an introduction/manual re the parts of the language you use before you use them & before you ask a question re how you used them. — philipxy, May 27 '19 at 20:42
(Obviously--) These errors will be faqs. Before considering posting please always google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names; read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. PS Please clarify via edits, not comments — philipxy, May 27 '19 at 20:42

score 0 · Answer 1 · answered May 27 '19 at 12:56

0

If I am not wrong, you are trying to fetch entries where 'Origin' = 'MEDVIR' Although, none of your insert statements have Origin as 'MEDVIR'

Please check below,

Select * 
from (Patients inner join
      Symptoms
      on Patients.nip = Symptoms.nip
     ) inner join
     Pathologies
    on Symptoms.nip = Pathologies.nip
 where Symptoms.origin IN ('SOFTWARE', 'HOSPITAL') and 
       Pathologies.origin IN ('SOFTWARE', 'HOSPITAL');

Also, some of your INSERT statement has an extra comma before the values, which would cause a syntax error.

answered May 27 '19 at 12:56

Aagam Shah

41
5

Insert into Symptoms values ('001', 'Headache', 'SOFTWARE'); Insert into Pathologies values ('001', 'Fever', 'SOFTWARE'); Insert into Symptoms values ('001', 'Stomache', 'HOSTPITAL'); Insert into Pathologies values ('001', 'Gastro-enteritis', 'HOSPITAL'); Insert into Symptoms values('002', 'Headache', 'SOFTWARE'); Insert into Pathologies values ('002', 'Unknow', 'SOFTWARE'); Please use these Insert statements. – Aagam Shah May 27 '19 at 13:01
Understood I just made the change. I'll try to see what I can do concerning the SQL design. – Uriel May 27 '19 at 13:24

How to fix an SQL design

1 Answers1