Relational Model and Normalization

• He begins by introducing the following table

  Buyer Name	SKU_Managed	College Major
  Pete Hansen	100100	Business Administration
  Pete Hansen	100200	Business Administration
  Nancy Meyers	101100	Art
  Nancy Meyers	101100	Info Systems
  Nancy Meyers	101200	Art
  Nancy Meyers	101200	Info Systems
  Cindy Lo	201000	History
  Cindy Lo	202000	History
  Jenny Martin	301000	Business Administration
  Jenny Martin	301000	English Literature
  Jenny Martin	302000	Business Administration
  Jenny Martin	302000	English Literature

  • We have multiple buyers in our company
  • Each Buyer manages multiple SKUs
  • Each Buyer can have multiple degrees.
  • Does the number of records seem right?
  • What do we need to do if Nancy Meyers gains an additional SKU?
  • How about an additional Degree?
  • This table has, what we will later define, multivalued dependencies
• Some Terminology
  • Relation:
    • Mostly relation and table can be used interchangeably
    • But a relation is a table with the following properties
      • Rows contain data about a single 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 of the table hold only a single value
      • The order of the columns is unimportant
      • The order of the rows is unimportant
      • No two rows may be identical.
    • All entries in a column are of the same kind
      • A column with a buyer name holds only buyer names.
      • This is not just a constraint on type
      • But also on content.
      • This is called a domain integrity constraint
    • He introduces an additional table (or two, but I have condensed them)

      Employee Number	Name	Email	Phone
      100	Jerry Johnson	jj@somewhere.com	834-1101
      200	Mary Abernathy	ma@somewhere.com	834-1102
      200	Mary Abernathy	ma@somewhere.com	834-1102
      300	Liz Smathers	ls@somewhere.com	834-1103
      		Home:	834-1104
      		Fax:	834-1105
      400	Tom Caruthers, President	tc@somewhere.com	834-1106
      500	Eleanore Caldera	ec@somewhere.com	834-1107, 834-1108,834-1109

    • This table violates many of the relation properties
      • Row order is important now, as the Home: and Fax: values need to follow Liz Smathers.
      • The Email column now contains different types of data
      • The phone column for Eleanore Caldera now contains multiple data values
      • Tom Caruthers' name field also contains position information.
      • Mary Abernathy appears twice.
    • In a spreadsheet, or any other human managed data form these would be fine.
    • And all of these can be ignored in a table.
    • But to be a relation, all must be followed.
    • If a table follows the rules then it is a relation
    • A database where all of the tables are relations is said to be in First Normal Form
  • Columns are called attributes
  • Rows are called tuples