Learning Center
Plans & pricing Sign in
Sign Out

Database Concepts


									Database Concepts

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

Database Management
   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
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
   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.

Database Models
   The way a database organizes data
    depends on the type, or model, of the
   Three types:
       Hierarchical
       Network
       Relational – organizes data in a table form
        consisting of related rows and columns
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

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

Database Power
   The relationships between each file in the
   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
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

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

What Enforcing Referential
Integrity Means
   We cannot add a new child record if its
    common field references a non-existent
   We cannot delete a parent which has existing
   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
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

  The Database Management
  Systems (DBMS) Process

                                     Review the data and
                                      edit until accurate
Describe the data

                    Enter the data
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
   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

             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.

         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

   Formatted presentation of data from the
   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”

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


To top