1 Entity relationship diagramFigure 1: entity relationship diagram2 Normalization Normalization involves eliminating data redundancy thus enforcing data integrity on a relational database through restructuring the database through the following stages;First normal form (1NF) - Normalization to first normal form involves removing all repeating groups and decomposing the relations in UNF to a set of relations is 1NF.
Second Normal Form (2NF) - Normalization to second normal form involves taking the relations achieved in normalizing to 1NF and the removing all partial dependencies to obtain relations that are in 3NF.Third Normal Form (3NF)- Normalization to third normal form involves taking the relations obtained in second normal form and eliminating all the transitive dependencies to obtain relations in 3NF.To describe the normalization process, the first step is to identify relations in UNF.Criminals (Name, address, phoneNO, violentOffenderStatus, probationStatus, aliases)Crime (classifification, dateCHarged, appealStatus, hearingDate, appealCutOffDate,CrimeCodes, criminals,AmountFile,charges,CourtFile,amountPaid, paymentDate,chargesStatus,arrestingOfficers)Appeal(appealFilingDate,appealhearingDate, status, noOfTImesAppeal)Sentencing (startDate, endDate, numberOfViolations, typeOfSentence)PoliceOffice(Name, precint, badgeNumber, phoneContact, Status)Criminals TableUNFCriminals (Name, address, phoneNO, violentOffenderStatus, probationStatus, aliases)1NFThe name attribute is a composite attribute and can be split to firstName and lastName.Criminals (firstName, lastName, address, phoneNO, violentOffenderStatus, probationStatus, aliases)2NFPartial dependencies exist in the relation because there is no unique attribute thus to eliminate the partial dependencies a primary key criminalNumber is introduced.Criminals (CriminalNumber,FirstName,lastName, address, phoneNO, violentOffenderStatus, probationStatus, aliases)3NFCriminals (CriminalNumber,FirstName,lastName, address, phoneNO, violentOffenderStatus, probationStatus, aliases)CrimeCase TableUNFCrime (classifification, dateCHarged, appealStatus, hearingDate, appealCutOffDate,CrimeCodes, criminals,AmountFile,charges,CourtFile,amountPaid, paymentDate,chargesStatus,arrestingOfficers)1NF
End of preview
Want to access all the pages? Upload your documents or become a member.
Related Documents
Tables in Un-normalized Form as per Case Studylg...
|7
|1155
|13
ERD, Business Rules, 3NF Relations and Data Dictionary | Deskliblg...
|28
|4977
|359
Design and Normalization of a Database for a Sports Clublg...
|27
|5046
|218
Introduction to Database | Relational Data Structureslg...
|14
|1570
|26
Achieve Scheme Database Analysis and Designlg...
|9
|2172
|56
Enhanced Database Management Strategy for Multinational Tour Operator Agencylg...