Exercise 8.1

Do the specified lab 7 review exercises.

Exercise 8.1.1

Given the Student and Enrollment tables, what do the following relational algebra queries return?

Student ⨯ Enrollment

π_{firstName,lastName,email}(σ_{grade=‘A’}(σ_{S.id=E.studentId}(S ⨯ E)))
π_{firstName,lastName,email}(σ_{grade=‘A’}(S ⋈_{S.id=E.studentId} E))
π_{firstName,lastName,email}(S ⋈_{S.id=E.studentId} σ_{grade=‘A’}(E))
π_{firstName,lastName,email}(S) ⋈_{S.id=E.studentId} σ_{grade=‘A’}(E)

x ← π_s.id(ρ_s(Student) ⋈_{m.id=s.mentorId} ρ_m(Student))
y ← π_m.id(ρ_s(Student) ⋈_{m.id=s.mentorId} ρ_m(Student))
result ← x ∪ y

Exercise 8.1.2

What do the following relational calculus queries return?

{s.firstName, s.lastName, s.email |
        Student(s) ∧ ∃e(Enrollment(e) ∧ e.grade=‘A’ ∧ e.studentId=s.id)}
{s.firstName, s.lastName, s.email |
        Student(s) ∧ Enrollment(e) ∧ e.grade=‘A’ ∧ e.studentId=s.id}

{s | ¬Student(s)}

Exercise 8.2

Consider the following questions.

1. If possible, specify a useful database command of some sort that can’t be implemented using standard SQL.

2. What are the basic approaches to database programming that abstract above raw SQL? Have you used technologies that implement any of them?

Exercise 8.3

For each of the following code segments:

• Identify the type of the code segment.
• Explain what it does.
• Explain how to run it.
• Indicate whether the function could be implemented using SQL.

Exercise 8.3.1

BEGIN
  dbms_output.put_line('Hello, PL/SQL!');
END;

Exercise 8.3.2

DECLARE
  CURSOR namesOut IS
    SELECT firstName, lastName
    FROM Student
    ORDER BY lastName, firstName;
BEGIN
  FOR nOut IN namesOut LOOP
    dbms_output.put_line(nOut.firstName || ' ' || nOut.lastName);
  END LOOP;
END;

Exercise 8.3.3

CREATE PROCEDURE skipCount(limit IN INTEGER, step IN INTEGER) AS
BEGIN
  FOR i IN 1..limit LOOP
    IF MOD(i, step) = 1 THEN
      dbms_output.put_line(i);
    ELSE
      dbms_output.put_line('-');
    END IF;
  END LOOP;
END;

Exercise 8.3.4

CREATE PROCEDURE enrollStudent
(studentIDIn IN Student.ID%type,sectionIDIn IN Section.ID%type) AS
  counter INTEGER;
BEGIN
  SELECT COUNT(*) INTO counter FROM Enrollment
    WHERE sectionID = sectionIDIn AND studentID = studentIDIn;
  IF counter >= 1 THEN
    RAISE_APPLICATION_ERROR(-20000, 'Student ' || studentIDIn ||
    	' is already enrolled in section ' || sectionIDIn);
  END IF;
  INSERT INTO Enrollment(studentID, sectionID)
  	VALUES (studentIDIn, sectionIDIn);
  dbms_output.put_line('Student ' || studentIDIn ||
  	' enrolled in section ' || sectionIDIn);
END;

Exercise 8.3.5

CREATE FUNCTION factorial (n IN POSITIVE) RETURN INTEGER AS
BEGIN
  IF n = 1 THEN
    RETURN 1;
  ELSE
    RETURN n * factorial(n - 1);
  END IF;
END factorial;

Exercise 8.4

CREATE TRIGGER noOverflow BEFORE INSERT ON Enrollment FOR each row
DECLARE
  counter INTEGER;
  sectionTooBig EXCEPTION;
BEGIN
  SELECT COUNT(*) INTO counter FROM Enrollment
    WHERE sectionID = :new.sectionID;
  dbms_output.put_line('First, check the size of that section.');
  IF counter >= 5 THEN
	raise sectionTooBig;
  END IF;
EXCEPTION
  WHEN sectionTooBig THEN
    RAISE_APPLICATION_ERROR(-20001,'full sect:'||:new.sectionID);
END;