This data analysis session focuses on concluding the study of MySQL and preparing to transition to Tableau. The session covers the following key themes and topics:

1. Data Import and Schema Setup

  • Data Preparation: The session uses three datasets: “students,” “enrollments,” and “courses,” all in CSV format.
  • Schema Creation: A new schema named “student_record” was created in MySQL.
  • Table Import: The datasets were imported into the “student_record” schema using the Table Import Wizard. Troubleshooting was required for some participants, identifying issues with CSV naming conventions and table naming conflicts that required renaming tables via the “Alter Table” function.

2. Database Relationships and Joins

  • Identifying Keys: The instructor and participants identified the primary and foreign keys necessary to relate the tables.
    • Courses: Course ID is the primary key.
    • Students: Student ID is the primary key.
    • Enrollments: Enrollment ID is the primary key, while Student ID and Course ID act as foreign keys.
  • Fact and Dimension Tables: The “enrollments” table was identified as the fact table, while “students” and “courses” serve as dimension tables.

3. Advanced SQL Joins

  • Left Join: Used to return all records from the left table and matching records from the right table. An example was provided to extract student records and their registered courses.
  • Right Join: The opposite of a left join, focusing on the right table.
  • Full Join and Unions: Because MySQL does not support “Full Join,” the instructor demonstrated using the UNION operator to combine the results of a left join and a right join to simulate a full join.
  • Best Practices: The instructor advised against using SELECT * when performing joins to avoid column naming conflicts and improve clarity, recommending the explicit selection of required columns.

4. Conclusion and Next Steps

  • Wrap-up of SQL: The session marked the conclusion of the SQL portion of the curriculum.
  • Transition to Tableau: The instructor plans to introduce Tableau in the next class. Participants were instructed to install Tableau Public and review the provided PowerPoint materials on the drive before the next session.
Previous