Docstoc

DATABASE - PowerPoint

Document Sample
DATABASE - PowerPoint Powered By Docstoc
					              4123201
         Database Management
              System
1/2543         Database Management System   1
               อ.เจษฎาภรณ์ มะลิหอม
               Jassadaporn Malihom

         E-mail : Jase@thaiengineering.com




1/2543              Database Management System   2
                คาอธิบายรายวิชา
     ความรู ้เกี่ยวกับระบบฐานข้อมูล การประยุกต์ Link listed
ความสัมพันธ์ระหว่างRecord ในฐานข้อมูล การใช้ Key มากกว่า
1 Key ในการประมวลผล รู ปแบบRelation ระบบ Hierarchy
และระบบ Network การป้ องกันข้อมูล ระบบการสารองข้อมูล
และการเรี ยกคืน การวิเคราะห์ออกแบบข้อมูลการบริ หารข้อมูล


1/2543                 Database Management System       3
         Text Book :
         Peter Rob Carlos Coronel (1997)
         Database Systems
         Design, Implementation, and Management
         Reference :
         C.J. Date
         An Introduction to Database System

         Raghu Ramakrishnan (1998)
         Database Management Systems

1/2543              Database Management System    4
         Course Outline :
         I. Database Concept
                1. Introducing the database
                2. A file system Critique
                3. Database System
                4. Database Models
         II. The Relational Database Model
                1. A logical view of data
                2. Keys
                3. Integrity Rule Revisited
                4. Relational Database Operator
                5. Relational Database Classification
1/2543
                                 Within the Relational Database
                6. RelationshipDatabase Management System         5
         Course Outline :
         III. Entity Relationship (E-R) Modeling
                  1. Basic Modeling Concepts
                  2. Data Models : Degree of abstraction
                  3. The Entity Relationship (E-R) Model
                  4. Developing an E-R Diagram
         IV. Normalization of Database Table
                  1. Database Table and Normalization
                  2. Normalization and Database Design
         V. Database Design
                  1. Information system
                  2. The Systems Development Life Cycle (SDLC)
1/2543
                  3. The Database Life Cycle (DBLC)
                                Database Management System       6
 File Systems and Database


1/2543    Database Management System   7
         Chapter Objectives

           Understand   characteristics of a file system
           Understand   advantages of using a database
           Understand   characteristics of database system
           Understand   basic database concepts
           Understand   three different types of database models



1/2543                       Database Management System            8
         Chapter Overview
            1.1 Introducing the Database
            1.2 The Historical Roots of the
                Database : Files and File Systems
            1.3 A File System Critique
            1.4 Database Systems
            1.5 Database Models

1/2543                Database Management System    9
    Introducing the Database
       Major Database Concepts
         - Data (raw facts) from which the required information
           is produced.
         - Data management
           -> data collection, storage and retrieval
         - Efficient data management required the used of a
           computer database.
         - Database
           -> a shares, integrated computer structure that house a
           collection of : End user data & Metadata
         - Metadata (data about data)-data characteristic,relationship
1/2543
         - Database management system System
                                Database Management
                                                    (DBMS)               10
         Introducing the Database
         A DBMS is a software package designed to store
           and manage database
         Why Use a DBMS?
           - Data independence and effcient access.
           - Reduced application development time.
           - Data integrity and security.
           - Uniform data administration
           - Concurrent access, recovery from crashes.
1/2543                     Database Management System     11
     Introducing the Database
     Importance of DBMS
         - It help make data management more efficient and effective
         - Its query language allows quick answers to and ad hoc
            queries.
         - It provides and users better access to more and better-
            managed data.
         - It promotes an integrated view of organization’s operations--
           ‚big picture‛. (much easier to see how action in one segment
            of company will affect other segments)
         - It reduces the probability of inconsistent data.
1/2543                         Database Management System                  12
         Data Independence
         Applications insulated from how data is
           structured and stored.
         Logical data independence : Protection
           from changes in logical structure of data.
         Physical data independence : Protection
           from changes in physical structure of data.

         One of the most important benefits of using a DBMS!
1/2543                     Database Management System           13
         Concurrency Control
         Concurrent execution of user program is essential
            for good DBMS performance.
            - Because disk accesses are frequent, and relatively
               Slow, it is important to keep the cpu humming by
               working on several user programs concurrently
         Interleaving actions of different user programs can
            lead to inconsistency : e.g., check is cleared while
            account balance is being computed.
         DBMS ensures such problem don’t arise : users can
            pretend they are using a single-user system.
1/2543                       Database Management System            14
         Transaction : An Execution of a DB Program
         Key concept is transaction, which is an atumic sequence of
           database action (reads/writes).
         Each transaction, executed completely, must leave the DB in a
           consistent state of DB is consistent when the transaction begins.
           - Users can specify some simple integrity constraints on the
             data, and the DBMS will enforce these constraints.
           - Beyond this, the data. (e.g.,it does not understand how the
              interest on a bank account is computed).
           - Thus, ensuring that transaction (run alone) preserves
              consistency is ultimately the user’s responsibility!
1/2543                            Database Management System               15
 Scheduling Concurrent Transactions
 DBMS ensures that execution of (T1,…..,Tn) is equivalent to
   some serial execution T1’…Tn’.
   - Before reading/writing an object, a transaction requests a lock
      on the object, and waits till the DBMS gives it the lock. All
      locks are released at the end of the transaction. (Strict 2PL
      locking protocol.)
   - Idea : If an action of Ti (say, writing X) affects Tj (which
      perhaps reads X), one of them say Ti, will obtain the lock on
     X first and Tj is forced to wait until Ti completes; this
      effectively orders the transactions.
   - What if Tj already has a lock on Y and Ti later requests a lock
      on Y? (Deadlock!) Ti or Tj is aborted and restartes!
1/2543                      Database Management System                 16
         The Log
         The following actions are recorded in the log :
            - Ti writes an object : the old value and the new value.
               * Log record must go to disk before the changed page!
            - Ti commits/aborts : a log record indicaaing this action.
         Log records chained together by Xact id, so it’s easy to
              undo a specific Xact (e.g.,to resolve a dealock).
         Log is often duplexed and archived on ‚stable‛ storage.
         All log regated activities (and in fact, all CC related activities
            such as lock/unlock, dealing with deadlocks etc.) are handled
            transparently by the DBMS.
1/2543                           Database Management System                   17
         Database make these folks happy…
         End users and DBMS vendors
         DB application programmers
           - E.g. smart webmasters
         Database administrator (DBA)
           - Design logical / physical schemas
           - Handles security and authorization
           - Data availavbility , crash recovery
           - Datavbase tuning as needs evolve
         Must understand how a DBMS works!
1/2543                    Database Management System   18
         Structure of a DBMS
         A typical DBMS has a
           layered architecture.
         The figure does not
           show the concurrency
           control and recovery
           components.
         This is one of several
           possible architectures ;
           each system has its
           own variations.
1/2543              Database Management System   19
         Introducing the Database



1/2543           Database Management System   20
         Introducing the Database
         Why Database Is Important?
           - A poorly designed database is breeding ground for
               uncontrolled data redundancies.
           - Data redandant when the same data are kept in different
               location for the same entity.
           - A poorly designed database generates errors that lead to
               bad decisions.
           - (do not have access to timely, or event correct, information)
         Entity
           - a person, place or thing for which data are to be collected
               or stored,
1/2543                           Database Management System                  21
     Historical Roots
     Why Studying File Systems?
       - It provides historical perspective.
       - It teaches lessons to avoid pitfalls of data
          management
       - Its simple characteristics facilitate
          understanding of the design complexity of a database
       - It provides useful know ledge for converting a file
          system to a database system.
1/2543                     Database Management System            22
    Historical Roots




1/2543             Database Management System   23
         Historical Roots
         Data Processing (DP) specialist create necessary file
           structure, write program to manage data within those
           structure.
         Reports Prepared by DP Specialist Using the CUSTOMER
           file :
           - Monthly summaries of types and amounts of insurance
              sold by each agent
           - Monthly checks to determine which customers must be
              contacted for renewal
           - Analysis reports on ratios of insurance types sold by ecah
              agent
           - Customer contact letters with coverage summary and
              customer relation bonuses
1/2543                            Database Management System              24
 Historical Roots




1/2543              Database Management System   25
         Historical Roots




1/2543                      Database Management System   26
         Historical Roots




1/2543                      Database Management System   27
         Historical Roots




1/2543                      Database Management System   28
         Historical Roots
         File system is composed of 5 parts :
            - Hardware - computer
            - Software. The operating system, the utilities, the files,
              the file management programs, the application programs
              that generate reports from the data stored in the files :
            - People : DP managers, DP specialists, programmers and
              end users.
            - Procedures : The instructions and rules that govern the
              design and use of the software component.
            - Data : The collection of facts :
1/2543                           Database Management System               29
         File System Critique
         File System Data Management
            - File systems require ectensive programming in 3GL.
            - As file systems become more complex, managing files
              gets more difficult.
            - Making changes in existing file structures is important
              and difficult.
            - Data access programs are subject to change with file
              Structure changes (structural dependince).
            - Security features diffucult to implement and are
              lacking.
1/2543                            Database Management System            30
         File System Critique
         Structure and Data Dependence
           - Structure Dependence
                A change in any file’s structure requires the
                 modification of all programs using that file.
           - Data Dependence
                A change in any file’s data characteristics requires
                changes of all data access programs.
           - Data dependence makes file system extremely
              cumbersome from a programming and data
              management point of view.
1/2543                           Database Management System            31
         File System Critique
         Field Definitions and Naming Conventions
           - A good (flexible) record definition anticipates
             reporting requirements by breaking up fields
             into their components.
                * Example :
                    - Customer Name * Last Name, First Name, Initial
                    - Customer Address * Street Address, City, State
           - Selecting proper field names is very important.
                *Descriptive
                *Self-documenting
1/2543                           Database Management System            32
         File System Critique
         Data Redundancy :
           Uncontrolled data redundancy sets the syage for
             - Data Inconsistency (lack of data intrgrity)
             - Data anomalies (abnormality)
               * Modification anomalies
               * Insertion anomalies
               * Deletion anomalies


1/2543                        Database Management System     33
         Database Systems
         Type of Database Systems
         - Location
           * Centralized
           * Distributed
         - Use
           * Transactional (Production)
           * Decision support
           * Data warehouse
1/2543            Database Management System   34
     Database Systems
     DBMS Functions
     - Data Dictionary Management
        * Data dictionary stores definitions of the data elements and their
          relationships (metadata)
        * It provides data abstraction and removes structural and data
          dependency from the system.
     - Data Storage Management
        * DBMS creates data storage structure and relieves us from the
          task of defining and programming physical data characteristics.
1/2543                        Database Management System                35
         Database Systems
         DBMS Functions
         - Data Transformation and Management
            * DBMS relieves us from the chore of making
              distinction between logical format and physical
              format of data.
         - Security Management
            * DBMS provides user security and data privacy
              within the database.
            * Data security is especially important in multi-user
1/2543
              database         Database Management System           36
   Database Models
   # A database model is a collection of logical constructs
     used to represent the data structure and the data
     relationships found within the database.
   # Two Categories of Database Models
       - Conceptual models focus on the logical nature of the data
         representation. They are concerned with what is represented
         rather than how it is represented.
       - Implementation models place the emphasis on how the data
         are represented in the database or on how the data structures are
1/2543
         implemented.           Database Management System               37
         Data Models
         # A schema is a description of a particular collection of
           data, using the a given data model.
         # The relational model of data is the most widely used
           model today.
           - Main concept : relation, basically a table with
             rows and columns.
           - Every relation has a schema, which describes the
             columns, or fields.
1/2543                     Database Management System          38
         Levels of Abstracton
         # Many views, single                              view1        View 2         View 3
           conceptual (logical) schema
           and physical schema.                                    Conceptual Schema
           - Views describe how users
             see the data.                                         Physical Schema
           - Conceptual schema defines
             logical structure
           - Physical schema describes
             the files and indexes used.
         * Schemas are defines using DDL ; data is modified2queried using DML.
1/2543                             Database Management System                             39
         Database Models

         # Three Types of Relationships in Conceptual Database
           Models
           - One-to-many relationships




1/2543                    Database Management System      40
         Database Models

         # Three Types of Relationships in Conceptual Database
           Models
           - Many-to-many relationships




1/2543                    Database Management System        41
         Database Models

         # Three Types of Relationships in Conceptual Database
           Models
           - One-to-one relationships




1/2543                    Database Management System        42
         Database Models

         # Three Types of Implementation Database Models
           - Hierarchical database model
           - Network database model
           - Relational database model



1/2543                  Database Management System     43
         Database Models
         # Hierarchical Database Model
         - Background
          * GUAM (Generalized Update Access Method) was developed by
            North American Rockwell.
          * It conformed to upside-down tree structure-- hierarchical structure.
          * Information Management System (IMS) -- Jointly developed by IBM
            and Rockwell.
          * Hierarchical database model concepts for the basis for subsequent
            database development.
          * Its limitations lead to a different way of looking at database design.
1/2543                           Database Management System                    44
         Database Models
         # Hierarchical Database Model
           - Basic Structure
            * Collection of records percieved as organized to conform to
              the upside-down tree structure.
            * A tree structure is represented as a hierarchical path on the
              computer’s storage media.
            * One-to-Many (1:M) Relationship
              - Each parent can have many children.
              - Each child has only one parent.
1/2543                          Database Management System               45
         Database Models
         # Hierarchical Database Model
           Advantages
           * Data sharing and security provision
           * Data independence -- Reduced programming and
             maintenance effort
           * Database integrity
           * Efficiency dealing with a large database
           * Large installed (mainframe) base
           * Abundant business applications
1/2543                     Database Management System       46
         Database Models
         # Hierarchical Database Model
          - Disadvantages
           * Requirement of knowledge of physical level of data storage
           * Inability of represent relationships that do not conform to
             the hierarchical 1:M standard
           * Complex and inflexible to manage
           * Time consuming and complicated application programming
           * Lack of ad hoc query capability for end users
           * Lack of standard concepts and implementation -- limited portability
           * Requirement of extensive programming activities
1/2543                            Database Management System                 47
         Database Models
         # Network Database Model
          - Record can have more than one parent.
          - Background
            * CODASYL (COnference on DAta SYstems Language) group
              created DataBase Tasks Group (DBTG) in 1971
            * DBTG specified three crucial database components :
            - Network schema defines the conceptual organization of the entire database
              as viewed by the database administrator.
            - Subschema defines the portion of the database as seen by the applications
              programs.
            - Data Management Language defines the data characteristics and the data
              structure and to manipulate the data.
1/2543                            Database Management System                       48
         Database Models
         # Network Database Model
          - Background
           * Three DBTG data management language components :
             - Schema Data Definition Language (DDL)
             - Subschema Data Definition Language
             - Data Manipulation Language
           * ANSI SPARC (Standards Planning And Requriments
             Committee) augmented the database standards in 1975.
1/2543                      Database Management System              49
         Database Models
         # Network Database Model
           - Adventages
             * Easier implementation of M : N relationships
             * Superior data access type and flexibility
             * Enforced data integrity
             * Sufficient data independence

1/2543                     Database Management System         50
         Database Models

         # Network Database Model
           - Disadvantages
             * Difficult to design and use properly
             * Difficult to make changes in a database
             * Very complex structure from the application
               programmer’s point of view
             * Complex navigational data access environment
1/2543                 Database Management System         51
         Database Models
         # Relational Database Model
          - Background
            * E.F.Codd developed the relational model in 1970.
              - Conceptually simple but versatile
              - Major breakthrough for both users and designers
            * Requires more computing power
              - Considered impractical in the 1970’s.
              - Modern computers (even PCs) are powerful enough to
                handle relational databases.
1/2543                         Database Management System            52
         Database Models
         # Network Database Model
          - Basic Structure
            * Set - A relationship is called a set. Each set is composed of
              at least two record types : an owner (parent) record and a
              member (child) record.



1/2543                          Database Management System                    53
         Database Models

         # Network Database Model
           * Relationships among the records are decomposed into a
             series of sets.




1/2543                         Database Management System        54
         Database Models

         # Network Database Model
          - Advantages
            * Easier implementation of M:N relationship
            * Superior data access type and flexibility
            * Enforced data integrity
            * Sufficient data independence

1/2543                Database Management System          55
         Database Models

         # Network Database Model
          - Disadvantages
            * Difficult to design and use properly
            * Difficult to make changes in a database
            * Very complex structure from the application programmer’s
              point of view
            * Complex navigational data access environment
1/2543                    Database Management System             56
         Database Models
         # Relational Database Model
          - Background
           * E.F.Codd developed the relational model in 1970.
            - Conceptually simple but versatile
            - Major breakthrough for both users and designers
           * Requires more computing power
            - Considered impractical in the 1970’s
            - Modern computers (even PCs) are powerful enough to
              handle relational databases.

1/2543                    Database Management System               57
         Database Models
         # Relational Database Model
           - Basic Structure
            * Relational DataBase Management System (RDBMS)
              - RDBMS allows user2designer operate in a human logical environment
              - Relational database is perceived by the user as a collection of tables in
                which data are stored.
              - Each table consists of series of row2column intersections
              - Tables (or relations) are related to each other by sharing a common
                entity characteristic.
              - The relationship type is often shown in a relational schema.
              - A table yields complete data and structural independence because it is
                a purely logical structure.
1/2543                              Database Management System                        58
         Database Models

         # Relational Database Model



1/2543         Database Management System   59
         Database Models
         # Relational Database Model
          - Advantages
            * Data independence and structural independence
           * Easy to design the database and to manage its contents
           * Less programming effort required
           * Powerful and flexible query capability :
              - Structured Query Language (SQL)
                >> Fourth Generation Language (4GL)
                >> Specify ‚what to do‛ not ‚how to do’
                >> Introduced by IBM in 1974
                >> 3 parts : (1) User interface, (2) Set of tables, (3) SQL engine
1/2543                           Database Management System                          60
         Database Models

         # Relational Database Model
           - Disadvantages
             * RDBMS requires substantial hardware and
               operating system overhead.
             * It tends to be slower than other database systems.

1/2543                      Database Management System              61
The Relation Database
       Model
Chapter Objectives
 Understand the relational model’s logical
 structure
 Understand components of a relational database and
 their characteristics and relationships
 Understand basic concepts of table design
 Understand characteristics of good and bad tables
Chapter Overview
    2.1 A Logical View of Data
    2.2 Keys
    2.3 Integrity Rules Revisited
    2.4 Relational Database Operators
    2.5 Relational Database Software Classification
    2.6 The Data Dictionary and the System Catalog
    2.7 Relationships within the Relational Database
    2.8 Relationships within the Relational Database
    Data Redundancy Revisited
    2.9 Indexes
Why Syudy the Relational Model?
 Most widely used model.
   - Vendors: IBM, Informix, Microsoft, Oracle, Sybase, etc.
 “Legacy system” in older models
   - e.g., IBM’s IMS
 Recent competitor:Object-Oriented model
   - ObjectStore, Versant, Ontos
   - a synthesis emerging: object-relational model
     Informix Universal Server, UniSQL, 02
Relational Database : Definitions
 Relational database : a set of reltions.
 Relation : made up of 2 parts:
   - Instance : a table, with rows and columns.
      #rows = cardinality, #fields = degree / arity
    - Schema : specifies name of relation, plus name and type of
      each column.
       *E.g. Students(sid:string, name: string, login : string.
        Age : integer, gpa : real)
 Can think of a relation as a set of rows or tuples. (i.e.,all rows are distinct)
Example Instance of Students Relation
     Sid        name         login         age      gpa
    53666       Jones      Jones@cs        18       3.4
    53688       Smith      smith@eecs      18       3.2
    53650       Smith      smith@math      19       3.8

  * Cardinality = 3, gegree = 5 , all rows distinct
  * Do all columns in a relation instance have to be distinct ?
Creating Relations in SQL
 Creates the Students             CREATE TABLE Students
 relation. Observe that the type       (sid: CHAR(20),
 (domain) of each field is              name : CHAR(20),
 specified, and enforced by the         login : CHAR(10),
 DBMS whenever tuples are               age : INTEGER,
 added or modified.                     gpa : REAL,
 As another example, the          CREATE TABLE Enrolled
 Enrolled, table holds                  (sid : CHAR(20),
 information about courses that          cid : CHAR(20),
 students take.                          grade : CHAR(2)),
Adding and Deleting Tuples
 Can insert a single tuple using:
    INSERT INTO Students ( sid, name, login, age, gpa)
    VALUES (53688,’Smith’,’smith@ee’,’18,3,2)
 Can delete all tuple satisfying some condition ( e.g., name = Smith)
          DELETE
          FROM Student S
 WHERE S. name = ‘Smith’
 Powerful variants of these commands are available; more later!
A Logical View of Data
  Relational database model’s structural and data
  independence enables us to view data logically rather than
  physically.
  The logical view allows a simpler file concept of data
  storage.
  The use of logically independent table is easier to
  understand.
  Logical simplicity yields simpler and morw effective
  database design methodologies.
A Logical View of Data
 Entities and Attributes
   - An entity is simple a person, place, event, or thing for which we intend to collect data.
      * Examples;
         - University -- Students, Faculty Members, Courses
         - Airline -- Pilots, Aircraft, Routes, Suppliers
    - Each entity has certain characteristics known as attributes.
      * Examples:
        - Student -- Student Number, name,GPA, Dateof Enrollment, Data of Birth, ome
 Address, Phone Number, Major
        - Aircraft -- Aircraft Number,Data of Last Maintenance, Total Hours Flown, Hours
 Flown since Last Maintanannce
A Logical View of Data
Entities and Attributes
 - A grouping of related entities become an
   etity set.
  * Examples:
     - The STUDENT entity set contains all student entities.
     - The FACULTY entity set contains all faculty entities.
     - The AIRCRAFT entity set contains all aircraft entitites.
A Logical View of Data
A Logical View of Data
  Table and Their Characteristics
     - A table contains a group of related entities -- I.e.
  an entity set.
    - The terms entity set and table are often used
  interchangeably.
    - A table is also called a relation.
A Logical View of Data
  Characteristics of a Relational Table
    - A table is perceived as a two-dimensional stucture composed of rows and
  columns.
    - Each table row (tuple) represents a single entity within the entity set.
    - Each column represents an attibute and each column has a distinct name.
    - Each row/ column intersection represents a single data value.
    - Each table must have a primary key that uniquely indentifies each row.
A Logical View of Data
   Characteristics of a Relational Table
       - All values in a column must conform to the same data format.
       - Each column has a specific range of values known as the
   attribute domain.
        - Each row carries information describing one entity occurrence.
   The order of the rows and columns is immaterial to the DBMS.
A Logical View of Data
KEYs
 A key is a device that helps define entity relationships.It is an
 attribute that determines the values of other attributes within the
 entity.
 The key’s role is based on a concept known as determination,
 which is used in definition of functional dependence.
 The attribute B is functionally dependent on if A determines B.
 An attribute that is part of a key is known as a key attribute.
 A multi-attribute key is known as a composite key.
Keys
                         Table 2.2 Relational Database Keys
Superkey          Anattribute(or combination of attributes) that uniquely identifies
                  each entity in a table.
Candidate Key A minimal superkey. A superkey that does not contain a subset of
                   attributes that is itself a superkey.
Primary Key A candidate key selected to uniquely identify all other attribute
                   values in any given row. Cannot contain null entities.
Secondary Key An attribute (or combination of attribtues) used strictly for data
retrieval purposes.
Foreign Key             An attribute (or combination of attributes) in one table whose values
                  must either natch the primary key in another table or be null.
Primary Key Constraints
 A set of field is a key for a relation if :
   1. No two distinct tuples can have same values in all key
       fields, and
   2. This is not true for any subset of the key.
    - part 2 false? A superkey.
    - If there’s>1 key for a relation, one of the keys is chosen
      ( by DBA ) to be the primary key.
 E.g., sid is a key for Students. (What about name?) The set
  {sid, gpa} is a superkey.
Key
  STUDENT Table
  (Stu_num, Stu_Lname, Stu_Init,
    Stu_Hrs, Stu_Class, Stu_Gpa, Stu_phone,
    Stu_Dob, Stu_transfer, Dept_Code, Prof_Num)

  SUPERKEY
       Stu_num
       Stu_num, Stu_Lname
       Stu_num, Stu_Lname, Stu_Init
Primary and Candidate Keys in SQL
 Possibly many candidate keys (specified using UNIQUE), one of which
 is chosen as the primary key.
Integrity Rules Revisited
Integrity Constraints (ICs)
Foreign Keys, Referential Integrity
Foreign Keys in SQL
Relational Database Operators
 UNION combines all rows from two tables. The two tables
 must be union compatible.
Relational Database Operators
  INTERSECT produces a listing that contains only the rows
  that appear in both tables. The two tables must be inion
  compatible.
Relational Database Operators
  DIFFERENCE yields all rows in one table that are not found in the
  other table ; I.e., it subtracts one table from the other. The tables must
  be union compatible.
Relational Database Operators
  PRODUCT produces a list of all possible pairs of rows from two
  tables.
Relation Database Operators
  SELECT yields values for all attributes found in a table. It yields a
  horizontal subset of a table.
Relation Database Operators
  PROJECT produces a list of all values for selected attributes.It
  yields a vertical subset of a table.
Relation Database Operators
  JOIN allrows us to combine information from two or more
  tables.JOIN is the real power behind the relational database,
  alloeing the user of independent table linked by common
  attributes.
Relational Database Operators
 - Natural JOIN links tables by selecting only the rows with
 comman values in their common attribute(s). It is the resuil of a
 three-stage process.
 - A PRODUCT is performed on two tables.
      A SELECT is performed to yield only the rows for which the
 common attribute values match.
      A PROJECT is performed to yield a single copy of each
 attribute .thereby eliminating duplicate column.
Relational Database Operators
 - EquiJOIN links table based on an equality condition that compares
 specified columns of each table.The outcome of the EquiJOIN does not
 eliminate duplicate columns and the condition or criteria to join the table
 must be explicitly defined.
 - ThetaJOIN id an equiJOIN that compares specified columns of each table
 using a comparison operator other than the equality
 - In an OuterJOIN the unmatched pairs would be retained and the values for
 the unmatched other tables would be left blank or null.
Relational Database Operators
 DIVIDE requires the use of one single-column tables and one two-
 column table.
Relational Database Software Classification
  Not all relational DBMSs are equal some are more
  relational than others
  The degree to which DBMS conforms to the fully
  relational model affects database design and
  implementation effort.
  It is important that a relational DBMS should at least
  enforce the integrity rules.
Relation Database Software Classification
The Data Dictionary and the System Catalog
  Data dictionary contains metadata to provide detailed accounting of all
  tables found within the database.
  System catalog is a very detailed system data dictionary. It describes all
  objects within the database.
       - System catalog is a system-created database whose tables store the
  database characteristics and contents.
        - System catalog tables can be queried just like any other tables.
        - System catalog automatically produces database documentation.
Enforcing Referential Integrity
Where do ICs Come From?
Relational Database Operators
Relationships within the Relational Database
COURSE
CLASS
Relationships within the Relational Database
STUDENT
Relationships within the Relational Database
Relationships within the Relational Database
Relationships within the Relational Database
DATA Redundancy Revisited
Data Redundancy Revisited
Indexs
  An index is composed of an index key and a set of pointers.
         Entity Relationship
           (E-R) Modeling


1/2543         Database Management System   118
         Chapter Objectives
            Understand basic modeling concepts
            Understand the entity relationship model
            Learn how to develop an E-R disgram



1/2543                    Database Management System    119
Chapter Overview
    4.1 Basic modeling Concepts
    4.2 Data Models:Degrees of Data Abstraction
    4.3 The Entity Relationship (E-R)Model
    4.4 Developing an E-R Diagram
    4.5 A Comparison of E-R Modeling Symbols
     4.6 The Challenge of Database Design:Conflicting Goals


1/2543                  Database Management System    120
 Basic Modeling Concepts
 Database design is both art and science.
  A data model (Webster’s Dic.)is a discription or analogy used to
 visualize something that can not be directly observed
  A data model is the relatively simple
        represention,usually graphic,of complex real-world data
        structures.It represents data structures and their
        characteristics,relations,constraints,and transformations.
1/2543                   Database Management System           121
     Basic Modeling Concepts
      The database designer usually employs data models as
     communications tools to facilitate the interactions among the
     designer,the applications programmer,and the end user.
      A good database is the foundation for good applications.
      We can not develop good applications without a good database
     design
      The good database design begins with building a good data model


1/2543                     Database Management System           122
            Different information users and
         producer often reflect the”blind men
                and elephant”analogy.



1/2543                Database Management System   123
          Data Models:Degrees of Data Abstraction
            ANSI/SPARC’s Three Data Models
           (Based on their levels of abstraction)
         - Conceptual Model
         - External Model
         - Internal Model
1/2543                Database Management System    124
         Data Models:Degrees of Data Abstraction




1/2543                 Database Management System   125
  Data Models:Degrees of Data Abstraction
  The Conceptual Model
  -The conceptual model represents a global view of the data. It is an
  enterprise-wide representation of data as viewed by high-level
  managers.
  -Entity-Relationship(E-R)model is the most widely used conceptual
  model.
  -The conceptual model forms the basis for the conceptual schema.
  -The conceptual model is independent of both software(software
  independence)and hardware(hardware independence).
1/2543                     Database Management System             126
         Data Models:Degrees of Data Abstraction




1/2543                 Database Management System   127
         Data Models:Degrees of Data Abstraction




1/2543                 Database Management System   128
 Data Models:Degrees of Data Abstraction
 The Conceptual Model
 -The internal model adapts the conceptual model to a
 specific DBMS(e.g., hierarchical,network,and relational).
 -The internal model is software-dependent.
 -Development of the internal model is especially important
 to hierarchical and network database models.
1/2543                  Database Management System            129
         Data Models:Degrees of Data Abstraction




1/2543                 Database Management System   130
              Data Models:Degrees of Data Abstraction
         The External Model
         - The external model is the end user’s view of the
            data environment
         - Each external model is then represented by is
           own external schema
          CREATE VIEW CLASS_VIEW AS SELECT (CLASS_ID, CLASS_NAME,
         PROF_NAME, CLASS_TIME, ROOM_ID) FROM CLASS, PROFESSOR,
         ROOM WHERE CLASS.PROF_ID = PROFESSOR. PROF_ID AND
         CLASS.ROOM_ID = ROOM.ROOM_ID;
1/2543                           Database Management System         131
         Data Models:Degrees of Data Abstraction
    The Conceptual Model
    -Advantages of Using External Schemas
    The use of external schemas makes application program
    development much simple.
    The use of external schemas facilitates the designer’s task by
    making it easier to identify specific data required to support each
    business unit’s operations.
1/2543                       Database Management System              132
         Data Models:Degrees of Data Abstraction
         The existence of external models makes the
         designer’s job easier by providing feedback
           about the conceptual model’s adequacy.
         The creation of external models helps to ensure
         



           security constraints in the database design.

1/2543                 Database Management System   133
         Data Models:Degrees of Data Abstraction




1/2543                 Database Management System   134
              Data Models:Degrees of Data Abstraction
              The Physical Model
              -The physical model operates at the lowest level of
                abstraction,describing the way data is saved on storage media
                such as disks or tapes.
               -It requires the definition of both the physical storage devices and
                 the access methods required to reach the within those storage
         devices.
               -The physical model is both software and hardware-dependent.
1/2543                            Database Management System                 135
                Overview of Database Design
        Conceptual design: (ER Model is used at this stage)
        -What are the entities and relationships in the enterprise?
        -What information about these entities and relationships
     should
          we store in the database?
         -What are the integrity constraints or business rules that hold?
         -A database schema in the ER Model can be represented
           pictorially(ER diagrams).
         -Can map an ER diagram into a relation schema.
1/2543                         Database Management System                   136
                    Overview of Database Design

               Schema Refinement:(Normalization)
              Check relational schema for redundancies and related anomalies.
                Physical Database Design and Tuning:
               Consider typical workloads and further refine the database
         design.

1/2543                            Database Management System                    137
               The Entity Relationship (E-R) Model
  E-R model is commonly used to:
         -Translate different views of data among managers,users,and programmers
           to fit into a common framework.
         -Define data processing and constraint requirements to help us meet the
          different views.
         -Help implement the database.


1/2543                            Database Management System                138
         The Entity Relationship (E-R) Model
     E-R model   Components
          - Entities
         In E-R model an entity refers to the entity
          set,not an entity occurrence.
          An entity is represented by a rectangle
          containing the entity’s name.
          -Attributes
1/2543    -Relationships   Database Management System   139
         (E-R) Model
         Entity:   Real-world object distinguishable from other objects
         -An entity is described (in DB)using a set of
         attributes.
         Entity set: A collection of similar entities. E.g., all employees.
           -All entities in an entity set have the same of attributes.
              (Until we consider ISA hierarchies,anyway!)
           -Each entity set has a key.
            -Each attributes has a domain.
            -Can map entity set to a relation easily.
1/2543                           Database Management System                140
  (E-R) Model
          ssn                     name
                                                              lot



                                Employees
     CREATE TABLE Employees
       (ssn CHAR(11),                              ssn      name       lot
        name CHAR(20),                         123-22-366 Attishoo 48
        lot INTERGER,                          231-31-     Smiley    22
                                               131-24-3650 Smethurst 35
        PRIMARY KEY (ssn)) Database Management 5368
1/2543                                         System                      141
              The Entity Relationship (E-R) Model
    Attributes
         -Attributes are represented by ovals and are connected to the entity with a line.
         -Each oval contains the name of the attributes it represents.
         -Attributes have a domain - - the attribute’s set of possible values.
         -Attributes may share a domain.
         -Primary keys are underlined.




1/2543                             Database Management System                   142
         The Entity Relationship (E-R) Model




1/2543               Database Management System   143
         The Entity Relationship (E-R) Model




1/2543               Database Management System   144
         The Entity Relationship (E-R) Model




1/2543               Database Management System   145
     The Entity Relationship (E-R) Model
         Classes of Attributes
         - A simple attribute cannot be subdivided.
              Examples: Age,Sex,and Marital status

          - A composite attribute can be further subdivided to yield additional attribut
               Examples:

                - ADDRESS Street, City, State,Zip
                              


                - PHONE NUMBER Area code,Exchange number
                                          




1/2543                            Database Management System                 146
     The Entity Relationship (E-R) Model
         Classes of Attributes
          - A derived attribute is not physically stored within the database;instead,
              it is derived by using an algorithm.
          - Example : AGE can be derived from the data of birth and the current date.




1/2543                            Database Management System                  147
         The Entity Relationship (E-R) Model
         Classes of Attributes
           - A single-valued attribute can have only a single value.
              Examples:
                - A person can have only one socail security number.
                - A manufactured part can have only one serial number.




1/2543                     Database Management System                    148
         The Entity Relationship (E-R) Model
          Classes of Attributes
            - Multivalued attributes can have many values.
              Examples:
                  - A person may have several college degrees.
                  - A household may have several phones with different numbers
              Multivalued attributes are shown by a double line connecting to the ent
                (See Figure 4.7)



1/2543                        Database Management System                  149
The Entity Relationship (E-R) Model
       Multivalued Attribute in Relational DBMS
     - The relational DBMS cannot implement multivalued attributes.
     - Possible courses of action for the designer
      Within the original entity,several new attributes,one for each of the original
    multivalued
       attribute’s components(See Figure 4.8).
        Example:
          CAR_COLOR
         




          CAR_TOPCOLOR,CAR_BODYCOLOR,
          CAR_TRIMCOLOR
   Create anew entity composed of the original multivalued attribute’s components
       (See Figure 4.9).
1/2543                          Database Management System                  150
         The Entity Relationship (E-R) Model




1/2543               Database Management System   151
         The Entity Relationship (E-R) Model




1/2543               Database Management System   152
         The Entity Relationship (E-R) Model




1/2543               Database Management System   153
         The Entity Relationship (E-R) Model
          Relationships
             - A Relationship is an association between entities.
              - Relationships are represented by diamond-shaped symbols.




1/2543                       Database Management System               154
             The Entity Relationship (E-R) Model
              Relationships
         - A Relationship’s degree indicates the number of associated
           entities or participants(see Figure 4.12).
            A unary relationship exists when an association is maintained within
              a single entity.(Recursive relationship)
            A binary relationship exists when two entities are associated.
               (Most common)
            A ternary relationship exists when three entities are associated.

1/2543                            Database Management System                155
         The Entity Relationship (E-R) Model




1/2543               Database Management System   156
         The Entity Relationship (E-R) Model




1/2543               Database Management System   157
         The Entity Relationship (E-R) Model
         Connectivity
            - The term connectivity is used to describe the relationship classificatio
               (e.g., one-to-one,one-to-many,and many-to-many).




1/2543                       Database Management System                   158
           The Entity Relationship (E-R) Model
 Cardinality
         - Cardinality expresses the specific number of entity occurrences
           associated with one occurrence of the related entity.
         - The actual number of associated entities is usually a function of an
            organization’s policy



1/2543                          Database Management System               159
         The Entity Relationship (E-R) Model




1/2543               Database Management System   160
         The Entity Relationship (E-R) Model
         Existence Dependency
         - If an entity’s existence depends on the existence of one or more other
           entities,it is said to be existence-dependent.
          - Example:
          In Database Table 4.4, the existence of an occurrence of CRS_CODE in
           the COURSE table is dependent on CRS_CODE in the CLASS table.
           That is,no class can be offered for a course that doesn’t exist



1/2543                         Database Management System                 161
         The Entity Relationship (E-R) Model
         Relationship Participation
  - The participation is optional if one entity occurrence does not require
    a corresponding entity occurrence in a particular relationship.
  - An optional entity is shown by a small circle on the side of the optional entity.




1/2543                          Database Management System                    162
         The Entity Relationship (E-R) Model




1/2543               Database Management System   163
     The Entity Relationship (E-R) Model
         Weak Entities
         - A Weak entity is one that
           is existence-dependent and
           has a primary key that is partially or totally derived from the parent entity
           in relationship.
           - The existence of a weak entity is indicated by a double rectangle.
             (See figure 4.18)
           - The weak entity inherits all or part of its primary key from its strong
             counterpart.


1/2543                          Database Management System                    164
         The Entity Relationship (E-R) Model




1/2543               Database Management System   165
         The Entity Relationship (E-R) Model




1/2543               Database Management System   166
         The Entity Relationship (E-R) Model
         Recursive Entities
           - A recursive entity is one in which a relationship can exist between
             occurrences of the same entity set.
           - A recursive entity is found within a unary relationship.(See Figure 4.1
           - Examples:
              Database Table 4.7 - - Unary Recursive Relationship
              Database Table 4.8 - - M:N Recursive Relationship
              Database Table 4.9 - - M:N Recursive Relationship
              Database Table 4.10 - - I:M Recursive Relationship

1/2543                       Database Management System                   167
         The Entity Relationship (E-R) Model




1/2543               Database Management System   168
         The Entity Relationship (E-R) Model




1/2543               Database Management System   169
         The Entity Relationship (E-R) Model




1/2543               Database Management System   170
         The Entity Relationship (E-R) Model




1/2543               Database Management System   171
            The Entity Relationship (E-R) Model
            Composite Entities
          - A composite entity is composed of the primary keys of each of the entities
               to be connected. The composite entity serves as a bridge between the
               related entities.
          -The composite entity may contain additional attributes.
           - Example :
         ENROLL table in Database table 4.1 bridges STUDENT and CLASS.



1/2543                             Database Management System                  172
         The Entity Relationship (E-R) Model




1/2543               Database Management System   173
     The Entity Relationship (E-R) Model
    - A class may exist even though it contains optional to CLASS. When the
      M:N relationship is decomposed into two 1:M relationships through
      ENROLL,the optionality must be transferred to ENROLL(See Figure 4.21).
    - To be classified as a STUDENT, a person must be enrolled in at least one
       CLASS; i.e, CLASS is mandatory to STUDENT.
   - A class can occur more than once in the ENROLL table.



1/2543                         Database Management System                174
         The Entity Relationship (E-R) Model




1/2543               Database Management System   175
             The Entity Relationship (E-R) Model
   Entity Supertypes and Subtypes

          - The generalization hierachy
            depicts the parent-child relationship.(See Figure 4.22)
         - The supertype contains shared attributes,while the subtype contains the unique
             attributes.




1/2543                             Database Management System                   176
           The Entity Relationship (E-R) Model
           Entity Supertypes and Subtypes
         - A subtype entity inherits its attributes and its relationship from the
             supertype entity.
         - The supertype entity set is usually related to several unique and disjointed
           (nonoverlapping)subtype entity sets.
         - The supertype and its supertype(s) maintain a 1:1 relationship.




1/2543                            Database Management System                    177
         The Entity Relationship (E-R) Model




1/2543               Database Management System   178
         The Entity Relationship (E-R) Model




1/2543               Database Management System   179
     Developing an E - R Diagram
     The processs of database design is an iterative rather than a liner or
     sequential process.
     Infornation for database design is gathered by interwiewing the end users and
     examining the business forms and reports




1/2543                          Database Management System                    180
  Developing an E - R Diagram
         Tiny College Database ( 1 )
         - Tiny College ( TC ) is divided into several
  school. Each school is administered by a dean . A 1: 1
  relationship exists bettween DEAN and SCHOOL.
         - Each dean is member of group of administators
  ( ADMINISTRATOR ) . Deans also hold professorial rank
  and may teach a class ( PROFESSOR ) . ( See Figure 4.25 )
1/2543                 Database Management System       181
  Develolping an E - R Diagram
        - Administrators and Professsors have professional
  characteristic the other employees do not have Subset /
  supperset
         - Not all employees are professors
         - PROFESSOR is optional to EMPLOYEE

1/2543                   Database Management System          182
    Developing an E - R Diagram




1/2543             Database Management System   183
         Developing an E - R Diagram




1/2543                   Database Management System   184
  Developing an E - R Diagram
         Tiny College Database ( 2 )
         - Each school is composed of several departments.
  Operated by a school is one , and the largest number of
  departments is inderterminate ( N )
         - Each department belongs to only a single school .

1/2543                    Database Management System           185
     Developing an E - R Diagram
          Tiny College Database ( 3 )
          - Each department ofters several Couses




1/2543                   Database Management System   186
    Developing an E - R diagram
         - Tiny College Database ( 3 )
         - Each department offers several Couses.




1/2543                   Database Management System   187
                   Developing an E - R Diagram
         Tiny College Database ( 4 )
         - A department may offer several sections ( Classes )
         of the same couse.
         - 1 : M relationship exists between Course and CLASS
         - CLASS is optional to COURSE

1/2543                        Database Management System         188
Developing an E - R Diagram
Tiny College Database ( 5 )
         - Each department has many professors assigned to it.
         - One of those professors chairs the department . Only
         one of the professors can chair the department.
         - DEPARTMENT is optional to PROFESSOR in the
         ‚chair ‚ relationship.
1/2543                       Database Management System           189
                 Developing an E - R Diagram
         Tiny college Database ( 6 )
         - Each professor may teach up to four classes, each one
         a section of a course.
         - A professor may also be on a research contract and
         teach no classes.

1/2543                        Database Management System           190
                  Developing an E- R Diagram
         Tiny college Database ( 7 )
        - A student may enroll in several classes, but (s) he takes each
class only once during any given enrollment period.
      - Each student may enroll in up to six clases and each class
may have up to 35 students in it.
         - STUDENT is optional to CLASS.

1/2543                      Database Management System               191
                   Developing an E - R Diagram
         Tiny College Database ( 8 )
         - Each department has several students whose major is
         offered by that department.
         - Each student has only a single department and associated
         with a single department.



1/2543                      Database Management System                192
         Developing an E - R Diagram
                 Tiny College Database ( 9 )
                 - Each student has an advisor in his or her department ; each
         advisor counsels several s students .
                 - An advisor is also a professor; but not all professors advisw
         students.



1/2543                            Database Management System                 193
    Developing an E - R Diagram
         Entities for the Tiny College Database
         - SCHOOL
         - DEPARTMENT
         - EMPLOYEE
         - PROFESSOR
         - COURSE
         - CLASS
         - ENROLL ( Bridge between STUDENT AND CLASS )
         - STUDENT
1/2543                           Database Management System   194
1/2543   Database Management System   195
  Developing an E - R Diagram

          Converting an E - R Model into a Database Structure for
          artist Database
          - A painter might paint many paintings. The cardinality is ( 1: N )_in
          the relationship between PAINTER AND PAINTING.
          - Each painting is painted by one ( only one ) painter .
          - A painting might ( or might not ) be exhibited in a gallery ; the
          GALLERY is optional to PAINTING.


1/2543                          Database Management System                      196
1/2543   Database Management System   197
1/2543   Database Management System   198
    Developing an E - R Diagram
         Summary of table structures and Special
    Requirements for the ARTIST database




1/2543                 Database Management System   199
 Converting an E - R Model into a Database
 Structure for ARTIST Database
         - PRT - NUM is foreign key
         - PAINTER AND PAINTING


1/2543              Database Management System   200
Normallization
of
Database Tables
Chapter Objectives
   Understand concepts of normalization
   Learn how to normalize tables
   Understand normalization and database design
   issues
Chapter Overview
5.1 Database Tables and Normalization
5.2 Normalization and Database Design
5.3 Higher - Level Normal Forms
5.4 Denomalization
Database Tables and Normalization
Normalization is a process for assigning attributes to entities.
It reduces data redundancies and helps eliminate the data
anomalies.
Normalization works through a series of stages called normal
forms :
    - First normal form (1NF)
    - Second normal form (2NF)
    - Third normal form (3NF)
The highest level of normalization is not always desirable
Database Tables and Normalization
The Need for Normalization
    - Case of a Construction Company
           * Building project -- Project number, Name, Employees assigned to the project.
      * Employee -- Employee number, Name , Job classification
      * The company charges its clients by billing the hours spent on each project. The
hourly billing rate is dependent on the employee’s position.
      * Periodically, a report is generated as shown in Figure 5.1
      * The table whose contents correspond to the reporting requirements is shown in
Database Table 5.1
Database Tables and Normalization
Problems with the Table 5.1
   - The project number is intended to be a primary key, but it
contains nulls.
   - The table displays data redundancies.
   - The table entries invite data inconsistencies.
   - The data redundancies yield the following anomalies :
      *Update anomalies.
      *Addition anomalies.
      *Deletion anomalies.
Database Tables and Normalization
 Conversion to First Normal Form
   -A relational table must not contain repeating groups.
   -Repeating groups can be eliminated by adding the
     appropriate entry in at least the primary key column
    (s). (See Database Table 5.3)
Database Tables and Normalization
Database Tables and Normalization
Dependency Diagram
       - The arrows above the entity indicate that the entity’s attributes
are dependent on the combination of PROJ_NUM and EMP_NUM.
    - The arrows below the dependency diagram indicate less desirable
dependencies based on only a part of the primary key -- partial
dependencies.
Database Tables and Normalization
1NF Definition
      - The term first normal form (1NF) describes the tabular format
in which :
         * All the key attributes are defined.
         * There are no repeating groups in the table.
         * All attributes are dependent on the primary key.
Database Tables and Normalization
Conversion to Second Normal Form
   - Starting with the 1NF format, the database can be
converted into the 2NF format by
         * Writing each key component on a separate line, and then writing the
original key on the last line and
         * Writing the dependent attributes after each new key.
PROJECT (PROJ NUM, PROJ_NAME)
EMPLOYEE (EMP NUM, EMP_NAME, JOB_CLASS,
CHG_HOUR)
ASSIGN (PROJ NUM, EMP NUM, HOURS)
Database Tables and Normalization
2NF Definition
    - A table is in 2NF if :
        * It is in 1NF and
        * It includes no partial dependencies; that is, no attribute is
dependent on only a portion of the primary key.
     - Note :
        It is still possible for a table in 2NF to exhibit transitive dependency
; that is, one or more attributes may be functionally dependent on nonkey
attributes.
Database Tables and Normalization
Conversion to Third Normal Form
   - Create a separate table with attributes in a transitive
functional dependence relationship.

            PROJECT (PROJ NUM, PROJ_NAME)
          ASSIGN (PROJ NUM, EMP NUM, HOURS)
           EMPLOYEE (EMP NUM, EMP_NAME,
                JOB_CLASS, CHG_HOUR)
Database Tables and Normalization
 3NF Definition
  -A table is in 3NF if:
      * It is in 2NF and
      * It contains no transitive dependencies.
Normalization and Database Design
 Database Design and Normalization
Example :
(Construction Company)
  - Summary of Operations :
       * Some employees are not assigned to a project and perform duties not
specifically related to a project. Some employees are part of a labor pool, to be
shared by all project teams.
      * Each employee has a (single) primary job classification. This job
classification determines the hourly billing rate.
      * Many employees can have the same job classification.
Normalization and Database Design
Two Initial Entities :
  PROJECT (PROJ NUM, PROJ_NAME)
  EMPLOYEE (EMP NUM, EMP_LNAME,
  EMP_FNAME, EMP_INITIAL,
  JOB_DESCRIPTION, JOB_CHG_HOUR)
Normalization and Database Design
Three Entities After Transitive Dependency Removed
    PROJECT (PROJ_ NUM, PROJ_NAME)
    EMPLOYEE (EMP NUM, EMP_LNAME,
    EMP_FNAME, EMP_INITIAL, JOB_CODE)
    JOB (JOB CODE, JOB_DESCRIPTION,
    JOB_CHG_HOUR)
Normalization and Database Design
Database Tables and Normalization
 Boyce-Codd Normal Form (BCNF)
   - A table is in Boyce-Cold normal form (BCNF) if every determinant in the
table is a candidate key.
     (A determinant is any attribute whose value determines other values with a row.)

    - If a table contains only one candidate key, the 3NF and the BCNF are
equivalent.
   - BCNF is a special case of 3NF.
   - Figure 5.4 illustrates a table that is in 3NF but not in BCNF.
   - Figure 5.5 shows how the table can be decomposed to conform to the BCNF
form.
Database Tables and Normalization
Database Tables and Normalization
Database Tables and Normalization
Database Tables and Normalization
  BCNF Definition
    - A table is in BCNF if every determinant
      in that table is a candidate key. If a table
     contains only one candidate key, 3NF and
     BCNF are equivalent.
Normalization and Database Design
 Database Design and Normalization
 Example :
 (Construction Company)
    - Summary of Operations :
      * The company manages many projects.
      * Each project requires the services of many employees.
      * An employee may be assigned to several different
 projects.
Normalization and Database Design
Creation of the Composite Entity ASSIGN
Normalization and Database Design
 Attribute ASSIGN_HOUR is assigned to the composite entity
 ASSIGN.
 ‚Manages‛ relationship is created between EMPLOYEE and
 PROJECT.
    PROJECT (PROJ NUM, PROJ_NAME,
    EMP_NUM)
   EMPLOYEE (EMP NUM, EMP_LNAME,
    EMP_FNAME, EMP_INITIAL, EMP_HIREDATE, JOB_CODE)
  JOB (JOB CODE, JOB_DESCRIPTION,
    JOB_CHG_HOUR)
  ASSIGN (ASSIGN NUM, ASSIGN_DATE,
    PROJ_NUM, EMP_NUM, ASSIGN_HOURS)
Normalization and Database Design
Higher - Level Normal Forms
4NF Definition
   - A table is in 4NF if it is in 3NF and has no multiple sets
of multivalued dependencies.
Higher - Level Normal Forms
Denormalization
Normalization is only one of many database
design goals.
Normalized (decomposed) tables require
additional processing, reducing system speed.
Denormalization
Normalization purity is often difficult to
sustain in the modern database environment.
The conflict between design efficiency,
information requirements, and processing
speed are often resolved through
compromises that include denormalization.

				
DOCUMENT INFO