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 2 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...

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"

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...

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?

Consider the following XML file: <root> <students> <element> <ID>100345</ID> <Nationality>USA</Nationality> <Program>ICT</Program> <age>23</age> <name>John</name> </element

Consider the following XML file: <root> <students> <element> <ID>100345</ID> <Nationality>USA</Nationality> <Program>ICT</Program> <age>23</age> <name>John</name> </element> <element> <ID>100876</ID> <Nationality>MALAYSIA</Nationality> <Program>CS</Program> <age>28</age> <name>Awang</name> </element> <element> <ID>100257</ID> <Nationality>AUSTRALIA</Nationality> <age>25</age> <name>Alex</name> </element> </students> </root Write an XQUERY to display the information for all students who are not Malaysians or older than 25.

Consider the following three tables, primary and foreign keys. Table Name SalesPeople Attribute Name &nbsp

Consider the following three tables, primary and foreign keys. Table Name SalesPeople Attribute Name Type Key Type EmployeeNumber Number Primary Key Name Character JobTitle Character Address Character PhoneNumber Character YearsInPosition Number Table Name ProductDescription Attribute Name Type Key Type ProductNumber Number Primary Key ProductName Character ProductPrice Number Table Name SalesOrder Attribute Name Type Key Type SalesOrderNumber Number Primary Key ProductNumber Number Foreign Key EmployeeNumber Number Foreign Key SalesOrderDate Date Assume that you...

Consider the following data. Boardgames ID name price manuf_id 3234 Monopoly 60 HB 2244 Settlers of...

Consider the following data. Boardgames ID name price manuf_id 3234 Monopoly 60 HB 2244 Settlers of Catan 45 MF 2389 Catan: Seafarers 20 MF 4211 Carcassonne 55 VG 4383 Citadels 35 HB 4450 Pandemic 55 VG Manufacturer ID name founded HB Hasbro 1997 MF Mayfair Games 1982 VG Ventura Games 2005 Answer the following: Write the SQL DDL statements to create the tables. Include primary and foreign keys.

Consider the following relational database schema: employee(employee-name, employee-id, street, e-city) works(employee-

Consider the following relational database schema: employee(employee-name, employee-id, street, e-city) works(employee-id, company-id, salary) company(company-name, company-id, c-city) manages(employee-id, manager-id) Specify the following queries on this database schema using the relational operators we discussed in class. Write your answers on a separate sheet of paper in the order that they are specified below. Retrieve the name and address of employees who work for First Bank Corporation. Retrieve the name, street address, and city of residence of all employees...

Question