CS465 Databases and Scripting Languages

Midterm Fall 2016

  1. This exam is closed-note, closed book.
  2. You may not use any electronic devices.
  3. Circle the appropriate answer(s) for the multiple choice/answer questions.
  4. The last page of the exam gives the relations you should use for many of the exam questions. I recommend ripping this page off the exam so that you can easily refer to them.
  5. You must answer all of the questions.
  6. Good luck!

Multiple Choice (28 points): Choose the best answer from the following choices. Circle only one answer! If two answers seem like they might work, choose the best of the two answers.
  1. Extendible hash tables have an advantage over B+ trees for what type of query?
    1. Point query
    2. Range query
    3. Group by query
    4. Order by query

  2. If a B+ tree is used as a cluster index for a file, what is the worst case number of disk accesses that will be required to access a record from a relation?
    1. 1
    2. 2
    3. 3
    4. log⌈M/2⌉N
    5. logMN
    6. log2N

  3. The time required for the read/write arm of a disk to move to the appropriate track is called:

    1. transfer rate
    2. rotational latency
    3. seek time
    4. random access time

  4. In the Section relation, am I likely to want to place a secondary index on courseNo?
    1. No, it is an artificial key without much meaning
    2. No, it is rare that this attribute would appear in the where (i.e., selection) clause of a query
    3. No, a query on this attribute will retrieve a significant fraction of the relation
    4. Yes, it is the primary key of the Section relation
    5. Yes, it will be frequently used in joins involving the Section relation
    6. Yes, it will often be used by an aggregate function such as avg or min

  5. In the Professor relation, am I likely to want to place a secondary index on street?
    1. No, it is an artificial key without much meaning
    2. No, it is rare that this attribute would appear in the where (i.e., selection) clause of a query
    3. No, a query on this attribute will retrieve a significant fraction of the relation
    4. Yes, it is the primary key of the Professor relation
    5. Yes, it will be frequently used in joins involving the Professor relation
    6. Yes, it will often be used to sort the results of a query

  6. One of the constraints for the Relational Model states that if a foreign key exists in a relation, either the foreign key value must match a candidate key value of some tuple in its parent relation or the foreign key value must be wholly null. What is this constraint named?

    1. Entity integrity
    2. Referential integrity
    3. Business constraint
    4. Relationship integrity

  7. What is the formal basis for the DML of a relational database?

    1. SQL
    2. Relational algebra
    3. Relational calculus
    4. Relational model

  8. What is the English language meaning of the following SQL query?
    SELECT name FROM Professor p JOIN Section s ON p.profNo = s.profNo NATURAL JOIN Roster r
       GROUP BY r.sectionNo, p.name
       HAVING count(*) > 30
    		 
    1. List the names of all professors who teach more than 30 sections
    2. List the names of all professors who teach more than 30 students in a section
    3. List the names of all professors who average more than 30 students in all the sections that they teach
    4. List the names of all professors who cumulatively teach more than 30 students in all their sections (e.g., if a professors teaches 10 students in one section and 25 students in a second section, then the professor would be printed because the students teaches a cumulative total of 35 students).

  9. Select the SQL query from the following list that is the same as this SQL query:
    SELECT courseNo, title FROM Catalog c
        WHERE department = 'Computer Science'
             AND courseNo IN (SELECT courseNo FROM Section);
    		     

    1. SELECT c.courseNo, title
          FROM Catalog c NATURAL JOIN Section s
          WHERE department = 'Computer Science';
      			   
    2. SELECT c.courseNo, title
          FROM Catalog c, Section s
          GROUP BY s.courseNo
          WHERE department = 'Computer Science';
      			   
    3. SELECT c.courseNo, title
          FROM Section s, Course c
          WHERE department = 'Computer Science';
      			   
    4. SELECT c.courseNo, title
          FROM Course c
          WHERE department = 'Computer Science'
          AND (SELECT courseNo FROM Section s) IN c;
      			   

  10. Which of the following queries would require set division?
    1. List all students who have taken COSC 140
    2. List all rooms that are either a classroom or a professor's office
    3. List all professors who have taught all courses in the computer science department
    4. List all professors and all students who live in zip code 37996

  11. What is the English-language meaning of the following relational algebra query?

    ΠProfessor.name((σcourseNo='ECE351' Section) ⋈ Professor ⋈ (σbuilding='Min Kao'Room))

    1. List all professors who are both teaching sections of ECE351 and who have offices in Min Kao.
    2. List all professors who are either teaching sections of ECE351 or whose office is in Min Kao.
    3. List all professors who are teaching sections of ECE351 in Min Kao
    4. List professors whose only set of classes are ECE351 sections in Min Kao (i.e., to appear on this list they must not teach any courses other than ECE 351 and they must not teach in any other building)

  12. What property do foreign keys preserve when performing normalization?

    1. Dependency preservation property that ensures that a functional dependency in the original relation exists in one of the smaller relations
    2. Redundancy elimination property that ensures that no attributes are duplicated in the decomposed relations
    3. Lossless join property that allows any tuple in the original relation to be re-constructed from corresponding tuples in the smaller relations
    4. Anomaly preservation property that ensures that if an insert, update, or delete anomaly is present in the original relation, it is present in at least one of the smaller relations.

  13. What is the name of the trap that occurs when an ER diagram suggests that there is a path from entity A to entity B to entity C, but the relationship between an instance of A and an instance of C cannot always be inferred because a path between certain instances of B and C do not actually occur (e.g., a branch employs staff and staff manage properties, yet we cannot always infer which branch manages a property because the property may not be managed by any staff member). Here's what the ER diagram for this type of trap would look like:

    Branch 1..11..* Staff 0..10..* PropertyForRent

    1. Normalization trap
    2. Lossless join trap
    3. Fan trap
    4. Chasm trap

  14. What is the name of the dependency used to convert a relation from 2nd normal form to 3rd normal form?

    1. partial dependency
    2. transitive dependency
    3. candidate key dependency
    4. multi-valued dependency

    Multiple Answer (14 points): Choose all answers that are appropriate from the following choices.

  15. What are all possible candidate keys for the Section relation?

    1. sectionNo
    2. courseNo, days, startTime
    3. roomId
    4. days, startTime
    5. courseNo, roomId
    6. profNo, days, startTime
    7. roomId, days, startTime
    8. courseNo, roomId, days, startTime
    9. profNo, roomId

  16. ER Diagrams: Suppose we have three entities-sections, rooms, and exams-and that they have the following two relationships 1) rooms host sections, and 2) sections take exams. The ER diagram might be drawn as:
    Rooms ------------------------⟶ Sections ----------------------------⟶ Exams
         1        host              2         3              take           4
    
    The numbers shown above represent missing multiplicities for the relationships. You may assume that:
    1. A section is offered in only one room
    2. A room may host multiple sections but in any semester may also not host any sections
    3. A section takes anywhere from 1 to 5 exams.
    4. An exam may be administered to as few as 1 section as a lower bound and an unlimited number of sections as an upper bound.

    For each number, enter the letter associated with its multiplicity:

    a.	1..1			1) ________________
    b.	0..1
    c.	1..0			
    d.	1..*                    2) ________________
    e.	*..*			
    f.	0..*			
    g.	*..0			3) ________________
    h.	*..1
    i.      1..5
    j.      5..1                    4) ________________
    k.      *..5
    

    (14 points) Normalization. Suppose you are given the following relation named Exam:

    ExamIdSectionRoomIdRoomNoDateTimeFromDurationAnswersDateAnswersTime
    2711011R101MK5242014-10-0813:000:55:002014-10-1508:00:00
    2714031R102MK4042014-10-0814:000:55:002014-10-1508:00:00
    2715063R101MK5242014-10-0917:000:55:002014-10-1508:00:00
    2734031R102MK4042014-11-1014:001:30:002014-11-0112:00:00
    ...........................

    Assume that the table has the following functional dependencies:

    ExamId, Section → Date
    ExamId → Duration, AnswersDate, AnswersTime
    Section → RoomId, TimeFrom
    RoomId → RoomNo
       
    You may not assume that any other functional dependencies exist. Answer the following questions about this relation.

  17. This table is susceptible to various types of anomalies. Circle all of the following anomalies that apply (Hint: there are 4 anomalies in the following list):

    1. You cannot insert a new exam unless you have a section that is taking the exam
    2. If you insert a section of an exam, you must simultaneously insert all sections of the exam or you will have an inconsistency
    3. You cannot insert an exam unless you have an answers date and an answers time
    4. If you change the room for a certain section, you must change every instance of the section's room in the relation or you will have an inconsistency
    5. If you change an exam's answers date and answers time, you must change all instances of the exam's answers date and answers time in the relation or you will have an inconsistency
    6. If you change a section's id, you must change its room and timeFrom attributes.
    7. If you delete the last tuple containing a section, you lose information about the section
    8. If you delete one section of an exam, you must delete all sections of the exam or you will have an inconsistency

  18. Assume that the primary key for this relation is (ExamId, Section). What functional dependency(s) would you use to convert this relation from 1st to 2nd normal form? Circle all functional dependencies that apply.

    1. ExamId, Section → Date
    2. ExamId → Duration, AnswersDate, AnswersTime
    3. Section → RoomId, TimeFrom
    4. RoomId → RoomNo

  19. Assume that the primary key for this relation is (ExamId, Section). What functional dependency(s) would you use to convert this relation from 2nd normal form to 3rd normal form? Circle all functional dependencies that apply.

    1. ExamId, Section → Date
    2. ExamId → Duration, AnswersDate, AnswersTime
    3. Section → RoomId, TimeFrom
    4. RoomId → RoomNo

    Queries. Write the following queries in the requested language.

  20. (8 points) SQL. Write an SQL query that lists the name and gpa of each student whose gpa is greater than 3.5.
    
    
    
    
    
    
    
    
    
      
  21. (8 points) SQL. Write an SQL query that lists the names of all students who received a grade of 'A', 'B', 'C' in a course whose courseNo is 'CS102'. You must write the answer using joins, not subqueries.
    
    
    
    
        
    
    
    
    
    
    
    
    
    
      
  22. (7 points) SQL--CS565 students only. Write an SQL query that prints the total number of sections being taught by each professor and sort the result by profNo. Print two items-the professor's number and the number of sections taught by that professor. The field for the number of sections should be named numSections.
    
    
    
    
        
    
    
    
    
    
    
    
    
      
  23. (7 points) Relational Calculus--CS565 students only. Write a relational calculus expression that returns the names of students who are enrolled in sectionNo '30189'.
  24. (20 points) Entity Relationship Design. Draw an ER diagram for the university database that shows entities and relationships. Do not show multiplicities. Make sure you label the relationships and where appropriate, draw arrows indicating the directionality of the relationship. Hint: One or more of the relations are not entities but relationships.
  25. (8 points) B+ Trees. Show what the following degree 5 B+ tree looks like after 30 is inserted into it.

University Database

The following information should be used in answering all exam questions except the Normalization questions (17-19). You are given the following relations about a university database that is maintained by a university.

Student(studentNo, name, city, street, zipcode, dateOfBirth, gpa)
Professor(profNo, name, city, street, zipcode, salary, roomId)
Catalog(courseNo, title, description, department)
Section(sectionNo, courseNo, roomId, days, startTime, duration, profNo)
Roster(sectionNo, studentNo)
Transcript(studentNo, courseNo, grade)
Room(roomId, roomNumber, size, building, type)