Introduction The purpose of this assignment is to provide you with experience in
ID: 3605247 • Letter: I
Question
Introduction
The purpose of this assignment is to provide you with experience in converting ER diagrams into MySQL tables and populating them.
One patient may visit 1 or more doctors, and a doctor may have multiple patients.
Here are the steps to complete the assignment:
Convert the above ER diagram into a database design. This means that:
The relationship has to be resolved so that the 2 tables have a common field
Create an associative entity/intersection table to resolve the M:N relationship.
Create the tables in your cis202920_NN database on the webclass server. Use SQL statements or phpMyAdmin.
Populate the tables with data.
Download file containing doctor and patient information from BB as csv files. Use that to load into the tables.
Data for the Assignment
Contents of the DB-2_assignment_data_doctor_patient googlesheet file are as follows. You may copy them into an Excel spreadsheet if you wish: below is the link for the data.
https://drive.google.com/open?id=1hAGSlReLjYRYpac9fTCTI6rm58GZjNslUhTXSqo6cKE
Diagram 2
Shown below are patients, along with the doctors they visited.
Patient
Doctors visited and Date
James Randall
Mary Jones on 12/5/2010
Paula Fenton on 12/10/2010
John Skitch
Paula Fenton on 1/1/2011
Marla Going
Paula Fenton on 2/1/2011
Mary Jones on 3/1/2011
Hattie Bird
Mary Jones on 5/1/2012
Use this information to build the intersection tables. Create the intersection table's data in the excel or google sheet file and convert each worksheet into a csv file that can be imported into the MySQL table. If you wish to hand enter the data into the MySQL tables, that is OK!
Note on creating date fields in Excel to import into MySQL
Date fields must be placed in yyyy-mm-dd format in the csv file. For example, March 12, 1998 should be written as 1998-03-12. When entering the value in the spreadsheet, prefix it with the ' character to force it to be treated as a text field. This is shown below:
If the single quote is not placed before the data in the cell, the system will treat it as a date and display it using date formatting, which defaults to date being displayed in mm-dd-yy format, which MySQL cannot use, since it requires that date be specified in yyyy-mm-dd format.
Shown below is the csv file from the above sheet after selecting "File-Download as csv file". I used Notepad++ to open it
Submit Instructions
Submit via blackboard the following.
ER diagram with attributes and relationships for the problem.
Send in your database name.
Patient
Doctors visited and Date
James Randall
Mary Jones on 12/5/2010
Paula Fenton on 12/10/2010
John Skitch
Paula Fenton on 1/1/2011
Marla Going
Paula Fenton on 2/1/2011
Mary Jones on 3/1/2011
Hattie Bird
Mary Jones on 5/1/2012
Explanation / Answer
# creating database 'cis202920', if it does not exist
CREATE DATABASE IF NOT EXISTS cis202920;
# using database ‘cis202920’
USE cis202920;
# creating table ‘Patient’ with the given set of attributes. Declaring PatientID as Primary key.
CREATE TABLE Patient
(
PatientID INT NOT NULL PRIMARY KEY,
PatientName VARCHAR(50),
PatientAddress VARCHAR(50),
PatientCity VARCHAR(20),
PatientStateZip VARCHAR(20)
);
# creating table ‘Doctor’ with the given set of attributes. Declaring DoctorID as Primary key.
CREATE TABLE Doctor
(
DoctorID INT NOT NULL PRIMARY KEY,
DoctorName VARCHAR(50)
);
# creating table ‘Visit’ which stores the information about the Patient and Doctor visit status along with date of visiting Doctor by Patient. PatientID and DoctorID referred to Patient and Doctor Relation respectively.
CREATE TABLE Visit
(
AppointmentNum INT NOT NULL PRIMARY KEY,
PatientID INT NOT NULL,
DoctorID INT NOT NULL,
VisitDate DATE NOT NULL,
FOREIGN KEY (PatientID) REFERENCES Patient (PatientID),
FOREIGN KEY (DoctorID) REFERENCES Doctor (DoctorID)
);
# inserting given records to the Patient Table
INSERT INTO Patient VALUES (95, ‘James Randall’, ‘1145, Smith Lane’, ‘Skander’, ‘IL 55647’);
INSERT INTO Patient VALUES (203, ‘Jahn Skitch’, ‘334, Harriet St, #24’, ‘Rose City’, ‘CA 33546’);
INSERT INTO Patient VALUES (678, ‘Marla Going’, ‘#12, Pandora lane, ‘Pulvertown’, ‘CA 33546’);
INSERT INTO Patient VALUES (1029, ‘Hattie Bird’, ‘6734 S56 on Glifford’, ‘Randallton’, ‘CA 56674’);
# inserting records to the Doctor table
INSERT INTO Doctor VALUES (96, ‘Mary Jones’);
INSERT INTO Doctor (102, ‘Paula Felton’);
INSERT INTO Doctor (110, ‘Gerald Byte’);
# inserting record to the visit table
INSERT INTO Visit VALUES (23, 95, 96, ‘2010-05-12’);
INSERT INTO Visit VALUES (32, 95, 102, ‘2010-10-12’);
INSERT INTO Visit VALUES (42, 203, 102, ‘2011-01-01’);
INSERT INTO Visit VALUES (45, 678, 102, ‘2011-01-02’);
INSERT INTO Visit VALUES (50, 678, 96, ‘2011-01-03’);
INSERT INTO Visit VALUES (53, 1029, 96, ‘2012-01-05’);
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.