Question

Given the following requirements: we need to construct a database that manages the hospital data. The hospital contains four departments (Department of Internal Medicine, Orthopedic department, pediatric department, surgery department), each department has several doctors; each department receives and treats several patients. Each doctor has an ID number (unique); Full Name; Gender; Department ID number, each patient has an ID number (unique); Full Name; Gender; Department ID number; Condition (admitted or not), The doctor in charge; the described medications (serial number of the medicine ) where the patients can take more than one medicine. Each patient is treated by one doctor. The hospital medicine storages have several medicines where each has a serial number, type, and name. This is a group project. Therefore, constitute groups of three students each, and do the following: 1- Produce the ER diagram the represents these requirements. (3 marks) 2- Map the ER that you produce into a relational schema. (3 marks) 3- Design the database tables. For each column in a table, identify its name, data type, size, Null allowed or not, primary key or not, check constraints, unique constraint, and foreign key constraints. (3 marks) 4- Implement the database including all tables and constraints using Sqlserver, and then input enough sample data for each table. (6 marks) 5- Be ready for presentation and discussion of your work through Microsoft teams. During the discussion, you may sked to write SQL queries to retrieve or manipulate data. (5 marks)

Answer 1

From the given above information we are going to learn about the following topics: 1. ER diagram.    2. SQL. 3. how to retrieve and manuplate data.

1. So first let us see about ER diagram based on the given above data. sol: ER is defined as Entity Relational diagram, which is used to transform data into simple designed database.it is also easily understandable about the data by using the diagram. So for the real world ER diagrams plays a crucial role in database. ER some what looks like flow chart and its includes of entities, attributes, relationships which helps in clear understanding.

ER diagram includes three basic parts: 1. entity 2. attributes. 3.relationships.

1. Entity is defined as the main thing which explains about the most important topics in the data. 2. Attributes are defined as the sub parts which helps in explaining about the entities. 3. Relationship is used to connect the data with there functioning.

entities are explained in top of the box; attributes are explained in that box as sub part and relationship is explained with diamond shape.   

Now let us see the how will be the ER diagram from the given above data

.

so from the above diagram Entities are: Doctor, medicine, patient.

   attributes are for Doctor: ID, Full name, Gender Medicine: Serial No, Type, Name Patient: Medicine Serial number, Doctor, Department Id, Admitted/not.

  Relationship is From Doctor to Patient One-to-one so it is symbolized as single line   For Patient to medicine One-to-Many So it is symbolized as Single line and line with side triangle.

2. now let us see Tables in sql :

sol: SQL is defined as structured query language. which is used to store data easily by using tables.

so now from the given information we need to create three tables:(i will data in tables as example.)

for table1: name it as Doctors.

CREATE TABLE Doctors (Doctor_ID INT NOTNULL PRIMARY KEY, Name VARCHAR, Gender VARCHAR);

INSERT INTO Doctors (Doctor_ID INT NOTNULL PRIMARY KEY, Name VARCHAR, Gender VARCHAR) values (111, xxx, male);   INSERT INTO Doctors (Doctor_ID INT NOTNULL PRIMARY KEY, Name VARCHAR, Gender VARCHAR) values (222, yyy, male); INSERT INTO Doctors (Doctor_ID INT NOTNULL PRIMARY KEY, Name VARCHAR, Gender VARCHAR) values (333, zzz, female);

doctors table is created . now let us see about patient table.

Table 2; name it as Patient.

CREATE TABLE Patient (Doctor_Name VARCHAR, MedicineSerial_no INT, DepartmentID INT, Admitted VARCHAR);

INSERT INTO Patient (Doctor_Name VARCHAR, MedicineSerial_no INT, DepartmentID INT, Admitted VARCHAR) values (aaa, 12112, 0001, YES); INSERT INTO Patient (Doctor_Name VARCHAR, MedicineSerial_no INT, DepartmentID INT, Admitted VARCHAR) values (bbb, 12132, 0002, YES);    INSERT INTO Patient (Doctor_Name VARCHAR, MedicineSerial_no INT, DepartmentID INT, Admitted VARCHAR)   values (ccc, 12112, 0002, YES);

patient table is created; now let us see about medicine table.

Table 3: name it as medicine:

CREATE TABLE Medicine (Medicine_type VARCHAR, MedicineSerial_no INT, Medicine_NameVARCHAR);

INSERT INTO Medicine (Medicine_type VARCHAR, MedicineSerial_no INT, Medicine_NameVARCHAR)   values (pediatrics, 12112, adcdef ); INSERT INTO Medicine (Medicine_type VARCHAR, MedicineSerial_no INT, Medicine_NameVARCHAR) values (gynecology, 12132, sdefr); INSERT INTO Medicine (Medicine_type VARCHAR, MedicineSerial_no INT, Medicine_NameVARCHAR)       values (orthopedics, 12114, swedrft);

medicine table is created.

now table4: let name the table as Internal_Medicine_Departments.

CREATE TABLE Internal_Medicine_Departments (Patient_Name VARCHAR, Doctor_Name VARCHAR, Medicine_Serial_no INT);

INSERT INTO Internal_Medicine_Departments (Patient_Name VARCHAR, Doctor_Name VARCHAR, Medicine_Serial_no INT)

values (somex, somea, 67934 ) INSERT INTO Internal_Medicine_Departments (Patient_Name VARCHAR, Doctor_Name VARCHAR, Medicine_Serial_no INT) values (somey, someb, 75684)  INSERT INTO Internal_Medicine_Departments (Patient Name VARCHAR, Doctor_Name VARCHAR, Medicine_Serial_no INT)       values (somez, somec, 12114);

now table5: let name the table as Orthopedic_Departments.

CREATE TABLE Orthopedic_Departments (Patient_Name VARCHAR, Doctor_Name VARCHAR, Medicine_Serial_no INT);

INSERT INTO Orthopedic_Departments (Patient_Name VARCHAR, Doctor_Name VARCHAR, Medicine_Serial_no INT)

values (joshey, manesey, 334455 ) INSERT INTO Internal_Medicine_Departments (Patient_Name VARCHAR, Doctor_Name VARCHAR, Medicine_Serial_no INT) values (messey, masasae, 44848)  INSERT INTO Orthopedic_Departments (Patient Name VARCHAR, Doctor_Name VARCHAR, Medicine_Serial_no INT)       values (psped, soede, 22002);

now table6: let name the table as Pediatric_Departments.

CREATE TABLE Pediatric_Departments (Patient_Name VARCHAR, Doctor_Name VARCHAR, Medicine_Serial_no INT);

INSERT INTO Pediatric_Departments (Patient_Name VARCHAR, Doctor_Name VARCHAR, Medicine_Serial_no INT)

values (sohey, daney, 334455 ) INSERT INTO Pediatric_Departments (Patient_Name VARCHAR, Doctor_Name VARCHAR, Medicine_Serial_no INT) values (dera, kara, 44748)  INSERT INTO Pediatric_Departments (Patient Name VARCHAR, Doctor_Name VARCHAR, Medicine_Serial_no INT)       values (qwerd, pede, 22102);

now table6: let name the table as Surgery_Departments.

CREATE TABLE Surgery_Departments (Patient_Name VARCHAR, Doctor_Name VARCHAR, Medicine_Serial_no INT);

INSERT INTO Surgery_Departments (Patient_Name VARCHAR, Doctor_Name VARCHAR, Medicine_Serial_no INT)

values (piney, laene, 341455 ) INSERT INTO Surgery_Departments (Patient_Name VARCHAR, Doctor_Name VARCHAR, Medicine_Serial_no INT) values (raiey, sohey, 46848)  INSERT INTO Surgery_Departments (Patient Name VARCHAR, Doctor_Name VARCHAR, Medicine_Serial_no INT)       values (maery, dore, 92002);

3. Now let us know about how to retrieve data.

sol: Retrieving data means selecting from part of the data from particular database. to retrieve data SELECT statement is used.

so from any of the above table lets consider medicine table if you want to retrieve data of medicine name for medicine serial no OF 12132 then the syntax will be like

SELECT Medicine_Name, MedicineSerial_no FROM Medicine:

so by using this command you can retrieve the data all medicine serial numbers and medicine names from medicine table.

hope you got all of your answers. and please rate it