A route will not happen without a plane. The same route happens with different planes over time. A plane does not have to fly a route. A plane can fly on many different routes.

The following rules will be used in Question 5 as well. We can describe a Route with the following attributes and conditions: Routing ID: A 6 digit series of numbers and letters. It is never less or more than 6. Length Of Route: The number of approximate hours a flight should take on this route. This number is optional, but when present it must be a positive number less than 20 hours. Departure City Name and Arrival City Name. These names are required and are 85 characters or less.

We can describe a plane with the following attributes and conditions: Plane serial number: An up to 10 digit series of numbers. Plane name: Is required and is up to 100 characters in length. Fuel capacity: An optional number representing the number of litres of fuel the plane can hold. This value can have 2 decimals and must be large enough to contain the largest planes fuel tank which is just over 300,000 litres. (Note: there are no specific rule restrictions for fuel capacity other than defining the appropriate data type)

When a plane flies a route, we must know two additional attributes: Departure Date and Arrival Date: Both are dates indicating a year, month and day. These dates are both required. Arrival Dates cannot happen before departure dates.

Q1 ) Solve the above by creating an ERD You must label each relationship line with a verb and use the appropriate cardinality symbols. Be sure to label PRIMARY and FOREIGN KEYs and all column names. You do not need datatypes in your ERD.

Q2 )

CREATE the tables and any CONSTRAINTS specified for the Routes & Planes business rules above.

Create 2 valid INSERTs on each table.

Create an INSERT to violate each of the constraints (in-line and out-of-line) on each table. In other words, if there are 3 constraints, you need 3 INSERTs to test each.

You must write a comment above each failing INSERT and describe what is being tested.

For example, if you had the follow Table with column constraint:

CREATE TABLE ExampleTable ( Name VARCHAR(50) NOT NULL );

-- The following INSERT will violate the NOT NULL constraint on the Name column. INSERT INTO ExampleTable (Name) VALUES (NULL);