DBMS-Week-4A-Functional Dependencies-Normalization - WEEK...

This preview shows page 1 - 11 out of 44 pages.

WEEK4A SHAILESHAGARWAL, PH.D. 1
Review of last class Data Definition Language Data Types Create, Alter, Drop Table Indexes Constraints Triggers 2
Agenda Relational Database Design Functional Dependencies Relational Design Anomalies Normal Forms Converting to Normal Forms 3
Application Model Relational Database Design Database design is driven by applications using this data 4 Application Usage Model Services & Functionality Specification Data Persistence, Access & Update Specification Conceptual / Logical Data Model (E/R or UML) Physical Data Model
Relational Database Schema Database schema is a physical implementation of a conceptual / logical data model Database schema is informed by Inherent database schema elements supported by a particular DBMS (depends on database model and vendor) Types of data need to be stored, accessed & managed Relationships and dependencies between data elements Types of query and update operations that may be required by various applications Volume, complexity and velocity of data that needs to be managed 5
Relational Model Relations are a major building block of the Relational Model A Relation is a way to represent data in the form of a 2-dimensional table with columns and rows (e.g. INVENTORY) In Relational model, row is formally referred to as a Tupleand columns are the Attributesof a tuple Each tuple in a relation represents an instanceof the item represented by the relation Name of a relation and the set of attributes are referred to as the schemafor that relation 6
Functional Dependencies The actual specification of attributes for a given relation is drive by an analysis of “Functional Dependencies” amongst attributes of the proposed relation A “Functional Dependency” is a constraint between two attributes or two sets of attributes for a given relation For any relation R, attribute B is functionally dependent on attribute A if for every valid instance of A, that value of A uniquely determines value of B 7
Functional Dependencies A functional dependency (FD) is represented as A → BNote that an FD is not a mathematical dependency. B cannot be computed from A. Rather, based on real world data, if you know A there can be only one value of B Both A and B can be sets of attributes 8
Relational Model Design Theory Functional Dependency” is a generalization of the idea of a “Key” for a RelationUsing this notion of functional dependencies, we can define various normal forms for a relational schema This is known as “Normalization”, a design theory for Relational models 9
Relational Model Design Theory A relation may have several functional dependencies some of whom may be undesirable since they lead to anomalies and redundancies The normalization process is to successively decompose relations into two or more relations until all undesirable anomaliesand redundanciesare removed to yield a normalized database schema 10

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture