Databases Unplugged Industry Consolidation & Evolution Cheryl Stepney Microsoft Corporation

1. Databases Unplugged Industry Consolidation & Evolution Cheryl Stepney Microsoft Corporation

2. Agenda • Core Components • Database Models • Modeling the Database • Job Roles & Opportunities • Database Vendors • Industry Convergence - XML

3. What is a Database • An organized set of data • Have discrete fields with datatype definitions • Ensure data accuracy via validation rules • Be able to easily query the data using the definitions • A core component of every computer application in the world today

4. Database Types • Flat Files • 1960’s – present • Comma/tab delimited, no structure? • Order No., Customer Name, Customer Location, Product A name, Product A price, Product B name, Order Total, end record • Hierarchical • 1970’s – present • Structured, non-flexible, hard to change schema • Example: IBM’s IMS • Segments of Customer, Order and Product linked by keys held until reorganization • Relational • 1980’s – present • Flexible, links based on data values, primary and foreign keys • Tables are linked by the existence of data in a row • Separate Tables: Order, Customer, Location, Product, Order Line Detail, Contact at Location • Object Oriented • 1990’s – present • Subject oriented, slow to gain adoption, slow performance • Objects: Order and Customer

5. Database Terminology • Logical Design • Entities – things about which information needs to be known or held • Relationships – Connectors between appropriate data • Physical Design – User View • Tables - Columns and Rows • Keys - Primary and Foreign • Tables are linked by Keys • Major Core • Data Model • Structured Query Language (SQL) • Computational Model • Query Optimizer • Extensible Markup Language (XML)

6. Database Components • Major Core Functionality • Data Model • Structured Query Language (SQL) • Computational Model • Query Optimizer • Extensible Markup Language (XML) • Security Module • Components • Tables • Constraints – eg. Zip code must be 5 integers, mandatory • Defaults – eg. blank or null on Middle Initial • Indexes – Table and View • User-defined data types • Keys • Views • User-defined functions • Triggers • Stored procedures

7. Database Career Roles • Database Designer • Data Architect • Database Modeler • DBA – Database Administrator • Intended responsibilities • Current Role Definition • Business User • Business Intelligence • Data Analyst

8. System Development LifecycleWhere Data and Code Interact • Strategic Analysis • Data Model • Functional Decomposition • Detailed Analysis • Design • Code • Test • Production

9. Cost of Making A Change • Strategic Analysis $ 1 x n • Data Model • Functional Decomposition • Detailed Analysis $ 5 x n • Design $ 50 x n • Code $ 100 x n • Test $ 500 x n • Production $1000 x n

10. Data Modeling • HIPO Charts • IBM – Hierarchical Input/Output Diagrams • Gane-Sarson – DFD (Data Flow Diagram) • Entity / Relationship Modeling • IDEFIX Standards • System Architect • Oracle’s CASE Method • Microsoft’s Visio • Express-G • Standard for Exchange of Product Model Data • Object Role Modeling – ORM • Microsoft’s Visio

11. Relational ModelWhat is 1st, 2nd, 3rd Normal Form?What is Normalization? Order Product • Remove repeating Groups • Remove dependencies • Cater for Time Customer

12. Normalization • First normal form (1NF) • It contains two-dimensional tables with rows and columns. • Each column corresponds to a sub-object or an attribute of the object represented by the entire table • Each row represents a unique instance of that sub-object or attribute and must be different in some way from any other row (that is, no duplicate rows are possible). • All entries in any column must be of the same kind • Second normal form (2NF) • Each column in a table that is not a determiner of the contents of another column must itself be a function of the other columns in the table • For example, in a table with three columns containing customer ID, product sold, and price of the product when sold, the price would be a function of the customer ID (entitled to a discount) and the specific product • Third normal form (3NF) • For example, using the customer table just cited, removing a row describing a customer purchase (because of a return perhaps) will also remove the fact that the product has a certain price • In the third normal form, these tables would be divided into two tables so that product pricing would be tracked separately. • Domain/key normal form (0NF) • A key uniquely identifies each row in a table. A domain is the set of permissible values for an attribute. By enforcing key and domain restrictions, the database is assured of being freed from modification anomalies

13. Relational Model Example supplied via for Order Detail Product Order on place by of responsible for Contact Customer subject of with Product Type employer of

14. Object Oriented Modeling • Shapes: • Objects • Entity • Value • Constraints • Connectors • Mandatory • Uniqueness • Predicates • Roles entity value

15. Database Vendors • Flat Files 1960’s – present • All file systems start out as flat • Hierarchical 1970’s – present • IBM’s IMS is still in use • Relational (RDBMS) 1980’s – present • IBM – DB/2, UDB (Universal Database), Informix • Oracle – Oracle 7.x • Microsoft – SQL Server 2000 • Sybase – Dynamic SQL • Computer Associates – OpenIngres • Object Oriented (ODBMS) 1990’s – present • Computer Associates - Jasmine • Gemstone • O2 • Object Store • Objectivity • Versant ODBMS • IBM – Informix Illustra

16. Former Relational Database Vendors • Ingres • Informix • Unify • Cullinet • Dec Digital – RDB • Verity • Natural Language

17. Relational Market Share • Gartner perspective 2002 • Based on Revenue Units • IBM 31% 20% • DB/2, UDB (Universal Database) • Acquired Informix to gain lead • Oracle 30% 25% • Oracle 7.x • Microsoft 29% 50% • SQL Server 2000 • Sybase • Dynamic SQL 5% 3% • Computer Associates • OpenIngres 5% 2%

18. Relational Database Vendors • IBM • IMS – 1960’s, transactional, still in use • DB/2 – implemented around 1990 • Data Management • On Version 8.1 • Initially, Mainframe based • AS/400 – UDB • DB2 for Linux • Different code one each platform • Market share: 31%

19. Relational Database Vendors • Oracle • IPO 1986, founded in 1977 • Project Oracle to get funding (CIA) • Implemented IBM’s “System R” Paper • Core to their business applications • Multi-Platform is business goal • Unix, Linux, Mainframe, Windows, etc. • Oracle 9i • Market share: 30% • Several product offerings to buy • SQL Plus, Report Writer, Discoverer, Oracle Developer Suite • Applications • Oracle Financials - Oracle 11i • Oracle Collaboration Suite • E-Business Suite

20. Microsoft SQL Server History • Not a Database Company at IPO in 1986 • History • 1992: Beginning of SQL Server on Windows® • 1996: SQL Server 6.5 Ships • 1998: SQL Server 7.0 released – complete rewrite • 2000: SQL Server 2000 w. Data Warehousing • 2001: SQL Server Wins Numerous Awards • Scaleable from pocket pc to Intel Mainframe – but only on Windows • Market share: 25% based on Revenue • All components in one box for single price • Transactional, OLAP, Data Transformation • Notification Services, Reporting Services • Data Warehouse • First to support XML – no extra charge

21. Retail Price Comparison # of CPUs ORACLE Enterprise SQL Server Enterprise IBM Enterprise * Oracle - Additional for Reports, Data Warehouse, ** Microsoft - All Services in one price *** IBM - Different pricing depending on platform

22. Market Innovation • The Big 3 • Oracle Corporation • IBM • Microsoft • Transactional Databases • Data Warehouses • Data Analysis – Business Intelligence • XML support • Full Text Search

23. XML • Extensible Markup Language (XML) is a simple, very flexible text format derived from SGML (ISO 8879). • Originally designed to meet the challenges of large-scale electronic publishing • XML is also playing an increasingly important role in the exchange of a wide variety of data on the Web and elsewhere • Extensible Markup Language (XML) 1.0 (Second Edition) • W3C Recommendation 6 October 2000

24. Leading the Web to Its Full Potential... • Working Groups • XML Coordination Group • XML Core Working Group • XSL Working Group • Extensible Stylesheet Language • XML Linking Working Group • XML Query Working Group • XML Schema Working Group http://www.w3.org/

25. A SQL Query Select ACO.Name 'Owner Name', ACC.Name 'Region Name', ACB.Name 'Site Name' From AtriumComponent ACO Join AtriumComponent ACC On ACC.ContainerKey = ACO.ComponentId Join AtriumComponent ACB On ACB.ContainerKey = ACC.ComponentId Where ACO.ContainerKey = -1 Order By ACO.Name, ACC.Name, ACB.Name

26. For XML Auto Query Select ACO.Name, ACC.Name, ACB.Name From AtriumComponent ACO Join AtriumComponent ACC On ACC.ContainerKey = ACO.ComponentId Join AtriumComponent ACB On ACB.ContainerKey = ACC.ComponentId Where ACO.ContainerKey = -1 Order By ACO.Name, ACC.Name, ACB.Name For XML AUTO

27. Query Results Owner Name Region Name Site Name -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- Atrium Music Stores Eastern Region Atrium Music Store 38 Atrium Music Stores Eastern Region Atrium Music Store 39 Atrium Music Stores Eastern Region Atrium Music Store 41 Atrium Music Stores Western Region Atrium Music Store 34 Atrium Music Stores Western Region Atrium Music Store 37 Atrium Music Stores Western Region Atrium Music Store 42 Atrium Music Stores Western Region Atrium Music Store 44 (7 row(s) affected)