# Preamble

## How to Use This Guide

These lecture notes are written in an elusive style: they are a support for the explanations that will be made at the board. They are not designed to be self-contained: they are rather a list of topics and reminders, along with handy examples, code and drawings. Reading them before coming to the lecture will help you getting a sense of the next topic we will be discussing, but you may sometimes have trouble deciphering their … unique style.

When it comes to code, you can normally copy-and-paste it from the document and use it as it is. Or, you can browse the source code of the code snippets at rocketgit to download it directly. Some portion of code starts with a path in comment, like so:

    /* code/sql/HW_HelloWorld.sql */
    SELECT "Hello World!";

This means that this code can be found at rocketgit.

The `SQL` code frequently starts with

    DROP SCHEMA IF EXISTS HW_NAME_OF_SCHEMA;
    CREATE SCHEMA HW_NAME_OF_SCHEMA;
    USE HW_NAME_OF_SCHEMA;

This parts starts by deleting the schema `HW_NAME_OF_SCHEMA` if it exists, then create and use it: it allows the code to run independently of your installation. It needs to be used with care, though, since it would delete everything you have in the `HW_NAME_OF_SCHEMA` schema before re-creating it, but empty.

Finally, the comments `-- start snippet something` and `-- end snippet something` can be ignored, as their are an artifice from pandoc-include-code to select which portion of the code to display.

To clone this source of those notes and have a local copy of it, please refer to the instructions at README. Instructions on how to compile those notes and how to contribute are linked from this document.

On top of the notes, you will find in this document:

- References, at the very end of this document
- and for each chapter,

- A list of additional resources,
- A list of short exercises,
- Solution to those exercises,
- A list of problem,
- Sometimes, solution to some of those problems.

Any feedback is greatly appreciated. Please refer to README for how to contribute to those notes.

The syllabus is at my website, and the webpage for this notes is at the notes page. Please, refer to those notes using this entry (Aubert 2019):

    @report{AubertCSCI3410-DatabaseSystems,
    author={Aubert, Clément},
    title={CSCI 3410 - Database Systems},
    url={http://spots.augusta.edu/caubert/db/ln/},
    urldate={2019-11-03},
    year={2019},
    institution={{School of Computer and Cyber Sciences, Augusta University}},
    location={Augusta, Georgia, USA},
    langid={en},
    type={Lecture notes}
     }

## Planned Schedule

A typical (meeting twice a week, ±17 weeks, ±30 classes) semester is divided as follows:

- Lecture 1: Presentation and Syllabus
- Lecture 2: Introduction
- Lecture 3–5: The Relational Model
- Lecture 6–9: The SQL Programming Language
- Lecture 10–11: Review session and Exam #1
- Lecture 12: Introduction to High-Level Design
- Lecture 13–15: Entity-Relationship Model
- Lecture 16: ER-to-Relational Models Mapping
- Lecture 17–20: Guidelines and Normal Form
- Lecture 21–22: Unified Modeling Language Diagram
- Lecture 23–24: Review session and Exam #2
- Lecture 25–18: Database Applications
- Lecture 29–30: Presentation of NoSQL

## Exams Yearbooks

To give you a sense of what you will be asked during the exams, or simply to practise, please find below the exams given previous semesters, in reverse chronological order. The quizzes are not indicated, but were generally a mix of up to five exercises and one problem from the relevant chapter(s).

### Spring 2020

Due to the Covid-19 pandemic, only one exam took place, and the final exam was taken remotely on D2L. A second project, more ambitious, was also asked from the students, and accounted for a large portion of their grade.

- Project #1: Problem 3.16 (A simple database for authors of textbooks)
- Exam #1:

- Problem 3.17 (A database for residencies)
- Problem 2.5 (Relational model for an auction website)

- Final:

- Problem 3.10 (Write select queries for the SocialMedia schema), where the last query was treated as a bonus, due to its difficulty.
- Problem 7.1 (Explaining NoSQL)
- Problem 4.22 (Normal form of the DELIVERY relation)
- Problem 4.5 (Incorrect ER diagram)

### Fall 2019

- Exam #1:

- Problem 3.15 (A simple database for published pieces of work)
- Three exercises (Exercise 3.14, Exercise 3.32 but for the OR operator, and Exercise 3.30)
- Problem 2.4 (Relational model for universities)

- Exam #2:

- Problem 4.30 (From Business Statement to ER Diagram to Relational Model – A Network of Libraries)
- Problem 4.18 (From business statement to dependencies, ROUTE)
- Problem 5.2 (A GUEST Java Program)
- Problem 4.25 (PRINT relation in third normal form)

- Final:

- Problem 7.3 (ER Diagram from XML File – Award)
- Problem 4.14 (Normal form of a simple relation)
- Problem 4.19 (From business statement to dependencies, ISP)
- Problem 3.14 (A database for website certificates)
- Three small exercises about security (Exercise 6.1, Exercise 6.2, Exercise 6.3)

### Spring 2019

- Exam #1:

- Problem 3.13 (A simple database for books)
- Five exercises (Exercise 1.5, Exercise 2.12, Exercise 3.32, Exercise 2.14, Exercise 3.39)
- A variation on Problem 2.3 (Design a relational model for bills)

- Exam #2:

- Problem 4.4 (ER diagram for job and offers)
- Problem 4.10 (From ER diagram to Relational model – RECORD)
- Problem 4.15 (Normal form of a SCHEDULE relation)
- Problem 4.24 (Normal form of the MESSAGE relation)

- Final:

- A variation on Problem 7.1 (Explaining NoSQL)
- Problem 4.21 (Normal form of the BOOK relation)
- Problem 4.28 (A Relation for Network Cards)
- Problem 3.11 (Write select queries for a variation of the COMPUTER table)
- A variation on Problem 4.30 (From Business Statement to ER Diagram to Relational Model – A Network of Libraries)
- Five exercises from the Database Application chapter.

### Spring 2018

- Exam #1:

- Problem 2.2 (Design a relational model for a cinema company), except that I gave some of the relations and attributes, to help getting started with the problem.
- Problem 4.1 (Design for your professor)
- Problem 3.6 (TRAIN table and more advanced SQL coding)
- Problem 3.7 (Read, correct, and write SQL statements for the COFFEE database)

- Exam #2:

- A variation on Problem 4.23 (Normal form of the CONTACT relation)
- A variation on Problem 4.26 (CONSULTATION relation: justification, primary key and normal form)
- Problem 3.12 (Improving a role-playing game with a relational model)
- A variation on Problem 4.32 (From business statements to dependencies – KEYBOARD)
- Problem 4.11 (ER-to-Relation mapping for Country)

- Final:

- Take the relational model of the solution of Problem 2.2 (Design a relational model for a cinema company), and “reverse-engineer” it to obtain a ER diagram (this problem was probably too hard).
- Six small exercises (Exercise 4.32, Exercise 7.1, Exercise 7.2, Exercise 7.3, Exercise 7.4)
- Problem 4.16 (Normalizing the FLIGHT relation)
- A variation on Problem 4.12 (From business statements to ER diagram – UNIVERSITY)
- A variation on Problem 4.33 (From UML to relational model – DRIVER): students were asked to draw the ER diagram for that schema.

### Fall 2017

- Exam #1:

  > This exam was probably a bit too long, but students managed it pretty well.

- Six small exercises (Exercise 1.11, Exercise 2.4, Exercise 2.8, Exercise 3.6, Exercise 3.8 and Exercise 3.12)
- Problem 2.1 (Find a candidate key for the CLASS relation)
- Problem 2.2 (Design a relational model for a cinema company)
- A variation on (Elmasri and Navathe 2010, Exercise 3.11), (Elmasri and Navathe 2015, Exercise 5.11)
- Problem 3.6 (TRAIN table and more advanced SQL coding)

- Exam #2:

- Six small exercises, (Exercise 4.9, Exercise 4.14, Exercise 4.40, Exercise 4.37, Exercise 4.6, Exercise 4.25)
- Problem 4.12 (From business statements to ER diagram – UNIVERSITY)
- Problem 4.17 (From business statement to dependencies, BIKE)
- Problem 4.23 (Normal form of the CONTACT relation)
- A variation on Problem 4.11 (ER-to-Relation mapping for Country)

- Final:

- A variation on (Exercise 5.16)
- A variation on Problem 3.7 (Read, correct, and write SQL statements for the COFFEE database)
- A variation on Problem 4.33 (From UML to relational model – DRIVER): students were asked to draw the ER diagram for that schema.
- Problem 4.21 (Normal form of the BOOK relation)
- Problem 4.26 (CONSULTATION relation: justification, primary key and normal form)

## Typesetting and Acknowledgments

The source code for those notes is hosted at rocketgit, typeset in markdown, and then compiled using pandoc and multiple filters (pandoc-numbering, pandoc-citeproc, pandoc-include-code). The drawings use various LaTeX packages, including PGF, TikZ, tikz-er2, pgf-umlcd and tikz-dependency. The help from the TeX - LaTeX Stack Exchange community greatly improved this document. The underline text is obtained using YayText, the unicode symbols are searched in the “Unicode characters and corresponding LaTeX math mode commands”. Finally, the pdf version of the document uses Linux Libertine fonts, the html version uses Futura.

> For technical reasons, underlined words cannot be searched in the document.

These lecture notes were created under an Affordable Learning Georgia Mini-Grant for Ancillary Materials Creation and Revision (Proposal M71).

## Resources

You can find at the end of this document the list of references, and some particular resources listed at the beginning of each chapter. Let me introduce some of them:

- (Elmasri and Navathe 2010) and (Elmasri and Navathe 2015) are two editions of an excellent and detailled book on Databases. It is commonly used, cover almost every aspect in a fairly accessible way.
- (Watt and Eng 2014) is an open-source, cost-free textbook on Database design that can be of good support.
- (Sadalage and Fowler 2012) and (Sullivan 2015) are two textbooks on the NoSQL approach that are short and good introductions.
- To get started on Java and how it interfaces with databases, I believe (Gaddis 2014) is a good introduction.
- awesome-mysql is a “curated list of awesome MySQL free and opensource software, libraries and resources” that is definitely worth checking out. Among other ressources, note this bank of SQL programming exercises.

Those resources are listed as complements, but it is not require to read them to understand the content of those notes. (Watt and Eng 2014)—being available free of charge—is more descriptive than the current notes, and as such can constitutes a great complement. Unfortunately, it lacks some technical aspects, and the database program aspect is not discussed in detail.

## Copyright

This work is under Creative Commons Attribution 4.0 International License or later.

Some figures and resources are borrowed from other sources, in which case it is indicated clearly.