Normalizing Tables

(avoiding redundancy/duplication)

Courses Table

Primary Key: Code

Code Title Cap
COMP 105 Intro to CS 15
COMP 107 Pictures & Sound 15
COMP 108 Scientific Computing 20
COMP 110 Intro to Prog 15
COMP 210 Data Structures 25
COMP 215 Algorithms 25
COMP 230 Comp. Org. 25
COMP 300 Automata 20
COMP 430 Op. Sys. 20
COMP 483 Cryptography 20
COMP 488 FOSS Development 15
COMP 489 Mobile Development 15
COMP 490 SR Sem 15

A key is a field you can search or sort on.
A primary key uniquely identifies each record.

Offerings Table

Primary Key: PKey

(better than (CourseCode, Section, Term, Year) tuple)

PKey CourseCode Section Term Enrollment
1 COMP 105 01 F16 14
2 COMP 105 02 F16 15
3 COMP 108 01 F16 17
4 COMP 210 01 F16 17
5 COMP 488 01 F16 15
6 COMP 490 01 F16 16
7 COMP 105 01 W17 21
8 COMP 110 01 W17 18
9 COMP 110 02 W17 14
10 COMP 215 01 W17 27
11 COMP 430 01 W17 23
12 COMP 483 01 W17 20
13 COMP 107 01 S17 14
14 COMP 107 02 S17 17
15 COMP 110 01 S17 16
16 COMP 210 01 S17 17
17 COMP 230 01 S17 21
18 COMP 300 01 S17 26
19 COMP 105 01 F17 15
20 COMP 105 02 F17 15
21 COMP 108 01 F17 22
22 COMP 210 01 F17 15
23 COMP 489 01 F17 11
24 COMP 490 01 F17 21

CourseCode and Term are foreign keys: fields that are equivalent to primary keys in another table, thus connecting the two tables.

Term Table

Primary Key: Term

(better than (TermName, Year) tuple)

Term TermName Year StartDate
F16 Fall 2016 09/12/2016
W17 Winter 2017 01/03/2017
S17 Spring 2017 03/27/2017
F17 Fall 2017 09/11/2017

But: How do we get the report/table we had before?

SQL Query

SELECT CourseCode, Section, TermName, Year, StartDate, Title, Cap, Enrollment FROM Offerings
LEFT JOIN Courses ON Offerings.CourseCode = Courses.Code
LEFT JOIN Term ON Offerings.Term = Term.Term
ORDER BY StartDate, CourseCode, Section;

CourseCode Section TermName Year StartDate Title Cap Enrollment
COMP 105 01 Fall 2016 09/12/2016 Intro to CS 15 14
COMP 105 02 Fall 2016 09/12/2016 Intro to CS 15 15
COMP 108 01 Fall 2016 09/12/2016 Scientific Computing 20 17
COMP 210 01 Fall 2016 09/12/2016 Data Structures 25 17
COMP 488 01 Fall 2016 09/12/2016 FOSS Development 15 15
COMP 490 01 Fall 2016 09/12/2016 SR Sem 15 16
COMP 105 01 Winter 2017 01/03/2017 Intro to CS 15 21
COMP 110 01 Winter 2017 01/03/2017 Intro to Prog 15 18
COMP 110 02 Winter 2017 01/03/2017 Intro to Prog 15 14
COMP 215 01 Winter 2017 01/03/2017 Algorithms 25 27
COMP 430 01 Winter 2017 01/03/2017 Op. Sys. 20 23
COMP 483 01 Winter 2017 01/03/2017 Cryptography 20 20
COMP 107 01 Spring 2017 03/27/2017 Pictures & Sound 15 14
COMP 107 02 Spring 2017 03/27/2017 Pictures & Sound 15 17
COMP 110 01 Spring 2017 03/27/2017 Intro to Prog 15 16
COMP 210 01 Spring 2017 03/27/2017 Data Structures 25 17
COMP 230 01 Spring 2017 03/27/2017 Comp. Org. 25 21
COMP 300 01 Spring 2017 03/27/2017 Automata 20 26
COMP 105 01 Fall 2017 09/11/2017 Intro to CS 15 15
COMP 105 02 Fall 2017 09/11/2017 Intro to CS 15 15
COMP 108 01 Fall 2017 09/11/2017 Scientific Computing 20 22
COMP 210 01 Fall 2017 09/11/2017 Data Structures 25 15
COMP 489 01 Fall 2017 09/11/2017 Mobile Development 15 11
COMP 490 01 Fall 2017 09/11/2017 SR Sem 15 21
Alyce Brady, Kalamazoo College