1 WEB DATABASE BY SEONGGYU KIM
2 Web Database Web (SQL) Database is a web page API for storing data in databases that can be queried using a variant of SQL W3C Web Applications Working Group ceased working on the specification due to the lack of independent implementations XML database a data persistence software system that allows data to be stored in XML format, and these data can then be queried
3 Objectives of Database SystemsData Sharing Controlled Amount of Data Redundancy Data Independence Represent Objects & Relationships Maintain Data Integrity - Consistency Efficient Physical Database Design Security
4 Data Abstraction Logical Data Independence Physical Data Independence
5 Database System
6 Data Models Hierarchical Data Model Network Data Modela collection of conceptual tools for describing data, relationships among data, integrity constraints Hierarchical Data Model Network Data Model Relational Data Model Object-oriented Data Model Deductive & Object-oriented Data Model
7 E-R Diagram
8 Relational Database
9 Network Database
10 Hierarchical Database
11 Deductive and Object-oriented Databaselowery[name → “Lowery”, street → Maple:Evanston, deposit → acc1[number → 900, amount → 55]] shiver[name → “Shiver”, street → North:Bronx, deposit → {acc2, acc3}] hodges[name → “Hodges”, street → Sidehill:Brooklyn, deposit → {acc3, acc4}] acc2:account[number → 556, amount → ] acc3[number → 647, amount → ] acc4[number → 801, amount → 10533]
12 Differences among the Modelsrelationship - pointer, values predefined relationship object identifier type hierarchy deduction
13 A Classification of Database Programming Languages
14 Database Design Proper structure of tablesProper relationships among tables Appropriate data constraints Database design from existing data analyze data tables, extract data from them, and apply normalization Database design from New system development create a data model from users’ requirements and transform it Database design from redesign migrate database or integrate two or more databases to newer databases
15 Phases of Database System Design
16 Entity Relationship ModelEntity - an existing object, distinguishable from other objects represented by a set of attributes Relationship - an association among entities
17 ER Diagram 1
18 ER Diagram 2 Weak Entity
19 The Jefferson Dance ClubCharges $45 / hour per Student (Couple) for a Private Lesson $6 / hour per Student of a Group Lesson Offers Private Lesson from noon to 10 pm, six days / week Group Lesson in the evenings Employs Full-time instructor paid a fixed amount / week Part-time instructor paid a fixed amount / hour Requirements - keep track of students and the classes types and times of classes each instructor has
20 ER Diagram 3
21 ER Diagram 4
22 Relationships among RelationshipsRedundant
23 Aggregation
24 Normalization Good Database Design using Functional DependenciesSide Effects
25 2nd Normal Form Key ® Nonkey attributes (SID, Activity) → FeeSA(SID, Activity) SID Activity 100 Skiing 150 Squash Swimming 175 200 ACF(SID, Activity, Fee) SID Activity Fee 100 Skiing 200 150 Squash 50 Swimming 175 AF(Activity, Fee) Activity Fee Skiing 200 Swimming 50 Squash (SID, Activity) → Fee Activity → Fee
26 3rd Normal Form No Transitive Dependency SB(SID, Building)SBF(SID, Building, Fee) SID Building 100 Randolph 150 Ingersoll 200 250 Pitkin 300 SID Building Fee 100 Randolph 1200 150 Ingersoll 1100 200 250 Pitkin 300 ST(Building, Fee) Building ® Fee SID ® Building ® Fee Building Fee Randolph 1200 Ingersoll 1100 Pitkin
27 Boyce-Codd Normal FormEvery Determinant is a Candidate Key SID Fname 100 Cauchy 150 Perls 200 Kim 250 300 Norman SID Major Fname 100 Math Cauchy 150 Psychology Perls 200 CS Kim 250 Economics 300 Norman Fname Major Cauchy Math Perls Psychology Kim CS Economics Norman (SID, Major) ® Fname (SID, Fname) ® Major
28 4th Normal Form BCNF & No Multivalued Dependencies SID →→ MajorActivity 100 Music Swimming CS Tennis 150 Math Jogging 200 SID MAJOR 100 Music CS 150 Math 200 SID ACTIVITY 100 Swimming Tennis 150 Jogging 200 SID →→ Major SID →→ Activity 100 Music Skiing CS
29 Lossy Join PCZ = ({Phone, Company, Zip},Phone Company, Zip Company) COMPANY ZIP Edison 60025 61136 PHONE COMPANY ZIP Edison 60025 61136 PHONE COMPANY Edison
30 Lossless Join R R1 and R2 R1 R2 is lossless if R1 R2 R1 or R1 R2 R2 R(Project, Manager, Employee#) R1(Project, Manager) R2(Employee#, Manager) Lossy R3(Manager, Project) R4(Employee#, Manager) Lossless
31 5th Normal Form Lossless Join SIN(SID, Name) SIBLDG(SID, Building)Fee 100 Jones Randolph 1200 150 Hayes Ingersoll 1100 175 Jackson 1500 200 Pitkin 1050 215 Turner SIN(SID, Name) SIBLDG(SID, Building) SIF(SID, Fee)
32 Domain/Key Normal FormAll Constraints on relations are logical Consequences of domains and keys STUDENT(SID, GradeLevel, Building, Fee) Constraints Building ® Fee SID must not begin with digit 1 Domain Definitions SID IN CDDD, where C ¹ 1 digit, D digit GradeLevel IN {‘FR’, ‘SO’, ‘JR’, ‘SN’, ‘GR’} Building IN CHAR(4) Fee IN DEC(4) Relation and Key Definitions STUDENT(SID, GradeLevel, Building) BLDG-FEE(Building, Fee)
33 Transformation of ER Models into Relational Database DesignsBinary Relationships Entity Þ Normalized Relation Weak Entity Þ ID Dependent Relation
34 Ternary Relationship
35 Binary Relationship
36 Representing Binary Relationships1:1 or 1:N Relationships EMPLOYEE(EmpNO, EmpName, Phone, Address) AUTO(LicenseNo, SerialNo, .., Year, EmpNo) EMP-JOB-EVAL(EmpNO, EmpName, ..., Grade)
37 Representing Binary RelationshipsM:N Relationships STUDENT(StudentNO, StudentName) CLASS(ClassNO, ClassName) IN(ClassNo, StudentNo)
38 Representing Binary RelationshipsWeak Entity INVOICE(InvNO, CustNo, Date, ..) LINE-ITEM(InvNO, LineNo, ItemNO, Qty, ..)
39 Representing Binary RelationshipsRecursive Relationships Customer Number Referred Customers , 400 , 700 CUSTOMER(CustNo, CustName, Phone, ReferredBy)
40 Representing Binary RelationshipsISA Relationships INST(InsNO, Name, Addr,..) FT-INST(InsNO, APPW) PT-INST(InsNO, APPH)
41 Relational Data ManipulationProcedural - how to do Relational Algebra Declarative - what to do Relational Calculus Structured Query Language Query By Example
42 DML Interfaces to DBMS Means of Forms Query LanguageApplication Program Interface - subroutine calls, data access commands in programs in Java, C, Pascal, etc. - languages provided by DBMS impedance mismatch
43 Relational Algebra Unary Operator Selection Projection RenameBinary Operator - logical domains should be the same Product Join Union Intersection Difference Division
44 SQL - 92 Data Definition Language Data Manipulation LanguageView Definition Authorization Integrity Transaction Control
45 Schema Definition in SQLCREATE TABLE RelationName ([AttributeName DataDefinition]+ [integrity constraints]*) CREATE TABLE account (account_number char(10) not null, branch_name char(15), balance integer, primary key (account_number), check (balance >= 0))
46 SQL - Query SELECT [distinct] attributes [Aggregate functions*]FROM relations [WHERE conditions [in (SQL)] [EXISTS / NOT EXISTS (SQL)]] [ORDER / GROUP BY attributes] [HAVING Aggregate functions*] Aggregate functions - avg, min, max, sum, count
47 Example Tables Student Enrollment Class SID Name Major Grade Level Age100 JONES History GR 21 150 PARKS Accounting SO 19 200 BAKER Math 50 250 GLASS SN 300 41 350 RUSSEL JR 20 400 RYE FR 18 450 24 Enrollment Student Number Class Name Position No 100 BD445 1 150 BA200 200 2 CS250 300 CS150 400 BF410 450 3 Class Name Time Room BA200 M-F9 SC110 BD445 MWF3 SC213 BF410 MWF8 CS150 EA304 CS250 MWF12 EB210
48 SQL - Example 1 SELECT Major FROM STUDENTSELECT SID, Name, Major, GradeLevel, Age FROM STUDENT WHERE Major = ‘Math’ WHERE Major IN (‘Math’, ‘Accounting’)
49 SQL - Example 2 SELECT Name, Major, Age FROM STUDENTWHERE GradeLevel IN (‘GR’, ‘SO’, ‘SN’) ORDER BY Name ASC, Age DEC SELECT COUNT(DISTINCT Major) FROM STUDENT
50 SQL - Example 3 SELECT Major, COUNT(*) FROM STUDENT GROUP BY MajorHAVING COUNT(*) > 2 SELECT Major, AVG(Age) FROM STUDENT WHERE GradeLevel = ‘SN’ GROUP BY Major HAVING COUNT(*) > 1
51 SQL - Example 4 SELECT DISTINCT ClassName FROM ENROLLMENTWHERE StudentNumber IN (SELECT SID FROM STUDENT WHERE GradeLevel NOT = ‘GR’) SELECT DISTINCT ENROLLMENT.ClassName FROM ENROLLMENT, STUDENT WHERE ENROLLMENT.StudentNumber = STUDENT.SID AND STUDENT.GradeLevel NOT = ‘GR’
52 SQL - Example 5 SELECT DISTINCT StudentNumber FROM ENROLLMENT AWHERE EXISTS (SELECT * FROM ENROLLMENT B WHERE A.StudentNumber = B.StudentNumber AND A.ClassName NOT = B.ClassName)
53 SQL - insert, delete, updateINSERT INTO relation [(attributes)] VALUES [(values) | (SQL)] DELETE relation WHERE condition [IN (SQL)] UPDATE relation SET new value into attribute WHERE condition
54 SQL - Example 6 INSERT INTO ENROLLMENT VALUES (400, ‘BD445’, 44)DELETE ENROLLMENT WHERE ENROLLMENT.StudentNumber IN (SELECT STUDENT.SID FROM STUDENT WHERE STUDENT.Major = ‘Accounting’) UPDATE CLASS SET ClassName = ‘BD564’ WHERE ClassName = ‘BD445’
55 Referential IntegrityCREATE TABLE customer (customer_name char(20) not null, customer_phone char(12), primary key (customer_name)) CREATE TABLE account (account_no char(10) not null, balance integer, primary key (account_no) check (balance >= 0)) CREATE TABLE depositor (customer_name char(20) not null, account_no char(10) not null, primary key (customer_name, account_no), foreign key (customer_name) references customer, foreign key (account_no) references account) on delete / update relation
56 Web Interface to DatabaseNetwork Database Applications Database applications on Internet and Intranet Static Report Publishing DB Query Publishing Private Network Public Network via Firewall Web (SQL) Database
57 Database Access StandardsThree-tier architecture Standard & DBMS-independent Interfaces for accessing database server Browser DB Web Server Database Server Browser ODBC ADO (Active Data Object) OLE/DB JDBC Native Calls HTML DHTML XML
58 Role of ADO Browser Web Server ODBC DB Server RDB OLE DB ADO NRDB FileNative Interface ODBC DB Server NRDB File Multimedia Data RDB OLE DB ADO Enables programmer in almost any languages to be able to access OLD DB Works in conjunction with Remote Data Services objects
59 JDBC(Java Database Connectivity)API for JAVA interface to Database Basic Order Connection Type 1 : JDBC-ODBC bridge driver Type 2 : Native-API partly-java driver Type 3 : JDBC-Net pure java driver Type 4 : Native-protocol pure java driver Transaction processing Disconnection
60 Database Connection //======= declaration begin===========//static Connection conn = null; static String driver = "oracle.jdbc.driver.OracleDriver"; static String url = static String username = "csdb40"; static String passwd = "csdb40"; //========== declaration end ============// // db connection try { Class.forName(driver); conn = DriverManager.getConnection(url, username, passwd); } catch (ClassNotFoundException e) { System.out.println(e.getMessage()); } catch (SQLException se) { System.out.println(se.getMessage()); } Driver registration For JDBC-ODBC Bridge Connection con = DriverManager.getConnection("jdbc:odbc:DSName","user","pw")
61 Transaction ProcessingStatement Query Types of Statement Statement (default) Statement st=con.createStatement(); ResultSet rs=st.executeQuery("select * from customer"); PreparedStatement – Parameter (“?”) PreparedStatement ps=con.prepareStatement( "update customer set c_addr=? where c_name=?); ps.setString(1,“경기도 안양시"); ps.setString(2, “홍길동"); ps.executeUpdate(); CallableStatement – calls Function, Procedure, Package
62 Query Execution Query execution ResultSet class next() Return valueexecuteUpdate Insert, Delete, Update, Drop, Create, Alter executeQuery Select execute when Type is ambiguous If return is true ResultSet, otherwise update st.getUpdateCound() st.getResultSet() ResultSet class Receives the result from the executeQuery of SQL Statement next() If ResultSet is not empty, move to the next row Return value True if next row is available, False otherwise ResultSet rs; Statement st; rs = st.executeQuery (“select * from customer”); while(rs.next()){ … }
63 JDBC 2.0 methods ResultSet insert, update, delete in ResultSetrs.next(), rs.previous(), rs.first(), rs.last() insert, update, delete in ResultSet rs.insertRow(), rs.updateRow, rs.deleteRow() Close() method shall deallocate the objects generated by the driver close() classes ResultSet, Statement, Connection format rs.close(); st.close(); con.close();
64 JDBC Example createTable.java import java.sql.*;public class createTable { public static void main(String[] argv) Connection conn; Statement stmt; try{ Class.forName("oracle.jdbc.driver.OracleDriver"); conn = DriverManager.getConnection "csdb40", "csdb40"); stmt = conn.createStatement(); stmt.executeUpdate( "CREATE TABLE createTest(" + "name VARCHAR2(10), " + "tel VARCHAR2(15))" ); System.out.println("Table Created!!!"); stmt.close(); conn.close(); }catch(Exception e) {e.printStackTrace();} }
65 Markup Languages SGML HTML D(Dynamic)HTMLSpecify the appearance and behavior of Web pages SGML HTML No way to specify contents from layout & format Lack of style definitions No way to access web page elements with scripts No construct to facilitate caching & data manipulation on clients D(Dynamic)HTML Microsoft implementation of HTML 4.0 Document Object Model(DOM) enables contents to be altered without refreshing them from server Cascading Style Sheet Remote Data Service(RD), ActiveX objects for exchanging data with server
66 XML (eXtensible Markup Language)Clear separation of contents, layout and materialization Standards, but extensible by developer Standard means for expressing the structure of database views using DTD or XML Schema No built-in compression scheme A Standardized facility to describe, validate and materialize any database view
67 XML Satellite TechnologiesCore syntax XML Applications - XHTML, MathML Document Modeling - DTD, XML Schema Style & Transformation – CSS, XLS, XSLT, XSL-FO Data Addressing & Querying - XPath, XPointer, XQL Programming and infrastructure - DOM(Document Object Model) - SAX(Simple API for XML)
68 A Goofy XML Example
69 HTML
DB Database Processing David M. Kroenke SCIENCE Blink Malcolm Gladwell
70 XML Example - book.xml
71 XSL Example – book.xsl
72 XML in detail A set of rules for building markup languagesA markup language is a set of symbols that can be placed in the text of a document to demarcate and label the parts of that document Markup can be processed by computer programs
73 XML Document with a prolog and a root element XML” ]>
74 XML Declaration VersionEncoding – euc-kr, utf-8 Standalone - no if there are external entities, i.e., files to load, yes otherwise
75 Document Type Declarationspecify the name of the root element the DTD to use for validating the document various parameters such as entity declarations to be used to augment or redefine the external declaration PUBLIC “-//ORA/DTD DBLITE XML//EN” SYSTEM “/usr/local/prod/dtds/dblite.dtd” [ XML” ]>
76 Entities Placeholders for content, variables [ "> ]>
77 Elements Parts of a documentContainers with a mixture of text and other elements
78 Attributes convey more information about an elementcan be used to give the element a unique label so it can be easily located can describe a property about the element, such as the location of a file at the end of a link can be used to describe some aspect of the element's behavior or to create a subtype Attribute values can be constrained to certain types: ID & IDREF A DTD can restrict attributes is by creating an allowed set of values
79 Well Formed XML DocumentsXML documents must have a root element XML elements must have a closing tag XML tags are case sensitive XML elements must be properly nested XML attribute values must be quoted Valid XML Documents a "Well Formed" XML document, which also conforms to the rules of a Document Type Definition (DTD)
80 Well formed Document some minimal rules for XML parsers to protect themselves Good Bad
A good nesting example. This is a poor nesting scheme.
81 DTD (Document Type Definition)Consists of Elements, Attributes, Entities, Notations, CDATA Declares a set of allowed elements Defines a content model 1:n :1 Declares a set of allowed attributes for each element id ID #REQUIRED security (high | low) “high” keywords NMTOKENS #IMPLIED > Provides a variety of ways to manage the model easier
82 DTD Declaration define the legal building blocks of an XML document < !ELEMENT ARTICLE (HEADLINE,BYLINE,LEAD,BODY,NOTES)> < !ELEMENT HEADLINE (#PCDATA)> < !ELEMENT BYLINE (#PCDATA)> < !ELEMENT LEAD (#PCDATA)> < !ELEMENT BODY (#PCDATA)> < !ELEMENT NOTES (#PCDATA)> < !ATTLIST ARTICLE AUTHOR CDATA #REQUIRED> < !ATTLIST ARTICLE EDITOR CDATA #IMPLIED> < !ATTLIST ARTICLE DATE CDATA #IMPLIED> < !ATTLIST ARTICLE EDITION CDATA #IMPLIED> ]> Nesting elements
83 Declaring Content of element typeEMPTY Refers to tags that are empty. 
, or . ANY Refers to anything at all, as long as XML rules are followed. ANY is useful to use when you have yet to decide the allowable contents of the element. children elements You can place any number of element types within another element type. These are called children elements, and the elements they are placed in are called parent elements. Mixed content Refers to a combination of (#PCDATA) and children elements. PCDATA stands for parsed character data, that is, text that is not markup. Therefore, an element that has the allowable content (#PCDATA) may not contain any children.
84 Declaring Attributes Type Description CDATAThe value is character data (en1|en2|..) The value must be one from an enumerated list ID The value is a unique id IDREF The value is the id of another element IDREFS The value is a list of other ids NMTOKEN The value is a valid XML name NMTOKENS The value is a list of valid XML names ENTITY The value is an entity ENTITIES The value is a list of entities NOTATION The value is a name of a notation xml: The value is a predefined xml value Value Explanation value The default value of the attribute #REQUIRED The attribute is required #IMPLIED The attribute is not required #FIXED value The attribute value is fixed
85 XML Elements vs. Attributes
86 Internal & External DTDInternal DTD < !DOCTYPE note [ ]> < note>
87 XML DTD The purpose of a DTD is to define the structure of an XML document. It defines the structure with a list of legal elements ]> DTD
88 XML Schema Quite a new technology XML documents themselvesDTD - a detailed way to define what the data can and cannot contain Provide a much more powerful means by which to define your XML document structure and limitations Provide an Object Oriented approach to defining the format of an XML document With core data type along with various operators and modifiers, complex type can be created
89 Create a XML Document With DTD Kim Oh Manager Personnel departmentname=“sales” employee name=“marketing” employee name title staff name title staff Kim employee employee employee employee name title name title name title name title Oh Manager
90 Relationships CREATE TABLE Invoice ( invoiceID integer PRIMARY KEY,customerID integer, orderDate datetime, shipDate datetime) CREATE TABLE LineItem ( lineItemID integer, invoiceID integer, productDescription varchar(255), quantity integer, unitPrice float, CONSTRAINT fk_LineItemInvoice FOREIGN KEY (invoiceID) REFERENCES Invoice (invoiceID)) orderDate CDATA #REQUIRED shipDate CDATA #REQUIRED> productDescription CDATA #REQUIRED quantity CDATA #REQUIRED unitPrice CDATA #REQUIRED> Use of pointers – IDREF(S)-ID attributes – have a profound impact on processing performance
91 XLS(EXtensible Stylesheet Language)CSS = Style Sheets for HTML XSL = Style Sheets for XML XSL consists of three parts: XSLT - a language for transforming XML documents XPath - a language for navigating in XML documents XSL-FO - a language for formatting XML documents XSLT = XSL Transformations Normally transforms each XML element into an (X)HTML element add/remove elements and attributes to or from the output file rearrange and sort elements, perform tests and make decisions about which elements to hide and display
92 XSL Elements
93 Linking XSL to XML < xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> < xsl:template match="/"> My CD Collection
Title Artist