Scribd
Upload
40 views13 pages

Hospital Management Database Normalization

The document discusses normalization of databases. It explains what normalization is and the process of structuring data to minimize redundancy and avoid anomalies. It also describes the different types of normalization including first, second and third normal forms which are processes that evaluate relations against criteria to remove dependencies and improper functional dependencies.

Uploaded by

vinay.niranjan006
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
40 views13 pages

Hospital Management Database Normalization

The document discusses normalization of databases. It explains what normalization is and the process of structuring data to minimize redundancy and avoid anomalies. It also describes the different types of normalization including first, second and third normal forms which are processes that evaluate relations against criteria to remove dependencies and improper functional dependencies.

Uploaded by

vinay.niranjan006
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Maharashtra State Board of Technical

Education, Mumbai
Year 2022-23

CERTIFICATE
This is to certify that Roll no. of SY CO 22326 , 22336 , 22343 are of Third Semester of
Diploma in Computer Engineering of Institute GOVT. POLYTECHNIC MIRAJ (Inst.
Code; 0131) has completed the Micro-Project satisfactorily in the Subject Database
Management System (22319) for the academic year 2023-2024 as prescribed in the
curriculum by MSBTE Mumbai.

Place : Miraj
Date :

Mr. [Link] Dr. V. R. Falmari Capt. Dr. N. P. Sonaje


Subject In-charge HOD, CO Principle
Maharashtra State Board of Technical Education ,
Mumbai

[Link] MIRAJ
MICRO-PROJECT
Academic year-2022-23
TITLE OF MICRO- PROJECT
NORMALISATION OF
HOSPITAL MANAGEMENT

Program: Computer Engg. Program Code: 3I


Course: DMS Course Code : 22319
GROUP DETAILS

[Link]. Name of group members Roll No. Enrollment


No.
1 Sarthak C Kamble 22326 2201310195

2 Vinay S Niranjan 22336 2201310205

3 Rutuja B Patil 22343 2201310211


Micro-Project Proposal

Title of Microproject: NORMALISATION OF


HOSPITAL MANAGEMENT

1.0Aim of the Micro-Project :

• To study and understand the concept of normalisation in DBMS


• Understanding various queries
• Practical implementation of data in database
• Improved processes

2.0 Course Outcomes Addressed :

• To develop a base of foundation using SQL.


• To develop a database using different queries and commands.
• Creating our own logic to develop a different table and inserting values.
• Perform Normalization of the tables.

3.0 Proposed Methodology :

 [Link] will decide the topic of the micro-project related to computer.


 [Link] decision we will discussion the topic of micro-project.
 [Link] will distribute sub-points to do by every team member as it claims
 a team works.
 [Link] will look for our sub- points and collected the information.
 [Link] will prepare our official micro-project .
4.0 Action plan :

Sr. Details of activity Planned Planned


No. start date finish
date
1 Selection of topic
2 Making of proposal
3 Code writing
4 Collection of information
5 Making final report

3.0 Resourses used:

Sr. Name of Specification


no resource
material
1 Hardware Intel core i5
9th gen

2 Software MS Access, Oracle


Micro-project Report

Title of micro-project: Blood Bank Management system

1.0 Rationale:

The Hospital management system feature list is concentrated on providing the details
regarding all the Entities and Attributes related to the topic. For the constant and rapid
changes to improve the efficiency of Hospital related services and satisfaction of the peoples.
The greater number of Entities and involvement of different sectors create need for proper
management .The interactions between the every element or data with each other can be
simplified for the convenience of both sides.
This micro-project helps in gaining all these targets to be done using ‘Database
Management System’.

2.0 Aims /Benefits of the micro-project:

Some of the Benefits of the micro-project are as follows:


• Management of Hospital
• Understanding various queries
• Practical implementation of data in database
• Improved processes

3.0 Course outcomes related micro-project:

• To develop a base of foundation using SQL.


• To develop a Database using different queries and commands.
• Creating our own logic to develop a different table and inserting values.
• Develop ER diagram and table to solve problems logically.

4.0 Literature review:

• The Procedures, functional, and various database objects presented in the


code above.
• The resources used are different reference books, a personal computer.

5.0 Actual methodology followed :

 We decided the topic of the micro-project related to computer.


 After decision we discussed on the topic of micro-project.
 We distributed sub-points to do by every team member as it claims a team work.
 We looked for our sub- points and collected the information regarding it.
 We prepared our official micro-project .
6.0 Actual Resources Required :

Sr. Name of Specification


no resource
material
1 Internet surffing Intel Core i5
9th Gen
2 Software MS Access, Oracle

7.0 Skill developed /learning outcomes :

 The concept of project understood


 The structure and requirement of proposal and report understood
 The necessity of group work is well understood
 How to search information and use of different online tools come to know.
 Handling the database efficiently.
 Practical implementation of queries.

8.0 CONCLUSION
 Our project well addressed the limitations of the existing system. We designed well
organized database management system which is a challenging job in this era.

 We have built a database for a Hospital using Microsoft SQL Server. Before
implementing the database, in the design phase, we have explored various features,
operations of a blood bank to figure out required entities, attributes and the
relationship among entities to make an efficient Entity Relationship Diagram(ERD).

 After analyzing all the requirements, we have created our ERD and then converted the
ERD to relational model and normalized the tables.

Finally, we have executed sample queries on our database to check its performance to
retrieve useful information accurately and speedily.
INTRODUCTION

Normalization is the process of structuring and handling the relationship between data to minimize
redundancy in the relational table and avoid the unnecessary anomalies properties from the
database like insertion, update and delete. It helps to divide large database tables into smaller tables
and make a relationship between them. It can remove the redundant data and ease to add,
manipulate or delete table fields.
A normalization defines rules for the relational table as to whether it satisfies the normal form. A
normal form is a process that evaluates each relation against defined criteria and removes the
multivalued, joins, functional and trivial dependency from a relation. If any data is updated, deleted
or inserted, it does not cause any problem for database tables and help to improve the relational
table’ integrity and efficiency.
WHAT IS NORMALIZATION

Normalization is the process of organizing data in a database. This includes creating tables and
establishing relationships between those tables according to rules designed both to protect the data and
to make the database more flexible by eliminating redundancy and inconsistent dependency.

In statistics and applications of statistics, normalization can have a range of meanings.[1] In the
simplest cases, normalization of ratings means adjusting values measured on different scales to a
notionally common scale, often prior to averaging. In more complicated cases, normalization may
refer to more sophisticated adjustments where the intention is to bring the entire probability
distributions of adjusted values into alignment. In the case of normalization of scores in
educational assessment, there may be an intention to align distributions to a normal distribution.
A different approach to normalization of probability distributions is quantile normalization, where
the quantiles of the different measures are brought into alignment.
In another usage in statistics, normalization refers to the creation of shifted and scaled versions of
statistics, where the intention is that these normalized values allow the comparison of
corresponding normalized values for different datasets in a way that eliminates the effects of
certain gross influences, as in an anomaly time series. Some types of normalization involve only a
rescaling, to arrive at values relative to some size variable. In terms of levels of measurement, such
ratios only make sense for ratio measurements (where ratios of measurements are meaningful), not
interval measurements (where only distances are meaningful, but not ratios).
In theoretical statistics, parametric normalization can often lead to pivotal quantities – functions
whose sampling distribution does not depend on the parameters – and to ancillary statistics –
pivotal quantities that can be computed from observations, without knowing parameters.
TYPES OF NORMALIZATION
I. First Normal Form:
Eliminate repeating groups in individual tables.
Create a separate table for each set of related data.
Identify each set of related data with a primary key.
Do not use multiple fields in a single table to store similar data. For example, to track an inventory
item that may come from two possible sources, an inventory record may contain fields for Vendor
Code 1 and Vendor Code 2. Also, what happens when you add a third vendor? Adding a field is
not the answer; it requires program and table modifications and does not smoothly accommodate
a dynamic number of vendors. Instead, place all vendor information in a separate table called
Vendors, then link inventory to vendors with an item number key, or vendors to inventory with a
vendor code key.

II. Second Normal Form :


Create separate tables for sets of values that apply to multiple records.
Relate these tables with a foreign key.
Records should not depend on anything other than a table’s primary key (a compound key, if
necessary). For example, consider a customer’s address in an accounting system. The address is
needed by the Customers table, but also by the Orders, Shipping, Invoices, Accounts Receivable
and Collections tables. Instead of storing the customer’s address as a separate entry in each of these
tables, store it in one place, either in the Customers table or in a separate Addresses table.

III. Third Normal Form :


Eliminate fields that do not depend on the key.
Values in a record that are not part of that record’s key do not belong in the table. In general, any
time the contents of a group of fields may apply to more than a single record in the table, consider
placing those fields in a separate table.
For example, in an Employee Recruitment table, a candidate’s university name and address may
be included. But you need a complete list of universities for group mailings. If university
information is stored in the Candidates table, there is no way to list universities with no current
candidates. Create a separate Universities table and link it to the Candidates table with a university
code key.

NORMALIZED DATABASE FOR HOSPITAL

Patient Medication Form:


Important Assumptions
Patient No., Drug No., and Start Data are all needed to uniquely identify a record. This is because the
patient can take many drugs, but he can also take each drug many times The finish date could also be
used as an alternative to start date in the PK Bed no. and related information can be uniquely
determined by the patient No. In addition, Bed # can be used to determine Ward [Link] units
per day are based on the patient taking the drug, the drug, and the start date of their treatment.

Un-normalized Table :

Patient Patient Word Word Bed Drug Drug Name Description


No. Name No Name No. No.
10034 Robert 11 Orthopedic 84 10223 Morphin, Pain killer
Macdonald 10334 Tetracycline Antibiotic

When moving from an Un-normalized table to 1NF you need to take out repeating groups.
One approach is to fill in empty cells. A second is to split out

First Normal Form: No Repeating Groups

Patient Patient Word Word Bed Drug Drug Name Description


No Name No Name No No
10034 Robert 11 Orthopedic 84 10223 Morphin Pain Killer
Macdonald
10034 Robert 11 Orthopedic 84 10334 Tetracycline Antibiotic
Macdonald
Second Normal Form: Eliminate Redundant Data
In Second normal form all non-key attributes are fully functional dependent on primary key

Patient Patient Word Word Bed Drug Drug Name Description


No Name No Name No No
10034 Robert 11 Orthopedic 84 10223 Morphin Pain Killer
Macdonald
10034 Robert 11 Orthopedic 84 10334 Tetracycline Antibiotic
Macdonald

In Above table non-prime attribute drug name is dependent on drug no which is proper subset of
candidate key

Drug No Drug Name


10223 Morphin
10334 Tetracycline

When moving from 2NF to 3NF, remove the transitive dependencies that [Link] are two
transitive dependencies that exist; Ward Information is functionally dependent on Ward and
Ward is functionally dependent on Bed
Third Normal Form: Eliminate Data Not Dependent On Key
A relationship in third normal form .there is no transistive dependency for non-prime attributes
as well as is in second normal form

Patient Patient Word Word Bed Drug Drug Name Description


No Name No Name No No
10034 Robert 11 Orthopedic 84 10223 Morphin Pain Killer
Macdonald
10034 Robert 11 Orthopedic 84 10334 Tetracycline Antibiotic
Macdonald

Bed No Word No
84 11
84 11

Word No Word Name


11 Orthopedic
11 Orthopedic

Patient No Patient Name Address


10034 Robert Macdonald Banglor
10034 Robert Macdonald Banglor
CONCLUSION
Database normalization or database normalisation is the process of structuring a relational database
in accordance with a series of so-called normal forms in order to reduce data redundancy and
improve data integrity. It was first proposed by British computer scientist Edgar F. Codd as part
of his relational [Link] is the process of organizing data in a database. It includes
creating tables and establishing relationships between those tables according to rules designed both
to protect the data and to make the database more flexible by eliminating redundancy and
inconsistent dependent

Common questions

Powered by AI

In the normalization process for the hospital management micro-project, Entity Relationship Diagrams (ERD) were used to identify the necessary entities and their relationships. Through ERD, the project identified critical features, operations, and dependencies within the hospital management system. The diagram served as a blueprint to create a relational model, which was subsequently normalized to eliminate redundancy and establish clear relationships, making the design phase more efficient and effective in capturing all data interactions coherently .

Challenges faced during the normalization of a hospital management system's database included identifying adequate entities to encapsulate all functions without redundancy, managing complex relationships between tables, and ensuring data integrity across the system. These challenges were addressed by designing a comprehensive Entity Relationship Diagram (ERD), which facilitated recognizing necessary attributes and eliminating transitive dependencies. Moreover, detailed analysis during the design phase allowed the conversion from ERD to a normalized relational model, ensuring an efficient and adaptable database structure .

Normalization reduces data redundancy in a hospital database by organizing data into tables based on defined rules, ensuring that each piece of data is stored only once. By disallowing data duplication across tables and enforcing dependencies through primary and foreign keys, normalization prevents repeated data entries in multiple locations, which could lead to inconsistencies. This reduction prevents data anomalies upon data insertion, update, and deletion, ultimately preserving data integrity across the hospital database system .

The benefits of implementing a normalized database structure in hospital management systems include improved data integrity and the efficient handling of the database through minimized redundancy. This structure facilitates the practical implementation of data and allows for precise execution of queries, thereby enhancing the efficiency and reliability of hospital services. Additionally, it supports the creation of well-organized databases that adapt rapidly to changes, providing a robust foundation for data-driven decision-making in hospital management .

Critical skills developed during the micro-project on database normalization included understanding the structure and requirements of project proposals and reports, enhancing group work capabilities, and proficiently conducting information searches using online tools. Additionally, participants learned to handle databases efficiently and apply practical SQL queries, deepening their hands-on experience with database management systems .

The Third Normal Form (3NF) ensures data consistency by eliminating fields that do not depend on the primary key of the table, thereby removing any transitive dependencies. In a hospital management system, this means organizing data so that attributes not directly related to a key attribute are placed in separate tables, preventing anomalies due to redundant fields. By ensuring that each field is only dependent on the primary key, the system maintains consistent data across various operations like updates and queries .

In the design of a Blood Bank Management System, normalization plays a crucial role in ensuring efficient data management by organizing data into tables that reduce redundancy and allow for clear relationships between tables. This ensures that data related to blood banks, such as donor information, blood types, and stock levels, are stored efficiently and can be managed effectively without duplication or inconsistency. Implementing normalized structures allows for easy retrieval and manipulation of data, meeting the functional requirements of such a system .

Normalization in a hospital management database system is significant as it helps minimize redundancy and avoid anomalies such as insertion, update, and delete issues. By structuring data in a normalized form, larger tables are divided into smaller related tables, thus promoting data integrity and allowing efficient data manipulation. This organization aids in ensuring that updates to the data do not cause inconsistencies across the database, enhancing the overall integrity and functionality of the system .

To move from an Un-normalized table to First Normal Form (1NF), repeating groups within a table must be eliminated. For example, if a hospital table contains multiple drugs taken by a patient in a single record, these multiple entries must be separated such that each row contains only a single drug entry by removing repeating groups. This involves creating a separate record for each drug that a patient takes, ensuring that each entry in the table is atomic and there are no grouped fields for similar data .

The methodology for the micro-project on hospital management normalization involved topic selection, discussion, task distribution among team members, research on sub-topics, and compilation of the final report. Resources used included hardware like Intel Core i5 processors, and software such as MS Access and Oracle for database creation and management. The team utilized theoretical knowledge and reference books alongside practical tools to implement and document the project effectively, reflecting thorough research and coordinated teamwork .

You might also like