Question

Typically, a hospital patient receives medications that have been ordered by a particular doctor. Because the patient often receives several medications per day, there is a 1:M relationship between PATIENT and ORDER. Similarly, each order can include several medications, creating a 1:M relationship between ORDER and MEDICATION.

REQUIREMENTS:

A. Screenshot of the ERD diagrams showing the PK and FK

B. SQL commands used.

C. Screenshot of the Table structure

Answer 1

A. Screenshot of the ERD diagrams showing the PK and FK

PATIENT(Pid, Pname, Paddress,Pphone)

Attribute:-Pid, Pname, Paddress,Pphone

Primary Key:- Pid

ORDER(Oid, Oname, Oaddress,Pid)

Attribute:-Oid, Oname, Oaddress,Pid

Primary Key:- Oid

Foreign Key:- Pid

MEDICATION(Mid, Mname,Mtype)

Attribute:-Mid, Mname,Mtype

Primary Key:- Mid

B. SQL commands used

1.CREATE TABLE PATIENT ( Pid int AUTO_INCREMENT, Pname varchar(255) NOT NULL, Paddress varchar(255), Pphone int, PRIMARY KEY (Pid) )

1. CREATE TABLE PATIENT ( Pid int AUTO_INCREMENT, Pname varchar(255) NOT NULL, Paddress varchar(255), Pphone int, PRIMARY KEY (Pid) )

2. CREATE TABLE Orders ( Oid int AUTO_INCREMENT, Oname varchar(20) NOT NULL, Oaddress varchar(50) NOT NULL, Pid int, PRIMARY KEY (Oid), FOREIGN KEY (Pid) REFERENCES patient(Pid) )

3. CREATE TABLE MEDICATION ( Mid int AUTO_INCREMENT, Mname varchar(20), Mtype varchar(10), PRIMARY KEY (Mid) )

C. Screenshot of the Table structure

PATIENT Table Structure

ORDER Table Structure

MEDICATION Table Structure