WEEK 6_ACTIVITY 2.pdf

NAME: ELLA MAUREEN D. RIOVEROS SECTION:BSIT – 3A WEEK 6 Activity 2: Creating an Entity-Relationship (ER) Model and Mapping to a Physical Database Objective: To design an ER diagram for a university...

NAME: ELLA MAUREEN D. RIOVEROS SECTION:BSIT – 3A WEEK 6 Activity 2: Creating an Entity-Relationship (ER) Model and Mapping to a Physical Database Objective: To design an ER diagram for a university database and map it to a physical database in MySQL, demonstrating the process of translating a conceptual model into a relational schema. Materials: MySQL server and client software (e.g., MySQL Workbench or command-line interface) A computer with an internet connection Procedure: 1. Design the ER Diagram: ○ Sketch an ER diagram with the following entities: Students: with attributes student_id, student_name, student_email Courses: with attributes course_id, course_name, course_credits Enrollments: with attributes enrollment_id, student_id, course_id, enrollment_date ○ Define the relationships: A Student can enroll in multiple Courses. A Course can have multiple Students enrolled. Please attach an ER in the box. 2. Create the Database: ○ Execute the following command to create a new database: CREATE DATABASE UniversityDB; USE UniversityDB; 3. Create the Students Table:  ○ Define the Students table with the following structure: CREATE TABLE Students ( student_id INT AUTO_INCREMENT PRIMARY KEY, student_name VARCHAR(100), student_email VARCHAR(100) ); 4. Create the Courses Table: ○ Define the Courses table with the following structure: CREATE TABLE Courses ( course_id INT AUTO_INCREMENT PRIMARY KEY, course_name VARCHAR(100), course_credits INT ); 5. Create the Enrollments Table: ○ Define the Enrollments table with the following structure: CREATE TABLE Enrollments ( enrollment_id INT AUTO_INCREMENT PRIMARY KEY, student_id INT, course_id INT, enrollment_date DATE, FOREIGN KEY (student_id) REFERENCES Students(student_id), FOREIGN KEY (course_id) REFERENCES Courses(course_id) ); Please attach a screenshot demonstrating the successful execution of the script in the box. 6. Insert Sample Data: ○ Insert data into the Students table: INSERT INTO Students (student_name, student_email) VALUES ('Mark Brown', '[email protected]'), ('Lisa White', '[email protected]'), ('Tom Green', '[email protected]');  ○ Insert data into the Courses table: INSERT INTO Courses (course_name, course_credits) VALUES ('Database Systems', 3), ('Algorithms', 4), ('Web Development', 3); ○ Insert data into the Enrollments table: INSERT INTO Enrollments (student_id, course_id, enrollment_date) VALUES (1, 1, '2024-08-01'), (2, 2, '2024-08-02'), (1, 3, '2024-08-03'), (3, 1, '2024-08-04'); Please attach a screenshot demonstrating the successful execution of the script in the box. 7. Write and Execute Queries: ○ List all students enrolled in a specific course (e.g., 'Database Systems'): SELECT Students.student_name FROM Students JOIN Enrollments ON Students.student_id = Enrollments.student_id JOIN Courses ON Enrollments.course_id = Courses.course_id WHERE Courses.course_name = 'Database Systems'; Please attach a screenshot demonstrating the successful execution of the script in the box. ○ Show all courses taken by a particular student (e.g., 'Mark Brown'): SELECT Courses.course_name FROM Courses JOIN Enrollments ON Courses.course_id = Enrollments.course_id JOIN Students ON Enrollments.student_id = Students.student_id WHERE Students.student_name = 'Mark Brown'; Please attach a screenshot demonstrating the successful execution of the script in the box. ○ Count the number of students enrolled in each course: SELECT Courses.course_name, COUNT(Enrollments.student_id) AS number_of_students FROM Courses LEFT JOIN Enrollments ON Courses.course_id = Enrollments.course_id GROUP BY Courses.course_name; Please attach a screenshot demonstrating the successful execution of the script in the box. 8. Reflection: Discuss how designing an ER model helps in creating a well-structured relational database. Please attach a reflection in the box. Designing an Entity-Relationship (ER) model is essential for creating a structured relational database, as it represents visually the data and the relationships between different entities. This model helps to clarify the data structure, define relationships, reduce redundancy and enhance efficiency. The ER model also highlights important attributes, such as primary and foreign keys, which are essential for connecting tables and maintaining data integrity. Furthermore, it fosters better communication as it give a clear framework or visual representation of data which can help for future database growth. Conclusion: In this activity, you have translated an ER model into a relational database schema, created tables, inserted data, and executed queries to retrieve and analyze data in a university context. These laboratory activities are designed to provide hands-on experience with MySQL database design, implementation, and querying, aligning with the concepts of relational and entity-relationship models covered in the lecture notes. Submission Directions: 1. Submit the SQL script containing all the SQL commands used in Activity 1 & 2. 2. Provide screenshots showing the successful execution of each part of the SQL. Score = 10 points