LINK Courses Platform

Project Description

Text ...

Project Setup

In order to run this project, you have to either set up environment variables for your database or provide them as parameters to the Connect class.

Environment variables

• ACADEMY_MYSQL_USERNAME - the username used to connect to the database
• ACADEMY_MYSQL_PASSWORD - the password used to connect to the database
• ACADEMY_MYSQL_HOST (optional, default 127.0.0.1) - the MySQL server address
• ACADEMY_MYSQL_PORT (optional, default 3306) - the MySQL server port
• ACADEMY_MYSQL_DATABASE (optional, defulat academy) - the name of the MySQL database

Database Structure

Tables

Table courses

One entry in the courses table consists of a number of lessons. Example of courses inside the system:

• Python Core Programming
• Python Databases and SQL
• Python Object Oriented Programming etc.

Table teachers

One entry in the teachers table stores a person that teaches courses.

Table cars

One entry in the cars table stores a car, which belongs to an entry in the teachers table.

Table students

One entry in the students tables stores a student that learns courses.

Table students_courses

id_student	id_course
1	1
1	2
1	3
2	2
2	4
3	1
3	4

Database Relationships

One-to-one Relations (1 ... 1)

One (1) teachers owns one (1) cars.

Inner Join:

Select all teachers with cars and the details about their cars.

With native INNER JOIN:

SELECT t.id AS id_teacher, t.first_name, t.last_name, t.start_date, t.end_date, c.plate
FROM teachers AS t JOIN cars AS c ON t.id_car=c.id

With SELECT ... WHERE:

SELECT t.id AS id_teacher, t.first_name, t.last_name, t.start_date, t.end_date, c.plate
FROM teachers AS t, cars AS c WHERE t.id_car = c.id

Left Outer Join:

Select all teachers who currently work in the academy (they don't have and end_date). For those who have a car, select details about their cars.

SELECT t.id id_teacher, t.first_name, t.last_name, t.start_date, t.end_date, c.plate
FROM teachers t LEFT OUTER JOIN cars c ON t.id_car=c.id
WHERE t.end_date IS NULL

Right Outer Join:

Select all cars. For those who have a teacher, select details about their teachers.

SELECT c.plate, t.first_name, t.last_name, t.start_date, t.end_date, t.id id_teacher
FROM teachers t RIGHT OUTER JOIN cars c ON t.id_car=c.id

One-to-many Relations (1 ... n)

One (1) teachers teaches multiple (n) courses.

Many-to-many Relations (n ... n) == n ... 1 + 1 ... n

Multiple (n) students attend multiple (n) courses.