Information Engineering and Database Systems
UBC Robson Square, June 6 - July 11, 2005

part of UBC Software Engineering Certificate presented by Brian Mullen, M.Sc. through the faculty of Continuing Studies at the University of British Columbia at UBC Robson Square in downtown Vancouver, British Columbia, Canada. 

This electronic brochure allows you to find detailed information for this course.

Table of contents for course
IE course outline and schedule Course Assignments Reference Books on Database Design 
Generalized Database table recommendations Zachman's Framework Database Design Web Sites
  UBC's software engineering certificate information  

Information Engineering and Database Systems

Database design provides solid foundation for information systems development. Software engineer can produce better systems with database design methods learned in this course.

Database concepts will be demonstrated using Microsoft Access. This course is part of UBC's Software Engineering certificate.

In Information Engineering and Database Systems, participants will learn how to:

Schedule for Information Engineering and Database Systems
Evening INFORMATION ENGINEERING 
& DATABASE SYSTEMS
Start dates Duration Location
1
  • The goals of Information Engineering and database design.
  • Understand the business enterprise, business processes and transactions with Business Modeling using interaction diagrams.
  • Define Information requirements with transaction analysis and forms prototyping.
June 6, 2005 6:30pm - 9:30pm Robson Square
2
  • Catalog business objects with data repositories.
  • Hands-on creation of a relational database using Microsoft Access.
  • Case study will be a data repository with a common code table to define data components including domains.
  • Review relational database components: Tables, columns, indexes and foreign keys.
June 13, 2005 6:30pm - 9:30pm Robson Square
Lab
3
  • Determine the tables required in a database and the columns in each table using entity-relationship modeling & normalization.
  • Roles and recursive relationships.
  • Case study will be transaction analysis.
  • Increase flexibility and reduce number of tables in a database by generalizing from sub-types to super types.
  • Integrate business and data modeling with entity life-cycle modeling.
  •  
June 20, 2005 6:30pm - 9:30pm Robson Square
4
  • XML data structures and languages
  • XML for database storage
  • XML for internet transactions
  • XHTML presenting data
  • DOCBOOK for storing books
  • SVG for presenting graphics
  • SOAP
  • BIZTALK 
June 27, 2005 6:30pm - 9:30pm Robson Square
Lab 420
5
  • Simplify database design by generalizing from roles to application independent kernel entities.
  • Review financial accounting system design to application of application- independent entity generalization.
  • Case study will be a generalized name and address database using the PARTY concept.
July 4, '05 6:30pm - 9:30pm Robson Square
6
  •  
  • Managing the database environment.
  • Integrating applications with shared databases.
  • Data Administration.
  • Database administration.
  • Data Warehouses.
July 11, '05 6:30pm - 9:30pm Robson Square

Information Engineering Web Sites

Universal "People and Organization" Data Models article by Len Silverston & Kent Graziano. This article introduces the concept of the super-type Party similar to what will be talked about in session 5. Highly recommended reading.


Samples of UML (Unified Modeling Language) diagrams

Class Assignments

IE2 Repository Database June 13, 2005

  1. Create as PERSON table as outlined on page 72 of your course book.
  2. Link the OBJECT and COMMON CODE FILE tables with relationships as shown on page 68 of your book. You must choose enforce referential integrity for the header and detail portions of your form to be linked. Identify coded fields in your PERSON table.
  3. Create a code table for Gender (M=male, F=female) in Common Code file as outlined on page 73 of your book. Create a drop-down list for the Gender column in the Person table using the lookup tab similar to page 69.
  4. Add some people to the person table in Data Sheet view.
  5. Link the PERSON table to the Created By and the Updated By columns in the OBJECT table so you see a drop down list of valid people in your Object table and OBJECT forms.
  6. Generate a form with OBJECT header and COMMON CODE FILE detail information. Be sure to click-on enforce data integrity in step 2 above.
  7. Document three new things that you learned about database design from the Repository case study in your email message. Write about four sentences about each item.
  8. Rename your Access file to "IE2 firstname lastname" so I know who sent each file.
  9. Email your Access file to brianmullen@shaw.ca assignment to the Web Page.

Information Engineering Reference Books

You can always search for books on Amazon and Indigo

Book Title Author Date Contents
Business Modeling with UML Hans-Erik Eriksson, Magnus Penker 2000 Business Patterns at work
The Data Model Resource Book Len Silverston 2001 A library of Universal Data Models for all Enterprises
Analysis Patterns Martin Fowler 1997 Reusable Object Models
Data Warehouse Lifecycle Toolkit Ralph Kimball 1998 Expert Methods for Designing, Developing and Deploying Data Warehouses
Oracle Database Developer's Guide Ulka Rodgers 1999 Streamline Oracle development and supercharge you applications
XML Handbook Charles Goldfarb 2001 Applications, products, technologies and tutorials
Rapid Development Steve McConnell 1996 Taming Wild Software Schedules
 

Generalized Table Suggestions

Generalization in database design offers huge advantages to the database designer.

  1. Increases database flexibility that new requirements can be accommodated with existing tables.
  2. Reduces cost of building and maintain applications.
  3. Reduces number of tables in the database.
  4. Increases reuse and sharing of data between applications.
  5. Reduces software required to meet build an application.
  6. Reduces duplication and inconsistency of information.

The earliest database table that we generalized were the Name_And_Address database to centralize all name and address information in a single table. Any address changes were available to all applications in the system. We implemented this at the Port of Vancouver in 1977. This evolved into the Party, Place and Address database tables. The following two authors make numerous suggestions for generalizing entities in data models. The advantages of generalization is that you reduce the number of tables in your database (simplification) while at the same time increasing the flexibility of your database to handle future changes without significant changes to the data structures. For example the party database captures all the information you need about people and organizations.
Notice that even with high-level of generalization, you will still end up with 100 tables in your database provides information for a wide range of applications.

The second table that we generalized at the Port of Vancouver was the Common Code file. This provided a common repository for defined domains for coded fields. Companies that implement this approach reduce their software development and maintenance costs by thousands of dollars. Users can add and delete entries from these tables. Contents of simple drop-down lists can be populated from the tables.
As per Silverston in Universal Data Models
Business Area Generalized Tables Missing tables
People and organizations Party
Party relationship
Party contact (phone/email)
Party communication event
Postal address (Place)
Party Postal Address (=Party at Place)
Geographic boundary
Products Product definition
Product features
Suppliers & Manufactures
Inventory Items
Pricing
Costing
Product to Product associations
Product to Parts
Ordering Products Orders
Order Items
Sales order parties
Order Adjustments
Order Item associations
Requirements
Requests
Quotes
Agreement definition
Agreement Item
Agreement terms
Agreement Pricing
Agreement to Order
Shipments Shipment types
Shipment parties
Shipment detail
Shipment to Order relationship
Shipment Receipts
Item Issuance for outgoing shipments
Shipment documents
Shipment Routing & Vehicle
Work effort Work requirements
Work effort generation
Work effort Associations
Work effort Party assignment
Work effort Time Tracking
Work effort rates
Inventory assignments
Fixed asset assignments
Party fixed asset assignments
Work effort Type Standards
Work effort results
Invoicing Invoices
Invoice Items
Invoice Roles
Billing accounts
Invoice Terms and status
Invoice and assorted transactions
Accounting and Budgeting GL Chart of accounts
Accounting transactions
Accounting transaction details
Account balances and transactions
Subsidiary accounts
Asset Depreciation
Budget definition
Budget revision
Budget relationship to GL
Human Resources Employment
Position definition (type, authorization, responsibilities, )
Position fulfillment and tracking
Position Reporting relationships
Salary determination & pay history
Benefits definition and tracking
Payroll information
Employment application
Employee skills and qualifications
Employee performance
Employee termination

Martin fowler analyzes generalization from a different perspective.
As per Martin Fowler in Analysis Patterns
Business Area    
Accountability Party
Organization hierarchies
Accountability
Accountability knowledge level
Party type generalizations
Hierarchical accountability
Operating scopes
 
Observations & Measurements Quantity
Conversion Ratio
Compound Units
Measurement
Observation
Protocol
Dual time records
Rejected observations
Active observation, hypothesis and projection
Associated observation
Process of observation
 
Corporate finance Enterprise segment
Measurement protocol
Range
Phenomenon with range
 
Objects Name
Identification Scheme
Object merge
Object equivalence
 
Inventory and Accounting Account
Transactions
Summary account
Memo account
Posting Rules
Individual instance method
Posting rule execution
Choosing entries
Accounting practice
Sources of entries
Balance sheet and Income statement
Corresponding Account
Specialized account model
Booking entries to multiple accounts
 
Planning Proposed and implemented action
Completed and abandoned actions
Suspension
Plan
Protocol
Resource allocation
Outcome and start functions
 
Trading Contract
Portfolio
Quote
Scenario
 
Derived contracts Forward contracts
Options
Products
Subtype State machines
 

The Zachman Framework for Information Engineering

The rows in the Zachman Framework represent levels of detail in the successive system description.

The Zachman framework illustrates where each type of design method or representation is used.

The original Zachman Framework had four columns (Level, Data, function and Network). It was expanded later to include People, Time and Motivation.

The Motivation Column should come first in before Data in the matrix because the business motivation drives the other activities in the definition of the system.

 
The revised Zachman Framework for Information Engineering
Level of abstraction Motivation (why?) People (who?) Time (when?) Data (what?) Function (how?) Network (where?)
1. Objectives and Scope List of business goals / strategies List of organizations List of business events / cycles List of things important to the enterprise List of processes that the function performs List of operating locations
2. Business Model Business plan Organization chart with roles Master schedule, Event response matrix. Entity Relationship diagrams, 
Object diagrams
Business Process Flow,
Interaction diagram
Location, process and data matrix distribution
3. Information System Model Business rules Human interface architecture (roles, access) Dependency diagram, Entity life cycle history Data model, Entity relationship, object type model Data flow diagram, Use Cases Distributed system architecture
4. Technology Model Rule enforcement User interface (how the system will behave?) Control flow diagram Data architecture schema diagram (tables and columns) Structure Chart, Pseudo Code. System Architecture (Hardware, software tools)
5. Detailed Representation Rule servers and logic Security architecture (who can see what) Interrupts and machine cycles Data design (denormalized), Physical storage design Detailed Program design Network Architecture
6. Functioning System

Working Systems

Updated: February 4, 2005. email Brian Mullen with your questions