Unit 2 Introduction to Database

1. Unit 2Introduction to Database Dr. Magdi AMER

2. Intro • Many programs need to save information on disk. • The role of DB system is to provide a layer of abstraction between the program and the database (Save, read, update, delete). • First system was released in 1960 by IBM called Information Management System (IMS). • It was based on the hierarchical model. Dr. Magdi Amer

3. Intro (Cnt) • Hierarchical model has several drawbacks (query, update) • Edgar Codd, a British mathematician, created the relational model in 1970, where information is saved into tables. • SEQUEL, which was later named SQL (but still pronounced sequel), was developed, based on relational algebra to allow users to define, query and update the database in a standardized way. • Codd, with the collaboration of Chris Date, formed their own consulting company and continued to develop the relational standard. Dr. Magdi Amer

4. First Normal Form Dr. Magdi Amer

5. First Normal Form • Each record contains the same number of columns. • Each column contain 1 and only 1 value. • No information is saved in the order of the records. • Each Record must have a key Dr. Magdi Amer

6. Second Normal Form • Each column is a function of the entire key, not part of the key. Dr. Magdi Amer

7. Second Normal Form • Each column is a function of the entire key, not part of the key. Dr. Magdi Amer

8. Third Normal Form • Each column is directly a function of the key. • Third normal form is violated when a non-key field is a fact about another non-key field Dr. Magdi Amer

9. Normalization What you need to remember: • Normalization is needed: • To save space • To prevent data inconsistency (2 rows supposedly containing the same info but in reality there is a difference in the information stored) • To facilitate update Example of inconsistent data due to lack of normalization Dr. Magdi Amer

10. Introduction to SQL Dr. Magdi Amer

11. Introduction to SQL Dr. Magdi Amer

12. Introduction to SQL Dr. Magdi Amer

13. Getting data from normalized tables Employee Grade_Data This is done by rebuilding the original table before normalization Select Employee.Employee_number, Employee.Employee_name, Grade_data.Grade, Grade_data.Cost_per_hour From Employee inner join Grade_data on Grade_data.Grade = Employee.Grade Dr. Magdi Amer

14. Accessing Oracle Express Dr. Magdi Amer

15. Accessing Oracle Express Dr. Magdi Amer

16. Dr. Magdi Amer

17. SQL Dev Dr. Magdi Amer

18. SQL Dev Dr. Magdi Amer

19. SQL Dev Dr. Magdi Amer

20. SQL Dev Dr. Magdi Amer

21. SQL Dev Dr. Magdi Amer

22. Creating NetBeans App Dr. Magdi Amer

23. Making a Connection Dr. Magdi Amer

24. Accessing the data from Java Dr. Magdi Amer

25. Accessing the data from Java Dr. Magdi Amer

26. Making a query import java.io.*; import java.sql.*; public class DatabaseTest{ public static void main(String[] args){ Connection con= null; Statement s = null; • try { con = DatabaseManager.getConnection(); • s = con.createStatement(); • String query = " SELECT firstName, lastName, sin FROM Employee" ; • System.out.println(query); • ResultSet result = s.executeQuery(query); • String firstName, lastName, sin; Dr. Magdi Amer

27. Making a query while(result.next()) • { • firstName = result.getString("firstName"); • lastName = result.getString("lastName"); • sin = result.getString("sin"); • System.out.println("first Name = "+firstName+"last Name="+lastName+ • "sin ="+sin); • }//end while • }//end try • catch(Exception ex) • { System.out.println(ex); } • finally { • if(con != null) • try • { con.close(); } • catch(Exception ex) • { ex.printStackTrace(); } } • } // end main • } // end class Dr. Magdi Amer

28. Writing in a table Dr. Magdi Amer

29. Writing in a table Dr. Magdi Amer

30. Writing in a table Dr. Magdi Amer

31. Writing in a table Dr. Magdi Amer

32. Writing in a table try { • con = DatabaseManager.getConnection(); s = con.createStatement(); String sql = "create table Data (name varchar (32), id integer);"; int result = s.executeUpdate(sql); sql = "insert into Data (name, id) values ('Tom', 123 );"; result = s.executeUpdate(sql); sql = "insert into Data (name, id) values ('Mike', 123 );"; result = s.executeUpdate(sql); sql = "UPDATE Data SET Data.id = 121 WHERE (Data.name='Mike');"; result = s.executeUpdate(sql); } Dr. Magdi Amer

33. Writing in a table catch(Exception ex) { System.out.println(ex); } finally { try { s.close(); con.close(); } catch(Exception e) { System.out.println(e);} } } } Dr. Magdi Amer

34. SQL Injection • Consider the following code • Normally this code will return the information if the password is correct • What will happen if the user enters ' or '1'='1 • The executed query will be SELECT firstName, lastName, sin FROM Employee where password = ' ' or '1'='1‘ • The previous query result will always be true  hacker can access any record ……… String pass = // read from GUI or web • String query = " SELECT firstName, lastName, sin FROM Employee where password = ' "+pass+ " ' "; ResultSet result = s.executeQuery(query); …………… Dr. Magdi Amer

35. Prepared Statement public void Save() { String query = "INSERT INTO LANGUAGE ( ID, NAME) VALUES (?, ?)"; PreparedStatement s = null; Connection con = null; String url=”…”; try { Connection con = DatabaseManager.getConnection(); s = con.prepareStatement(query); s.setString(2, ""+getName()); //reads it from GUI or web s.setString(1, ""+getId()); // reads it from GUI or web int result = s.executeUpdate(); } catch (SQLException e) { System.err.println(e); } finally { try{ if(s !=null) s.close(); if(con != null) con.close(); } catch(Exception e1) { System.err.println(e1); } } } Dr. Magdi Amer

36. Example Dr. Magdi Amer

37. From Java to DB Dr. Magdi Amer

38. Saving: Tire Dr. Magdi Amer

39. Saving : Motor Dr. Magdi Amer

40. Saving : Motor Dr. Magdi Amer

41. Saving : Car Dr. Magdi Amer

42. Saving : Car Dr. Magdi Amer

43. Example: Saving Dr. Magdi Amer

44. Loading: Motor Dr. Magdi Amer

45. Loading: Tire Dr. Magdi Amer

46. Loading: Car Dr. Magdi Amer

47. Example: Loading Dr. Magdi Amer