1 / 45

Web Databases

Web Databases. David Kim. Index. What is a Database? File System vs. DBMS Database Models Database Modeling Databases SQL DBC Choosing a database IA & DB Conclusion References. What is a Database ?. A very large, integrated collection of data

tulia
Télécharger la présentation

Web Databases

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. Web Databases David Kim David Kim

  2. Index • What is a Database? • File System vs. DBMS • Database Models • Database Modeling • Databases • SQL • DBC • Choosing a database • IA & DB • Conclusion • References David Kim

  3. What is a Database ? • A very large, integrated collection of data • A collection of information stored in a computer in a systematic way • DBMS (= Database Management System) - A software used to manage and query a database David Kim

  4. Index • What is a Database? • File System vs. DBMS • Database Models • Database Modeling • Databases • SQL • DBC • Choosing a database • IA & DB • Conclusion • References David Kim

  5. FMS vs. DBMS David Kim

  6. Index • What is a Database? • File System vs. DBMS • Database Models • Database Modeling • Databases • SQL • DBC • Choosing a database • IA & DB • Conclusion • References David Kim

  7. Database Models 1 data modelingcapacity Object-relational model (late ’90s) Object-oriented model (mid ’90s) Relational model(late ’80s) Hybrid model(mid ’80s) File System(before ’70s) data managementfacilities * adapted from the material in http://www.ktdata.co.kr/ktdata/kthome7/zeus_5.htm David Kim

  8. Database Models 2 Database Model: a collection of logical constructs • flat (or table) model ☞ • hierarchical model ☞ • network model ☞ • relational model ☞ • object-oriented model ☞ • object/relational (= post-relational) model ☞ David Kim

  9. Index • What is a Database? • File System vs. DBMS • Database Models • Database Modeling • Databases • SQL • DBC • Choosing a database • IA & DB • Conclusion • References David Kim

  10. ER Model ID Name ORDER CUSTOMER Is for Order date Phone # Initiate Order type SSN Quantity Birthday has Discount Is included ITEM PRODUCT ID ID Is part of Description Description consist of Type Price DB Modeling 1 *source: www.fkii.or.kr/data/seminar/uploaded_files/ 유해진%20부장%20(액센츄어).ppt David Kim

  11. DB Modeling 2 Relational Model ORDER CUSTOMER ORDER_ITEM ID Order_date (M) Order_type (M) SSN Name (M) Phone # Birthday Order_ID Item_ID Quantity (M) Discount ITEM PRODUCT ID Description (M) Price (M) PRT_ID ID Description (M) Type *source: www.fkii.or.kr/data/seminar/uploaded_files/ 유해진%20부장%20(액센츄어).ppt David Kim

  12. Supplier_ID Supplier_Name Normalization 1 • 1st Normal Form : (No Repeating Groups) • 2nd Normal Form : ( Non-key Attribute depend on the whole primary key) Customer_ID Child Customer_ID Children1 Comment1 …. ChildrenX CommentX Comment Supplier_ID Part_No City_Name Supplier_ID Part_No City_Name Supplier_Name Part_color Contact_Name Contact_Phone Part_Price Contact_Name Contact_Phone Part_No Part_Color Part_Price *adpated from www.fkii.or.kr/data/seminar/uploaded_files/ 유해진%20부장%20(액센츄어).ppt David Kim

  13. Normalization 2 • 3rd Normal Form : (Non-Key Attributes depend only and fully on primary key) • Boyce-Codd Normal Form(BCNF) • 4NF • 5NF Part_No Part_No Supplier_Name Contact_Name Supplier_Name Part_color Contact_Name Contact_Phone Part_Price Part_Color Part_Price Supplier_Name Contact_Name Contact_Phone *adpated from www.fkii.or.kr/data/seminar/uploaded_files/ 유해진%20부장%20(액센츄어).ppt David Kim

  14. Index • What is a Database? • File System vs. DBMS • Database Models • Database Modeling • Databases • SQL • DBC • Choosing a database • IA & DB • Conclusion • References David Kim

  15. Databases • Open source Databases • MySQL • PostgreSQL Commercial Databases • MS SQL Server • ORACLE • DB2 • Informix David Kim

  16. Index • What is a Database? • File System vs. DBMS • Database Models • Database Modeling • Databases • SQL • DBC • Choosing a database • IA & DB • Conclusion • References David Kim

  17. SQL • Structured Query Language: an ANSI standard computer language for accessing and manipulating databases • ANSI SQL: SQL Standard (SQL-92) ☞ • MySQL: MySQL Server • Jet SQL: MS Access ☞ • T-SQL (Transact-SQL) : SQL Server ☞ • SQL*Plus, PL/SQL (Procedural Language/SQL) : Oracle ☞ David Kim

  18. Index • What is a Database? • File System vs. DBMS • Database Models • Database Modeling • Databases • SQL • DBC • Choosing a Database • IA & DB • Conclusion • References David Kim

  19. Database Providers • ODBC (Open Database Connectivity) ☞ • OLEDB(Object Linking and Embedding DB) ☞ • ADO.NET (ActiveX Data Objects for .NET) ☞ • JDBC (Java Database Connectivity) ☞ Data Consumer Data Provider Data Store SQL Server ASP.NET ADO.NET David Kim

  20. Index • What is a Database? • File System vs. DBMS • Database Models • Database Modeling • Databases • SQL • DBC • Choosing a Database • IA & DB • Conclusion • References David Kim

  21. Choosing a Database 1 • Desktop or Server Database • OS Environment • Size of the project • Flexibility • Portability • Cost/Budget What else? David Kim

  22. Choosing a Database 2 * source : www.sraapowergres.com/en/ whitepapers/postgres%20vs%20mysql.pdf David Kim

  23. Index • What is a Database? • File System vs. DBMS • Database Models • Database Modeling • Databases • SQL • DBC • Choosing a database • IA & DB • Conclusion • References David Kim

  24. IA & DB Database Modeler - Coder - Information Architect • IA on the whole project • Front-end UI based on the DB Modeling • Your opinion… David Kim

  25. Index • What is a Database? • File System vs. DBMS • Database Models • Database Modeling • Databases • SQL • DBC • Choosing a database • IA & DB • Conclusion • References David Kim

  26. Conclusion DBMS is now essential: “Information Architects should know about it!” David Kim

  27. Index • What is a Database? • File System vs. DBMS • Database Models • Database Modeling • Databases • SQL • DBC • Choosing a database • IA & DB • Conclusion • References David Kim

  28. References • Database Models: Hierarcical, Network, Relational, Object-Oriented, Semistructured, Associative and Context. • Database Models • Normalization • eXtropia | Tutorials | Introduction to Databases for the Web | Database Models • Data Modeling: Overview of the Relational Model • DBMS - April 1998 - Modeling Object/Relational Databases • Object-Relational Model • Object-relational database articles and products • Java Database • Overview of ADO.NET • Welcome to FKII • PowerGres • ZEUS/UniSQL - Korea Telecom Data,Inc David Kim

  29. Appendix David Kim

  30. Flat Model • Spreadsheet (e.g. Excel) ☝ David Kim

  31. Hierarchical Model ☝ A B C D E F C1 C2 F1 F2 D1 David Kim

  32. Network Model ☝ Company Customer 1 1 1 M M M Product Payment Invoice 1 1 M M InvoiceLine David Kim

  33. Relational Model 1 David Kim

  34. Relational Model 2 ☝ David Kim

  35. Object-Oriented Model Class Department Student studentsInDept() profInThisDept() printTranscript() studentsInClass() inherit Grad Student ☝ David Kim

  36. Object/Relational Model Relational Model Object-oriented Model Object-relational Model ☝ * material from: http://www.ktdata.co.kr/ktdata/kthome7/zeus_5.htm David Kim

  37. ANSI SQL SQL Data Manipulation Language (DML) • SELECT - extracts data from a database table • UPDATE - updates data in a database table • DELETE - deletes data from a database table • INSERT INTO - inserts new data into a database table SQL Data Definition Language (DDL) • CREATE TABLE - creates a new database table • ALTER TABLE - alters (changes) a database table • DROP TABLE - deletes a database table • CREATE INDEX - creates an index (search key) • DROP INDEX - deletes an index ☝ David Kim

  38. Jet SQL IIF(InStr(si.GEOG_UNIT,',')<>0,MID([si.GEOG_UNIT],1,InStr([si.GEOG_UNIT],',')-1)) $query0 .= ($state && $state != 'state_undet') ? "AND ((si.GEOG_RANK='county' AND IIF(InStr(si.GEOG_UNIT,',')<>0,MID([si.GEOG_UNIT],1,InStr([si.GEOG_UNIT],',')-1)) $sql_op '$state') OR (si.GEOG_RANK='province' AND IIF(InStr(si.GEOG_UNIT,',')<>0,MID([si.GEOG_UNIT],InStr([si.GEOG_UNIT],',')+1)) $sql_op ' $state') OR (si.GEOG_RANK='province' AND IIF(InStr(si.GEOG_UNIT,',')=0,MID([si.GEOG_UNIT],InStr([si.GEOG_UNIT],',')+1)) $sql_op '$state'))":""; ☝ David Kim

  39. Stored Procedure CREATE PROCEDURE prGet_CustomerRowSet @sCustomerID NCHAR(5) AS SELECT CompanyName, ContactName, City FROM Customers WHERE CustomerID = @sCustomerID GO ☝ David Kim

  40. PL/SQL DECLARE qty_on_hand NUMBER(5); BEGIN SELECT quantity INTO qty_on_hand FROM inventory WHERE product = 'TENNIS RACKET' FOR UPDATE OF quantity; IF qty_on_hand > 0 THEN -- check quantity UPDATE inventory SET quantity = quantity - 1 WHERE product = 'TENNIS RACKET'; ELSE INSERT INTO purchase_record VALUES ('Out of tennis rackets', SYSDATE); END IF; COMMIT; END; ☝ David Kim

  41. ADO <%set conn=Server.CreateObject("ADODB.Connection")conn.Provider="Microsoft.Jet.OLEDB.4.0"conn.Open(Server.Mappath("/db/northwind.mdb"))set rs = Server.CreateObject("ADODB.recordset")rs.Open "Select * from Customers", conndo until rs.EOF    for each x in rs.Fields       Response.Write(x.name)       Response.Write(" = ")       Response.Write(x.value & "<br />")     next    Response.Write("<br />")    rs.MoveNextlooprs.closeconn.close%> ☝ David Kim

  42. ADO.NET • Visual Basic Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;" & _ "Integrated Security=SSPI;Initial Catalog=northwind") • C# SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind"); ☝ David Kim

  43. ODBC • VB Dim nwindConn As OdbcConnection = New OdbcConnection("Driver={SQL Server};Server=localhost;" & _ "Trusted_Connection=yes;Database=northwind") • C# OdbcConnection nwindConn = new OdbcConnection("Driver={SQL Server};Server=localhost;" + "Trusted_Connection=yes;Database=northwind"); ☝ David Kim

  44. OLEDB • VB Dim nwindConn As OleDbConnection = New OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;" & _ "Integrated Security=SSPI;Initial Catalog=northwind") • C# OleDbConnection nwindConn = new OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind"); ☝ David Kim

  45. JDBC Class.forName("oracle.jdbc.driver.OracleDriver") Connection con = DriverManager.getConnection( "jdbc:oracle:thin:@dbaprod1:1521:SHR1_PRD", username, passwd); ☝ David Kim

More Related