BCNF and JDBCCSE 444 section, July 8, 2010Today• BCNF decompositions• JDBC for project 2What is BCNF?A relation R is in BCNF if:If A1,… An B is a non-trivial dependency in R, then {A1, …, An} is a superkey for RWhy do BCNF decompositions?5BCNF decomposition algorithmBCNF_Decompose(R)find X s.t.: X ≠X+≠ [all attributes]if (not found) then “R is in BCNF”let Y = X+- Xlet Z = [all attributes] - X+decompose R into R1(X Y) and R2(X Z)continue to decompose recursively R1 and R2Note: a set of attributes X is a superkey if X+ = ABCDEConsider the following FDs:• CD → E• D → B• A → CDBCNF example: table R(A, B, C, D, E)Which one are the bad dependences?CD is not a superkeyD is not a superkeyA is a superkeyCD+ = BCDED+ = BDA+ = ABCDEBADBADNote: a set of attributes X is a superkey if X+ = ABCDEConsider the following FDs:• CD → E• D → B• A → CDBCNF example: table R(A, B, C, D, E)R3(A,C,D)[BCNF]R5(C,D,E)[BCNF]R2(B,C,D,E)[D+ = BD ≠ BCDE]BADBADR(A,B,C,D,E)[CD+ = BCDE ≠ ABCDE]R4(B,D)[BCNF]Note: a set of attributes X is a superkey if X+ = ABCDConsider the following FDs:• C → D, C+ = ACD• C → A, C+ = ACD • B → C, B+ = ABCDAnother example: R(A,B,C,D)R3(B,C)[BCNF]R2(A,C,D)[BCNF]BADBADR(A,B,C,D)[C+ = ACD ≠ ABCD]Note: a set of attributes X is a superkey if X+ = ABCDEConsider the following FDs:• AB → C, AB+ = ABCD• DE → C, DE+ = CDE• B → D, B+ = BDA third example: S(A,B,C,D,E)S3(A,B,E)[BCNF]S5(A,B,C)[BCNF]S2(A,B,C,D)[B+ = BD ≠ ABCD]BADBADS(A,B,C,D,E)[AB+ = ABCD ≠ ABCDE]S4(B,D)[BCNF]BAD1stSolution:Note: a set of attributes X is a superkey if X+ = ABCDEConsider the following FDs:• AB → C, AB+ = ABCD• DE → C, DE+ = CDE• B → D, B+ = BDA third example: S(A,B,C,D,E)S3(C,D,E)[BCNF]S5(A,B,E)[BCNF]S2(A,B,D,E)[B+ = BD ≠ ABDE]BADBADS(A,B,C,D,E)[DE+ = CDE ≠ ABCDE]S4(B,D)[BCNF]BAD2ndSolution:Note: a set of attributes X is a superkey if X+ = ABCDEConsider the following FDs:• AB → C, AB+ = ABCD• DE → C, DE+ = CDE• B → D, B+ = BDA third example: S(A,B,C,D,E)S3(B,D)[BCNF]S5(A,B,E)[BCNF]S2(A,B,C,E)[AB+ = ABC ≠ ABCE]BADBADS(A,B,C,D,E)[B+ = BD ≠ ABCDE]S4(A,B,C)[BCNF]BAD3rdSolution:Note: a set of attributes X is a superkey if X+ = all attributesA table with “real” dataCandID Date Time EmpID RoomNoC21 16-April-09 9.30 AM E211 CSE550C21 17-April-09 11.00 AM E211 CSE550C5 16-April-09 11.00 AM E51 CSE218C2 1-May-09 9.30 AM E211 CSE218JobInterviewsNote: a set of attributes X is a superkey if X+ = all attributesA table with “real” dataCandID Date Time EmpID RoomNoC21 16-April-09 9.30 AM E211 CSE550C21 17-April-09 11.00 AM E211 CSE550C5 16-April-09 11.00 AM E51 CSE218C2 1-May-09 9.30 AM E211 CSE218JobInterviewsWhat are the FDs?• CandID EmpID, Room• Date, Time CandID, EmpID, RoomNo• Date, EmpID CandID, Time, RoomNo• more?BADNote: a set of attributes X is a superkey if X+ = all attributesA table with “real” dataJobInterviews (CandID, Date, Time, EmpID, RoomNo)What are the FDs?• CandID EmpID, Room• Date, Time CandID, EmpID, RoomNo• Date, EmpID CandID, Time, RoomNo• more?BADJI2 (CandID, Date, Time)[BCNF]JI1 (CandID, EmpID, Room)[BCNF]JI (CandID, Date, Time, EmpID, RoomNo)[CandID+ = {CandID, EmpID, Room} ≠ all]Today• BCNF decompositions• JDBC for project 2JDBC (Java Database Connectivity)A Java API to access a database: – connect to a data source– send queries and update statements– retrieve and process resultsDocumentation: http://java.sun.com/javase/6/docs/technotes/guides/jdbc/JDBC lets Java talk to your databaseDBMSJDBCJava ApplicationClient machineDBMS-proprietary protocolDatabase serverDBMS vendors make JDBC drivers…DBMSJDBCJava ApplicationClient machineDBMS-proprietary protocolDatabase serverJDBC API for appsJDBC API for drivers… letting JDBC talk to any databaseMySQLJDBCJava ApplicationClient machineMySQL JDBC DriverDatabase serverJDBC API for appsJDBC API for driversDB2PostgresPostgresJDBC DriverDB2JDBC DriverJDBC architectureThree-tier modelDBMSJDBCApplication ServerClient machineDBMS-proprietary protocolDatabase serverApplication, BrowserServer (business logic)RMI, SOAP, REST, HTTPconnection poolingdistributed transactions…First, load the driver• For Project 2, look in project2.tar.gz– SQL Server driversqljdbc4.jar– PostgreSQL driverpostgresql-8.4-701.jdbc4.jar– Already installed on Lab PCs (use 444shell.cmd)• Put on class path, then tell Java to load itClass.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");Class.forName ("org.postgresql.Driver");– Class.forName() optional in current versions of JavaJDBC exampleConnection con = DriverManager.getConnection("jdbc:sqlserver://iisqlsrv;database=imdb", "username", "password"); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table1"); while (rs.next()) { int x = rs.getInt("a"); String s = rs.getString("b"); float f = rs.getFloat("c"); }Modifying the databaseUse Statement.executeUpdate():Statement stmt = con.createStatement(); int rowsUpdated = stmt.executeUpdate ( "UPDATE Actor " + "SET gender = 'F' " + "WHERE gender IS NULL" );• Works with any database modification, not just UPDATE• Warning – will throw if you run it with a query!Close all JDBC objects when doneConnection con = DriverManager.getConnection(...);Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table1");// do work with rs...rs.close();stmt.close();con.close();Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");Connection con = null;try { con = DriverManager.getConnection( … ); …} catch (Exception e) {e.printStackTrace();} finally {con.close();}Parameterized queries -PreparedStatementPreparedStatement pstmt = con.prepareStatement("SELECT lname FROM persons WHERE id = ? ");…pstmt.setInt(1, 34);ResultSet rs1 = pstmt.executeQuery();…pstmt.setInt(1, 63);ResultSet rs2 = pstmt.executeQuery();…Parameterized queries -PreparedStatementNo need to worry about quotes ‘, “ PreparedStatement pstmt = con.prepareStatement("SELECT website FROM shopsWHERE name = ? OR owner = ? ");…pstmt.setString(1, "George's");pstmt.setString(2, "Oh \"wow\"!");…Parameterized queries -PreparedStatementNo need to worry about quotes ‘, “ PreparedStatement pstmt = con.prepareStatement("SELECT website FROM shopsWHERE name = ? OR owner = ? ");…pstmt.setString(1, "George's");pstmt.setString(2, "Oh \"wow\"!");…Statement
View Full Document
Unlocking...