Co-developed
by
Loai
Na’amani, Mazen
Manasseh, Farah Dakhil, and Abdurrahman
Khansaheb
The ‘CEE Labs DBMS’ is an attempt at helping
laboratories in the civil and environmental engineering department at AUB lever
up their administrative and data storage/manipulation tools, both of which, if
realized, would favorably reflect on their overall productivity, efficiency,
and reliability when it comes to attracting new clients and better serving old
ones. As to its scope, from the perspective of lab work, it has been designed
to accommodate all 3 main CEE labs: the soil
mechanics lab, the concrete and
materials lab, and the environmental
sciences and water quality lab; and covers/automates all administrative (client profiles,
experiment orders, invoice archives, result reports, etc…) and experimental (experiments inventory,
results calculation, storage, & retrieval, experimental data aggregation,
etc…) duties of the respective labs. Technically, however, this endeavor has
become more and more involving as its IT scope stretched to include fluency in
automating MS Access via Visual Basic for Applications (VBA), advanced querying with Structured Query Language (SQL), and a database-driven web outlet
to the ‘CEE Labs DBMS’ using Active
Server Pages (ASP) and various web
scripting technologies.
Below
is a schematic of the DBMS components and connectivity (currently up & running
at the AUB servers):
The online window to the ‘CEE Labs
DBMS’ is meant to facilitate
both: (1) order placement online, invoice calculation, and price listing for
registered clients, and (2) the luring of new local or regional clients to a
laboratory that has a presence on the world wide web, and uses a state-of-the
art DBMS to organize/sore its data.
For security reasons, it was agreed upon that
updating the database from the web should be minimized, and that no access
should be given to the client over experiment results and/or dates in which
they were completed. However, the pages have been designed to support such
functionality, yet still be configurable from the database to enable/disable
it. This brings us to the most important feature of the ‘CEE Labs’ website, and it’s the fact that it’s totally ‘database-generated’ or ‘database-driven’, form and content
wise. That is, all aspects are parameterized and query-based, inheriting their
value from the database every time a page is requested from the server. For
instance, the backgrounds, banners, and bullets can all be configured from
within the database by choosing another picture file for each. All order forms,
category dropdown boxes, prices, etc. are synchronized in real-time with the
database via select queries. Even more, any literature and/or pictures of
experiments can be added, removed, or modified (even the format can be edited
as HTML, and not necessarily plain text) from the database by the DBA; no prior
knowledge of HTML or the ‘Internet’ is required. (You can access the
‘CEE Labs DBMS Website’ at http://webfea.fea.aub.edu.lb/ceelabs)
Below is a detailed listing of the preliminary
project milestones, which have served as major guidelines since its inception:
Sequence
reflects execution package then order; preliminary table & relationships
structure is appended.
1. Deciding on database scope, structure, and relationships
m Discussing proposed alternatives for
database structure draft (efficiency vs. space requirements vs. relational
logic…)
m Adopting optimal mechanism;
sketching preliminary table relationships and component flowchart
m Brainstorming for the essential vs.
the extra fields
m Defining optimal relationships that:
(1) maximize efficiency, (2) reduce system resource requirements, (3) satisfy
long-term requirements, and (3) support overall database upgradeability
2. Building raw database and testing integrity with virtual data
m Creating tables with uniform nomenclature,
aliases, and explicit field descriptions/notes
m Applying stringent requirements on
fields (data types; validation rules and input masks – on datasheet vs. form,
taking into account that datasheet validation rules need not conflict with
Web/ASP access; required fields – not empty; limiting to ‘lookup’ when
necessary, etc…)
m Testing integrity with virtual data
(use sub-datasheet cascading feature for testing relational logic; generate
temporary SELECT queries to be automated later on; etc…)
m Running ‘Analyze Performance’ and
‘Analyze Table’ wizards for preliminary checks
3. Gathering and incorporating all experiment-related data
m Preparing experiment inventory (to
contain experiment-specific details; specs references; input/output requirements;
format analogous to which experiments?)
m Deciding on experiment format, and
grouping relevant experiments accordingly (for ‘forms’ and ‘reports’ later on)
m Pointing out special features, graph
option, export-to-spreadsheet option for specific experiments
m Deciding on result calculation
methodology: (1) spreadsheet format (securely saved at relative path vs.
embedded in database vs. reproduced in Excel programmatically) or (2) implicit
parameterized procedures manipulated via VBA
m Implementing calculation methodology
(collect and standardize available spreadsheets; create missing ones; retain
backups of all spreadsheets; make cell-to-attribute link(s); create Access
‘calculation forms’ with optional ‘Save’ and/or ‘Graph’ features when relevant;
etc…)
4. Importing available data (clients, invoices, experiment descriptions,
etc…) into ‘table datasheets’ – document techniques for fast future
Excel-to-Access data importing
5. Building generic query procedure for virtual experiment-specific tables
(refer to “Experiment Description” and “Experiment Analysis Data” tables)
m Distinct tables containing all data
relating to each of the 180+ experiments will be generated in query-runtime
using one generic parameterized query. Upon entry of the ‘Experiment Name’ parameter,
query should use the ‘Number of Input/Output’ and ‘Input/Output Aliases’ fields
in the ‘Experiment Description’ table to construct new table(s) custom-sized
and field-aliased for the required ‘Experiment Name’ parameter
m Writing event query (‘Make-Table
Query’) in SQL; checking for any VBA requirements; extensive testing of query
on all experiments (‘Experiment Name’ records) – especially for trimming the
‘Input/Output Alias’ fields, which will be comma delimited and the extent of
trimming being a function of the ‘Number of Input/Output’ fields for each
‘Experiment Name’
6. Designing switchboard/control panel for DBA (database administrator)
m Will serve as DBA’s GUI (graphical
user interface); should provide access to all queries, forms, reports; should
cater for all database features – (DBA has no programming knowledge and should not need that for appending, deleting,
updating, executing, documenting, or printing any aspect of the database)
m To be designed in close
collaboration with Database Administrator (DBA), as he is the target (and
perhaps only) user
m Will serve as guide outline for
forms, reports, and queries to be still created by team
m To be extensively documented
(HTML-compiled Help File) for later reference by DBA and whoever is to evaluate
and/or upgrade the database
7. Building all other imaginable queries
(complete parameterization; no static criteria) – see ‘Package 9’ for query and
form categories
8. Database Theme
m Deciding on a general theme, color
scheme, logo/banner, graphic style, etc… and a standard for ActiveX control
effects, borders, colors, and fonts (no use of templates; theme to be
custom-made)
m Checking FEA and AUB
requirements/standards for all the above
m Applying theme to all forms,
reports, invoices, and web data pages (note that only the web component of the
database is accessible to regular clients/users; hence if such a ‘theme’ is not
important to DBA, it need not be applied to the ‘Access’ component which will
be viewed only by DBA – it can be left as ‘default’)
9. Forms (for administrative, editing, financial, calculation, and
research/statistical tasks)
10. Reports (official formatting dictated by paper template procured by AUB
vs. printer-based template generated in print-time; content controlled by forms
or custom-wizards for full customization by DBA)
11. Managing web logistics
m Securing web quota and access
permission details from AUB server officials
m Requesting permission to
access/upload to designated location from off-campus
m Deciding on where to store
‘database’: (1) in website (as subweb), (2) on file server (AUB intranet), (3)
on database server (i.e. SQL server), or (4) web server using DNS
12. Online aspect of database
m Deciding on scope of ‘online portion’
of database and accessibility considerations:
o
(1)
DBA is assumed to control database via Access and not through the web; hence
DBA has no special web accessibility features
o
(2)
Client can: sign-up for a new
profile, login into profile, logout of profile, update old profile, place
orders (multiple experiments), contact DBA, view activity log, query old
personal experiment results and invoices
o
(3)
Client cannot: delete own profile,
cancel or update order(s) being processed, view results online for unpaid
experiments, access profiles/results/balances of other clients – in such cases,
DBA is to be contacted and requested to administer the services listed herein
o
(4)
Orders are placed only by registered clients (new clients must sign-up for new
profile before placing orders)
o
(5)
DBA is alerted to all client activity (and consequent database client-specific
additions/updates) via automatic email received as new activity is recorded
m Developing ‘online portion’ of
database can benefit from 3 development mediums:
o
(1)
Access ‘data page’ features: aid of wizards and samples; quicker
processing/viewing of values from database;
predefined controls and HTML headers that highly facilitate record
manipulation; limited automation flexibility; limited customization
o
(2)
FrontPage ‘database web’ features: ability to import Access database (in case
database is chosen to be stored in website folder, unlikely in Soil Database
case); using the ‘Database Interface Wizard’ to automate development; predefined controls and HTML headers that
highly facilitate record manipulation; limited customization
o
(3)
Writing own ‘HTML’ and ‘ASP’ coding: maximum control over web interface,
security, and layout; sufficient HTML and ASP (VBScript) syntax knowledge is required; essential in ‘login’
& ‘logout’ page creation and ‘cookie’ management (to keep track of client’s
online session); much more time
consuming; all database-to-web linking to be built from scratch
o
(4)
Combination of (2) & (3): using FrontPage’s WYSIWYG (what you see is what
you get) editor to deign websites; using it’s advanced HTML editor (in
comparison with a regular text editor) and tag tools to insert ASP code into
the HTML (adopted; Microsoft Script
Editor 10.0 used as HTML/Script editor instead of FrontPage)
m Discuss the possibility of
publishing part of the research, statistical, or correlation studies supported
by database and used to analyze experimental bulk data offline to the soil lab
website; this can be done on a quarterly or semiannual basis. Client/User will have
access to various grouping, filtering, and sorting criteria for this data
13. Extensive (and creative) crash-testing of database; completing database
and project documentation; wrapping up and presenting work to CEE faculty and lab
officials/staff; congratulating ourselves on a job well done
