Question

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 second milestone assignment builds upon the concepts that you have learned thus far. In the first milestone assign ment, you created a database from scratch. In this assignment, you start with an existing database, and you must make the necessary modifications to it and manipulate the data within the database. Like the first assignment, you are using MySQL in the Codio learning environment. You will write a series of SQL statements to alter a table in the database, insert records, update records, and delete records. You will provide a written report explaining your work. Directions: Your first project at SNHU Airlines was a success. You were able to implement the database for the company's human resources system. You have now been assigned to work on the database for the flight reservation system. Unlike the human resources system, the flight reservation system does not need to be completely rebuilt. Only a few modifications are needed to the flight reservation database. This database is used to keep track of customers, flights, flight status and which customers are on which flights. Refer to the Database Schema section for details on the database. oon Database Schema Ra Toe he Tepe Piay Rare stea ch wteri atvired d de os seL Baly asen 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 the plain-text editor first and then copy and paste the SQL statements into the MySQL command line interface. statements Task List The following tables have already been created for you. Each table is already populated with some records. Table Name: customer Field Туре Notes int(8) customer id Primary key Auto-increment value Required Required Required varchar(25) first name varchar(25) last name Table Name: flight status Field Туре int(8) Notes status_id Primary key Auto-increment value Required Required varchar(25) status Table Name: flight Field Туре int(8) Notes flight_id Primary key Auto-increment value Required varchar(25) varchar(25) from_city Required to city Required Required departure_date_time datetime arrival date time datetime Required status id int(8) Required Table Name: flight_reservation Field Notes Туре int(8) flight_id Primary key Required Primary key Required int(8) customer id To use the "flight_reservation" database, use the following command: # Use the "flight_reservation" database use flight_reservation; Task One: Alter the Customer Table Construct the SQL statement to alter the table named "customer." The columns, column data types, and column notes are provided in the previous section. You need to alter the table 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: customer Field Туре int(8) Notes customer_id Primary key Auto-increment value Required first_name last_name varchar(25) Required Required Your Choice . varchar(25) Custom Column Your Choice Task Two: Update Records in the Customer Table Construct the SQL statement to update the existing records in the "customer" table to use the new column that you created. Update the first five records in the table with a value for the new column that you create. ... Task Three: Update Remaining Records in the Customer Table Construct the SQL statement to update the remaining existing records in the "customer" table to use the new column that you created. Update the last five records in the table with a value for the new column that you create. Task Four: Insert Records to Customer Table Construct the SQL statement to insert five new records in the "customer" table. Make sure that you use the new column that you created in the previous step. Note: You are required to add yourself as a new record in the "customer" table. Use your first name and last name for one of the new records that you insert. Task 5: Insert Records to Flight Table Construct the SQL statement to insert two new records in the "flight" table. Task 6: Insert Records to Flight Reservation Table Construct the SQL statement to insert four new records in the "flight_reservation" table. Note: You are required to add at least one record in which you are the customer on a flight. The customer_id in the "customer" table for the record with your name must have a "reservation" on a flight in the "flight" table. Task 7: Delete Records From Flight Table Construct the SQL statement to delete the records from the "flight" table in which the flight is from Seattle to Las Vegas 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: Insert new records into tables using appropriate SQL statements. Update existing records using appropriate SQL statements. Delete existing records using appropriate SQL statements.

Answer 1

Task 1 : ALTER customer ADD( city varchar(15) NOT NULL);

Task 2 : UPDATE TOP(5) customer SET city='Delhi'

ORDER BY customer_id;   //For top 5 rows

Task 3 : UPDATE TOP(5) customer SET city='Mumbai'

ORDER BY customer_id DESC; //For bottom 5 rows

Task 4 :-> INSERT INTO customer

VALUES(11223344,'Rohan','Malik','Amritsar');

-> INSERT INTO customer

VALUES(11223346,'Rishi','Verma','Delhi');

-> INSERT INTO customer

VALUES(11223348,'Vishwas','Rana','Mumbai');

-> INSERT INTO customer

VALUES(11223350,'Lalit','Kumar','Kolkata');

-> INSERT INTO customer

VALUES(11223352,'Naman','Adab','Pune');

Task 5 : INSERT INTO flight

VALUES(40221211,'Delhi','Amritsar','2019-07-14 10:23:00','2019-07-14 12:40:00',16644551);

-> INSERT INTO flight

VALUES(40221212,'Delhi','Mumbai','2019-07-16 11:25:00','2019-07-16 14:40:00',16644555);

Task 6 : INSERT INTO flight_reservation

VALUES(40221211,11223344);

-> INSERT INTO flight_reservation

VALUES(40221212,11223348);

Task 7 : DELETE FROM flight WHERE from_city='Seattle' AND to_city='Las Vegas' ;