McMillan

1
McMillans 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, hell 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

• 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 Codds 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) ?StudI
D1StudID2 ?(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
• sqlitegt SELECT FROM Sample
• ab
• 11
• 24
• 39
• 48
• 55
• sqlitegt 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