Transcript Today
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 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 How 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.1 SQL • Quick example: we’ll see this again • Three basic clauses: SELECT, FROM, WHERE SELECT Lastname, MovieName FROM Customers, Rentals WHERE Customers.CustId = Rentals.CustomerID and Rentals.Duration > 30 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) 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 • Example Selection Example Employee SSN 999999999 777777777 888888888 Name John Tony Alice DepartmentID 1 1 2 Salary 30,000 32,000 45,000 SSN Name 888888888 Alice DepartmentID 2 Salary 45,000 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 Projection Example Employee SSN 999999999 777777777 888888888 Name John Tony Alice SSN 999999999 777777777 888888888 Name John Tony Alice DepartmentID 1 1 2 Salary 30,000 32,000 45,000 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! Cartesion Product Example Employee Name John Tony SSN 999999999 777777777 Dependents EmployeeSSN 999999999 777777777 Dname Emily Joe Employee_Dependents Name SSN John 999999999 John 999999999 Tony 777777777 Tony 777777777 EmployeeSSN 999999999 777777777 999999999 777777777 Dname Emily Joe Emily Joe 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 Join Example Employee Name John Tony SSN 999999999 777777777 Dependents EmployeeSSN 999999999 777777777 Dname Emily Joe Employee_Dependents Dname SSN Name 999999999 Emily John 777777777 Joe Tony