article

Free access

Query processing techniques in the summary-table-by-example database query language

Editor: Gio Wiederhold Authors:

Gultekin Özsoyoğlu,

Meral ÖzsoyoğluAuthors Info & Claims

ACM Transactions on Database Systems (TODS), Volume 14, Issue 4

Pages 526 - 573

https://doi.org/10.1145/76902.76906

Published: 01 December 1989 Publication History

Abstract

Summary-Table-by-Example (STBE) is a graphical language suitable for statistical database applications. STBE queries have a hierarchical subquery structure and manipulate summary tables and relations with set-valued attributes.

The hierarchical arrangement of STBE queries naturally implies a tuple-by-tuple subquery evaluation strategy (similar to the nested loops join implementation technique) which may not be the best query processing strategy. In this paper we discuss the query processing techniques used in STBE. We first convert an STBE query into an “extended” relational algebra (ERA) expression. Two transformations are introduced to remove the hierarchical arrangement of subqueries so that query optimization is possible. To solve the “empty partition” problem of aggregate function evaluation, directional join (one-sided outer-join) is utilized. We give the algebraic properties of the ERA operators to obtain an “improved” ERA expression. Finally we briefly discuss the generation of alternative implementations of a given ERA expression.

STBE is implemented in a prototype statistical database management system. We discuss the STBE-related features of the implemented system.

References

[1]

ABDUL-QADER, W., AND 0ZSOYO(~LU, G. A human factors study of two screen oriented query languages: STBE and QBE. Tech. Rep., Case Western Reserve Univ., Cleveland, Ohio, 1987.

[2]

AHO, A., HOPCROFT, J., AND ULLMAN, J. The Design and Analysis of Computer Algorithms. Addison-Wesley, Reading, Mass., 1976.

Digital Library

[3]

CHAN, P., AND SHOSHANI, A. SUBJECT: A dictionary driven system for organizing and accessing large statistical databases. In Proceedings of the 6th International Very Large Data Bases Conference (Montreal, Oct. 1980).

[4]

CODD, E.F. Relational completeness of database sublanguages. In Database Systems, Rustin, R., Ed. Prentice-Hall, Englewood Cliffs, N.J., 1972.

[5]

CODD, E.F. Extending the database relational model to capture more meaning. ACM Trans. Databse Syst. 4, 4 (1979)

Digital Library

[6]

DATTA, A. Implementation and performance analysis of STBE. M.S. thesis, Dept. of Computer Eng. and Science, Case Western Reserve Univ., Cleveland, Ohio, May 1986.

[7]

DATTA, A., FOURNIER, S., HOU, W-C., AND 0ZSOYO(~LU, G. The design and implementation of the SSDB. In Proceedings of the 3rd International Workshop on Statistical and Scientific Database Management (Luxembourg, July 1986).

Digital Library

[8]

FOURNIER, B. A display manager for the STBE query language. M.S. thesis, Dept. of Computer Eng. and Science, Case Western Reserve Univ., Cleveland, Ohio, Sept. 1985.

[9]

GELDER, A., AND TOPOR, R.W. Safety and correct translation of relational calculus formulas. In Proceedings of the ACM PODS Conference (San Diego, Calif., Mar. 1987). ACM, New York, 1987.

Digital Library

[10]

GHOSH, S. Statistical relational tables for database management. IBM Research Rep. RJ 4394. IBM, Almaden Research, San Jose, Calif., Aug. 1984.

[11]

HOU, W.-C. The implementation of the extended relational database management system. M.S. thesis, Dept. of Computer Eng. and Science, Case Western Reserve Univ., Cleveland, Ohio, Jan. 1985.

[12]

IKEDA, H., AND KOBAYASH!, Y. Additional facilities of a conventional DBM~ to ~uppo_rt interactive statistical analysis. In Proceedings o/the First LBL Workshop on Statistical Database Management (Menlo Park, Calif., Dec. 1981).

Digital Library

[13]

JOHNSON, R. Modeling summary data. In Proceedings of the ACM SIGMOD Conference (Ann Arbor, Mich., Apr. 1981). ACM, New York, 1981.

Digital Library

[14]

KIM, W. On optimizing an SQL-like nested query. ACM Trans. Database Syst. 7, 3 (1982).

Digital Library

[15]

KLUG, A. Abe--a query language for constructing aggregates-by-example. In Proceedings of the First LBL Workshop on Statistical Database Management (Menlo Park, Calif., 1981).

Digital Library

[16]

KLUG, A. Equivalence of relational algebra and relational calculus query languages having aggregate functions. J. ACM (1982).

Digital Library

[17]

KLUG, A. Access paths in the Abe statistical query facility. In Proceedings of the ACM SIGMOD

Digital Library

[18]

Proceedings of the First LBL Workshop on Statistical Database Management (Menlo Park, Calif., Dec. 1981).

[19]

Prne~dlngs of the Seennzt International Workshop on,Stnti_Qtle,l Dntnhn_~ Mnn.ngement (Los Altos, Calif., Sept. 1983).

[20]

LORIE, R. A., AND NILSON, J.F. An access specification language for a relational database system. IBM J. Res. Dev. 3 (1979).

[21]

MARIN, G. Efficient implementation of the extended relational algebra operators. M.S. thesis, Dept. of Computer Eng. and Science, Case Western Reserve Univ., Cleveland, Ohio, July 1985.

[22]

MATA, F.J. Physical organization techniques for set-valued relations and summary tables. M.S. thesis, Dept. of Computer Eng. and Science, Case Western Reserve Univ., Cleveland, Ohio, May 1984.

[23]

MATOS, V. Extensions to the relational data model for statistical database applications. Ph.D. thesis, Dept. of Computer Eng. and Science, Case Western Reserve Univ., Cleveland, Ohio, Sept. 1985.

Digital Library

[24]

MCCARTHY, J.L. Metadata management for large statistical databases. In Proceedings of the Conference on Very Large Data Bases (Mexico City, 1982)

Digital Library

[25]

NICOLAS, J.-M., AND DEMOLOMBE, R. On the stability of relational queries. Tech. Rep. ONERA-CERT, 1982.

[26]

OLKEN, F. How baroque should a statistical database management system be? In Proceedings of the Second International Workshop on Statistical Database Management (Los Altos, Calif., Sept. 1983).

Digital Library

[27]

0zsoYOdLU, G., 0zsoYodLu, Z. M., AND MATA F. A language and a physical organization technique for summary tables. In Proceedings of the ACM SIGMOD Conference (Austin, Tex., 1985). ACM, New York, 1985, pp. 3-16.

Digital Library

[28]

0ZSOYO(~LU, G., 0ZSOYO(~LU, Z. M., AND MATOS, V. Extending relational algebra and relational calculus with set-valued attributes and aggregate functions. ACM Trans. Database Syst. 12, 4 (Dec. 1987), 566-592.

Digital Library

[29]

OZSOYOGLU, G., AND OZSOYGLU, Z. M. Features of SSDB. In Proceedings of the Second international Workshop on Statistical Database Management (Los Altos, Caii~, Sept, 1983).

Digital Library

[30]

OZSOYOGLU, Z. M., AND OZSOYOGLU, G. An extension of relational algebra for summary tables. In Proceedings of the Second International Workshop on Statistical Database Management (Los

Digital Library

[31]

OZSOYOGLU, Z. M., AND OZSOYOGLU, G. STBE--A database query language for manipulating summary data. In Proceedings of the IEEE COMPDEC Conference (Los Angeles, 1984). IEEE, New York, 19P,4_

Digital Library

[32]

OZSOYOGLU, G., AND OZSOYOGLU, Z.M. SSDB--An architecture for statistical databases. In Proceedings of the Fourth International JCIT Conference (Jerusalem, May 1984).

[33]

OZSOYOGLU, Z. M., AND OZSOYOGLU, G. A query language for statistical databases. In Query Processing in Database Systems, W. Kim, D. Reiner, and D. Batory, Eds., Springer-Verlag, New York, 1985.

[34]

OZSOYOGLU, G., AND OZSOYOGLU, Z. M. Statistical database query languages. IEEE Trans. Soflw. Eng. (Oct. 1985), 1071-1080.

Digital Library

[35]

OZSOYOGLU, G., AND WANG, H. A relational calculus with set operators, its safety, and graphical languages. IEEE Trans. Softw. Eng. 15, 9 (Sept. 1989), 1038-1052.

Digital Library

[36]

ROSENTHALL, A., AND REINER, D. Extending the algebraic framework of query processing to handle outerjoins. In Proceedings of the Conference on Very Large Data Bases (Singapore, Aug. 1984).

Digital Library

[37]

SATO, H. Handling summary information in a database: Derivability. In Proceedings o{ the ACM SIGMOD Conference (Ann Arbor, Mich., Apr. 1981). ACM, New York, 1981.

Digital Library

[38]

SELINGER, P. G., ASTRAHAN, M. M., CHAMBERLIN, D. D., LORIE, R. A., AND PRICE, T.G. Access path selection in a relational database management system. In Proceedings of the ACM SIGMOD Conference (1979). ACM, New York, 1979.

Digital Library

[39]

SHOSHANI, A. Statistical databases: Characteristics, problems and some solutions. In Proceedings of the Conference on Very Large Data Bases (Mexico City, 1982).

Digital Library

[40]

Su, S., NAVATHE, S. B., AND BATORY, D. Logical and physical modeling of statistical scientific databases. In Proceedings of the Second International Workshop on Statistical Database Management (Los Altos, Calif., Sept. 1983).

Digital Library

[41]

ULLMAN, J. Principles of Database Systems. Computer Science Press, Rockville, Md., 1982.

Digital Library

[42]

Table Producing Language System. Version 5. Bureau of Labor Statistics. Washington, D.C., July 1980.

[43]

ZLOOF, M.M. Query-by-example: A database language. IBM Syst. J. (1977).

[44]

ABI)UL-QADER, WAEL ERAM benchmarking and STBE human factors study. M.S. thesis, Case Western Reserve Univ., Cleveland, Ohio, July 1987.

[45]

JAESCHKE, G., AND SCHEK, H.J. Remarks on the algebra on nonfirst normal form relations. In Proceedings of the ACM PODS Conference (Los Angeles, Mar. 1982). ACM, New York, 1982.

Digital Library

Cited By

Wongsuphasawat KPlaisant CTaieb-Maimon MShneiderman B(2012)Querying event sequences by exact match or similarity searchInteracting with Computers10.1016/j.intcom.2012.01.00324:2(55-68)Online publication date: 1-Mar-2012
https://dl.acm.org/doi/10.1016/j.intcom.2012.01.003
Lakhal LCicchetti R(2011)STAR$^+$, un langage de manipulation de résumés statistiques structurésRAIRO - Operations Research10.1051/ro/199024040365124:4(365-432)Online publication date: 29-Mar-2011
https://doi.org/10.1051/ro/1990240403651
Lorentzos NDondis K(2006)Query by example for nested tablesDatabase and Expert Systems Applications10.1007/BFb0054527(716-725)Online publication date: 26-May-2006
https://doi.org/10.1007/BFb0054527
Show More Cited By

Index Terms

Query processing techniques in the summary-table-by-example database query language

Recommendations

View-based query processing: On the relationship between rewriting, answering and losslessness

As a result of the extensive research in view-based query processing, three notions have been identified as fundamental, namely rewriting, answering, and losslessness. Answering amounts to computing the tuples satisfying the query in all databases ...
A graph query language and its query processing
Exploiting Constraints in Query Processing: Query transformation using interity constraints and views

Reviews

Reviewer: Alan Raymond Hevner

Summary-Table-By-Example (STBE) is a query language designed for use in System-for-Statistical-Databases (SSDB), a research system designed and implemented as a prototype by the authors. The material in this paper concentrates on the translation of summary tables, as defined by a user in a query-by-example format, to queries on a relational database. The authors present an extended relational algebra to support this translation process and the accompanying query optimization process. They rigorously define and prove algebraic properties of language operations. They provide numerous examples to illustrate query processing in STBE. Researchers in query language design and optimization for statistical databases are the audience for this paper. A potential reader should be forewarned, however, that the paper is not a self-contained presentation of SSDB or even STBE. No fewer than 18 papers and theses produced by the authors and their colleagues in the SSDB project are referenced. Interested readers will need to read these references in order to understand the full scope and contributions of the project.

Access critical reviews of Computing literature here

Become a reviewer for Computing Reviews.

Comments

Information & Contributors

Information

Published In

Copyright © 1989 ACM.

Publisher

Association for Computing Machinery

New York, NY, United States

Publication History

Published: 01 December 1989

Published in TODS Volume 14, Issue 4

Permissions

Request permissions for this article.

Request Permissions

Check for updates

Qualifiers

Article

Contributors

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

42
Total Citations
View Citations
854
Total Downloads

Downloads (Last 12 months)92
Downloads (Last 6 weeks)23

Reflects downloads up to 15 Oct 2024

Other Metrics

View Author Metrics

Citations

Cited By

Wongsuphasawat KPlaisant CTaieb-Maimon MShneiderman B(2012)Querying event sequences by exact match or similarity searchInteracting with Computers10.1016/j.intcom.2012.01.00324:2(55-68)Online publication date: 1-Mar-2012
https://dl.acm.org/doi/10.1016/j.intcom.2012.01.003
Lakhal LCicchetti R(2011)STAR$^+$, un langage de manipulation de résumés statistiques structurésRAIRO - Operations Research10.1051/ro/199024040365124:4(365-432)Online publication date: 29-Mar-2011
https://doi.org/10.1051/ro/1990240403651
Lorentzos NDondis K(2006)Query by example for nested tablesDatabase and Expert Systems Applications10.1007/BFb0054527(716-725)Online publication date: 26-May-2006
https://doi.org/10.1007/BFb0054527
Cicchetti RLakhal L(2005)Matrix relation for statistical database managementAdvances in Database Technology — EDBT '9410.1007/3-540-57818-8_39(31-44)Online publication date: 3-Jun-2005
https://doi.org/10.1007/3-540-57818-8_39
Ozsoyoglu GHou WOla A(2005)Database systems for programmable logic controllersStatistical and Scientific Database Management10.1007/3-540-52342-1_28(183-199)Online publication date: 9-Jun-2005
https://doi.org/10.1007/3-540-52342-1_28
Vardaki MPapageorgiou H(2004)An integrated metadata model for statistical data collection and processingProceedings. 16th International Conference on Scientific and Statistical Database Management, 2004.10.1109/SSDM.2004.1311232(363-372)Online publication date: 2004
https://doi.org/10.1109/SSDM.2004.1311232
Balkir NOzsoyoglu GOzsoyoglu Z(2002)A Graphical Query LanguageIEEE Transactions on Knowledge and Data Engineering10.1109/TKDE.2002.103376714:5(955-978)Online publication date: 1-Sep-2002
https://dl.acm.org/doi/10.1109/TKDE.2002.1033767
Aversano LCanfora GDe Lucia AStefanucci S(2002)Understanding SQL through iconic interfacesProceedings 26th Annual International Computer Software and Applications10.1109/CMPSAC.2002.1045084(703-708)Online publication date: 2002
https://doi.org/10.1109/CMPSAC.2002.1045084
Lee TSheng LBozkaya TBalkir NÖzsoyoglu ZÖzsoyoglu G(2002)Querying Multimedia Presentations Based on ContentReadings in Multimedia Computing and Networking10.1016/B978-155860651-7/50122-4(413-437)Online publication date: 2002
https://doi.org/10.1016/B978-155860651-7/50122-4
Lakshmanan LSadri FSubramanian S(2001)SchemaSQLACM Transactions on Database Systems10.1145/503099.50310226:4(476-519)Online publication date: 1-Dec-2001
https://dl.acm.org/doi/10.1145/503099.503102
Show More Cited By

View Options

View options

PDF

View or Download as a PDF file.

eReader

View online with eReader.

Get Access

Login options

Check if you have access through your login credentials or your institution to get full access on this article.

Full Access

Get this Article

Media

Figures

Other

Tables

View Issue’s Table of Contents