x

relationships between tables, and diagrams

alt textenter code hereenter code heremy question is ,, how to build a diagram, I don't understand that many to many , or one to many and about primary key and foreign key and etc..

here is my code. is about a hospital where i have ,

NURSES, WARD, PATIENTS, DOCTORS.

CREATE TABLE Nurses( idNurses INT NOT NULL AUTO_INCREMENT, Fname VARCHAR(45) NULL, Lname VARCHAR(45) NULL, Speciality VARCHAR(45) NULL, PRIMARY KEY (idNurses)) ENGINE = InnoDB;

CREATE TABLE Doctors ( idDoctors INT NOT NULL AUTO_INCREMENT, Fname VARCHAR(45) NULL, Lname VARCHAR(45) NULL, Speciality VARCHAR(45) NULL, PRIMARY KEY (idDoctors)) ENGINE = InnoDB;

CREATE TABLE Ward( idWard INT NOT NULL AUTO_INCREMENT, Name VARCHAR(45) NULL, Location VARCHAR(45) NULL, Nurses_idNurses INT NOT NULL, Doctors_idDoctors INT NOT NULL, PRIMARY KEY (idWard, Nurses_idNurses, Doctors_idDoctors), INDEX fk_Ward_Nurses_idx (Nurses_idNurses ASC), INDEX fk_Ward_Doctors1_idx (Doctors_idDoctors ASC), CONSTRAINT fk_Ward_Nurses FOREIGN KEY (Nurses_idNurses) REFERENCES mydb.Nurses (idNurses) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_Ward_Doctors1 FOREIGN KEY (Doctors_idDoctors) REFERENCES mydb.Doctors (idDoctors) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

CREATE TABLE Patient ( idPatient INT NOT NULL AUTO_INCREMENT, Fname VARCHAR(45) NULL, Lname VARCHAR(45) NULL, DOB DATE NULL, Phone_number INT NULL, Ward_idWard INT NOT NULL, Ward_Nurses_idNurses INT NOT NULL, Ward_Doctors_idDoctors INT NOT NULL, PRIMARY KEY (idPatient, Ward_idWard, Ward_Nurses_idNurses, Ward_Doctors_idDoctors), INDEX fk_Patient_Ward1_idx (Ward_idWard ASC, Ward_Nurses_idNurses ASC, Ward_Doctors_idDoctors ASC), CONSTRAINT fk_Patient_Ward1 FOREIGN KEY (Ward_idWard , Ward_Nurses_idNurses , Ward_Doctors_idDoctors) REFERENCES mydb.Ward (idWard , Nurses_idNurses , Doctors_idDoctors) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;<code>alt text</code>

myn.png (41.2 kB)
myn1.png (18.9 kB)
more ▼

asked Jul 26 at 10:58 PM in Default

andybr gravatar image

andybr
1 2

So, this is your homework. What specifically are you tasked with doing with it? What point are you stuck on? @VirtualJosh gave you some good pointers on normalization, but is that your issue, or does it lie elsewhere? Please add a comment here in order to get better assistance.
Jul 29 at 11:53 AM Grant Fritchey ♦♦

okay this not home work I'm just trying to learn a bit more.. In a hospital, inpatients are assigned to a single ward. This ward can contain more than one patient. Inpatients are also assigned to a doctor. They are usually treated by one doctor, but can if applicable be treated by more than one. Nurses are assigned to a ward, a nurse can be assigned to only one ward, but a ward can have many nurses working in it. that's what I'm trying to do.

Jul 30 at 07:22 PM andybr

could you please check my Diagrams above ?

thanks
Jul 30 at 07:25 PM andybr
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

If you "*don't understand that many to many, or one to many and about primary key and foreign key and etc*"; then you should read all you can find on "Data Normalization" and the Normal forms. It is a basic concept in Relational Databases. You won't get good at this line of work if you lack such basic concepts. Here are a couple of articles that will get you started.

3 Normal Forms Database Tutorial\

http://phlonx.com/resources/nf3/

SQL and the JOIN Operator

http://www.sqlservercentral.com/articles/T-SQL/67941/

more ▼

answered Jul 28 at 09:36 PM

virtualjosh gravatar image

virtualjosh
311 1 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x742

asked: Jul 26 at 10:58 PM

Seen: 204 times

Last Updated: Jul 30 at 07:25 PM