1 / 40

Comp2513 Database and E-Commerce

Comp2513 Database and E-Commerce. Daniel L. Silver, Ph.D. Objectives. To introduce the basic concepts of database and DBMS To describe the relational database model To discuss the Stuctured Query Language To define ODBC and JDBC To outline the role that database plays in E-Commerce

yardley
Download Presentation

Comp2513 Database and E-Commerce

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Comp2513Database and E-Commerce Daniel L. Silver, Ph.D.

  2. Objectives • To introduce the basic concepts of database and DBMS • To describe the relational database model • To discuss the Stuctured Query Language • To define ODBC and JDBC • To outline the role that database plays in E-Commerce • Reference: portions of Chapter 6 Daniel L. Silver

  3. Outline • Databases and DBMS • Relation database • Structured Query Language (SQL) • ODBC and JDBC Daniel L. Silver

  4. What is a Database? • Database - A collection of data, structured in a well defined format, accessed by multiple applications using standard commands, ensuring integrity of access • A Database can contain many records and the equivalent of many files each containing many records Daniel L. Silver

  5. Database Management System • DBMS – a collection of software that facilitates and optimizes database I/O for applications • Flexible access to data - independent of physical storage • Rapid response to ad hoc queries • Access by multiple applications in various ways • Ensures data integrity • Elimination of redundant data Daniel L. Silver

  6. Relational Database • Different databases have different ways of organizing and representing data – referred to as a data model • The relational data model – data is placed in tables where rows represent records and columns represent fields • Tables have no predefined relation to one another, instead data can be dynamically related Daniel L. Silver

  7. Relation DBMS - RDBMS • Major Commercial RDBMS vendors: • IBM (DB2) • Oracle • MS (Access, SQL Server) • INGRES (RTI) • Informix • Freely Available RDBMS: • PostgreSQL • MySQL Daniel L. Silver

  8. An Example: ERD ERD = Entity Relationship Diagram Daniel L. Silver

  9. An Example: Relational Tables PROJECT EMPLOYEE • pnum pdesc psd ped • E911 Apr Nov • CAPC Aug Dec eid ename 9902 Ritter, Tex 0103 Nasium, Jim ASSIGNMENT LOCATION • aid ……………….. • apnum aeid alnum abd aed • 9902 Hfx1 May Oct • 1 0103 Hfx1 May Aug • 2 0103 Yrm2 Sep Oct lnum laddress Hfx1 1234 Barrington St. Yrm2 56 Front St. Daniel L. Silver

  10. SQL- Structured Query Language • Data within a DBMS is manipulated via a 4GL or by a specific application program using a DBMS access language • SQL is data definition and manipulation langauge for relational databases • SQL has become an international standard Daniel L. Silver

  11. SQL Basics • CREATE TABLE – creates a table and defines its fields (columns), e.g.: CREATE TABLE PROJECT (pnum integer NOT NULL. pdesc character NOT NULL. PRIMARY KEY (pnum); • ALTER TABLE – delete or add fields • DROP TABLE – delete an entire table Daniel L. Silver

  12. SQL Basics • INSERT INTO – places values into a table • UPDATE – changes values in a table • DELETE FROM – removes records in table • SELECT – columns from a table, general format: SELECT <colname>, <colname> FROM <tablename> WHERE <condition> Daniel L. Silver

  13. An Example: Relational Tables PROJECT EMPLOYEE • pnum pdesc psd ped • E911 Apr Nov • CAPC Aug Dec eid ename 9902 Ritter, Tex 0103 Nasium, Jim ASSIGNMENT LOCATION • aid ……………….. • apnum aeid alnum abd aed • 9902 Hfx1 May Oct • 1 0103 Hfx1 May Aug • 2 0103 Yrm2 Sep Oct lnum laddress Hfx1 1234 Barrington St. Yrm2 56 Front St. Daniel L. Silver

  14. SQL Select Example Find all projects in which Jim Nasium is involved … Set qename = “Nasium, Jim” SELECT pnum, pdesc FROM employee, assignment, project WHERE ename = qename AND assignment.aeid = employee.eid AND project.pnum = assignment.apnum Returns: • pnum pdesc • E911 • CAPC Daniel L. Silver

  15. Our E-Commerce Mall DBMS • We are using PostgreSQL (postgres) • Freely available off the web • What is PostgreSQL? • The PostgreSQL page. Daniel L. Silver

  16. Our E-Commerce Mall DB • Consists of 3 tables created and managed by PostgreSQL • Categories • category_id int • category_name char 50 • description text • image char 100 • parent int (null if a store) Daniel L. Silver

  17. Our E-Commerce Mall DB • Products • product_id int • product_name char 50 • sku char 50 • description text • image char 100 • price real • category int Daniel L. Silver

  18. Our E-Commerce Mall DB • Product_category • product_id int • category_id int • Used to display a product in more than one category Daniel L. Silver

  19. SQL and Our E-Commere Mall Database From index.jsp, a java bean is used to query our E-Commerce Mall DB to get the categories for a particular store: (“SELECT category_id, category_name, description, image FROM category WHERE parent = ?"); pstmt.setInt(1,getId()); Daniel L. Silver

  20. SQL and Our E-Commere Mall Database DETAILS: From index.jsp, the following use of a java bean gets the categories of a store: List categories = store.getCategories(); The getCategories method in turn uses another bean to get all categories: category.getChildren() The getChildren method makes the SQL query via a JDBC request: conn = StoreDatabase.getConnection(); pstmt = conn.prepareStatement("SELECT category_id,category_name,description,image FROM category WHERE parent = ?"); pstmt.setInt(1,getId()); rs = pstmt.executeQuery(); Daniel L. Silver

  21. SQL and Our E-Commere Mall Database From category.jsp, a java bean is used to query our E-Commerce Mall DB to get the products of a particular category: ("SELECT product_id, product_name, sku,description, image,price FROM product WHERE category = ?" + " OR EXISTS (SELECT * FROM product_category WHERE product_category.category_id = ?" + " AND product_category.product_id = product.product_id)"); pstmt.setInt(1,category.getId()); pstmt.setInt(2,category.getId()); Daniel L. Silver

  22. SQL and Our E-Commere Mall Database DETAILS: From category.jsp, a java bean is used to get the categories of a store: List products = Product.getProducts(category); The getProducts method makes the SQL query via a JDBC request: conn = StoreDatabase.getConnection(); pstmt = conn.prepareStatement("SELECT product_id,product_name,sku,description, image,price FROM product WHERE category = ?" + " OR EXISTS (SELECT * FROM product_category WHERE product_category.category_id = ?" + " AND product_category.product_id = product.product_id)"); pstmt.setInt(1,category.getId()); pstmt.setInt(2,category.getId()); rs = pstmt.executeQuery(); Daniel L. Silver

  23. ODBC • Open Database Connectivity is a widely accepted application programming interface (API) for database access developed by a consortium led by MicroSoft • ODBC is a combination of ODBC API function calls and the SQL language Daniel L. Silver

  24. ODBC • Originally, a proprietary language was used to talk to each DBMS • A program required unique code to interact with Access, DB2 and Oracle databases Access DBMS Access DB Program with 3 different sets of API calls and SQL DB2 DB DB2 DBMS Oracle DB Oracle DBMS Daniel L. Silver

  25. ODBC • ODBC abstracts away specific DBMS • The application issues ODBS API calls • ODBC Manager interfaces with the target DBMS • ODBC driver must be installed for each DBMS Program with ODBC API calls and SQL Access DBMS Access DB DB2 DB ODBC Manager DB2 DBMS Oracle DB Oracle DBMS Daniel L. Silver

  26. JDBC • Java Database Connectivity • JDBC is a trademark of SUN Microsystems • Standard API that lets you access virtually any tabular data source from Java programs • relational databases, flat files, spreadsheet files • JDBC builds on and reinforces the style and virtues of Java (easy to use) Daniel L. Silver

  27. JDBC Facilitates DB I/O (The following examples are taken from IndexServlet.java) • Connect to DB and establish a session: conn = StoreDatabase.getConnection(); … within getConnection … Connection conn = DriverManager.getConnection(“jdbc:postgresql://raven.acadiau.ca/ 2513DB”, “storexx_uid”, “storexx_pwd”); • Creates an object for issuing SQL statements (commands) to the connection: pstmt = conn.prepareStatement("SELECT category_id,category_name, description,image FROM category WHERE parent = ?"); • Two JDBC statement methods: • executeQuery() – used for issuing SELECT queries • executeUpdate() – used for issuing DB inserts, updates and deletes Daniel L. Silver

  28. JDBC Facilitates DB I/O (The following examples are taken from IndexServlet.java) • The executeQuery() method returns a ResultSet object that contains the results of the query operation on the DB: conn = StoreDatabase.getConnection(); pstmt = conn.prepareStatement("SELECT category_id,category_name, description, image FROM category WHERE parent = ?"); pstmt.setInt(1,getId()); rs = pstmt.executeQuery(); • This “rs” object can be explored row by row: while (rs.next()) { int childId = rs.getInt("category_id"); String childName = rs.getString("category_name"); String childDesc = rs.getString("description"); String childImage = rs.getString("image"); … } Daniel L. Silver

  29. JDBC Facilitates DB I/O DETAILS of getChildren: List children = new Vector(); Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; try { conn = StoreDatabase.getConnection(); pstmt = conn.prepareStatement("SELECT category_id,category_name,description,image FROM category WHERE parent = ?"); pstmt.setInt(1,getId()); rs = pstmt.executeQuery(); while (rs.next()) { int childId = rs.getInt("category_id"); String childName = rs.getString("category_name"); String childDesc = rs.getString("description"); String childImage = rs.getString("image"); Category child = new Category(childId,childName,childDesc,childImage,this); children.add(child); child.isNew = false; } … return children; Daniel L. Silver

  30. JSPs simplify JDBC (WS/DB2) • Java Server Pages can make use of pre-written code to facilitate DB access • To connect: <jsp:dbconnect id="conn" url="jdbc:db2:demomall" driver="COM.ibm.db2.jdbc.app.DB2Driver" userid="db2user" passwd="db2pass"> </jsp:dbconnect> • To execute a query: <jsp:dbquery connection="conn" id="catalog"> SELECT … </jsp:dbquery> • To get the result (in this case a category name): <jsp:getProperty name="catalog" property=“name" /> Daniel L. Silver

  31. JSPs simplify JDBC Portion of code within index.jsp that accesses the Mall DB when displaying the categories at the top of the page: <a class="catLink" href="category.jsp?id=<jsp:getProperty name="category" property="id" />" onMouseOver="hiliteCell(<%=i%>)“ onMouseOut="unhiliteCell(<%=i%>)"> <jsp:getProperty name="category" property="name" /> </a> Daniel L. Silver

  32. JSPs simplify JDBC DETAILS: <% int spaceWidth = 600 - (categories.size() * 110); int i = 0; for (Iterator it = categories.iterator(); it.hasNext();) { Category c = (Category) it.next(); pageContext.setAttribute("category",c); %> <td width="110"><table border="0" cellspacing="0" cellpadding="0" width="110"><tr><td align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"> <a class="catLink" href="category.jsp?id=<jsp:getProperty name="category" property="id" />" onMouseOver="hiliteCell(<%=i%>)" onMouseOut="unhiliteCell(<%=i%>)"><jsp:getProperty name="category" property="name" /></a> </font></td></tr></table></td> <% i++; } %> Daniel L. Silver

  33. The Role of Database in E-Commerce • Database is used within E-Commerce to provide dynamic ad hoc information on-demand to users • Store administrators use databases to set up categories and products in a secure and reliable manner • E-Commerce applications can be written with only the logical structure of data and not its physical storage • The database holds the content of a page and it also can hold the presentation of that content such that the same data can be presented in different ways to different customers Daniel L. Silver

  34. Major Architectural Components of the Web Bank Server Client 1 Dedicated Browser HTTP TCP/IP Server A HTTP Server App. Server Database Server Internet Client 2 Browser prog.class URL index.html Server B Bank Server Daniel L. Silver

  35. THE ENDdanny.silver@acadiau.ca

  36. SQL and WebSphere From index.jsp, the following gets the various category names (cgname) and reference numbers for a specified merchant (cgmenbr): SELECT cgryrel.crccgnbr, category.cgname, category.cgrfnbr FROM cgryrel, category WHERE crmenbr = <%= request.getParameter("cgmenbr") %> AND (cgryrel.crpcgnbr IS NULL) AND category.cgmenbr = <%= request.getParameter("cgmenbr") %> AND category.cgrfnbr = crccgnbr Daniel L. Silver

  37. SQL and WebSphere From index.jsp, the following gets the various category names (cgname) and reference numbers for a specified merchant (cgmenbr): SELECT cgryrel.crccgnbr, category.cgname, category.cgrfnbr FROM cgryrel, category WHERE crmenbr = <%= request.getParameter("cgmenbr") %> AND (cgryrel.crpcgnbr IS NULL) AND category.cgmenbr = <%= request.getParameter("cgmenbr") %> AND category.cgrfnbr = crccgnbr Daniel L. Silver

  38. SQL and WebSphere From product.jsp, the following gets the various product category names (cgname) for specified merchant (cgmenbr) and category reference number (cgrfnbr): SELECT category.cgname FROM category WHERE cgmenbr = <%=request.getParameter("cgmenbr") %> AND cgrfnbr = <%= request.getParameter("cgrfnbr") %> Daniel L. Silver

  39. SQL and WebSphere From productDisplay.jsp, the following gets various product values for display for specified merchant (prmenbr) and product number (prrfnbr): SELECT product.prsdesc, product.prnbr, product.prfull, product.prthmb,prodprcs.ppprc, prodprcs.ppcur, product.prldesc1,product.prldesc2, product.prldesc3, product.prwght, product.prwmeas, product.prheight,product.prlngth, product.prwidth, product.prsmeas FROM product, prodprcs WHERE ppprnbr = prrfnbr AND prmenbr = <%= request.getParameter("prmenbr") %> AND prrfnbr = <%= request.getParameter("prrfnbr") %> Daniel L. Silver

  40. JDBC Facilitates WS DB I/O (The following examples are taken from IndexServlet.java) • Connect to DB and establish a session: Connection myConnection = DriverManager.getConnection("jdbc:db2:demomall", "db2user", "db2pass"); • Create an object for issuing SQL statements (commands): Statement statement = myConnection.createStatement(); • Two JDBC statement methods: • executeQuery() – used for issuing SELECT queries • executeUpdate() – used for issuing DB inserts, updates and deletes Daniel L. Silver

More Related