University Nursing Informatics: Database Assignment Solution

This assignment solution focuses on database design and implementation within the context of Nursing Informatics. The document provides SQL commands for creating database tables, including `admininstrator`, `alert`, `diagnosis`, `disease`, `medicine`, `patient`, `prescribed_dose`, and `staffs`. It includes the table structures, data insertion scripts, and primary/foreign key constraints. The solution also addresses the addition of a 'Deleted_Date' column to the `prescribed_dose` and `alert` tables to facilitate data management and deletion tracking. Finally, the solution includes a bibliography of relevant database systems and management resources.

Running head: NURSING INFROMATICS
Nursing Informatics
Name of the Student:
Name of the University:
Author Note

1
NURSING INFROMATICS
Answer to question number 1
The sql commands for the development of the tables and the population of data is provided
below:
-- Table structure for table `admininstrator`
CREATE TABLE `admininstrator` (
`AdminID` int(11) NOT NULL,
`AdminName` varchar(20) NOT NULL,
`AdminAddress` varchar(30) NOT NULL
) ;
-- Dumping data for table `admininstrator`
INSERT INTO `admininstrator` (`AdminID`, `AdminName`, `AdminAddress`) VALUES
(101, 'Jhon', '42 silver street'),
(102, 'Kevin', '40 Silver Spring Road'),
(103, 'Philip', '39 Lewis Road'),
(104, 'James', '44 Hudson Street');
-- Table structure for table `alert`

2
NURSING INFROMATICS
CREATE TABLE `alert` (
`AlertID` int(11) NOT NULL,
`DoseID` int(11) NOT NULL,
`StaffID` int(11) NOT NULL,
`Intervals` time NOT NULL
);
-- Dumping data for table `alert`
INSERT INTO `alert` (`AlertID`, `DoseID`, `StaffID`, `Intervals`) VALUES
(1, 1, 1, '00:15:00'),
(2, 2, 2, '00:20:00'),
(3, 3, 3, '00:10:00'),
(4, 4, 4, '00:20:00');
-- Table structure for table `diagnosis`
CREATE TABLE `diagnosis` (
`DiagnosisID` int(11) NOT NULL,
`StaffID` int(11) NOT NULL,
`DiseaseID` int(11) NOT NULL,

3
NURSING INFROMATICS
`PatientID` int(11) NOT NULL
);
-- Dumping data for table `diagnosis`
INSERT INTO `diagnosis` (`DiagnosisID`, `StaffID`, `DiseaseID`, `PatientID`) VALUES
(1, 1, 1, 1),
(2, 2, 2, 2),
(3, 3, 3, 3),
(4, 4, 4, 4);
-- Table structure for table `disease`
CREATE TABLE `disease` (
`DiseaseID` int(11) NOT NULL,
`DiseaseName` varchar(20) NOT NULL
);
-- Dumping data for table `disease`
INSERT INTO `disease` (`DiseaseID`, `DiseaseName`) VALUES
(1, 'Tuberculosis'),
(2, 'Pneumonia'),

4
NURSING INFROMATICS
(3, 'Cancer'),
(4, 'Jaundice');
-- Table structure for table `medicine`
CREATE TABLE `medicine` (
`MedID` int(11) NOT NULL,
`MedName` varchar(20) NOT NULL,
`Composisition` varchar(30) NOT NULL
);
-- Dumping data for table `medicine`
INSERT INTO `medicine` (`MedID`, `MedName`, `Composisition`) VALUES
(1, 'Pyrazinamide', 'C5H5N3O'),
(2, 'Levofloxacin', 'C18H20FN3O4'),
(3, 'Lipegfilgrastim', 'C27H46N4O19'),
(4, 'tylenol', 'C8H9NO2');
-- Table structure for table `patient`
CREATE TABLE `patient` (

5
NURSING INFROMATICS
`PatientID` int(11) NOT NULL,
`PateintName` varchar(30) NOT NULL,
`Age` int(11) NOT NULL
);
-- Dumping data for table `patient`
INSERT INTO `patient` (`PatientID`, `PateintName`, `Age`) VALUES
(1, 'Jamei', 12),
(2, 'Kelly', 21),
(3, 'Becky', 19),
(4, 'David', 23);
-- Table structure for table `prescribed_dose`
CREATE TABLE `prescribed_dose` (
`DoseID` int(11) NOT NULL,
`DiagnosisID` int(11) NOT NULL,
`MedID` int(11) NOT NULL,
`PatientID` int(11) NOT NULL,
`AdminID` int(11) NOT NULL,

6
NURSING INFROMATICS
`DoseDetails` int(11) NOT NULL
);
-- Dumping data for table `prescribed_dose`
INSERT INTO `prescribed_dose` (`DoseID`, `DiagnosisID`, `MedID`, `PatientID`, `AdminID`,
`DoseDetails`) VALUES
(1, 1, 1, 2, 101, 3),
(2, 2, 2, 2, 102, 4),
(3, 3, 3, 3, 103, 2),
(4, 4, 4, 4, 104, 5);
-- Table structure for table `staffs`
CREATE TABLE `staffs` (
`StaffID` int(11) NOT NULL,
`StaffName` varchar(20) NOT NULL,
`StaffAddress` varchar(30) NOT NULL,
`Contact` int(11) NOT NULL,
`Fax` int(11) NOT NULL,
`Email` varchar(30) NOT NULL
);

7
NURSING INFROMATICS
-- Dumping data for table `staffs`
INSERT INTO `staffs` (`StaffID`, `StaffName`, `StaffAddress`, `Contact`, `Fax`, `Email`)
VALUES
(1, 'Helms', '45 B James Street ', 45673829, 4567, '[e-mail]'),
(2, 'Gregory', '11 J Street', 45678902, 3456, '[e-mail]'),
(3, 'Harry', '10 jeremy Street', 45676109, 2757, '[e-mail]'),
(4, 'Peter', '44 Jump Street end', 45017364, 3109, '[e-mail]');
ALTER TABLE `admininstrator`
ADD PRIMARY KEY (`AdminID`);
ALTER TABLE `alert`
ADD PRIMARY KEY (`AlertID`),
ADD KEY `DoseID` (`DoseID`),
ADD KEY `StaffID` (`StaffID`);
ALTER TABLE `diagnosis`
ADD PRIMARY KEY (`DiagnosisID`),
ADD KEY `DiseaseID` (`DiseaseID`),
ADD KEY `StaffID` (`StaffID`),
ADD KEY `PatientID` (`PatientID`);

8
NURSING INFROMATICS
ALTER TABLE `disease`
ADD PRIMARY KEY (`DiseaseID`);
ALTER TABLE `medicine`
ADD PRIMARY KEY (`MedID`);
ALTER TABLE `patient`
ADD PRIMARY KEY (`PatientID`);
ALTER TABLE `prescribed_dose`
ADD PRIMARY KEY (`DoseID`),
ADD KEY `DiagnosisID` (`DiagnosisID`),
ADD KEY `AdminID` (`AdminID`),
ADD KEY `MedID` (`MedID`),
ADD KEY `PatientID` (`PatientID`);
ALTER TABLE `staffs`
ADD PRIMARY KEY (`StaffID`);
ALTER TABLE `alert`
ADD CONSTRAINT `alert_ibfk_1` FOREIGN KEY (`DoseID`) REFERENCES
`prescribed_dose` (`DoseID`),
ADD CONSTRAINT `alert_ibfk_2` FOREIGN KEY (`StaffID`) REFERENCES `staffs`
(`StaffID`);

9
NURSING INFROMATICS
ALTER TABLE `diagnosis`
ADD CONSTRAINT `diagnosis_ibfk_1` FOREIGN KEY (`DiseaseID`) REFERENCES
`disease` (`DiseaseID`),
ADD CONSTRAINT `diagnosis_ibfk_2` FOREIGN KEY (`StaffID`) REFERENCES `staffs`
(`StaffID`),
ADD CONSTRAINT `diagnosis_ibfk_3` FOREIGN KEY (`PatientID`) REFERENCES
`patient` (`PatientID`);
ALTER TABLE `prescribed_dose`
ADD CONSTRAINT `prescribed_dose_ibfk_1` FOREIGN KEY (`DiagnosisID`)
REFERENCES `diagnosis` (`DiagnosisID`),
ADD CONSTRAINT `prescribed_dose_ibfk_2` FOREIGN KEY (`AdminID`) REFERENCES
`admininstrator` (`AdminID`),
ADD CONSTRAINT `prescribed_dose_ibfk_3` FOREIGN KEY (`MedID`) REFERENCES
`medicine` (`MedID`),
ADD CONSTRAINT `prescribed_dose_ibfk_4` FOREIGN KEY (`PatientID`) REFERENCES
`patient` (`PatientID`);
Answer to question number 2
Inserting deleted data field in the tables:

10
NURSING INFROMATICS
ALTER TABLE `prescribed_dose` ADD `Deleted_Date` DATE NOT NULL AFTER
`DoseDetails`;
ALTER TABLE `alert` ADD `Deleted_Date` DATE NOT NULL AFTER `Intervals`;
The deleted date field is updated in the tables which would be helpful for retrieving that
are deleted from the table. The field is updated in the alert table as all the other tables have data
which would be referring to the other tables in the database. However, the alert table does not
consist of a table that it is referring to and hence, the table is updated.
The delete statement is generally used for removing the rows from any type of table in a
database. It cannot clear the referenced column though as this clears the data for the entire
column. When an additional column is added for making the delete button that would be helpful
for deleting the data that is required for the removing any type of data from the database. This is
tradeoff so that the marker-column can be used for deletion of a row from the table. Hence, the
focus on certain rows can be done which is to be deleted.

11
NURSING INFROMATICS
Bibliography
Coronel, C., & Morris, S. (2016). Database systems: design, implementation, & management.
Cengage Learning.
Ma, L., Van Aken, D., Hefny, A., Mezerhane, G., Pavlo, A., & Gordon, G. J. (2018, May).
Query-based workload forecasting for self-driving database management systems. In
Proceedings of the 2018 International Conference on Management of Data (pp. 631-645).
ACM.