A. ( TOTAL 21 POINTS )
A1.
(step1) T1 <- Sigma
(OFFERING)
semester='spring 2000'
(step2) T2 <-
GROUPING
(T1)
Course# COUNT
section#
(step3) Result <- Rho
T2
(course#,No_of_sections_offered)
** Rho line
is optional
Grading scale : Total 6 points for A1.
Minus 2 for missing
step 1
Minus 4 for missing
step 2(missing GROUPING)
( minus 2 for error
in using parameter for GROUPING )
A2.
(step1) T1 <- Sigma
(OFFERING)
semester='Fall 99'
(step2) T2 <- T1 * TEXT
(step3) T3 <- Sigma
(BOOK)
publisher='Addison Wesley'
(step4) T4 <- T2 LEFT_OUTER_JOIN
T3
T2.BookISBN=T3.BookISBN
(step5) Result <- PI
T4
section#,course#,BookISBN
Grading Scale: Total 8 points for A2.
minus 1 for missing step 1
minus 1 for missing step 2
minus 1 for missing step 3
minus 3 for missing step 4
minus 1 for missing step 5
minus 1 for reverse order ** step
3 must be done before step 4
A3.
(step1) T1 <- Sigma
COURSE
Dept='CS'
T2 <- TEXT * OFFERING * T1
(step2) T3 <- Sigma
COURSE
Dept='EE'
T4 <- TEXT * OFFERING * T1
(step3) T5 <- Pi T2
BookISBN
T6 <- Pi
T4
BookISBN
(step4) T7 <- T5 INTERSECTION T6
Grading Scale : Total 7 pts for A3.
Minus 2 for missing step 1
Minus 2 for missing step 2
Minus 1 for missing step 3
Minus 2 for missing step 4
B. ( TOTAL 22 POINTS )
B1
select o.Section#, o.InstructorName, b.bookISBN
from TEXT t ,OFFERING o, BOOK b
where b.BookISBN = t.BookISBN AND t.Section# = o.Section# AND
b.AuthorName= o.Instructor_name
Grading Criteria: (Total: 6pts)
-1 for each missing reln
-2 for mising "b.AuthorName= o.Instructor_name"
-1 for each other missing joins
upto -4 for using wrong relations.
B2
Create View CSBOOKS
AS select o.Section # , o.Course #, o.InstructorName, t.BookISBN
from TEXT t, OFFERING o, COURSE c,
where c.Dept = CS AND t.Section# = o.section# and c.Course# = o.Course#
AND o.Semester = "Spring 2000"
Grading Criteria: (Total: 6pts)
-1 for each missing reln
-2 for mising "create view CSBOOKS AS"
-1 for each other missing joins
No. In general, join views are considered NOT updatable.
Grading Criteria: (Total: 2pts)
Credit is given for Yes with a valid reason.
B3
select. s.Stud#, count(*)
from student s, enroll e, offering o
where o.Semester = "Spring 2000" AND s.Major = "ISYE" AND
s.stud# =
e.student# AND e.section# = o.section #
Group BY stud#
Having count(*) > 5
Grading Criteria: (Total: 8pts)
-1 for each missing reln
-2 for mising "Group BY"
-2 for missing "Having"
-1 for each other missing join
upto -4 for doing "wrong things" like wrong choice of grouping
attribute
or wrong use of counting
etc.
C. (Total 13 points)
C1
INSTRUCTOR | InstructorName | I_phone | I_rank
|
P._X P.
"Asst Prof"
OFFERING | Section # | Course # | Semester
| Instructor_name |
_Y Spring 2000
_X
COURSE | Course# | C_name | Dept
_Y CS
Grading Criteria: (Total: 6pts)
-2 for each join condition
-1 for each missing selection constants
minus points for using wrong tables.
C2
BOOK| BookISBN | AuthorName | BookTitle |
Publisher |Year
| Price
_X
_T
Addison Wesley
TEXT| Section # | BookISBN
_S
_X
RESULT|
|
P.
_S | _T
Grading Criteria: (Total: 7pts)
-3 for missing result table
-2 for missing join
-1 for mising selection constant
minus points for using wrong tables.
D. ( TOTAL 8 POINTS )
D1.
{ t.C_Name, t.Dept
| COURSE(t) and
( Exist s )( OFFERING(s) and s.semester='spring 2000' and
s.course# = t.course# ) }
Grading Scale : Total 5 pts for D1
Minus 1 for missing
t.C_Name, t.Dept
Minus 1 for missing
COURSE(t)
Minus 2 for missing
(Exist s) or OFFERING(s)
Minus 1 for missing
s.semester='spring 2000'
Minus 1 for missing
s.course# = t.course#
* maximum total minus
is -5
D2. Quantifier : Existential Quantifier
Free tuple Variable
: t
Bound tuple Variable
: s
Grading Scale : Total 3 pts for D2
Minus 1 for missing
Quantifier
Minus 1 for error in
designating Free variable
( example for bad answer
: t and s are Free variable )
Minus 1 for error in
designating Free tuple
( example for bad answer
: t.C_name and t.Dept are free variable)
E. ( TOTAL 6 POINTS )
E1.
List (StudentName,StudentPhone,Section#)
for all students
that enrolled
any section in any semester.
Grading scale : Total 3 pts
for E1
Minus 1 for missing
any attribute in
(StudentName,StudentPhone,Section#)
Minus 2 for missing
"that enrolled any section in any semester"
E2. STUDENT
--------------------------------------------
| STUD# | Bdate | Major
| S_Name | S_Phone |
--------------------------------------------
| _SX |
| | P. | P.
|
ENROLL
-----------------------
| Student# | Section#
|
-----------------------
| _SX
| P. |
Grading scale : Total 3 pts
for E2
Minus 2 for missing
joining condition ( _SX )
Minus 1 for missing
any attribute in printing ( P. )
F. (Total 5 points)
1NF: Employee (EmpID, Bdate) PK = EmpID
Emp-Dependents (EmpID, Dependent-name) PK = EmpID +
Dependent-name, FK = EmpID
Emp-Tags (EmpID, Car-lic-tag#) PK = EmpID + Car-lic-tag#,
FK =
EmpID
Grading Scale: Total 5 pts for F.
minus 1 for missing the (Empid, Bdate)
minus 2 for error in Emp-Dependents. Must show that it is an all-key
relation.
minus 2 for error in Emp-Tags. Must show that it is an all-key
relation.
(No points off for foreign key specification.)
G. (Total 7 points)
G1. interpreted key is Acct# + Cust_name
G2. Is this in 3NF? No - it is supposed to be in 1NF
as given with
one row per customer per account (with possible duplication which
is
removed in second normalization.) The reason it is NOT in 3NF is
because it
fails the general test which says that for every f.d. where
X -> A, either X must be a superkey or A must be a prime attribute
(i.e.
part of some candidate key).
For the 2 dependencies in question, Acct# and Cust_name which are
on the LHS of the dependencies: none of the LHS is a superkey.
Hence both violate 3NF.
G3. Normalized:
The following decomposition removes the two problem dependencies.
Account (Acct#, Acct-type, Monthly-fee) PK = Acct#
Account-Customers (Acct#, Cust-name), PK = Acct# +
Cust-name, FK = Acct#
Customer (Cust_name, Cust_phone), PK = Cust_name.
GRADING: Total 7 pts for G.
1 point for G1.
2 pts for G2. They must clearly say why the general test fails.
(Saying that relation is not even in 2NF is good also -
but must state
why.)
4 points for G3.
minus 2 for each relation missed in the decomposition (upto
-4).
upto minus 4 for breaking up wrongly .
H. (Total 8 points)
Relation needs to get rid of Flight# - > Plane_type at the 2NF level
and then Plane_type - > Plane_capacity at the 3NF level.
2NF: Flight (Flight#, Plane-type, Plane-capacity) PK = Flight#
Reservation (Flight#, Date, Cust-name,Seat#)
PK = Flight#, Date, Cust-name
3NF: the flight is further decomposed to remove the
dependency : Plane_type - > Plane_capacity
FlightX (Flight#, Plane-type) PK =Flight#
Plane (Plane-type, plane-capacity) PK = Plane-type
The final result in 3NF has relations Reservation, FlightX and
Plane.
Note : It is OK to state the final result as 3 relations without
the intermediate Flight relation provided a justification is given
for breaking up in a certain way.
All these three are in BCNF? Yes
GRADING H: Total 8 points.
4 points for removing the two problem dependencies properly.
3 points for getting the correct end result.
1 point for BCNF answer.