MySQL

My SQL

Overview: Database management plays an integral role in nearly every area of business. Databases house customer information, accounting, and employee management, and these different data groups must all be efficiently managed in order to make the information accessible. Companies rely on database engineers to ensure that their records are accurate, updated, and tracked in real time. This course covers structured query language (SQL) and how it can be used to manage database schemas, manipulate data, and analyze data. For your final project, you will apply the skills you learned in order to demonstrate your mastery of the key concepts necessary for effective database management. These are skills you will be able to apply to your future courses as well as your career in managing information systems.

Prompt: The first milestone assignment for this course involves applying the concepts that you have learned to create a database and all associated tables from scratch using MySQL in the Codio learning environment. In this assignment, read the given scenario and review the schema for the database that you are to create. You will write a series of SQL statements to create the database and tables per the specifications provided to you, and you will provide a written report explaining your work.

Directions: For this assignment, you will submit a Microsoft Word document with screenshots and explanations of your work. For each SQL statement that you construct in the steps below, you must provide the screenshot of your SQL statement, the screenshot of the results of executing the SQL statement, and a written explanation of the SQL statement. The written explanation of the SQL statement should explain the different parts of your SQL statement.

1. Log in to Codio and access the Personal Playground unit for the course.

2. If the terminal does not appear, select Tools -> Terminal.

3. Type the following command to start MySQL: mysql

4. Construct the SQL statement to create a new database named hr.

5. Type the following command to use the new database: use hr;

6. Construct the SQL statement to create a table named person. The columns, column data types, and column notes are provided here. Create the person table according to these specifications. In addition to the five properties of a person provided, you are to include an additional column of your choice. This column should represent some property of a person. You can choose the data type for the column and any constraints on the column.

Table name; person

Field Type Notes

person_id int(8) ? Primary key ? Auto-increment value ? Required

first_name varchar(25) ? Required

last_name varchar(25) ? Required

gender varchar(1)

age int(8)

Custom Column Your Choice Your Choice

7. Construct the SQL statement to create a table named department. The columns, column data types, and column notes are provided here. Create the department table according to these specifications.

Table Name: department

Field Type Notes

dept_id int(8) ? Primary key ? Auto-increment value ? Required

dept_name varchar(25) ? Required

building varchar(25) ? Required

8. Construct the SQL statement to create an intersection table named person_department. The columns, column data types, and column notes are provided here. Create the person_department table according to these specifications.

Table Name: person_department

Field Type Notes

person_id int(8) ? Primary key ? Required

dept_id int(8) ? Primary key ? Required

9. Verify the results of your SQL statements. Refer to the Frequently Asked Questions document for instructions on how to list the tables in the database and describe the details for each table.