TodayProjectsDBApplications & TheorySQLQuick Look: Relational AlgebraSet OperationsSelectionSlide 9ProjectionSlide 11Cartesian ProductSlide 13JoinSlide 15Today•Collection of unrelated stuff•Questions?•HW–4 -5 & drop lowest–emphasize project•Projects•Functional Dependencies?–Someone asked why: Keys, Normal forms•Relational Algebra/SQLProjects•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 pageHow do we get info out of a Database?•Customers(CustID ,LastName,FirstName )•Inventory(TapeID, MovieName) •Rentals(CustomerID,TapeID,CkoutDate,Duration)DBApplications & Theory•Need a way to grab data interested in–Query•SQL, VHLL-you’ll need it in your projects•Relational Algebra- underlying principles–Set of operators to extract info–Doesn’t allow us to form any question we want, but can construct most that we want–Chapter 4.1SQL•Quick example: we’ll see this again•Three basic clauses: SELECT, FROM, WHERESELECT Lastname, MovieNameFROM Customers, RentalsWHERE Customers.CustId = Rentals.CustomerID and Rentals.Duration > 30Quick 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)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… what’s the universe?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, >, <… to build condition•ExampleSelection ExampleEmployeeSSN Name DepartmentID Salary999999999 John 1 30,000777777777 Tony 1 32,000888888888 Alice 2 45,000SSN Name DepartmentID Salary888888888 Alice 2 45,000Projection•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•ExampleProjection ExampleEmployeeSSN Name DepartmentID Salary999999999 John 1 30,000777777777 Tony 1 32,000888888888 Alice 2 45,000SSN Name999999999 John777777777 Tony888888888 AliceCartesian 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!Cartesion Product ExampleEmployeeName SSNJohn 999999999Tony 777777777DependentsEmployeeSSN Dname999999999 Emily777777777 JoeEmployee_DependentsName SSN EmployeeSSN DnameJohn 999999999 999999999 EmilyJohn 999999999 777777777 JoeTony 777777777 999999999 EmilyTony 777777777 777777777 JoeJoin•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•ExampleJoin ExampleEmployeeName SSNJohn 999999999Tony 777777777DependentsEmployeeSSN Dname999999999 Emily777777777 JoeEmployee_DependentsName SSN DnameJohn 999999999 EmilyTony 777777777
View Full Document
Unlocking...