Database Application Design

1
Database Application Design
February 4, 2000

• Handout 5

2
Course information

• Instructor Dragomir R. Radev (radev_at_si.umich.edu)
  
• Office 305A, West Hall
• Phone [phone]
• Office hours Thursdays 3-4 and Fridays 1-2
• Course page http//www.si.umich.edu/radev/654w00
  
• Class meets on Fridays, 230 - 530 PM, 311 WH

3
Foundations of relational implementation(contd)
4
Expressing queries in relational algebra
5
Examples
STUDENT
6
Examples (Contd)
ENROLLMENT
7
Examples (Contd)
CLASS
8
Examples (Contd)

• Full set of relations
• JUNIOR (Snum, Name, Major)
• HONOR-STUDENT (Number, Name, Interest)
• STUDENT (SID, Name, Major, GradeLevel, Age)
• CLASS (Name, Time, Room)
• ENROLLMENT (StudentNumber, ClassName,
  PositionNumber)
• FACULTY (FID, Name, Department)

9
Examples (Contd)

• What are the names of all students?
• What are the student numbers of all students
  enrolled in a class?
• What are the student numbers of all students not
  enrolled in a class?
• What are the numbers of students enrolled in the
  class BD445?
• What are the names of the students enrolled in
  class BD445?
• What are the names and meeting times of PARKS
  classes?
• Wwhat are the grade levels and meeting rooms of
  all students, including students not enrolled in
  a class?

10
Structured Query Language (SQL)
11
SQL Overview

• ANSI Standard
• Multitude of implementations
• SQL92 (ANSI)
• Not a programming language!
• Two major modes of use embedded and interactive

12
Projections in SQL

• SELECT SID, Name, MajorFROM STUDENT
• SELECT MajorFROM STUDENT
• SELECT DISTINCT MajorFROM STUDENT

13
Selections in SQL

• SELECT SID, Name, Major, GradeLevel, AgeFROM
  STUDENTWHERE Major MATH
• SELECT FROM STUDENTWHERE Major MATH
• SELECT FROM STUDENTWHERE Major MATH AND Age
  gt 21

14
Selections in SQL (Contd)

• Set values for the condition (IN, NOT IN)
• Ranges (BETWEEN)
• LIKE
• Wild cards (, _)
• IS NULL

15
Sorting in SQL

• ORDER BY
• ASC, DESC

16
Built-in functions

• COUNT, SUM, AVG, MAX, MIN
• SELECT COUNT()FROM STUDENT
• SELECT COUNT(Major)FROM STUDENT

17
Grouping

• GROUP BY
• HAVING
• Example
• SELECT Major, COUNT()FROM STUDENTGROUP BY
  MajorHAVING COUNT() gt 2
• Ordering (WHERE is computed first)

18
Subqueries

• SELECT NameFROM STUDENTWHERE SID
  IN (SELECT StudentNumber FROM ENROLLMENT
  WHERE ClassName BD445)

19
Joins in SQL

• Using more than one table in a SELECT
• Comparing subqueries and joins
• not equivalent
• Outer joins (ANSI vs. Access)

20
EXISTS and NOT EXISTS
SELECT DISTINCT StudentNumberFROM ENROLLMENT
AWHERE EXISTS (SELECT FROM ENROLLMENT
B WHERE A.StudentNumber B.StudentNumber
AND A.ClassName NOT B.ClassName)
21
EXISTS and NOT EXISTS (Contd)
SELECT Student.NameFROM STUDENTWHERE NOT
EXISTS (SELECT FROM ENROLLMENT WHERE
NOT EXISTS (SELECT FROM CLASS WHERE
CLASS.Name ENROLLMENT.ClassName AND
ENROLLMENT.StudentNumber STUDENT.SID))
22
Inserting data

• INSERT INTO ENROLLMENT VALUES
  (400,BD445,44)INSERT INTO ENROLLMENT (Student
  Number,ClassName) VALUES (400,BD445)INSERT
  INTO JUNIOR VALUES (SELECT SID, Name,
  Major FROM STUDENT WHERE GradeLevel JR)

23
Deleting data

• DELETE STUDENTWHERE STUDENT.SID 100DELETE
  ENROLLMENTWHERE ENROLLMENT.StudentNumber
  IN (SELECT STUDENT.SID FROM STUDENT WHERE
  STUDENT.Major Accounting)DELETE
  STUDENTWHERE Student.Major Accounting

Ordering!
24
Updating data
UPDATE ENROLLMENTSET PositionNumber 44WHERE
SID 400UPDATE ENROLLMENTSET PositionNumber
MAX (PositionNumber) 1WHERE SID 400
25
Database design using E-R Models
26
E-R Design

• Transforming user requirements represented using
  E-R models into relational database designs

27
Readings for next time

• Kroenke
• Chapter 10 Database application design
• YRK (optional)
• Chapter 8 Database application architectures
• Chapter 9 CGI programming