An extended structured query language incorporating object data types for the construction industry

MISSING IMAGE

Material Information

Title:
An extended structured query language incorporating object data types for the construction industry
Physical Description:
viii, 105 leaves : ill. ; 29 cm.
Language:
English
Creator:
Hollister, Kevin C., 1965-
Publication Date:

Subjects

Subjects / Keywords:
SQL (Computer program language)   ( lcsh )
Construction industry -- Data processing   ( lcsh )
Architecture thesis Ph. D
Dissertations, Academic -- Architecture -- UF
Genre:
bibliography   ( marcgt )
non-fiction   ( marcgt )

Notes

Thesis:
Thesis (Ph. D.)--University of Florida, 1992.
Bibliography:
Includes bibliographical references (leaves 99-104).
Statement of Responsibility:
by Kevin C. Hollister.
General Note:
Typescript.
General Note:
Vita.

Record Information

Source Institution:
University of Florida
Rights Management:
All applicable rights reserved by the source institution and holding location.
Resource Identifier:
aleph - 001803868
oclc - 27779165
notis - AJM7679
System ID:
AA00002090:00001

Full Text












AN EXTENDED STRUCTURED QUERY LANGUAGE
INCORPORATING OBJECT DATA TYPES
FOR THE
CONSTRUCTION INDUSTRY












By

KEVIN C. HOLLISTER


DISSERTATION PRESENTED TO THE GRADUATE SCHOOL
THE UNIVERSITY OF FLORIDA IN PARTIAL FULFILLMENT
OF THE REQUIREMENTS FOR THE DEGREE OF
DOCTOR OF PHILOSOPHY













ACKNOWLEDGMENTS



I would like to thank Dr. Charles Kibert for his assistance and guidance on


this research project.


To Dr. Gary Koehler I am grateful for both his technical input


and his guidance in course selections in the decision and information sciences.


would also like to thank Drs.


Weilin Chang and Carleton Coulter for their concern


and academic guidance.

I want to express my deepest gratitude to Dianne Woodbury, for without her


editorial skills


this dissertation would not have been


possible.


To my wife,


Gail,


credit is due for her typing assistance and for putting up with me during this difficult

process.
















TABLE OF CONTENTS


page
ACKNOWLEDGMENTS . . . . . . . . . . ii

LIST OF FIGURES . . . . .. . . . ... . . vi


ABSTRACT


CHAPTERS


INTRODUCTION


1.1 Dissertation Organization
1.2 Dissertation Purpose ..
1.3 Summary . . .


HISTORY AND BACKGROUND OF SQL


2.1 Data Base Concepts


S S S S S S S 5 6


S S S S S S S S S S S 56


Origin of Relational Data Bases ..... ..
Codd's Twelve Fidelity Rules .. .. ..
Origin and Implementation of Structured Query
Standardization of SQL ..
Weaknesses of SQL .. . .
Possible Solutions to SQL Problems ..


. S S S S S
Language.

* p S S S .
* . S 4 .


Summary


FUNDAMENTALS OF SQL

3.1 Basic Principles of SQL
3.2 Functions of SQL ...
3.3 SQL Relational Operators


* S S . 4 S S S . S S .

. 5 5 . 5 5 S S S . . .
. S . S S 4 5 S S S S S .
* S S S S S S S S S S S S S S S


3.3.1
3.3.2
3.3.3


UNION Operator .
INTERSECTION Operate
DIFFERENCE Operator


3r . . . . .
S S S . S . .









3.5 Summary .. .. .......

FRAMEWORK FOR SQL EXTENSIONS


4.1 Previous SQL Extensions


4.2 Nature of CI-SQL


4.3 Object Data Typing ..
4.4 Objects, Identity, and Attributes .


4.5 User-Defined Functions
4.6 SQL3 Function Definition


. .. .. ... .... .... .S S S
. S S S S . S S S .


4.7 Syntax Conventions
4.8 Summary .. .


CONSTRUCTION-SPECIFIC SQL EXTENSIONS


Construction Materials Domain


5.1.1


General Materials Data Base Structure


* a S S S S S S S S S S S S S S
* S S S S S S S a S S S S S 5 4 5 5 5
a S S S S S 5 5 5 a a S S S S S S S S S


tested


5.1.6 mater


per_test
ial tested


_per


* S S S S S S S S S S S S S S S S S S S S S S
* S a S S S S S S S S S S S S S S S S S S S S 5 9


5.1.7 meets or exceeds
5.1.8 Sample Application
Construction Scheduling Don
5.2.1 Basic Scheduling Coi
5.2.2 Extension Assumptic


ain . . . . .
ncepts . . . . .
ns . . . . . .


General Scheduling Data Base Structur


.2.4 concurrent


activities


.2.5 preceding activities
.2.6 succeeding activities


5.3 Summary


CONCLUSION


. S S . S S S S S .
. . S S S S S S S S S .


N S .. .. .. .. .. .. .. .. .. .. ... .. .


6.1 Summary of Dissertation
6.2 Results . . .
6.2.1 Functions Created


6.2.2 Impediments and Probl


ems


. S S S S S S S S . S .


.1.2 Extension Assumptions
.1.3 complieswith _standard
.1.4 material complies _with








APPENDICES


ACTUAL PROJECT SPECIFICATIONS


CSI MASTERSPEC FORMAT


SAMPLE SCREENS FROM SWEET'S SWEETSEARCH ON


CD-ROM

GLOSSARY ..


REFERENCES


BIOGRAPHICAL SKETCH


















LIST OF FIGURES


a . a a a a a a a a a S


a a a a S a a a S a a a a a a a a a S S S S


The UNION Operator .. ....


The INTERSECTION Operator


The DIFFERENCE Operator


The PRODUCT Operator .


The PROJECTION Operator


The JOIN Operator ..


The SELECT Operator ......


. S S a a a a S S S S S a a a a a a a


. C a S S S S S a S S a a


. .*.S. . . .S a a. S a a .S S


. a S S S . S S a S S S a S S S S S


. S S S S C . a S a a S S S S S S S S S


* a S S S S S S a a S S S a a a S S S S a S


. S S S . a a a S S S S a S S S S .


SQL Keywords . . . . . . .3 . . 35


Object Attributes


Arrow Diagram


. S S S a S S a S a a S S S S S S a a S S S S S S S S S a a a a S S


S S S a a S S S a S a S S a S S S a a S S S S a a S S S S


Arrow Diagram with i-j Numbers


Construction Scheduling Data Base Layout ................. 70


The Standards Organizations


The Solutions













Abstract of Dissertation Presented to the Graduate School
of the University of Florida in Partial Fulfillment of the
Requirements for the Degree of Doctor of Philosophy

AN EXTENDED STRUCTURED QUERY LANGUAGE


INCORPORATING OBJECT DATA


FOR THE
CONSTRUCTION INDUSTRY

By

Kevin C. Hollister


TYPES


August 1992


Chairman: Weilin P


Chang, Ph.D.


Major Department: Architecture

The purpose of this research is to extend the relational query language SQL


provide


construction


construction


industry-specific


information.


functions


chapter


retrieval


dissertation


introduces


organization and purpose.


Chapter


2 provides both the history and background of


SQL,


which serve as the foundations for the construction-specific SQL extensions.


To provide a clear understanding of the basic principles and structure of SQL, a


review of its primary functions and syntax is presented in Chapter 3.


SQL's relational


operators, and its keys and keywords provided the basis for further development in


Chapter 4.


Although previous extensions to SQL have been undertaken, none has








Industry-SQL (CI-SQL)


extensions.


Chapter 5,


primary applications


examined.


In the construction materials domain, five extensions are created


directly address the selection of materials during the design and bidding phases of the


construction process.


In the second domain, construction scheduling, an additional


extensions


are developed


are applicable


to the


retrieval


scheduling


information.


The provision of a model for the evolution of construction industry-


specific


SQL


extensions


these


domains,


advanced


application


computers for managing construction information.


The final


chapter,


Chapter 6,


describes the conclusions of this research and addresses the need for future research

in this area.














CHAPTER 1
INTRODUCTION


While


1980's


remembered


as the


microcomputer revolution, the 1990's are shaping up as the age of the


data


base


explosion.


Microcomputers


battle


are more


powerful


desktop


than


ever,


has
Sthe


processing power is being used for applications traditionally reserved
for minicomputers and mainframes, like data base management. The


been


won.


desktop


become


a window


access


corporate


information.


(Crutchfield, 1990, p. 193)


In few industries is accessing information more crucial than in the construction


industry.


Issues such as retrieving detailed information on the availability and cost


of construction materials, as well as determining the interrelationships among project


activities,


are critical


components in


providing the construction


industry with


information services it requires.


By providing such services productivity increases can


be realized through improved methods of information retrieval.


These improvements


are primarily


derived


from


addition


industry-specific


vocabulary.


macroscopic level the addition of vocabulary with which construction professionals

are already familiar begins to tailor an application to the specific information needs


of the construction industry.


With multiple key players (e.g., the owner, the architect,


and the contractor) timely and accurate information sharing is a vital ingredient to


a successful project.


The difficulty in sharing this information


is exacerbated by


era of


increased








Concurrent


with


business


world's


acceptance


relational


data


base


management systems (RDBMSs) as the preferred method of managing business data,

Structured Query Language (SQL) has become the de facto standard for accessing


this data.


While SQL may be adequate for industries that do not have a specialized


vocabulary,


it falls


short


meeting


information


needs


construction


industry.


It is the purpose of this dissertation, therefore, to extend SQL to address


the unique information demands of the construction enterprise.

will be referred to as Construction Industry SQL (CI-SQL). Th


This SQL extension


is dissertation will be


organized as follows.


1.1 Dissertation Organization

Chapter 2 will be a review of the history of the relational data base and the


development of structured query language.


This historical analysis will include the


relational


model developed by


Codd


from which relational


data bases were


derived.


Significant events that shaped SQL and eventually caused it to become a


standard will also be discussed.


Additionally, current problems in the standardization


process as well


as measures being undertaken


to resolve


these dilemmas will be


included to further explain the current status of SQL in industry use.


A discussion


these


events


issues


help


familiarize


reader


with


significant


developments and provide a theoretical framework for the applied extension of SQL.

In Chapter 3 an overview of the relational model and SQL will be presented.








words will be included to highlight the features of SQL. Query examples will be

given to provide the framework for construction-specific queries. Thus, this chapter


will supply a foundation for the extension of SQL in the subsequent chapter.


Chapter 4


needs will be explain


the guidelines used for extending SQL to meet construction

d. Since the author's purpose is not to improve SQL but rather


to provide


an industry-specific extension


to the


language,


SQL


standard,


accepted by the American National Standard Institute and its X3 Committee, will be


left unchanged.


The design methodology used in arriving at a "superset" of SQL will


be detailed.


Chapter 5 the


new construction-specific vocabulary of the Construction


Industry-SQL, CI-SQL, will be generated and explained.

new command syntax will be derived and explained. The


From this vocabulary the


product of this chapter will


provide


the construction industry with additional


tools beneficial


managing an


industry-specific relational data base.


final


chapter,


Chapter 6,


summarize the


findings of the


research


project


discuss


specific


areas


language


necessitate


further


investigation.


Consequently, the author acknowledges that this research and product


are not the completion of a process but rather the fundamental basis for providing

the construction industry with extensions to a query language designed to meet its

specific needs.






4

The essence of these chapters will serve to generate new knowledge in several


specific areas.


First, the concept of an industry-specific SQL will be developed to


include


an industry with


some


most


extensive


information


needs


business.


Secondly, guidelines for extending SQL to assist in


the management of


construction information systems will be set forth.


Finally, the language of SQL will


be extended


to include


a construction


vocabulary


command syntax


that will


provide the construction industry with a several query capabilities tailored to meet

its specific informational needs.

While CI-SQL can play a significant role in changing the way information is

retrieved by the construction industry, it is in no way a complete solution to the many


problems


managing


construction


information.


order


provide


comprehensive solution to information management, several issues must be resolved.


Initially, robust construction data bases must be developed that support SQL.


This


support should be unquestionably provided since SQL is the de facto standard for


relational query languages.


Second, the author does not assume that construction


professionals would become data-base professionals as well.


To maximize the user'


ability to utilize the extensions described in this work, a customized user interface

would have to be developed that would provide storage and easy access to repetitive


commands.


This interface might well be developed under a graphical user interface


such as Microsoft Windows or IBM'


OS/2.


Such an interface could further simplify


the process of building and repeating queries. A le


ss tangible, but no less important,








information.


Since the construction industry has a distinct reputation for resisting


change


difficulty


should


not be


underestimated.


Finally,


implementation of many of the ideas described in this research would mandate a

resolve on the part of the industry to minimize the enormous duplication of work that


currently exists in the design and construction processes.


Much of the information


pertaining


to materials


their


associated


costs


availability


must


obtained by the project designers and then again by all companies bidding on the


project.


This duplication of effort could be greatly limited by passing this information


along in electronic form from the design team to the construction


team.


CI-SQL


could aid in this process by furnishing an industry-specific vocabulary of data base


operators


could


used


anyone


familiar with


construction


terminology.


Based


on these


issues


it should


be apparent


that CI-SQL


one


several


important ingredients in the overall solution to the informational requirements of the

construction industry.


1.3 Summary


addition


to generating


new


knowledge,


research


augment the


existing body of knowledge in two key ways.


First, the information requirements of


construction


industry


more


adequately


applying


existing


management information system techniques to construction.


Secondly, additional


evidence advocating the adoption of an application independent SQL standard will


C1


4














CHAPTER


HISTORY


AND BACKGROUND OF SQL


This chapter will


provide


readers with


ample


history


background


information necessary to grasp the fundamental principles of relational data bases


and their companion query language, SQL.


Beginning with E.F. Codd's relational


model, an explanation of the concepts and terminology of relational data bases will


presented.


It was in


response


to this foundational


work


that SQL began


emerge as the preeminent tool for accessing relational data.


As it developed, several


significant events transpired that forged a direction and determined the structure of


SQL.


Due to its sound theoretical basis and its solid practical application, SQL was


accepted and designated as an international standard.

standard, however, is not without its pitfalls. Its propor


The process of becoming a


nents and critics have debated


merits


faults


SQL


standard


in a


continuing


effort


to refine


attributes and applications.


Therefore, the life of the Structured Query Language,


from its inception through its current state, will be discussed.


2.1 Data Base Conceots


The concept of a data base is a rather simple one.


of data.


It is merely a collection


Construction professionals deal with loosely defined data bases daily in the








base


is a


shared


collection


interrelated


data


designed


to meet


varied


information


needs


an organization"


(McFadden


Hoffer,


1988,


One


particular


type


data


base,


relational


data


base,


associated


query


language, will be the basis of this research.

is a collection of two-dimensional tables.

whose intersections are often called fields.


In its simplest form a relational data base

A table is composed of rows and columns

Each row in a table is frequently referred


to as a record which is a single, usually unique, instance of data.


For example, an


employee data


base


could contain


last name,


first name,


employee


number of all current employees.


The relational model, detailed in the subsequent


section,


gained


tremendous


popularity


1970's


over


other


models


fundamental reasons.


First,


the simple approach of organizing data in tables was


appealing to data base developers as well as users.


Second, the relational system


supports


access


by the end


user


rather


than


system


programmer.


This


accessibility opened the door for the creation of ad hoc queries that allowed the user


to retrieve data in almost any desired format.


These characteristics of relational data


bases


blend well


with


varied


information


retrieval


needs


of the construction


industry.


2.2 Origin of Relational Data Bases


In 1970, while working at IBM'


Research Laboratory in San Jose, California,


Codd published his definitive paper,


"A Relational


Model of Data for Large








relational model" (Date,


1989, p. 1).


According to Pascal (1989b),


"His relational


model, based on the set mathematics of relations of first-order predicate logic, covers


three aspects of data


that any


DBMS


must address:


structure,


integrity, and


manipulation" (p. 248).


These characteristics were originally set forth in the form of


a set of features as follows:


A. Structural features


R-Tables
Base (stored)
View (virtual)
Query (derived)
Snapshot


C. Manipulative features
Basic operations
Assignment
Project
Restrict


Product


Union


Domains


Difference


Derived operations
Join


Keys
Primary (PK)
Foreign (FK)


B. Integrity features
Entity integrity
Referential integrity
Domain integrity
Column integrity
User-defined integrity


Codd'


Intersect


Divide


Extended operations
Outer
Maybe
Domain override


Twelve Fidelity Rules


Due to misunderstandings and distortions, Codd later created his now famous

Twelve Fidelity Rules, a minimum of six of which must be met in order for a data


Columns








serves as a mandatory foundation for all relational DBMSs.


These rules, as outlined


by Pascal in "A Brave New World" (Pascal, 1989b, p. 249) are given as follows:

Rule 0: Foundation Rule

Any system that is advertised as, or claimed to be, a relational DBMS, must

manage the data base entirely through its relational capabilities.

Rule 1: Information Rule

All information in a relational data base must be represented explicitly at

the logical level in exactly one way by table values.

Rule 2: Guaranteed Access Rule

Each and every data value in a relational data base is guaranteed to be

logically accessible by resorting to a combination of table name, column

name, and primary key value.

Rule 3: Missing Information Rule

Missing value indicators distinct from empty character strings, strings of

blank characters, zero, or any other numbers, must represent and support,

in operations at the logical level in a systematic way independent of data


type,


values


are missing


applicable


inapplicable


information.

Rule 4: System Catalog Rule


description


data


base


represented


at the


logical


level


dynamically like ordinary data so that authorized users can apply the same






10

Rule 5: Comprehensive Language Rule


matter


many


languages


terminal


interactive


modes


supported, at least one language must be supported that is expressible as

character strings per some well-defined syntax that supports interactively by

program
1 data definition
2 integrity constraints
3 data manipulation
4 views
5 transaction boundaries
6 authorization privileges


Rule 6: View Updatability Rule

The DBMS must have a way of determining at view definition time whether

a view can be used to insert rows, delete rows, or update which columns of

its underlying base tables and store the results in the system catalog.

Rule 7: Set Level Updates Rule

The capability of operating on whole tables applies not only to retrieval but

also to insertion, modification, and deletion of data.

Rule 8: Physical Data Independence Rule


Application


programs and


interactive


operations should


not have


to be


modified whenever changes are made in internal storage or access methods.

Rule 9: Logical Data Independence Rule


Application


programs and


interactive operations should


not have


to be


modified whenever certain types of changes involving no loss of information






11

Rule 10: Integrity Independence Rule


Application


programs and


interactive


operations should


not have


to be


modified whenever changes are made in integrity constraints defined by the

data language and stored in the catalog.

Rule 11: Distribution Independence Rule


Application


programs and


interactive operations should


not have


to be


modified


whenever


data


distributed


redistributed


different


computers.

Rule 12: Nonsubversion Rule

If a DBMS has a low-level (procedural) language, that language should not

be allowed to subvert or bypass integrity constraints or security constraints

expressed in the high level relational level.


2.4 Origin and Implementation of Structured Query Language


with


data


base


system,


facilities


must


provided


retrieving


information from the data base.

called a query language. "Whei


data base in 1970, he suggested that,


The language used to perform these retrievals is


1 E.F. Codd introduced the concept of a relational


'the adoption of a relational model of data ..


permits


development of a


universal


data


sublanguage


based


on an


applied


predicate calculus.


' Although he indicated the requirements and the advantages of


such a language, he did not attempt at that time to devise one" (Hursch & Hursch,








1988, p. 1).


In his book, A Guide to the SQL Standard (1989), C.J. Date details the


evolution of SQL products based upon Chamberlin and Boyce's work.


initial


implementation of


a standard query


language


was


IBM


relational prototype, SEQUEL XRM.

released under the name SEQUEL/2.


later changed to SQL.

base, System R. Dur


This product was subsequently revised and

For legal reasons, this product's name was


Thereafter, IBM implemented SEQUEL/2 in its research data


ing the lifetime of the System R project, several modifications


were made to the syntax of SQL including the addition of an EXISTS function and


outer joins.


Due to the success of System


IBM began working


on additional


commercial SQL products.


In an effort to compete with IBM, other vendors, such


as Relational


Software,


began


developing


their


own


SQL products.


Relational'


release of its product, ORACLE, actually predated the market entry of IBM's SQL


product, SQL/DS.


SQL/DS, designed for the DOS VSE environment, was quickly


followed by a


VM/CMS version in


1982.


DB2, an MVS product compatible with


SQL/DS, was released in 1983.


In the years that followed, various vendors entered


arena


with


SQL-based


products


including


Hewlett-Packard,


Sybase,


Gupta


Technologies, Digital Equipment,


Teradata, Ingres, and Informix.


With a myriad of


vendors producing SQL products an inevitable variety of SQL dialects ensued.


response


to this proliferation,


American National


Standards


Institute (ANSI)


created its Data Base Committee, X3H2.


This committee, composed of vendors and


users,


began


attempt to se


standards


SQL


language


1982.


a






13
committee, ISO/RDA, has been working on a standard client/server interface for

multiple computing platforms.


Standardization of SOL


The SQL standard,


"which was finally ratified by


ANSI


1986, consisted


essentially of the IBM dialect of SQL,


" including its many idiosyncrasies (Date, 1989,


p. 3). The following year the ISO/RDA accepted ANSI's work as an international

standard. It is this standard, ANSI/SQL level 1, which is usually referred to as "the


SQL standard."


It is also the "only concrete expression of the relational model that


has gained industry acceptance" (Pascal, 1989b, p. 250).


"Since


1987


both the ANSI


and ISO committees have been meeting and refining their standardization efforts.

ANSI/SQL level 2 should be published in 1992; the RDA work will not be completed


until


1992"


(Crutchfield,


1990,


194).


addition


to the


quasi-governmental


standards organizations, several industry driven consortium have been working on


developing their own standards.


These groups include the X/Open Consortium and


the SQL Access Group (Crutchfield, 1990, p. 194).


The emergence of these industry


associations has primarily been a response to the ever-changing needs of users in the


marketplace.

in Figure 1 (


The development of these SQL standards organizations is summarized


Crutchfield, 1990, p. 194).


The fact that SQL is a language designed for accessing relational data bases


rather


than


complete


application


development


language


assisted


three




































Figure


The Standards Organizations


another


general


purpose


language,


such


as complexity


attempts


to create


programming panacea, can be avoided.


Thirdly,


the difficult political process of


extending


mainstream


procedural


languages


address


relational


data


base


requirements can be averted (Pascal, 1989b, p. 250).


The actual process of selecting


a model for the standard, however,


was somewhat arbitrary.


The chairman of the


committee developing the structured query language standard in the United States,


Don


Deutsch,


remembers,


"We just chose from


between


the existing products to


establish a least common denominator standard" (Moad, 1990, p. 27).


Clearly, the


standardization of SQL is an evolving process and one that is not without problems.


ISO/RDA
Committee
formed


SQL Access
specification
completed


ANSI SQL ANSI SQL Access SQL Access
formed SQL Group intoperlity
level 1 formnned demostio






1980 1982 1984 1986 1988 1990 1992


IC/Open
COnsatiumn
fonned


X/Open
joins
SQL Acces


----






15

2.6 Weaknesses of SQL


For anything to become a standard, it must inevitably encounter numerous


difficulties; SQL is no exception to this rule.


One of the most difficult obstacles for


SQL to overcome is for it to be capable of communicating across multiple hardware


and software platforms.


Since a


typical


relational


data base


management system


(RDBMS)


installation


include


hardware


software


supplied


several


different vendors, this problem is nontrivial.


Although many vendors have attempted


to resolve this situation through various forms of interfaces and integration, complete

interoperability has not yet been achieved.

The above problem is compounded when one considers the numerous dialects


of SQL that currently exist in the marketplace. This prevents one vendor's SQL

product from accessing the data in other SQL products. This problem is magnified


by the sheer number of SQL programs.


"According to industry estimates, there are


more than


140 implementations of SQL RDBMSes in use"


(Crutchfield,


1990, p.


196).


Initially


problem


seem


somewhat


trivial;


however,


consider


following practical example: An


individual may have a checking account, a credit


card, overdraft protection, and a loan all at the same bank.


Pertinent information


for all of these bank services could be contained in different data bases.


If a bank's


customers were to move and submit a change of address, it is very likely that all the

records would have to be updated individually since each bank service's information

probably resides on a different heterogeneous data base (Crutchfield, 1990, p. 196).








communication between competing SQL products.


Just as users strongly desire the


ability


to import and export


between


similar software


applications,


they


are also


requesting the ability to communicate with different RDBMSs on multiple computing

platforms.

Technology also poses its own unique set of difficulties for the standardization


process.


Rapidly changing


technology


makes it extremely


difficult for standards


committees to keep up with the feverish pace.


According to Don Loughrey,


vice-


chairman of the


X3H2


committee and a standards


manager at


Hewlett-Packard,


"Almost before the ink is dry on one [new standard], we are now writing the standard


to replace it.


That didn't happen ten years ago.


It was all sequential and much


slower paced.


Today, it's much more parallel, much faster"


(Moad,


1990, p. 25).


Additionally, "Because technology is advancing so rapidly they [standards committees]


often


no longer


have


luxury


of starting off with a handful


of proven,


tested


products from which to form a standard" (Moad, 1990, p. 27).


Ballooning committee


sizes in combination with this fierce technological advancement, cause many to feel


that the standards process needs a complete overhaul. Michael B. Spring, a professor

of information sciences at the University of Pittsburgh says, "I have no doubt that


anybody who is involved with standards at any level of intensity understands there is

a need for changing the process" (Moad, 1990, p. 32).

Unfortunately, even the way the standard was originally written contributed


problems to the issue of standardization.


One of these inherent problems is that "the








Catchings, 1989, p. 175).


Others criticize the vagueness in the ANSI standard, which


does a good


job of specifying


language's syntax


(grammar) but a bad


job of


nailing down its semantics (what the commands actually do)" (Glass, 1991, p. 514).


Additional


differences


"crop


APIs


(application


programming


interfaces)


interfaces between SQL and application programs written in languages such as C or

Cobol" (Glass, 1991, p. S14).


According to E.F.


Codd, the father of the relational


model, several of the


most significant flaws in SQL lie in its original design.


flaws that he has termed fatal.


in relations.


Codd has identified three


His first criticism is that SQL permits duplicate rows


He deems this a serious weakness since it violates the original relational


model and also permits uncontrolled redundancy.


Secondly, Codd criticizes SQL for


supporting an inadequately defined kind of nesting of a query within a query.


objection here is that by allowing one query to contain other subqueries the user


becomes


responsible


ensuring


adequate


performance.


Consequently,


difference in performance in nested and non-nested versions of the same query puts

an unnecessary performance-oriented burden on users, which will not disappear until


nesting is prohibited" (Codd, 1988a, p.


Codd'


third reproof of SQL is that it


does not adequately support three-valued logic, which is the support for null values


in addition to true and false.


valid results.


That is, in any Boolean expression true and false are


However, Codd argues that a null value, resulting from a query that


retrieves a record in which one or more of the columns is blank, is not adequately








belongs.


Because of this, he


maintains


that "users are


liable


to make numerous


mistakes if they are forced to support three-valued logic mentally" (Codd, 1988b, p.

72).


One


of Codd'


colleagues,


Date,


finds


fundamental


flaws


design of SQL.


He states that "it cannot be denied that SQL in its present form


leaves rather a lot to be desired--even that, in some important respects, it fails to


realize the full potential of the relational model" (Date, 1989, p.


Date contends


that,


"although


there


well-established


principles


design


formal


languages, there is little evidence that SQL was ever designed in accordance with any


such principles" (Date, 1989, p. 5).


He also feels that several specific functions, such


as a DROP TABLE function, were omitted from SQL's original design.

In its original design, problems such as lack of support for necessary functions,

idiosyncrasies in its semantics, and flaws in its programming interfaces have haunted


SQL.


During


evolution,


emergence


plethora


SQL


dialects


contributed to its shortcomings.


These issues, in conjunction with rapidly changing


technology and the need for support on multiple software and hardware platforms,


have contributed to SQL's tempestuous history.


Although the reproofs are varied,


the absence of a true standard appears to be a common thread in the majority of


these difficulties.


Pascal encapsulates this theme by saying


is an improved, fully relational SQL standard


that leads,


, "What is badly needed

not follows the market"


(Pascal, 1989b, p. 256).






19

2.7 Possible Solutions to SQL Problems


In response to these many problems involving the interoperability of SQL, "A

group of companies, including INGRES, Sun Microsystems, Digital Equipment, and


Tandem


Computers,


began


meeting


informally


discuss


achieve


interoperability and portability of SQL-based RDBMSes" (Crutchfield, 1990, p. 195).


This


industry


coalition


that although


work


ANSI


was


both


necessary and meaningful, it did not accomplish the ultimate goal of fully supported


SQL compatibility.


For this reason, the SQL Access Group was formed in


1989.


"The


Group's


mission


is to develop


technical


specification


that will


allow


one


vendor'


SQL application to access data in other vendors' SQL servers, thus enabling


RDBMSes


application


tools


from


different


vendors


work


together"


(Crutchfield,


1990,


195).


Currently


partial


solution


problem


interoperability can be achieved by buying or building "a front-end program that hides

the differences between various manufacturers' SQL engines" (Glass, 1991, p. S14).

These gateways must be written by both the data base vendor and the application


programmers so that one product can communicate with another.


This solution's


shortcoming, however, is that each client must create a gateway for every SQL server,


and each server must create a gateway for every client.


Consequently, the number


of necessary connections equals the number of clients times the number of servers


(see Figure 2) (Crutchfield, 1990, p.


196).


However,


if the SQL Access Group's recommendations are adopted,


each










Now: Connections -
(number of clients) x (number of servers)
Clients


SQL Access connections -
(number of clients) + (number of servers)
Clients


Servers


Servers


Figure 2 The Solutions


necessary under this solution would equal the number of clients plus the number of


servers.


This proposed solution directly addresses the problems of multiple software


and hardware platforms, SQL dialects, and communication protocols.

The additional problems associated with rapidly changing technology and the


outdated standardization


processes currently


used


ANSI


are being dealt with


successfully by at least one industry organization.


"The IEEE POSIX (The Institute


of Electrical and Electronics Engineers Inc. Portable Operating System Interface for

UNIX) Working Group, in just five years, built an impressive record for responding


user


vendor requirements


rapidly


working


with


standards consortia"


(Moad,


1990, p. 27).


According to IEEE officials, one fundamental reason for its


successful


involvement.


development


"According


a standard


to Paul


been


Borrill.


ability


IEEE Comnuter Society' Vice
C .-. -. .-


to solicit


strong


user


IEEE


Comnuter


S,,itv'


Vicc~








an institutional


basis.


'The users get the same vote as the manufacturers in


meetings,'


Borrill"


(Moad,


1990,


According


to the


University


Pittsburgh's Michael B. Spring, a professor in the information sciences department,

the standardization process can be successful even in a rapidly changing environment

if the standards are developed "closest to the end user" (Moad, 1990, p. 27).

Although the solutions for the problems in the implementation of SQL are


relatively focused, the range of suggested solutions for SQL


's original design flaws


varies widely.


According


to Codd,


problem


duplicate


rows,


or duplicate


records,


should


treated


three


phases.


First,


users


should


warned


support for


duplicate


rows will


phased


over


specified


period


time.


Secondly, a software switch should be installed in new software releases that would

allow the DBMS to operate in two different modes: one mode that permits duplicate


rows and a second mode that rejects them.


Thirdly,


Codd believes that after the


specified


period


time,


duplicate


support


should


dropped


altogether.


Another problem that Codd identified, that of inadequately defined nested queries


within queries, should be completely revamped, even


to the


point of creating an


entirely new relational language with ease of user properties being its paramount


objective.


Codd also feels that support for four-valued logic should be implemented


so that the


IS NULL clause could be phased out (Codd,


1988b,


Beech's


approach to resolving these design flaws is a much broader one.


He states,


"If SQL


develops as suggested into a more general language that includes the relational model






22
expansion of SQL includes such progressive ideas as incorporating an object data


model,


which


believes would


capitalize


on the


advantages


OOPS


(Object


Oriented Programming Systems).


Other authors feel that the solution is much more


concise.


According to


Trimble,


"The solution is obvious: Standardize.


Develop one


version of SQL supported by all


vendors,


which


can be


used


on all


systems.


standard version of the


language would make life much simpler for its users (the


effect on SQL vendors is more problematic, but at least in theory, it's the users who


have


the final say)"


(Trimble &


Chappell,


1989,


p. 8).


Although none of SQL's


problems appears to be irreconcilable since many proposed solutions exist, reaching


a consensus on


these proposals will be an arduous process.


ANSI


is continually


working on these issues, and, as of this writing, has proposed standards for SQL2 that


are currently awaiting approval by organizations such as the ISO.


SQL3, an extension


include


object


data


typing,


is already


being


developed


most


probably be ready for review and acceptance as early as 1993.


Clearly, the process


by which SQL evolves is occurring at an accelerated pace in order to incorporate the

most modern methods of managing relational data bases.


2.8 Summary


In this chapter, the history and background of relational data bases have been


addressed.


Additionally, the benefits and disadvantages of the query language SQL


have been enumerated along with some proposed solutions to the criticisms. The














CHAPTER 3
FUNDAMENTALS OF SQL

While the previous chapter covered the history and background of SQL this


chapter's


purpose is to acquaint the reader with the essential principles and syntactic


structure


SQL.


This


understanding


IS a


prerequisite


extending


SQL


encompass functions and capabilities necessary to address the informational needs of


construction


industry.


This


extension


is both


desirable


necessary


since


currently no extensions to SQL exist that address the industry-specific vocabulary of


the construction industry.


By addressing this unique vocabulary productivity gains can


be achieved through more efficient searches of relational data bases.


In this chapter,


an overview of the basic principles of SQL is provided first, followed by a synopsis


of the three primary functions of the language.


Third, the seven relational operators


are examined, and finally, the concepts of keys and keywords are addressed.


Basic Principles of SQL


Prior


to comprehending


CI-SQL,


a fundamental


understanding


relational model and its access language, SQL, is essential.


Codd's relational model,


as outlined in Chapter 2, sets forth both the precepts and the structure of relational


data bases.


According to


Wipper,


"A relational


data base


is characterized by its








tables that are composed of rows and columns.

data associated with each of the tables' columns.


Rows, also called tuples, contain the

"Each column in a table is assigned


a unique name and contains


a particular type of data" (Trimble & Chappell,


1989,


p. 3), such as an employee number for each employee in a personnel file.


Acting


conceptually as a storage medium, the intersections of rows and columns, sometimes

called fields, provide the vehicle necessary for locating and accessing relational data.

The number of rows in a given table is referred to as the cardinality of that table.


The number of columns is called the degree (Date,


columns form two types of tables.


1989


The first type, a base table,


These row


may be thought of as


a table that actually exists;


whereas a viewed table, or view, is a virtual table that is


extracted


relational


from


data


single


base


base


table


component,


or a combination


catalog,


of base


system


data


tables.


base


Another


containing


information about base tables, views, access rights, user-ids, etc. that can be queried


through the use of SQL SELECT


statements" (Hursch & Hursch,


1988, p. 10).


Functions of SQL


The relational sublanguage, SQL, provides support for three general functions.

First, SQL acts as a Data Definition Language (DDL), which is used for defining the


structure of the data.


Secondly,


it serves as a Data Manipulation Language (DML),


which


is used


modifying data


within


data


base.


Finally,


SQL


Data


Control Language (DCL), which controls user access by specifying security constraints






25

3.3 SQL Relational OPerators


The tools used for performing these data management processes are termed


relational operators.


The operators that are supported by the relational model are


UNION, INTERSECTION, DIFFERENCE, PRODUCT, PROJECTION, JOIN, and


SELECT


these


operators,


only


PRODUCT,


PROJECTION,


JOIN,


SELECT


3.3.1


may be performed on tables with differing structures.


UNION Operator


As its name implies, the UNIC

tables to create a third resulting table.


)N operator allows the user to combine two

To perform this function, both tables must


have the same number of columns with identical headings and data types for each


column.


Therefore, the resulting table contains as many rows of information as the


sum of the rows of the two initial tables less any duplicate rows.


An example of a


UNION operation is illustrated in Figure 3.


3.3.2 INTERSECTION OPerator


Like the UNION operator, the INTERSECTION operator requires that both


tables used in this function be identical in structure but not in content.


The table


resulting from the INTERSECTION command contains data that was common to

both initial tables. Therefore, if the initial tables had no common data, the resulting

table would be empty. Given in Figure 4 is an example of an INTERSECTION


function.






26

Store A


I S1.


Description


Item #


Item Cost


Item Price


3/4" CDX Plywd 0896 $6.45 $8.25
Nail Apron 0766 $13.22 $15.99
Claw Hammer 2265 $8.96 $9.99

UNION

Store B
Description Item # Item Cost Item Price
1/2" Drywall 3244 $7.55 $8.95
Nail Apron 0766 $13.22 $15.99
Roofing Hammer 3341 $9.36 $11.99


Produces:

Stores A and B


Figure 3


The UNION Operator


Description Item # Item Cost Item Price
3/4" CDX Plywd 0896 $6.45 $8.25
Nail Apron 0766 $13.22 $15.99
Claw Hammer 2265 $8.96 $9.99
1/2" Drywall 3244 $7.55 $8.95
Roofing Hammer 3341 $9.36 $11.99






27

Store A


Description


Item #


I I a~---


Item Cost


Item Price


3/4" CDX Plywd 0896 $6.45 $8.25

Nail Apron 0766 $13.22 $15.99

Claw Hammer 2265 $8.96 $9.99


INTERSECTION

Store B


Description Item # Item Cost Item Price

1/2" Drywall 3244 $7.55 $8.95

Nail Apron 0766 $13.22 $15.99

Roofing Hammer 3341 $9.36 $11.99

Produces:

Stores A and B

Description Item # Item Cost Item Price

Nail Apron 0766 $13.22 $15.99


Figure 4


The INTERSECTION Operator


3.3.3 DIFFERENCE Ooerator


Unlike


UNION


INTERSECTION,


results


DIFFERENCE


operation are directly affected by the order in which the original tables are accessed.

For example, DIFFERENCE can be used to locate all rows that exist in table one


but not in table two.


This operation, however, would not produce the same results


I *a I 11 a -


,r r






28

Store A


Description Item # Item Cost Item Price
3/4" CDX Plywd 0896 $6.45 $8.25
Nail Apron 0766 $13.22 $15.99
Claw Hammer 2265 $8.96 $9.99


DIFFERENCE

Store B
Description Item # Item Cost Item Price
1/2" Drywall 3244 $7.55 $8.95
Nail Apron 0766 $13.22 $15.99
Roofing Hammer 3341 $9.36 $11.99


Produces:


Only Store A
Description Item # Item Cost Item Price
3/4" CDX Plywd 0896 $6.45 $8.25
Claw Hammer 2265 $8.96 $9.99

and

Only Store B
Description Item # Item Cost Item Price
1/2" Drywall 3244 $7.55 $8.95
Roofing Hammer 3341 $9.36 $11.99


Figure 5


The DIFFERENCE Operator








3.3.4 PRODUCT Operator


PRODUCT is the first of four relational operators that can be performed on


tables with


differing structures.


This function


produces a


table


that contains all


possible combinations of the rows in the


two original tables.


For example, if the


three primary models of Honda automobiles were contained in one table, and all four


exterior


color


choices were


listed


a second


table,


table


resulting from


PRODUCT


operation would contain twelve rows listing all possible combinations.


This example is given in Figure 6.


3.3.5 PROJECTION Operator


Unlike the previously discussed operators, a PROJECTION can be performed


on a single table.


The PROJECTION operation permits the selection of specified


columns from an existing table. Therefore, PROJECTION produces a table with

fewer columns than its source table. For example, employee number and race could


be listed in a PROJECTION table that was derived from a detailed personnel file.


This illustration is shown in Figure


3.3.6 JOIN Operator


Like the PRODUCT operator, JOIN also creates a new table that contains

data from the two original tables. However, a JOIN condition must first be satisfied

in order to perform this operation. The most common JOIN condition is called an


equi-ioin.


sthht cas e


-" I'" -- -- ~


values from specified columns must be equal


in order to






30

Models


Model Model # Cost Price
Prelude 1244 $17,233 $22,821
Accord 2776 $14,356 $18,189
Civic 3220 $9,976 $11,821

PRODUCT


Color Color #
Blue B165
Green J323
White W459
Gray G921


Produces:


Model Model # Cost Price Color Color #
Prelude 1244 $17,233 $22,821 Blue B165
Prelude 1244 $17,233 $22,821 Green GN323
Prelude 1244 $17,233 $22,821 White W459
Prelude 1244 $17,233 $22,821 Gray G921
Accord 2776 $14,356 $18,189 Blue B165
Accord 2776 $14,356 $18,189 Green GN323
Accord 2776 $14,356 $18,189 White W459
Accord 2776 $14,356 $18,189 Gray G921
Civic 3220 $9,976 $11,821 Blue B165
Civic 3220 $9,976 $11,821 Green GN323


S








tables.


instance,


if table


detailed


model,


weight,


color


automobile inventory, and table two listed colors, and their associated paint numbers,

the process of equi-join on color would appear as shown in Figure 8.


Last First Emp. ID # Sex Race

Smith David 1122 M B


Richards Michelle 3442 F W


Gonzales Juan 8127 M H


Brett Mary 6310 F W


Harkins Allison 5528 F B


Produces:


Emp. ID # Race


1122 B


3442 W


8127 H


6310 W



































Produces:


Figure 8


The JOIN Operator


Model Weight Color
Corolla 2600 Blue
Cressida 3215 White
Supra 3312 Gray
Corona 2788 Black
Camry 3190 Red


Color Paint #
Blue 132
White 212

Gray 234
Black 621
Red 517


Model Weight Color Paint #
Corolla 2600 Blue 132
Cressida 3215 White 212
Supra 3312 Gray 234
Corona 2788 Black 621
Camry 3190 Red 517








3.3.7 SELECT Ooerator


The final and most frequently used relational operator is SELECT


. Since the


primary purpose of any query language is the retrieval of data from the data base,


the SELECT


statement is clearly at the core of SQL.


Instead of selecting specific


columns of data as the PROJECTION operator does, SELECT


rows of data.


purposes to choose


The general form of SELECT is


SELECT

FROM

WHERE constraints() >


where represents the specific columns that are to be extracted


from the table specified in the FROM clause.


WHERE is an optional clause that


allows the user to institute further constraints on the query.


A Boolean expression


is used in the WHERE clause as a tool for evaluating data to determine whether it


fits the given set of conditions.


This condition, or Boolean expression, is called the


predicate.


Examples


predicates


are EXISTS,


(NOT


EXISTS),


BETWEEN


...AND....


The following is an example of the SELECT


operator:


SELECT


salesman


, region,


gross


sales


FROM Sales


WHERE gross


sales


> 100,000






34

Sales Table


Figure 9


The SELECT Operator


Through


use of


these


seven


relational


operators,


powerful


queries


can be


formulated in


both


nested and


non-nested forms to extract


necessary


data


from


relational data bases.


3.4 SOL Keys and Keywords


Two additional instruments provide for searching and query construction in


the SQL environment: keys and key words.


Since the rows in a relational data base


are unordered, and efficient searches are of supreme importance, a device must exist


for rapidly locating desired data.


This device, called a key, performs this function.


An individual column or columns may be designated as the key(s) for a particular


table which reonires that each vailne in the klev rnlumn hp ilninull


Thik Pnurcic that


Salesman Region Supervisor Age Gross Sales
Smith NE D. Jones 36 75,000
Arnold SE L. Brown 44 123,450
Drake SW B. Benson 51 112,500
Samuels NW R. Rogers 29 88,300


Salesman Region Gross Sales
Arnold SE 123,450
Drake SW 112,500






35
Finally, like most programming languages, SQL retains a list of several words


that may not be used in tables or column names.


Figure 10 lists SQL's key words


(Trimble & Chappell, 1989, p. 12).


ALL


AND


ANY


ASC


AUTHORIZATION


AVG


BEGIN


BETWEEN


CHAR


CHARACTER


CHECK


CLOSE


CONTINUE
CURSOR
DELETE
END
FETCH
FOUND


COUNT
DEC
DESC
ESCAPE
FLOAT
FROM


COBOL
CREATE
DECIMAL
DISTINCT
EXEC
FOR
GO


COMMIT
CURRENT
DECLARE
DOUBLE
EXISTS
FORTRAN
GOTO


GRANT


GROUP


INDICATOR


HAVING


INSERT


INTO


INTEGER


LANGUAGE


LIKE


MAX


MIN


MODULE


NOT


NULL


NUMERIC


OPTION


PASCAL
PROCEDURE
SCHEMA
SMALLINT
SQLERROR
UNION


PLI
PUBLIC
SECTION
SOME
SUM
UNIQUE


OR
PRECISION
REAL
SELECT
SQL
TABLE
UPDATE


ORDER
PRIVILEGES
ROLLBACK
SET
SQLCODE
TO
USER


OPEN








Clearly,


there


is not an


attempt


this chapter to


examine


the complete


structure and the many nuances of SQL.


However, sufficient framework has been


established such that an extension of this query language can be undertaken in the


subsequent chapter.


This extension will preserve all components of the relational


model and SQL discussed in this chapter, adding only those modifications necessary

to make it specific to the construction industry.


3.5 Summary


In Chapter 3 the fundamental structure of SQL queries has been described.

This description is key to providing a basis for the extensions to be undertaken in the


following chapter. Several simple examples were also given to clarify the usage of the

primary SQL operators. Chapter 4 will detail the extensions that have been designed


to be directly applicable to queries that are particularly useful to the construction


industry


order


to achieve


more


efficient


intuitive


method


querying


construction data bases.














CHAPTER 4
FRAMEWORK FOR SQL EXTENSIONS


With the foundational syntax of SQL having been reviewed in the previous

chapter, the power and simplicity of this relational query language should be evident.

It is this combination of characteristics that has caused SQL to be adopted as an

industry standard and is the primary motivation for using this language as the basis


for the construction industry-specific extensions.


In creating these extensions the


purpose of this research is not to develop a new relational query language, to develop

a new software package, or to completely revamp the most widely accepted relational


data base


query


language.


Many


authors and


relational


data base


experts


have


thoroughly covered such


territory in both


theory and practice.


The intent of this


research is, however, to develop a new conceptual model of construction industry-


specific


extensions


upon


which


subsequent


research


applications


generated.


Aimed at extending construction research and improving the handling of


construction information, this research focuses on industry-specific extensions that


have


not yet


been


addressed.


primary


benefit


research


construction


industry


improved


productivity


area


information


retrieval.


The productivity gains will be realized through the creation of simple, more






38
aversion could be overcome and the construction industry could begin to tap into the


power and efficiency of the standard in relational query languages.


It is important


to note, however, that this research only begins the process of simplifying relational


queries for the construction industry.


Many other issues, as addressed in section 1.2,


must


resolved


order


to complete


work


tailoring


SQL


use by


construction professionals.


remainder


chapter


is organized


several


major


divisions.


Section 4.1 examines other extensions to SQL and their results.


Section 4.2 defines


what CI-SQL is and how it compares/contrasts to the


ANSI


version of SQL.


section 4.3 the concepts of object data typing are described as they relate to possible


applications


the construction


industry.


Section 4.4 contains an


explanation


Beech's


main


concepts


correlating


relational


data


bases


to object


data


base


management systems.


Section 4.5 blends object data base management concepts with


the application of user-defined functions using a construction-specific case.


Finally,


sections 4.6 and 4.7 briefly review the SQL3 method of defining functions and the


conventions used in extending the syntax of SQL.


With these explanations in place,


the reader will have the necessary foundation for the extensions that will be detailed

in Chapter 5.


4.1 Previous SQL Extensions


Several previous efforts have been directed at extending SQL in various areas,








developed


PSQL


(Pictorial


SQL)


which


clauses


were


added


to the


SELECT-FROM-WHERE construct.


In his work, Egenhofer criticizes this extension


for making "the formulation of queries unnecessarily complicated" (Egenhofer, 1989,


1987 Ingram added syntax extensions to SQL to address the needs of


geographic information systems.


However, in this work, Ingram (Egenhofer, 1989,


79) also added a command set outside of ANSI supported SQL.


The following


year


Herring


(Egenhofer,


1989,


pursued


an object-oriented


approach


extending SQL but sacrificed some SQL principles in the process.


In 1989 Egenhofer


attempted unsuccessfully to combine spatial concepts with a graphical user interface.


own


admission


his work


culminated


negative


demonstration


extension of an SQL-like language for spatial data handling"


(Egenhofer,


1989, p.


161).


Although several attempts have been made at extending SQL in application


specific areas, no published undertakings exist in handling construction information.


4.2 Nature of CI-SQL


Unlike some of the works cited in the previous section, CI-SQL will preserve


all of the functionality and regulations of the ANSI supported version.


By doing so,


complete compatibility is maintained with existing implementations that use the ANSI


standard.


Maintaining complete compatibility is essential in order to ensure that this


work is a true extension which augments rather than replaces an existing standard.


The additions proposed in


this work will be


form of semantic rather than








occurs


in an area


in which


SQL


rarely


been


applied,


existing


field


compatible relational data bases will be meager.


For this reason a generic data base


structure will be explained using a construction material example for illustration.

The role that CI-SQL plays in providing information retrieval facilities for the


construction


industry


is that


an intermediary.


CI-SQL begins


process of


bridging the gap between construction data and the construction professional.


order for a user to access a data base, a query language must be provided as the tool


for achieving this access.


Here, CI-SQL acts as the intermediary between the user


and the data, possibly through the use of a user interface.


The user interface would


provide a more intuitive method of accessing the data without requiring the user to


become an expert at formulating SQL queries.

cliche, but relevant, issue of user-friendliness.


This interface would also address the

For users who are unfamiliar with


query languages, a user-friendly interface could conceal the actual process of building


SQL queries.


Because this research does not attempt to address the development of


a user interface, further research is certainly warranted in this area.


It should also


be noted that this research is not purported to be the culmination of a process, but

rather the commencement of the effort necessary to supply a complete CI-SQL.


4.3 Object Data Typing


Although


purpose


dissertation


means


to address


comprehensively the addition of object data typing and user-defined functions to the








Beech,


who has been


particularly outspoken regarding the specific benefits to be


derived from such an addition.


In their paper,


"Object data bases as generalizations


of relational data bases," Beech and Ozbutun propose that the object data model


should subsume rather than replace or be appended to the relational model.


approaching object data modeling in


this fashion,


they believe


that the relational


concepts and benefits of the existing SQL standard can be preserved and improved


rather


than


creating


an entirely


new


language.


Thus,


object


data


base


management system (ODBMS) could then contain within it all the functionality of

a relational data base management system (RDBMS)" (Beech & Ozbutun, 1991, p.


221).


The use of object data bases and their comparably simple syntax potentially


could overcome many of the difficulties associated with the syntactic complexity of


standard relational


query


languages.


Through


implementation of


Entity-


Relationship (E-R) model a much more intuitive approach to relational data base


modeling can be achieved.


In the E-R model, the relationships between entities are


described,


rather


than


using


keys


to model


connections


between


columns.


example, this approach "allows the user to describe both entities such as Employees

and Departments, and relationships between them such as Worksln" (Beech, 1989b,


p. 45).


Thus, the intent of this chapter is to develop the framework within which the


construction industry-specific extensions will exist.


4.4 Obiects, Identity, and Attributes








model.


This correlation


model


which


provides a smooth


incorporates


transition


characteristics of


from


the strictly


an object


data


relational


base.


addition to describing tuples as objects, each object has both identity and attributes.

The principle of identity is that although the characteristics, or attributes, of an object


change


over


time,


it is still


same


object.


example,


although


employee's department and address may change, the object itself is still an employee.


The concept of attributes is most easily explained through the use of an example.


person


object


have


such


attributes


as name,


address,


birthdate


characterize and uniquely identify that object (see


Figure


11) (Beech & Ozbutun,


1991, p. 222).


Figure


Object Attributes


Address

Person /
Person
nanameme
address name
birthdate
Person
father
name
mother ...
hair color
phones PhoneNumber


PhoneNumber
\-






43
of inheritance means that when a new instance is created from an existing class, it


inherits the attributes and operations, or functions, of that class.


These principles of


object oriented data bases are being woven into SQL to develop a richer and more

powerful relational data base language.


4.5 User-Defined Functions


In addition to providing embedded construction-specific functions, one of the


most desirable


extensions


to SQL proposed


research


is the capability


creating construction-specific, user-defined data types, objects, and functions.


Due


to the


specialized


nature


construction


data


bases


queries,


ability


construct and reuse functions that are application specific is of enormous benefit to


the end user.


By providing this facility the queries can be tailored to construction


industry data bases as well as the individual query needs of the user.


The following


is a sample scenario that demonstrates the need and details the creation process of

a user-defined function.

During the design phase of a construction project, project specifications are

created to detail the materials and methods to be used during construction in order


to provide the level of quality desired by the owner and his or her architect.


In the


process


creating


these


specifications


designer


enumerate


literally


thousands of products or materials and the particular characteristics that they should


possess.


Again, the nature of this process lends itself very well to the use of object


t -








facilities


user-definable


functions will


allow for


the construction


complex


industry-specific queries that are not currently provided for in SQL.


creating


user-defined


function


following


distinct


steps


involved:

1. Define the object type


Create an instance or instances of the object type.


The initial step in creating a user-defined function that maintains the properties of


the object oriented data base management system is the object type definition.


This


creates


template


or class


from


which


other


objects


having


same


properties can be created.


In this example several of the critical attributes of wood


doors are used to create the object type WoodDoor.


It should be noted that in these


constructions existing SQL vocabulary is shown in uppercase letters, while additions


to the syntax are shown in bold type. Object

their instances begin with a lowercase letter.

its data type is characterized. For instance


types begin with a capital letter whereas

After each attribute, or field, is defined


, in the following example the attribute


Manufacturer is defined as a character string of up to fifteen characters (CHAR(15)).

CREATE type WoodDoor

(Manufacturer CHAR(15),

Model# CHAR(10),


Face


material CHAR(20),






45

Once the object type has been created the user is able to create actual objects,


or instances, of the object type.


Here, an instance of WoodDoor is woodDoor and


is created as follows:

CREATE woodDoor


instance ('Pella',


'SCO-30'


'oak'


, 'particle board'


'multi-ply laminated')


This


process


creating


an instance


of WoodDoor,


as with


object


type,


accomplished much like the insertion of a row into a conventional relational table.

However, instead of using the keyword INSERT, CREATE is used since the concept


of "creating"


an instance, or object, may be much more intuitive to the user.


each of the attributes defined in the creation of the object type, or class, the instance

command inserts the subsequent values into the type definition parameters.

The final step in providing a user-defined function is the procedure of creating


the function


itself.


The justification for such a development and addition


to the


existing


body


SQL


functions


is that


ability


to create


application


specific


functions


would


greatly


enhance


power


usability


SQL


industry


professionals.


Since the purpose of SQL is information retrieval, the more a user can


customize the query language to meet his informational needs the more powerful that


language becomes.


Thus,


"the goal is to make the modeling of information as direct


and natural as possible, and to overcome the impedance mismatch with programming


languages that already have many of these richer facilities"


(Beech,


1989b, p.


For this reason a suggested procedure for creating user-defined functions is given








specific


need


information


retrieval.


as is


common


practice,


an architect


intended to specify the doors in a project based upon the materials that composed

the face, core, and edge, or stile, of the door, the following function would allow him

to search a construction data base and retrieve all products that met his criteria.

CREATE function selectdoor (face, core, edge) as


SELECT


manufacturer, model#


FROM Division


WHERE face

AND core mi


Doors&Windows


material = face


material = core


AND edge


material


= edge


By specifying the arguments face, core and edge,


this function would


return


manufacturer and model number of all products in the Division


Doors&Windows


data base that met the given criteria.


developing


a robust


collection


such


user-defined,


industry-specific


functions, the procedure of retrieving data in a vertical market data base could be


thoroughly


customized.


This customization could


enhance


simplify an


industry-specific


query


language


to the


point


knowledge


industry'


vernacular could largely replace a detailed knowledge of SQL syntax.


The net result


of this simplification would be that the information contained in the data base would

be brought much closer to the end user.











[] FUNCTION



RETURNS

;

END FUNCTION


where





::= CONSTRUCTOR


ACTOR


DESTRUCTOR





::=


( [,...])





::=


SQLCODE

SQLSTATE


The following restrictions are placed upon these functions:


SQL


functions


with


same


name


must


have


same


corresponding


parameter modes

2) all SQL functions must contain a RETURN statement




type >


must be


used


Abstract


Data


Type


(ADT) definition and


nowhere else

4) constructor functions must have appropriate

5) destructor functions must have appropriate






48

4.7 Syntax Conventions

Additionally, the following syntax conventions apply to all extensions contained

in the following chapter:

1) function names are italicized and not capitalized

2) object types or classes, as well as data base names, are capitalized

3) data base fields or object instances are not capitalized


4.8 Summary


This chapter


has set forth a


format for the


development of


construction-


specific extensions to SQL.


Previous attempts at extending SQL have been described


and their successes and failures have been noted.


A framework for the nature of CI-


SQL


been


characterized,


including


features


object


data


typing


applicable to CI-SQL.


The following chapter will detail the actual extensions that


comprise CI-SQL and give query examples to illustrate their uses.














CHAPTER 5
CONSTRUCTION-SPECIFIC SQL EXTENSIONS


The preceding chapter has defined the parameters for the development of


construction-specific extensions to SQL.


Within


this framework, this chapter will


detail the extensions included in CI-SQL.


The first and largest domain in which SQL


extensions are created is in the area of construction materials.


This application is


developed


section


with


specific


extensions.


Within


section


hypothetical general data base structure for construction materials is described.


assumptions regarding the extensions are contained in section 5.1.


Also,


The subsequent


section,


develops


second


major


domain


construction-specific


SQL


extensions.


This


section


includes


description


general


data


base


structure, and the assumptions necessary for creating the extensions, as well as the


applicable


basic


scheduling


concepts.


final


section,


summarizes


extensions that were developed for CI-SQL.


Construction Materials Domain


major


domain of


construction-specific


SQL extensions


been


developed to address the unique query needs of construction material and product


plpfrtinn


Due to the comnlex nature nf the data ha re itself the need fnr vernarnlar


I






50
data base using vocabulary currently familiar to the typical user is necessary in order


to maximize the use of this powerful relational tool.


Using terminology very common


to actual project specifications (see Appendix A), these extensions were developed


to provide


construction-specific


functions


information


retrieval.


This


set of


extensions will be developed


following format:


first, each function will


described


along


with


need


or motivation


development;


second,


function's syntax will be detailed; third, a sample query will be given followed by the

formal representation of the query; finally, the result of the query will be provided

in standard tabular form.


5.1.1


General Materials Data Base Structure


general


concepts


relational


data


base


design


clearly


apply


to the


creation


complex construction


materials data


base.


First,


idea


that all


relational data can, and must, be represented in the form of tables at the logical level

simplifies the process of structuring the data while representing it in a familiar form.


Second,


"the decoupling of logical and physical levels means that logical and physical


design problems can be separately addressed" (Date, 1988b, p. 18).


The logical level


of design addresses how the data is structured and retrieved while the physical design


addresses


the data


is actually


stored


on disk.


Third,


normalization,


or the


process of simplifying data structures into simple relations, ensures that the structure


of the data is in its simplest form.


Finally, the ability to dynamically define data






51
format of a data base that would contain all applicable divisions of the construction

materials domain as systematized by the Construction Specifications Institute (CSI).


conforming


data


base


to the


sixteen


divisions


enumerated


MasterSpec


format


Appendix


compatibility


with


current


construction


conventions


familiarity


to construction


professionals can


preserved


while


maintaining the simplicity of the relational model.


Based on this premise a natural division of data bases begins to emerge.


illustrative purposes, the general data base structure of an industry standard product


data base, Sweet'


SweetSearch Building Product Search System on CD-ROM (see


Appendix C), will be used as the basis for sample relational queries.


It should be


noted that although Sweet's data base does not currently support SQL queries it is

highly probable that a "live" or on-line version of this product or a similar one would,


by necessity, support the SQL standard.


Using Division 8, Doors and Windows as


an example,


following


a description


general


pattern


would


characterize such a data base.


In the definition of any construction materials data


base schema the typical characteristics of the general class of materials would have


to be


clearly


defined.


example,


doors


windows,


materials


belonging to this class would have to be separated into two distinct divisions: 1) doors


and 2) windows.


Within


each division


the attributes


indigenous


to that material


would become fields in the data base. For example

metal, flush or paneled, and hollow or solid core.


e, doors would be either wood or

This procedure would begin to








a specific door and


designate


whether it had


lites or


louvers.


data base


specification could also be designed to include performance qualities such as fire


resistance and sound transmission properties.


These attribute


templates must be


generated for each class of materials in order to map properly a data base design to


the actual characteristics of the construction material.


In the case of Sweet'


Building


Product


Search


System,


these


templates


have


been


created


sixteen


divisions and implemented using an internal search engine.


5.1.2 Extension Assumptions


At this point it becomes necessary to outline several assumptions that must be


in place in order to define properly the scope and format of this work.


First, it is


assumed for the purposes of this research that a data base structure of comparable

size and complexity to Sweet's has already been created and does support Structured


Query Language searches.


Because SQL is the industry standard and an increasing


number of data base developers are designing data bases that support this query


language, this assumption is exceedingly viable.


Second, it also is assumed that any


data


base


magnitude


would


arranged


according


numbering


convention of the Construction Specifications Institute (CSI) since this system has


become


facto


standard


dividing


construction


products,


services,


specifications into manageable sections.


Third, the general format of this chapter will


be to introduce each new extension, describe its application, detail


its syntax, and






53
Therefore, a typical query used by a construction professional would entail the use


of some, if not many, more modifiers in order to retrieve an acceptable


body of


information.


example,


if the


user wanted


to retrieve


selection


doors,


qualifiers such


as size,


swing,


material,


height,


rating


and style


could


specified in order to limit the scope of the search.


5.1.3 complies with standard


The first SQL extension created for construction industry-specific queries is

the complies_with_standard function which would retrieve all standards to which a


particular product conforms.


The practical application of this function is twofold.


During the design phase, the architect/engineer would be able to use this function to


determine which products in a manufacturer's data base


standard that he or she has specified.


meet a particular design


Additionally, during the bidding phase of the


project, the contractor could use the same query function on a different data base

containing pricing and availability information to determine the cost and availability


of a particular product that has been specified by the design team.


By providing the


ability to return both the standards to which a particular material complies as well


as the


materials


meet a


particular standard,


search


capabilities


for this


scenario would be thoroughly covered.


The following syntax extension shows how the


first of two extensions are defined by using the particular material in question as the


parameter or argument for the function and returning


the standard or standards to






54

ACTOR FUNCTION complies _with standard (material REF (Materials))

RETURNS standard;


RETURN SELECT


manufacturer, product, conformance


FROM Materials

WHERE product = material

END FUNCTION


Sample Query:


Given a data base Division


Finishes with an instance acousticalCeilingTile,


standard


with


which


Armstrong's


ceiling


model


ACT-22M,


complies.


Query Form:

SELECT *


FROM Division


Finishes


WHERE complieswith standard (ACT-22M)


Query Result:


Manufacturer


Product


Conformance


Armstrong


ACT-22M


ASTM E 1264


5.1.4 material


complies with








standard as the argument and returns a list of materials meeting that standard.


the construction industry the application of this function would be in the retrieval of


appropriate


materials


conforming


standard


specified


project


specifications.


For example, if a contractor bidding on a job desired to see a list of


all fan motors that conform to a particular UL standard, the following function would


provide


necessary


capabilities


retrieving


acceptable


options.


contractor could then choose from those options while considering such issues as cost


and projected delivery time or add such restrictions as predicates in the

clause of the query.


Syntax:


material


WHERE


complies with


ACTOR FUNCTION material


complieswith (standard REF (Standards))


RETURNS material;


RETURN SELECT


conformance, manufacturer, product


FROM Materials

WHERE conformance = standard

END FUNCTION


Sample Query:


Given a data base Division


Finishes with an instance acousticalCeilingTile,


list all ceiling tiles that comply with ASTM standard E 1264.








FROM Division


Finishes


WHERE material complies with (ASTM E 1264)


Query Result:


Conformance


Manufacturer


Product


ASTM E 1264

ASTM E 1264

ASTM E 1264


Armstrong

Armstrong

Mannington


ACT-22M

ACT-28R

MCT0800


In both of the previous models standard refers to an instance of the object


type Standard, which is the class of construction standards.


Likewise, material is an


instance of the object type


Material


which


is the class of construction


materials.


Obviously,


depending


on the


particular subclass


materials


or standards


being


referenced


query,


more specificity would


used


order


access


appropriate data base. For example, if an architect was creating project specifications

for Division 8, Doors and Windows, the material object might be replaced with a


specific instance woodDoor of the object type Doors.


Therefore the models must be


expressly tailored to meet the informational needs of the user.


5.1.5 tested


per test


third construction-specific SQL extension, testedper test,


provides the


capability of determining whether a specified product has been subjected to a given








proper performance in the case of fire.


S7

Similarly, the project owner might have a


desire to incorporate a particular manufacturer's product with which he has had a


high degree of success on previous projects.


This function would allow the project


owner to determine whether his chosen products met the requirements of the product

testing specified by the designer.


Syntax:


tested per test


ACTOR FUNCTION tested


per test (manufacturer, material REF (Materials))


RETURNS testing


RETURN SELECT


manufacturer, product, test


FROM Materials

WHERE product = material

END FUNCTION


Sample Query:


Given


data


base


Division


Doors& Windows


with


instance


fireResistantWireGlass, list the test, if any, against which ASG Industries' wire

glass has been tested.


Query Form:

SELECT *








Query Result:


Manufacturer


Product


Test


ASG Industries


Wire glass


UL9


5.1.6 material tested per


syntax.


The fourth extension, materialtestedper, is a variation of the tested jertest

However, this implementation returns the material or materials that have


passed a particular test rather than returning the test against which the material has


been


tested.


The application


here is clear in


that during the bidding phase of a


construction project, a contractor could search a construction data base, retrieving


all materials of a given type that have passed the tests specified by the designer.


This


process would allow the contractor to maximize his profit on the material costs of a

job by incorporating materials of equal quality but lower cost than those specified by

the architect/engineer.

Syntax:


material


tested per


ACTOR FUNCTION material


_tested yer (test)


RETURNS material;

RETURN SELECT test, manufacturer, product

FROM Materials

WHERE test = test








Given


data


base


Division


Doors&Windows


with


instance


fireResistantWireGlass, list all wire glass products and their manufacturers

that have been tested against UL 9.


Query Form:

SELECT *


FROM Division


WHERE material


Doors&Windows


testedper (UL 9)


Query Result:


Test


Manufacturer


Product


UL9

UL9


ASG Industries

Hordis Brothers


Wire glass

Wire glass


5.1.7 meets or exceeds

Another material constraint common to project specifications is that a certain


attribute of a product must meet or exceed a given value.


To address this need, the


CI-SQL function meets or


exceeds has been developed.


When passed the parameters


product


type,


attribute,


value,


function


returns


products


whose


specified attribute is greater than or equal to the specified value.


project architect commonly specifies that a building's


As an example, the


heating and air conditioning


system must have an EER (energy efficiency ratio) that meets or exceeds a given








meets or exceeds


ACTOR FUNCTION meets_or exceeds (attribute, value)

RETURNS material;


RETURN SELECT


manufacturer, product, attribute, value


FROM Materials


WHERE attribute


value


END FUNCTION


Sample Query:


Given a data base Division


Mechanical with an instance fanMotor, list all


fan motors whose efficiencies meet or exceed 81%.


Query Form:

SELECT *


FROM Division

WHERE meets


Mechanical


or exceeds (efficiency, .81)


Query Result:


Manufacturer


Product


Attribute


Value


Marathon

US Motors

GE


FM2710

215EFM

EF2117G


efficiency

efficiency

efficiency






61
5.1.8 Sample Application

In the previous sections of this chapter, the individual extensions of CI-SQL


have been detailed.


The purpose of this section is to demonstrate the use of several


of these extensions in a combined form.


This demonstration will be designed to


mimic an actual query that might be required in industry practice.


It should be noted


that in a total solution a user interface would hide much of the query construction


from


user.


Again,


data


base


that would


used


for this query


is only


hypothetical and an actual industry data base might differ significantly depending on

the input obtained from product manufacturers and specifiers.



Sample Scenario: The chief estimator for XYZ Construction Company is bidding on


expansion


Liberty


Elementary


School


Hawthorne,


Florida.


determined


from


project


drawings


sixteen


wood


doors


have


to be


supplied


wing


school.


These


doors


must


have


following


characteristics:


Solid Core
1-3/4" Thick
3'0" Wide
AWI Grade III
Cost less than $250.00 each
Primer finish
Comply with ASTM E 221
Tested per ASTM E 81-S
Fire rating that meets or exceeds 1-1/2 hours
) Acceptable manufacturers are:
a. Allied Wood Products








Based


on this


information


estimator constructs


a query


to search


WoodDoors data base for all doors matching the above specifications.


The query is


structured as shown below.


SELECT


manufacturer, model, cost


FROM WoodDoor


WHERE


core = 'Solid'


AND


thickness = '1-3/4"'


width


= '3'-0"'


_grade =


AND


AND


AND


< 250 AND


finish = 'primer'

complies_with _st


material


AND


indard (ASTM E 221) AND


tested_per (ASTM E 81-S) AND


meets_or exceeds (fire


rating, 1.5) AND


manufacturer = 'Allied Wood Products' OR


'TreeCo Corporation'


'Wood Builders Products'


'Florida Door'


quantity_c

ORDER BY


AND


in hand


manufacturer, model:








Manufacturer


Model


Cost


Allied Wood Products

Allied Wood Products

Allied Wood Products


JL 211-2

JL 212-5

JL 297-5


228.50

244.65

249.85


TreeCo Corporation


TCWD


1156


219.38


Wood Builders Products

Wood Builders Products


W-8972Z

W-8978Z


249.99

235.75


The primary benefit to such query capabilities would be that if a "live" data


base


were


maintained


local


or even


national


suppliers,


architects


contractors would have computerized access to detailed product information.


Other


beneficial options could be added to the system such as the ability to provide cut

sheets, installation information, and pricing structures that depend upon passwords.


Clearly, the level of sophistication inherent in such a system is almost limitless.


cost of such sophistication could be supported by both the manufacturers, who have


an obvious financial interest, and the construction professionals,


who need timely


access to large amounts of data.


5.2 Construction Scheduling Domain

The second major domain of construction-specific SQL extensions has been


developed to address query needs in area of construction project scheduling.


Like








form an activity must have a name, a start date, a finish date, and a duration.


Using


these properties it becomes possible to determine a project's expected completion


date.


Again, the purpose of this research is not to provide a tutorial on the many


facets


construction


scheduling


using


Critical


Path


Method


(CPM)


or the


Project


Evaluation


Review


Technique


(PERT),


rather


develop


construction-specific


extensions


to the


relational


data


base query


language


SQL.


With this purpose clarified the following section will develop these extensions in the


following format:


first,


generic structure of


an appropriate


data base will be


described;


second,


a review


basic


scheduling


concepts


necessary


extensions


described;


third,


assumptions


necessary


creating


extensions will be explained; fourth, for each CI-SQL extension a description of the


function will be provided as well as the need or motivation for its development;


fifth,


the exact syntax will be detailed; next, a sample query will be created followed by the

formal representation of the query; finally, the result of the query will be provided.


5.2.1 Basic Scheduling Concepts


Regardless


which


planning


method


is used,


CPM


or PERT


several


commonalities exist between them.

defined for the entire project. Sec


be determined.


First, all significant activities or tasks must be


ond, the relationships among the activities must


Third, a network diagram is drawn, assigning numbers to the activity


nodes and connecting the activities to graphically represent their relationship to each








627).


"Although PERT


and CPM are similar in their basic approach, they do differ


in the way activity times are estimated" (Render, 1988, p. 627).


In the PERT method


a probabilistic approach is used and three time estimates are calculated to determine


the expected activity duration and its variance.


CPM, however, uses a deterministic


method


to assign


activity


durations.


Using


historical


data


duration


called


normal time is assigned to each activity.


This normal time is the amount of time,


under normal circumstances, that it should take to complete the task.

In planning a construction project the process begins by simplifying the entire


project


units,


or activities,


which


represent


individual


work


phases.


macroscopic level


microscopic level


these units may represent major phases of the project,


may embody very small, specific steps in


or at a


the construction


process.


The order of these activities represents the logical order of construction.


At this point if the project planner is using arrow diagrams to represent the project,

an arrow diagram is constructed diagramming the relationships between the many


activities (see Figure


* I








order


to place


activity


arrows


project


proper


sequence,


necessary to answer three questions for each arrow:

1. What arrows (activities) must precede this one?


What


arrows


(activities)


can


concurrent


with


one?


concurrent, activities must start from


the same point or finish at the same


point.


What arrows (activities) must succeed this one?" (Cox, 1986, p. 9).


By answering these three important questions for each activity, the arrow diagram can


be constructed, and the relationships among the activities become explicit.


In order


to allow computerized analysis of the construction schedule, numbered event nodes


are implemented.


This


numbering


system,


often


referred


as an activity's


number, thus permits a computer to identify all activities that have a common starting


or ending point.


The procedure


for creating the


numbers for the activities is


accomplished by beginning with the first activity and assigning numbers to the activity


nodes in ascending order.


This operation proceeds from left to right skipping an


arbitrary number of integers between each node in order to provide space for future


expansion of the network.


For example, the starting node might be numbered 1,


while the next node to the right might be numbered


This method proceeds until


all activities have a unique i-j number that can later be used to identify individual

activities (see Figure 13).
























Figure 13 Arrow Diagram with i-j Numbers


created and to limit the scope of the research.


For the construction scheduling


extensions, it is important to note that several fundamental and significant differences

exist between the CI-SQL functions that will be detailed next and the features already


available


commercial scheduling


packages


such


as Primavera Project


Planner.


First, in the case of commercial packages,


there is no industry accepted standard.


Second,


implementation of specific features varies widely


from


one software


product to the next and may be extremely complex in some cases.


Third, many of


products


currently


available


prohibitive


many


construction


professionals.


Conversely, with the implementation of many desirable and industry-


specific


functions


implemented into SQL,


several


distinct advantages exist.


ability


to rely


upon


a widely accepted,


world-wide


standard


for relational


query


languages is comforting at least.


Also, the implementation of these functions follows








1) The intent of adding scheduling functions to SQL is not to replace commercial

scheduling packages but rather to provide industry-specific facilities, in a way that has

never been done before, to the eminent relational query language.


is acknowledged


many


other


features


are both


possible


desirable and warrant further research.

3) This work represents a conceptual model upon which full-featured products could

be developed.


4) All examples, unless otherwise noted, are based upon the PERT


system of project


management.

With these assumptions in place, the following sections describe the functions, their

syntax, and demonstrate sample queries.


5.2.3 General Scheduling Data Base Structure


Although


not nearly


as complex


as the


construction


materials


data


base


described earlier, a data base that would properly manage project scheduling data has


several important features.


First, each activity would be required to have a name or


number that could uniquely identify it. In strict relational terms the activity_name

column would be the primary key for this data base. Two additional columns, i# and

j# would contain the i-j number of each activity. The second and third columns

would thus characterize the relationships among activities. At this point, depending

on whether the data base were intended to support CPM or PERT, the design would








scheduling, this duration probably would come from a company's historical data.


PERT


projects, however, which is a probabilistic method, the fourth, fifth, and sixth


columns would


slightly


different.


fourth


column,


would


receive


optimistic time estimate, or the time an activity will take to complete if everything


goes as well as possible.


Because this value is based on near perfect conditions, the


probability that the activity would be completed in this amount of time should be very


small.


The fifth column, Tm would contain the most likely time estimate, or the most


realistic time to complete the activity.


The probability associated with this column's


value should be high since this is the most likely time for completion of an activity.


The sixth column,


T,, would hold the pessimistic time estimate, that is, the time an


activity would take given very unfavorable conditions. The probability associated with

this value, like the optimistic time should be very small. This data base layout is


illustrated


Figure


no means does


example cover


information


applicable to a construction scheduling data base.


However,


it does provide


necessary basis for the development of the construction-specific extensions to SQL


that will be enumerated in


subsequent sections.


5.2.4 concurrent activities


first SQL extension created for construction


project scheduling is


concurrent


activities


function


would


retrieve


project


activities


occur


concurrently with the given activity. To be consi


dered concurrent, activities must




























Figure 14 Construction Scheduling Data Base Layout


provides the user with all activities that commenced at the same time as the activity


in question.


example,


if the


project


manager


wanted


to determine,


coordinating site access, if the crane operator and the concrete pumper would be

arriving on the same day, he could retrieve all records that begin at the same time


the crane operator arrives.


The following syntax extension shows how this function


is defined by using the activity name in question as the parameter or argument for

the function and returning the concurrent activity or activities.


Syntax:


concurrent activities


ACTOR FUNCTION concurrent activities (activity


name)








FROM Project


WHERE i# = activity


name.i#


OR j# = activity_name.j#

END FUNCTION


Sample Query:

Given a project data base, P1, list all activities that are concurrent with the


activity plumbingrough


Query Form:

SELECT *

FROM P1


WHERE concurrent activities (plumbing_


rough


Query Result:


Activity name

plumbing_rough in


Duration


grading


lumber


delivery


5.2.5 preceding activities

The second CI-SQL function created for retrieving construction scheduling


information is the precedingactivities function.


This function will retrieve all activities






72
activities that had to be completed before the brick mason could begin laying the


brick veneer for the front of an office complex.

would provide the manager with this information.


The preceding_activities function

The syntax extension given below


shows how this function is defined by using the activity name


in question as the


argument for preceding_activities and returning the preceding activity or activities.


Syntax:


preceding _activities


ACTOR FUNCTION preceding activities (activity_


name)


RETURNS activity or activities;


RETURN SELECT


activity name, i#, j#, duration


FROM Project


WHERE j# = activity_


name.i#


END FUNCTION


Sample Query:


Given


a project


data


base,


activities


precede


activity


test electrical


system.


Query Form:

SELECT *

FROM P1








Activity name


test electrical


Duration


system


electrical roughin


temp service


_hookup


5.2.6 succeeding activities


The succeeding activities function


is designed


to retrieve


activities


follow a given activity.


If activity


2 succeeds activity


1, activity


2 cannot begin until


the completion of activity


The succeeding activities function is best illustrated


through its use in determining which project activities dictate the commencement of


particular


task.


example,


if the


construction


manager


desired


see all


activities that could not begin until the plumbing subcontractor completed his work,


he could retrieve all activities that succeed the plumbing operation.


The following


example illustrates the use of this function.


Syntax:


succeeding activities


ACTOR FUNCTION succeeding activities (activity name)


RETURNS activity or activities;


RETURN SELECT


activity_


name, i#, j#, duration


FROM Project

WHERE i# = activity name.ji#






74

Given a project data base, P1, list all activities that are dependent upon the

completion of the drywall crew's work in order to begin.


Query Form:

SELECT *

FROM P1


WHERE succeeding activities (hang and


finish


drywall)


Query Result:


Activity


hang_


name


finish


Duration


drywall


hang_wallpaper


paintdrywall


5.2.7 T.


As mentioned earlier, the PERT


scheduling system utilizes probabilities to


determine


straightforward,


expected

there is


durations


currently


activities.


no provision


Although


such


process


calculations


SQL.


Therefore,


function,


desired


functionality


to the


CI-SQL


vocabulary.


basic premise behind the


PERT


method of determining activity


durations is that activity duration estimates will follow a beta probability distribution.








Expected Time =


where


= [(a + 4m + b)/6]2


= optimistic time


= T


= most likely time


= pessimistic time.


By adding the functionality of this formula to the builtin capabilities of CI-SQL an


activity's expected time for completion can be determined.


The implementation of


function,


however,


is somewhat


different from


previous


functions.


function is implemented similarly to the aggregate functions such as SUM, MIN, and


MAX already provided in SQL, in that it is placed in the SELECT clause.


However,


unlike aggregate functions, which are applied to an entire column and return a single


value, the


T, function's argument can be a single activity.


Therefore, this function


can be grouped with an individual value column without GROUPing on that column.


functionality


might


appear


to violate


SQL


aggregate


functions and


individual value columns cannot be


mixed in


the SELECT


clause.


However, it is important to remember that this function is not actually an aggregate


function since it does not return a single value from an entire column of values.


reason for this is obvious, in that, attempting to find Te for an entire column would

not only be illogical but would not be possible because the argument for this function


is a tuple rather than a column.


The ensuing explanation shows how this function


calculates


an activity's


expected


completion


time


when


passed


parameter


T,













ACTOR FUNCTION T, (activity)

RETURNS Te;


RETURN SELECT


activity


name, T, (activity_


name)


FROM Project


WHERE activity_


name = activity_name


END FUNCTION


The T, function would be internally defined as:


= [activity_name.To


+ (4*activity_name.T,) + activity_name.Tp]/6


in the same way that the AVG, MIN, and MAX functions are currently internally

defined for SQL.


Sample Query:

Given a project data base, P1, calculate the expected time for the activity

pour foundations.


Query Form:

SELECT T7(pour foundations)

FROM P1


Query Result:








5.2.8 V


In addition to determining the expected time an activity will take to complete,


part of the PERT process is determining the variance of each activity.


The dispersion


or variance of an activity's time estimate is calculated as:


Variance = V


where


= [(b- a)/6]2


= pessimistic time.


= optimistic time


By providing this capability as an internally defined CI-SQL function, an activity's

variance can be calculated.

Like the T, function the variance function also behaves much like an aggregate

function in that it too is placed in the select clause since it calculates a value that may


not be explicitly contained in the data base.


Since it is not summing or averaging all


the values in a given column, this function is not bound by the same constraints as


true aggregate function.


The explanation


that follows develops the syntax and


implementation of the variance function.


Syntax:

V ::=

ACTOR FUNCTION V (activity)

RETURNS V;


RETURN SELECT


activity name, V (activity_


name)






78

The V function would be internally defined as:


= [(activity


name.T


- activity_


name.T,)/6]2


in the same way that the AVG, MIN, and MAX functions are currently internally

defined for SQL.


Sample Query:


Given


a project


data


base,


calculate


variance


activity


excavate


foundations.


Query Form:


SELECT V(excavate

FROM P1


Query Result:

Activity name


foundations)


excavate


foundations


5.3 Summary

This chapter has set forth the specific content of several crucial extensions to


the powerful relational query language, SQL.


Two fundamental areas of construction


information management


have


been addressed.


Extensions


directed


at both


selection of construction materials and the retrieval of scheduling information have


been created.


In both cases the


intent of the


research has been


to develop








only


SQL


functions


can and


should


developed


construction


industry,


the work


represented


this chapter begins an


entirely


new process


construction research.














CHAPTER 6
CONCLUSIONS


This chapter will summarize the dissertation and its results.


Included in this


summary will


be a synopsis of the


CI-SQL


functions


have


been


created


enhance


information


management


capabilities


construction


industry.


Impediments and


problems that were encountered


design


process will


addressed, as well as the insights gained in the extension procedure.


An examination


of future research possibilities and further extensions in construction industry usage

will conclude the chapter.


6.1 Summary of Dissertation


extending


ANSI'


Structured


Query


Language


necessitated a


review of the history and background of SQL.


E.F. Codd's contributions in this area


were particularly important in


that he


has set the stage


for over twenty years of


relational


query


language


development.


Although


without


criticism,


principles, embodied in the relational model have withstood examination and scrutiny


from all angles.


It is this solid foundation upon which SQL was designed.


Having


become an industry standard, SQL has continued to mature through the correction

of some of its weaknesses and proposed improvements such as the addition of object






81

To provide a clear understanding of the basic principles and structure of SQL,


review


primary


functions


syntax


presented.


SQL's


relational


operators,


keys


keywords


provided


basis


further


development in


Chapter 4.


Although previous extensions to SQL have been undertaken, none has


addressed the unique needs and vernacular of the construction industry.


Chapter 4


focused on these distinct requirements and discussed the use of object data typing,

object attributes, and user-defined functions as they related to the CI-SQL extensions.

After developing the syntax conventions to be utilized in the extensions, two primary


domains were examined.


In the construction materials domain, five extensions were


created that directly confront the selection of materials during the design and bidding


phases of the construction process.


In the second domain, construction scheduling,


an additional five extensions were developed that are applicable to the retrieval of


scheduling information.


By providing a model


for the


evolution


construction


industry-specific SQL extensions in these two domains, the application of computers

for managing construction information has been advanced.


6.2 Results


primary


results


research


are twofold.


First,


several


specific


extensions were created which afford


other related functions.


the potential for the development of many


This expansion clearly could be cultivated into a robust


collection


construction


applications.


Second,


development


process


itself








6.2.1 Functions Created


In all, ten new CI-SQL functions were created from this research.


Five of


these functions are in the domain of construction materials, and five can be utilized


in construction scheduling.


The following is a list of those functions with a brief


description of each:


complies with standard


- retrieves


standards


to which


a particular


product conforms


material complieswith


- returns


materials


meet


given


standard

3) testedfper_test determines whether a specified product has been tested

against a given test


4) material


_tested per


- returns the material or materials that have passed a


particular test


5) meets


exceeds


- retrieves all products whose specified attribute is greater


than or equal to the specified value


6) concurrent activities


- retrieves all project activities that occur concurrently


with the given activity

7) preceding activities retrieves all activities that precede a given activity

8) succeeding activities retrieves all activities that follow a given activity


- determines the expected


duration


an activity based


upon


three


estimates of its duration






83

These functions provide the basis for the development of a comprehensive CI-

SQL that could address all the query needs of a complete construction data base.

As SQL3 enters the data base environment many other desirable features can be

incorporated into CI-SQL that will provide additional benefits to the construction

industry in the form of increased efficiency and productivity.


6.2.2 Impediments and Problems


primary


difficulty


encountered


developing


construction-specific


extensions to SQL was remaining within the confines of the SQL construct.


Because


SQL is not a true programming language many of the facilities commonly available


in fourth-generation languages (4GL) are not present in SQL.

so much a criticism as it is merely a statement of fact. Thi


This statement is not


s truth translated into


some difficulty in designing features that are easily obtainable in a 4GL, but have not


been


implemented in SQL.


key circumstance in


that this occurred was the


attempt to establish extensions that made use of date/time functions.


In order to


fully provide construction-specific SQL extensions in the area of project scheduling,


the ability for SQL to handle date/time math is mandatory.


no such facility.


At present SQL provides


For this reason it is important to address the issue of date/time


support necessary for many desired extensions. Based on C.J. Date's work on defining

data types, several interdependent steps are necessary to provide adequate support


for this function.


Initially, the concept of an interval should be defined such that an








that the computer can manipulate.


Date suggests that several built-in conversion


functions should be created to perform these operations.


He describes the following


as being desirable:


1) INTERVAL


CHAR and CHAR


TO INTERVAL


- These functions would


"convert a specified interval to some standard external representation of an interval

(e.g., a character string of the form d:h:m:s, with a leading minus sign if the interval

is negative), and vice versa" (Date, 1990, p. 66), where d represents days, h hours, m


minutes,


s seconds.


Date's


recommended


functions,


representation would only need to contain the level of detail desired by the user.


example, in construction scheduling most projects are not scheduled to include hours,


minutes, and seconds.


Therefore, these values would be either truncated or assigned


zeros as placeholders.


2) DATE


CHAR and CHAR


DATE


- These functions would convert a


conventional


calendar


date


form


such


as y:n:d:h:m:s


represents years,


months, days, hours, minutes, and seconds respectively.


The reverse of this function


could also be performed, converting a y:n:d:h:m:s representation to a date.


INTERVAL


NUM


NUM TO


INTERVAL


- These


functions would


convert intervals to numbers, and vice versa, with NUM representing the number of

specified time units in the given interval.


DAY


WEEK


- This function would provide the ability to convert a given


interval


to a number representing the day of the week,


where


= Sunday, 2








DAY


YEAR


- This function would provide


the ability to convert a given


interval to a number representing the day of the year with acceptable values being

between 1 and 366.


WEEK


YEAR


- This function would provide the ability to convert a given


interval to a number representing the week of the year with acceptable values being


between 1 and


addition


providing


built-in


conversion


functions,


standard


comparisons (<,


=- <=, >=


, and <>) and mathematical operators (+,


*. and


/) would also be supported.


The use of the mathematical operators would allow such


operations as the addition of two intervals to obtain a third interval, the subtraction

of two intervals to obtain a third interval, and the multiplication of an interval by an


integer to obtain a new interval.


Based on Date's recommendations all the following


would be valid date/time expressions under his suggested extensions.

SELECT *

FROM Emp


WHERE review


date


< CHAR


DATE (:date


_parameter);


SELECT MIN (birth


date)


FROM Emp;



UPDATE Emp








Although SQL currently does not support such


functionality,


according to


GE's


Donald


Deutsch,


chairman


ANSI's


X3H2


data


base


committee, similar


capabilities will be available in the soon to be released SQL2.


When this revision


becomes available CI-SQL will be further expandable to encompass many scheduling

functions that cannot be accomplished without full-featured date/time capabilities.


6.3 Implications


Since this research focused as much on a process as it did a product,


implications of the research are open-ended.


Viewed in a positive way, this provides


many opportunities for the future expansion of SQL into almost any industry-specific


application.


As quickly as SQL is being modified CI-SQL is clearly open for further


extensions.


6.3.1 Future Research


This research should be viewed as the first of multiple passes necessary to


create a complete and full-featured construction-specific superset of SQL.


One of


the additional passes necessary to providing a complete version of CI-SQL would be


the expansion of construction-specific vocabulary.


The development of many other


pertinent construction vocabulary words could be achieved through the creation of


a consortium


design


professionals,


construction


management


personnel,


construction materials experts.


The goals of such a consortium should be aimed at


developing all vocabulary that would increase productivity by eliminating much of the








professionals can be greatly enhanced.


Given


the fact that other industries


have


proprietary vocabularies, the procedure used in this research could be replicated in


almost


other


industry.


Each


time


new


revision


SQL


released,


incorporating additional functions and desired features, CI-SQL and other industry-


specific extensions can be taken through another iteration of development.


The short


time span


that apparently will


exist between


publication


of SQL2 and SQL3


means that it is possible for industry-specific development to experience a similar

acceleration.


6.3.2 Future Construction Use


Clearly, future construction use will depend on the future research and release


of SQL products.


For example, once date/time functions are supported by SQL,


construction applications such as calculating expected material delivery dates will be


feasible.


Also, many scheduling operations such as calculating activity float times,


and projected project completion dates will be possible.


Other construction activities


such as cost estimating and bid analysis should be examined for applicable uses and

extensions of SQL.

In addition to building CI-SQL based on future research, extensive data base

development would be required in order to provide the construction industry with


critical data such as pricing and availability of products.


The development of such


data bases would depend largely upon the participation of manufacturing groups to








include such fields as size, swing,


thickness, material, lites, style, and


rating.


Characteristics such as these would allow an architect or contractor to specify the


values for each of these attributes that uniquely identify doors.


Thus, participation


on the


manufacturers would


be crucial


to the


success


of building


large


construction materials data bases that could be used by the construction industry to

increase productivity.













APPENDIX A
ACTUAL PROJECT SPECIFICATIONS

Note: Construction vocabulary is indicated by boldface type.

Example 1
SECTION 08110
STEEL DOORS AND FRAMES


PART 1


- GENERAL


DESCRIPTION OF WORK


Finish Hardware: Refer to Section 80710.
Painting: Refer to Section 09900.
Colors: Refer to Section 09000.


QUALITY


ASSURANCE:


A. Provide doors and frames complying with Steel Door Institute
Recommended Specifications: Standard Steel Doors and Frames" (SDI-
100) and as herein specified.
1. Fire-Rated Door Assemblies: Where fire-rated door assemblies are
indicated or required, provide fire-rated door and frame assemblies that
comply with NFPA 80 "Standard for Fire Doors and Windows", and
have been tested, listed, and labeled in accordance with ASTM E 152
"Standard Methods of Fire Tests of Door Assemblies" by a nationally
recognized independent testing and inspection agency acceptable to
authorities having jurisdiction.


SUBMITTALS:


A. Product Data:


Submit seven (7) copies of manufacturer's


technical product


data substantiating that products


Shoj
steel


Drawings: Submit seven (
I doors and frames. Include


s comply with requirements.
7) copies of fabrication and installation of
e details of each frame type, elevations of








DELIVERY


STORAGE AND HANDLING:


A. Deliver hollow metal work cartoned or crated to provide protection during


transit and


job storage.


Provide


additional


sealed


plastic wrapping for


factory finished doors.


Inspect hollow metal work upon delivery for damage. Minor damages may
be repaired provided finish items are equal in all respects to new work and
acceptable to the Architect; otherwise, remove and replace damaged items
as directed.


C. Store doors and


frames at


building site


under cover


protect


from


damage.


PART


- PRODUCTS


ACCEPTABLE MANUFACTURERS:


Manufacturers: Subject to compliance with requirements, manufacturers
offering steel doors and frames which may be incorporated in the work
include, but are not limited to, the following:


Allied Steel Products, Inc.
Ceco corporation


Republic Builders Prod.


Corporation


4. Firedoor Corporation of Florida.

MATERIALS:


A. Hot-Rolled


Steel


sheets


Strip:


Commercial


Quality


carbon


steel,


pickled and oiled, complying with ASTM A 569 and ASTM A 568.


B. Cold-Rolled Steel Sheets:


Commercial quality carbon steel, complying with


ASTM A 366 and ASTM A 568.


Galvanized Steel Sheets:


Zinc-coated carbon steel sheets of commercial


quality,


complying


with


ASTM


with


ASTM


G60


coating, mill phosphatized.








Example


SECTION 15001


MOTORS

A. Refer to the equipment schedules and specification sections for specific
voltages required.


B. All motors shall be


1750 RPM, unless otherwise noted.


NEMA


Design


motors,


normal


starting


torque


with


Class


insulation


unless specified


otherwise


or unless


manufacturer of the


equipment on which the motor is being used has different requirements.
All motors to have a 1.15 service factor.


D. All motors 1 HP and larger, except specially wound motors, shall be high
efficiency design. Nominal efficiency of each high efficiency motor shall


meet or exceed


the value


listed


below when


tested


in accordance


with


NEMA MG 1-12.53a and 1-12.53b.


HP 1200 rpm


1800 rpm

82
82
84
85
87


3600 rpm

80
81
82
82
85


7 1/2 89 88 87
10 89 89 88
15 90 91 89
20 91 91 91
25 91 92 91
30 91 92 91
40 91 92 91
50 91 92 91

E. All equipment furnished under this contract utilizing a combined electrical
load of greater than 100 Watts shall have a power factor of not less than






92
F. Single phase motors for hard starting applications including outdoor
applications single phase operation shall be capacitor start type, motors for
hard starting applications including outdoor applications. Motors for fans
and pumps located indoor may be split phase or permanent split-capacitor.
Motors shall be equipped with permanently lubricated and sealed ball
bearings, and shall be selected for quiet operation. Motors 1/8 HP and
below may be shaded pole type with permanently oiled unit bearings.

G. Unless otherwise indicated, two speed motors to be one winding,
consequent pole, variable torque type.

Part 3 EXECUTION

3.1 INSTALLATION

A. Install materials in accordance with details, approved shop drawings, and
manufacturers' instructions.

END OF SECTION 15001