Please answer all four questions.

1. Explain the difference between external, internal, and conceptual schemas. How are these different schema layers related to the concepts of logical and physical data independence? Explain the difficulties around external views, particularly with updateable views. (25 pts)
   
   
   

2. Define a the following: weak entity set, a partial key, participation constraint. (25 pts)
   Draw an ER Diagram that illustrates the use of of these constraints on the Employees, Policy, and Dependents entity and relationship sets discussed in class.
   
   
   

3. PapaCosta Airlines has a database that contains information about its Pilots (identified by social security number, or SSN) and Planes (identified by type). The plane types can be single engine, multi-engine, and jet. Pilots fly planes; the following situations concern the Flies relationship set. For each situation, draw an ER diagram that describes it (assuming no further constraints hold). (25 pts)
   1. Every pilot must fly some plane.
   2. Every pilot flies exactly one type plane (no more, no less)
   
   
   

4. Consider the SQL query whose answer is shown in Table Table 1. (25 pts)
   1) Modify this query so that only the name and login columns are included in the answer
   2) If the clause WHERE S.gpa >= 1.9 is added to the original query, what is the set of tuples in the answer?
   
   
   

sid	name	login	age	gpa
53831	Madayan	madayan@music	11	1.8
53832	Guldu	guldu@music	12	2

Table 1:Student with age < 18 on Instance S

NOTE: This is due next class, September 17, 2003 – No late submissions!