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.
|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|
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:
|Evening||INFORMATION ENGINEERING |
& DATABASE SYSTEMS
||June 6, 2005||6:30pm - 9:30pm||Robson Square|
||June 13, 2005||6:30pm - 9:30pm||Robson Square|
||June 20, 2005||6:30pm - 9:30pm||Robson Square|
||June 27, 2005||6:30pm - 9:30pm||Robson Square|
||July 4, '05||6:30pm - 9:30pm||Robson Square|
||July 11, '05||6:30pm - 9:30pm||Robson Square|
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
You can always search for books on Amazon and Indigo
|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|
Generalization in database design offers huge advantages to the database designer.
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.
|Business Area||Generalized Tables||Missing tables|
|People and organizations||Party
Party contact (phone/email)
Party communication event
Postal address (Place)
Party Postal Address (=Party at Place)
Suppliers & Manufactures
Product to Product associations
Product to Parts
Sales order parties
Order Item associations
Agreement to Order
Shipment to Order relationship
Item Issuance for outgoing shipments
Shipment Routing & Vehicle
|Work effort||Work requirements
Work effort generation
Work effort Associations
Work effort Party assignment
Work effort Time Tracking
Work effort rates
Fixed asset assignments
Party fixed asset assignments
Work effort Type Standards
Work effort results
Invoice Terms and status
Invoice and assorted transactions
|Accounting and Budgeting||GL Chart of accounts
Accounting transaction details
Account balances and transactions
Budget relationship to GL
Position definition (type, authorization, responsibilities, )
Position fulfillment and tracking
Position Reporting relationships
Salary determination & pay history
Benefits definition and tracking
Employee skills and qualifications
Martin fowler analyzes generalization from a different perspective.
Accountability knowledge level
Party type generalizations
|Observations & Measurements||Quantity
Dual time records
Active observation, hypothesis and projection
Process of observation
|Corporate finance||Enterprise segment
Phenomenon with range
|Inventory and Accounting||Account
Individual instance method
Posting rule execution
Sources of entries
Balance sheet and Income statement
Specialized account model
Booking entries to multiple accounts
|Planning||Proposed and implemented action
Completed and abandoned actions
Outcome and start functions
|Derived contracts||Forward contracts
Subtype State machines
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.
|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,
|Business Process Flow,
|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||
Updated: February 4, 2005. email Brian Mullen with your questions