by David Siguelnitzky, MIS; MTE

Lecture 3 - Version 1.1.0

The Relational Database Model

        A Logical View of Data

             • Relational model

                  – Enables us to view data logically rather than physically

                  – Reminds us of simpler file concept of data storage

             • Table

                  – Easier to understand than its hierarchical and network database predecessors

        Keys

             • Consists of one or more attributes that determine other attributes

             • Primary key (PK) is an attribute (or a combination of attributes) that uniquely identifies any given entity (row)

             • Key’s role is based on determination

                  – If you know the value of attribute A, you can look up (determine) the value of attribute B

        Keys

             • Composite key

                  – Composed of more than one attribute

             • Key attribute

                 – Any attribute that is part of a key

             • Superkey

                  – Any key that uniquely identifies each entity

             • Candidate key

                  – A superkey without redundancies

        Controlled Redundancy

             • Makes the relational database work

             • Tables within the database share common attributes that enable us to link tables together

             • Multiple occurrences of values in a table are not redundant when they are required to make the relationship work

             • Redundancy is unnecessary duplication of data

Example of a Relational Database

Relational Schema

        Keys (continued)

             • Foreign key (FK)

                  – An attribute whose values match primary key values in the related table

             • Referential integrity

                  – FK contains a value that refers to an existing valid tuple (row) in another relation

             • Secondary key

                  – Key used strictly for data retrieval purposes

        Relational Database Operators

             • Relational algebra

                  – Defines theoretical way of manipulating table contents using relational operators:

                       • SELECT                           • UNION

                       • PROJECT                        • DIFFERENCE

                       • JOIN                                • PRODUCT

                       • INTERSECT                    • DIVIDE

                  – Use of relational algebra operators on existing tables (relations) produces new relations

        Relational Algebra Operators

             • Union:

                  – Combines all rows from two tables, excluding duplicate rows

                  – Tables must have the same attribute characteristics

             • Intersect:

                  – Yields only the rows that appear in both tables

             • Difference

                  – Yields all rows in one table not found in the other table - that is, it subtracts one table from the other



             • Product

                  – Yields all possible pairs of rows from two tables

                       • Also known as the Cartesian product

             • Select

                  – Yields values for all rows found in a table

                  – Can be used to list either all row values or it can yield only those row values that match a specified criterion

                  – Yields a horizontal subset of a table



             • Project

                  – Yields all values for selected attributes

                  – Yields a vertical subset of a table

             • Join

                  – Allows us to combine information from two or more tables

                  – Real power behind the relational database, allowing the use of independent tables linked by common attributes

        The Data Dictionary and System Catalog

             • Data dictionary

                  – Used to provide detailed accounting of all tables found within the user/designer-created database

                  – Contains (at least) all the attribute names and characteristics for each table in the system

                  – Contains metadata - data about data

                  – Sometimes described as “the database designer’s database” because it records the design decisions about tables and their structures

        A Sample Data Dictionary

             • System catalog

                  – Contains metadata

                  – Detailed system data dictionary that describes all objects within the database

                  – Terms “system catalog” and “data dictionary” are often used interchangeably

                  – Can be queried just like any user/designer-created table

        Relationships within the Relational Database

             • 1:M relationship

                  – Relational modeling ideal

                  – Should be the norm in any relational database design

             • M:N relationships

                  – Must be avoided because they lead to data redundancies

             • 1:1 relationship

                  – Should be rare in any relational database design

        The M:N Relationship

             • Can be implemented by breaking it up to produce a set of 1:M relationships

             • Can avoid problems inherent to M:N relationship by creating a composite entity or bridge entity

        Linking Table

             • Implementation of a composite entity

             • Yields required M:N to 1:M conversion

             • Composite entity table must contain at least the primary keys of original tables

             • Linking table contains multiple occurrences of the foreign key values

             • Additional attributes may be assigned as needed

        Data Redundancy Revisited

             • Data redundancy leads to data anomalies

                  – Such anomalies can destroy database effectiveness

             • Foreign keys

                  – Control data redundancies by using common attributes shared by tables

                  – Crucial to exercising data redundancy control

             • Sometimes, data redundancy is necessary

        A Small Invoicing System

Source: Rob and Coronel - Database Systems: Design, Implementation and Management – 6th Edition Course Technology

Return to Courses | Course Content