up

2020-02-06

Looks like I missed something important last class

Catchup

This section is for the info I find in myCourses about last class

• HW3 is due on the 11th at 11:59PM
• New Data Types
  • SMALLINT
    • an int is 4 bytes but a smallint is only 2
  • DECIMAL
    • two arguments: precision and scale
    • precision: how many digits. 999.99 has 5.
    • scale: How many of the digits are to the right of the decimal
  • Things can be UNSIGNED
• New Constraints
  • NOT NULL
    • it cannot be null
    • Attribute level
  • CHECK
    • keeps the value of a column compliant with an expression
    • Attribute level: Age int CHECK >= 18 (I think)
    • Table level: CONSTRAINT table_chk CHECK (Age>=18)
    • Literally ignored by MySQL
  • DEFAULT
    • what is it by default?
    • Name varchar(255) DEFAULT 'Greg',
  • Atomic vs Relative data integrity checks
    • Atomic
      • looks at proposed value for attribute before insertion
      • NOT NULL
    • Relative
      • Looks at proposed field value compared to other data
      • UNIQUE
  • Naming
    • What?
  • Table versus Attribute Level
    • What?
• DML
  • SELECT
    • Relational Operators (>, >=, LIKE string, IN (list), etc)
    • LIKE operators:
      • _ matches any single char
      • % matches 0 or more chars
      • WHERE thing LIKE '%.exe' matches exe files
    • WHERE attribute BETWEEN low AND high
    • WHERE attribute = value OR attribute = otherValue
    • Calculations
      • SELECT
  • UPDATE
  • DELETE
    • Will delete everything
    • WHERE clauses are mandatory here
• DDL
  • ALTER
  • The Help System

HW2 review

• Converting from an ER diagram to a Relation
  • Colors should be included in the Relation, even though there’s no way to store it (it’s multi-valued)