|
|
Database Administration |
|
My database training was done on Oracle. Unfortunately I have not had the opportunity to work with Oracle. The training I received during my Oracle classes has been invaluable to me professionally. Databases can still be created using flat files. A flat file database is only a list of files containing tables which contain records. An example of this would be a small company keeping all its information strictly in Excel Workbooks. Flat file databases are only feasible for small offices. All modern databases are Relational Databases using the acronym RDB. These databases have a common methodology behind them. RDB software enforces the information going into the database to have referential integrity. For example, an RDB has a table called EMPLOYEE that has a primary key (key must be unique in the table) of EMPLOYEE_ID. The RDB will not allow a record entered for an employee without an unique EMPLOYEE_ID. Also this database has another table called EMPLOYEE_PICS with a foreign key referencing the EMPLOYEE tables EMPLOYEE_ID column. No records can be added to the EMPLOYEE_PICS table without an EMPLOYEE_ID in the EMPLOYEE table. Using SQL (Structured Query Language) on RBD's you can quickly retrieve accurate information. The old standard "Who Sold What to Who and Where". RDB's require regular scheduled maintenance performed through their software. Usually this is index rebuilds, table records reorganization and tables optimization. Below is a list of Enterprise database software packages where I have been the DBA:
|
||
Microsoft SQL Server 2008 Express |
||
For proof of concept you can use free SQL Server 2008 Express. The Express edition is free but has a few limitations. Some of the limitations that you need to consider are:
When it's time to go into production with your database upgrade to Microsoft SQL Server STD and purchase required licensing. I don't recall all the particulars around this procedure but I don't remember it being too difficult. Just a quick note on licensing and pricing. In my opinion Microsoft SQL Server STD is an excellent product with good pricing and licensing schemes. |
||
SQL Server Script Examples |
||
These stored procedures were written to provide a dashboard for executives. Syteline has excellent reporting capabilities but all databases require custom reporting. Recently IT has taken to the term "Business Intelligence". I guess this makes it sound new and exciting somehow. The store procedures pull the required accounting information for invoices, customer orders, aged accounts receivables and aged accounts payables on a schedule into tables in the database. Then I wrote a Microsoft Excel add-in that selects the raw data from these Syteline tables and displays them in Excel worksheets. This process drastically increases the reporting speed. Then the Excel add-in fires an VBA Excel macro provided and written by accounting professionals that summarises the data into readable and charted details for the executives. MS SQL Stored Procedures Excel Addin Executive Dashboard:
MS SQL Stored Procedures for Reporting, EDI and Outlook:
MS SQL Stored Procedures for Excel Addin to assist BOM creation:
MS SQL Functions: |
||
Syteline Indented BOM reporting |
||
Syteline 8.03 comes with a good indented BOM report. What it's lacking is a summary of parts from all the assemblies. For instance say you have a left and right side assemblies of a machine that are mirror images of each other and have different part numbers. The Syteline report will produce many separate lines for the same parts that are in the left and right assemblies. What my augmented indented BOM report does is drill down into the assemblies and tally up all the same parts together into one Excel line per part. That way production can produce the parts in one quick batch per part number. This has the side effect of making time and attendance taking easer. The recursive nature of this process intrigued me. I rarely get to make complex recursive function calls. |
||
IBM DB2 Express - C Edition |
||
This is a nice free database. It is the complete IBM DB2 Express Edition only with more restrictions. Some of the restrictions are:
The nice thing about the way IBM organized their database software is that if you develop on this database it will be compatible for most if not all of their other higher end database software packages. From my experience if you have over 20 users on a DB2 Express - C server the databases server resource restrictions become a problem. I recommend purchasing the yearly subscription for 3 to 4 thousand and run the full version of DB2 Express Edition using 4 CPU cores and 4 GB of memory. Upgrading your server to a 64 bit Operation System is best in this scenario. If the DB2 database you are running doesn't take advantage of server side processing like Stored Procedure and Functions then any amount of server upgrades really isn't going to make a huge difference. |
||
IBM Data Studio |
||
This IBM database development software is excellent. It made writing DB2 SQL queries and stored procedures for DB2 much easier. Below are some SQL stored procedures and SQL functions I wrote for reporting and data migration of the Quantus ERP Database. DB2 SQL Stored Procedures:
DB2 SQL Functions: |
||
The best way to contact Chad is by email at info@protechs-online.com. |
||
