Differentiate LAN and WAN

Q.n. 1   Differentiate LAN and WAN

	LAN	WAN
Definition:	LAN (Local Area Network) is a computer network covering a small geographic area, like a home, office, schools, or group of buildings.	WAN (Wide Area Network) is a computer network that covers a broad area (e.g., any network whose communications links cross metropolitan, regional, or national boundaries over a long distance
Speed:	high speed(1000mbps)	less speed(150mbps)
Data transfer rates:	LANs have a high data transfer rate	WANs have a lower data transfer rate as compared to LANs
Example:	Network in an organization can be a LAN	Internet is a good example of a WAN
Technology:	Tend to use certain connectivity technologies, primarily Ethernet and Token Ring	WANs tend to use technology like MPLS, ATM, Frame Relay and X.25 for connectivity over the longer distances
Components:	layer 2 devices like switches, bridges. layer1 devices like hubs , repeaters	Layers 3 devices Routers, Multi-layer Switches and Technology specific devices like ATM or Frame-relay Switches etc.
Set-up costs:	If there is a need to set-up a couple of extra devices on the network, it is not very expensive to do that	In this case since networks in remote areas have to be connected hence the set-up costs are higher. However WANs using public networks can be setup verycheaply, just software (VPN etc)
Geographical Spread:	Have a small geographical range and do not need any leased telecommunication lines	Have a large geographical range generally spreading across boundaries and need leased telecommunication lines
Bandwidth:	High bandwidth is available for transmission.	Low bandwidth is available for transmission
Geographical Area:	LAN covers 100 m	WAN covers more than 100 m

Explain the steps involved in recovery of distributed transactions.

q.n.4 Explain the steps involved in recovery of distributed transactions.

Ans. For Recovery of Distributed transactions, let’s assume that at each site a Local Transaction Manager is available. Each agent can issue begin_transaction, commit, and abort primitives to its LTM. After having issued a begin_transaction to its LMT. An agent will possess the properties of a local transaction. We will call an agent that has issued a begin_transaction primitive to its local transaction manager a Sub-transaction. Also to distinguish the begin_transaction, commit, and abort primitives of the distributed transaction from the local primitives issued by each agent to its LTM, we will call the later as local_begin, local_commit, and local_abort.
For building a Distributed Transaction Manager (DTM), the following properties are expected from the LTM:
  Ensuring the atomicity of sub-transaction
  Writing some records on stable storage on behalf of the distributed transaction manager
We need the second requirement, as some additional information must also be recorded in such a way that they can be recovered in case of failure. In order to make sure that either all actions of a distributed transaction are performed or none is performed at all, two conditions are necessary:
  At each site either all actions are performed or none is performed all sites must take the same decision with respect to the commitment or abort of sub transaction.
Begin_transaction: When it is issued by the root agent, DTM will have to issue a local_behin primitive to the LTM at the site of origin and all the sites at which there are already active agents of the same application, thus transforming all agents into sub_transaction; from this time on the activation of a new agent by the same distributed transaction requires that the local_begin be issued to the LTM where the agent in activated, so that the new agent is created as a Sub-transaction. The example of FUND TRANSFER is taken for explaining this concept.
Abort: When an abort is issued by the root agent, all existing sub-transactions must be aborted. Issuing local_aborts to the LTMs at all sites where there is an active sub transaction performs this.
Commit: The implementation of the commit primitive is the most difficult and expensive. The main difficulty originates from the fact that the correct commitment of a distributed transaction requires that al sub-transactions commit locally even if there are failures.
In order to implement this primitive for a distributed transaction, the general idea of 2-Phase commit Protocol has been developed.

Discuss the objectives of Query Processing.

Q.n. 3 Discuss the objectives of Query Processing.
Ans. Objectives of Query Processing
 The main objectives of query processing in a distributed environment is to form a high level query on a distributed database, which is seen as a single database by the users, into an efficient execution strategy expressed in a low level language in local databases.
 An important point of query processing is query optimization. Because many execution strategies are correct transformations of the same high level query the one that optimizes (minimizes) resource consumption should be retained.
 The good measure of resource consumption are:
1. The total cost that will be incurred in processing the query. It is the dome of all times incurred in processing the operations of the query at various sites and intrinsic communication.
2. The resource time of the query. This is the time elapsed for executing the query. Since operations can be executed in parallel at different sited, the response time of a query may be significantly less than its cost.
 Obviously the total cost should be minimized.
1. In a distributed system, the total cost to be minimized includes CPU, I\O, and communication costs. This cost can be minimized by reducing the number of I\O operation through fast access methods to the data and efficient use of main memory. The communication cost is the time needed for exchanging the data between sited participating in the execution of the query.
2. In centralized systems, only CPU and I\O cost have to be considered.

Draw the diagram of logical structure of oracle database and explain it in brief.

Q.n. 2 Draw the diagram of logical structure of oracle database and explain it in brief.

The Oracle database is divided into increasingly smaller logical units to manage, store, and retrieve data efficiently and quickly. The figure shows the relationships between the logical structures of the database.

Logical structure mainly consists of table space, segments, extents, and oracle data blocks

TABLE SPACE 

 Each database is logically divided into one or more table spaces . One or more data files are explicitly created for each table space to physically store the data of all logical structures in a tables pace. The combined size of the data files in a table space is the total storage capacity of the table space.

SEGMENT 

 A segment is a set of extents allocated for a certain logical structure. The segments can be of one of following type data segment, index segment, temporary segment, rollback segment.

EXTENT 

The next level of logical database space is an extent. An extent is a specific number of contiguous data blocks, obtained in a single allocation, and used to store a specific type of information.

ORACLE DATA BLOCKS

 At the finest level of granularity, Oracle database data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk. The standard block size is specified by the DB_BLOCK_SIZE initialization parameter. In addition, you can specify up to five other block sizes.

Differentiate between PL/SQL functions and procedures.

Q.n. 1  Differentiate between PL/SQL functions and procedures.

1. Procedure is named PL/SQL block which perform one or more task but function performs a specific task.
2. Procedure may or may not return value whereas function should return one    value.
3. Function can be called from SQL statements whereas procedure cannot be called from the SQL statements
4. We can call a stored procedure within function but we cannot call function within stored procedure.
5. Functions are normally used for computations whereas procedures are normally used for executing business logic.
6. You can have DML (insert update delete) statements in a function. But you cannot call such a function in a SQL query.
7. Function returns 1 value only. Procedure can return multiple values (max 1024).
8. Stored procedure returns always integer value by default zero. Whereas function returns type could be scalar or table or table values.
9. Stored procedure is precompiled execution plan whereas functions are not.
A procedure may modify an object where a function can only return a value The RETURN statement immediately completes the execution of a subprogram and returns control to the caller.

Difference Between Static Memory and Dynamic Memory

Q.n. 4 Difference Between Static Memory and Dynamic Memory

Static Memory: Static Memory devices are semiconductor memories in which the stored data will remain permanently stored as long as power is applied without the need of periodically rewriting or refreshing the data into the memory. The basic element of this storage is a flip flop or a gate. RAM, Punched Card and Tape are examples of Static Memory.

Dynamic Memory: Dynamic Memory devices are semiconductor memories in which the stored data will not remain permanently stored, even with power applied unless the data is periodically rewritten into

the memory. Data is stored as charge on capacitors. The charge on capacitor has to be periodically refeshed in order to prevent it from leaking away. DRAM & Charge Coupled Device (CCD) are example of Dynamic Memory.

STATIC MEMORY ALLOCATION	DYNAMIC MEMORY ALLOCATION
Memory is allocated before the execution of the program begins. (During Compilation)	Memory is allocated during the execution of the program.
No memory allocation or deallocation actions are performed during Execution.	Memory Bindings are established and destroyed during the Execution.
Variables remain permanently allocated.	Allocated only when program unit is active.
Implemented using stacks and heaps.	Implemented using data segments.
Pointer is needed to accessing variables.	No need of Dynamically allocated pointers.
Faster execution than Dynamic.	Slower execution than static.
More memory Space required.	Less Memory space required.

What are macros and macro processors? Explain in brief.

Q.n.3 What are macros and macro processors? Explain in brief.

A macro is similar to a subroutine (or a procedure), but there are important Differences between them. A subroutine is a section of the program that is writtenOnce, and can be used many times by simply calling it from any point in the program. Similarly, a macro is a section of code that the programmer writes (defines) once, and then can use many times. The main difference between a subroutine and a Macro is that the former is stored in memory once (just one copy), whereas the Latter is duplicated as many times as necessary. Macros involve two separate phases. Handling the definition and Handling the expansions. A macro can only be defined once but it can be expanded many times. Handling the definition is a relatively simple process. The assembler reads the Definition from the source files and saves it in a special table, the Macro Definition Table (MDT).The assembler does not try to check the definition for errors, to assemble it, execute it, or do anything else with it.

A macro processor is a program that copies a stream of text from one place to another, making a systematic set of replacements as it does so. Macro processors are often embedded in other programs, such as assemblers and compilers. Sometimes they are standalone programs that can be used to process any kind of text.

Macro processors have been used for language expansion (defining new language constructs that can be expressed in terms of existing language components), for systematic text replacements that require decision making, and for text reformatting (e.g. conditional extraction of material from an HTML file).