Docstoc

Relational Database

Document Sample
Relational Database Powered By Docstoc
					Introduction to Databases




                            1
             Objectives
• To understand basic database terms and
  concepts




                                           2
                  Flat Files or
           File Processing Systems
• The Traditional
  Approach
   – Separate files
     are created and
     stored for each
     application
     program.




                                     3
           Drawbacks to the
       File Processing Systems
• Data redundancy
   – Duplication of data in separate files.
• Lack of data integrity
   – The degree to which the data in any one file is
     accurate.
• Program-data dependence
   – A situation in which program and data organized for
     one application are incompatible with programs and
     data organized differently for another application.

                                                           4
    The DATABASE Approach

• Central repository of shared data
• Data is managed using a DBMS
• Stored in a standardized, convenient form



Requires a Database Management System (DBMS)

                                           5
Database Management System
  • A software system that is used to create, maintain,
    and provide controlled access to user databases




                                                                             6
DBMS manages data resources like an operating system manages hardware resources
       Advantages to the Database
               Approach
•   Improved strategic use of the corporate database
•   Reduced Data Redundancy
•   Improved Data Integrity
•   Easier Modification and Updating
•   Data and program independence
•   Better access to data and information
•   Standardization of data access
•   A framework for program development
•   Better overall protection of data
•   Shared data and information resources resulting in
    lower costs
                                                         7
           Basic Terminology
•   What is a Database?
•   What is Data?
•   What is Information?
•   What is Metadata?




                               8
Data in Context   Information Utility?




                                     9
Characteristics of Valuable Information




                                     10
           Types of Data
•   Alphanumeric
•   Numeric
•   Date
•   Image or graphical
•   Audio
•   Video
•   Other……

                           11
       Relational Database
• A type of logical database structure that treats
  data as if it were stored in two-dimensional tables
  called relations.

• It can relate any piece of information stored in one
  table to any piece in another table as long as the
  two tables share a common data element.




                                                     12
            Relational Database
• Tables stored in the database are often referred to as
  flat files, tables, relations or entities.
   – In each table, the rows are unique records and the
     columns are fields.
   – Another term for a row or record in a relational database
     is a tuple.
   – Attributes are also referred to as columns or elements.
• Relational database products (DBMS) range from PC-
  based (lower-end) to client/server (higher-end) and/or
  mainframe based (legacy systems).


                                                           13
   Relational Database Model
• Relational Data Model
  – All data elements are placed in two-dimensional tables,
    called relations, that are the logical equivalent of files.




                                                            14
             Data Hierarchy
•   Data element
•   Field/ Column/ Atrribute
•   Record/ Row/ Tuple
•   File/ Table/ Entity
•   Database


                               15
   Modeling Organizational
           Rules
• Business Rules
  – Statements that define or constrain some
    aspect of the business
  – Assert business structure
  – Control/influence business behavior
  – Expressed in terms familiar to end users
  – Automated through DBMS software


                                               16
  Data Names and Definitions
• Data Names
  –   Related to business, not technical, characteristics
  –   Meaningful and self-documenting
  –   Unique
  –   Readable
  –   Composed of words from an approved list
  –   Repeatable


• Data Definitions

                                                            17
 Properties of Tables (Entities)
1)   Every table has a unique name.
2)   Every attribute value is atomic.
3)   Every row is unique.
4)   Attributes in tables have unique names.
5)   The order of the columns is irrelevant.
6)   The order of the rows is irrelevant.
                Attributes
• Attribute - property or characteristic of
  an entity type (table)
• Can be identifying or descriptive
• Classifications of attributes:
  – Required versus Optional Attributes
  – Simple versus Composite Attribute
  – Stored versus Derived Attributes
  – Identifier Attributes

                                              19
A composite attribute




                        20
                Identifiers
• Identifier or Key - An attribute (or
  combination of attributes) that uniquely
  identifies individual instances of an entity
  type.
• Simple Key versus Composite Key




                                                 21
Simple and composite key attributes

(a) Simple key attribute




                                      22
(b) Composite key attribute




                              23
    Criteria for Selecting Identifiers
• Will uniquely identify an instance of the entity.
• Will not be null.
• Will not change in value.
• Intelligent identifiers not recommended
  (containing e.g. locations or people that might
  change)
• Substitute new, simple keys for long, composite
  keys.

                                                  24
Relational Keys
 Candidate Key
 Primary Key
 Composite Key / Concatenated Key
 Secondary Key
 Foreign Key
Functional Dependencies & Keys
 The key’s role is based on a concept known as
  determination, which is used in the definition of
  functional dependence.
 Functional Dependency: The value of one attribute (the
  determinant) determines the value of another attribute.
 Candidate Key: Each non-key field is functionally
  dependent on every candidate key.
 Candidate Key:
    – Properties:
       • Unique Identifier
       • Non-Redundant or not-null
       • Each non-key field is functionally dependent on every
         candidate key
Foreign Keys
   Controlled redundancy (shared common
    attributes) makes the relational database work.

   The primary key of one table appears again as
    the link (foreign key) in another table.

   If the foreign key contains either matching
    values or nulls, the table(s) that make use of
    such a foreign key are said to exhibit referential
    integrity.
Primary and Foreign Keys
Integrity Constraints
 Entity Integrity
 Referential Integrity
 Domain Constraints
Integrity Rules

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:5
posted:10/21/2011
language:English
pages:30