Pitt Logo

INFSCI 1022
Database Management Systems

(Spring 2008, CRN 19275)

 SIS Logo
Homework 1. Part 1. Relational Model
Due before the class on Tuesday, 1/29/08.
4 problems, graded out of 80 points.
Most of the problems are referencing the following relations:
student(student_id, last_name, first_name)
address(student_id, state, city)
education(student_id, course, semester),
represented by the following tables:
student
student_idlast_namefirst_name
jb11JonesBrett
sa12SmithAlex
jb12JohnsonBill
tb1TurnerBill
ba3BrownAlex
ja75JonesAndrew
address
student_idstreetcity
jb11FifthPittsburgh
sa12ForbesPittsburgh
jb12MurrayPittsburgh
tb1FifthPittsburgh
ba3MainWexford
ja75PennWilkinsburg
education
student_idcoursesemester
jb111022Spring2007
sa121022Spring2007
sa121017Spring2007
jb121022Fall2006
tb11017Fall2007
ba31022Fall2007
ja751017Fall2006

Problem 1. [15 pts] Which of the following is not a true statement about the relation student above:

  1. student has 6 tuples.
  2. Brett is a tuple of the student.
  3. (sa12, Smith, Alex) is a tuple of the student.
  4. (tb1, Johnson, Brett) is a tuple of the student.
  5. (first_name, Brett, Alex, Bill, Andrew) is a tuple of the student.
  6. (student_id, last_name, first_name) is a schema of the student.
  7. The schema of student is schema_id.
  8. last_name is an attribute of the student.
  9. Jones is an attribute of the student.
  10. (jb11, Jones, Brett) is a schema of the student.
  11. The student has 6 attributes.
  12. The student has 3 tuples.
  13. The student has an arity of 3.
  14. The domain of the student_id consists of all integer numbers.
  15. We can write the relation student as {(jb11, Jones, Brett), (sa12, Smith, Alex), (jb12, Johnson, Bill), (tb1, Turner, Bill), (ba3, Brown, Alex), (ja75, Jones, Andrew)}.

Problem 2. [15 pts] "Execute" the following expression for the relations above. Show (draw) each intermediate table generated by each relational operation. Write in English what task the expression performs.

Problem 3. [15 pts] You have two boxes, one with markers and one with envelopes. Relation markers with one attribute color shows all possible colors of markers in the first box. Relation envelopes with one attribute color shows all possible colors of envelopes in the box. Write a relational algebra expression that would give you all possible color combinations of a random marker from the first box and a random envelope from the second box.

Problem 4. [35 pts] For the relations above, write a relational algebra expression for the following tasks:

  1. List last names of all students who have first name Brett.
  2. List the last names and the first names of all students who have taken 1022.
  3. List all cities where at least one student who took 1022 in Spring 2007 lives.
  4. List all students who have taken both 1022 and 1017 courses.
Homework 1. Part 2. Unix and sqlplus
Due before the class on Tuesday, 1/29/08, assuming that we'll have Unix Lab on Tuesday, 1/22/08.
Graded out of 20 points.
Note: Unix and Oracle logins/passwords were distributed after the lecture on 1/17/08. If you haven't received your Unix and Oracle login/password, you should contact the instructor or GSA via email from a valid email address, so the login/password could be emailed to you. The assignment:
  1. Login into your Unix account.
  2. Login into your Oracle account.
  3. Type spool hw1.sql
  4. Type create table A (name char(20));
    Press Enter.
    You should see a response Table created.
  5. Type insert into A (name) values ('Smith');
    Press Enter.
    You should see a response 1 row created.
  6. Type select name from A;
    Press Enter.
    You should see a response
    NAME
    --------------------
    Smith
  7. Type spool off
  8. Exit sqlplus.
  9. Print the file hw1.sql. Include this printout with the rest of your homework.