; p2
Documents
Resources
Learning Center
Upload
Plans & pricing Sign in
Sign Out
Your Federal Quarterly Tax Payments are due April 15th Get Help Now >>

p2

VIEWS: 7 PAGES: 23

  • pg 1
									           CPSC-608 Database Systems
                       Fall 2010

                 Instructor: Jianer Chen
                 Office: HRBB 315C
                 Phone: 845-4259
                 Email: chen@cse.tamu.edu


Notes #2
                                                                                  in tables
                                                                                 (relations)

                                           lock table
                    DDL
                 language
  database                       DDL      concurrency
administrator
                               complier                                 file
                                             control     logging &
                                                                      manager
                                                          recovery



                                          transaction
                                            manager
                                                         index/file    buffer
                                                          manager     manager
                DML (query)
                 language
 database                                   query
programmer                                 execution
                                            engine
                                 DML
                               complier                                 main
                                                                       memory
                                                                       buffers
                                                                                 secondary
                                                                                  storage
                              DBMS                                                 (disks)


                                                   graduate database
                                                                                in tables
                                                                               (relations)

                                           lock table
                    DDL
                 language
  database                       DDL      concurrency
administrator
                               complier                                file
                                             control    logging &
                                                                     manager
                                                         recovery



                                          transaction
                                            manager
                                                        index/file    buffer
                                                         manager     manager
                DML (query)
                 language
 database                                   query
programmer                                 execution
                                            engine
                                 DML
                               complier                               main
                                                                     memory
                                                                     buffers
                                                                               secondary
                                                                                storage
                              DBMS                                               (disks)


                                  A Quick Review on Undergraduate Database
          We have agreed
Information (i.e., database) is organized
  in tables (i.e., relations) stored in disks.
          We have agreed
Information (i.e., database) is organized
  in tables (i.e., relations).

● How is information represented by
 relations?
● What are “good” table structures?
● What operations can we apply on
 tables?
How is information represented
         by relations?
 How is information represented
          by relations?
Information consists of
● objects (i.e., entities) plus
● connections (i.e., relationships) among
  entities
 How is information represented
          by relations?
Information consists of
● objects (i.e., entities) plus
● connections (i.e., relationships) among
  entities
Thus, information can be given by
Entity/relationship (R/E) diagrams
 How is information represented
          by relations?
Information consists of
● objects (i.e., entities) plus
● connections (i.e., relationships) among
  entities
Thus, information can be given by
Entity/relationship (R/E) diagrams
Read: Sections 4.1-4.4
 The Entity/Relationship Model
How to convert E/R diagrams
 into relations (i.e., tables)?
  How to convert E/R diagrams
   into relations (i.e., tables)?
Fairly straightforward:
How are E/R diagrams converted
  into relations (i.e., tables)?
Fairly straightforward:
● an entity set is given by a table where
  each column corresponds to a property
  (i.e., attribute) of the entities;
● a relationship among entities is given
  by a table whose columns correspond
  to the identifications of the related
  entities (that now become attributes).
How are E/R diagrams converted
  into relations (i.e., tables)?
Fairly straightforward:
● an entity set is given by a table where
  each column corresponds to a property
  (i.e., attribute) of the entities;
● a relationship among entities is given
  by a table whose columns correspond
  to the identifications of the related
  entities (that now become attributes).
Read: sections 4.5.
 From E/R Diagrams to Relational Designs
What are “good” table strcutures?
● have no inconsistency;
● avoid redundancy;
● easy to use
What are “good” table strcutures?
● have no inconsistency;
● avoid redundancy;
● easy to use
Typical questions:
● Should we split a table when it is too fat?
● Should we merge tables when they are too
  thin?
What are “good” table strcutures?
● have no inconsistency;
● avoid redundancy;
● easy to use
Typical questions:
● Should we split a table when it is too fat?
● Should we merge tables when they are too
  thin?
Read: Chapter 3.
 Design Theory for Relational Databases
             Some terminology
Relation, attribute, tuples
Attributes
(column
headers)     name          manf
 Tuples      Winterbrew    Pete’s
 (rows)      Bud Lite      Anheuser-Busch
                      Beers

                              a relation
        Some terminology
Keys and superkeys
• Superkey: a set of attributes that
   uniquely determines a tuple;
• Key: a superkey that does not contain
   any smaller superkey.
        Some terminology
• Relation schema: relation name and
  attribute list.
• Database schema: set of all relation
  schemas in the database.
• Database: collection of relations.
        Relational operations
Typically, selecting tuples that meet a
  given condition.
        Relational operations
Core relational operations:
• Union, intersection, and difference.
  – Usual set operations;
  – Extended to bags
• Selection: picking certain rows.
• Projection: picking certain columns.
• Products and joins: compositions of
  relations.
• Renaming of relations and attributes.
        Relational operations
extended relational operations:
●δ = eliminate duplicates from bags.
●τ = sort tuples.


●γ = grouping and aggregation.
        Relational operations
extended relational operations:
●δ = eliminate duplicates from bags.
●τ = sort tuples.


●γ = grouping and aggregation.




Read: Chapter 5
 Algebraic and Logical Query Languages

								
To top