DO NOT include the Questions or other content from the instructions

Part 1)

Identify 4 database constraints (examples can include: Not NULL, primary key, unique, domain; there can be different ones as well). Explain each constraint (40%) and provide an example of each constraint other than the one in the text (15%). The response should be in the order of 500+ words

Part 2)
HINT: Review the Normalization Process: Parts and Suppliers One-to-Many Example in the Terms and Concepts discussion.
Background
We want to keep track of the price we charge for each type of part, the supplier for each type of part, and the amount we pay the supplier for each type of part (the cost). Each supplier can provide us with many different types of parts, but each part can be provided to us by only one supplier. This requires a one-to-many relationship.

Functional analysis:
Part —- > Price, Cost, Supplier, Street, City, State, Zip, Telephone
Supplier —- > Street, City, State, Zip, Telephone
Part —- > Price, Cost, Supplier
Table:
Parts (Part, Price, Cost, Supplier, Street, City, State, Zip, Telephone)

Compete the following:
1. Include a definition of 2 normal form
2. Is the above table in 2NF?
3. Using the table and field names in the above table explain your answer

WEEK 8
You were just hired by an airline industry to create a database to record the various airlines and the pilots that work for them
Discussions with the industry representatives focused on two initial entities, Airlines and Pilots; the following key points were agreed:

1. Each pilot is assigned a unique PilotID.
2. Each AirlineID is unique
3. Each pilot may work for one airline, each airline can have many pilots.
4. FName, LName, JobTitle and Salary describe each pilot (with the obvious meaning)
5. AirlineID, NPlanes, NRoutes, NPilots describe each airline (NPlanes number of planes, NRoutes number of routes, NPilots number of pilots)
6. No fields beyond those in the report are needed.
The sample data may not represent all possible values of each field – consider each field’s domain during the design.

The objective is to replace the following report with a relational database. The submission will consist of a word compatible document to record the design process, and an Access DB.
Here is the relational schema (table notation) for the above report / table:
PilotAirlines (PilotID, FName, LName, JobTitle, Salary, AirlineID, NPLanes, NRoutes, NPilots)

The functional dependencies are:
PilotID – – > FName, LName, JobTitle, Salary, AirlineID, NPLanes, NRoutes, NPilots
PilotID – – > FName, LName, JobTitle, Salary, AirlineID
AirlineID – – > NPLanes, NRoutes, NPilots

The specific tasks are listed below, the percentage in parenthesis corresponds to the grade weight for each task.
Name your document Last Name_TermProject (i.e. Smith_TermProject). When you are asked to provide an explanation or description, include enough content to demonstrate that you understand the definition, term, concept, etc. and how it applies to this exercise.
Incorporate the following outline in your submission – include the section numbers – not the questions or other content from the instructions.

Review the existing report, functional dependencies provided and the sample data (as well as field domains and common knowledge) and document any assumptions you feel are appropriate (beyond those in the key points) and identify initial entities (person, place, thing).
Define functional dependency, and explain one row of the functional dependencies provided above in plain English (use field names). (you do not need to explain partial or transitive dependency)
Based on multiplicity – explain the relationship between the initial Entities in plain English – (either one-to-many, or many-to-many).
Design: specify all tables and fields at each normal form level
a) First Normal Forum (1NF) assessment / action – if needed
Copy the 1NF definition from the text (include quotes and page number)
Assess the UNF table provided and if necessary, make the changes needed to conform to the 1NF definition. Document the resulting 1NF table(s) using relational schema notation or spreadsheet format
Explain how each table(s) meets the 1NF definition (use field names)
b) Second Normal Form (2NF) assessment / action – if needed
Copy the 2NF definition from the text (include quotes and page number)
Assess the 1NF table(s) in the previous section and if necessary, make the changes needed to conform to the 2NF definition. Document the resulting 2NF table(s) using relational schema notation or spreadsheet format.
Explain how each table meets the 2NF definition (use field names)
c) Third Normal Form (3NF) assessment / action if needed
Copy the 3NF definition from the text (include quotes and page number)
Assess the 2NF table(s) in the previous section and if necessary, make the changes needed to conform to the 3NF definition. Document the resulting 3NF table(s) using relational schema notation or spreadsheet format.
Explain how each table meets the 3NF definition (use field names)
Use the 3NF tables in your design, to create an new SQLite database, load the sample data provided,
Name your database Last Name_TermProject (i.e. Smith_TermProject).
Create a Query, that provides the results for the following request: List each PilotID, FirstName, LastName, the Airline each pilot works for, sorted by pilot.
Create a Query, that lists the PilotID, FirstName, LastName and salary. Sort by salary highest to lowest
Create a Query that lists Airline and sum the number of planes and number of routes and sort by Airline

Include screen shots of your query syntax and your results. Include a comment /* Your name and date */ at the start of each query. Include these screen shots in the same document.
Submission content organization, clarity, spelling and grammar (10%)
CO8

Design a database management system with the skills gained from this course.