I am having trouble understand creating mysql code for this E-R model. I know I

Question

I am having trouble understand creating mysql code for this E-R model. I know I should have foreign keys, but not sure where I should have them. The relations are confusing me also the Assign and Treat. Do I need to create a table for them? Also IP and IP are confusing me with patient should I make them seperate tables or in Patient. I have included the Exel sheet also showing data I should input. I would like someone to create code so I can see what I missed from my code ,but any help is better then none.

Equipment Start time Name Finish time Pager number Capability Room ID Exam ID MD ID Duration nesthesia IR room Assign Exam Treat MD Phone number Specialty Working address Patient ID Name Patient Gender Adress 0 eduled Bay areaIP OP xam time

Explanation / Answer

I've gone through the above ERD as well as the excel sheet.

You can very well create the table from the excel sheet without any modification.Only on table EX_AM you've to create foreign key, which refers the primary key of both MD and patient table.

Further, there is no issues with IP, OP it just defines whether patient staying in hospital or moving patient, so it does not going to affect anything with the table.

Table IR_Room:

create table IR_Room(

RoomID varchar(5),

Equipment varchar(25),

Capability varchar(25)

)

Table MD:

create table MD(

MD_ID      INT NOT NULL AUTO_INCREMENT,

NAME      VARCHAR(100) NOT NULL,

PAGER_NUMBER INT NOT NULL,

PHONE_NUMBER INT NOT NULL,

SPECIALITY VARCHAR(100) NOT NULL,

WORKING ADDRESS VARCHAR(100) NOT NULL,

PRIMARY KEY(MD_ID)

);

Table Patient:

create table Patient(

PatientID INT NOT NULL AUTO_INCREMENT,

Gender VARCHAR(6) NOT NULL,

Address VARCHAR(40) NOT NULL,

IP_OP CHAR(2) NOT NULL,

BAY_AREA INT NOT NULL,

SCHEDULED_EXAM_TIME TIME,

);

TABLE EXAM:

create table EX_AM(

Ex_am_ID INT NOT NULL,

Patient_ID INT NOT NULL,

MD_ID INT NOT NULL,

Start_Time TIME,

Finish_TIME TIME,

Anesthesia CHAR(1),

FOREIGN KEY(PatientID) references Patient(PatientID),

FOREIGN KEY(MD_ID) references MD(MD_ID)

);

So all the above tables are created along with FOREIGN KEY and PRIMARY KEY, now you can insert data into those tables using the insert Command

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.