Overview: Database management plays an integral role in nearly every area of business. Databases house customer, accounting, and employee data, and these different data sets must all be efficiently managed in order to make the data 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 third milestone assignment covers the final major concept of this coursedata retrieval. In the first milestone assignment, you created a database from scratch. In the second milestone assignment, you made alterations to an existing database and manipulated the data within it. For this assignment, you will apply what you learn about data retrieval to create some queries to extract insightful information from the database. Like in the first two assignments, you will use MySQL in the Codio learning environment. In addition to submitting the queries that you created, you will provide a written report explaining your work. Directions: Your first two projects required you to manage databases and manipulate the data for the human resources and flight reservations systems. Fortunately, the scheduling system for SNHU Airlines is functional and does not require any modifications. The company executives would like some reports on the employees meetings though, so you have been asked to create some queries to extract information from the database. Refer to the Database Schema section for details on the database. Database Schema

Instructions and Specifications For this assignment, you will do your work in Codio in the Personal Playground unit. If the terminal does not appear in the Personal Playground, select Tools and then Terminal. Once you are in the terminal, type mysql to start the MySQL command line interface. You will need to provide all of the SQL commands that you created and executed to complete the assignment. You can do this by copying and saving your SQL commands to a .sql file or .txt file using a plain-text editor like NotePad or Sublime Text. Refer to the Frequently Asked Questions document for details on how to create this file. Alternatively, you can create the SQL statements in the plain-text editor first, and then copy and paste the SQL statements into the MySQL command line interface. In addition to submitting your work as a .sql file or .txt file, 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. Task List The following tables have already been created for you. Each table is already populated with all of the necessary records. 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 Table Name: building Field Type Notes building_id int(8) ? Primary key ? Auto-increment value ? Required building_name varchar(50) ? Required Table Name: room Field Type Notes room_id int(8) ? Primary key ? Auto-increment value ? Required room_number varchar(10) ? Required building_id int(8) ? Required capacity int(8) ? Required

Table Name: meeting Field Type Notes meeting_id int(8) ? Primary key ? Auto-increment value ? Required room_id int(8) ? Required meeting_start datetime ? Required meeting_end datetime ? Required Table Name: person_meeting Field Type Notes person_id int(8) ? Primary key ? Required meeting_id int(8) ? Primary key ? Required To use the scheduling database, use the following command: Task One: Find all the meetings that Tom Hanks has to attend Construct the SQL statement to find all the meetings that Tom Hanks has to attend. Display the following columns: ? Persons first name ? Persons last name ? Building name ? Room number ? Meeting start date and time ? Meeting end date and time Task Two: Find all the people that are attending meeting ID 2 Construct the SQL statement to find all the people that are attending meeting ID 2. Display the following columns: ? Persons first name ? Persons last name # Use the scheduling database use scheduling;

? Building name ? Room number ? Meeting start date and time ? Meeting end date and time Task Three: Find all the people who have meetings in the Main Street building Construct the SQL statement to find all the people who have meetings in the Main Street building. Display the following columns: ? Persons first name ? Persons last name ? Building name ? Room number ? Meeting ID ? Meeting start date and time ? Meeting end date and time Task Four: Find the number of attendees for every meeting Construct the SQL statement to find the number of attendees for every meeting. Display the following columns: ? Count of meeting attendees ? Meeting ID ? Meeting start date and time ? Meeting end date and time Task Five: Find All of the People that Have Meetings Only Before Dec. 25, 2016 at Noon Using INNER JOINs Construct the SQL statement to find all of the people that have meetings only before Dec. 25, 2016 at noon using INNER JOINs. Display the following columns: ? Persons first name ? Persons last name ? Meeting ID ? Meeting start date and time ? Meeting end date and time

Verification 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 to describe the details for each table. Specifically, the following critical elements must be addressed: ? Retrieve data from multiple tables by using the appropriate WHERE clauses. ? Retrieve data from multiple tables by using the appropriate JOIN statement. ? Write a query that aggregates results, groups results, and includes appropriate column aliases.