Transcription
B.Sc Engineering Thesis PaperOn“Designingand Interfacing a Hospital-BasedDatabase System”(A Case Study of BIRDEM)Department of Computer Science & EngineeringAhsanullah University of Science & TechnologyDhaka, Bangladesh.A thesis paper submitted in partial fulfillment of the requirementsfortheDegreeofEngineering)Date: - July 12, 2010Session: - Spring ’10B.ScEngineering(ComputerScience&
“Designing and Interfacing a Hospital-BasedDatabase System”(A Case Study of BIRDEM)Submitted By:-1. Syed Mahboob Nur06.02.04.0132. Jahid Hasan06.02.04.0363. Kazi Sumaiya06.02.04.0424. Tasfia Rahman06.02.04.044In Partial Fulfillment for the Degree ofB.Sc Engineering in Computer Science & EngineeringAhsanullah University of Science & Technology.ii
CertificationWe hereby, proclaim that the thesis on “Designing and Interfacing aHospital-Based Database System (A Case Study of BIRDEM)” wasconducted under the supervision of Ms. Rosina Surovi Khan.We also declare that neither this nor any part thereof has beensubmitted elsewhere for the award of any degree.Approved By:Submitted By:---------------------------------------Dr. S. M. Abdullah Al-MamunSyed Mahboob NurProfessor & Head of the DepartmentDept of C.S.E , AUST--------------Jahid HasanSupervised By:---------------------------------------Kazi Sumaiya MonaMs. Rosina Surovi KhanAssistant Professor----------------------Dept of C.S.E, AUSTTasfia Rahmaniii
CONTENTS AT A GLANCEPREFACEABSTRACT1. Introduction2. Designing the Database System3. Interfacing the Database System using.NET framework4. Conclusion and Future workiv
AcknowledgementStarting by the name of Almighty Allah Authors would like to express their sincere and hearty gratitude and profoundindebtedness to their respectful teacher Ms Rosina Surovi Khan, AssistantProfessor, AUST, for her constant timely and appropriate guidance, helpfuladvice, invaluable assistance and endless patience throughout the progress oftheir work, without which the work could not have been completed.Authors also acknowledge with hearty thanks to all the members of theBIRDEM hospital for their important information and cooperation.Finally, authors acknowledge all cooperation of their friends, who helped themthrough giving their important time, their knowledge and their best advice.Special thanks to our parents and elders for their help and support.v
Table of ContentsPREFACE ixABSTRACT x1. INTRODUCTION 12. DESIGNING THE DATABASE SYSTEM. 42.1 Determining Entities and Attributes 42.2 Entity Relationship Diagram 72.3 Relational Model 92.3.1 Relational Tables’ Descriptions 132.3.2 Explanation of Relational Model 262.4 Relational Database Design 342.4.1 Functional Dependency 342.4.2 Normalization 35vi
2.5 Implementation in SQL Server 492.5.1 Creation of Tables and Insertion of data 512.5.2 Sample Data Values of Tables 532.6 Complex Queries .603. INTERFACING THE DATABASE SYSTEMUSING .NET FRAMEWORK 643.1 Research on Interface Design Guidelines 643.2 FRONT END Design 743.2.1 Forms’ Design . 753.2.2 Relating Interface Design Guidelines toFront End Design 933.3 Security feature of FRONT END 963.4 Implementation of Insert, Delete, Update buttons &Search Option 1073.5 Usage of DLL file 113vii
4. CONCLUSION & FUTURE WORK 1154.1 Conclusion 1154.2 Future Work 1154.2.1 Gridline View Features 1164.2.2 Trigger Features 129REFERENCES 133APPENDIX 135viii
PREFACEOur thesis is about Designing and Interfacing a Hospital-Based DatabaseSystem. It forms a basic entity of the management of a Hospital. Hence, it isvery important for the system to be reliable, user friendly, and should beproperly functional for a long time without cropping up of any errors.To start with the system study we visited Bangladesh Institute of Research andRehabilitation for Diabetes, Endocrine and Metabolic Disorders (BIRDEM). Wesaw their system, studied it and tried to develop a better system. Our system isan automated system for Hospital Management. This gave us the idea of thedifferent fields that ought to be in a Hospital Management System such aspatient registration, his/her advance payment, the records, the details etc. andalso how a software system can make the work easy both for the hospital staffand the patients. Moreover, the evaluation helped us to arrive at the conclusionthat the automated software is far more superior to the manual ones.ix
ABSTRACTOur motive is to develop a software that is very much user friendly and easy togather information in a very short time. We try to make our software reliableand comfortable.As our thesis paper is on Designing and Interfacing a Hospital ManagementSystem (A Case Study of BIRDEM) we divide our work into two basic partsDesigning part and Interfacing Part. We give a flow chart on our work division in THESIS OVERVIEW part.Chapter 1 IntroductionIn this chapter we discuss the definition of Database and its usefulness. Wealso describe the reason to take HOSPITAL MANAGEMENT SYSTEM as ourthesis work.Chapter 2 Designing the Database SystemIn this chapter we describe the entities and attributes. We draw the EntityRelationship Diagram (ERD) and Tables. We determine the attributes of tablesand its data types. We also find functional dependencies and normalize all thetables. Then we implement our database in SQL Server and finally we executesome complex queries on the system.Chapter 3 Interfacing the Database System using .Net Framework.We made a research on Interface Design Guidelines and designed our front endin C#. We applied some of the guidelines in our front end.We control our software security using C#. We Insert Delete, Update andSearch data from the database in our software. We used a DLL file so that wex
can easily access to any Operating System and we don’t need to load ourdatabase.Chapter 4 Conclusion and Future Work.We tried to Save, Delete and Update data using Data Grid view and we alsotried to use Trigger in SQL Server but we cannot complete them. So we includeit as a part of future work.xi
C H A P T E R 1INTRODUCTION What is a Database? A Database is a collection of records which are stored on acomputer; a database organizes the data according to databasemodels such as a relational model. [1] Why do we need Databases? Databases collect items on which the user can carry out variousoperations such as viewing, navigating, creating tables, andsearching. Databases can be seen as a symbolic form of thecomputer age. [2]We use databases for these reasons. Such as,1. We use database because we can easily manipulate, edit or delete data.2. Data are kept organized in a database so we can easily retrieve data.3. Easy to find out desired data.4. Data are secured. Advantages of Database Reduced Data Redundancy. Reduced updating errors and increased consistency. Greater data integrity and independence from applicationsprograms.1
Improved data access to users through use of host and querylanguages. Improved data security. Reduced data entry, storage, and retrieval costs. Facilitated development of new application programs. [3]In our thesis Designing and Interfacing a Hospital-Based DatabaseSystem (A case study of BIRDEM) we can see two basic parts. Designing & InterfacingOur Thesis Teacher Ms. Rosina Surovi Khan decided that we have to completethe design part in semester 4/1 and interfacing part in semester 4/2. In theintroductory class of the thesis our respected madam suggested to select aspecific database system to work on.# Choosing Hospital Management System for our thesisWe study and select three systems at first. The systems were Banking System Computer Sales Management System Hospital Management SystemWe saw the demos of the respective systems from different sources and all thegroup members decided to do the thesis on Hospital Management System (ACase Study of BIRDEM) because the system is less complex and easy to study.Most Banking Systems and Computer Sales Management Systems are controlledusing online based software where users can access from any part of thecountry. But we are determined to make desktop based software. So we decided2
to choose Hospital Management System based on a Case Study of BIRDEM. Wetry our best to make the system efficient and user friendly with the help of ourdatabase and front end software.# Thesis OverviewDATABASEINTERFACINGDESIGNING Determining Entities and Research on InterfaceAttributesDesign Guidelines Entity Relationship Diagram Front End Design Relational Model Normalization Security feature of Front Implementation in SQLEndServer Implementation Complex Queries(Insert, Delete, UpdateButtons and Search Option) Usage of DLL file3
C H A P T E R 2DESIGNING THE DATABASE SYSTEM2.1 Determining Entities and Attributes Entity An entity is something that has a distinct, separate existence, though itneed not be a material existence. In particular, abstractions and legalfictions are usually regarded as entities. In general, there is also nopresumption that an entity is animate. Entities are used in systemdevelopmental models that display communications and internalprocessing of, say, documents compared to order processing. An entity could be viewed as a set containing subsets. A DBMS entity is either a thing in the modeled world or a drawingelement in an Entity Relationship Diagram(ERD) .[4]4
Attribute An attribute is a specification that defines a property of an object,element, or file. It may also refer to or set the specific value for agiven instance of such. Attributes should more correctly be considered metadata. It isfrequently and generally a property of an entity. An attribute of an object usually consists of a name and a value; of anelement, a type or class name; of a file, a name and extension.[5] Data Type A data type (or datatype): In programming, a classification identifyingone of various types of data, as floating-point, integer, or Boolean, statingthe possible values for that type, the operations that can be done on thattype, and the way the values of that type are stored.[6]We thinkourbestand determinethe entitiesandattributesfor our Database System. The Entities and Attributes are given below.5
Fig1: Determining Entities and Attributes.6
2.2 Entity Relationship Diagram (ERD):We draw the Entity Relationship Diagram (ERD) very carefully andefficiently for the whole system of BIRDEM.We were able to cover all probable information of BIRDEM in our ERD.The ERD is given below:Fig2: Entity Relationship Diagram (ERD).7
2.3 Relational Model:After completing the ERD successfully we made the relational model(table schemas) taking into account all the entities and the relationships.Patient Table:Pat idPat nameAgeSexAddressDOBMOBRoom Table:Room idRoom NoRoom typeRoom costReceptionist Table:Rcp idRcp nameAgeAddressMOBshiftingsalaryAdmission Table:This is a junction table between Patient, Receptionist & Room tables.Admsn idPat idRoom idRcp iddatetimeAddressMOBDoctor Table:Doc idDoc nameDoc typeDesignationAge8Passed fromSalary
Appointment Table:This is a junction table between Patient, Receptionist & Doctor tables.Ap idPat id Doc id Rcp id apnmt dateapnmt timeBill Table:Bill idBill forBill typeBill totalAccountant Table:Acct idAcct nameAgeAddressMOBWorking timeAcct salaryPayment Table:This is a junction table between Patient, Bill & Accountant Tables.Pay idBill forPat idAcct idPay typeCompanym datePay dateMedicine Table:Mdcn idMdcn namee datepricePrescription Table: This is a junction table between Patient, Doctor & Medicine tables.Prs idDoc idMdcn idPat iddate9Fee
Test Table:Test idTest namedaterep datefeeAssist Table:This is a junction table between Patient, Doctor & Test tables.Serial noPat idDoc idTest idtimedateOT Table:Ot idOt room noOperation Table:This is a junction table between Patient, Doctor & OT tables.Op idDoc idPat idOt idOp dateOp timeDepartment Table:Dept idDept nametreatmentDoctor from Department Table:This is a junction table between Doctor & Department tables.Dfd idDoc idDept idNurse Table:Nrs idNrs nameAgeAddressMoB10Nrs wo shiftexperienceSalary
Nursing Service Table:This is a junction table between Patient, Room & Nurse tables.Ns idPat idNrs idRoom idMoBw shiftWard Boy Table:Wb idwb nameSalaryCleaning Service Table:This is a junction table between Patient, Room & Ward Boy tables.Cls idPat idWb idRoom idMobAddressDriver Table:Dr idDr nameShiftSalaryAmbulance Table:Amb idAmb num CapacityAmbulance Service Table:This is a junction table between Patient, Driver & Ambulance tables.As idPat idDr idAmb idCarriers Table:Cr idCr nameMOBAddressSalary11
Carrying Service Table:This is a junction table between Patient, Ambulance & Carriers tables.CS idCr idAmb idPat id2.3.1 Relational Tables’ DescriptionsPatient tableAttributesData typeCommentsPat idintUnique id for a PatientPat namevarchar(20)AgeintSexvarchar(20)Patient’s NamePatient’s AgePatient is Male orFemaleAddressvarchar(20)Patient’s AddressDobvarchar(20)Date of BirthMobintMobile NumberRoom tableAttributesData typeRoom idintRoom novarchar(20)Room numberRoom typevarchar(20)Room is VIP or NormalRoom costint12CommentsUnique id for a RoomCost of the Room
Receptionist tableAttributesData typeRcp idintCommentsUnique id for aReceptionistRcp namevarchar(20)Receptionist’s 20)Receptionist working shiftSalaryintSalary a Receptionist getsReceptionist’s ageReceptionist’s AddressMobile NumberAdmission tableAttributesData typeAdmsn idintCommentsUnique id for anAdmissionPat idintUnique id for a PatientRoom idintUnique id for a RoomRcp idintUnique id for aReceptionistDatevarchar(20)13Date of Admission
Doctor table:AttributesData typeCommentsDoc idintDoc namevarchar(20)Doctor’s nameDoc typevarchar(20)Doctor’s varchar(20)Doctor’s designationPassed fromvarchar(20)Doctor is passed from whichUnique id for a DoctorDoctor’s ageDoctor’s addressMobile Numbermedical collegeSalaryintSalary of a doctorAppointment tableAttributesData typeCommentsApnmt idintUnique id for an AppointmentPat idintUnique id for a PatientDoc idintUnique id for a DoctorRcp idintUnique id for a ReceptionistApnmt datevarchar(20)Date of an Appointment14
Bill tableAttributesData typeCommentsBill idintUnique id for a BillBill forvarchar(20)Purpose of the billBill typevarchar(20)Bill either in Cash orCheckBill totalintTotal amountAccountant tableAttributesData typeAcct idintCommentsUnique id for anAccountantAcct namevarchar(20)Accountant’s NameAgeintAddressvarchar(20)MobintMobile NumberAcct salaryintSalary of an AccountantAccountant’s age15Accountant’s Address
Payment tableAttributesData typeCommentsPay idintUnique id for a PaymentBill idintUnique id for a BillPat idintUnique id for a PatientAcct idintUnique id for an AccountantPay typevarchar(20)Payment in Cash or CheckPay datevarchar(20)Date of PaymentMedicine tableAttributesData typeCommentsMdcn idintMdcn namevarchar(20)Medicine’s Namecompanyvarchar(20)Medicine’s CompanyM datevarchar(20)Manufacture DateE datevarchar(20)Expire DatepriceintUnique id for a MedicinePrice of the Medicine16
Prescription tableAttributesData typePrs idintCommentsUnique id for aPrescriptionDoc idintUnique id for a DoctorMdcn idintUnique id for a MedicinePat idintUnique id for a PatientDatevarchar(20)Date of the PrescriptionTimevarchar(20)Time of the PrescriptionFeevarchar(20)Prescription FeesTest tableAttributesData typeTest idintTest namevarchar(20)Name of the TestDatevarchar(20)Date of TestRep datevarchar(20)Date of the ReportFeeint17CommentsUnique id for a TestTest Fees
Assist tableAttributesData typeCommentsSerial nointUnique id for an AssistedTest directed to a Patientby a DoctorPat idintUnique id for a PatientDoc idintUnique id for a DoctorTest idintUnique id for a TestDatevarchar(20)Date of the Assisted TestTimevarchar(20)Time of the Assisted TestOT tableAttributesData typeOt idintCommentsUnique id for anOperation Theater (OT)Ot room novarchar(20)18OT Room Number
Operation tableAttributesData typeOp idintCommentsUnique id for anOperationDoc idintUnique id for a DoctorPat idintUnique id for a PatientOt idintUnique id for an OTOp datevarchar(20)Date of the OperationOp timevarchar(20)Time of the OperationDepartment tableAttributesData typeDept idintCommentsUnique id for aDepartmentDept namevarchar(20)Department’s nametreatementvarchar(20)Treatments of a patientconducted in a Department19
Doctor from Department tableAttributesData typeDfd idintCommentsUnique id for aDoctorsfromDepartment junctiontableDoc idintUnique id for a DoctorDept idintUnique id for a DepartmentNurse tableAttributesData typeCommentsNrs idintNrs namevarchar(20)AgeintAddressvarchar(20)Nurse’s AddressMobintMobile NumberNrs wo shiftvarchar(20)Unique id for a NurseNurse’s NameNurse’s ageNurse working char(20)salaryint20Nurse’s ExperienceSalary of a Nurse
Nursing Service tableAttributesData typeNs idintCommentsUnique id for a NursingServicePat idintUnique id for a PatientNrs idintUnique id for a NurseRoom idintUnique id for a RoomDatevarchar(20)Date of Nursing ServiceTimevarchar(20)Time of Nursing ServiceWard boy tableAttributesData typeCommentsWb idintWb namevarchar(20)MobintW shiftvarchar(20)salaryintUnique id for a Ward BoyWard Boy’s NameMobile NumberWorking shift of a Ward BoySalary of a Ward boy21
Cleaning Service tableAttributesData typeCls idintCommentsUnique id for a CleaningServicePat idintUnique id for a PatientWb idintUnique id for a Ward BoyRoom idintUnique id for a RoomDatevarchar(20)Date of Cleaning ServiceTimevarchar(20)Time of Cleaning ServiceDriver tableAttributesData typeCommentsDr idintDr namevarchar(20)Driver’s NamemobintMobile Numberaddressvarchar(20)Driver’s AddressShiftvarchar(20)Working shift of a DriversalaryintUnique id for a DriverSalary of a Driver22
Ambulance tableAttributesData typeCommentsAmb idintUnique id for anAmbulanceAmb numvarchar(20)Ambulance’s NumberCapacityintCapacity of an AmbulanceAmbulance Service tableAttributesData typeCommentsAs idintUnique id for an AmbulanceServicePat idintUnique id for a PatientDr idintUnique id for a DriverAmb idintUnique id for an AmbulanceDatevarchar(20)Date of the Ambulance ServiceTimevarchar(20)Time of the Ambulance Service23
Carriers tableAttributesData typeCr idintCommentsUnique id for a Carrierwho will carry patientsinside the hospital’spremises from theambulance.Cr namevarchar(20)Carrier’s NameMobintMobile NumberAddressvarchar(20)Carrier’s AddressSalaryintSalary of a CarrierCarrying Service tableAttributesData typeCs idintCommentsUnique id for a CarryingServiceCr idintUnique id for a CarrierAmb idintUnique id for an AmbulancePat idintUnique id for a PatientDatevarchar(20)Date of the Carrying ServiceTimevarchar(20)Time of the Carrying Service24
2.3.2 Explanation of Relational Model# Relationship between Receptionist, Patient and Room Entities in the ERModel: 1 Receptionist can admit 1 Patient in 1 Room in a certain date and time. 1 Receptionist can admit in 1 Room 1 Patient in a certain date and time. In 1 Room, 1 Patient is admitted by 1 Receptionist in a certain date andtime.So the relationship is a Ternary Relationship named Admission (in thediamond) with cardinality ratio from Patient to Receptionist to Room as 1to 1 to 1.25
# Relational model for Receptionist, Patient and Room Entities:Receptionist, Patient and Room Entities become Receptionist, Patient and Roomtables.Patient Table:Pat idPat nameAgeSexDOBMOBAddressRoom Table:-Room idRoom NoRoom typeRoom costReceptionist Table:-Rcp idRcp nameAgeAddressMOBshiftingDatetimesalaryThe junction Admission also becomes a table.Admission Table:admsn idPat idRoom idRcp id Primary Key of the Patient Table goes to Admission Table as Foreign Key. Primary Key of the Room Table goes to Admission Table as Foreign Key. Primary Key of the Receptionist Table goes to Admission Table as ForeignKey.Since the Cardinality Ratio from Patient to Receptionist to Room is 1 to 1 to 1,26
admsn id is a Primary key in the Admission Table. Pat id from Patient Table,Room id from Room Table and Rcp id from Receptionist Table become ForeignKeys in the Admission Table.In a similar way, as cardinality ratio for Receptionist Patient Doctorrelationship is 1 to 1 to 1, Receptionist, Patient and Doctor entities becomeseparate tables along with a junction Appointment table which has Rcp id,Pat idandDoc idasforeignkeys.SimilarlogicappliestoPatient Ambulance Driver relationship with cardinality ratio 1 to 1 to 1.# Relationship between Doctor and Department Entities in the ER Model: 1 Doctor can be from 1 or Many Departments. 1 Department may have 1 or Many Doctors.So it is a Many to Many relationship named Doctor from Department (in thediamond).27
# Relational model for Doctor and Department Entities:Doctor and Department Entities become Doctor and Department tables.Doctor Table:Doc idDoc nameDoc typeDesignationAgeAddressMOBPassed fromSalaryDepartment Table:Dept idDept nametreatmentThe junction table Doctor from Department also becomes a table.Doctor from Department Table:Dfd idDoc idDept id Primary Key of the Doctor Table goes to Doctor from Department Tableas part of Primary Key. Primary Key of the Department Table goes to Doctor from DepartmentTable as part of Primary Key.Since the Cardinality Ratio from Doctor to Department is Many to Many, Dfd idis a part of Primary key in the Doctor from Department Table. Doc id fromDoctor Table and Dept id from Department Table become parts of Primary Keyin the Doctor from Department Table.28
# Relationship between Patient, Doctor and Medicine Entities in the ER Model: 1 Doctor gives 1 patient 1 or more medicine. 1 patient takes 1 medicine prescribed by 1 doctor. 1 medicine is prescribed by 1 doctor to 1 patient.So the relationship is a Ternary Relationship named Prescription (in thediamond) with a Cardinality Ratio from Patient to Doctor to Medicine 1 to1 to Many.29
# Relational model for Patient, Doctor and Medicine Entities:Patient, Doctor and Medicine Entities become Patient, Doctor and Medicinetables.Patient Table:Pat IdPat nameAgeSexDOBMOBAddressDoctor Table:Doc idDoc nameDoc typeDesignationAgeAddressMOBPassed fromSalaryMedicine Table:Mdcn idMdcn namecompanym datee datepricePrescription Table: This is a junction table between Patients, Doctor & Medicine Table.Prs idDoc idMdcn idPat iddatefee Primary Key of the Patient Table goes to Prescription Table as ForeignKey. Primary Key of the Doctor Table goes to Prescription Table as ForeignKey. Primary Key of the Medicine Table goes to Prescription Table as part ofPrimary Key.Since the Cardinality Ratio from Patient to Doctor to Medicine 1 to 1 to M,Prs id is a Primary key in the Prescription Table. Pat id from Patient Table,30
Doc id from Doctor Table and Mdcn id from Medicine Table become ForeignKeys in the Admission Table.In a similar way relational tables have been designed for Patient-Doctor-Test,Patient-OT-Doctor, Patient-Bill-Accountant relationships with cardinality ratio 1to 1 to M. Similar logic applies for Patient-Ambulance-Carrier relationship withcardinality ratio 1 to 1 to M.#Relationship Between Patient,Room & Nurse Entities in the ER Model :- 1 room is fixed for 1 Patient to provide nursing service for 1 orMany nurses in a certain date. 1 patient receives nursing service from 1 Nurse in 1 Room in acertain date. 1 nurse can render proper services in 1 room to many patients in acertain date.So it is a Ternary Relationship named Nursing Services (in the diamond)with cardinality Ratio from Room to Nurse to Patient 1 to M to M.31
#Relational model between Patient, Nurse and Room Entities:Patient Table:Pat idPat nameAgeSexDOBMOBAddressRoom Table:Room idRoom NoRoom typeRoom costNurse Table:Nrs idNrs nameAgeAddressMobNrs wo shiftexperienceSalaryNursing Service Table:This is a junction table between Patient, Room and Nurse Table.Ns idPat idNrs idRoom id Primary Key of the Patient Table goes to Nursing Service Table aspart of Primary Key. Primary Key of the Nurse Table goes to Nursing Service Table aspart of Primary Key. Primary Key of the Room Table goes to Nursing Service Table asForeign Key.Since the Cardinality Ratio from Room to Patient to Nurse is 1to M to M. Ns idis a Primary key in the Nursing Service Table. Pat id from Patient Table, Nrs id32
from Nurse Table become parts of Primary Key in the Nursing Service Table.Room id from Room Table becomes Foreign Key in the Nursing Service Table.In a similar way relational tables are created for Patient-Room-Wardboyrelationship with cardinality ratio 1 to M to M.2.4 Relational Database DesignRelational databases are the most commonly used database today. It usesthe table to structure information so that it can be readily and easilysearched through.To make a Relational database design we have to be clear about two parts:1. Functional Dependency2. Normalization2.4.1 Functional DependenciesDefinition of functional dependencies:Given a relational schema R (A1, A2, ., An) and X, Y {A1, ., An}.Then X - Y means that for every extension of R, the following holds:R contains no two tuples that are equal in all values of X but differ in at leastone value of Y.(Pronunciation: "X determines Y functionally" "Y is functionally dependent ofX").Example:Student (matNr, name):{matNr} - {name}Definition of full functional dependencies:Prerequisites as in Definition 1.33
Y is said to be fully functionally dependent of X, if there is no proper subset X’ X,Where X’ - Y.Notation: X Y.Example:A University Database:Class (classId, room, day, pName){classId, room} - {pName}{classId, day, pName} - {room}{classId} {pName}{classId} {room} [7]2.4.2 NormalizationNormalization is the process of organizing data in a database. This includescreating tables and establishing relationships between those tables according torules designed both to protect the data and to make the database more flexibleby eliminating redundancy and inconsistent dependency.It has mainly two goals: First goal: eliminate redundant dataFor example, storing the same data in more than one table Second Goal: ensure data dependencies make senseFor example, only storing related data in a tableBenefits of Normalization: Less storage space Quicker updates Less data inconsistency Clearer data relationships34
Easier to add data Flexible StructureBad database designs results in: Redundancy: inefficient storage. Anomalies: data inconsistency, difficulties in maintenance.[7]1NF, 2NF, 3NF, BCNF are some of the early forms in the list that address thisproblem.First Normal Form (1NF)Definition:A relation is in first normal form if it contains only simple, atomic values forattributes, no sets. ista12Peter10Iris9Martin17Rainer1835StuttgartTrier
The value of an attribute can be a relation by itself. Operations in the model are much more complicated In order to keep the model simple: 1NFWays to normalize the above relation:First attempt:Person (name, place, child1, child2, child3) Not good. Reason: either not enough available columns for some data records(How many children can a person have?) Or, if there are enough columns toprovide for all thinkable cases, waste of much space (many NULL values).Second llerIris9SchmidtMartin17SchmidtRainer1836
Advantage:This requires just the right amount of space that is actually needed.Disadvantage:It requires an additional table. pName is redundantly stored.Second Normal Form (2NF)Definitions:Definition of second normal form (simple version):A relation is in 2NF, if it is in 1NF and every non-primary-key attribute is fullyfunctionally dependent on the primary key of the relation.Definition of second normal form (extended version):A relation is in 2NF, if it is in 1NF and every non-candidate-key attribute is fullyfunctionally dependent on every candidate key.Example:A University Database:TA (matNr, classId, sName, hours, taSalary)Full functional dependencies:{matNr, classId} {hours}{matNr, classId} {taSalary}{matNr} {sName}TA (matNr, classId, sName, hours, taSalary)Student (matNr, sName) TA is not in 2NF37
Redundancy since the name is repeated for every occurrence of the sameMatrikel Number.Solution:Move the dependency {matNr} {name} to a separate relation. Relation "Student"Third Normal Form (3NF)Definition:A functional dependency X- Y in a relation R is called a transitive dependency, ifR contains a set of attributes, Z for which holds:. A chain Exists.X- Z- Y. Y is not a part of primary key. Z is not a super key and. X- Z- YY is then called transitively dependent on X via Z.Definition of Third Normal Form:A Relation is in 3NF, if it is in 2NF and no non primary key attributes istransitively dependent on the primary key.Example:TA (matNr, classId, hours, taSalary)38
Functional dependencies:{matNr, classId} {hours}{matNr, classId} {taSalary}Assumption:{hours} {taSalary}There is the following transitive dependency:{matNr, classId} {hours} {taSalary}Since taSalary is not an attribute in a candidate key and hours is not a superkey,TA is not in 3NF.There is unnecessary redundancy since taSalary is repeated for eachoccur
Jul 12, 2010 · specific database system to work on. # Choosing Hospital Management System for our thesis We study and select three systems at first. The systems were Banking System Computer Sales Management System Hospital Management System We saw the demos of the respective
