DB

Document Sample
DB Powered By Docstoc
					資料庫概論
  Database Management
      System(DBMS)
• Collection of interrelated data
• Set of programs to access the data
• DBMS contains information about a
  particular enterprise
• DBMS provides an environment that
  it both convenient and efficient to
  use
   Purpose of Database Systems
Database management systems were developed to
handle the following difficulties of typical file-
processing systems supported by conventional
operating systems.
• Data redundancy and inconsistency
• Difficulty in accessing data
• Data isolation  multiple files and formats
• Concurrent access by multiple users
• Security problems
           Instances and Schemas

• Schema  the logical structure of the
  database

• Instance  the actual content of the
  database at a particular point in time
  Database Systems Concepts
           E-R Model
• Entity-Relationship model
  – Ref. Peter Chen’s paper in 1976.
  – an abstract and conceptual
    representation of data
  – Top-down design
  – To describe an ontology




          4
             E-R Model
• Building blocks
  – Entity
     • Strong vs. weak
  – Attribute
     • Key
     • Multi-valued attribute
  – Relationship
     • Associated with attributes



             5
            E-R Model
• Entity
  – An entity may be defined as a thing which
    is recognized as being capable of an
    independent existence and which can be
    uniquely identified. [wiki].
  – An entity may be a physical object such as
    a house or a car, an event such as a house
    sale or a car service, or a concept such as
    a customer transaction or order. [wiki]


           6
                E-R Model
• Entity
  – Entities can be thought of as nouns.
    Examples: a computer, an employee, a
    song, a mathematical theorem. [wiki]
  – Strong vs. weak


      account       log     transactions




                7
              E-R Model
• Attribute
  – Entities and relationships can both have
    attributes. Examples: an employee entity
    might have a Social Security
    Number (SSN) attribute;
    the proved relationship may have
    a date attribute. [wiki]
  – Every entity (unless it is a weak entity)
    must have a minimal set of uniquely
    identifying attributes, which is called the
    entity's primary key. [wiki]

           8
                      E-R Model
                                                   name
• Attribute                            SID
                                                              phone
   – (General) atomic
     attribute
   – Key attribute            N_or_S               Student
   – Multi-valued attribute
   – Composite attribute
                                                      addr
   – Derived attribute
                                        city                      no

                                                     street




                                               9
          E-R Model
• Relationship
  – A relationship captures how two or
    more entities are related to one
    another.
  – Relationships can be thought of as
    verbs, linking two or more nouns.
  – Examples: an owns relationship
    between a company and a computer,


          10
               E-R Model
• Relationship
  – Role

                    husband
           Person             married
                     wife




                husband             wife
     Person               married          Person


              11
               E-R Model
• Relationship
  – Cardinality: 1-to-1, 1-to-N, M-to-N


                    N                1
      Person            Birthplace       Location




                                          [Chen 1976]



               12
          E-R Model
• Non-binary relationship

           A         R       C



                     B



               R2



   A           R1        C
          13
                                    E-R Model
                   • Example of E-R diagram
                                                                     Name

            Name              CID                              SID
TID                                          Title
                                                                               credit



  Teacher          teaching         Course           choose          Student




                                                       Score


                                    14
  Schema and constraint
• Model diagram [wiki]




         15
              Schema and constraint
                                                   Foreign key
            • An example [wiki]:
               – Customer(Customer ID, Tax ID, Name,
(Primary)        Address, City, State, Zip, Phone)
   Key
               – Order(Order No, Customer ID, Invoice No,
                 Date Placed, Date Promised, Terms, Status)
               – Order Line(Order No, Order Line No, Product
                 Code, Qty)
               – Invoice(Invoice No, Customer ID, Order No,
                 Date, Status)
               – Invoice Line(Invoice No, Invoice Line
                 No, Product Code, Qty Shipped)
               – Product(Product Code, Product Description)

                        16
              Schema and constraint
            • A “customer” relation
            • Properties
(Primary)     – No repeated tuples
   Key
              – Unordered tuples / attributes
              – Atomic values




                      17
  Schema and constraint
• Key
  – Primary key: not null, never change
  – Candidate key
• Domain (attributes)
  – Atomic
  – NULL values: missing / unknown / not
    applicable



          18
  Schema and constraint
• Foreign key
  – Foreign keys are integrity
    constraints enforcing that the value of
    the attribute set is drawn from
    a candidate key in another relation.
  – For example in the Order relation the
    attribute Customer ID is a foreign key.




           19
Functional dependency (FD)
• In a given table, an attribute Y is said
  to have a functional dependency on a
  set of attributes X (written X → Y) if
  and only if each X value is associated
  with precisely one Y value.
• For example, in an "Employee" table
  that includes the attributes "Employee
  ID" and "Employee Date of Birth",
• The FD ,Employee ID- → ,Employee
  Date of Birth} would hold.

           20
Functional dependency (FD)
•   Full functional dependency
•   Partial functional dependency
•   Transitive functional dependency
•   Armstrong’s Rule
    – Reflexive rule
    – Augmented rule
    – Transitive rule


            21
Functional dependency (FD)
• Decomposition
  – Lossless-join decomposition
  – Dependency-preserving
    decomposition




          22
         Normalization
• Normalization is the process of
  efficiently organizing data in a
  database.
• There are two goals of the
  normalization process:
   – eliminating redundant data (for example,
     storing the same data in more than one
     table)
   – ensuring data dependencies make sense
     (only storing related data in a table).

            23
          Normalization
• The process of organizing data to minimize
  redundancy is called normalization. [wiki]
   – The goal: to decompose relations with
     anomalies in order to produce smaller, well-
     structured relations.
   – Normalization usually involves dividing large
     tables into smaller (and less redundant)
     tables and defining relationships between
     them.
   – The objective is to isolate data so that
     additions, deletions, and modifications of a
     field can be made in just one table and then
     propagated through the rest of the database
     via the defined relationships.

             24
In summary *wiki+, …
                                                Normalization
       Normal form                       Defined by                                    Brief definition
                           Two versions: E.F. Codd (1970), C.J.    Table faithfully represents a relation and has
 First normal form (1NF)
                           Date (2003)[11]                         no repeating groups
                                                                   No non-prime attribute in the table is functionally
 Second normal form (2NF) E.F. Codd (1971)[2]
                                                                   dependent on a proper subset of acandidate key

                           E.F. Codd (1971);[2] see +also Carlo
                                                                   Every non-prime attribute is non-transitively
 Third normal form (3NF)   Zaniolo's equivalent but differently-
                                                                   dependent on every candidate key in the table
                           expressed definition (1982)[12]

 Boyce–Codd normal         Raymond F. Boyce and E.F. Codd          Every non-trivial functional dependency in the table is
 form(BCNF)                (1974)[13]                              a dependency on a superkey

                                                                   Every non-trivial multivalued dependency in the table
 Fourth normal form (4NF) Ronald Fagin (1977)[14]
                                                                   is a dependency on a superkey

                                                                   Every non-trivial join dependency in the table is
 Fifth normal form (5NF)   Ronald Fagin (1979)[15]
                                                                   implied by the superkeys of the table

                                                                   Every constraint on the table is a logical
 Domain/key normal
                           Ronald Fagin (1981)[16]                 consequence of the table's domain constraints and
 form(DKNF)
                                                                   key constraints

                           C.J. Date, Hugh Darwen, and Nikos       Table features no non-trivial join dependencies at all
 Sixth normal form (6NF)
                           Lorentzos (2002)[4]                     (with reference to generalized join operator)


                                                      25
            Basic Structure of SQL
• SQL is based on set and relational operations with certain
  modifications and enhancements
• A typical SQL query has the form:
        select A 1 , A 2 , ..., A n
        from r 1 , r 2 , ..., r m
        where P
– Ais represent attributes
– ris represent relations
– P is a predicate.
• This query is equivalent to the relational algebra
  expression:
 A 1 , A 2 , ..., A n ( p(r 1 X r 2 X …X r m ))
• The result of an SQL query is a relation.
        The Select Clause
• The select clause corresponds to the projection
  operation of the relational algebra. It is used to
  list the attributes desired in the result of a
  query.
• Find the names  all branches in the loan
                    of
  relation
        select branch-name
        from loan
  An asterisk in the select clause denotes”all
  attributes”
        select *
        from loan
  The Select Clause(Cont.)
• SQL allows duplicates in relations as well as in
  query results.
• To force the elimination of duplicates, insert the
  keyword distinct after select.
  Find the names of all branches in the loan
  relation, and remove duplicates
       select distinct branch-name
       from loan
• The keyword all specifies that duplicates not be
  removed.
       Select all branch-name
       from loan
  The Select Clause(Cont.)
• The select clause can contain arithmetic
  expressions involving the operators,+,-,*,/,and
  operating on constants or attributes of tuples.
• The query:
      select branch-name,loan-number,amount *
  100
      from loan
  would return a relation which is the same as the
  loan relation, except that the attribute amount
  is multiplied by 100
        The where Clause
• The where clause corresponds to the selection
  predicate of the relational algebra. It consists of
  a predicate involving attributes of the relations
  that appear in the from clause.
• Find all loan numbers for loans made at the
  Perryridge branch with loan amounts greater
  than $1200.
       select loan-number
       from loan
       where branch-name=“Perryridge” and
  amount>1200
 The where Clause(Cont.)
• SQL includes a between comparison operator in
  order to simplify where clauses that specify that
  a value be less than or equal to some value and
  greater than or equal to some other value.
• Find the loan number of those loans with loan
  amounts between $90,000 and $100,000(that is,
  $90,000 and $100,000)                    
       select loan-number
       from loan
       where amount between 90000 and 100000
                The from Clause
• The from clause corresponds to the Cartesian product
  operation of the relational algebra. It lists the relations to
  be scanned in the evaluation of the expression.
• Find the Cartesian product borrower loan
       select *
       from borrower, loan
• Find the name and loan number of all customers having a
  loan at the Perryridge branch.
       select distinct customer-name,borrower.loan-number
       from borrower,loan
       where borrower.loan-number=loan.loan-number and
             branch-name=“Perryridge”
              The Rename Operation

• The SQL mechanism for renaming relations and attributes is
   accomplished through the as clause:
        old-name as new-name
• Find the name and loan number of all customers having a
   loan at the Perryridge branch;replace the column name loan-
   number with the name loan-id.
select distinct customer-name,borrower.loan-number as loan-id
from borrower,loan
where borrower.loan-number=loan.loan-number and
        branch-name=“Perryridge”
                Tuple Variables
• Tuple variables are defined in the from clause via the use of
  the as clause.
• Find the customer names and their loan numbers for all
  customers having a loan at some branch.
       select distinct customer-name,T.loan-number
       from borrow as T,loan as S
       where T.loan-number=S.loan-number
• Find the names of all branches that have greater assets than
  some branch located in Brooklyn.
       select distinct T.branch-name
       from branch as T,branch as S
       where T.assets > S.assets and S.branch-city=“Brooklyn”
   Ordering the Display of Tuples
• List in alphabetic order the names of all customers having
  a loan at Perryridge branch
  select distinct customer-name
  from borrower,loan
  where borrower.loan-number=loan.loan-number and
          branch-name=“Perryridge”
  order by customer-name
• We may specify desc for descending order or asc for
  ascending order, for each attribute; ascending order is the
  default.
• SQL must perform a sort to fulfill an order by request.
  Since sorting a large number of tuples may be costly, it is
  desirable to sort only when necessary.
                Set Operations
• Find all customers who have a loan, an account, or both:
       (select customer-name from depositor)
       union
       (select customer-name from borrower)
• Find all customers who have both a loan and an account.
       (select customer-name from depositor)
       intersect
       (select customer-name from borrower)
• Find all customers who have an account but no loan.
       (select customer-name from depositor)
       except
       (select customer-name from borrower)
         Aggregate Operations
• These functions operate on the multiset of values
  of a column of a relation, and return a value
      avg:average value
      min:minimum value
      max:maximum value
      sum:sum of values
      count:count of values
     Aggregate Functions(Cont.)
• Find the average account balance at the Perryridge branch
       select avg (balance)
       from account
       where branch-name=“Perryridge”
• Find the number of tuples in the customer relation.
       select count (*)
       from customer
• Find the number of depositors in the bank
       select count(distinct customer-name)
       from depositor
         Aggregate Functions -- Group
                     By
• Find the number of depositors for each branch.
  select branch-name,count(distinct customer-name)
  from depositor,account
  where depositor.account-number=account.account-number
  group by branch-name

Note : Attributes in select clause outside of aggregate
  functions must appear in group by list.
Aggregate Functions -- Having Clause
• Find the names of all branches where the average account
  balance is more than $1,200
       select branch-name,avg(balance)
       from account
       group by branch-name
       having avg(balance)>1200
• Note: predicates in the having clause are applied after the
  formation of groups
                       Null Values
• It is possible for tuples to have a null value, denoted by null,
  for some of their attributes; null signifies an unknown value
  or that a value does not exist.
• The result of any arithmetic expression involving null is null.
• Roughly speaking, all comparisons involving null return false.
  More precisely,
   – Any comparison with null returns unknown
   – (true or unknown) = true, (false or unknown) = unknown
       (unknown or unknown) = unknown, (true and unknown) =
       unknown, (false and unknown) = false, (unknown and
       unknown) = unknown
   – Result of where clause predicate is treated as false if it
       evaluates to unknown
   – “P is unknown”evaluates to true if predicate P evaluates
       to unknown
              Null Values(Cont.)
• Find all loan numbers which appear in the loan relation
  with null values for amount.
       select loan-number
       from loan
       where amount is null
• Total all loan amounts
       select sum(amount)
       from loan
  Above statement ignores null amounts; result is null if
  there is no non-null amount.
• All aggregate operations except count(*) ignore tuples
  with null values on the aggregated attributes.
      Nested Subqueries
• SQL provides a mechanism for the
  nesting of subqueries.
• A subquery is a select-from-where
  expression that is nested within another
  query.
• A common use of subqueries is to
  perform tests for set membership, set
  comparisons, and set cardinality.
                Example Query
• Find all customers who have both an account and a loan at
  bank.
       Select distinct customer-name
       from borrower
       where customer-name in (select customer-name
                                 from depositor)
• Find all customers who have a loan at the bank but do not
  have an account at the bank.
       Select distinct customer-name
       from depositor
       where customer-name not in (select customer-name
                                    from depositor)
                     Example Query
• Find all customers who have both an account and a loan at
  the Perryridge branch.
       select distinct customer-name
       from borrower, loan
       where borrower.loan-number = loan.loan-number and
               branch-name =“Perryridge”and
               (branch-name, customer-name) in
                       (select branch-name, customer-name
                       from depositor, account
                       where depositor.account-number =
                               account.account-number)
         Set Comparison
• Find all branches that have greater assets than
  some branch located in Brooklyn.
       select distinct T.branch-name
       from branch as T, branch as S
       where T.assets > S.assets and
               S.branch-city =“Brooklyn”
          Example Query
• Find all branches that have greater assets than
  some branch located in Brooklyn.
       select branch-name
       from branch
       where assets > some
               (select assets
               from branch
               where branch-city =“Brooklyn”)
          Example Query
• Find the names of all branches that have greater
  assets than all branches located in Brooklyn.
       select branch-name
       from branch
       where assets > all
               (select assets
               from branch
               where branch-city = “Brooklyn”)
               Example Query
• Find all customers who have an account at all branches
  located in Brooklyn.
       select distinct S.customer-name
       from depositor as S
       where not exists (
               (select branch-name
               from branch
               where branch-city = “Brooklyn”)
               except
               (select R.branch-name
               from depositor as T, account as R
               where T.account-number = R.account-number and
                        S.customer-name = T.customer-name))
              Derived Relations
• Find the average account balance of those branches
   where the average account balance is greater than $1200.
        select branch-name, avg-balance
        from (select branch-name, avg (balance)
                from account
                group by branch-name)
                as result (branch-name, avg-balance)
        where avg-balance > 1200
Note that we do not need to use the having clause, since we
compute in the from clause the temporary relation result,
   and
the attributes of result can be used directly in the where
   Modification of the Database -- Deletion
• Delete all account records at the Perryridge branch
      delete from account
      where branch-name = “Perryridge”
• Delete all accounts at every branch located in Needham.
      delete from account
      where branch-name in (select branch-name
                             from branch
                             where branch-city = “Needham”)
      delete from depositor
      where account-number in (select account-number
               from branch, account
               where branch-city = “Needham” and
               branch.branch-name = account.branch-name)
                Example Query
• Delete the records of all accounts with balances below the
  average at the bank
      delete from account
      where balance < (select avg (balance)
                        from account)
   – Problem: as we delete tuples from deposit, the average
     balance changes
   – Solution used in SQL:
      • First, compute avg balance and find all tuples to
        delete
      • Next, delete all tuples found above (without
        recomputing avg or retesting the tuples)
 Modification of the Database -- Insertion

• Add a new tuple to account
       insert into account
               values (“Perryridge”, A-9732, 1200)
  or equivalently
  insert into account (branch-name, balance, account-number)
       values (“Perryridge”, 1200, A-9732)
• Add a new tuple to account with balance set to null
       insert into account
               values (“Perryridge”, A-777, null)
Modification of the Database -- Updates
• Increase all accounts with balances over $10,000 by 6%,
  all other accounts receive 5%.
   – Write two update statements:
                      update account
                      set balance = balance * 1.06
                      where balance > 10000

                     update account
                     set balance = balance * 1.05
                     where balance 1000
   – The order is important         
   – Can be done better using the case statement

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:39
posted:1/1/2012
language:English
pages:55