CSC459-800              E X A M   # 1             Name:____________________

R. Mohammadi                                                     Spring 2000     

 

(20 Pts) 1.

--------

Answer the following short answer questions:

 

   a) In creating a relation in SQL some fields may be accompanied by a NOT NULL option. What is this option   used for?

 

 

   b) What is the purpose of the REFERENCES constraint in CREATE TABLE?

 

 

   c) What is the purpose of the CHECK constraint in CREATE TABLE?

 

 

   e) How do you ensure uniqueness of primary keys for tables in SQL?

 

 

 

(15 Pts.) 2.

---------

How did you ensured that no two rows were the same in the table resulting from a union in project1 part2 (RA Interpreter Project)? Choose a, or b, or explain your way in c).

 

   a) designed a merge algorithm and included code that ensured only one of  the two rows that were the same             ended up in the resulting table.

 

   b) put all rows from both tables in the resulting table and then removed the duplicates.

 

   c)

 

 

========================================================================

Consider the following entities/tables in a typical university database

with the relationships that are articulated:

 

        Faculty (fss_num, name, address, age, salary)

        Attends (sss_num, c_num, sec_num)

        Section (c_num,sec_num,fss_num)

        Student (sss_num, name, address, age)

        Course  (c_num, cdesc,credits, size_limit)

 

        A Course may have multiple Sections. 

        A Student Attends multiple course Sections.

        A Section may have multiple Students.

        A Section has only one Faculty.

        A Faculty may teach multiple Sections.


 

(25 Pts.) 3.

---------

 a) What do you consider as the primary key for each entity?

 

 

 

b) What are the foreign keys for each of the following tables. In each case, specify the attributes and the table they link the specified table to:

 

    Attends -

 

 

    Section -

 

 

 c) What type of relationships (i.e. 1-1, 1-m, or m-n) exists between

    each of the following pairs:

      1. Student        Section

      2. Course         Attends

      3. Faculty        Student

      4. Student        Attends

      5. Section        Section

 

 

 d) What Referential Integrity Constraint exists between Student and Attends?

 

 

 

 

(20 Pts.) 4.

---------

a) What are we trying to get with the following relational algebra expression, concisely state the query that it is written for.  NO POINTS are given for explanations that are phrased like: first a selection is performed on ... then a join with ... then a projection of ....

 

    ((Courses WHERE credits > "3") JOIN Section) JOIN Faculty) [fss_num,name]

 

 

 

 b) Write the equivalent RC.

 

 

 

(20 Pts.) 5.

---------

Write an RA expression and an RC formula that finds the cdesc, and credits

of all courses taken by the student with the name "Jones".