Transcript Proof of Design

Cincinnati Bell Telephone
INsideOUT Database Redesign
Senior Design III
Final Presentation
Vernon Chamberlain
Overview
•
•
•
•
•
•
•
•
•
Statement of Problem
Solution Description
User Profile
Timeline
Technical Elements
Budget
Deliverables
Proof of Design
Conclusion
Statement of Problem
• Support terminated for Informix database
• INsideOUT Database
– Business functions served:
•
•
•
•
•
Work Orders / Service Orders
Job Costing
Material Reporting
Time Reporting
Vendor Billing
• Applications are managing the data.
Solution Description
•
•
•
•
•
•
Structure
Naming Conventions
Security Tier
Efficient Use of Database Storage
Minimizing Redundancy
Centralizing Common Processes
User Profile
INsideOUT Database
Updates to various Work
Order information (features,
BOM, etc.)
Manage the Database
in its entirety
OPUS
Administrator
COM
(Costing
Maintenance)
ECO
(Estimated
Costing)
Costing
Maintenance Updates
Initiate, Edit and
Delete Work Orders / Schedule
Hours
Engineers Estimate Work
Orders using Costing
Information
Export BOM and CIP data /
Import Hours and Material data to
and from MyHR system
MyHR
Import / Export
Routine
Updates Vendor
Tables, Creates Invoices
Tracker
Contractor Billing
Export Material Status
data to Accounting
Department
Accounting
Export Routine
Timeline
Senior Design I Accomplishments – Fall 2007
•
•
•
Researched and Analyzed Database
Began Database Redesign
Wrote, submitted, and presented the Proposed Project
Senior Design II Accomplishments – Winter 2008
•
•
•
Created new entities
Researched and Analyzed Application Code
Wrote, submitted, and presented the Design Freeze
Senior Design III Accomplishments – Spring 2008
•
•
•
•
Created Stored Procedures, Triggers and Views
Tested the entire project
Wrote, submitted, and presented final project at Tech Expo
Present final project June 9th 2008
Technical Elements
• Hardware
– Database Server
– Portable Hard Drive
• Software
–
–
–
–
–
–
Windows Server 2003 Standard Edition
Oracle Database 10g
Toad for Oracle Freeware
PL/SQL Developer (all around automations)
Microsoft Visual Studio .NET 2005
Microsoft Visual Source Safe
Budget
Item
Description
Real Cost
My Cost
Server
Provided by CBT
$ 5,000.00
$0.00
Windows Server 2003 - Std Ed
Provided by CBT
999.00
0.00
Oracle Database - Enterprise
Provided by CBT
40,000.00
0.00
Visual Studio .NET 2005
Provided by CBT
799.00
0.00
Visual Source Safe
Provided by CBT
549.00
0.00
0.00
0.00
139.00
0.00
$47,486.00
$0.00
Toad for Oracle, Freeware
Portable Hard Drive
DBA Freeware
Mine – Store Project
TOTALS
Figure 3 Proposed Budget, Sources: (2, 6, 11, 15)
Deliverables
Structure
• New tables that adhere to industry standards
• Efficient use of database storage
Naming Convention
• Standardized Column and Entity names to create a higher degree of
manageability and consistency
Application Functionality as Stored Procedures and Triggers
• Centralization of current code in the database. This creates a single point of
accountability and simplifies updating much of the code.
• Incorporation of common processes into stored procedures, triggers and
functions using PL/SQL
Added Security Tier
• Redundant in-line SQL statements are being created as stored procedures,
functions, and triggers as deemed necessary.
• By implementing the code within the database, the overall system will have an
added tier of security.
Minimize Redundancy
• The redesign minimizes redundancy that currently occurs within the database.
Tables such as JOB and WOFRONTPAGE carry much of the same data.
Proof of Design
Structure:
Proof of Design
Naming Convention:
Summary
Application Functionality as
Procedures and Triggers
In-line SQL Statements in Visual Basic 6 Code
Summary
Application Functionality as
Procedures and Triggers
PL/SQL Code created inside the Database as a Stored Procedure
Proof of Design
Added Security Tier:
New Stored Procedure for COSTING table information
Proof of Design
Minimizing Redundancy:
Table Comparison of Same Data in Different Columns
Conclusion
• INsideOUT Database was in a state of disrepair
• INsideOUT needed normalization, stored
procedures, and triggers
• INsideOUT is now more efficiency and
manageable, and now has a higher level of
security
Questions? ?
Questions