tirsarp/databases

Course notes and examples in MySQL and Mongo from database systems class

Databases

Relational and Non-Relational Data and Database Systems

About

This three-week program aims to introduce the fundamental concepts of data entities and data storage.

Key Objectives

By the end of this module, students should have a familiarity with and basic understanding of the following:

• Entities
• The relational model
• The Structured Query Language (SQL)
• The construction of a database system
• MySQL as an example of a relational database system
• Non-relational data and NoSQL
• MongoDB as an example of a NoSQL database

The Practice Database

The login information for the student practice database will be provided during the first class. If you don't have them, send us a message - you will need the username, password, hostname, port, and database name.

Lesson Plan

Lesson 1: Retrieving Data

In this class, students will be introduced to retrieving data from a MySQL database using SELECT queries.

Objective: Students should be able to retrieve data from a database table using SELECT statements that include WHERE, GROUP BY, ORDER BY, LIMIT, and JOIN.

Lesson 2: Practical Database Usage

In this class, students will learn how to use more complex SQL queries to retrieve information across tables, and interact with data including write operations.

Objective: Students should be able to build CRUD functionality using SQL statements, including INSERT INTO, UPDATE WHERE, etc. Students will also have a basic understanding of database usage in a web application, including parameter validation, escaping, and prepared statements.

Lesson 3: Data Models, Relationships, and Schemas

In the final week, additional theory will be covered to discuss more complex relational data. Students will learn about entity relationship modelling and how to convert these models to a database schema using normalisation and foreign-key constraints. Non-relational data will also be considered, as well as the benefits and drawbacks of relational and non-relational models.

Objective: Students should be able to create an entity relationship diagram based on a qualatative description of data requirements, and translate that into a MySQL database schema. Students should also be able to compare and contrast relational (like MySQL) and NoSQL databases (considering their benefits and drawbacks).

Handing in homework

Take a look at this video made by Daan, he explains how your homework needs to be handed in.

Also review the Git workflow material from the JavaScript3 module, use this as a reference.