Docstoc

Database Concepts

Document Sample
Database Concepts Powered By Docstoc
					Database Concepts




                    1
Definition of a Database
   An organized
   Collection
   Of related records




                           2
Database Management
System
   Software that helps you organize data
    in a way that allows fast and easy
    access to data
   Data in a database is stored differently
    than in other applications:
       In those forms, data is entered and used in
        the same form as it resides on disk
       In a database, data can reside on the disk
        in ways unknown to a user
                                                3
Advantages of Databases
   Reduced Redundancy: data which might be stored more than
    once in separate files, are stored in only one place or file in a
    database.
   Integrated Data: rather than being in separate and independent
    files, data is integrated because any item of data can be used to
    satisfy an inquiry or report.
   Data Integrity: that the files are accurate and up-to-date
        Edit checks on various fields to insure that the data keyed in at
         least conforms to standards we have established.
        Because one item of data should not be stored in two different files
         of a database, it prevents data being stored correctly in one file
         and incorrectly in another file.


                                                                             4
Database Models
   The way a database organizes data
    depends on the type, or model, of the
    database
   Three types:
       Hierarchical
       Network
       Relational – organizes data in a table form
        consisting of related rows and columns
                                                 5
Fields, Records, and Keys - 1
   Each “box” in a table contains a data item
   Each column is a field, which is a type of data
    – every data item in a field has similar
    characteristics and meanings.
   Full set of data in any given row is a record.
   A collection of related records makes a file,
    or, in database nomenclature, a table



                                                6
Fields, Records, and Keys - 2
   The file or table is the underlying
    basis of all relational databases.
   Each table in a database must have a
    unique identifier, or key




                                           7
Database Power
   The relationships between each file in the
    database
   How these relationships allow you to connect
    one file to another
   Connected by a field in each table, called a
    common field. They may not have the
    same name, but will have the same
    characteristics and meaning
   Common field is usually the key to one table,
    and an embedded field in another table
                                              8
Three Types of Relationships not
Discussed in the Text
   One to many:
       Most common
       Example: salesperson to customer – one
        salesperson may have more than one customer
   One to one:
       Example: salesperson to company car
   Many to many:
       Example: inventory items to customer orders. We
        won’t create this kind of relationship in class

                                                      9
Parent-Child & Enforcing
Referential Integrity
   In a one to many relationship, the one table
    is defined as the “parent” and the many table
    is defined as the “child”
   Referential Integrity
       We must define which table is the parent, and
        which is the child
       We must define the common field
       While the common fields in each table do not
        have to have the same name, they must have the
        same characteristics

                                                   10
What Enforcing Referential
Integrity Means
   We cannot add a new child record if its
    common field references a non-existent
    parent
   We cannot delete a parent which has existing
    children
   If a salesman is the parent, and the
    customers are his/her children, then we
    cannot delete the parent until the children are
    reassigned to a new parent
                                                11
Example of Related Tables
    SP ID         Last Name       First Name         Office    Hire Date        Salary

         12    Davis             James            Savannah        12/01/95        $45,000

         15    Jones             Susan            Savannah        10/14/94        $51,500

         16    Wilson            William          Augusta         07/08/94        $53,400




  CUST ID       Customer Name              City        State   Credit Limit       Salesperson


      1004    XYZ Co.               Vidalia          GA               $55,000                   12


      1007    PDQ Distributing      Garden City      GA              $150,000                   12



      1009    ABC, Inc.             Savannah         GA              $200,000                   15


      1011    SC Wholesale          N. Augusta       SC               $85,000                   16


                                                                                                12
  The Database Management
  Systems (DBMS) Process




                                     Review the data and
                                      edit until accurate
Describe the data

                    Enter the data
                                                     13
Queries - 1
   Another way of viewing data in a table
   May be used to view data in one table, or any
    multiple number of tables joined by their
    relationships
   Can query a table from any field in the table
    (integrated data)
   Can sort the data either ascending (A to Z) or
    descending (Z to A) from any field in the
    table

                                               14
             Queries - 2
   The power of a query are the two following points:
       A query allows us to view only the fields we wish to
        see. If a table contains as many as 50 fields, we can view
        only those we want to.
       A query allows us to view only the records (rows) we
        wish to see, by setting the criteria for viewing the data
            We can, for example, view only the customers who are from GA, or
             also just from GA with a specified sales amount
       Can also join two tables in a query, and further specify the
        data we wish to see via the criteria selection.

                                                                        15
         Queries - 3
   Created either by
       SQL - Structured Query Language
            Entered directly by user
            Included in programs




   Or by
       QBE – Query by Example
            Graphical interface to specify criteria
            This is what we will do in MS Access


                                                       16
Report
   Formatted presentation of data from the
    database
   Normally printed
   Designed either by using a report generator
    (a wizard in Access, which is what we will do)
    or from “scratch” in Access “Design View”




                                               17
Concurrency Control
   Databases are used concurrently by
    many users
   Problem if several users attempt to
    update the same record at the same
    time
   Record locking
       First user requests record
       Others are locked out for update

                                           18

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:6
posted:8/12/2012
language:English
pages:18