Answer the following database-related questions | Complete Solution

• Answer the following database-related questions:

• Explain the major challenges you may encounter when you implement an EER with a superclass/subclass relationship.  Explain the possible ways to implement it.  What factors will you will consider when implementing a superclass/subclass relationship?  (10 points)

• What is the most important factor for performing normalization?  In practice, what normal form is the typical to reach a normalized design?  Why do we normally not need to reach high level normal forms?  Do you start your conceptual database design following a bottom-up normalization process?  (10 points)

• What is a SQL injection vulnerability?  What are root causes for common web applications? What actions you need to take to prevent this problem? (5 points)

Compare and contrast stored procedures and triggers within a database (5 points).

• Do you create an ordered file whose records are based on a primary key such as customer name and use a primary index on the attribute?  Explain your reasons.  (5 points)

Compare and contrast B-Tree and B⁺-tree.  (5 points)

• Compare and contrast join, correlated subquery, and non-correlated subquery.  (5 points)

Explain the benefits for using database VIEWs.  (5 points)

• Normalization:

2.a Consider the following relation about car sales:

CAR_SALE

Please be aware that only a single value is allowed for Salesman# of each tuple.  One car can be sold by only one salesman.

The primary key of CAR_SALE is {Car#, Salesman#}. 

Some of functional dependencies are:

Date_sold → Discount_amt

Salesman# → Commission%

Car# → Date_sold

You need to figure out the rest of the general functional dependencies.

Based on the given primary key, is this relation in 1NF, 2NF, or 3NF? Why or why not? How would you successively normalize it completely?  Please explain your normalization steps (15 points).

2.b Consider the following relation

Supplier_PART_PROJECT

The business requirement is to track the quantity of a particular part from one particular supplier for one project.  Based on the given primary key, is this relation in 5NF?  Why or why not?  Please explain your rationale (5 points).

• Create an Airline database schema based on the ERD in the textbook (also see below).  You need to implement your DDL statements. Your DDL statements should clearly specify the Primary Key, Foreign Key, Unique, NOT NULL and Check constraints.

In addition, you need to implement the following business rules that are declaratively specified in your schema or be implemented through triggers.  If your RDBMS support triggers, you may implement your trigger solutions.  If your RDBMS doesn’t support triggers, you need to show your trigger scripts (no need to implement them) (15 points):

• The Airline and Number in the FLIGHT must be unique.
• The fare amount (Amount) is in a range of ($0 – $10,000).
• The maximum seats (Max_seats) for any airplane type cannot exceed 600.
• The maximum number of flight legs (leg_no in the FLIGHT_LEG) cannot exceed 4.
• For any instance of a flight leg, the date of (Date in the LEG_INSTANCE) must be either current date or a future date.

You need to implement your DML statements with sample test data to retrieve the following information (15 points):

• Create a list of aircraft types that can land in the airport at Washington Dulles International Airport (Airport_code is 'IAD').
• List all fare information for flight 'United 189'.
• Create a list of direct flights (including scheduled departure time and arrival time) starting from Baltimore Washington International Airport (Airport_code is 'BWI') and terminating at San Francisco International Airport (Airport_code is 'SFO') which have more than two seats available on 4/16/2015.  In addition, create a list of direct returning flights from 'SFO' to 'BWI' on 4/22/2015.

You need to turn in your DDLs (create tables) and your SQLs.  You are required to enter a few sample test data and perform queries.  You may turn in your DBMS, the log file or screenshots that include your DDLs in a document (e.g., Word or text file) to prove your work is done properly.