CS465 Databases and Scripting Languages
Midterm Fall 2016
- This exam is closed-note, closed book.
- You may not use any electronic devices.
- Circle the appropriate answer(s) for the multiple choice/answer questions.
- 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.
- You must answer all of the questions.
- 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.
- Extendible hash tables have an advantage over B+ trees for what type of query?
- Point query
- Range query
- Group by query
- Order by query
- 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
- 2
- 3
- log⌈M/2⌉N
- logMN
- log2N
- The time required for the read/write arm of a disk to move to the
appropriate track is called:
- transfer rate
- rotational latency
- seek time
- random access time
- In the Section relation, am I likely to want to place a secondary index on courseNo?
- No, it is an artificial key without much meaning
- No, it is rare that this attribute would appear in the
where (i.e., selection) clause of a query
- No, a query on this attribute will retrieve a significant fraction of the relation
- Yes, it is the primary key of the Section relation
- Yes, it will be frequently used in joins involving the Section relation
- Yes, it will often be used by an aggregate function such
as avg or min
- In the Professor relation, am I likely to want to place a secondary index on street?
- No, it is an artificial key without much meaning
- No, it is rare that this attribute would appear in the
where (i.e., selection) clause of a query
- No, a query on this attribute will retrieve a significant fraction of the relation
- Yes, it is the primary key of the Professor relation
- Yes, it will be frequently used in joins involving the Professor relation
- Yes, it will often be used to sort the results of a query
- 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?
- Entity integrity
- Referential integrity
- Business constraint
- Relationship integrity
- What is the formal basis for the DML of a relational database?
- SQL
- Relational algebra
- Relational calculus
- Relational model
- 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
- List the names of all professors who teach more than 30 sections
- List the names of all professors who teach more than 30 students in a section
- List the names of all professors who average more than 30 students in all the sections that they teach
- 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).
- 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);
-
SELECT c.courseNo, title
FROM Catalog c NATURAL JOIN Section s
WHERE department = 'Computer Science';
-
SELECT c.courseNo, title
FROM Catalog c, Section s
GROUP BY s.courseNo
WHERE department = 'Computer Science';
-
SELECT c.courseNo, title
FROM Section s, Course c
WHERE department = 'Computer Science';
-
SELECT c.courseNo, title
FROM Course c
WHERE department = 'Computer Science'
AND (SELECT courseNo FROM Section s) IN c;
- Which of the following queries would require set division?
- List all students who have taken COSC 140
- List all rooms that are either
a classroom or a professor's office
- List all professors who have taught all
courses in the computer science department
- List all professors and all students who
live in zip code 37996
- What is the English-language meaning of the following relational algebra
query?
ΠProfessor.name((σcourseNo='ECE351' Section) ⋈ Professor ⋈ (σbuilding='Min Kao'Room))
- List all professors who are both teaching sections of ECE351 and who have offices in Min Kao.
- List all professors who are either teaching sections of ECE351
or whose office is in Min Kao.
- List all professors who are teaching sections of ECE351 in Min Kao
- 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)
- What property do foreign keys preserve when performing normalization?
- Dependency preservation property that ensures that a functional dependency in the original relation exists in one of the smaller relations
- Redundancy elimination property that ensures that no attributes are duplicated in the decomposed relations
- Lossless join property that allows any tuple in the original relation to be re-constructed from corresponding tuples in the smaller relations
- 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.
- 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..1 ⟶ 1..* Staff 0..1 ⟶ 0..* PropertyForRent
- Normalization trap
- Lossless join trap
- Fan trap
- Chasm trap
- What is the name of the dependency used to convert a relation from 2nd normal form to 3rd normal form?
- partial dependency
- transitive dependency
- candidate key dependency
- multi-valued dependency
Multiple Answer (14 points): Choose all answers that are appropriate from the following choices.
- What are all possible candidate keys for the Section relation?
- sectionNo
- courseNo, days, startTime
- roomId
- days, startTime
- courseNo, roomId
- profNo, days, startTime
- roomId, days, startTime
- courseNo, roomId, days, startTime
- profNo, roomId
- 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:
- A section is offered in only one room
- A room may host multiple sections but in any semester may also not host any sections
- A section takes anywhere from 1 to 5 exams.
- 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:
ExamId | Section | RoomId | RoomNo | Date | TimeFrom | Duration | AnswersDate | AnswersTime |
271 | 1011 | R101 | MK524 | 2014-10-08 | 13:00 | 0:55:00 | 2014-10-15 | 08:00:00 |
271 | 4031 | R102 | MK404 | 2014-10-08 | 14:00 | 0:55:00 | 2014-10-15 | 08:00:00 |
271 | 5063 | R101 | MK524 | 2014-10-09 | 17:00 | 0:55:00 | 2014-10-15 | 08:00:00 |
273 | 4031 | R102 | MK404 | 2014-11-10 | 14:00 | 1:30:00 | 2014-11-01 | 12:00:00 |
... | ... | ... | ... | ... | ... | ... | ... | ... |
- The Date, TimeFrom, and Duration attributes give the dates and times on which an exam is being administered.
- The AnswersDate and AnswersTime attributes give the date and time on which the exam's solutions will be released.
- The other attributes should be self-explanatory
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.
- 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):
- You cannot insert a new exam unless you have a section that is taking the exam
- If you insert a section of an exam, you must simultaneously insert all sections of the exam or you will have an inconsistency
- You cannot insert an exam unless you have an answers date and an answers time
- 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
- 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
- If you change a section's id, you must change its room and timeFrom attributes.
- If you delete the last tuple containing a section, you lose information about the section
- If you delete one section of an exam, you must delete all sections of the exam or you will have an inconsistency
- 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.
- ExamId, Section → Date
- ExamId → Duration, AnswersDate, AnswersTime
- Section → RoomId, TimeFrom
- RoomId → RoomNo
- 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.
- ExamId, Section → Date
- ExamId → Duration, AnswersDate, AnswersTime
- Section → RoomId, TimeFrom
- RoomId → RoomNo
Queries. Write the following queries in the requested language.
- (8 points) SQL. Write an SQL query that lists the name and gpa of each student whose gpa is greater than 3.5.
- (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.
- (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.
- (7 points) Relational Calculus--CS565 students only. Write a relational calculus expression that returns the names of students who are enrolled in sectionNo '30189'.
- (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.
- (8 points) B+ Trees. Show what the following degree 5 B+ tree looks like after 30 is inserted into it.
- Assume that both leaves and internal nodes can contain a maximum of 4 keys.
- When you split a bucket and one of the two buckets must contain an odd number of keys, put the odd number of keys in the rightmost bucket.
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)
- The Student relation contains information about each student enrolled in the university, including their grade point average (gpa).
- The Professor relation contains information about each professor employed by the university. The roomId attribute denotes the professor's office.
- The Catalog relation contains information about each course offered by the university, including its name (title), description of the course contents (description), and the department offering the course (department).
f
- The Section relation contains information about each course that is offered by the university during the current semester, including the course number (courseNo), the classroom in which it is being offered (roomId), and the professor who is teaching the section (profNo).
- There may be multiple sections of the same course, such as multiple sections of CS102 and they may occur at the same time and on the same days.
- days is an integer with 1 denoting MWF classes and 2 denoting TR classes.
- A professor cannot teach two sections at the same time on the same
days.
- On a given day/time, only one section may be offered in a room (i.e., a room may not have two sections being simultaneously offered)
- The Roster relation contains information about which students are currently enrolled in each section of a course. A section must have a minimum of 10 students enrolled.
- The Transcript relation contains information about which courses a student has taken and the grade assigned to the student in that course (one of 'A', 'B', 'C', 'D', or 'F'). Students may have grades assigned in zero or more courses. The Transcript relation denotes past courses taken by a student while the Roster relation denotes courses the student is currently taking.
- The Room relation contains information about both professor offices and classrooms. This information includes the room's type (either office or classroom), the number of seats in the room (size), the room number and the building where the room is located. If the room is a professor's office, then the room may contain exactly one professor. If the room is a classroom, the room may host multiple class sections during the current semester, and it may host as few as 0 class sections. The roomId uniquely identifies each room.