Practice Midterm Soln's ======================= 1. Define the following terms a) i) domain of an attribute -set of possible values an attribute can take ii) domain of a table - the cartesian product of the domains of the attributes in its head. b) first normal form rule - a table should have no multi-valued fields. c) superkey - a subset A of a head of a table T such that for any two rows r,s in T we have r[A]=s[A} => r=s. d) null value - a special value for a field in a row to indicate that the value of the field for this row is unknown, not yet defined, or inapplicable. e) outer join - if Head(R) = A_1..A_nB_1..B_k and Head(S)=B_1..B_kC_1..C_m then the outer join of R & S is the table (R join_O S) with Head(R join_O S) = A_1..A_nB_1..B_kC_1..C_m with rows w that satisfy one of the following 3 conditions: 1) w is in R join S 2) w is of the form u*(Null,..,Null) (m many Nulls) where u in R is not joinable with any v in S. 3) w is of the form (Null,..,Null)*v (n many Nulls) where v in S is not joinable with any u in T. 2. a) Convert the following relational algebra expressions to SQL: i) (T_1 join T_2 join (T_3 where A='Bob'))[A,B] select A,B from T_1 x, T_2 y, T_3 z where x.C=y.C and ... /* here we'd and over each common attribute of x and y */ and z.A='Bob' and z.D=x.D and .. /* here we'd perform and's over each attribute z shares with x */ and z.E=y.E and .. /* here we'd perform and's over each attribute z shares with y */ ii) T_1 divideby ((T_2 join T_3) where A=B) Suppose Head(T_1)=A_1..A_n,B_1..B_m Head(T_2)=B_1..B_r Head(T_3)=B_s..B_m where s <=r Then the SQL is: select A_1,..,A_n from T_1 where not exists ( select T_2.B_1,..,T_3.B_m from T_2, T_3 where A=B and T_2.B_s=T_3.B_s and ... and T_2.B_r=T_3.B_r and not exists (select * from T_1 y where T_1.A_1=y.A_1 and .. and T_2.B_1 = y.B_1 .. and T_3.B_m =y.B_m)) b) Show how to express join with \pi,\sigma, x. Let Head(T) = A_1...A_n, B_1... B_m Let Head(S) = B_1...B_m, C_1... C_k Then T join S = ((T x S) where T.B_1=S.B_1 and..T.B_m=S.B_m)[A_1..A_nB_1..B_mC_1..C_k] 3. a) Write SQL to create an Employee table that looks like: Employee ======== |FNAME | LNAME | SSN | Salary | Department| up to up to int float 4 chars 13 chars 13chars Sol'n ----- create table Employee ( FNAME VARCHAR(13), LNAME VARCHAR(13), SSN INT, Salary FLOAT, Department CHAR(4) primary key(SSN)); b) Write SQL to insert three rows into this table then write a command to delete the second row. Finally, write a command to add a column ManagerSSN which is an integer. insert into Employee values ('John','Smith',022339999,10,'Rsch'); insert into Employee values ('Jane','Smith',072339999,20,'Acct'); insert into Employee values ('Joe','Smith',082339999,50,'Sale'); delete from Employee where SSN='072339999' alter table Employee add ManagerSSN INT; (actually I didn't go over this last command in class so it would not be on a test.) c) Explain how the following query would evaluate on the row |John |Smith |022749910 |50000 |NULL| (select FNAME from Employee where Dept='_') union (select FNAME from Employee where Salary > 40000) This query would return John. This first subselect would not return this row since the NULL will not satisfy the where clause. However, the second subslect will be satisfied. 4. a) Give an example of a query that cannot be performed in SQL Let Parent(X,Y) be a table which stores a row (X,Y) if X is a parent of Y. Just using SQL can't write a query which returns rows (Z,W) iff Z is an ancestor of W. b) Explain how inheritance works in OODBs. Two kinds of inheritance common in OODBs:inheritance between types and inheritance between tables. In a typical OOBD one can declare create type as create type type_name1 ( components and their types); Another type can then inherit from this type using a command: create type type_name2 (some new components) inherits type_name2; type_name2 will have all the component s of type_name1 together with each new component declared in the parenthesis. A table of type type_name2 could be created with the command create table HiThere of type type_name2; Tables can inherit from each other in OODBs for instance one could write create table HiThere2 (some new components) inherits HiThere1, ..,some other tables; This table would then have the attributes listed in some new components and those in HiThere1, and some other tables. In addition a row in HiThere2 partially appears in any table Hithere2 inherits from. Specifically for a given row V in HiThere2 a row would appear in HiThere1 consisting of just those attributes of V which are from HiThere1. 5. Convert the ER diagram in the practice midterm to tables. Reasonable to assume card(Employee, Works_on) = (0,N) card(Projects, Works_on) = (1,N) So get the four tables (~~~ used to indicate primary key, foreign keys are menioned but hard to draw arrows pointing to what they reference with only text): Employee ======== |SSN | street | city | state| ~~~ Dependents Here SSN is a Foreign key referencing Employee.ssn =========+ |SSN | Dependent| ~~~~~~~~~~~~~~~~~ Works_On Here both SSN and PID are foreign keys ======== |SSN | PID | ~~~~~~~~~~~ Projects ======== |PID |PNAME |LName | PSSN| ~~~~