Docstoc

DBMS

Document Sample
DBMS Powered By Docstoc
					              Basic DB Terms

• Data: Meaningful facts, text, graphics, images, sound,
  video segments
   – A collection of individual responses from a marketing research
• Information: Data processed to be useful in decision
  making
   – Pattern of geographical buying habit based on analysis of a
     marketing research
• Metadata: Data that describes data




                                                                      1
Data in Context




Large volume of facts, difficult to interpret / make decisions
                                                           2
Information




Useful for decision making / interpretation

                                              3
Metadata




Descriptions of the properties or characteristics of the
data, including data types, field sizes, allowable values,
and documentation (Data Dictionary)

                                                             4
                           Database

• Collection of data in electronic format
   – A digital library of organization
 Managed by one set of software that provides access to all
  the data
   – No data redundancy, data inconsistency, poor security, application-
     data dependency...




                                                                       5
               Database Systems

Application
    #1




Application
    #2

                  DBMS             Database
                                  containing
                                  centralized
                                  shared data

 Application
     #3




                                            6
        Database Management System
                  (DBMS)
• Database software
• Act as an interface between application and physical data
  files
• Support centralization of data
• Independent of specific computer programs
• small (MS Access), large/popular (Oracle)




                                                              7
                Database Models

• Hierarchical (Tree) Models
• Network Model
• Relational Models




                                  8
      Hierarchical database Model
• Logically represented by an upside down tree
   – Each parent can have many children
   – Each child has only one parent




                                                 9
Hierarchical Database
                 Hierarchical Database




A schematic diagram of a hierarchical database (a) and a sample part of a
hierarchical database showing relationships among different records (b)
                                                                            11
         Network Database Model

• Each record can have multiple parents
   – Composed of sets
   – Each set has owner record and member record
   – Member may have several owners




                                                   12
Network Database
                    Network Database




A schematic diagram of a network database (a) and a sample of part of a network
database showing relationships among different records (b)
                                                                          14
         Relational Database Model

• A group of related tables
• Introduced in 1970 by E. F. Codd of IBM
• The most popular model.
   – Mathematical simplicity
   – Ease of visualization




                                            15
                    Relational Database




A schematic diagram of a relational database (a) and a sample part of a
relational database showing different tables (b)
                                                                          16
         Relational database and keys

• A relational database is a collection of tables that are
  related to one another based on a common field.
• A field, or a collection of fields, is designated as the
  primary key.
   – The primary key uniquely identifies a record in the table.
• When the primary key of one table is represented in a
  second table to form a relationship, it is called a foreign
  key.




                                                                  17
         Relating tables using a common field
The primary key in the
Employer table (EmployerID) is
the common field that relates
this table to the Position table.



PositionID is the primary key in
the Position table. The EmployerID
field is a foreign key in this table.

Primary keys can only have one
occurrence in a table. Foreign keys
may have multiple occurrences.


                                                18
                            Primary Key
• Unique identifiernt
   – Last name vs. SS#
• Prevent confusion
• Cost of PK
   – SS# vs. finger print
– Entity Integrity Rule
   – Any primary key is allowed to accept null values.




                                                         19
                Foreign Key

• An attribute in one table whose values must either match
  the primary key in another table or be null.
• The database must not contain any unmatched foreign key
  values.




                                                     20
Figure 2




           21
            Referential Integrity Rule

• Cascade Update Related Fields
   – Change of PK values in primary table  automatic change of FK
     values
• Cascade Delete Related Fields
   – Delete of a record in the primary table  automatic delete of all
     records in the related table that have a matching FK value
• See example from the class web site
   – Primary table: customer




                                                                         22
        Relational Database Model
• Advantages
   – Easier database design, implementation, management, and use
   – Ad hoc query capability with SQL
   – Powerful database management system




                                                                   23
DATABASE MODEL      PRODUCT       VENDOR                               HARDWARE
Hierarchical        Focus         Information Builders International   Mainframe/PC
                    IMS           IBM                                  Mainframe
                    Ramis         Online Software International        Mainframe
Network             ADABAS        Software AG of North America         Mainframe
                    Image         Hewlett-Packard                      Mainframe
Relational          Access        Microsoft                            PC
                    DB2           IBM                                  Mainframe
                    dBASE V       Borland International                PC
                    EDA/SQL       Information Builders International   PC
                    FoxPro        Microsoft                            PC
                    Ingres        Ask Group                            PC
                    NOMAD         Must Software International          Mainframe/PC
                    Oracle        Oracle                               Mainframe/PC
                    Paradox       Borland International                PC
                    Rbase         Microrim                             PC
                    SQL/DS        IBM                                  Mainframe
                    SQL Server    Microsoft                            PC
                    Sybase        Sybase                               PC
Object-Relational   ObjectStore   Object Design                        PC
                    Universal     Informix                             PC
                    Server
                    Illustra      Informix                             PC
                                                                                  24
       Basic Components of DBMS

• Data dictionary
• DDL (Data Definition Language)
• DML (Data Manipulation Language)




                                     25
The Data Dictionary (Metadata)
 - description of every piece of data in database
 - Maintains all information supplied by the developer when   constructing the
schema




A typical data dictionary for a staff file

                                                                                 26
• Data Definition Language (DDL)
     – language to create and modify data
     – Access table with data type, description, and field properties




Data definition language to create a schema in NOMAD

                                                                        27
  • Data Manipulation Language (DML)
       – language that process, update, and retrieve data
       – Access query




A Paradox query by example

                                                            28
       Structured Query Language (SQL)

• Standard Query Language (SQL) is the relational model’s
  standard language.
• Another way to generate queries
   – MS Access: queries by QBE
   – Other DBMSs: queries by SQL




                                                            29
                      Example of SQL
 • You want to see the address of each employee: FirstName, LastName,
   Address, City, and State

                        EmployeeAddressTable
SSN        FirstName LastName Address                City        State
512687458 Joe          Smith       83 First Street   Howard      Ohio
758420012 Mary         Scott       842 Vine Ave.     Losantiville Ohio
102254896 Sam          Jones       33 Elm St.        Paris       New York
876512563 Sarah        Ackerman    440 U.S. 110      Upton       Michigan




                                                                         30
                     Example of SQL
  • SELECT FirstName, LastName, Address, City, State
    FROM EmployeeAddressTable;

First Name   Last Name     Address           City           State
Joe          Smith         83 First Street   Howard         Ohio
Mary         Scott         842 Vine Ave.     Losantiville   Ohio
Sam          Jones         33 Elm St.        Paris          New York
Sarah        Ackerman      440 U.S. 110      Upton          Michigan


SQL tutorial: w3.one.net/~jhoffman/sqltut.htm



                                                                    31
     Database Design Process

           Conceptual Design
Problem
Domain
             Conceptual Schema



            Logical Design

              Logical Schema



            Physical Design

              Physical Schema
                                 32
                      Conceptual Design

• The conceptual design is a high level description of the
  structure of the database, independent of the particular
  DBMS software that will be used to implement the database.
• The conceptual design revolves around discovering and
  analyzing organizational and users data requirements.
   – What data is important
   – What data should be maintained
• The major activity of this phase is constructing a data model
  (Entity-Relationship Diagram).


                                                             33
Data Model: Entity-Relationship Diagram
Why Conceptual Modeling is Important?

• Effective Communication Tool
• User involvement
• Independence from a particular DBMS
• Documentation




                                        35
                   Logical Design
• The logical design is a description of the structure of the
  database that can be processed by the DBMS software. In
  other words, the logical design adapts the conceptual design
  to a specific DBMS implementation model
• Thus, the logical design is software-dependent.
• Logical Models
   – Relational Model
   – Network Model
   – Hierarchical Model


                                                            36
                  Physical Design
• The physical design describes the storage structures and
  data access methods used in system. In other words, the
  physical design is a description of the implementation of
  the database in secondary memory.




                                                              37

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:1
posted:7/1/2012
language:
pages:37