Name: ____________________________CSC 3800Final ExamFall 20091. Use the relational schema on pages 4 & 5 of this exam for the following questions:a. What is the output of QueryF1SELECT s.snameFROM Student sWHERE 2 < (SELECT COUNT(*)FROM Grade_report grWHERE gr.student_number = s.stnoAND gr.grade = “B”);b. What is the output of QueryF2SELECT *FROM Student sWHERE s.class = 4;c. Write a query to list student name, student number, department name, and class for students in class 1 or 2 who are majoring in computer science.d. What is the output of QueryF4SELECT s.sname, s.majorFROM Student sWHERE s.stno IN (SELECT g.student_number FROM Grade_report g WHERE g.grade = 'A');Final Exam Page 1 CSC 3800 Fall 200925 ptsDue not later than 12 noon onFriday December 11, 2009Name: ____________________________2. Define 1NF, 2NF, 3NF, and BCNF. What is the motivation for putting a relation in BCNF? What is the motivation for 3NF?Final Exam Page 2 CSC 3800 Fall 200925 ptsName: ____________________________Answer any two of the following questions (25 pts each):3. Identify the main components of a DBMS and briefly explain what they do.4. Name the main steps in database design. What is the goal of each step? In which step is the ER model mainly used?5. What are integrity constraints? Define the terms primary key constraint and foreign key constraint. How are these constraints expressed in SQL? What other kinds of constraints can we express in SQL?6. Relational calculus is said to be a declarative language, in contrast to algebra, which is a procedural language. Explain the distinction.7. What are null values? Are they supported in the relational model, as described in Chapter 3? How do they affect the meaning of queries? Can primary key fields of a table contain null values?8. What is Dynamic SQL and how is it different from Embedded SQL?9. Illustrate redundancy and the problems that it can cause. Give examples of insert,delete, and update anomalies. Can null values help address these problems? Are they a complete solution?10. What is decomposition and now does it address redundancy? What problems maybe caused by the use of decompositions?11. Define functional dependencies. How are primary keys related to FDs?12. When is decomposition said to be dependency-preserving? Why is this property useful?Final Exam Page 3 CSC 3800 Fall 2009Name: ____________________________Final Exam Page 4 CSC 3800 Fall 2009RoomSectionDepartment_to_majorCoursePrereqName: ____________________________Final Exam Page 5 CSC 3800 Fall