Comp 305-001/405-001: Database Administration
Spring 2024: MWF
Week 1
Homework 1: sql1.text, due Friday, Feb 9
Read the notes Introduction
to Relational Databases
For those interested, here are references to Elmasri & Navathe (Chapter
numberings are (7th edition)/(6th edition))
References to Elmasri & Navathe (EN). Chapter numberings are (7th
edition)/(6th edition)
- Chapter 1/1, Databases and Database Users (skim)
- Chapter 2/2, Database System
Concepts an Architecture, Section 1 on Data Models, Schemas and
Instances
- Chapter 5/3, The Relational Data Model and SQL Sections 1
& 2
Course work:
- midterm
- final
- SQL assignments
- other homework?
Major concepts for this course (references are to Parts of EN)
- The basic relational-DB model, and SQL (part 2)
- DB modeling (part 3)
- Normalization & other design theory (part 6)
- internal DB structures (part 7)
- optimization & tuning (part 8)
- concurrency, locking, and transactions (part 9)
We will also cover a fair bit of DB administration, though that isn't
always so directly in EN.
Uber
and the switch from Postgres to MySQL
Introduction to
Relational Databases
- Intro to multiple tables and SQL
- DBA tasks
- "Mongo is web scale". Yeah.
- Oracle, Postgres and MySQL
- Terminology; more about tables
- NoSQL
- SQL select
- SQL joins
- keys
- foreign keys
- Entity-Relation (ER) diagrams
- The Company DB
- Relational Math
- concepts: self-describing, transactions
Installation
assignment:
Install
PostgreSQL on your own machine
Murder in SQL City
https://mystery.knightlab.com/
Also the walkthrough,
which has some helpful suggestions (and in particular the ER diagram!)
end of class Wednesday: we just identified Annabel Miller and Morty
Shapiro.