IST 634 Assignment 1 – MySQL Administration & Simple Queries
IST 634 Assignment 1 – MySQL Administration & Simple Queries
In this laboratory exercise, you will gain initial experience with MySQL Server and the MySQL Workbench application. Create a Microsoft Word document. For each task, paste a screenshot (more than one if necessary) into a single Word document proving that you have carried out the task. Submit the document via Blackboard. Due date: 10/1/2020 7:00am. Download/install MySQL Server and Workbench if you have not already done so. Start the server, open MySQL Workbench, and create a connection for the root account. Connect to the server using this connection. The COMPANY database of the Elmasri/Navathe text will be used throughout this assignment (see Figure 5.5). Carry out each of the following tasks from SQL Workbench.
1. From your MySQL Administrator (root account), create the company database (it will be empty – you will create the components of the schema, tables, later).
2. Create a user named 'book' and grant all rights on the 'company' database to this user. The SQL commands should look like:
CREATE USER 'book'@'localhost';
GRANT ALL PRIVILEGES ON company.* TO 'book'@'localhost' WITH GRANT OPTION;
Create a MySQL Workbench connection for the 'book' account. Use this connection to connect to the server and select the 'company' database.
3. For each table in the company schema (see Figure 5.5), create a DDL statement to add the table to the database, then execute the statement. Use SQL script files for each table where applicable.
4. After creating the tables, load data into each from corresponding .dat files using the LOAD command. Use scripts to execute these commands and verify data loading with SELECT queries.
5. Follow instructions for ensuring MySQL configuration allows file loading properly, based on your OS (MacOS or Windows). Execute needed commands to enable 'local_infile' and verify setup.
6. Load data files into tables, troubleshooting any errors by editing data files as needed.
7. Write and execute SQL queries based on provided requirements, such as retrieving employees working more than a certain number of hours, employees with matching dependents, supervisors, updating department locations, adding new dependents, and deleting projects.
Paper For Above instruction
The purpose of this assignment is to develop practical skills in MySQL database administration and simple query formulation. It immerses students in the process of database creation, user management, schema design, data loading, and executing essential SQL queries relevant in real-world scenarios. This exercise will deepen understanding of MySQL's functionalities, configuration, and data manipulation capabilities.
First, establishing a working environment involves installing and configuring MySQL Server and Workbench, starting server instances, and creating necessary user accounts. The root account is used to set up the base schema, specifically creating an empty database named 'company.' Creating a restricted user, such as 'book,' with privileges limited to the 'company' database, enforces appropriate security and demonstrates user privilege management.
Next, schema design involves creating tables for the 'company' database according to the company's schema, such as employee, department, project, and dependent tables. Each table creation is accomplished through DDL statements, often scripted for efficiency. Ensure foreign keys and other constraints are added after data load to promote data integrity. The process of executing these scripts reinforces scripting skills and understanding of SQL DDL operations.
Loading data requires converting raw data files (.dat) into formats suitable for SQL commands. With MySQL's LOAD DATA INFILE feature, data is imported efficiently. Adjustments to server configuration facilitate the process, especially on MacOS and Windows. Proper setup of configuration files (my.cnf) or execution of global variables ensures smooth data import. Importing data involves troubleshooting and cleaning data files to resolve loading errors.
Data validation follows loading, using SELECT * queries to confirm table population. This step is crucial for verifying data integrity before proceeding to analysis or further modifications. This process simulates production scenarios where data accuracy and consistency are paramount.
Subsequently, performing specific SQL queries based on assignment requirements demonstrates the ability to manipulate and analyze data effectively. Examples include retrieving employees working more than a specified number of hours, matching dependent names, identifying supervisors, updating location data, adding new dependents, and deleting entries such as projects. These queries encompass a broad range of SQL commands including SELECT, UPDATE, INSERT, and DELETE, reinforcing core SQL skills.
Through this comprehensive process, students learn to manage database security, efficiently load and verify data, and perform complex queries, preparing them for real-world database administration tasks. Mastery of these skills supports subsequent advanced topics in database design, optimization, and application development.