1. Given the following ERD, answer the questions a-e. Assume a professor can teach at most four classes and at least a class; A student can enroll in at most 5 classes and at least 3 classes, and a class can have enrollment of at least 10 students and at most 25 students.

 

a. Name the entity types and the relationship types in the ERD

b. Specify the cardinality numbers for the relationships

c. Translate the above ERD into relational model (indicate the tables with their attributes and constraints in graphical representation)

d. Create ENROLLS table using SQL create command with all the attributes; identify the primary key and Foreign key where applicable.                        

e. Present an expression in relational algebra to output first name, last name of a professor who teaches the class COSC531-101

f. Provide SQL query to print the professor's first name and last name who teach the class with CNUM = 'COSC531-101' (Remember that profnum appears as a foreign key in the class table)                                                                                            

 

2. Define Super key, Candidate Key, Primary key, and Foreign Key

3. What are the three approaches to designing a database?

4. Name the integrity constraints

5. Given the following relations 

            R1                   

A	B
a1	b1
a2	b2
a3	b3

            R2

C	D	B
c1	d1	b1
c2	d2	b3
c3	d3	--

a. Express R1xR2 as a relation                                                                          

b. Express R1* R2 as a relation (* join operator)                                                          

c. Relations for outer join of R1 and R2 as a relation    

 

6. Provide answer to (a-d)

a. What is de-normalization, and why is it important?  

b. Define Second Normal Form                                                                        

c. Define third Normal Form                                                                             

d. Define Boyce Codd Normal Form                 

e. Define  fourth Normal Form

f. Define fifth Normal Form                              

 

7.   Name and present three Armstrong's inference rules                                          

8.  Prove {X->Y, X->W, WY->Z} |=  {X->Z}                                                         

9.  Consider the universal relation R = {A,B,C,D,E,F,G,H,I,J} and the set of functional dependencies, F = {A,B} -> {C}, {A}-> {D, E}, {B}-> {F}, {F}-> {G, H}, {D}->{I,J}

a. Present a graphical representation of the relation R with all functional dependencies.

b. Decompose, R into 2 NF.

c. Decompose, R into 3 NF.                                                                                

10. Decompose the relation given below such that the resulting relations are in BCNF

11. Prove that the decomposition D(R) = (R1, R2, R3) has loss less property using the matrix method.                                                                                         

R = {SSN, ENAME, PNUMBER, PNAME,PLOCATION,HOURS}

R1= EMP = {SSN,ENAME}

R2=PROJ={PNUMBER,PNAME,PLOCATION}

R3=WORKS_ON = {SSN,PNUMBER,HOURS}

Function dependency, F = {SSN-> ENAME; PNUMBER->(PNAME, PLOCATION;

                                                 (SSN, PNUMBER)->HOURS}

 

12.  What is the difference between Relational Algebra and Relational Calculus?

13.  Present a relational calculus (tuple calculus) expression to retrieve fist name and last name of all students who have GPA >= 3.0  and also present an equivalent SQL statement.

14. Present a relational calculus expression for retrieving Professors first name, last name who teaches class CNUM = COSC531-101 and also present an equivalent SQL 
 

SLNAME SFNAME SNUM STUDENT GPA GRADE ENROLLS CNUM PNO CTIME CLASS Teaches ROOM PROFESSOR PLNAME (PFNAME OFFICENO