Database And DBMS Are Crucial For Managing Data Effectively, Facilitating Efficient Information Retrieval And Storage.

1) Describe a database.

2) Describe a database management system with examples for commercial database management systems.

3) What is a relational database.

4) What are the advantages of using a database management system over the conventional way of storing structured data.

5) Describe the importance of ER diagrams in the database design process.

6) What is a database anormally. Describe with examples.

7) Describe 1NF, 2NF and 3NF in your own words.

8) Consider the following scenario

What is a database?

Answer of Question 1:

Collecting information in an organized way is generally known as database. In a database, the data can be easily accessed, updated, and managed. The data that are put in database are put in rows, tables, and columns and the data are indexed so that the accessing is easier to find the relevant information. The data that are stored in the database can be deleted, expanded, and updated when new information are added. The workloads are created and can also be updated by the processes involved in database and querying of data is also possible with the data in the database.

DBMS commonly known as Data Base Management System is a system software used for managing and creating a database. Programmers and users are provided by DBMS in a scheduled way to create the project, retrieve and update the data and also manage the data.

The end users gets the ability to delete, creates, update, and read data in database using DBMS. DBMS works as an interface in between the end user and the database or in between the application program so that the data can be organized consistently and can also be accessed easily. Mainly three important things are managed by DBMS. They are: data, the engine of database which helps in data accessible and database schema, in which logical structure of database is defined.

A scientist E.F.Codd in IBM first invented relational database in the year 1970. Relational database is mainly collection of data that is accessed or reassembled in different ways without organizing the table sin the database. The interface between the application program and the standard user is the Structured Query Language (SQL). Statements that are involved in SQL have interactive queries from the relational database and gather the data for the reports. Set of tables, which has the data fit in the predefined categories of the database, is the relational database. Example: for data entry of a business, a table describes a customer with all the columns for customer name, customer address, phone number and many more details.

The advantages of database DBMS over conventional storing system of data are:

• The flexibility of DBMS is much more than the convectional way of database. The programs and the data that are independent on each other and there is no need of modification of the program when different kinds of unrelated data are generally added or is deleted from database. The physical storage does not change with the alteration of data.
• The DBMS has a fast response to the information request that is given by the user where as in the conventional process, the response to any request is generally slow.
• DBMS can have multiple access but the conventional method of data storing does not support multiple access. The software of database allows the data to be accessed in various ways.
• The training cost of DBMS is comparatively less than giving training to users who are involved in conventional method.

The importance of Entity relationship diagrams are much more better than any other diagram because:

• Provides Visual representation of design that is being done
• ERD make the communication effective as it states all the entities and attributes involved in the design.
• The ERD is a very simple diagram and it is very easy to understand even if the user is not trained at al.

The issues that arise in the database are generally known as the database anomaly, which mainly occurs because of storing the data in flat database and poor planning. This anomaly of the database can be removed by normalization process, which is splitting the tables into simplified form.

What is a database management system and its advantages?

1NF (First Normal Form)- I First Normal Form, the columns in a table does not have multiple value. It will only have anatomic value.

2NF (Second Normal Form)- A table is considered to be in 2NF if:

• The table should be in 1NF.
• All the non-prime attributes should be dependent on proper subset on the candidate key of the table.

3NF (Third Normal Form)- Third Normal Form has to satisfy the following conditions:

• Table should be in 2NF.
• If there is transitive functional dependency of the non-prime attributes on super key, that should be removed.

• Entity Relationship Diagram of a hospital

Figure: ERD Diagram for the hospital

• 
  ERD shows the relationships, primary key, and foreign key in 1NF. This ERD is in 2NF

Figure: ERD in 1NF

• ERD after removing the anomalies and normalizing the ERD in 3NF

Figure: 3NF Diagram of the hospital removing the anomalies

DOCTOR

SQL>CREATE TABLE DOCTOR(D_ID NUMBER(3) PRIMARY KEY, D_NAME VARCHAR(30) NOT NULL, D_CONTACT_NO NUMBER(10), D_TYPE VARCHAR(10), D_HOURS NUMBER, SPECIALIZATION VARCHAR(20));

PATIENT

SQL> CREATE TABLE PATIENT(P_ID NUMBER(5) PRIMARY KEY, P_NAME VARCHAR(30) NOT NULL,GENDER VARCHAR(1) NOT NULL,P_ADDRESS VARCHAR(50) NOT NULL, CONTACT_NO NUMBER(10),D_ID NUMBER(3), FOREIGN KEY(D_ID) REFERENCES DOCTOR(D_ID), DOB DATE NOT NULL);

ROOM

SQL> CREATE TABLE ROOM (ROOM_ID NUMBER(3) PRIMARY KEY, ROOM_TYPE VARCHAR(10) NOT NULL);

PATIENTS_STATUS

SQL>CREATE TABLE PATIENTS_STATUS (P_ID NUMBER(5),DATE_ADMITTED DATE NOT NULL, DATE_DISCHARGED DATE NOT NULL, ROOM_ID NUMBER(3), FOREIGN KEY(P_ID) REFERENCES PATIENT(P_ID), FOREIGN KEY(ROOM_ID) REFERENCES ROOM(ROOM_ID));

EMPLOYEE

SQL> CREATE TABLE EMPLOYEE(E_ID NUMBER(4) PRIMARY KEY, E_NAME VARCHAR(30) NOT NULL, E_GENDER VARCHAR(1), E_CONTACT_NO NUMBER(10) NOT NULL, E_ADDRESS VARCHAR(50) NOT NULL, E_TYPE VARCHAR(10), SALARY NUMBER NOT NULL);

NURSE

SQL> CREATE TABLE NURSE (EMP_ID NUMBER(4) PRIMARY KEY, NURSE_NAME VARCHAR(30) NOT NULL, NURSE_TYPE VARCHAR(10), SHIFT_START_TIME VARCHAR(5), END_SHIFT_TIME VARCHAR(5), FOREIGN KEY(EMP_ID) REFERENCES EMPLOYEE(E_ID));

NURSE_STATUS

SQL> CREATE TABLE NURSE_STATUS( EMP_ID NUMBER(4) NOT NULL, D_ID NUMBER(3), P_ID NUMBER(5), FOREIGN KEY(EMP_ID) REFERENCES NURSE(EMP_ID),FOREIGN KEY(P_ID) REFERENCES PATIENT(P_ID),FOREIGN KEY(D_ID) REFERENCES DOCTOR(D_ID));

Doctor table

SQL> INSERT INTO DOCTOR VALUES(012,'JASON',3214569870,'SURGEON',9,'HEART');

SQL> INSERT INTO DOCTOR VALUES(101,'NANCY',8972564870,'REGULAR',8,'AURTHOPEDDIC');

SQL> INSERT INTO DOCTOR VALUES(136,'ROB',5645987802,'SURGEON',9,'DENTAL');

Patient Table

SQL> INSERT INTO PATIENT VALUES(00001, 'JOHN', 'M', 'SYDNEY, AUSTRALIA',1019654789,101,'05-OCT-1992');

SQL> INSERT INTO PATIENT VALUES(00002, 'RACHEL', 'F', 'MELBOURNE, AUSTRALIA',1035103579,136,'16-OCT-1991');

SQL> INSERT INTO PATIENT VALUES(00003, 'RICKY', 'M', 'BRISBANE, AUSTRALIA',1045698701,101,'31-AUG-1985');

Room Table

SQL> INSERT INTO ROOM VALUES (106,'ICU');

SQL> INSERT INTO ROOM VALUES(135,'ICCU');

SQL> INSERT INTO ROOM VALUES(021,'OT');

SQL> INSERT INTO ROOM VALUES(005,'CHECK-UP');

Patient Status

SQL> INSERT INTO PATIENTS_STATUS VALUES (001,'12-JAN-2018','20-JAN-2018',21);

SQL> INSERT INTO PATIENTS_STATUS VALUES (002,'26-DEC-2017','05-JAN-2018',5);

SQL> INSERT INTO PATIENTS_STATUS VALUES (003,'02-FEB-2018','05-FEB-2018',106);

Employee Table

SQL> INSERT INTO EMPLOYEE VALUES (0120,'KELVIN','M',1236547890,'PERTH, AUSTRALIA','NURSE',500);

SQL> INSERT INTO EMPLOYEE VALUES (1571,'NANCY','F',4569871230,'KOLKATA, INDIA','DOCTOR',2000);

SQL> INSERT INTO EMPLOYEE VALUES (1074,'ROB','M',1509831531,'MELBOURNE, AUSTRALIA','DOCTOR',2000);

Nurse Table

SQL> INSERT INTO NURSE VALUES (120,'KELVIN','ATTENDANT','20:00','06:00');

SQL> INSERT INTO NURSE VALUES (1134,'JULIA','OT HELPER','05:00','15:00');

SQL> INSERT INTO NURSE VALUES (0014,'RICHARD','GENERAL','15:00','00:00');

Nurse_Status Table

SQL>INSERT INTO NURSE_STATUS VALUES (120,136,2);

SQL> INSERT INTO NURSE_STATUS VALUES (120,101,3);

SQL> INSERT INTO NURSE_STATUS VALUES (14,101,1);

SQL> UPDATE PATIENT SET DOB='31-JUL-1996' WHERE P_ID=1;

SQL> SELECT *FROM PATIENT;

SQL> DELETE FROM PATIENTS_STATUS WHERE P_ID=2;

Al-Masree, H. K. (2015). Extracting Entity Relationship Diagram (ERD) from relational database schema. International Journal of Database Theory and Application, 8(3), 15-26.

Batory, D., & Azanza, M. (2017). Teaching model-driven engineering from a relational database perspective. Software & Systems Modeling, 16(2), 443-467.

Nidzwetzki, J. K., & Güting, R. H. (2016). DISTRIBUTED SECONDO: An extensible highly available and scalable database management system. FernUniversität, Fakultät für Mathematik und Informatik.

Rajakumari, S. B., & Nalini, C. (2014). An efficient data mining dataset preparation using aggregation in relational database. Indian Journal of Science and Technology, 7(S5), 44-46.

Yang, L., & Cao, L. (2016). The Effect of MySQL Workbench in Teaching Entity-Relationship Diagram (ERD) to Relational Schema Mapping. International Journal of Modern Education and Computer Science, 8(7), 1.

Yunus, M. A. M., Krishnan, S. K. G., Nawi, N. M., & Surin, E. S. M. (2017). Study on Database Management System Security Issues. JOIV: International Journal on Informatics Visualization, 1(4-2), 192-194.