Data Management Class Notes for 28/3/2014

How to login to MySQL

Review for Exam

• 1 hour (covering first 2 homeworks)
• What to know:
• • ER Diagrams
  • • Entities
    • Attributes
    • • Base or derived
      • single valued or multivalued
      • simple or composite
    • Relations
    • • one to one
      • many to one
      • one to many
      • many to many
    • Keys
    • • superkey
      • candidate key
      • primary key
      • foreign key
      • weak entity set VS strong entity set
      • • discriminant (no child from the same mother can have the same name -> name is a discriminant)
    • ISA
    • • disjoint
      • overlapping
      • total
      • partial
  • How to go from ER to Relational Model
    
  • • Relational Schema
    • • table name
      • column names
      • domains
    • Primary keys
    • Identify foreign keys
    • Crows feet notation for cardinality

SQL as Data Definition Language

• Assertions
• • it is like a CHECK constraint, but it is not attached to a table definition (free floating)
  • CREATE ASSERTION Assertion01 CHECK ( (SELECT COUNT (*)…..);
• Triggers
• • A piece of code that executes after you put some data in a table
  • CREATE TRIGGER Trigger01 AFTER INSERT ON ….. END Trigger01;
• Views
• • A table that is continuously updating (dependant on one or more other tables)
  • some views cannot be updated
  • can add a trigger to automatically update tables behind the view
• Additional commands
• • ALTER
  • • some features are changed
  • DROP
  • • remove (e.g. table)
  • REPLACE
  • • replace by new object (substitute)
• Privileges
• • can be granted to user or be PUBLIC
  • • operations
    • references on
    • • base tables
      • views
  • types of privileges
  • • select
    • insert
    • update
    • delete
    • references
  • Examples: 
  • • GRANT SELECT, INSERT ON Customer TO Li, Brown;
    • restricted by columns: GRANT SELECT, INSERT ON Customer.City TO Li, Brown;
    • pass the privileges to other users: <some sql> WITH GRANT OPTION;
  • Privilege to reference
  • • allow a user to use columns in a table when foreign keys are primary keys in other tables that user does not have privileges for (slide 72)
    • GRANT REFERENCES (C) ON Customer TO Li;
  • Privileges on views
  • Revoking Privileges
  • Remark about testing equals with 'null' (a = b)
  • • In DDL an equals with a='null' and b='b' will evaluate to false
    • In DML an equals with a='null' and b='b' will evaluate to true
    • this can create problems as an insert can 'override' the table definition