CS411 Midterm Review

1

• CS411 Midterm Review
• Tao Cheng
• (tcheng3_at_uiuc.edu)

2
Midterm Details (Non-I2CS)

• Tues (3/13/07) 7pm 75 minutes. Please arrive
  early.
• Two Rooms!
• Lastname A-L DCL 1320
• Lastname M-Z SIEBL 1404.
• Bring UIUC photoid, pen, pencil,eraser
• Closed book, no notes
• Don't cheat

3
No class on exam day

• but Lawrence will hold additional "office
  hours" 2pm - 315 in DCL1320
• Please come with questions and your copy of the
  textbook.
• (Chengkai Li will provide an extra session for
  I2Cs students)

4
Questions during exam

• We will not answer technical questions.
• (Minor non-technical English clarifications OK)
• Assume proctors do not know or understand CS411
  content.
• Instead -
• State your assumptions and answer accordingly.

5
Format

• Problem 1(20) 10 Difficult Multiple Choice
• Problem 2(15) E-R model
• Problem 3(15) Function Dependencies,NFs etc
• Problem 4(25) SQL
• Rel Algebra covered in Prob 1 but you will not
  need to create any Rel. Algebra expressions

6
Difficulty

• Questions are detailed but fair
• If you know the material we want you to do well!
• Multiple Choice questions are tricky because the
  incorrect responses may seem very reasonable if
  you don't know the material.

7
Answers

• Put your NetID on every page
• Write your answers in the exam sheet
• Make sure your work is concise and clear
• Show your working
• Will not penalize for minor SQL syntax errors
  (this is subjective but in your favor)
• Your writing must be legible

8
How to screw up the exam
9
Throwing away points

• Attempt to cheat
• Skim read the questions or responses
• Take care even with 'simple' questions
• Miss an entire problem
• Spend too much time on one question

10
Style

• Multiple Choice questions test your direct
  knowledge
• Series of short answer questions test your
  ability to apply that knowledge

11
What to study

• An incomplete checklist
• Example Questions and Answers

12
Checklist

• E-R Can you create an ER diagram from a
  specification?
• Are you familiar with and can apply E-R concepts?
  e.g. constraints(keys,ref. integrity)
  multiplicity, relations,roles,attributes, weak
  entity sets,isa, supporting relations
• Can you define these?

13
Fds and all that jazz

• Review HW1 solutions
• 3rd 4th BCNF definitions
• Manipulate Functional Dependencies
• Is a Relation in 3rd NF/BCNF etc?
• Calculate Closure
• Find keys
• BCNF Decomposition
• Dependency Preserving?
• MVDS

14
Relational Algebra

• Do you know the operators symbols and what they
  do?
• Can you compare a rel. algebra expressions with
  an English description?
• Able to use binary operators - intersection,
  difference, union, join,natural join, Cartesian
  product
• "Minimum? Except? And? Or?" Style questions
• Which operators are the 'primitive' operators?

15
SQL (incomplete list)

• Be able to create a SELECT statement from an
  English question or relational algebra
• Proficient with using Group By, Having,
  Intersection Except Union All In Any Exists
  Natural Join Outer Join Where Correlated
  Subqueries...
• Be able to CREATE TABLE VIEW ...
• Constraints(Check, Foreign key...)

16
SQL

• CREATE Table , Trigger, View, CHECK
• Primary Keys, Indexes
• INSERT/UPDATE/DELETE
• Complex SELECT
• (subqueries, Aggregrates, multiple tables)

17

• Example questions?

18

• Review HW1 HW2 for examples of long questions

19
Example E/R questions

• Create an ER model from the following facts... X
  has a Y. There is always one U for every V...
• Transform your diagram into set of Relations
• Describe the OO approach
• What are the design principles of ER modeling?
• What is wrong with the following diagram?

20
Example Q, FDs

• R1(A,B,C) AB -gt C in 3rd NF? BCNF? 4th NF?
  (midterm q is harder than this example)
• Definition of 3rd NF , BCNF, 4th NF etc?
• Closure of xy?
• Decompose R2 into a set of relations that satisfy
  BCNF
• many other possible questions

21
Example Questions

• Create a table (including PK and FK constraints)
  
• Create a view that returns all names of students
  enrolled in a 2007 course who have the highest
  GPA
• Create a view that returns the number of students
  that achieved each grade letter
• Write the SQL to remove rows with a missing name.
• Create a trigger to delete inserted rows where
  sid is not in table T2

22

• Example answers...

23

• A(a)

24
What's the question?

• A p a s agta2(A x rA(a2) A)

25
Minimum of A(a)

• A p a s agta2(A x rA(a2) A)

26
What's the exam question?

• SELECT st_id FROM students WHERE st_id IN (SELECT
  st_id FROM ENROLLED WHERE year2006)

27
The student id values of students enrolled this
year?

• SELECT st_id FROM students WHERE st_id IN (SELECT
  st_id FROM ENROLLED WHERE year2006)

28
What's the question?

• SELECT name,st_id FROM nicknames NATURAL JOIN
  (SELECT st_id FROM students WHERE st_id IN
  (SELECT st_id FROM ENROLLED WHERE year2006))

29
Nickname and studentids of students that joined
this year?

• SELECT name,st_id FROM nicknames NATURAL JOIN
  (SELECT st_id FROM students WHERE st_id IN
  (SELECT st_id FROM ENROLLED WHERE year2006))

30

• SELECT netid,e1.sid FROM students s1,enrollment
  e1 WHERE s1.side1.sid
• GROUP BY name,netid
• HAVING count() gt5

31
Names of students that have enrolled in more than
5 courses

• SELECT name FROM students s1,enrollment e1 WHERE
  s1.side1.sid
• GROUP BY name
• HAVING count() gt5

32
Oops?

• SELECT FROM students
• WHERE nicknameltgtNULL

33
Oops?

• SELECT FROM students
• WHERE nicknameNULL

34
Both cases- No rows returnedInsteadIS NOT
NULL

• SELECT FROM students
• WHERE nickname IS NULL

35
Time

• 75 minutes is sufficient time if you know the
  material.
• 1 point per minute
• ... but watch out in case you get stuck

36
Resources

• HW solutions
• Previous midterm pdf on website. (Note emphasis
  will be different more SQL questions)
• Feel free to post your answers to the newsgroup
  (we will not post the solution)
• Newsgroup LA posted 'practice midterm Questions'
• Book.
• I2CS videos these slides on the website

37
Book

• The ER model (Chap 2)
• The relational model translation from ER
• Sections 3.1 3.3
• Schema refinement (Sections 3.4 3.7)
• Relational algebra (Sections 5.1 5.4)
• SQL Views (Chapter 6)
• Constraints Triggers (Chapter 7)

38
Not in the midterm

• System Aspects
• Security
• PHP / project information

39

• Good luck

???
40
Final Reminder

• Drop class  deadline for undergrads that is
  tomorrow, Mar 9th.

???
41
Today's speaker

• Opportunity for extra credit.
• Compass Submit short report (lt1000) words on
  today's guest lecture - see Compass for details.
• Lets welcome Marty.