Consider the following relation AIRLINE TABLE Flight Date Flight ID Origin Destination Captain ID Captain Name...

Consider the following relation

AIRLINE TABLE

Flight Date	Flight ID	Origin	Destination	Captain ID	Captain Name	Number of Passengers on the Flight
April 10	111	Chicago	L.A.	111	Tracy	110
April 10	222	L.A.	Phoenix	222	Matt	105
April 12	111	Chicago	L.A.	444	Tim	98
April 12	333	L.A.	N.Y.	111	Tracy	75
April 12	444	L.A.	N.Y.	333	Pat	110
April 12	555	L.A.	Dallas	444	Tim	111
April 12	222	L.A.	Phoenix	555	Pat	107

Some of the requirements that this table is based on are as follows:

The AIRLINE is flying number of flights on various routes daily.
A flight with a particular Flight Id always flies on the same route (same Origin and same Destination).
Every captain has a unique Captain Id, and a non-unique Captain Name.
For each instance of a flight (a particular flight on a particular Flight Date) we keep track of who was the captain of that instance of a flight and how many passengers were on board (Number of Passengers on the Flight).
A captain can fly multiple flights during the same day.

Normalize the relation to the second normal form (2NF).

Normalize the relation to the third normal form (3NF).

Expert Solution

Colby Messinger answered 3 years ago

Create a table ‘StudentInfo’ with following fields: ID First Name Last Name SSN Date of Birth...

Create a table ‘StudentInfo’ with following fields: ID First Name Last Name SSN Date of Birth Create a table ‘ClassInfo’ table: ID Class Name Class Description Create a table ‘RegisteredClasses’ table: StudentID ClassID The RegisteredClasses table should have a foreign key relationship to StudentInfo and ClassInfo tables for the respective IDs. Also the IDs in StudentInfo and ClassInfo need to be primary keys. When you submit the file your email should also contain the following SQL Queries: Query to show...

Employee ID First Name Last Name email Title Address Extension Department Department ID Hiring Date Department...

Employee ID First Name Last Name email Title Address Extension Department Department ID Hiring Date Department Phone # 0001 John Smith jsmith Accountant 1300 West st 5775 Accounting 2100 8/1998 407-366-5700 0002 Brian Miller badams Admin Assistant 1552 Palm dr 5367 Human resource 2300 4/1995 407-366-5300 0003 James Miller miller Inventory Manager 2713 Buck rd 5432 Production 2520 8/1998 407-366-5400 0004 John Jackson jackson_sam Sales Person 433 tree dr 5568 Sales 2102 6/1997 407-366-5500 0005 Robert Davis Davis Manager 713...

Write an SQL query that will output the employee id, first name and hire date from...

Write an SQL query that will output the employee id, first name and hire date from the employee table. Pick only those employees whose employee ID is specified in the employee table (no nulls). If the employee id is 777, name is ABC and hire date is 01-JAN-2016, the output should be like - ' ABC (Employee ID - 777) was hired on 1, January of 2016'. Note - The date should not have preceding zeros.

Student Name and ID no. _________________________________________________________ Write the journal entries in the given table for below...

Student Name and ID no. _________________________________________________________ Write the journal entries in the given table for below transactions of Clean Corporation during June 2020. 1 Provided services to customers on account for $650. 2 Purchased a building using 10 year Note Payable for $200,000. 3 Paid salaries to employees, $2,600. 4 Received payment from customers to whom service was given in transaction 1 5 Paid $400 dividends to shareholders. Answer Accounts Debit Credit 1 2 3 4 5

write a sql statement that retrieves product ID (ProductID) and name (Name) from Production.product table for...

write a sql statement that retrieves product ID (ProductID) and name (Name) from Production.product table for all product whose name includes both the words "silver" and "frame"

PROBLEM 3: Given the following relational database table: Patients(ID, name, symptom, days_in_hospital) The following insertions are...

PROBLEM 3: Given the following relational database table: Patients(ID, name, symptom, days_in_hospital) The following insertions are performed on the table Patients: Insert record <20, Johnson, cough, 3> Insert record <10, Black, fever, 5> Insert record <30, Miller, fever, 10> Insert record <70, Brown, fatigue, 2> Insert record <60, Grant, headache, 4> Insert record <50, Miller, nausea, 15> Insert record <90, Brown, cough, 8 > Assume each block in the Patients file can store up to 2 patient records. Do the...

1. A travel agency would like to track the destination of each flight. Given the following...

1. A travel agency would like to track the destination of each flight. Given the following business rule, what constraint(s) can you apply to the flight table, given there is also a destination table? "A flight must be associated with a destination". Question 1 options: Referential integrity Check constraint Nullability Default constraint 2. Constraint applied to a one-to-many relationship when a referenced record (on the 1 side) is deleted, then the referencing records (on the Many side) should also be...

Consider the following schema: Student(id, name) Registration(id, code) Course(code, instructor) A) Explain how we can violate...

Consider the following schema: Student(id, name) Registration(id, code) Course(code, instructor) A) Explain how we can violate the foreign key constraint from the id attribute of Registration to the Student relation. Ans. B)Give the following queries in the relational algebra. 1)What are the names of students registered in IT244? Ans. 2 )What are the names of students registered in both IT244 and CS141? Ans. 3)What are the names of students who are...

Consider a router that has the following Routing Table contents: Destination Subnet Outgoing Interface Next Hop...

Consider a router that has the following Routing Table contents: Destination Subnet Outgoing Interface Next Hop 50.62.8.0/24 Fa0/0 118.2.77.4 24.19.0.0/16 Fa0/1 59.16.1.1 0.0.0.0/0 Fa0/2 18.12.52.43 0.0.0.0/0 is the default route. When a packet arrives, the router checks the destination IP address of the packet to see in which subnet it falls. If the destination IP address does not fall within any of the specified subnets, then the default route is used as a last resort. The packet is consequently forwarded...

You have a table called scholar where there are two fields id and create date. You...

You have a table called scholar where there are two fields id and create date. You want to create an ETL process that loads the data from scholar table into the big data platform. What are the different things that you will put on a requirement document for the developer to write code and implement the solution?

Question