LECTURE NOTES DBMS PART 6 ( 2MARKS)

LECTURE NOTES DBMS PART 6 ( 2MARKS)

111. Explain a join between tables

A join allows tables to be linked to other tables when a relationship between the tables exists.

The relationships are established by using a common column in the tables and often uses the

primary/foreign key relationship.

112. Describe and contrast a trigger and a procedure.

Triggers are stored and controlled in the DBMS. A trigger is executed automatically when a

condition is met (INSERT, UPDATE, or DELETE). A procedure is also stored in a database. A

procedure is not executed automatically.

113. Briefly describe an outer join.

An outer join includes the records that match and those that do not have a matching value in

another table. Outer joins can be a LEFT outer join (includes all records from the first table

listed) or a RIGHT outer join (includes all records from the second table listed). Outer joins are

not easily used with more than two tables.

114. Describe a subquery.

A subquery is a query that is composed of two queries. The first query (inner query) is within

the WHERE clause of the other query (outer query). In some cases the inner query provides

results for the outer query to process. In other cases, the outer query results provide results

for the inner query (correlated subquery).

115. Describe the difference between embedded and dynamic SQL.

Embedded SQL is the process of including hard coded SQL statements. These statements do

not change unless the source code is modified. Dynamic SQL is the process of generating SQL

on the fly. The statements generated do not have to be the same each time.

116. Explain the difference between two and three-tier architectures.

A three-tier architecture includes a client and two server layers. The application code is stored

on the application server and the database is stored on the database server. A two-tier

architecture includes a client and one server layer. The database is stored on the database

server.

117. Describe and contrast SQL and QBE.

QBE is a direct-manipulation database language that uses a graphical approach to query

construction. Some database systems translate QBE queries into SQL. QBE does not adhere to

a standard but SQL does. Both SQL and QBE are relational database languages.

118. Describe ODBC

ODBC is a standard that contains an interface that provides a common language for

application programs to access and process SQL databases. In order to use ODBC, a driver,

server name, database name, user id, and password are required. ODBC is important for

Internet applications and has gained wide acceptance.

119. Compare a thin and a fat client.

A fat client is a PC that is responsible where most processing occurs on the client rather than

the server. Some of the fat client activities include processing, presentation logic, and

business rules logic. A thin client is a PC where only limited processing occurs. In these cases,

more processing should occur on the application server.

120. Describe some reasons that someone familiar with Microsoft Access will want

to learn VBA.

You can perform more complex functions and error handling can be accommodated by VBA.

VBA code will execute faster since code runs faster than macros and maintenance is easier

because VBA modules are stored with the forms and reports. Reading VBA is easier because

you can see the entire code. Finally, you can pass parameters and use OLE automation better.

121. Describe middleware applications that ease the connection of databases to Web

applications.

Both ASP and ColdFusion are middleware that ease the connection of databases to Web

applications. Both require the use of several files. Both use tags and are executed on the

server. Both Internet Explorer and Netscape can access these files. The programmer does not

need to be concerned with the client when they work with this middleware.

122. Describe JavaScript and VBScript.

JavaScript shares many features with Java. Developers use it to achieve interactivity.

JavaScript is an open language and does not require a license. It is supported by both Internet

Explorer and Netscape. VBScript is similar to JavaScript. It is based on Visual Basic but is

simpler. Microsoft developed this language.

123. Describe Web Services.

Web Services are improving the ability of computers to communicate over the Internet. These

services use XML programs and usually run in the background. Easier integration of

applications may be possible because developers do not need to be familiar with the technical

details with applications that are being integrated. UDDI is a technical specification for

creating a distributed registry of Web services and businesses that are open to communicating

through Web services.

124. Provide an overview of XML.

XML is used to structure and manipulate data involved with a browser and is becoming the

standard for e-commerce. XML uses tags that are similar to HTML in that they use the angle

brackets, but XML describes the content whereas HTML describes the appearance. The XML

schema standard was published in May 2001 by W3C.

125. Describe Website security issues.

Website security issues include unauthorized access to the several aspects of one's Website.

Security measures should include all aspects of the system such as the network, operating

level, database, and Web server. Regular monitoring and security testing by a company should

help to avoid intrusion into one's system.

126. Explain the role of metadata for the three-layer architecture.

Each of the three layers has a metadata layer linked with it. The metadata layer describes the

properties or characteristics of the data. The operational metadata describe the data used in

the various operational and external systems. The enterprise data warehouse metadata

describe the reconciled data layer. The data mart metadata describes the derived data layer.

127. Describe why operational and informational systems are separate.

Operational systems are used to run the business in real time on a daily basis and contain

current data. Non-managers and non-analysts work in this type of system. Operational

systems must process large volumes of data. Informational systems are used to support

decisions and contain historical data. Managers and analysts work in this type of system.

Informational systems have periodic batch updates.

128. Describe a data warehouse.

A data warehouse is organized around specific subjects. The data is defined using consistent

naming, format, and encoding structure standards. The data contains a time element, so that

the data can be studied for trends. No data in a data warehouse can be updated by end users.

129. Explain why an information gap exists in most organizations.

One reason that an information gap exists is the fact that systems have been developed in

separate, segmented efforts. This has helped the data from being stored in an integrated

database and thus the data is in an inconsistent structure. The other reason for the gap is that

most systems are created to support the operational aspect of an organization. The systems

were not developed for decision making.

130. Describe the differences between a data warehouse and data mart.

A data warehouse is for the enterprise and contains multiple subjects. A data mart is for a

specific functional area and focuses on one subject. A data warehouse is flexible and dataoriented

and contains detailed historical data. A data mart is restrictive and project-oriented

and contains some historical data.

131. Describe the difference between data and database administration.

Data administration is responsible for the overall management of data resources. Some of the

core roles include the creation of data polices, procedures and standards, resolve data

ownership issues, and manage the information repository. Database administration is physical

database oversight. Some of the core duties include the selection of the DBMS and software

tools, the installation and upgrade of the DBMS, and database performance tuning.

132. What are some of the important security features of a DBMS?

One of the features includes the use of views which allows the presentation of only data

needed by someone and limits the capability of database updates. The use of integrity controls

includes such things as domains, assertions, and checks. Also authorization rules, user-defined

procedures, encryption, authentication schemes, and backups are important.

133. Describe concurrency control.

Concurrency control is the process managing simultaneous operations against a database so

that database integrity is not compromised. There are two approaches to concurrency control.

The pessimistic approach involves locking and the optimistic approach involves versioning.

134. Explain locking.

Locking is denying others the ability to update a record until someone completes the update or

releases the record. Locking can occur on many different levels in a database. It can occur at

the database, table, record, or field level. A lock can be shared (another can read the record

while an update is in progress) or exclusive (no one can read the record while an update is in

progress).

135. Explain issues for database performance.

The installation of the database is an important issue since the better the installation, the

better the performance could be. Memory usage is learning how the DBMS uses main memory

and then using that knowledge to enable better performance. I/O is usually very intense in a

DB, so understanding how users will use the data will help to prepare the database better.

CPU usage and application tuning are also important considerations.

136. Describe the difference between homogeneous and heterogeneous distributed

database.

A homogenous database is one that uses the same DBMS at each node. Either each node can

work independently or a central DBMS may coordinate database activities. A heterogeneous

database is one that may have a different DBMS at each node. It may support some or all of

the functionality of one logical database. It may support full Distributed DBMS functionality or

partial Distributed DBMS functionality.

137. What is a distributed database?

A distributed database is a single logical database that is spread across more than one node or

locations that are all connected via some communication link. It requires multiple DBMSs,

running at each remote site. A distributed database can be either homogenous (same DBMS at

each node) or heterogeneous (different DBMS at some nodes).

138. What is the difference between horizontal and vertical partitioning?

Horizontal partitioning is where some rows of a table are placed into the base relations at one

site and other rows are placed at another site. Vertical partitioning is where some columns of a

table are placed into the base relations at one site and other columns are placed at another

site but each all of these relations must share a common domain.

139. Explain concurrency transparency.

Concurrency transparency is where each transaction in a distributed database is treated as if it

is the only one in the system. Therefore if several transactions are running at one time, the

results will be the same as if each transaction was run in serial order. The transaction

manager helps to provide concurrency control. The three methods that may be used are

locking, versioning, and timestamping.

140. Explain snapshot replication.

Snapshot replication can be used when an application does not require that the data always be

current. These applications can be updated by periodic table copying or snapshots. As part of

the snapshot effort, all of the data to be included in the snapshot is collected at a primary

point. Then a read-only snapshot is taken and the snapshot is sent to each site so that the

update can be made.