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".