Comp 305-001/488-305: Database Administration

Spring 2020: Mondays, 5:30-8:00

Week 2

Homework 1: on Sakai


0. Submit as a text file! (see also Rule 4)

1. Your queries should work even if additional data is added to the tables.

2. Every table referred to in the FROM clause should be named with a "table alias", that is e and d in:
    select e.lname, d.dname from employee e, department d ...

3. All answers should be in the form of a single query; do not retrieve a value with one query and then manually plug that value into a second query. Similarly, data appearing in the query should be from the exercise itself, and not from a "visual" lookup.

4. All SQL should be entered in a format that I can copy and paste directly into a command window. In particular, make sure:

    All "prompt" characters such as "->" have been stripped out.
    You use regular quotation marks, not special unicode quotation marks. Unicode-quoted ‛foo’ is not the same as 'foo'. (A sure way to convert inadvertently to unicode quotes is to paste your work into a Word document; try installing Notepad++. Or, better yet, Atom.) Postgres requires single quotation marks for strings: 'string'.
    There are no leading tabs or spaces

5. Use explicit join notation for joins
    select e.lname from EMPLOYEE E JOIN DEPARTMENT D ON E.DNO = D.DNUMBER where...

Uber and the switch from Postgres to MySQL

Introduction to Relational Databases

Start with Mappings

  1. Intro to multiple tables and SQL
  2. DBA tasks
  3. "Mongo is web scale". Yeah.
  4. Oracle, Postgres and MySQL
  5. Mappings
  6. Terminology; more about tables
  7. NoSQL
  8. SQL select
  9. SQL joins
  10. keys
  11. foreign keys
  12. Entity-Relation (ER) diagrams
  13. The Company DB
  14. Relational Math
  15. concepts: self-describing, transactions


Installation assignment:

    Install PostgreSQL on your own machine