NAVAL POSTGRADUATE SCHOOL Monterey, California THESIS A PROTOTYPE SEMANTIC INTEGRITY FRONTEND EXPERT SYSTEM FORA RELATIONAL DATABASE by GeorgeJoseph Salitsky September, 1991 Thesis Advisor: Magdi N. Kamel Approved for public release; distribution is unlimited REPORT DOCUMENTATION PAGE la.REPORTSECURITYCLASSIFICATION 1b RESTRICTIVEMARKINGS UNCLASSIFIED 2a.SECURITYCLASSIFICATIONAUTHORITY 3 DISTRIBUTION/AVAILABILITYOFREPORT Approvedtor publicrelease;distributionisunlimited. 2b DECLASSIFICATION/DOWNGRADINGSCHEDULE 4 PERFORMINGORGANIZATIONREPORTNUMBER(S) 5 MONITORINGORGANIZATIONREPORTNUMBER(S) 6a.NAMEOFPERFORMINGORGANIZATION 6b OFFICESYMBOL 7a NAMEOFMONITORINGORGANIZATION NavalPostgraduateSchool (Ifapplicable) NavalPostgraduateSchool 37 6c ADDRESS(City,State,andZIPCode) 7b ADDRESS(City,State,andZIPCode) Monterey,CA 93943-5000 Monterey,CA 93943-5000 8a NAMEOFFUNDING/SPONSORING 8b OFFICESYMBOL 9 PROCUREMENTINSTRUMENTIDENTIFICATIONNUMBER ORGANIZATION (Ifapplicable) 8c ADDRESS(City,State,andZIPCode) 10 SOURCEOFFUNDINGNUMBERS ProgramtlementNo ProjectNo TaskNo WorkunitAccession Number 11 TITLE(IncludeSecurityClassification) APROTOTYPESEMANTICINTEGRITYFRONTENDEXPERTSYSTEM FORA RELATIONALDATABASE 12 PERSONALAUTHOR(S) Salitsky.GeorgeJ. 13a.TYPEOFREPORT 13b TIMECOVERED 14 DATEOFREPORT(year,month,day) 15 PAGECOUNT Master'sThesis From To 1991,September,26 141 16 SUPPLEMENTARYNOTATION TheviewsexpressedinthisthesisarethoseoftheauthoranddonotreflecttheofficialpolicyorpositionoftheDepartmentofDefenseortheU.S. Government. 17 COSATICODES 18 SUBJECTTERMS(continueonreverseifnecessaryandidentifybyblocknumber) FIELD GROUP SUBGROUP Databaseintegrity,Frontendexpertsystem,Prototypeexpertsystem.Semanticintegrity. 19 ABSTRACT(continueonreverseifnecessaryandidentifybyblocknumber) Informationisacriticalresourceintoday'senterprises.Whethertheyareindustrial,commercial,educational,ormilitary,theseorganizations maintainaneverincreasingamountofinformationindatabases.Ensuringtheaccuracyofinformationinadatabaseisparamounttothe organizationthatmaintainthesedatabases.Manydecisionsaremadefromtheinformationextractedfromthedatabase,andincorrectdatawill leadtoincorrectdecisionmaking.Thisthesisexaminesthefeasibilityofusingexpertsystemsforenforcingsemanticintegrityconstraintsto relationaldatabases.Toaccomplishthisgoal,thethesisdevelopsaclassificationforsemanticintegrityconstraints,appliesittodeveloprulesfor theNavy'sNavalAircraftFlightRecordapplication,andbuildsafrontendexpertsystemtoenforcetheserulesdynamically.Theexpertsystem enforcesintegrityrulesforallmaintenanceoperations(UPDATE,INSERT,andDELETE.) 20 DISTRIBUTION/AVAILABILITYOFABSTRACT 21 ABSTRACTSECURITYCLASSIFICATION |x| UNCLASSIFIED/UNLIMITED ] SAMEASBEPOR1 ll DTICUSERS Unclassified 22a NAMEOFRESPONSIBLE INDIVIDUAL 22b TELEPHONE (IncludeAreacode) 22c OFFICE SYMBOL Magdi N.Kamel (408)646-2494 AS/KA DD FORM 1473.84 MAR 83APReditionmaybeuseduntilexhausted SECURITYCLASSIFICATIONOFTHISPAGE Allothereditionsareobsolete Unclassified T9RQ qn 1 Approved for public release; distribution is unlimited. A Prototype Semantic Integrity FrontEnd ExpertSystem for a Relational Database by George J. Salitsky Lieutenant, United States Navy B.S., University ofScranton Submitted in partial fulfillment ofthe requirements for the degree of MASTER OF SCIENCE IN INFORMATION SYSTEMS from the NAVALPOSTGRADUATE SCHOOL September, 1991 /7/ ABSTRACT Information is a critical resource in today's enterprises. Whether they are industrial, commercial, educational, or military, these organizations maintain an ever increasing amount of information in databases. Ensuring the accuracy of information in a database is paramount to the organizations that maintain these databases. Many decisions are made from the information extracted from the database, and incorrect data will lead to incorrect decision making. This thesis examines the feasibility of using expert systems for enforcing semantic integrity constraints to relational databases. To accomplish this goal, the thesis develops a classification for semantic integrity constraints, applies it to develop rules for the Navy' s Naval Aircraft Flight Record application, and builds a front end expert system to enforce these rules dynamically. The expert system enforces integrity rules for all maintenance operations (UPDATE, INSERT, and DELETE.) 111 . CI TABLE OF CONTENTS INTRODUCTION I 1 A. BACKGROUND 1 B. OBJECTIVES 2 C. RESEARCH QUESTIONS 3 D. SCOPE 4 E. ORGANIZATION OF THE STUDY 5 II. CLASSIFICATION OF INTEGRITY CONSTRAINTS 6 A. Domain Integrity Constraints 7 B. Column Integrity Constraints 8 C. Entity Integrity Constraints 10 D. Referential Integrity Constraints 11 E. User Defined Integrity Constraints 12 III. NAVAL AIRCRAFT FLIGHT RECORD RELATIONAL DATABASE DESIGN 16 A. BACKGROUND 16 B. AIRCRAFT FLIGHT RECORD OBJECTS 20 1. ORGANIZATION Object 21 2. AIRCRAFT Object 21 3. FLIGHT Object 21 4. AIRCREW Object 22 5. AIRCREW FLIGHT Object 22 6. LOGISTICS Object 23 IV