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.

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:

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.

You can always search for books on Amazon and Indigo

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