assignments of mb 0034

7/27/2019 Assignments of MB 0034

1/8

MB 0034- Database Management Systems(DBMS)Ensure that you answer all questions according to the marks allocated (not more than 400

words for a 10-mark question and not more than 200 words for a five-mark question).The total page limit shall not exceed 8 pages of A-4 size.

Q1. Differentiate between Traditional File System & Modern Database System?Describe the properties of Database & the Advantage of Database?Answer-

Traditional File Systems Vs Modern Database Management Systems

Traditional File System Modern Database Management Systems

Traditional File system is the system that was

followed before the advent of DBMS i.e., it is theolder way.

This is the Modern way which has replaced

the older concept of File system.

In Traditional file processing, data definition is part

of the application program and works with only

specific application.

Data definition is part of the DBMS

Application is independent and can be used

with any application.

File systems are Design Driven; they require

design/coding change when new kind of data

occurs.

One extra column (Attribute) can be added

without any difficulty

Traditional File system keeps redundant

[duplicate] information in many locations. This

might result in the loss of Data Consistency.

Redundancy is eliminated to the maximum

extent in DBMS if properly defined.

In a File system data is scattered in various files,

and each of these files may be in different formats,

making it difficult to write new application

programs to retrieve the appropriate data.

This problem is completely solved here.

Security features are to be coded in the Coding for security requirements is not


2/8

Application Program itself. required as most of them have been taken

care by the DBMS.

Hence, a data base management system is the software that manages a database, and

is responsible for its storage, security, integrity, concurrency, recovery and access.

The DBMS has a data dictionary, referred to as system catalog, which stores data abouteverything it holds, such as names, structure, locations and types. This data is alsoreferred to as Meta data.

Properties of Database

The following are the important properties of Database:

1. A database is a logical collection of data having some implicit meaning. If the data are

not related then it is not called as proper database.

Advantages of using DBMS

1. Redundancy is reduced

2. Data located on a server can be shared by clients

3. Integrity (accuracy) can be maintained

4. Security features protect the Data from unauthorized access

5. Modern DBMS support internet based application.

6. In DBMS the application program and structure of data are independent.

7. Consistency of Data is maintained

8. DBMS supports multiple views. As DBMS has many users, and each one of them

might use it for different purposes, and may require to view and manipulate only on a

portion of the database, depending on requirement.

Q2. What is the disadvantage of sequential file organization? How do youovercome it? What are the advantages & disadvantages of Dynamic Hashing?Answer- In this file organization, the records of the file are stored one after another both

physically and logically. That is, record with sequence number 16 is located just after the 15threcord.A record of a sequential file can only be accessed by reading all the previous records.It is the programmer's responsibility to take care of the record sizes in files. You must be carefulwhen declaring record structures for files. Any mistake you make in record sizes will cause your


3/8

program to read/write erroneous information. This is especially dangerous if the file contents are being altered (changed, updated).Since the records are simply appended to each other when building SEQUENTIAL files, yousimply end up with a STREAM of byte. If this string does not contain any "Carriage Return/LineFeed" control characters in it,Please note that LINE SEQUENTIAL files have two extra characters for each record. For files,

which have millions of records, this might use up a significant amount of disk space.It is NOT possible to delete records of a seq file. If you do not want a specific record to be kept ina seq file any more.

To overcome these disadvantages some of the following hashing techniques are in use:One disadvantage of sequential file organization is that we must use linear search or binary searchto locate the desired record and that results in more i/o operations. In this there are a number of unnecessary comparisons.The basic terms associated with the hashing techniques are:1) Hash table: It is simply an array that is having address of records.2) Hash function: It is the transformation of a key into the corresponding location or address inthe hash table (it can be defined as a function that takes key as input and transforms it into a hashtable index).3) Hash key: Let 'R' be a record and its key hashes into a key value called hash key.The different hashing techniques are:Internal HashingDynamic hashingExtendable hashingAdvantages of dynamic hashing:1. The main advantage is that splitting causes minor reorganization, since only the records in one

bucket are redistributed to the two new buckets.2. The space overhead of the directory table is negligible.Disadvantages:1. The index tables grow rapidly and too large to fit in main memory. When part of the index table

is stored on secondary storage, it requires extra access.2. The directory must be searched before accessing the bucket, resulting in two-block accessinstead of one in static hashing.

Q3. What is relationship type? Explain the difference among a relationshipinstance, relationship type & a relation set?Answer-Relationship type: Basically Relationshipe type is Client-Server ModelThe Client-Server model is basic to distributed systems; it allows clients to makerequests that are routed to the appropriate server in the form of transactions. The client-server model consists of three parts.1. The client passes a user query and decomposes it into a number of independent sitequeries. Each site query is sent to the appropriate server site.

2. Each server processes the local query and sends the resulting relation to the clientsite.3. The client site combines the results of the queries to produce the result of theoriginally submitted query.So the server is called database processor or back end machine, where as the client iscalled application processor or front end machine.1. Server The server is the machine that runs the DMS software. It is referred to as backend. The server processes SQL and other query statements received from clientapplications. It can have large disk capacity and fast processors.


4/8

2. Network The network enables remote data access through client server and server-to-server communication.

Advantages:Client applications are not dependent on physical location of the data. If the data ismoved or distributed to other database servers, the application continues to function withlittle or no modification.It provides multi-tasking and shared memory facilities; as a result they can deliver thehighest possible degree of concurrency and data integrity.In networked environment, shared data is stored on the servers, rather than on allcomputers in the system. This makes it easier and more efficient to manage concurrentaccess. Inexpensive, low-end client work stations can access the remote data of theserver effectively.Security and Integrity ViolationsMisuse of database can be categorized as being either intentional or accidental.

Accidental loss of data consistency:1. System crashes during transaction processing2. Due to multi-users accessing the database.3. Distribution of data over several computers.Intentional loss of data may be due to reading, writing or destruction of data byunauthorized users.

Q4. What is SQL? Discuss.

Answer-SQL stands for Structured Query language

The Structured Query language is used for programming the database. The history of SQL began in an IBM laboratory in San Jose, California, where SQL was developed inthe late 1970's. SQL stands for structured Query Language. It is a non-procedurallanguage, meaning that SQL describes what data to retrieve delete or insert ,

A SQL query is not-necessarily a question to the database. It can be command to do oneof the following.Create or delete a table.Insert, modify or delete rows.Search several rows for specifying information and return the result in order.Modify security information.THE SQL STATEMENT CAN BE GROUPED INTO FOLLOWING CATEGORIES.1. DDL(Data Definition Language)2. DML(Data Manipulation Language)3. DCL(Data Control Language)4. TCL(Transaction Control Language)

DDL: Data Definition LanguageDML: (Data Manipulation Language)The DML statements are used to alter the database tables in someway. The UPDATE,INSERT and DELETE statements alter existing rows in a database tables, insert newrecords into a database table.DCL: (Data Control Language)The Data Control Language Statements are used to Grant permission to the user andRevoke permission from the user, Lock certain Permission for the user.SQL DBA>Revoke Import from Akash;


5/8

SQL DBA>Grant all on emp to public;SQL DBA>Grant select, Update on EMP to L.Suresh;SQlDBA>Grant ALL on EMP to Akash with Grant option;Revoke: Revoke takes out privilege from one or more tables or views.SQL DBA>rEOKE UPDATE, DELETE FROM l.sURES;SQL DBA>Revoke all on emp from AkashTCL: (Transaction Control Language)It is used to control transactions.Eg: CommitThe DDL statement provides commands for defining relation schema i,e for creatingtables, indexes, sequences etc. and commands for dropping, altering, renaming objects.SQL* COMMANDS:This subsection discusses the often used commands in sql environment. For example, if your SQL commands are saved in a file (typically in note pad) you can execute this fileusing an "at" @command, similarly there are a number of such commands:@ Runs the command file stored in

DATA TYPES IN ORACLE 8i SQL:The fig . shows the complete listing of the data types allowed in oracle.

DATA TYPE DESCRIPTION

CHAR (sizs) Fixed length character. Max = 2000

VARCHAR2(size) Variable length character. Max=4000DATE Date, valid range is from jan1,4712 B.C to.

DEC 31,4712 A.D.

BLOB Binary large object Max =4GB

CLOB Character large object Max=4G.B.


6/8

BFILE Pointer to binary OS file

LONG Character data of variable size, Max=2G.B.

LONG RAW Raw binary data. Rest is same as long

NUMBER (size) Numbers. Max. size =40 digits

NUMBER(size,d) Numbers, range=1.0E-130 to 9.9E125

DECIMAL Same as NUMBER. Size /d can't be specified

FLOAT Same as NUMBER

Q5. What is Normalization? Discuss various types of Normal Forms?Answer- Introduction to NormalizationIn this unit we will study how to normalize the data in the database. Normalization is theprocess of building database structures to store data, because any application ultimatelydepends on its data structures. If the data structures are poorly designed, the applicationwill start from a poor foundation. This will require a lot more work to create a useful andefficient application.Normal forms Based on Primary Keys

A relation schema R is in first normal form if every attribute of R takes only single atomicvalues. We can also define it as intersection of each row and column containing one andonly one value. To transform the un-normalized table (a table that contains one or morerepeating groups) to first normal form, we identify and remove the repeating groupsSecond Normal Form (2 NF)

A second normal form is based on the concept of full functional dependency. A relation isin second normal form if every non-prime attribute A in R is fully functionally dependenton the Primary Key of R.

Normalizing EMP_DEPT into 3NF relations A Partial functional dependency is a functional dependency in which one or more non-key attributes are functionally dependent on part of the primary key. It creates aredundancy in that relation, which results in anomalies when the table is updated.Third Normal Form (3NF)This is based on the concept of transitive dependency. We should design relationalschema in such a way that there should not be any transitive dependencies, becausethey lead to update anomalies. A functional dependence [FD] x->y in a relation schema'R' is a transitive dependency. If there is a set of attributes 'Z' Le x->, z->y is transitive.

A relation schema R is in 3NF when it satisfies the conditions below.1. It is fully functionally dependent on every key of 'R'2. It is non_transitively dependent on every key of 'R'Fourth Normal Form (4NF)Multi valued dependencies are based on the concept of first normal form, which prohibitsattributes having a set of values. If we have two or more multi valued independentattributes in the same relation, we get into a situation where we have to repeat everyvalue of one of the attributes, with every value of the other attributes to keep the relationstate consistent, and to maintain independence among the attributes involved. This


7/8

constraint is specified by a Multi valued dependency.

Q6. What do you mean by Shared Lock & Exclusive lock? Describe briefly twophase locking protocol?Answer-

Shared Locks: It is used for read only operations, i.e., used for operations that do notchange or update the data.E.G., SELECT statement:,Shared locks allow concurrent transaction to read (SELECT) a data. No other transactions can modify the data while shared locks exist. Shared locks are released assoon as the data has been read.Exclusive Locks: Exclusive locks are used for data modification operations, such asUPDATE, DELETE and INSERT. It ensures that multiple updates cannot be made to thesame resource simultaneously. No other transaction can read or modify data whenlocked by an exclusive lock.Exclusive locks are held until transaction commits or rolls back since those are used for write operations.There are three locking operations: read_lock(X), write_lock(X), and unlock(X). A lockassociated with an item X, LOCK(X), now has three possible states: "read locked","write-locked", or "unlocked". A read-locked item is also called share-locked, becauseother transactions are allowed to read the item, whereas a write-locked item is calledexclusive-locked, because a single transaction exclusive holds the lock on the item.Each record on the lock table will have four fields: . The value (state) of LOCK is either read-locked or write-locked.read_lock(X):B, if LOCK(X)='unlocked'Then begin LOCK(X)"read-locked"No_of_reads(x)1endelse if LOCK(X)="read-locked"then no_of_reads(X)no_of_reads(X)+1else begin wait(until)LOCK(X)="unlocked" andthe lock manager wakes up the transaction);goto Bend;write_lock(X):B: if LOCK(X)="unlocked"Then LOCK(X)"write-locked";else beginwait(until LOCK(X)="unlocked" and

the lock manager wkes up the transaction);goto Bend;unlock(X):if LOCK(X)="write-locked"Then begin LOCK(X)"un-locked";Wakeup one of the waiting transctions, if anyendelse if LOCK(X)=read-locked"


8/8

then beginno_of_reads(X)no_of_reads(X)-1if no_of_reads(X)=0then begin LOCK(X)=unlocked";wakeup one of the waiting transactions, if anyendend;The Two Phase Locking ProtocolThe two phase locking protocol is a process to access the shared resources as their ownwithout creating deadlocks. This process consists of two phases.1. Growing Phase: In this phase the transaction may acquire lock, but may not releaseany locks. Therefore this phase is also called as resource acquisition activity.2. Shrinking phase: In this phase the transaction may release locks, but may not acquireany new locks. This includes the modification of data and release locks. Here twoactivities are grouped together to form second phase.IN the beginning, transaction is in growing phase. Whenever lock is needed thetransaction acquires it. As the lock is released, transaction enters the next phase and itcan stop acquiring the new lock request.

assignments of mb 0034

Documents