Exec Circle - BSC - BDM Presentation - draft3

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
• 1960s 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
• 1970s present
• Structured, non-flexible, hard to change schema
• Example IBMs IMS
• Segments of Customer, Order and Product linked by
  keys held until reorganization
• Relational
• 1980s 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
• 1990s 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
• Oracles CASE Method
• Microsofts Visio
• Express-G
• Standard for Exchange of Product Model Data
• Object Role Modeling ORM
• Microsofts 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 1960s present
• All file systems start out as flat
• Hierarchical 1970s present
• IBMs IMS is still in use
• Relational (RDBMS) 1980s 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) 1990s 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 1960s, 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 IBMs 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
4 320,000 79,996 82,000
8 640,000 159,992 164,000
16 1,280,000 319,984 328,000
32 2,560,000 639,968 656,000
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)