Lab: Structured Query Language (SQL) Refer To The Database D

Lab Structured Query Language Sqlrefer To The Database Design Crest

Perform the following steps in MySQL based on the database design created in Lab 2: creating tables and relationships, inserting records, and executing specific queries. Finally, document the process with screenshots in a Microsoft Word document, including a cover page with assignment details, and ensure the submission meets formatting standards.

Paper For Above instruction

The purpose of this lab is to develop proficiency in SQL programming by implementing a database design, inserting data, and retrieving information through queries. Utilizing MySQL, students will translate their lab-designed schema into actual database tables and relationships, populate these tables with data, and execute various select statements to retrieve specific information, thereby demonstrating their understanding of SQL Data Definition Language (DDL) and Data Manipulation Language (DML).

Creating Tables and Establishing Relationships

The initial step involves creating tables according to the database design from Lab 2. These tables likely include entities such as Students, Instructors, and Courses, with relationships designated through foreign keys. For instance, the Students table might include student ID, first name, last name, phone number, and possibly other demographic data. The Instructors table may contain instructor ID, first name, last name. The Courses table would include course ID, course name, and instructor ID to establish a relationship with the instructors’ table. Using MySQL syntax, create these tables with appropriate data types and constraints, ensuring relational integrity through the use of primary and foreign keys.

For example, the Student table could be created as follows:

CREATE TABLE Student (

student_id INT PRIMARY KEY,


first_name VARCHAR(50),


last_name VARCHAR(50),


phone VARCHAR(15)


);

Similarly, create the Instructors and Courses tables, establishing foreign key relationships where appropriate.

Populating Tables with Data

After table creation, insert at least five records into each table. Data entry should reflect realistic and consistent values. For example, for students:

INSERT INTO Student VALUES 

(1, 'John', 'Smith', '555-1234'),


(2, 'Jane', 'Doe', '555-2345'),


(3, 'Michael', 'Johnson', '555-3456'),


(4, 'Emily', 'Davis', '555-4567'),


(5, 'Chris', 'Wilson', '555-5678');

Repeat this process for the instructors and courses, ensuring each record has unique identifiers and meaningful data.

Executing Specific Query Tasks

1. Retrieve all fields from the Student table where the last name is "Smith".

SELECT * FROM Student WHERE last_name = 'Smith';

This query filters the student records to show only those with the last name "Smith".

2. Retrieve students’ first names, last names, and phone numbers.

SELECT first_name, last_name, phone FROM Student;

This query provides a simplified view focusing on contact information of students.

3. Retrieve instructors' first names, last names, and the courses they teach.

SELECT i.first_name, i.last_name, c.course_name

FROM Instructor i


JOIN Course c ON i.instructor_id = c.instructor_id;

This query joins the instructors and courses tables to show each instructor's name alongside the course they teach.

Creating Documentation with Screenshots

Capture screenshots at each step: displaying the created tables, the inserted records, and the outputs of each query. Insert these images into a Word document, along with the SQL code used. The Word document should include a cover page with the assignment title, student’s name, professor’s name, course title, and date, formatted appropriately.

Conclusion

This lab consolidates knowledge of database design, DDL, DML, relationships, and query formulation. Successfully completing these steps demonstrates competence in translating a conceptual database schema into a functioning relational database and retrieving information effectively to support data analysis and decision-making processes.

References

  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.
  • Database Systems: Design, Implementation, & Management (12th ed.). Cengage Learning.
  • MySQL Documentation. (2023). Retrieved from https://dev.mysql.com/doc/
  • Rob, P., & Coronel, C. (2007). Database Systems Conceptual Design, Implementation, and Management. Cengage Learning.
  • Teorey, T. J., & Lightstone, S. S. (2010). Database Modeling & Design. Morgan Kaufmann.
  • Ramakrishnan, R., & Gehrke, J. (2003). Database Management Systems. McGraw-Hill Education.
  • Harrington, J. L. (2016). SQL Clearly Explained. Morgan Kaufmann.
  • Kroenke, D., & Auer, D. J. (2018). Database Concepts. Pearson.
  • Chen, P. P. (1976). The Entity-Relationship Model—Toward a Unified View of Data. ACM Transactions on Database Systems, 1(1), 9–36.
  • Silberschatz, A., Korth, H. F., & Sudarshan, S. (2020). Database System Concepts (7th ed.). McGraw-Hill Education.

Related Assignments