Comp 305-001/405-001: Database Administration
Spring 2024: MWF
Week 2
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
Introduction to
Relational Databases
install company DB
create table commands, primary keys
Some simple queries
Wednesday
Where did the murder mystery go?
\dn, \dt murder.*, select max(annual_income) from murder.income;
University database
foreign keys
Friday
My tale of database woe. (\l lists all databases, \dn lists schemas)
Do you really need foreign keys? www.shayon.dev/post/2023/355/do-you-really-need-foreign-keys.
It depends.
.psqlrc file
atomicity example
Joins (introduction.html#joins,
introduction.html#joins2)
- Names and grades for everyone in Section 112.
- Names of courses for all courses taught in Fall 08.
- Printing all of each student's grades, by joining the Student_number
fields of STUDENT and GRADE_REPORT
- Printing all sections including Course_name, joining COURSE and
SECTION on the Course_number field
- Company examples