Today

1
Today

• Collection of unrelated stuff
• Questions?
• HW
• 4 -5 drop lowest
• emphasize project
• Projects
• Functional Dependencies?
• Someone asked why Keys, Normal forms
• Relational Algebra/SQL

2
Projects

• SQL Server (more on SQL later)
• Implement Database (in ms visual studio)
• ODBC
• Control Panel Establish data source
• Visual Interdev
• Connect to database make pages VBScript
• Microsoft Front Page
• c\programfiles\microsoft front page

3

• How do we get info out of a Database?
• Customers(CustID ,LastName,FirstName )
• Inventory(TapeID, MovieName)
• Rentals(CustomerID,TapeID,CkoutDate, Duration)

4
DBApplications Theory

• Need a way to grab data interested in
• Query
• SQL, VHLL-youll need it in your projects
• Relational Algebra- underlying principles
• Set of operators to extract info
• Doesnt allow us to form any question we want,
  but can construct most that we want
• Chapter 4.1

5
SQL

• Quick example well see this again
• Three basic clauses SELECT, FROM, WHERE
• SELECT Lastname, MovieName
• FROM Customers, Rentals
• WHERE
  Customers.CustId Rentals.CustomerID and
  Rentals.Duration gt 30

6
Quick Look Relational Algebra

• Operators sets as input, new set as output
• Basic Set Operators
• union, intersection, difference, but no
  complement. (watch comparable sets)
• Selection
• Projection
• Division(not in text)
• Cartesian Product
• Joins, combination of cart product/selection
• Unofficially aggregate functions(not in text)

7
Set Operations

• Binary operations
• Result is table(set) with same attributes
• Sets must be compatible!
• R1(A1,A2,A3)R2(B1,B2,B3)
• Domain(Ai)Domain(Bi)
• Union all tuples in R1 or R2
• Intersection all tuples in R1 and R2
• Difference all tuples in R1 and not in R2
• No complement whats the universe?

8
Selection

• Grab a subset of the tuples in a relation which
  satisfy a given condition
• Unary operation returns set with same
  attributes, but selects rows
• Use and, or, not, gt, lt to build condition
• Example

9
(No Transcript)
10
Projection

• Unary operation, selects columns
• Returned schema is different, so returned tuples
  are not subset of original set, like they are in
  selection
• Eliminates duplicate tuples
• Example

11
(No Transcript)
12
Cartesian Product

• Binary Operation
• Result is tuples combining any element of R1 with
  any element of R2, for R1XR2
• Schema is union of Schema(R1) Schema(R2)
• Example
• Notice we could do selection on result to get
  meaningful info!

13
(No Transcript)
14
Join

• Most often used
• Combines two relations, selecting only related
  tuples
• Equivalent to a cross product followed by
  selection
• Resulting schema has all attributes of the two
  relations, but one copy of join condition
  attributes
• Example

15
(No Transcript)