1Spring 2011Normalization is a foundation for relational database design2The goal of database normalization is to ensure that every non-key column in every table is directly dependent on the key, the whole key and nothing but the keyThe issue of keysCandidate and Primary Keysa set of one or more attributes that uniquely identifies a specific instance of an entityCandidate key any subset of the attributes of a superkey that is also a superkey and not reducible to another superkey key a selection from the
-used to index a relationDog registry: (dog_name, owner_name, address, phone)Field test: (field#, year, crop)Catalog Order: (catalog_item #, customer #, billing_address, shipping_address)Every relation (entity) must have a primary keyTo qualify as a primary key, an attribute must have the following properties: it must have a non-null value foof the entity the value must be unique for each instance of an entity Keysthe values must
become null during the
each entity instance Often more than one attribute is required to uniquely identify an entity.A primary key made up of more than one attribute is known composite key.Student #Student Y in relational table R is fully functionally on X of R if it is functionally dependent on X and not functionally dependent upon any subset of X.Full Functional DependenceApplies to tables with composite keysFull functional dependence means that when a primary key is composite, then the other columns must be identified by the entire key and not just some of the columns that make up the key.
Foreign KeysA foreign key identifies a column or a set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table.Columns in the referencing table must be the primary key or other candidate key in the referenced table.
Foreign keys provide a method for maintaining integrity in the data (called referential integrity) and for navigating between different instances of an entity.Aforeign key completes a relationship by identifying the parent entity.Every relationship in the model must be supported by a foreign key.Steps in NormalizationAssemble data items from user viewsConvert to un-normalized relationsConvert to first normal form (1NF)Convert to second normal form (2NF)Convert to third normal form
(3NF)Should result in simple relations that correspond to entities or associations between entity classesNormalized tables when recombined (joined), should convey exactly the same information as the original table.Un-Normalized RelationsUn-normalized relations contain one or more repeating groups multiple values at the intersection of rows and columnsStudent Student MajorCourse Course titleInstructor nameInstructor LocationGrade38214BrightISIS 350IS 465DatabasesSystem AliKempl69173JonesPMIS 465PM 300QM 440System AnalysisProd MangOp ResKempLewisKempContains redundant information e
.g IS 465 appears in more than one 4Un-Normalized RelationsStudent#Student rCourse#one-to-one relationshipone-to-one? relationshipone-to-many relationshipNameNeed to address the one-to-many relationshipsNormalized relations: First Normal FormA relation is in first normal form if the underlying domains contain only atomic valuesThtiithitlThere are no repeating groups within a tupMost relational systems require a database to be in 1NFFirst Normal FormStudent #Student repeating groups and form 2 new relations migrate the primary key, and assure there is a valid new primary keyStudent #Course #Course TitleInstructor nameInstructor LocationGrade38214IS 350DatabaseCoddB104A38214IS 465Sys AnalKempB213C69173IS 465Sys AnalKempB213A69173PM 300Op ResLewisD317BIdentification of Primary KeyStudent #Student #Course #Course TitleInstructor nameInstructor LocationGrade38214IS 350DatabaseCoddB104A38214IS 465Sys AnalKempB213C69173IS 465Sys AnalKempB213A69173PM 300Op ResLewisD317B5Insert anomalyInsertion of a new course cannot occur until a student has registered for the course since Student # is part of the composite keyStudent #Course #Course TitleInstructor nameInstructor LocationGrade38214IS 350DatabaseCoddB104A38214IS 465Sys AnalKempB213C69173IS 465Sys AnalKempB213A69173PM 300Op ResLewisD317BUpdate anomalyChanging a course title or course number requires searching all tuples to find every occurrence of a course
Sql Server Dba Topics - York Technical College
Explain different levels of normalization? ... used to provide fault tolerance to database servers. There are six RAID levels 0 through 5 offering different levels ... (fastanimals.com)
Database Normalization - Department Of Computer Science
DATABASE NORMALIZATION Normalization: process of efficiently organizing data in the DB. ↓ RELATIONS (attributes grouped together) ↓ Accurate representation of ... (yorktech.com)
Theory Of Relational Database Design And Normalization
Theory of Relational Database Design and Normalization ... - Two levels of relation schemas: - The logical "user view" level - The storage "base relation" level (dcs.vein.hu)
number or titleStudent #Course #Course TitleInstructor nameInstructor LocationGrade38214IS 350DatabaseCoddB104A38214IS 465Sys AnalKempB213C69173IS 465Sys AnalKempB213A69173PM 300Op ResLewisD317BDeletion anomalyDropping a single student from a course requires dropping the course and losing the associated course and instructor informationStudent #Course #Course TitleInstructor nameInstructor LocationGrade38214IS 350DatabaseCoddB104A38214IS 465Sys AnalKempB213C69173IS 465Sys AnalKempB213A69173PM 300Op ResLewisD317BFunctional DependenciesStudent #Course #Course TitleInstructor nameInstructor LocationGrade38214IS 350DatabaseCoddB104A38214IS 465Sys AnalKempB213C69173IS 465Sys OewBStudent #, Course # Course # Course TitleCourse # Instructor NameCourse # Instructor Location6Student #Course #Course TitleInstructor nameInstructor LocationGradeIS350CddSecond Normal FormA relation is in second normal form if it is in 1NF and every non-key attribute is fully dependent on the primary key38214IS DA38214IS 465Sys AnalKempB213C69173IS 465Sys AnalKempB213A69173PM 300Op ResLewisD317BCourse Title, Instructor Name and Instructor Location are partially dependenton the primary key (only on Course#) Second Normal FormTo convert from first to second normal form remove partial dependenciesStudent #Course #Grade38214IS 350A38214IS 465CCourses are independent of Student # and so can be inserted or deleted independently, only a Create 2 new relations, one with attributes fully dependent on primary key, other with attributes that were only partially dependent69173IS 465B69173PM 300CCourse #Course TitleInstructor NameInstructor LocationIS 350DatabaseCoddB104IS 465Sys AnalKempB213PM 300Prod manLewisD317QM 440Op ResKempB213single tuple needs to be updated in the course relationCourse #Course TitleInstructor NameInstructor LocationIS 350DatabaseCoddB104IS 465Sys AnalKempB213PM 300Prod manLewisD317QM 440Op ResKempB213Transitive dependenciesA non-key attribute is dependent on one or more no-Course TitleInstructor NameInstructor Locationone-to-one relationshipone-to-one relationshipCourse#key attributesSince instructor is dependent on Course # as primary key no information about an instructor can be added until an instructor has been assigned to a courseDeleteanomalyDelete Deleting data for a course results in deleting instructor information7Course #Course TitleInstructor NameInstructor LocationIS 350DatabaseCoddB104IS 465Sys AnalKempB213PM 300Prod manLewisD317QM440ORKB213Update anomalyQM OempTo update instructor information the entire relation must be searched since instructor information occurs more than once.Third Normal FormA relation is in third normal form if it is in 2NF and contains no transitive dependenciesEvery non-key attribute is fully dependent on the primary key and there are no transitive dependenciesInstructor NameInstructor Location Non-key attributes that #Course titleInstructor NameIS 350DatabaseCoddIS 465Sys AnalKempPM 300Prod MangLewisQM 440OP ResKempForeign key a non-key attribute in one relation that serves as a primary key in another relationparitransitive dependency form a new relationBoyce-Codd Normal FormOccurs in the case of overlapping candidate keysEach student can major in several subjectsFor each major a student has one advisorEach major has several advisorsEhdidiljStudent #Major advises only one majorThere are 2 possible candidate keys: Student #-Major or
Student# Advisor and they are overlapping.Attributes that are part of a candidate key are dependent on part of another candidate key.A relation is in Boyce-Codd normal form if it is in 3NF and there are no dependencies in candidate keysBoyce-Codd Normal FormStudent # #Major to project into 2 new relations8Fourth Normal FormRemoves multi-valued dependencieswhen 3 attributes (A, B, C) exist in a relation and for each value of A there is a well defined set of values for B and a well defined set of values for C, yet B and C are independent of each other,ypColor A record type should not contain two or more independent multi-valued facts about an entity.Fourth Normal exist in the relationCan generate deletion and to 2 relationsLeads to simpler (to implement) applications and to more maintainable systemsNormalization SummaryBased on a set of rules that define normal forms of which first three are most important:First normal form: All column values are atomic Second normal form: All column values depend on the whole primary key: no partial dependencies Third normal form: No column value depends on the value of any other column except the primary key no transitive dependencies Limits of NormalizationNormalization rules are guidelinesIn certain circumstances 3NF or higher may not be desirableCustomer (Name, Street, City, State, Zipcode)D3NFDoes not meet 3NFNeed to apply judgment and common senseCustomer(Name, Street, Zipcode)Location(Zipcode,City, State)3NF may not be efficient in terms of regular queries9Limits of NormalizationThere can be a number of cases where there is a compelling need for non first normal form structures.Spatial data objects is one of supports ability to implement non-first normal structuresnested tables1.E.F.Codd, "A Relational Model of Data for Large Shared Data Banks", Comm.ACM 13 (6), June 1970, pp.377-387.
The original paper introducing the relational data model.2.E.F.Codd, "Normalized Data Base Structure: A Brief Tutorial", ACM SIGFIDET Workshop on Data Description, Access, and Control, Nov.11-12, 1971, San Diego, California, E.F.Codd and A.L.Dean (eds.Referencesg()An early tutorial on the relational model and normalization.3.E.F.Codd, "Further Normalization of the Data Base Relational Model", R.Rustin (ed.), Data Base Systems (Courant Computer Science Symposia 6), Prentice-Hall, 1972.Also IBM Research Report RJ909.The first formal treatment of second and third normal forms.4.C.J.
Date, An Introduction to Database Systems (third edition), Addison-Wesley, 1981..
Tags: database normalization techniques,rules for database normalization,database normalization exercises,database normalization tutorial,database normalization practice,