MIDTERM DBF REVIEWER.pdf

Three Main Rules for Normalizing Data NORMALIZATION - Eliminate repeating data  This rule is all about disallowing...

Three Main Rules for Normalizing Data NORMALIZATION - Eliminate repeating data  This rule is all about disallowing multivalued attributes Functional Dependency - Eliminate partial dependencies - Describes the relationship between attributes  This refers to situations where the (columns) in a table. primary key for a table is a composite primary key, which means a key Classifications: composed of multiple columns. - Eliminate transitive dependencies Full Functional Dependency  This refers to situation where a column - Indicates that if A and B are attributes (columns) in the table refers to non-key column of a table, B is fully functionally dependent on A instead of the primary key of a table. if B is functionally dependent on A, but not on Normal Forms any proper subset of A. - First Normal Form (1NF) Partial Functional Dependency  States that the domain of an attribute - Indicates that if A and B are attributes of a table, must include only atomic values and B is partially dependent on A if some attributes that the value of any attribute in a row can be removed from A, and yet the dependency must be a single value from the domain still holds. of that attribute. - Second Normal Form (2NF) Transitive Functional Dependency  A relation in the second normal form 2NF if and only if it is in 1NF and every - Is a condition where A, B, and C are attributes non-key attribute is fully dependent on of a table such that, if A is functionally the primary key. dependent on B and B is functionally dependent  It is accomplished by eliminating partial on C then C is transitively reliant on A via B. dependencies  To put a table in 2NF requires: 1. Identify the primary key. Database Normalization 2. Determine the relationship between the parts of the key and the data. Normalization - Third Normal Form (3NF) - Is the process of organizing data to minimize  A relation is in 3rd normal form if and redundancy in the design of a relational database only if it is in 2NF and every non-key management system (RDBMS). attribute is non-transitively dependent - Refers to a series of recommended steps taken to on the primary key. remove redundancy and update anomalies from a database design. Goals of Normalization - Eliminate redundant data. - Eliminate insert, delete, and update anomalies. Normal Forms - Boyce-Codd Normal Form (BCNF)  Handles a problem with an entity that might have two primary keys.  Named after Ry Boyce & Edgar Codd - Fourth Normal Form (4NF)  A relation is in the 4NF if and only if a multi-valued dependency (MVD) exists in a table then all attributes of the table are also functionally dependent on A. - Fifth Normal Form (5NF)  Is also called the projection-join normal form (PJ/NF).  A relation is in the 5NF if and only if every join dependency in the relation is a consequence of the candidate keys of the relation. Alternative to Normal Forms - Defines data entities in a way that supports the view of the decision maker in a business, as well as the data entities that reflect the important operational aspects of a business. ORACLE NORMALIZATION PDF CLICK THIS TO OPEN! SQL Joins - An SQL JOIN clause is used to combine records from two or more tables in a database, based on a common field between them. - Other keywords are combines with the SELECT statement, these keywords are:  Inner join - The RIGHT JOIN keyword returns all rows  Outer join from the right table (Table B), even there are no o Left join matches in the left table (Table A). o Right join o Full join Inner Join - The INNER JOIN keyword return rows when there is at least one match in both tables. - The FULL JOIN, both tables are optional, such that if rows are being matched in table A and table B, then all rows from table A are displayed even if there is no matching row in table B, and vice versa. - Basic syntax: Entity – Relationship Diagram (ERD) ER Model Outer Join - An OUTER JOIN is an extension of INNER JOIN, but it does not require each record in the two joined tables to have a matching record. - The joined table retains each record-even if no other matching record exists. Entity Relationship Model - Outer join subdivide further into:  Left outer join  Introduced by Perter Chen in 1976  Right outer join  Yields a graphical representation of entities and their relationship in database structures.  Full outer joins, depending on which table/s one retains the rows from (left,  Usually represented by an entity relationship right, or both). diagram. - The LEFT JOIN keyword returns rows from  Introduce three types of relationships: the left table (Table A), even there are no o One – to – many relationship matches in the right table (Table B). (1:*)  o Many – to – many relationship (*:*) o One – to – one relationship (1:1) Based on the following components: Entity  Entity instance – person, place, object, event, Cardinality concept (often corresponds to a row in a table.)  Entity type – collection of entities (corresponds - Specifies how many instances of an entity relate to a table) to one instance of another entity. Attributes Cardinality Notations  Relationship instance – link between entities - Crow’s Foot Notation (corresponds to primary key – foreign key equivalencies in related tables).  Relationship type – category of relationships… link between entity types. Relationship  Properties or characteristic of an entity or relationship type (A field in a table). Relationship Dependencies Mandatory Relationship - Bachman Style - Specifies that for every occurrence of entity A, there must exist an entity B, and vice versa. Optional Relationship - Specifies that it is not necessary for every entity occurrence to participate in the relationship. Entity – Relationship Diagram - Martin Style - Can express the overall logical structure of a database in a graphical way. - Components of an ER Diagram: Relationship Degree