Relational Model Terminology

• Relations
  • Relations and tables are not the same thing.
  • All relations are tables, but not all tables are relations.
  • A relation has the following characteristics:
    • Rows contain data about an entity
    • Columns contain data about attributes of the entities
    • All entries in a column are of the same kind.
    • Each column has a unique name.
    • Cells in a table hold a single value.
    • The order of the columns in unimportant.
    • The order of the rows is unimportant.
    • No two rows may be identical.
  • Look at the tables on 104-5, are they relations? Why or why not.
• We are cautioned that int he literature, sometimes
  • Columns are referred to as attributes
  • Rows are referred to as tuples.
• And sometimes
  • file and table are use interchangeably
  • field and column
  • record and row
• Functional Dependency
  • Two fields in a relation have a functional dependency if one field completely determines the other.
  • For example, if we know the buyer, we know the Department (in the SKU_DATA) table.
  • In this case we say that the Buyer determines the Department
  • Or use the notation Buyer → Department
  • In this case, the buyer is the determinant
• The determinant can be compound, or composite.
• In the ORDER_ITEM table
  • Does the Quantity determine the ExtendPrice?
  • Does the Price
  • How about Price × Quantity?
  • (Quantity, Price) → ExtendedPrice
  • While this is a direct computation (ExtendedPrice = Quantity × Price), most functional dependencies are not.
• Does Department determine Buyer in SKU_DATA?
• Does Buyer determine anything else in SKU_DATA?
• What other functional dependencies exist in SKU_DATA?
  • SKU → (SKU_DESCRIPTION, Department, Buyer)
  • SKU_Description → (SKU, Department, Buyer)
• What functional dependencies exist in ORDER_ITEM
  • We have that (Quantity, Price) → ExtendedPrice
  • We can notice that SKU → Price
  • But we must be careful here, it is true in our table, but after talking to the users, we find that prices may change.
  • We will be cautioned later, knowledge of the process is not completely contained in the existing data.
  • (OrderNumber, SKU) → (Quantity, Price, ExtendedPrice)
• Keys
  • A key is a combination of one or more columns that identify a rows in a relation.
  • Keys with two or more columns are called compound keys
  • A candidate key
    • Is a determinate that determines all other columns in a relation.
    • SKU and SKU_Description are candidate keys in SKU_DATA
    • If we know the value of a candidate key, we can find one, and only one row in the relation.
  • A primary key is one of the candidate keys
  • A surrogate key is an artificial column added to a table to serve as a primary key.
    • This is used when the primary keys are large and unwieldy
    • For example, if an address (City, State, street, zip) would be needed for a primary key, a surrogate key would be appropriate.
  • We will discuss primary and surrogate keys in chapter 6.
  • A foreign key is a column, or composite of columns that is the primary key of a table other than the one in which it appears.