Docstoc

Relational Model

Document Sample
Relational Model Powered By Docstoc
					Relational Model

Reading: C&B, Chap 3
    In this lecture you will learn
• The concept of Model
• Simple definitions of terminology in Relational
  Model
• Why relational model is important?
• Initial idea of Relational Querying




   Dept. of Computing Science, University of Aberdeen   2
                        Introduction
• DBMS models data with respect to
  – Machine
      • physical storage of data
      • involves low level details that are not important for user
  – User
      • logical view of data
• We focus on User’s ‘DATA MODEL’




  Dept. of Computing Science, University of Aberdeen                 3
            The Concept of ‘Model’
• A model represents ‘something’.
   – At a level of detail suitable for its purpose
• For example, an architects ‘model’ of a bridge
   – Architects model brings the bridge to life even before its
     construction
• Models can be:
   – Blueprints for building the real ‘something’s (such as bridges)
   – Useful for testing (model a bridge and test it before
     construction)
   – Handy for learning (you start driving lessons on a simulator)
• All our thinking is based on ‘models’ of things
   – We build models of everything
• Thinking with models is considered ‘SMART’


   Dept. of Computing Science, University of Aberdeen              4
  Learning with Relational Model
• Theory of databases involves many models
   – Relational model, ER model
• Relational model is the user’s data model and we
  learn it first
• DBMSs that use relational model are called Relational
  Database Management Systems (RDBMS)
• Apply our learning on a few real RDBMS (MSAccess &
  SQL server)
   – Mostly in practical classes
   – Also in lectures
• Hopefully this training will help us with other RDBMS
  as well (such as MySQL and Oracle)

   Dept. of Computing Science, University of Aberdeen     5
            Why Relational Model?
• Most modern database software is based on relational
  model
   – Relational model is what the user sees in a RDBMS
• Relational Model has a simple logical structure (as we
  will see later)
• It is intuitive (as we will see later)
• Relational model has its origins in sound mathematics
   – Set theory and theory of relations (hence the name
     relational)
   – We study the mathematics later in the course
• Relational Model describes the pure concepts behind
  real database systems
   – Real systems at times may have unfaithful implementations
     of these pure concepts

   Dept. of Computing Science, University of Aberdeen            6
        What is relational model?
• A logical representation of a database
• Proposed by IBM’s E. F. Codd in 1970
  – Won a Turing Award for the model!!
• Earlier data models were not relational. They
  were
  – Hierarchical (tree) or
  – Network (graph)
• We focus on relational models in this course


   Dept. of Computing Science, University of Aberdeen   7
         Relational Model is simple
• Relational Database: a set of tables (relations)
  – Named Tables (relations) have
      • Named Columns (attributes)
      • Unnamed Rows (tuples)
  – Domain: a set of allowable values for one more
    columns
  – Relationships
  – Keys (primary, foreign)
  – Integrity Constraints
• It’s that simple!
    Dept. of Computing Science, University of Aberdeen   8
              Example: DreamHome
• DreamHome models data in a typical real estate business
   – you will use this database in the practical
• DreamHome has data related to
   – Branch offices
   – Staff in each branch office
   – …
• Branch office data
   – Branch number
   – Address
• Staff data
   – Staff number
   – Staff details
• Since staff belong to a branch office, there is a relationship
  between staff data and branch office data.

    Dept. of Computing Science, University of Aberdeen             9
              Branch          Example: DreamHome 2                                                        Attributes


                   branchNo             Street                  City               Postcode
                   B005                 22 Deer Rd              London             SW1 4EH
Relation           B007                 16 Argyll St            Aberdeen           AB2 3SU
                   B003                 163 Main St             Glasgow            G11 9QX
                   B004                 32 Clover Dr            Bristol            BS99 1NZ
                   B002                 56 Clover Dr            London             NW10 6EU


           Staff          Primary Key                                                    Foreign Key

            staffNo       fName     LName          Position            Sex   DOB         Salary        branchNo


            SL21          John      White          Manager             M     1-Oct-45    30000         B005


            SG37          Ann       Beech          Assistant           F     10-Nov-60   12000         B003
Relation
            SG14          David     Ford           Supervisor          M     24-Mar-58   18000         B003


            SA9           Mary      Howe           Assistant           F     19-Feb-70   9000          B007


            SG5           Susan     Brand          Manager             F     13-Jun-40   24000         B003


            SL41          Julie     Lee            Assistant           F     13-Jun-65   9000          B005

              Dept. of Computing Science, University of Aberdeen                                                  10
                       Relation (Table)
• Relation has two parts
    – Schema
        • Name of the relation and the set of attributes & their
          respective domains
        • For example,
Branch (branchNo:domain1, street: domain2, city:domain3, postcode:domain4)
    – Instance
        • Set of attributes and their values in a tuple
        • For example,
(branchNo:domain1, street: domain2, city:domain3,postcode:domain4)



     Dept. of Computing Science, University of Aberdeen                11
                                      Key
• A key is an attribute or set of attributes that
  uniquely identify a row
• A table may have many candidate keys
• Primary Key: Key selected for unique identification of
  rows
   – For example, branchNo in the Branch table
   – Cannot be NULL
• Foreign Key: an attribute or a set of attributes in one
  table that refer to a row in another table
   – For example, branchNo in Staff table


   Dept. of Computing Science, University of Aberdeen   12
            Properties of relations
• Rows & columns can be in any order
• no 2 rows are identical
• all values belonging to a particular attribute
  are from the same domain
• attributes are atomic
• attributes may have a null value (but not the
  primary key)



   Dept. of Computing Science, University of Aberdeen   13
     Relational model is intuitive
• Many common datasets naturally fit relational
  model
• Telephone directory
  – Directory(Name:domain1, Address:domain2,
    TelNo:domain3)
• Class Attendance Sheets
  – Attendance(Name:domain1, IDNo:domain2,
    Signature: domain3)
• Try with other datasets you know

   Dept. of Computing Science, University of Aberdeen   14
 Data Retrieval: Relational Querying

• The main strength of relational model: supports
  simple yet powerful data retrieval (querying in
  general)
• Output of a query is modelled as a relation (table)
• In RDBMS users view data always in terms of tables
   – if you learn relational model well you can find it easy to use
     databases
• SQL (see-quel): a popular query language for
  relational databases.



   Dept. of Computing Science, University of Aberdeen                 15
   Example: Relational Querying
                                             Branch
• Consider the branch                        branchNo   Street         City       Postcode

  relation from the                          B005       22 Deer Rd     London     SW1 4EH

  DreamHome database                         B007       16 Argyll St   Aberdeen   AB2 3SU


• Query: we want a list of                   B003       163 Main St    Glasgow    G11 9QX


  branches in London                         B004       32 Clover Dr   Bristol    BS99 1NZ

                                             B002       56 Clover Dr   London     NW10 6EU
• Output is a sub-relation
  (sub-table) of the
                                             London Branches
  Branch relation (table)
                                             branchNo   Street         City       Postcode

                                             B005       22 Deer Rd     London     SW1 4EH

                                             B002       56 Clover Dr   London     NW10 6EU




   Dept. of Computing Science, University of Aberdeen                                        16
                             Conclusion
• Relational model of database: a set of tables
  – Conceptually relational model is simple
  – Formal definitions of all the terminology will be
    introduced later
• User interactions with RDBMS are through
  SQL (Query language)
• We now focus on learning SQL




   Dept. of Computing Science, University of Aberdeen   17

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:8
posted:8/20/2012
language:English
pages:17