Download Database Access and Distributed Databases: An Overview and more Study notes Deductive Database Systems in PDF only on Docsity!
Chapter 10Chapter 10
Database Access Database Access from Client Applicationsfrom Client Applications
Query by Example (QBE) Query by Example (QBE)
n n No official standardNo official standard n n First pass at SQLFirst pass at SQL n n Interactive querying or updatingInteractive querying or updating n n Usability hierarchyUsability hierarchy n n objectsobjects n n functions/expressionsfunctions/expressions n n macrosmacros n n VBAVBA n n APIAPI
Building Queries using QBE Building Queries using QBE
n n Visual image of the table is used for writing queries.Visual image of the table is used for writing queries. n n Result is aResult is a dynasetdynaset
nn not a base tablenot a base table n n dynamic or virtual setdynamic or virtual set n n may or may not be updateablemay or may not be updateable n n Joins (relationships) based on relationship view orJoins (relationships) based on relationship view or created in query created in query
Joins Joins
n n Inner (normal) joinInner (normal) join n n instructors and sections they teachinstructors and sections they teach n n Outer joinOuter join n n all classes and scheduled sectionsall classes and scheduled sections n n Self joinSelf join n n students and their advisorsstudents and their advisors
Nested Queries Nested Queries
n n Use first query to compute lists or statistics necessaryUse first query to compute lists or statistics necessary for second query for second query n n which students have any status with MIS 320?which students have any status with MIS 320? n n which students have no status with MIS 320?which students have no status with MIS 320? n n which sections have two or more students (with anywhich sections have two or more students (with any status)? status)?
nn group header [repeatable]group header [repeatable] n n detaildetail n n group footergroup footer n n page footerpage footer n n report footerreport footer
Access as a Front End Access as a Front End
n n Access as the file server, uses built-in JET engineAccess as the file server, uses built-in JET engine n n ODBC with JET, translates queries from Access toODBC with JET, translates queries from Access to server SQL server SQL n n SQL pass-through, query must be written for serverSQL pass-through, query must be written for server SQL SQL n n ODBCdirect, an extension to JETODBCdirect, an extension to JET
Embedded SQL Embedded SQL
n n SQL commands in 3GL programsSQL commands in 3GL programs n n more flexible, accessible interfacemore flexible, accessible interface n n improve performanceimprove performance n n improve database securityimprove database security
nn Host programHost program (^) BB (^) precompilerprecompiler (^) BB Source code andSource code and expanded SQL expanded SQL BB compilercompiler BB Object codeObject code BB linker/editorlinker/editor BB Executable code Executable code
Visual Basic for Applications Visual Basic for Applications (VBA)(VBA)
n n BenefitsBenefits n n complex functionalitycomplex functionality n n error handlingerror handling n n faster executionfaster execution n n maintenancemaintenance n n OLE automationOLE automation n n more programmatic controlmore programmatic control n n easier to readeasier to read n n Event-drivenEvent-driven n n event occursevent occurs n n event detectedevent detected n n response generatedresponse generated
Chapter 11 Chapter 11
Distributed Databases Distributed Databases
Distributed Database Distributed Database
nn Same DBMS at each locationSame DBMS at each location n n All data managed by the distributed DBMSAll data managed by the distributed DBMS n n One global schemaOne global schema
DDBMS Objectives DDBMS Objectives
n n Location transparencyLocation transparency n n Local autonomyLocal autonomy n n Synchronous or asynchronousSynchronous or asynchronous n n Advantages:Advantages: n n increased reliabilityincreased reliability n n local controllocal control n n modular growthmodular growth n n lower communications costslower communications costs n n faster responsefaster response
Options for Distributing Options for Distributing
a Database a Database
n n Data replicationData replication n n Horizontal partitioningHorizontal partitioning n n Vertical partitioningVertical partitioning
nn Combinations of the aboveCombinations of the above
Data Replication Data Replication
n n AdvantagesAdvantages n n reliabilityreliability n n fast responsefast response n n less complicated integrity routinesless complicated integrity routines n n node decouplingnode decoupling n n reduced network traffic at prime timereduced network traffic at prime time n n DisadvantagesDisadvantages n n storage requirementsstorage requirements n n complexity and cost of updatingcomplexity and cost of updating
More on Data Replication More on Data Replication
n n SchemesSchemes n n snapshot replicationsnapshot replication n n near real-time replicationnear real-time replication n n pull replicationpull replication n n Replication favored whenReplication favored when
nn data management functions such as securitydata management functions such as security n n consistency across locationsconsistency across locations
DDBMS Architecture DDBMS Architecture
n n Each site:Each site: n n local DBMSlocal DBMS n n local databaselocal database n n distributed DBMSdistributed DBMS n n distributed data repositorydistributed data repository n n communications controllercommunications controller
DDBMS Objectives DDBMS Objectives
n n Location transparencyLocation transparency n n as if all data located at a single nodeas if all data located at a single node n n Replication transparencyReplication transparency n n Failure transparencyFailure transparency n n detect … reconfigure … recoverdetect … reconfigure … recover n n transaction manager maintains log and concurrency controltransaction manager maintains log and concurrency control scheme scheme n n two-phase commit protocoltwo-phase commit protocol
nn Concurrency transparencyConcurrency transparency n n timestampingtimestamping
