Lab 4 Structured Query Language (SQL) Refer To The Database
Lab 4 Structured Query Language Sqlrefer To The Database Design Cre
Perform the following steps in MySQL based on the database design created in Lab 2: Modify the database design in Visio. Create the tables and relationships, add records, and write specific queries. Take screenshots to document each step and compile all into a Microsoft Word document with a cover page containing the assignment title, student’s name, professor’s name, course title, and date.
Paper For Above instruction
This assignment involves utilizing Structured Query Language (SQL) within MySQL to implement a database design discussed previously in Lab 2, focusing on creating tables, inserting data, and querying information. The comprehensive nature of the task aims to develop proficiency in SQL data definition, manipulation, and retrieval commands, which are fundamental components in database management systems.
Introduction
The purpose of this exercise is to translate a previously designed database schema into a functioning database in MySQL, populate it with sample data, and perform specific queries to extract meaningful information. These steps form the backbone of understanding relational database management and querying techniques, illustrating real-world applications such as student and instructor data management.
Creating Tables and Relationships
The first step involves creating the tables as per the design in Lab 2, which includes tables such as Students, Instructors, and Courses. The SQL commands for creating these tables include defining primary keys, foreign keys, and appropriate data types to ensure data integrity and relational links. For instance, the Students table might include StudentID (primary key), FirstName, LastName, PhoneNumber, etc. The relationships among tables, such as linking students to courses and instructors, should be established using foreign keys, reflecting the design in the previous lab.
Inserting Records
After table creation, at least five records should be added into each table. These records require the determination of field values relevant to each table—for example, students' names and contact info, instructor details, and course titles. Proper data should be inserted to facilitate meaningful queries later. Inserting data involves the use of the INSERT INTO statement, ensuring that foreign key constraints are respected.
Performing SQL Queries
The assignment includes writing specific queries to retrieve data based on certain criteria:
- Create a query retrieving all fields from the Student table where the last name is “Smith”.
- Create a query that includes students' first names, last names, and phone numbers.
- Create a query that includes instructors' first names, last names, and the courses they teach.
Each query should be executed in MySQL, and the results captured through screenshots. The queries utilize SELECT statements with WHERE clauses, and JOIN operations if needed to relate instructors and courses.
Documentation and Submission
The screenshots should include:
- Tables created within MySQL.
- Records inserted into each table.
- The SQL code and query results.
All screenshots and the written instructions should be compiled into a Microsoft Word document, accompanied by a cover page with the specified details. This documentation demonstrates the successful creation, data entry, and querying of the database system.
Conclusion
This lab exercise ensures mastery over fundamental SQL commands and relational database design. The practical application emphasizes creating a database structure, populating it with data, and performing targeted queries—skills crucial to database development and management in real-world scenarios.
References
- Agrawal, R. (2018). Database System Concepts (7th ed.). McGraw-Hill Education.
- Database Administration: The Complete Guide to DBA Practices and Procedures (2nd ed.). Addison-Wesley.