CS-10337 – Applied Database Technologies� Lecture 3
By Prof. Rafael Orta
Wayground
Last class we covered
Agenda
Lab assignment #1
Relationships
Referential Integrity
Quiz #1
Lab #1
Objective:
• Explore relationships, referential integrity, and indexing in MySQL�• Practice joins, aggregates, and performance analysis
Schema Provided: CS10337_Orta (read-only)
Tables: departments, students, professors, courses, enrollments�Views: v_student_courses, v_course_roster, v_dept_enrollment_summary
Deliverables: SQL file & Screenshots
Lab #1
Tasks
Warm-up (selects & joins)
1.- Students and Majors – Show each student with their major department name.
2.- Course Roster – List the roster for 1001 in Fall-2025 with student name and grade.
3.- Professor’s Courses – List all course codes and titles taught by Dr. Elena Ruiz.
4.- Unenrolled Students – Show students not enrolled in any course in Fall 2025.
5.- Top courses by enrollments: Show the courses ordered by the highest enrollment first.
Lab #1- Help
DEMO TIME – Querying the Database
Lab #1- Help
1.- Students and Majors�Show each student with their major department name.�Hint: students → departments.
2.- Course Roster�List the roster for CS101 in Fall-2025 showing student name and grade.�Hint: Filter v_course_roster or join courses, enrollments, students.
3.- Professor’s Courses�List all course codes and titles taught by Dr. Elena Ruiz.
4.- Unenrolled Students�Show students who are not enrolled in any course in Fall-2025.�Hint: LEFT JOIN to enrollments + WHERE e.enrollment_id IS NULL.
5.- Top courses by enrollment�Using v_course_roster
Relationships
What do you think is the
Relational Database Model?
Relationships
Relationships
Relationships
Relationships
Relationships
Relationships
Relationships
One professor can have many students, and one student can have many professors.
One student can have many classes.
One student can have only one dorm room.
Relationships
Supplemental Video(s)
Supplemental Reading
Knowledge Check
Which of the following is the best example of a many-to-many relationship?
A. Each employee is assigned to exactly one company car.�B. A student can enroll in many courses, and each course can have many students.�C. Each country has one capital city.�D. A customer places one order at a time.
Correct Answer: B
Referential Integrity
Primary Key
Think of a primary key as the unique ID card for each row in a table.
Foreign Key
A foreign key is like a reference or pointer. It links a row in one table to a related row in another.
Referential Integrity
Referential Integrity
Referential Integrity
Referential Integrity
Referential Integrity
Referential Integrity
Referential Integrity
Referential Integrity
Any real live resemblance to the character in the picture is only in your mind ->
Referential Integrity
Referential Integrity
Referential Integrity
Referential Integrity
Referential Integrity
Supplemental Video(s)
Supplemental Reading
Knowledge Check
Which statement best describes referential integrity in databases?
A. It guarantees every column in a table is unique.�B. It prevents inserting a child record without a matching parent record.�C. It ensures that every row in a table has a primary key.�D. It allows duplicate values in the primary key column.
Correct Answer: B
Attendance
Quiz #1
Reference Material used in this presentation