Docstoc

Day_2_-_The_Relational_Database_Model

Document Sample
Day_2_-_The_Relational_Database_Model Powered By Docstoc
					Information & Communication Technology




                  Database Administration




                        A Vocational Degree programme developed by MCAST and Fraunhofer IAO.
Information & Communication Technology




                                              Day 2
                       The Relational Database Model




                        A Vocational Degree programme developed by MCAST and Fraunhofer IAO.
Objectives

• Understand the relational model‟s logical structure
• Understand components of a relational database and their characteristics
  and relationships
• Understand basic concepts of table design
• Understand characteristics of good and bad tables
• Understand the relational database operators
• Learn how keys are used in the relational database environment
• Understand a simple data dictionary




                       A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   3
Overview

2.1   A Logical View of Data
2.2   Keys
2.3   Integrity Rules Revisited
2.4   Relational Database Operators
2.5   Relational Database Software Classification
2.6   The Data Dictionary and the System Catalog
2.7   Relationships within the Relational Database
2.8   Data Redundancy, Integrity Revisited
2.9   Indexes




                        A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   4
A Logical View of Data

• Relational database model‟s structural and data independence enables
  us to view data logically rather than physically.

• The logical view allows a simpler file concept of data storage.

• The use of logically independent tables is easier to understand.

• Logical simplicity yields simpler and more effective database design
  methodologies.




                        A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   5
A Logical View of Data

• Entities and Attributes

   – An entity is simply a person, place, event, or thing for which we intend to
     collect data.
       • Examples:
           – University -- Students, Faculty Members, Courses
           – Airlines -- Pilots, Aircraft, Routes, Suppliers


   – Each entity has certain characteristics known as attributes.
       • Examples:
           – Student -- Student Number, Name, GPA, Date of Enrollment, Data of Birth, Home Address,
             Phone Number, Major
           – Aircraft -- Aircraft Number, Data of Last Maintenance, Total Hours Flown, Hours Flown
             since Last Maintenance




                              A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   6
A Logical View of Data

• Entities and Attributes

   – A grouping of related entities becomes an entity set.
       • Examples:
           – The STUDENT entity set contains all student entities.
           – The FACULTY entity set contains all faculty entities.
           – The AIRCRAFT entity set contains all aircraft entities.




                                A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   7
A Logical View of Data

• Tables and Their Characteristics

   – A table contains a group of related entities -- i.e. an entity set.

   – The terms entity set and table are often used interchangeably.

   – A table is also called a relation.




                           A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   8
A Logical View of Data

• Characteristics of a Relational Table

   – A table is perceived as a two-dimensional structure composed of rows and
     columns.

   – Each table row (tuple) represents a single entity within the entity set.

   – Each column represents an attribute and each column has a distinct name.

   – Each row/column intersection represents a single data value.

   – Each table must have a primary key that uniquely identifies each row.




                          A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   9
A Logical View of Data

• Characteristics of a Relational Table

   – All values in a column must conform to the same data format.

   – Each column has a specific range of values known as the attribute domain.

   – Each row carries information describing one entity occurrence.

   – The order of the rows and columns is immaterial to the DBMS.




                         A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   10
A Logical View of Data


                        Table,
                    Relation,
                   Entity Set,                                              Attribute, Column,
                          File                                             Characteristic, Field



    Real world entity a                                                                    Tuple, Entity, Row, Record



    Real world entity b

                                                                               Data value from attribute domain
                                                                               (or null)


                 Note: the file system terms „File“, „Field“ and „Record“ are
                 often mis-used in relational database context.


                          A Vocational Degree programme developed by MCAST and Fraunhofer IAO.                          11
A Logical View of Data

• Keys

   – Controlled redundancy (shared common attribute) makes the relational
     database work.

   – The primary key of one table appears again as the link (foreign key) in
     another table.

   – If the foreign key contains either matching values or nulls, the table(s) that
     make use of such a foreign key are said to exhibit referential integrity.




                           A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   12
A Logical View of Data




                     A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   13
Keys

• A key is a device that helps define entity relationships. It is an attribute
  that determines the values of other attributes within the entity.

• The key‟s role is based on a concept known as determination, which is
  used in the definition of functional dependence.

• The attribute B is functionally dependent on A if A determines B.

• An attribute that is part of a key is known as a key attribute.

• A multi-attribute key is known as a composite key.




                         A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   14
Keys
                       Table 2.2 Relational Database Keys

       Superkey         An attribute (or combination of attributes) that
                        uniquely identifies each entity in a table.
       Candidate Key    A minimal superkey. A superkey that does not
                        contain a subset of attributes that is itself a
                        superkey.
       Primary Key      A candidate key selected to uniquely identify all
                        other attribute values in any given row. Cannot
                        contain null entities.
       Secondary Key An attribute (or combination of attributes) used
                     strictly for data retrieval purposes.
       Foreign Key      An attribute (or combination of attributes) in one
                        table whose values must either match the primary
                        key in another table or be null.




                        A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   15
Integrity Rules Revisited
                         Table 2.3 Integrity Rules Revisited

      ENTITY INTEGRITY
      Requirement All entities are unique and no null entries in a primary key.
      Purpose        Guarantees that each entity will have a unique identity.

      REFERENTIAL INTEGRITY
      Requirement Foreign key must have either a null entry or an entry that
                     matches the primary key value in a table to which it is
                     related.

      Purpose        Makes it possible for an attribute NOT to have a
                     corresponding value, but it will be impossible to have an
                     invalid entity. The enforcement of the referential integrity
                     rule makes it impossible to delete a row in one table whose
                     primary key has mandatory matching foreign key values in
                     another table.

      Example        A customer might not (yet) have an assigned sales
                     representative (number), but it will be impossible to have an
                     invalid sales representative (number).


                          A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   16
 Naming Conventions

 • Homonyms appear when more than one attribute has the same name
 • Synonyms exist when the same attribute has more than one name
 • Avoid both to avoid inconsistencies!


an exception from
the rule – synonymous
multiple foreign keys:

  Type       Flight_No   Pilot_In_Command          Co-Pilot                 P_No         P_Lastname       P_Firstname   P_Flight_Hours

  A300-600   LH-615      9234                      3421                     9234         Miller           Chuck B.      1850

  A300-400   LH-923      3421                      4097                     4097         Brown            Phil G.       2345

                                                                            3421         Jones            Bob D.        9620




                                   A Vocational Degree programme developed by MCAST and Fraunhofer IAO.                                  17
Relational Database Operators

• The degree of relational completeness can be defined by the extent to
  which relational algebra is supported.
• Relational algebra defines the theoretical way of manipulating table
  contents using the eight relational functions:
   – SELECT
   – PROJECT
   – JOIN
   – INTERSECT
   – UNION
   – DIFFERENCE
   – PRODUCT
   – DIVIDE



                       A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   18
Relational Database Operators

• UNION combines all rows from two tables. The two tables must be union
  compatible.
• Tables are called union compatible if they have the same attribute
  characteristics (column names and domains must be identical)




                      A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   19
Relational Database Operators

• INTERSECT produces a listing that contains only the rows that appear in
  both tables. The two tables must be union compatible.




                       A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   20
Relational Database Operators

• DIFFERENCE yields all rows in one table that are not found in the other
  table; i.e., it subtracts one table from the other. The tables must be union
  compatible.




                         A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   21
Relational Database Operators

• PRODUCT produces a list of all possible pairs of rows from two tables.




                       A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   22
Relational Database Operators

• SELECT yields values for all attributes found in a table. It yields a
  horizontal subset of a table.




                         A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   23
Relational Database Operators

• PROJECT produces a list of all values for selected attributes. It yields a
  vertical subset of a table.




                        A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   24
Relational Database Operators

• JOIN allows us to combine information from two or more tables. JOIN is
  the real power behind the relational database, allowing the use of
  independent tables linked by common attributes.




                       A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   25
Relational Database Operators

• Natural JOIN links tables by selecting only the rows with common
  values in their common attribute(s). It is the result of a three-stage
  process:
   1. A PRODUCT is performed on two tables.
   2. A SELECT is performed to yield only the rows for which the common
      attribute values match.
   3. A PROJECT is performed to yield a single copy of each attribute, thereby
      eliminating duplicate column.




                         A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   26
Relational Database Operators
 Cus_Code    Cus_Name     Zip              Zip                Town                   State
010          Brown       67661           67661         Kaiserslautern              RLP

103          McCain      62504           62504         Wiesbaden                   Hessia                   Example: Natural Join
231          Benson      65207           66879         Kollweiler                  RLP

                            Product
  Cus_Code    Cus_Name    Zip_1   Zip_2               Town                   State
 010          Brown       67661   67661       Kaiserslautern              RLP
 010          Brown       67661   62504       Wiesbaden                   Hessia
                                                                                                Select
 010          Brown       67661   66879       Kollweiler                  RLP                                       • only matched entries
 103          McCain      62504   67661       Kaiserslautern              RLP                                         are retained
                                                                                                                    • note that the entities
 103          McCain      62504   62504       Wiesbaden                   Hessia
                                                                                                                      „benson“ and
 103          McCain      62504   66879       Kollweiler                  RLP                                         „Kollweiler“ are lost
 231          Benson      65207   67661       Kaiserslautern              RLP
 231          Benson      65207   62504       Wiesbaden                   Hessia
 231          Benson      65207   66879       Kollweiler                  RLP


                                                          Cus_Code             Cus_Name                   Zip        Town         State
                                  Project
                                                          010                  Brown                 67661      Kaiserslautern   RLP

                                                          103                  McCain                62504      Wiesbaden        Hessia
                                   A Vocational Degree programme developed by MCAST and Fraunhofer IAO.                                    27
Relational Database Operators

• EquiJOIN links tables based on an equality condition that compares
  specified columns of each table. The outcome of the EquiJOIN does not
  eliminate duplicate columns and the condition or criteria to join the tables
  must be explicitly defined.

• ThetaJOIN is an equiJOIN that compares specified columns of each
  table using a comparison operator other than the equality comparison
  operator.

• In an OuterJOIN the unmatched pairs would be retained and the values
  for the unmatched other tables would be left blank or null.




                         A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   28
Relational Database Operators
 Cus_Code    Cus_Name     Zip              Zip                Town                   State
 010         Brown       67661           67661         Kaiserslautern              RLP

 103         McCain      62504           62504         Wiesbaden                   Hessia                  Example: Equi Join
 231         Benson      65207           66879         Kollweiler                  RLP

                            Product
  Cus_Code    Cus_Name    Zip_1   Zip_2               Town                   State
  010         Brown       67661   67661       Kaiserslautern              RLP
  010         Brown       67661   62504       Wiesbaden                   Hessia
                                                                                                Select
  010         Brown       67661   66879       Kollweiler                  RLP
  103         McCain      62504   67661       Kaiserslautern              RLP
  103         McCain      62504   62504       Wiesbaden                   Hessia
  103         McCain      62504   66879       Kollweiler                  RLP
  231         Benson      65207   67661       Kaiserslautern              RLP
  231         Benson      65207   62504       Wiesbaden                   Hessia
  231         Benson      65207   66879       Kollweiler                  RLP


                                                        Cus_Code             Cus_Name              Zip_1    Zip_2        Town         State

                                                        010                  Brown                 67661    67661   Kaiserslautern   RLP

                                                        103                  McCain                62504    62504   Wiesbaden        Hessia
                                   A Vocational Degree programme developed by MCAST and Fraunhofer IAO.                                    29
Relational Database Operators
                   Example: Outer Join

         Cus_Code    Cus_Name            Zip                       Zip                Town                   State

         010         Brown             67661                    67661         Kaiserslautern              RLP
         103         McCain            62504                    62504         Wiesbaden                   Hessia

         231         Benson            65207                    66879         Kollweiler                  RLP



                                             Product

          Cus_Code    Cus_Name            Zip_1         Zip_2                 Town                   State

          010         Brown              67661          67661         Kaiserslautern                RLP

          103         McCain             62504          62504         Wiesbaden                     Hessia           • unmatched entries
                                                                                                                       are retained
          <null>      <null>             <null>         66879         Kollweiler                    RLP              • null values are
          231         Benson             65207          <null>        <null>                        <null>
                                                                                                                       provided for the
                                                                                                                       attributes comming
                                                                                                                       from the other table
                                                                                                                       indicating the „no
                                                                                                                       match“ condition

                             A Vocational Degree programme developed by MCAST and Fraunhofer IAO.                                        30
Relational Database Operators

• DIVIDE requires the use of one single-column table and one two-column
  table.
               Table 1                                                     Table 2                       Table 3
               CODE            LOC                                         CODE                          LOC
               A               5                                           A                             5
               B               5                                           B
               C               6
               D               7                    DIVIDE                                      Yields
               D               8
               E               8
               A               9
               A               4
               B               3


• To be included in table 3, a value in the unshared column (LOC) must be
  associated with every value from table 2 in table 1.

                         A Vocational Degree programme developed by MCAST and Fraunhofer IAO.                      31
Relational Database Software Classification

• Not all relational DBMSs are equal. Some are more relational than
  others.

• The degree to which a DBMS conforms to the fully relational model
  affects database design and implementation effort.

• It is important that a relational DBMS should at least enforce the integrity
  rules.




                         A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   32
Relational Database Software Classification

                      Table 2.4 Relational Database Classification




       Fully relational                 Supports all eight relational-algebra functions and also
                                        enforces both entity and referential integrity rules.

       Relationally complete            Supports all eight relational-algebra functions but
                                        not the integrity rules.

       Minimally relational             Supports only SELECT, PROJECT, and JOIN.


       Tabular                          Supports only SELECT, PROJECT, and JOIN and
                                        requires that all access paths be defined by the user.




                              A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   33
The Data Dictionary and the System Catalog

• Data dictionary contains metadata to provide detailed accounting of all
  tables found within the database.

• System catalog is a very detailed system data dictionary. It describes all
  objects within the database.
   – System catalog is a system-created database whose tables store the
     database characteristics and contents.
   – System catalog tables can be queried just like any other tables.
   – System catalog automatically produces database documentation.




                        A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   34
Sample Data Dictionary




                    A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   35
Relationships within the Relational Database

• Entity Relationship (E-R) Model provides a simplified picture of the
  relationships among entities.

• E-R Diagram (ERD) is used to map the E-R model:
   – Rectangles are used to represent entities
     (to be precise: entity sets; but the name entity is used in ERDs)
   – Diamonds are used to represent the relationship(s) between the entities.
   – The number 1 is used to represent the “1” side of the relationship.
   – The letter M is used to represent the “many” sides of the relationship.




                         A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   36
Database Models Summary

• Entity Relationship Model (ERM)
   – graphical notation: entity-relationship diagrams (ERD)
   – modelling attributes:
     (often not used)

   – alternative graphical notation for relationships:
                                                   1               m


• Modelling constraints in ERDs:
                                                                             1                        4
   – constrained relationship:                               prof                                         class



   – constraints on fields -- cannot be modelled
       • example 1: „a pilot must not fly longer than 10 consecutive hours“ can only be modelled on the
         implementation model
       • example 2: „the average grade of classes must range between 1.0 and 4.0“


                               A Vocational Degree programme developed by MCAST and Fraunhofer IAO.               37
Relationships within the Relational Database




                                               Figure 2.3 An Alternate Way to Present the
                                              Relationship between PAINTER and PAINTING




   Figure 2.2 The Relationship                                    The ERDs orientation is immaterial.
 between PAINTER and PAINTING



                        A Vocational Degree programme developed by MCAST and Fraunhofer IAO.             38
Relationships within the Relational Database




            Figure 2.4 The Relationship Between COURSE and CLASS




                       A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   39
Relationships within the Relational Database




         Figure 2.5 The M:N Relationship Between STUDENT and CLASS




                                            • the ad-hoc implementation of the m:n relationship
                                              introduces data redundancy in both tables
                                            • and makes relational operaters clumsy (and probably
                                              yielding meaningless results)



                       A Vocational Degree programme developed by MCAST and Fraunhofer IAO.         40
Relationships within the Relational Database




using one
composite entity
(bridge entity) to
break a M:N into two
1:M relationships



                  Figure 2.6 Changing the M:N Relationship to two 1:M Relationships




                              A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   41
Relationships within the Relational Database




Figure 2.8 The Relational
Schema for Figure 2.7’s
Entity Relationship Diagram


                                                Figure 2.7 The Expanded Entity Relationship Model


                              A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   42
Relationships within the Relational Database




                  composite entity
                  implemented as
                    linking table
                                                             The Tables for Figure 2.7’s Entity
                                                           Relationship Diagram and Figure 2.8‘s
                                                                    Relational Schema




                      A Vocational Degree programme developed by MCAST and Fraunhofer IAO.         43
Data Redundancy Revisited

• Database designers must reconcile three often contradictory
  requirements: design elegance, processing speed, and information
  requirements.

• Sometimes controlled data redundancies are required to serve crucial
  information purposes as shown in the following INVOICE example:




                       A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   44
Data Redundancy Revisited




    Figure 2.9 The Relational Schema for Database Table 2.11’s Invoicing System




• In this example, the redundancy is crucial to the system‟s success.
  Copying the product price from the PRODUCT table to the LINE table
  means that it is possible to maintain the historical accuracy of the
  transactions.


                         A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   45
Integrity Revisited


                        manage
                          s
          1                                                           1
                    1   employ                  M
         STORE                                           EMPLOYEE
                          s
    M

         contains
                                  mapping ERD Relations to keys (just one possible solution):
     1
         REGION                           Table                             Primary key           Foreign Key(s)
                                EMPLOYEE                             EMP_CODE                    STORE_CODE
                                STORE                                STORE_CODE                  REGION_CODE,
                                                                                                 EMP_CODE
                                REGION                               REGION_CODE                 NONE



                          A Vocational Degree programme developed by MCAST and Fraunhofer IAO.                     46
Integrity Revisited




                      A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   47
Integrity Revisited

     Table      Entity Integrity?         Explanation

     EMPLOYEE   Yes                       Each EMP_CODE value is unique and there are no
                                          nulls
     STORE      Yes                       Each STORE_CODE value is unique and there are no
                                          nulls
     REGION     Yes                       Each REGION_CODE value is unique and there are no
                                          nulls


     Table      Referential Integrity?              Explanation

     EMPLOYEE   Yes                                 Each STORE_CODE value in EMPLOYEE points
                                                    to an existing STORE_CODE value in STORE.
     STORE      Yes                                 Each REGION_CODE value in STORE points to an
                                                    existing REGION_CODE value in REGION and
                                                    each EMP_CODE value in STORE points to an
                                                    existing EMP_CODE value in EMPLOYEE.
     REGION     N/A




                            A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   48
Indexes

• An index is composed of an index key and a set of pointers.




                      Figure 2.10 Components of an Index




                       A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   49
Avoiding NULL Values
                   • NULL values used to indicate absence of a value can result in data
                     loss in joins: entities with the null valued join attributes would be
                     dropped from a result list of a natural join

                                                   A_ID              A_Name                  A-Phone
  Cus_Code    Cus_Name         A_ID
                                                  345           Bond                        435-9234
  010         Brown           213
                                                  213           Meyers                      234-8889
  103         McCain          - 999
                                                  456           Bertrand                    234-7892
  231         Benson          345
                                                  - 999         xxxxx                       xxx-xxxx          using a flag value (-999)
                                                                                                                 and a dummy row



        Cus_Code      Cus_Name        A_ID              A_Name                 A_Phone

        010           Brown           213          Meyers                     234-8889

        231           Benson          345          Bond                       435-9234
                                                                                                     this row is retained by using
        103           McCain          - 999        xxxxx                      xxx-xxxx
                                                                                                     a flag value and a dummy row




                                       A Vocational Degree programme developed by MCAST and Fraunhofer IAO.                               50
Summary


Table 2.1 A Summary of the Relational Table„s Characteristics
1. A table is perceived as a two-dimensional structure composed of rows and
   columns
2. Each row (tuple) represents a single entity within the entity set.
3. Each column represents an attribute and each column has a distinct name.
4. Each row/column intersection represents a single data value.
5. Each table must have a primary key that uniquely identifies each row.
6. All values in a column must conform to the same data format. For example, if
   the attribute is assigned an integer data format, all values in the column
   representing that attribute must be integers.
7. Each column has a specific range of values known as attribute domain.
8. Each row carries information describing one entity occurrence.
9. The order of the rows and columns is immaterial to the DBMS.



                          A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   51
Summary

• Conceptual Models
   – Entity-Relationship-Model (ERM)
   – Object Oriented Model (OO)


• Implementation Models
   – file systems
   – hierarchical database model
   – network database model
   – relational database model
   – object oriented database model (OODBM)




                       A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   52
Summary




          A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   53
Summary




          A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   54
Exercise 1 of day 1

Identify and describe the components of the following database table, using correct
terminology. Use your knowledge of the naming conventions to identify the table's
probable foreign key(s).




                          A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   55
Exercise 2 of day 1

Keys: answer the following questions:
• What are keys?
• What types of keys may be found within a relational database and what are their
  function?
• Give examples for each key type!


Integrity: answer the following questions:
• Explain entity and referential integrity!
• Why are entity and referential integrity important in databases?
• Give examples!




                           A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   56
Exercise 3 of day 1

Using the following small database, illustrate the difference between a natural
JOIN, an equiJOIN, and an outerJOIN.

   Table STUDENT                                                  Table PROFESSOR




                          A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   57
Exercise 4 of day 1
Use the following database:




                       A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   58
Exercise 4 of day 1 - continued

• For each table, identify the primary key and the foreign key(s). If a table
  does not have a foreign key, write NONE in the assigned space.
• Do the tables exhibit entity integrity? Answer Yes or No, then explain
  your answer.
• Do the tables exhibit referential integrity? Answer yes or No, then explain
  your answer. Write NA (Not Applicable) if the table does not have a
  foreign key.
• Draw the Entity Relationship diagram for this database.
• Create the data dictionary for this database.




                        A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   59
Exercise 5 of day 1

ROBCOR is an aircraft charter company that supplies on-demand charter
flight services, using a fleet of four aircraft. Aircraft are always identified by
a (unique) registration number. Therefore, the aircraft registration number
is an appropriate primary key for the AIRCRAFT table.

The nulls in the CHARTER table's CHAR_COPILOT column indicate that a
copilot is not necessarily required for some charter trips or for some
aircraft. (Federal Aviation Administration rules require a copilot on jet
aircraft and on aircraft having a gross take-off weight over 12,500 lbs.
(None of the aircraft in the AIRCRAFT table is governed by this
requirement; however, some customers may require the presence of a
copilot for insurance reasons.) All charter trips are recorded in the
CHARTER table.



                          A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   60
Exercise 5 of day 1 - continued




                      A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   61
Exercise 5 of day 1 - continued




                      A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   62
Exercise 5 of day 1 - continued




                      A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   63
Exercise 5 of day 1 - continued




                      A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   64
Exercise 5 of day 1 - continued




                      A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   65
Exercise 5 of day 1 - continued




                      A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   66
Exercise 5 of day 1 - continued

Given this aviation database, named CH2_AVIA, work the following problems:

• For each table, identify, where possible,
    –   The primary key
    –   A superkey
    –   A candidate key
    –   The foreign key(s)
    –   A secondary key.


• Create the Entity Relationship diagram. Hint: Take a lo ok at the table contents.
  You will discover that an AIRCRAFT can be used to fly many CHARTER trips,
  but that each CHARTER trip is flown by one AIRCRAFT. Similarly, you will
  discover that a MODEL references many AIRCRAFT, but each AIRCRAFT is
  referenced by a single MODEL, and so on.



                             A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   67
Summary of day 2

• Basic building blocks of relational databases are entities, also called entity sets, which are
  stored in tables.
• Keys are central to the use of relational tables and several types with different
  characteristics are known: Superkey, Candidate Key, Primary Key, Secondary Key, Foreign
  Key
• Every table must have a unique primary key attribute allowing to find back any specific row.
• Tables are independent but they can be linked by sharing common attributes.
• Attribute sharing for the purpose of linking tables introduces controlled redundancy.
• Opposite to all other database models relational models exhibit structural independence.
• Relational database systems can by classified according to the degree to which the
  relational algebraic functions are implemented and also to the degree that integrity rules
  are enforced.
• Physical aspects of data storage are completely handled by the relational database system
  and are neither visible nor relevant to the usage of such a system.
• In order to organize data storage the RDBS uses various lists which are also kept in the
  form of tables.



                              A Vocational Degree programme developed by MCAST and Fraunhofer IAO.   68

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:8
posted:12/28/2011
language:English
pages:68