09-23-2008, Tuesday Normalization / Converting E-R to TablesNormalization4NFConvert E-R to TablesMethodology – Logical DDBuild & ValidateRemove *:* Binary RelationshipRemove *:* RecursiveRemove Complex RelationshipsRemove Multi-valued Attributes1:* binary1:1 binarySuperclass/SubclassSummaryAlso Draw E-R (Emp-Proj-Man)Stolen Car DatabaseIs the table in 3NF ?DATA MODELSSlide 19Choose best solutionSlide 21End of LectureCS 8630 Database Administration, Dr. Guimaraes09-23-2008, TuesdayNormalization / Converting E-R to TablesClassWill Start Momentarily…CS8630 Database AdministrationDr. Mario GuimaraesCS 8630 Database Administration, Dr. GuimaraesNormalization•Normalization may be used as an alternative oras a supplement to E-R Diagrams.All non-key attributes of a table must be dependent on THE KEY, THE WHOLE KEY, and NOTHING BUT THE KEY.Normalization: helps maintenance (update, insert , delete).Not intended to speed up queries.CS 8630 Database Administration, Dr. Guimaraes4NF•Dependency between attributes (for example, A, B, and C) in a relation, such that for each value of A there is a set of values for B and a set of values for C. However, set of values for B and C are independent of each other.–Example Employee ->> Degree Employee ->> DependentsDon’t combine both tables.CS 8630 Database Administration, Dr. GuimaraesConvert E-R to Tables•1-N•N-N•1-1•1-1 Recursive•1-N Recursive•Super-Type & Sub-TypesCS 8630 Database Administration, Dr. GuimaraesMethodology – Logical DD•Build and validate local logical data model for each view –Remove features not compatible with the relational model (optional step)–Derive tables for local logical data model–Validate tables using normalization–Validate tables against user transactions–Define integrity constraints–Review local logical data model with user•Build and validate global logical data model–Merge local logical data models into global model–Validate global logical data model–Check for future growth–Review global logical data model with usersCS 8630 Database Administration, Dr. GuimaraesBuild & ValidateRemove features not compatible with the relational model (optional step)•To refine the local conceptual data model to remove features that are not compatible with the relational model. This involves:–remove *:* binary relationship types; –remove *:* recursive relationship types; –remove complex relationship types; –remove multi-valued attributes.CS 8630 Database Administration, Dr. GuimaraesRemove *:* Binary RelationshipCS 8630 Database Administration, Dr. GuimaraesRemove *:* RecursiveCS 8630 Database Administration, Dr. GuimaraesRemove Complex RelationshipsCS 8630 Database Administration, Dr. GuimaraesRemove Multi-valued AttributesCS 8630 Database Administration, Dr. Guimaraes1:* binary•(3) 1:* binary relationship types–Entity on ‘one side’ is designated the parent entity and entity on ‘many side’ is the child entity.–Post copy of the primary key attribute(s) of parent entity into relation representing child entity, to act as a foreign key.–May create an association table (not common) to avoid nulls1:* unary: Add a column – FK (default) or association tableCS 8630 Database Administration, Dr. Guimaraes1:1 binary(4) 1:1 binary relationship types–(a) mandatory participation on both sides of 1:1 relationship; Combine both entities into one table–(b) mandatory participation on one side of 1:1 relationship; FK on side that is optional.Example: Nurse (1,1) is in charge of (0,1) CareCenterFK nurseid must be added to CareCenter–(c) optional participation on both sides of 1:1 relationship. Add FK on either side or 3rd tableCS 8630 Database Administration, Dr. GuimaraesSuperclass/SubclassNote: 1) Mandatory = Total or Complete. Optional: Partial or Incomplete.Nondisjoint = overlapping 2) The solution (Relations required) is not necessarily the best. It will also depend on client’s specific performance needs and what type of queries and updates occur more.CS 8630 Database Administration, Dr. GuimaraesSummaryCS 8630 Database Administration, Dr. GuimaraesAlso Draw E-R (Emp-Proj-Man)CS 8630 Database Administration, Dr. GuimaraesStolen Car DatabaseCS 8630 Database Administration, Dr. GuimaraesIs the table in 3NF ?CS 8630 Database Administration, Dr. GuimaraesDATA MODELS•Conceptual Model – data as viewed by client•Relational Model – data that is associated to a Relational Database (includes FK).•Physical Model – data associated with a specific hardware/software configuration. Associated with a specific DBMS.CS 8630 Database Administration, Dr. GuimaraesConvert E-R to Tables•1-N - add FK on child side (default)•N-N - create associate table (3rd table)•1-1 - 3 possibilities. Look at minimum cardinality for the best solution•1-1 Recursive 2 possib. Look at min. card.•1-N Recursive 2 possib. Look at min. card.•N-N Recursive – create associate table (2nd table)•Super-Type & Sub-Types – one table for super-typeand one for each sub-type is usually the defaultCS 8630 Database Administration, Dr. GuimaraesChoose best solutionCS 8630 Database Administration, Dr. GuimaraesCS 8630 Database Administration, Dr. GuimaraesEnd of LectureEnd