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;`alt text`

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

asked Jul 26, 2014 at 10:58 PM in Default

avatar image

andybr
1 1 3

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, 2014 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, 2014 at 07:22 PM andybr

could you please check my Diagrams above ?

thanks

Jul 30, 2014 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, 2014 at 09:36 PM

avatar image

virtualjosh
311 5 5

(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:

x987

asked: Jul 26, 2014 at 10:58 PM

Seen: 416 times

Last Updated: Jul 30, 2014 at 07:25 PM

Copyright 2016 Redgate Software. Privacy Policy