McMillan’s Quizzes

1. McMillan’s Quizzes • Multiple choice, open book, open notes • Not open computers, not open classmates • Partial credit: if less than half the class gets a question right, he’ll give half-credit for the next best answer • Not easy!

2. Of the following, which is an advantage of using a • relational database? • Attributes are inherited • Relations are related to others hierarchically • It is the only database approach that provides • data independence • It permits queries that are at least as powerful as • relational algebra • All of the above

3. Which of the following is uncharacteristic of a database schema? • Attributes are not strongly typed • Entities without a primary key • A relation with a variable number of attributes • It is unique to a database instance. • All of the above

4. id dno since Professor WorksIn Department DeptHead since • Of the following, what is implied by the heavy arrow from • “Professor” to “WorksIn” • Each department must have at least one professor • All professors work in exactly one department • Professors may be members of one or more • departments • All professors either work in, or head some • department • None of the above

5. Why is the ‘join’ operator not one of the five • primitive (basic) operators of relational algebra? • It was not included in Codd’s seminal papers • Joins can be accomplished by composing primitive operations. • It is equivalent to the Carteasian product • It has too many variants to be considered a primitive operator. • None of the Above

6. StudID1( Course1Course2( (T1(1StudID1, 2Course1, 3Semester1, 4Grade1), Transcript) ⋈StudID1=StudID2 (T2(1StudID2, 2Course2, 3Semester2, 4Grade2), Transcript))) • Which of the following best describes the result of this • relational algebra statement? • IDs of students who have taken at least two different courses • IDs of students who have taken the same course twice • IDs of students who have taken exactly one course • IDs of students who have taken exactly two courses • None of the above

7. Problem Points • ER: binary, ternary, aggregate relationships. Problem: Represent supplier inventory, and who sells what to whom. Suppliers Products Orders

8. SQL: Aggregate Operators • Example: sqlite> SELECT * FROM Sample; a|b 1|1 2|4 3|9 4|8 5|5 sqlite> SELECT a, MAX(b) FROM Sample; • What does this return? Does it even run?

9. Relational Calculus – Some points • TRC != DRC • TRC Projection

10. CREATE TABLE Courses (    number TEXT,    semester TEXT,    seats INTEGER,    PRIMARY KEY(number, semester));CREATE TABLE Enrollment (    student_name TEXT,    course_number TEXT,    course_semester TEXT,    grade INTEGER,    PRIMARY KEY(student_name, course_number, course_semester),    FOREIGN KEY student_name REFERENCES Students(name),    FOREIGN KEY (course_number, course_semester) REFERENCES Courses(number, semester)); • 4C: Find all classes in which no student failed (TRC) • Do with and without the universal quantifier

11. Questions? • Database Concepts / History • ER model, ER diagrams • Relational model • Relational algebra • Relational calculus • SQL • Database Application Development