Homework Help Question & Answers

Overview: Database management plays an integral role in nearly every area of business. Databases house customer, accoun...

Overview: Database management plays an integral role in nearly every area of business. Databases house customer, accounting,Instructions and Specifications For this assignment, you will do your work in Codio in the Personal Playground unit. If the tTable Name: flight_reservation Field Notes Туре int(8) flight_id Primary key Required Primary key Required int(8) customer idTask Four: Insert Records to Customer Table Construct the SQL statement to insert five new records in the customer table. M

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.
0 0
Next > < Previous
ReportAnswer #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' ;

Know the answer?
Add Answer of:
Overview: Database management plays an integral role in nearly every area of business. Databases house customer, accoun...
Your Answer: Your Name: What's your source?
Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions
  • use workbench please Task 1: Create a database and name it practical2DB. (2 marks) Task 2:...

    use workbench please Task 1: Create a database and name it practical2DB. (2 marks) Task 2: Create all the four tables according to the relational scheme in Figure 1. (8 marks) Task 3: Insert 5 records to into each table. (5 marks) Task 4: Write a query to list alphabetically ordered names, addresses, and IDs of all the customers whose postcode is in (40150, 40400, 47500). (10 marks) Task 5: Delete 1 record from Products table using their primary keys....

  • Someone Please Help Me modify this in PHP I'm in desperate need I cant figure this out ... Make t...

    Someone Please Help Me modify this in PHP I'm in desperate need I cant figure this out ... Make the following modifications: For the vendors table: Comment out the table-level primary key Change the VendorIDcolumn to be a column-level primary key Add a VendorEmail column to the bottom of the table definition (define the data type for the column as variable character and set it to not exceed 45 characters) After the lineItems table, add code to create a table...

  • Java Help?!!! (Database File)

    SCENARIOIn order to test our skills on File processing in Java, we have been tasked to simulate a basic Database Management System.DESIGN QUESTIONSThink about how you might solve this problem. What classes/methods would you need? Which programming constructs and activities might be involved (e.g. loops, stringcomparison etc.)?SPECIFICATIONAt the least following classes are required (feel free to make changes) to support a database management system:A Tables class:Owner Name;Table Name;TableSpace Name;Status;Number of rows;A Table_Column class:Table Name;Column Name;Data Type;A Table class:Table Name;Column Name;Data...

  • For this set of Review Questions, we will create and use a database for the Wedgewood...

    For this set of Review Questions, we will create and use a database for the Wedgewood Pacific Corporation (WPC) that is similar to the Microsoft Access database we created and used in Chapters 1 and 2. Founded in 1957 in Seattle, Washington, WPC has grown into an internationally recognized organization. The company is located in two buildings. One building houses the Administration, Accounting, Finance, and Human Resources departments, and the second houses the Production, Marketing, and Information Systems departments. The...

  • SQL database

    Open a new text file in Notepad or a similar text editing tool.Write a SQL command to create a table called "STUDENT". Use the same fields you described in the previous assignment. Choose a suitable primary key.Write a SQL command to create another table called COURSES. You need three fields course_id, course_name field, and description. Choose a suitable primary key.Write a SQL command to create a third table called REGISTRATION. In this table we will record the student's registration information....

  • Learning Objectives: Learn to define constraints on tables Lean to define primary keys and foreign keys...

    Learning Objectives: Learn to define constraints on tables Lean to define primary keys and foreign keys Exercise                                                                                In Lab 09, we will continue the practice of creating tables. You will need to write SQL statements for the tasks specified below. After you complete them, submit your SQL script (in one *.sql file) to Blackboard by 11:59pm Nov. 16 (Friday). Include your name as a comment in the SQL script submitted. The creation statement for each table is worth one point,...

  • Use the SQL statements provided to create your tables. Write one SQL statement for each of...

    Use the SQL statements provided to create your tables. Write one SQL statement for each of the following problems. You can only use the conditions listed in the tasks. E.g., in task 1, you cannot manually look up pid of Information Systems undergraduate program. Here is the given code: drop table textbook_schedule cascade constraints; drop table textbook_author cascade constraints; drop table schedule cascade constraints; drop table course cascade constraints; drop table textbook cascade constraints; drop table author cascade constraints; drop...

  • SQL Fundamental 3rd ed. by John J. Patrick Final Project. (repost request Edan, or Shirlee)

    Note the link to the database is as followshttp://oracle.ecpicollege.com:8080/apex/f?p=4550:11:516714645771406::NO:::user and password is the same: RicJac7604. Access is under SQL iconNote: Section 1 ER design and section 2 have been done by Shirlee, Section 3 was done by Edan. Section 4 is what I need the final answer for. (request Edan, orShirlee) Thanks for all your help!You were tasked to create an Oracle database for a company to track their employees and projects. After speaking with the customer, you have determined...

  • database and sql problme THE QUESTIONS 3, 4,5 and 6 REFER TO THE RELATIONAL TABLES LISTED...

    database and sql problme THE QUESTIONS 3, 4,5 and 6 REFER TO THE RELATIONAL TABLES LISTED BELOW CREATE TABLE Department ( DECIMAL(5) VARCHAR(30) CHAR(5) DATE NOT NULL NOT NULL NOT NULL * Department number /*Department name * Department manager number */ /Manager start date DNumber DName Manager MSDate CONSTRAINT Department_PK PRIMARY KEY(DNumber) CONSTRAINT Department_CK UNIQUE(DName) CREATE TABLE DeptLocation DECIMAL(5) VARCHAR(50) NOT NULL NOT NULL DNumber * Department number */ * Department location */ Address CONSTRAINT DeptLocation_PK PRIMARY KEY(DNumber, Address) CONSTRAINT...

  • Need help on a database information and an example to get me going on this Create an approach to...

    I will rate Lifesaver for information and an example to this assignment to get me going in the right direction please.Thank you for your time.I will put in the assignment and part of the previous one it references in hopes that it helps to get the information to kick start me on this assignment.In the case and SLP assignments of this module, you will write SQL statements to query and modify the PC Store database. You will insert initial data...

Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
Share Your Knowledge

Post an Article
Post an Answer
Post a Question with Answer

Self-promotion: Authors have the chance of a link back to their own personal blogs or social media profile pages.