--.DI -OT^ '^.

Full text

(1)

(2) -OT^. --.DI. '^..

(3)

(4)

(5)

(6)

(7) NAVAL POSTGRADUATE SCHOOL Monterey. ,. California. THESIS SEMANTIC SHORTCOMINGS OF DATABASE MANAGEMENT SYSTEMS BASED ON A RELATIONAL MODEL. by. Jonathan. S.. Wall. June 1988. Thesis Advisor;. C.. Thomas Wu. Approved for public release; distribution is unlimited. T239307.

(8)

(9) UiNLLAi>sirir,u jCURITY CLASSIFICATION. OF ThiS PAGE. REPORT DOCUMENTATION PAGE REPORT SECURITY CLASSIFICATION. I.. lb. RESTRICTIVE. MARKINGS. Unclassified SECURITY Classification authority. DECLASSIFICATION. Approved for public release; Distribution is unlimited. DOWNGRADING SCHEDULE. PERFORMING ORGANIZATION REPORT NUMBER(S). NAME OF PERFORMING ORGANIZATION. ). 6b OFFICE. Naval Postgraduate School {City,. 1.. (City, State^. Naval Postgraduate School. Code 52 7b. NAME OF FUNDING- SPONSORING ORGANIZATION ADDRESS. NAME OF MONITORING ORGANIZATION. 7a. ADDRESS. (Oty, State,. and. ZIP Code). Monterey, California 93943-5000. 93943-5000. Monterey, California 3.. SYMBOL. applicable). and ZIP Code). State,. MONITORING ORGANIZATION REPORT NUMBER(S). 5. (If. ADDRESS. DISTRIBUTION /AVAILABILITY OF REPORT. 3. 8b OFFICE (If. SYMBOL. PROCUREMENT INSTRUMENT IDENTIFICATION NUMBER. 9.. applicable). and ZIP Code). SOURCE OF FUNDING NUMBERS. 10. PROGRAM. PROJECT. TASK. WORK. ELEMENT NO. NO. NO. ACCESSION NO. UNIT. TITLE (Include Security Classification). SEMANTIC SHORTCOMINGS OF DATABASE MANAGEMENT SYSTEMS BASED ON A RELATIONAL MODEL PERSONAL AUTHOR(S). 2.. Wall, Jonathan. S.. 3a.. TYPE OF REPORT. 13b TIME. Master's Thesis. COVERED. FROM. DATE OF REPORT. 14. (Year,. Month, Day). 15. PAGE COUNT. 1988 June. TO. SUPPLEMENTARY NOTATION. 6.. The views expressed in this thesis are those of the author and do not reflect the official policy or position of the Department of Defense or the U.S. Government. COSATl CODES FIELD. GROUP. ABSTRACT (Continue on. 9.. 18. SUB-GROUP. reverse. if. SUBJECT TERMS (Continue on reverse. if. necessary and identif-y by block number). Relational database management systems; Attributes; Joins. necessary. and. number). identify by block. To many critics and researchers, semantic meagerness is the main limitation of relational DBMS. The burden placed on users to remember attribute names and their domains is discussed and difficulties associated with the lack of set as a type for an attribute is examined. The paper explores the implications to high-level query languages necessitated The allowance of semantically improper joins by DBMS is studied by a set-type attribute. The semantic shortcomings of system-chosen as is the lack of strong data type checking. followed by recommended solutions. are These problems access paths is discussed.. DISTRIBUTION /AVAILABILITY OF ABSTRACT. >0. [?^. UNCLASSIFIED/UNLIM'TED. D. 21. SAME AS RPT. Q DTIC. NAME OF RESPONSIBLE INDIVIDUAL Prof. C. Thomas Wu. 22b TELEPHONE (/nc/ude Area Code). 2a. )DFORM. 1473, 84. MAR. ABSTRACT SECURITY CLASSIFICATION. Unclassified. USERS. (408) 53. APR. edition All. may be used. until. exhausted. other editions are obsolete. 646-3391. 'Ul'Shi7'^'. SECURITY CLASSIFICATION OF THIS PAGE O U.S. Government Printing Office: 1986—606-24.. UNCLASSIFIED.

(10) Approved for public release; distribution is unlimited. SEMANTIC SHORTCOMINGS OF DATABASE MANAGEMENT SYSTEMS BASED ON A RELATIONAL MODEL by. Jonathan S. Wall Lieutenant, United States Navy B.S., United States Naval Academy, 1981. Submitted in partial fulfillment of the requirements for the degree of MASTER OF SCIENCE IN COMPUTER SCIENCE from the. NAVAL POSTGRADUATE SCHOOL June 1983.

(11) .. ABSTRACT. critics and researchers, semantic meagerness. To many is the main. limitation. placed. users. on. of relational. The burden. DBMS.. remember attribute names and their. to. domains is discussed and difficulties associated with the set as a type for an attribute is examined.. lack of. paper. explores. the. implications. languages. necessitated. allowance. of. by. semantically. a. high-level. to. set-type. improper. query. attribute.. joins. by. discussed.. These. system-chosen. access. The. DBMS is. studied as is the lack of strong data type checking.. semantic shortcomings of. The. The. paths is. problems are followed by recommended. so lut ions. Ill.

(12) .. '. /. THESIS DISCLAIMER. expressed in. The views author. and. position. of. do. the. not. this thesis are those of the. reflect. the. Department. of. Government. 1. V. official. Defense. or. policy the. or. U.S..

(13) -.BY. Mul,. :^ i\. rS SCHOOL OjxhxavimiA 95943-500S. TABLE OF CONTENTS I.. II.. INTRODUCTION. 1. i* DATABASE CONCEPTS A. DEFINITION OF A DATABASE SYSTEM ^ COMPONENTS OF A DATABASE SYSTEM B. 7 1. Data 7 2. Hardware 8 Software 3. 8 Users A. 9 ADVANTAGES C. AND DISADVANTAGES OF DATABASE PROCESSING 10 Redundancy Can Be Reduced 1 11 2. Avoidance of Inconsistency 11 3. Shared Data 12 ^. Enforcements of Standards 12 Application of Security Restrictions 5. 12 6. Data Integrity 13 7. Balancing of Conflicting Reports 13 .. .. .. ..... III.. IV.. RELATIONAL DATABASE MODEL RELATIONAL DATA STRUCTURE A. Definition of a Relation 1. 2. Keys Extensions and Intensions 3. RELATIONAL ALGEBRA B. 1- Traditional Operators Union a. Difference b. Intersection c. Product d. 2. Special Relational Operators Selection a. Projection b. Join c. RELATIONAL DATA MANIPULATION LANGUAGES C.. 15 15 16 21. .... ADVANTAGES OF RELATIONAL APPROACH A. INTRODUCTION B. RELATIONAL DBMS ADVANTAGES 1 Ease of Understanding 2. Data Independence Power and Ease of Use 3. Theoretical Foundation 4. .. V.. THE RELATIONAL APPROACH: A. B.. INTRODUCTION MENUS AND VIEWS Problems 1. Solutions 2.. PROBLEMS AND SOLUTIONS. 2A 26 26 27 27 27 28 28 28 29 30 32. 3^ 3^ 3-^. 3^. 35 37 38 39 39 -^0 '^O -4 1.

(14) C.. D.. SET-TYPE ATTRIBUTES Problems 1. Solutions 2. QUERY LANGUAGE MOD I P I CAT IONS Problems 1 2. Solutions JOINS Problems 1. Solutions 2. TYPE CHECKING Problems 1. Solutions 2. ACCESS PATHS 1. Problems 2. Solutions .. E.. F.. G.. VI.. CONCLUSIONS. ^5 ^5 S'^. 64 6^ 67 69 69 71. 73 73 75 78 79 80 84. LIST OF REFERENCES. 86. INITIAL DISTRIBUTION LIST. 88. VI.

(15) LIST OF FIGURES. Figure 2.1.. Simple View of Database System. 14. Student? Teacher and Student /Teacher Relations Example of Cartesian Product 3.2. 3.3. Domains and Attributes 3 Selection of Courses Relation Projection 3.5. of Teacher Relation 3.6. Natural Join of Student and Teacher Relations Over District 3.7. Division Operation 5. 1 Command View Teacher 5.2. Command View Teacher Di str c t Relation School 5.3. Relation School with Set-Type Attribute 5.4. 5.5. Relation Titles 5.6. Relation Titles2 User Invisible Relation 5.7. Relations Teachers and Employee 5.8. 5.9. Relation Flights ... 5.10. Relations with A Common Attribute. Figure 3.1. Figure Figure Figure Figure Figure. Figure Figure Figure Figure Figure Figure Figure Figure Figure Figure Figure. .. "^. .. .. .. i. ..... V. 1 1. 17 18. 20 29 30 31. 32 ^^ ^^ ^t>. 46 58 60 65 70 74 81.

(16)

(17) I. Database growing. fields. one. is. computer. in. years. ten. last. technology. corporations. among. INTRODUCTION. .. and. government. rapidly. Its popularity. science.. agencies within the. staggering.. is. most. the. of. Database. systems on. personal computers is now commonplace.. collections of. Database denotes users. computer. of. technique. systems.. storage. data. of. data shared by end-. hence. security, and. and. an organization make. decisions by accessing the database. of access,. popularity. its. Decision-makers within. importance.. most modern. the. is. It. Obviously, the ease. integrity. database. the. of. is. extremely important. Database systems are distinguished from other systems by certain features.. constituting CRef. A. .. 1. :p. .. the. <t3l ]. The following may be. major. features. performance optimization. -. concurrent usage. -. data protection. -. data independence. -. flexibility of data structure. program,. system (DBMS) the. storing. a. database system. :. -. software. of. considered as. known. manages the and. retrieval. the. as. database management. database. of 1. data,. The DBMS controls and. the. users.

(18) .. themselves. of. database. many. where. In. applications. users. many. database. also facilitated the development. DBMS have. (computer. concurrently. terminals. at. applications access. a. ). the coming. database. years,. increasingly important.. With the cost of labor steadily of computers. increasing and the cost are being. will become. systems. replaced by computers.. decreasing, people. CRef. 2:p.. ID. There is. strong reason to believe this trend will continue.. There. currently. are. database models:. 1). commonly. three. relational database, 2) hierarchical. Relational DBMSs are. database, and 3) network database. based. on. strong. a. implemented. theoretical. foundation which is. in. contrast to the network structure which was borrowed from. telecommunications was borrowed. and. from bill. the hierarchical structure which of material. systems.. CRef.. 3:p.. 56] Other features which attract users to relational DBMS. are its ease of. structure.. use, data. Relational. independence, and. table data. have only recently begun to. DBMS. gain widespread popularity in the commercial environment. The relational. the future.. remains to to present. relational. system has. Whether or not be seen.. been touted it. lives. as the system of. up to. this billing. The primary goal of this thesis is. problems and shortcomings of DBMS based on the model. and. to. solutions to these problems.. provide. recommendations. and.

(19) Chapter. II. describes. The chapter. database.. gives. definition of. a. system, describes the components and. lists. the. database. model.. relational. data. manipulation. Chapter IV describes. the. as ease of understanding. are outlined.. the. of. relational. type attributes,. .. The. detailed as Finally,. languages are discussed.. advantages. and data. of. the relational. Advantages such. independence, as well. Chapter V examines the problems. approach. to. query. language. lack of strong data type checking,. VI. common. and. database. management. modifications required. attributes, semantically improper joins, the. by set-type. solutions.. relational. Semantic burdens on the user, the lack of set-. systems:. problems. a.rB. operators.. approach to database management systems.. as others,. database. explained.. are. algebra operators. relational. special. the. the. defined. is. terms. model. traditional relational are. a. database system,. a. describes. III. relation. A. database. relational. of. a. of. advantages and disadvantages of database Chapter. processing.. concepts. basic. the. are. detailed. and. and access paths.. followed. by. The. recommended. Finally, conclusions are presented in Chapter.

(20) I I. DATABASE CONCEPTS. .. DEFINITION OF A DATABASE SYSTEM. A.. system is. A database is to. maintain information. available. demand.. on. system whose overall purpose. a. make that information. and to. The information is whatever the. individual or organization deems to be significant to the served.. entity being. In short,. information is anything. required by the individual or organization to help in the making decisions.. process of also. may. considered. be. repository for shared. a. a. stored data. CRef. .. ^:p.. A database. ^1. collection. of. facts. or a. which is both integrated and may be considered. Integrated means the database. consolidation of several otherwise discrete data files.. Any redundancy. amongst. data. in. database. the. different users.. files. Each. may. be. data at the CRef.. ^:p.. same. either. fully or. shared. among. many. user may have access to the same. piece of data and different users different purposes.. is. Shared means that separate pieces. partially eliminated. of. the. may use. that data for. Different users may also access the time,. known. as. "concurrent access".. 5]. The definition. can be. summarized with the following. points:. database is. generalized compilation of data.. -. A. -. This compilation is integrated to reduce redundancy.. a.

(21) .. -. Data structure is based on natural data relationships which provides all necessary access paths. The required access path to a unit of data is really a result of its relationship to other data. The ability to represent the natural data relationships with all the necessary access path is the essence of the distinction between a database and a conventional f. -. i. le.. database must supply the differing users in an efficient and effective. data needs of manner. [Ref. A 1. :p.. The. 5]. importance. database. of. systems. organizations cannot be understated. shared resource,. database. A. is a. thus its design and use must be managed The difference between sound. with all the users in mind. poor decisions. decisions and. today's. to. rests with the. many times. quality of information in the database. The. person. organization (DBA).. group of the. controls. who. is. known. the. as. database. the. in. an. Administrator. Database. This position is critical because this person (or. responsibility for protecting. persons) assumes. while. database. maximize benefits. at. the. to users.. same CRef.. attempting. time 2:p.. 536]. to. The DBA's. responsibilities include the following: -. determines the information content of the database. -. determines the storage structure and access strategy. -. provides. -. specifies security and integrity checks. -. defines backup and recovery strategy. -. monitors performance and responds to changing requirements. CRef. ^:pp. E5-27]. a. liaison with users.

(22) J. ). final. The. piece. management system (DBMS). manages. database;. a. interpreted to. maintenance.. CRef.. word. l:p.. may. be. creation and. of. scale DBMS provides. A full. 6]. software that. "management". functions. the. database. the. is. The DBMS is the. the. include. puzzle. the. of. the following capabilities: -. storage, update, and retrieval of data. -. a. -. transaction support to ensure that all or none of a series of database changes ars reflected in the relevant databases. -. recovery services or program. -. ensure concurrency control services to that transactions function way concurrent the same as if having been run in some sequential order. -. authorization services to ensure that access to and manipulation of data is in accordance with defined constraints on users and programs. -. integration that commun i ca t i on. -. integrity services that ensure database states and changes in this state conform to specified rules.. catalog accessible descr i p t ion. [Ref.. The. 5: p.. users. to. in the. event of. includes. provides data. which. a. failure (system. support. for. data. 11^. database. system. centralized control of its. provides the organization with operational data.. CRef. ^:p.. 9D. Without a database system, an organization is subject. to. a. wide. applications.. range. of. private. files. interfacing. with. Control of one of any organization's most. important assets, the operational. data,. is. shaky.. This.

(23) may prove to be very hazardous to the corporation's wellbeing.. B.. COMPONENTS OF A DATABASE SYSTEM A database system consists of four. major components:. Figure S.l shows an. data, hardware, software, and users.. example of the arrangement of a simplistic system. 1. .. Data As stated earlier,. a. Kroenke. integrated files.. database is. relationships. those files." CRef. 2:p.. 113. true. to. lexicon. the. characters. characters,. called. a. file.. Database file processing. independently. CRef.. 2:p.. processing where each and. the. (8. the computer. bits. =. 1. byte). are grouped into fields, and. fields are grouped into records. is. in. a. records in. among. industry, bits are grouped into bytes or. collection of. states that "a database is. collection of files and. Holding. a. collection of records. A. 11]. significantly from. differs file is. considered to exist. structure. of. the. files. is. distributed across application programs. For tutorial purposes, we just one. will. assume. there is. database, containing the totality of all stored. data in the system although normally the system into one or more databases.. is split.

(24) ... Hardware. 2. no special hardware needed for database. There is. Hardware. systems.. consists. device. of. controllers,. secondary storage devices (disks,. input/output channels,. drums, etc.) on which the database resides, together with. associated devices.. primary storage will. is assumed. It. not be large enough store the entire database.. applications. Database. resources. main. larger. (i.e.,. processing unit, and more can. require. memory,. extensive. faster. direct access. expensive.. quite. be. often. Database. central. storage).. This. processing. also. involves special programs and overhead data.. Special purpose computers. processing. functions,. announced. by. several. According. to. this. processing the service and. manner,. vendors. 19S2. in. CRef.. were. 2:p. S]. program. sends. requests for. data over a channel to the database machine.. database manner. requests. the. messages back. concurrent. machines,. architecture, the computer. of. application. The machine processes data, or. database. called. type. perform database. that. to. with. sends results,. main computer.. the. processing. and. can. be. applications. In this. performed. in. processing.. a. The. effectiveness of database machines remains to be seen. 3. Software The. database. layer. (i.e.,. of. the. software data. between. the. physical. actually stored) and the. as. 8.

(25) users of the system is known system (DBMS).. database management. as the. Requests from users to interact with the. database ar^ processed by the DBMS.. The. DBMS acts. as a. safeguard between the users and hardware level details. The. operating. system. controls the computer's of this. (OS). resources. is. thus. Operating system. burden.. relieving users. programs cause tasks. performed and may be considered the. to be. program which. a. nucleus of all. the other programs.. The Communications Control Program. communication-oriented tasks. checking. error. (and. destination,. and. CCP. ). performs. provides communications. correction. coordinates terminal activity, proper. It. (. errors are found),. if. messages. sends. formats. messages. to. their. for various. types of terminal equipment.. Application. programs. applications where. many users. access a database and needs.. Specific. (. at. to specific. such as order entry,. accounting, and billing are satisfied. ^. •. computer. business inventory. CRef. 2:p. 93. Users Three broad classes of. database system:. The. users interface. with the. application programmers, end-users and. the database administrator. writing. are. ). terminals concurrently. are tailored. needs. AP. application. application. (DBA).. programmer. programs. that. is. use. responsible for the. database..

(26) .. Typically written in a. application. programs. language such. existing. information,. applications. batch. functions. are. receives the appropriate. on-line. or. support. to. it. functions. themselves may be conventional. programs. The. request.. after. deleting or. and. Such. information.. performed by the DBMS. or PL/1». used with data for retrieving. are. information, creating new. changing. as COBOL. an. applications. end-user. which. accesses. who. the. database from an on-line terminal. language provided. An end-user may employ a query as. composite. a. functions. part. retrieval,. of. modification of. data.. the. system. perform. to. the. deletion,. creation,. The alternative. is for. and. the end-. of the. on-line application programs. commands from. the terminal and then issues. user to utilize one that accepts. of. requests to the DBMS on the end-user's behalf. The. database the person. earlier is. a. group of persons) responsible The DBA staff. system.. a. focal point. conflicts.. ADVANTAGES AND DISADVANTAGES OF DATABASE PROCESSING As mentioned earlier, an. system. is. that. it. advantage. provides. centralized control of its 9].. mentioned. (DBA). guardian of the database and as. for resolving users'. C.. (or. the database. for the control of. serves as. administrator. the. operational. of. a. database. organization data. CRef.. with ^. :. p. Also, database processing allows more information to 10.

(27) ... be yielded from a given amount. gained. then. figures,. processing. by. recorded. these. Centralized. data.. the. Information is. of data.. facts. and. of. the. control. provides the following advantages CRef.. operational data 10-123.. -^ipp.. Redundancy Can Be Reduced. 1. One important advantage of database processing is. elimination. the. reduction. or. processing. conventional file. in. stored. In. each application. systems,. This may lead to considerable. has its own private files.. redundancy. of data duplication.. storage. wasting. therefore. data. space. Elimination of. duplicated information saves file. space and may reduce processing requirements. be noted. redundancy should. that all. eliminated.. 2. CRef.. exist must be. 2:pp. 3-6]. Avoidance of Inconsistency This follows. closely with. the above. two different entries in the database. fact. should. not necessarily be. However, redundancy that does. carefully monitored.. It. about. the. real. represent. occasions where the two entries. do not agree. only one entry has been properly updated).. an inconsistent. a. If. single. there will undoubtedly be. world,. the database is said to be. point.. inconsistent.. (i.e., when. At such. times. A database in. state may supply users with incorrect or. conflicting information which leads 11. to user. distrust of.

(28) computer generated. output.. If. the system can ensure that. inconsistent. in. applied. will. r^&vBr. be. of the user, by guaranteeing. either. that any change made to. automatically. database. the. eyes. the. redundancy is controlled,. of. other. the. to. the. entries is. two. one. also.. This. process is known as "propagating updates." CRef. 2:pp. 36D. 3. .. Shared Data As discussed. earlier, sharing. means existing as. well as new applications that are developed can share the. Users have access to the same data. data in the database. and different users. may use. and for different purposes. ^. .. the data CRef.. in. different ways. 2:pp. 3-63. Enforcements of Standards Because. the. data. centralized (vice private. is. files for each application),. the DBA. is. able. to ensure. that all pertinent standards are observed with respect to. Applicable standards may. the representation of the data.. include. any. or. state, division,. between systems. .. CRef.. DBA. organization,. following:. the. industry,. and international. national,. helps. in. data. migration. 2:pp. 3-6]. Application of Security Restrictions With complete. the. of. Standardizing. standards.. 5. all. can. ensure. control over the. through the proper channels. the database system,. database may be accessed only therefore requiring security 12.

(29) .. out whenever. be carried. checks to. data is attempted.. nature of. should. It. access to classified noted. be. that. the very. centralized database system requires. a. security system be in place.. CRef. .. a. sound. E:pp. 3-6]. Data Integrity. 6.. integrity. Data. deals. with. problem. the. guaranteeing the data in the database is accurate.. of. There. lack of data integrity if two entries that represent. is a. the same. inconsistent (which can only occur. fact are. redundancy exists. in. the. stored. if. Because the. data).. database is shared (unlike private files), data integrity is of. are. extreme importance. weak. this. in. shortcoming. is. Most current database products. Area.. common. A. conflicting. result from this. reports. by. corporation. employees who use these database products. CRef. 2:pp. 3-6] 7. Balancing of Conflicting Reports Because. the. database. whole organization as opposed. system to. is. just. shared by the. individuals, the. structure the system to provide service that is. DBA must. in the best. interests. organization. CRef. 2:pp.. of that. 3-6]. There are many disadvantages that come along with databases. is. that. disadvantage of. A major it. can. be. very. database processing. expensive.. DBMS. are. not. inexpensive, and because of its main memory requirements, 13.

(30) .. additional memory. may have. to be purchased.. The system. may also gain exclusive control of the Central Processing. Unit. (CPU). forcing the user to upgrade to. a. more powerful. computer Data processing. database system process large. different. complex.. The. and application programs must be able to. amounts of. formats.. programming and. be very. tends to. data that. results. This. in. sophisticated. trained programming and. requires highly. maintenance personnel.. ^re interrelated in. Backup and recovery of data also. increases system complexity and is difficult to carry out in the. database environment. Another. hence. entire. disadvantage. centralization, system. may. is. that. integration, and. increases. vulnerability.. The. fail. one. component. fails.. if. Obviously, this becomes extremely critical if. organization. depends. day-to-day operations.. upon the database for its. heavily CRef. .. the users'. 2:pp. 6-711. DATA. DBMS O.S. USER -^ USER. APPLICATION SOFTWARE DATA. Figure 2.1,. Simple View of Database System. 1^. USER.

(31) Ill. A.. RELATIONAL DATABASE MODEL. .. RELATIONAL DATA STRUCTURE The relational model was first proposed by Dr.. theoretical. of. interest. that. to. practical. of. This was the result of the announcement of. significance.. relational. important. several. F.. Only recently has the model moved from the. Codd in 1970. world. E.. products. DBMS. (i.e.,. SQL/DS, ORACLE, etc.).. relation. A. is. dimensional table.. relation and not. of. device in. A relational. the rows.. a. item values.. which. may. variety of ways.. relational. shows. a. form. will. STUDENT,. tables:. (ST).. The STUDENT. called. a. student attends. l:p.. school.. each teacher, a teacher. composed. 130]. very helpful.. be. which is. TEACHER,. number, a. of that student,. is. stored on a ph/sical. table contains,. student identification in school. is. data structure, sample data. relational view of data. three. database. be. CRef.. To explain the relational in. two-. rows and. by It. a. matrix due to a lack of homogeneity in. a. relations. such. for. the entries sre homogeneous in the columns,. its entries; in. term. characterized. is. It. contain data. columns that. but not. mathematical. a. and. Figure 3.1. organized into. and STUDENT-TEACHER for each. student,. student name, the. district. a. the year. where the. The TEACHER table contains, for. identification number, 15. a. course.

(32) name,. class size, the credit hours for the course, and. a. the teaching district.. assume each. As. teacher has. identification. contains. table. number,. relation, we. unique identification number.. a. STUDENT-TEACHER. The. the STUDENT. in. student. a. teacher identification number,. a. and a tenure value. 1. Definition of. .. Given. Relation. a. collection. a. Dl,D2,...,Dn (not. sets. of. necessarily distinct) R is relation on those n sets if is a set. of ordered n-tuples <d. belongs to. belongs to. dE. Dl,. ,. d2. D2. ,. ,. .. .. .. .. .. .. ,. dn> such. the degree of R.. Relations of degree. degree E:binary, degree. 3:ternary,. that dl. dn belongs to Dn.. ,. ...,Dn are the domains of R.. Sets Dl, D2,. CRef. ^:p.. 1. it. The value n is. are called unary;. 1. degree n:n-ary.. and. 83D. An. given from. definition. equivalent a. of. a. set-theory. mathematical. relation can be perspective.. A. relation is any subset of the Cartesian product of one or more domains. =. Al. For example, and. <:a,b). A2. =. CI. if we have n sets, ,. 2. ,. 3. >. ,. Cartesian product of these n sets.. then It. is. Al. C. (. and a2 belongs to A2 a,. 1. ). ,. (a ,2). ,. (a,3). ,. (b. ,. 1. ). the Cartesian product of and teacher. result. The. .. ,. (b ,2). ,. (b ,3). two sets. identification number). 16. >. .. x. of. =. 2,. A2 is the set of all. the. possible ordered n-tuples <al,a2> such that Al. say n. al. Al. belongs to x. A2 is. Figure 3.2 shows. SID and. TID (student.

(33) STUDENT SN. SID SI. ROSE. SE. S3. DISTRICT. YR 1. MTY. STONE. 2. SAL. VENTURA. 3. SAL. (a). TEACHER TID. COURSE. CLSIZE. Tl. HIST. MED. E. MTY. TE. MATH. LRG. ^. SAL. T3. ECON. SML. 3. CML. T^. PSYCH. MED. 3. MTY. DISTRICT. CREDIT. ST. TENURE/AMOUNT. SID. TID. SI. Tl. 3. SI. TE. E. SI. T3. ^. SE. Tl. 3. SS. TS. ^. S3. TS. E. (c. F. igure 3. .. 1. ). Student, Teacher and Student /Teacher Rel a t i ons. 17. (b. ).

(34) SID:. TID:. relation. STUDENT. called. illustrated in Figure 3.1(a). of. representing. values. (SID),. student name. TID Tl. S2 SE S2 S3 S3 S3. Tl. TS T3 TS T3 Tl. TE T3. Example of Cartesian Product. Figure 3.2. A. SID SI SI SI. (. SN. ). The four domains. year. the. and the. respectively.. The YR domain, for example,. district of the school. this. appear in. the STUDENT. (relations. a.r&. A. number of each. (DISTRICT) the set of. Note that there may be some year. valid year numbers.. numbers included in. is. is. Are sets. school. in. student (YR),. all. four. identification. student ,. degree. of. domain. that. relation at. do. not actually. this particular time. time-varying).. relation may be viewed as. a. table,. where each. row is called a tuple (corresponding to records) and each. column represents a field within the record. a.re. called. attributes.. The. number. of. The columns. tuples. relation is called the cardinality of that relation. 18. in a. For.

(35) example, the cardinality of the STUDENT relation is three and it has four attributes (columns). A domain may be. from. which. sets are. Keep in. relation do. j. th. (. a. 1. ,. have an. aS. ,. .. .. .. ,. an. relation. However,. ordering among. ). for a given is a. set and. domains. the. of a. them.. If we have a. with n components, the. value of the. component in this n-tuple must be pulled from the. th domain.. [Ref.. 4:p. 85]. The value of the third. field. attribute YR. is. integers.. mathematical. In. between. a. domain CRef. .. that domain.. relational. schema. defined. relation is. in. to. understand. the four. Figure which. 3.3 shows. four. which are a. part of a. domains (STUDENT_ID,. and LOCATION) and one relation (STUDENT). using. a. data. declared with. a. SAL).. the difference. 853 and attributes. definition. corresponding. language.. four attributes (SID, SN. and DISTRICT), and each attribute is. pulled from. 3,. under the. tuple. rearranging. terms,. 4:p.. drawn from. STU_NAME, YEAR,. the third. different relation.. a. important. is. this. of. j. drawn from the third domain all positive. columns results in It. Figure 3.S(a),. In. relation is (S3, VENTURA,. the STUDENT. tuple of. are. values. There is no ordering defined among. relation.. a. a. bank of values. as a. actual. the. mind that. not ordered.. the tuples of. tuple. drawn. are. attribute.. thought of. domain.. that more than one attribute has. 19. The ,. YR,. designated as being It. the same. may be the case. domain.. Data.

(36) ,. .. attributes having. values from allow the. relational. system. domain are what. the same. associate. to. tuples from. different relations. DOMAIN DOMAIN DOMAIN DOMAIN. STUDENT_ID STU_NAME YEAR LOCATION. ALPHANUMERIC CHARACTER NUMERIC CHARACTER. RELATION STUDENT SID. DOMAIN DOMAIN DOMAIN DOMAIN. (. SN YR. DISTRICT. (20) (1). (15). STUDENT_ID STU_NAME YEAR LOCATION). Domains and Attributes. Figure 3.3.. databases. Relational. (E). must. composed. be. of. relations which meet the following condition:. —. Every value in the relation i.e., each attribute value in each tuple is atomic (i.e., nondecomposab le so far as the system is concerned). CRef. A^ p B61. —. :. That. value in. every data. is,. one value.. A. the relation.. values in. a. set of Null. the relation. values. are. allowed. relation that are not known. it. This. in. greater. set-types. within. be. implementation d. i. discussed of. precisely. is. data values is not allowed within. meets the above criteria, will. .. is. to represent. When. a. relation. be normalized.. said to. detail a. when. relation. the is. scussed The format used to represent a relation is called. the relational structure.. For example, 20. STUDENT(SID, SN.

(37) YR,. describes. DISTRICT). relation.. structure. relational (. constraints. imposed. a.re. relational. the. ,. .. .. .. the be. to. at tr i buteN. ). If. .. data values which may. the. on. said. be. at tr ibuteS. ,. describes. that. may. Re lat i on_name at tr ibutel. exist in. format. generic. The. of the STUDENT. structure. the. structure,. then. relational. a. schema exists. E.. Keys When. relation. attribute. an. unique values within. with. used to identify tuples within that relation,. is. that attribute. said. is. to be. uniquely identifies. Within the STUDENT relation, the attribute. that person. is. A person's social. key.. a. security number is an attribute that. SID,. a. the key.. unique. Its. distinguish each. values. tuple within the relation.. attribute. 3.1(c)),. may. key. A. example,. For. the. key. combination of. SID. SID. is. TID. combination also acts relation.. It. should. to. as. (Figure. SID and TID. within the relation. were not the case.. this. itself. by. above example,. said. ST. not. (and. If. the. more than one tuple would have. the two), In the. and. if. TID. or. the same key.. the attributes. unique tuple. A. be identified. were. relation. the. in. combination of. the. serves as the key. could not. the composition of more than one. be. be. a. composite. primary. the. the combination of. key. key.. for. The the ST. be remembered that a relation is a. 21.

(38) .. set,. with tuples being elements of the set.. Because sets. contain duplicate elements, each tuple of. do not. relation. unique. is. guarantees. that. combination. the. This property. relation.. the. in. will act to uniquely identify a. given. a. of all. the attributes. within the. single tuple. rel at i on. Relations may exist where more than one attribute. combination is able to uniquely identify This relation. relation.. candidate key.. a. tuple within a. to have more than one. is said. Our STUDENT relation is such. relation.. a. Tuples (or records) may be uniquely identified within the. STUDENT relation by either the value of the SID attribute or by. the value. there are never columns.. We. SN attribute.. of the. duplicates. any. choose. candidates to act as the primary is. a. candidate key. either. in. arbitrarily. may. This is assuming of. either. the primary. act as. of. the. An alternate key. key.. that is not the primary key.. STUDENT relation SID may. the two. In the. key and SN. would act as the alternate key.. Tuples are keys depict. uniquely identified. entities. in. real. the. by their primary. world.. Therefore,. primary keys serve to uniquely identify entities. the STUDENT. students.. students. relation,. the. tuples. Within. represent individual. The values of the SID attribute identify those. rather. than. just. the. 22. tuples. that represent.

(39) offshoot. an. As. them.. interpretation,. this. of. we. introduce the following rule. INTEGRITY RULE 1 (Entity integrity) No component of a primary key value may be null. ^:p. 89],. [Ref.. states. rule. This. that. Primary. identifiable in some way.. If a. primary key were to have. the. entity. property.. a. serve. keys. as the. null value in a relation,. without. be. unique identification. a. prohibit. would. This. must be uniquely. function in a relational database.. unique identification. would. entities. all. particular entity. that. from being distinguishable from other entities within the. relation. null. recommended that both whole. is. It. identifiers be prohibited. times. Many. references. both. to. These references in ST.. If. a. s.re. tuple. -^. :. p. .. 89]. relation contains references to. one. Relation. another relation.. CRef.. and partial. ST,. for. includes. example,. STUDENT and TEACHER relations.. the. the attributes SID and TID contained of ST. has. a. value for SID, say SI,. then a tuple should exist within the STUDENT relation for. student. SI.. If. were. this. not. the. case,. containing SI in the ST relation would refer to. the tuple a. student. that does not exist. To further. elucidate matters, we must understand. the concept of a primary domain.. domain. may. optionally. only if there exists. be. some. Date. states,. "A given. designated as primary if and. single-attribute. primary key \. E3.

(40) defined on. that domain". its definition. able. Figure 3.3),. STUDENT_ID. domain. the. DOMAIN STUDENT_ID. follows.. By adding to. ^:p. 89].. shown in. (from that. specify. to. CRef.. we are. as primary as. ALPHANUMERIC. (S). PRIMARY A. relation. must. abide. following rule if that. the. by. relation includes an attribute. that. is. defined. on the. primary domain (for example, relation ST). INTEGRITY RULE 2 (Referential Integrity) primary domain, Let D be a and let Rl be a relation with an attribute A that is defined on Then, at any given time, each value of A in D. (a) null, or (b) Rl must be either equal to V, the primary key value of some say where V is in some relation R2 (Rl and R2 not tuple necessarily distinct) with primary key defined on D.. CRef.. The. definition. relation R2 key of Rl,. ^:p. 89] of. must exist.. If. domain. implies. attribute A is the primary. the rule is trivially satisfied.. Finally, we introduce When an. key.. primary. a. attribute in. another relation,. of. one relation. is. the attribute is called. attribute TID. For example,. notion. the. of relation. key because its values are values. of the. a. a. foreign. the key of. foreign key.. ST is a foreign. primary key of. the TEACHER relation. 3. .. Extensions and Intensions An. relation in. extension a. and. intension are components of. relational database.. 24. a.

(41) The set of tuples existing in the relation at any. given instant is known as the extension of that relation. Obviously,. extension. the. extension varies. time-dependent.. is. as changes. The. or operations are. are made. performed on tuples (i.e., update, delete, create, etc.). On the other hand,. The intension directly relates to what. time-independent. is. specified is. relation. (the. combination. the. constraints.. CRef.. -^. :. p. and. constraints. the. the structure of the. of. integrity. and. names. the. domain. divided. are. Hence,. The naming structure is the. 90]. .. respective. their. schema.. structure). naming. name of the relation with. relational. the. in. intension. the intension of a relation is. the attributes. of. into. integrity. The. names.. constraints,. key. referential constraints, and other constraints.. existence. The. constraints.. of. candidate. keys. implies. key. up the primary. The attribute(s) which make. key and the attribute(s) which make up the alternate keys if. any. exist,. are. uniqueness restraint Also,. primary. the. specified is. ^:p.. intension.. A. implied by these specifications. key. specification. accordance with Integrity Rule #1, CRef.. the. in. a. infers,. in. no-nulls constraint.. 91]. The existence of foreign keys implies referential. constraints.. A specification of all. foreign keys in the. relation implies a referential constraint. 25. The relations.

(42) in Figure 3.1. are examples of extensions.. They also show. the naming structure (relation and attribute names).. B.. RELATIONAL ALGEBRA operations. Relational algebra defines. Operators manipulate. relations.. desired result.. relations to achieve. important to. is very. It. work on. that. a. note that the. result of. any operation on a relation creates in another. relation.. Relational algebra. means. which. that. must not only know what he. user. the. wants when performing operations on a how to. get. relation considered elements. set with the tuples of each of. the. set.. Therefore. performed on sets may results in. a. relation, but also. previously mentioned, relations are. As. it.. to be procedural,. is said. operations. any. performed. be. on. can be. that. relations which. new relation.. Although relational. algebra is not often used,. important to understand as. is. it. the backbone. it. is. of other. high-level query languages such as SQL and QUEL. The relational. algebra may be said to consist of two. groups of operators. 1. .. Traditional Operators The. difference, in greater. traditional. set. operators. are. union,. intersection, and product which are discussed detail below.. CRef. ^:pp.. 252-260]. 26. E03-2. 1 1 ]. CRef. .. 2:pp..

(43) ... Un ion. a. Combining the those of. a. second relation to produce a third relation is. known as. the union of two relations.. combined. relations. compatible means of attributes,. must. union. be. To make sense,. compatible.. the. Union. each relation must have the same number. attributes. the. and. from. come. must. one relation with. tuples from. same. the. relation (relation A). domain.. For example,. of students. the set. is. matching columns. in. Monterey, and another relation (relation B). is. if one. living in the set of. students who play tennis, then A UNION B would be the set of. (relation. students. tennis (or both). tuples. a.re. who live in Monterey or play. should. It. noted. be. that duplicate. eliminated. Di f f erence. b. A third. of tuples which occur is said. C). to be. example, using example, A. relation, in. (relation C) that consists. relation A but not in relation. the difference of relations A and B.. the. MINUS B. relations. same is the. set of. Monterey and who do not play tennis.. used. in. B. For. the UNION. students who live in Note the relations. must be union compatible. c. I. nter sec t i on. A. third. common tuples between two and B). is said. (relation. relation. to be the. other. relations. C). containing (relations A. intersection of relations A and 27.

(44) For example,. B.. above examples,. the relations. again using. used in the. INTERSECT B is the set of students who. A. live in Monterey and play tennis.. the relations. Again,. must be union compatible. d-. Product The concatenation. of every tuple of relation. A with every tuple of relation C). B.. relation A has. If. then. tuples,. their. m. tuples. product. product is represented by A. and. has B. X. relation x. n. or. A. TIMES. identification number relation is the set. possible. all. student. living. in. has. B. tuples.. m. students living in Monterey TIMES. example, the. relation. in. the Cartesian product of relations A and. known as. is. (resulting. B. B. a. n. This For. student. or relation of. Monterey / student. identification number pairs. 2. .. Special Relational Operators. operators. The special relational. projection, a. .. join,. included in. from. a. and division.. Se 1 ec t i on. The. subset (row). are selection,. of. selection given. a. the new. operator outputs relation.. relation.. relation by specifying the. A. a. horizontal. tuple. A tuple may be. is now. extracted. relation name followed. by the keyword WHERE followed by a conditional statement.. This. conditional. statement. involving. attributes. is a. single expression or may involve a combination of Boolean 28.

(45) Figure. expressions.. relation STUDENT the selection. .. "^. a. (. WHERE SN. of STUDENT. selection of the. shows the. ). Figure. "ROSE".. =. WHERE YR. >. 3. .. -^. (. b. ). shows. Figure 3.^(c). 1.. selection of STUDENT WHERE YR. shows the =. 3. AND DISTRICT. 3. <. "SAL".. SN. SID. ROSE. SI. DISTRICT. YR. MTY. 1. (a). SN. SID S2. STONE. S. SAL. S3. VENTURA. 3. SAL. (b. .. "^. t. i. on. Specified by. the. ). Selection of Courses Relation. .. Pro jec. .. SAL. £ (c. Figure 3. DISTRICT. YR. STONE. SS. relation. ). SN. SID. b. DISTRICT. YR. attributes. subset. duplicate eliminated.. tuples. from. a. projection operation (projection draws. out columns from a relation).. vertical. selected. are. (column). within. The. of. a. operator. given. attributes. the. outputs. relation.. selected. Any. are. An attribute may only be specified once in. S9. a. a.

(46) .. The operator may. projection operation. change the. arrangement of. because the attributes are. the attributes. specified in. COURSE and CREDIT attributes. TEACHER. of a relation a. left-to-right. COURSED. [CREDIT,. projection is denoted. The. (relation name followed by. attributes to be projected in brackets). order. used to. Figure 3.5 shows the projection of TEACHER on the. order.. by. also be. attributes. the. of. changed due to the order. Duplicates. would. from the original relation is appear. they. been. have. Notice that the. in. eliminated. the brackets. had. they been. present. COURSE. CREDIT. Figure 3.5. c. .. HIST. 4. MATH. 3. ECON. 3. PSYCH. Projection of Teacher Relation. Jo i n A. sometimes. 2. combination. projection. is. known. as. denotes a join between relations A. relations. in. First take then do. a. a. natural. the Cartesian. selection. to. selection,. product,. of. join,. a. and B.. there. product of. eliminate. desired, remove duplicate attributes 30. join. To. and. A JOIN B. join two. are three steps. the two relations,. some tuples.. Then if. through projection..

(47) attributes are. duplicate. If. When the term join. equijoin.. mean. to. not removed it. a. natural. join.. is used,. The natural. it. called an. is is. understood. join of the STUDENT. and TEACHER relation over the attribute. DISTRICT appears. in F igure 3.6.. DISTRICT. COURSE. CLSIZE. Tl. HIST. MED. 2. MTV. T^. PSYCH. MED. 3. S. SAL. T2. MATH. LRG. ^. 3. SAL. 12. MATH. LRG. ^. SID. SN. SI. ROSE. 1. NTY. S2. STONE. E. SS. STONE. S3. VENTURA. YR. Figure 3.6.. d. D. .. i. a. Natural Join of Student and Teacher Relations Over District. the. domain of. a. relation is also. unary. binary relation, then we may. relation by. the unary. relation.. The. resultant. value of the uncommon. a. divide the binary. This results in a unary. relation consisting of the uncommon domain relation.. CREDIT. vi sion. If. domain of. TID. from the binary. relation contains an attribute its corresponding entries. domain if. in the. common domain contain all the values of the divisor. domain.. CRef.. l:p.. such an operation.. 150]. Figure. 3.7 shows. example of. The relation TEACH acts as the dividend. and relation LOCATION is the divisor.. TEACH/DISTRICT).. a.r\. In. the. figure,. 31. The result the. only. is. ID. (ID. teacher.

(48) identification. number. which. for. (TID). there. 1. s. corresponding values of both SAL and CML in TEACH is TS-. LOCATION. TEACH. DISTRICT. DISTRICT. TID Tl. MTY. SAL. TB. SAL. CML. T3. CML. T^. MTY. TS. CML. Figure 3.7. C.. ID. TID T2. Division Operation. RELATIONAL DATA MANIPULATION LANGUAGES. Manipulation. Data. Languages. (. DMLs. through which relational databases may be are. main. four. methods. relational. database.. relational. calculus,. accessed.. manipulating. for. These. ^re:. languages. are. ). data. relational. transform-oriented. There in. a. algebra,. languages,. and. graphic methods CRef. 2:pp. £52-253].. Relational. achieve. the. procedural. algebra. desired. uses. standard. result:. a. (the user must not only. new. set. operators to. relation.. know what. It. is. he wants but. also how to get it) and therefore difficult to use.. Relational calculus. is. non-procedural. tells the system what he wants,. concept. was. first. proposed. language based on this. not how. (the user only. to get. it).. The. by Codd who also presented a. calculus, 32. Data Sublanguage ALPHA..

(49) .. however,. .. this. language. Transform-oriented languages.. The. was never implemented.. languages. most. popular. facilities as. SQL. languages. :. p. .. is. provides retrieval. well as many others.. based DBMS products include. '^. non-procedural. these. of. Structured Query Language (SQL). and update. are. CRef.. SQL/DS, System. R,. Major SQLand ORACLE.. CRef. S:p. ^373. QUEL is based on tuple relational calculus and is very. similar to SQL.. It. is. non-procedural so the user. concern himself. need to. does not. with the underlying physical data. struc ture. Duery-by-examp 1 e access. a. database.. (QBE) In this. is. a. graphical. presented. relation.. This is. suitable only for terminal use and may not be embedded host. 1. anguage. 33. to. is. method the user. with a picture of the structure of the. method. in a.

(50) ADVANTAGES OF RELATIONAL APPROACH. IV.. INTRODUCTION. A.. database management. As mentioned earlier, relational. systems to. industry observers,. technology. most. for. understandab i. 1 i. ty. ,. relational DBMS.. According. will be the most prevalent DBMS. it. data-processing applications. independence, power. data. The. Its. and ease of. the. earmarks of. distinctive features. of DBMS are. foundation. theoretical. and. use,. widespread popularity.. enjoying. are. are. described below. B.. RELATIONAL DBMS ADVANTAGES 1. .. Ease of Understanding One of the main motivations for the research work. that resulted. termed the. relational. the. in. "communicab i. 1 i. was. model. ty objective".. CRef.. what Codd 5:p.. 110]. and programmers. Achieving. this. alike are. able to communicate with one another about the. objective. means. users. database because they both have a common understanding of the data;. the model. is. structurally simple.. The relational. DBMS, using. columns which are universally logical. view. concept. is. more. opposed. to. those. of. data.. easily who. tables with rows and. understandable, provides. This. means that the database. understood understand 3^. a. to. the. many. users as. hierarchical or.

(51) Navigation through. network based models.. because. necessary. This is. tables.. network models one is. data in. contrast. where one. working. should be. are no pointers connecting the. there in. hierarchical and. the. to. must maintain a position where. performing. when. operations.. most. It. while users may logically view the. noted that the database. collection of. as a. rarely stored as such in physical memory.. mentally sees the database. collection of. tables is not. tables or. as. being,. tables,. it. is. Thus,. the user. all. cases, a. in. one record type per. files with. table. 2. .. Data. I. ndependence. Data independence is a very important any. database. dependent,. strategy effect. system.. then. cannot on. the. applications. storage. the. structure. and. changed without having. be. application.. need. application. an. If. Data. worry. not. feature of. about. a. is. data. accessing. pronounced. independence means how. the. data. is. considered the data. accessed or physically stored.. Codd. independence objective. most important motivation. for the. research. CRef. 5:p.. spawned. which. the. relational model.. 1103. Under the. relational model,. its value in tables,. tables are. as the. related by. not. by. its. data is accessed by location.. Likewise,. value, not by pointers connecting. 35.

(52) the tables.. determines access paths,. the system. Also,. not the program.. reasons. Two. applications. why. independent are CRef. ^:p.. must. data. be. 133:. -. Different applications need same data.. -. The. different. views. of the. change existing not be forced to desires to change, due to changing applications if he structure or access the storage requirements, strategy (or both). DBA. must. both end-users. Data independence greatly affects and. programmers.. knowledge. end-users. the. database. physical. the. of. ordering. data. access;. From. tables. in. viewpoint,. required. is. of. having. combined with the ability to what the. users. This. the underlying data structure. know. to. for. insignificant.. is. Therefore, requests for data may be non-procedural. lack. no. just. system know. the. let. user wants (and not also how to get it) enables. with. experience. computer /database. little. to. immediately interact with the system. From. independence also spent. making. For example,. programmers. the. offers. changes if. advantages.. to. program and. changes, thus the current path to optimal. and. the. data. point,. Many. hours are. existing application programs.. relational queries. language application. vantage. a.re. embedded in. a. host. the nature of the data. the data. is no. longer. relational query language program only. needs to be recompiled,. CRef.. 6:p.. 36. 933. However,. if. the.

(53) .. situation. same. program. would. understands. occurred have. effects. the. rewritten. of. may. desired. organizations. changes. data types.. someone. who. change. there. a. on. the. the concept. writing. spent. be. grow,. the. due to. applications for which there tends to be As. database, by. data. the. The time saved. independence. data. CODASYL. a. be. to. application program. of. in. new. shortage of. sure. are. to be. data representation and growth in. in. Data independence affords organizations this. inflicting damage. luxury without. upon their application. programs 3. .. Power and Ease of Use. Relational operations are use. because. they. enable. users. multiple sets of records at Hence,. example,. relational in our. SELECT FROM WHERE. powerful. operations. are-. operate or process. to. time in. a. a. single statement.. less procedural.. tid. teacher district = "MTY". operation. as. done. is. advantage of being able to process at. one. time is obvious.. as operands. For. TEACHER relation, the SQL statements. would operate on the entire relation (versus record. easy to. and. a. record by. conventionally. whole tables. The of data. Relations (or sets) are treated. in the relational. approach.. 37.

(54) .. procedura 1. Less. productivity. t. promotes. y. retrieval. the. as. i. increased. update. and. operation. burdens Are placed on the system. ^. .. Theoretical Foundation. Unlike the hierarchical or network structure, the relational. DBMS. a.re. Relational. model.. foundation. systems. network. while. on a theoretical relational. based. used. and. model. this. hierarchical. as. DBMS. a. have. evolved over the years to accomplish as many requirements as. Because. possible.. theoretical. foundation,. relational systems have. results. the. of. a. sound. relational. operations are easily predictable. The. relational. next. chapter. model. and. examines offer. shor tcomi ngs. 38. deficiencies of the. solutions. to. these.

(55) -. THE RELATIONAL ftPPROftCH. V.. PROBLEMS AND SOLUTIONS. :. INTRODUCTION. A.. Despite. growing. popularity. and. belief. the. relational DBMS will be the system of choice too-distant. future,. standards set. the. heavy. from. input/output. systems require significant resources, and. suited. in. lengthy. run-times,. in. However, the. main. semantic. to. many. and memory. CPU. of. maintenance. is. are not we 1. environment relational. the. very time 1. because of concept. is. database. recovery. and backup.. database specialists and researchers, of. meagerness. by. Relational. and that relational DBMS bvb very of. limitation. problems caused. therefore less. relational DBMS. that. areas. the. is. amounts. transaction-heavy. a. unnecessarily complex, weak. and. from pure. than. improvements.. file (database). Others claim. consuming.. rather. 263. technological. to. (i.e.,. This performance degradation. number-crunching CRef. 7:p. responsive. not yet comparable. by the more familiar models. network and hierarchical). comes. in the not-. are not without their faults.. they. The performance of relational DBMS is to. that. relational This. this. chapter. semantic. offer solutions to these problems. on easing the formulation. systems. 39. their. will focus on the. meagerness. and will. The main goal will be. of queries. system's semantics.. is. and increasing the.

(56) .. B.. MENUS AND VIEUS. technology. Database years. few. ago.. academicians. of. foundation),. theoretical. practical applications.. importance. (probably. all. technology,. computer experience. and. even. frequently. seeing. of. a. its. widespread. Due to the rapid improvements in. the. must. to. because. now. is. of. and. interest. of. Relational database technology, always. organizations.. favorite. now. is. It. an esoteric subject only a. was. less. with. interact. users. with little. database experience the. organization's. da t abase 1. .. Prob lems To. database via all. seeker. naive. a a. high-level. of. information. query interface,. from it. the. is not at. obvious why one question may be asked and another may. not be. asked [Ref.. 8:p.. 113].. naive user expects. The. certain queries to be answered such as:. SELECT NAME WHERE job_title. =. "manager". but does not understand why a query such as "How. Smith. related. to. the. Personnel department?" cannot be. answered (assuming of course formulate. query to. a. exist. a. querying. the user. retrieve such. the second query is a query not. is Paul. is first. able to. information).. on meta-data. That. and there does. facility for database schema (as. opposed to data values) does not occur to the user.. This. lack of familiarity and knowledge of the relations within. ^0.

(57) the database places a constraint on the user. that causes. him frustration and inefficiency while using the system.. many. In. relations of. government. a. high. uncommon CRef.. 9:p.. degree 380D. (thirty. even. more). or. a.r. e not. relation (much. each. of. details. lower-level. databases,. Users should not be burdened. .. with remembering the attributes less. commercial. and. as the format and. such. units of the attributes). S. So lut i ons. .. We suggest the implementation of a command called. MENU which names. allows the. of. user to. relation's. a. The command. domains.. For example,. attributes is. the relation with which. become familiar with the its respective. and. typed, followed by the name of. the. user. questions about.. has. the user types:. if. MENU TEACHER The system would reply:. Relation TID COURSE CLSIZE CREDIT DISTRICT This command,. =. TEACHER alphanumeric string string integer string at. the user's fingertips, provides a quack. "explanation" of the relation or. a. experienced. more. hardcopy. This. users.. located. in. a. is. manual. ref am i. 1 i. ar. i. zat i on for. effective. which. may. than be. a. lost,. mutilated, not updated as the relations change, and which ^1.

(58) is. time-consuming and difficult. use. to. locating. (i.e.,. the appropriate information).. reference. A. entity occurrence. attribute. as its. reference attribute. i. (. .. e. ._,. 10:p. 288D has an. CRef.. value.. attribute is. If an. its domain is another entity or. relation) the system must indicate this.. domain. the. DISTRICT. the. of. For example,. attribute. in our. consisting. itself. attributes. of. if. TEACHER. relation was no longer just of type string but was relation. a. now a. MAYOR. and. POPULATION, the system should respond to the command MENU TEACHER with the following. :. Relation = TEACHER 1 phanumer ic COURSE string CLSIZE string CREDIT integer DISTRICT MAYOR POPULATION integer a. If. user. the. string. somewhat. was. TEACHER relation but was not quite. attribute's properties should. allow. attribute.. the. the user. domains may. the familiar. its domain),. the system. would respond with. that. by entering a. be examined. dot-notation. .. :. Attribute = DISTRICT MAYOR string POPULATION integer ^2. particular. its attributes and. "MENU TEACHER DISTRICT. may type. the. particular. access. to. with. a. sure of. A reference attribute and. their respective. command in. user. (such as. familiar. In this .. ". example. The system.

(59) .. The system nesting.. should allow is. It. degree of. to any. obviously in the database administrator's. interest. best. such notation. degree. this. limit. to. nesting to a. of. reasonable level to provide the users with a less complex. database totality. "The. viewed as 9:p.. collection. a. 379]. It. data. of. in. data bank may be. a. of time-varying. CRef.. relations.". these changes in the make-up of. is due to. the relations in the database that a command such as MENU and its extensions must be available.. solution. Another unf ami. 1 i. ar. to have. i. representation of pictures. that when called presents a graphical. very. provide. more. structure to. serves. command mentioned. concrete. image. of. view will. the. relation,. attributes,. attribute. is. with. and. the attribute and. domains.. This. its. A skeleton of. of. the. domains. is. displayed.. type,. the. a reference. associated. relation. (or. data. in a relation. name. the. their. tabular. not be shown in order to. prevent any possible unauthorized access. the. the same. earlier, but may. actual data. The. the user.. desires to. command. This. well).. the MENU. a. relation (humans remember. particular. a. purpose as. the user. users'. of. ty with relations within the database would be. command. a. problem. the. to. relation,. its If. an. skeleton would name. attributes. and their. reference type attribute). within a relation should be able to be viewed by the user -^3.

(60) t. .. through "relation. reduction", the technique of examining relations.. relations within other "VIEW TEACHER". ". such. as. the skeleton. (see Figure 5.1). would show. of the TEACHER relation.. command. A. the DISTRICT attribute wants. If. TEACHER TID. ALPHANUMERIC. COURSE. CLSIZE. CREDIT. DISTRICT. STRING. STRING. INTEGER. REFERENCE. Command View Teacher. Figure 5.1. to be. examined,. should present. the command "VIEW TEACHER DI STRICT .. to. DISTRICT), the. case,. its associated. attribute name (in this. user the. the. name of. the owner. relation,. and. domains (See Figure 5.S).. attributes and. The MENU and VIEW commands are very simple commands which. provide users with. fingertip tool to examine closer. a. (TEACHER). DISTRICT MAYOR. POPULATION. STRING Figure 5.2.. Command View Teacher D i str .. each relation. within the. enable. to. users. semantics f ormu. 1. a. t. i. of. each. INTEGER. have. database. a. better. relation. which. on. ^^. i. c. These commands will. understanding will. aid. of the in query.

(61) SET-TYPE ATTRIBUTES. C.. As mentioned previously, a. relational. model. meagerness. semantic. its. is. major limitation. meagerness.. exacerbated by the lack of. is. of the. a set. as. This a. type. for an attribute. 1. Prob lems. .. The. lack. schemas from. of. a. set-type. modeling in. the real-world. restricts. complete. and expressive way. relationships between. entities which may. a. be any distinguishable thing or object).. example. An. having. attribute. expressiveness shortcomings. a. record to be a collection. atomic. (. nondecomposab le. the case. is. said. ). of single-valued. value.. to be. relation. to depict. then. a. the. tuple. individual. instructors must. exist. each in. normal form (INF).. A. two dimensional. a. in the. If. columns would be Figure 5.3 is. the relation wishes. particular the. relation. instructor that student has.. nomalized relation.. In. is. than. call SCHOOL.. facts.. A relation where this. in first. homogeneous entries. we will. to. record must be an. a. needed if the INF requirement was not met. a. 286]. Normalized systems require. more complicated data structure table with. 10:p.. respect. with. item within. each data. other words,. CRef.. illustrate the problems of not. will. set-type. a. relational. student has for. each. This is called a. Figure 5.^ condenses this. However,. relation by relaxing the INF constraint. 45. Whereas the.

(62) SCHOOL. INSTRUCTOR. SID. YR. 126. SOPH. SMITH. 126. SOPH. JONES. 1E6. SOPH. DRAKE. Relation School. Figure 5.3. SCHOOL. Figure 5. SID. YR. INSTRUCTOR. 126. SOPH. SMITH, JONES, DRAKE. .. A-. Relation School with Set-Type Attribute. .. schema previously would have been SCHOOL yr. STRING;. :. implemented STRING;. it. instructor. becomes. instructor. INSTRUCTOR is saves space. now. SCHOOL SET_OF. :. a. 1153.. (sid. set-type yr. :. The attribute The set-type. an. entity CRef.. By retrieving this one record,. the user will. set-type, a record. whole. a. INTEGER;. redundancy and truly allows the. by reducing. and his instructors.. the. :. INTEGER;. :. attribute.. facts. about. see the natural or true relationship. a. with. STRING).. set-type. record to contain all the 8:p.. STRING). :. (sid. story. If. the INSTRUCTOR attribute was not. retrieved would. that was (i.e.,. instructor retrieved from one. students' only. between the student. the. user. may. not tell. believe. particular record. was the. instructor which may not be the case). ^6. the. A.

(63) .. lack of a set-type in addition. restriction. a. record from possibly containing all the facts about. on a an. to placing. entity. entities. creates. problems.. other. do not have single-valued. information which can. easily be represented within the field of 8:p.. For. 1163.. example,. real-world. Many. record [Ref. a. relation called TEAM may not. a. happen to have any single-valued. facts.. team may. The. have numerous players, many games, several coaches, and a large. number. relations. of. (i.e.,. containing an. potential. recruits.. TEAM_PLAYERS. attribute. TEAM_COACHES. ,. necessary to. is. number. A ,. of. etc.) each. store the data. rather than simply one relation called TEAM.. Many. been. have. changes. proposed to enable the. relational model to better capture the. Some have proposed adding semantic. real-world entities. data. model. semantics between. capabilities. systems. relational. to. combining the advantages of both models [Ref. Others have proposed extensions to the. 10:p.. thus 286!].. relational model,. such as Codd's extended relational model RM/T.. However,. relaxation. of. first normal form (INF). This proposal,. constraints is the most common proposal. in effect,. would allow. As mentioned earlier,. relational model's. be of a set-type.. the lack of a set-type inhibits the. expressivity.. arises from the lack of formulation.. attributes to. a. set-type. Another problem which. attribute. Asserting that the lack of ^7. a. is query. set-type. is a.

(64) .. ((. cause. main. difficulties. user. of. formulating many. in. queries, focus will now be directed to this problem.. make. To. formulation. query. the. transparent, we introduce four relations.. relation underlined.. attribute. the. is. CRef.. ll:p.. set. or. issue. more. The key of the. attributes. of. 23. student sname district) stud ies sname course developed tname course teacher tname district) ,. ). ,. (. ). ,. ,. As stated earlier, due to the lack of. some entities. are inappropriate. relation. The TEAM. relation. this.. With. smaller. degree. relations. set-type. for storage in a single. served. single-valued. no. a. as. example of. an. attributes,. would. better. a. number of. represent. the. ent i ty. same reason,. For the. forced to lists. create. studying.. If. could have the user. a. separate. a. courses. the. in. relation,. particular. a. set-type. been stored. been. in the. formulation. a. more. example, two relations. When a. through. STUDIES, which. student. is. implemented,. currently this data. STUDENT relation, giving. more concise, semantic view of the data.. a. set-type make. Why does the lack of a. where if. the above example we are. a. set-type. user attempts the. mental. difficult a.re. process?. needed (STUDENT. formulate. gymnastics ^8. In the above. and STUDIES). one would suffice.. were available, to. user query. a. query,. he must go. of joining relations to.

(65) t. :. result in the desired final relation from which he desires. retrieved.. will be. This is not an easy task. for users of any experience level.. relational systems. for. criticism of. Recent. has been the difficulty users have in. understanding joins. manageable. Languages such as SQL and. programming. but. example. best. formulation problem.. QUEL are. not for users; and the. reason is brought to light with joins CRef. An. the data. lS:p. 703.. demonstrates. the. query. Consider the following query in SQL. form. SELECT student sname teacher tname student, teacher FROM WHERE student d i str c t = teacher d i str This. .. ,. .. 1. obviously. is. relations. .. very. a. required. are. .. to. simple. relational algebra expression is. c. Only. query.. two. The equivalent. joined.. be. i. :. PROJECT sname, tname (student. teacher). IXI. The projection retrieves the (sname,. tname) pair who live. in the same district.. For. an. translation. of. teachers who not very. "Find. live in. difficult.. mental manipulation. quite so tname been. programmer. experienced. easy but. the. names. of. the. the same district" For. user,. or. students and. into a query is. inexperienced. an. the. user,. the. required to answer this query is not yet still. manageable.. set-type attributes, 49. If. sname and. the TEACHER and STUDENT.

(66) combined into. relations could have been the beginning. one relation in. relation called STUD_TEACH) and the. (a new. query would have been reduced to:. SELECT FROM WHERE. sname, tname stud_teach stud_teach. district. As can be seen,. =. X.. the gain (query-wise) of. having set-type. attributes in this particular case is minimal.. This will. not be the case with more difficult queries.. Let's consider a more challenging query:. SELECT sname FROM student IN WHERE district (SELECT district FROM teacher WHERE tname IN (SELECT tname developed FROM UHERE course = "math")).. Translating equivalent. is. this. not. so. query. However,. easy.. attributed to SQL syntax, which basic.. into. in. this. its. English. this cannot be case,. is very. Since any query one can formulate in SQL may also. be formulated. structure to the query. QUEL,. in. write. query. the. in. QUEL-like. this contributes to the semantic of. see if. :. SELECT student sname FROM student, teacher, developed WHERE student .district = teacher d i str i c t devel oped tname = teacher tname developed course = "math". .. .. .. .. .. 50. AND AND.

(67) .. This is equivalent to:. PROJECT sname (PROJECT sname, tname (student !X! teacher) ;XI PROJECT tname (developed UHERE course = "math")). The first nested projection retrieves the (sname, tname). sname. The second. district. after. where. pair. tname. and. in the same. live. nested projection. retrieves tname. has been selected where tname developed the math. it. course.. These. attribute. tname,. two. and. relations sname. are. joined. over. the. is. projected. from. a. QUEL-like. query does. this. rel at ion. This transformation. little to is. of. to. help us with the semantics of the query.. This. because one still must visualize the query as a series natural. joins. common attributes.. occurring As. between the relations over. easily digest joins, especially more than place within "List the. teacher who. the same. students who. a. a. couple taking. The English translation,. query.. live in the same district as the. developed the. formulate into. humans do not. earlier expressed,. math course". query because. it. separate relations.. 51. is. difficult to. requires joins of three.

(68) .. ). One more example will further illuminate matters:. SELECT sname FROM student IN WHERE district (SELECT district FROM teacher WHERE tname IN (SELECT tname developed FROM IN WHERE course (SELECT course FROM studies WHERE sname = student sname .. This query is still more difficult. is not. due to. The. Again,. this. syntax, but may be attributed to. the SQL. another situation where multiple joining of. required.. ))). equivalent. query. relations is. "QUEL-like". in. SQL. struc ture is:. SELECT student sname FROM student, studies, developed, teacher WHERE student .district = teacher d istr ic t AND student sname = stud ies sname AND stud ies course = deve loped course AND developed tname = teacher tname .. .. .. .. .. .. .. This is equivalent to. express ion. .. the. following. relational algebra. :. (student PROJECT sname (PROJECT sname, tname teacher INTERSECT (developed PROJECT sname, tname stud ies ). Here,. sname, city.. the. first nested. second. projection retrieves the. projection. in the same. retrieves the same pair. where sname studies the course developed intersection and. IXI. .. tname pair where sname and tname live The. !X:. subsequent projection results in 52. The. by tname. a. list.

(69) of the student names who. live in the same district as the. teachers who developed the courses they are studying. This query more. clear. users. to. as. intersection, appears. the set concept. natural. the. in. much. joins,. user's less. supported or strengthened by which. users. "softened". The difficult by the presence. operator which serves to re-establish. intersect. the. expression.. is. namely. operator,. set. a. in the. task of joining relations of. algebra is semantically. in relational. sequence. a. naturally compartmentalize. of them,. the other hand, users. On. and willingly. think. in terms. of sets but do not often mentally perform natural. Thus the lack. of. set-type,. a. joins.). requiring multiple. by. take place, forces users to think in terms of. joins to. physical database structure relate. is not. real-world occurrences with. identify.. may. concept of. The. mind.. world. real. the. to. and. they cannot. concept. a. (joins). a. opposed to the. as. natural concept of sets and set operations. The notion that each relation within the database is a set where each is an. element of. user's mind.. tuple (or record) within the relation. that. When. set. multiple. join). sequence through. the relation is a. within the. inconsistent. the user employs basic set operators. such as SELECT, he expects a. retrieved from. is. list. set. or. specified.. performed,. the. user. of. X. to be. When a join (or must mentally. series of steps that deal with lower53.

(70) :. ). t. .. level concepts.. traversal". "low-level. This. lay aside the thought of the set. user to. causes the. to be. retrieved. until the notions of common attributes, domains, etc. are dea It with. 2. So lut i ons. .. The proposed solution to the problem involves the. obvious addition of. set-type to. a. relational DBMS.. Any. attribute may be declared of type set within the database The declaration is of the form. schema.. Our sample. entity name).. :. SET_OF (type or. database schema may be defined. as foil ows. student (name STRING; instructor SET_OF develop; district STRING) develop (name STRING; course STRING; classes SET_OF study study (name STRING; course STRING) teacher (name STRING; district STRING) CRef. ll:p. 7] :. :. :. :. :. :. :. :. :. :. The query "List the students who live in the same. district as. the teacher. who developed. the math course". may be programmed as follows:. SELECT student WHERE student instructor CONTAINS develop WHERE deve op course = "math" AND student d i str ic t = teacher 1. .. .. .. d. i. s tr ic. Noticeably absent from this query is the presence Although more than one relation is involved. of. a. in. answering the query. does. join.. not. see. the. (student. explicit. student relation we "placidly. and. join.. develop). Instead, from the. migrate" into 5^. the user. the develop.

No results found