YouTube Icon

Interview Questions.

Top 100+ Database System Concepts Interview Questions And Answers - May 29, 2020

fluid

Top 100+ Database System Concepts Interview Questions And Answers

Question 1. List Four Significant Differences Between A File-processing System And A Dbms?

Answer :

Some predominant differences between a database control system and a record-processing system are:

Both structures include a collection of facts and a set of applications which get entry to that information. A database control machine coordinates both the bodily and the logical get right of entry to to the data, whereas a report-processing gadget coordinates handiest the physical access.
A database control gadget reduces the quantity of statistics duplication with the aid of ensuring that a physical piece of facts is available to all programs authorized to have get admission to to it,whereas facts written by means of one application in a record-processing gadget may not be readable via some other software.
A database control gadget is designed to allow bendy get admission to to statistics (i.E., queries), whereas a file-processing machine is designed to permit predetermined access to facts (i.E., compiled programs).
A database control machine is designed to coordinate a couple of users having access to the equal statistics on the identical time. A record-processing systemis normally designed to allow one or more programs to access special statistics documents on the equal time. In a record-processing device, a record may be accessed with the aid of  applications concurrently best if both packages have read-handiest get admission to to the report.
Question 2. This Chapter Has Described Several Major Advantages Of A Database System. What Are Two Disadvantages?

Answer :

Two risks related to database systems are listed under.

Setup of the database gadget calls for greater expertise, money, competencies, and time.
The complexity of the database can also bring about negative overall performance.
Python Interview Questions
Question three. Explain The Difference Between Physical And Logical Data Independence?

Answer :

Physical facts independence is the capability to regulate the bodily scheme with out making it essential to rewrite application packages. Such adjustments encompass converting from unblocked to blocked report garage, or from sequential to random get admission to documents.
Logical facts independence is the capacity to adjust the conceptual scheme with out making it necessary to rewrite application programs. Such a modification might be including a area to a record; an software application’s view hides this modification from this system.
Question four. List Five Responsibilities Of A Database Management System?

Answer :

A widespread purpose database manager (DBM) has five obligations:

interaction with the file supervisor.
Integrity enforcement.
Security enforcement.
Backup and recovery.
Concurrency manipulate.
Python Tutorial
Question five. What Are Five Main Functions Of A Database Administrator?

Answer :

Five predominant features of a database administrator are:

To create the scheme definition
To outline the garage shape and get entry to strategies
To adjust the scheme and/or physical employer whilst important
To provide authorization for facts get admission to
To specify integrity constraints
Teradata Interview Questions
Question 6. List Seven Programming Languages That Are Procedural And Two That Are Non Procedural. Which Group Is Easier To Learn And Use?

Answer :

Programming language type:

Procedural: C, C++, Java, Basic, Fortran, Cobol, Pascal
Non-procedural: Lisp and Prolog
Note: Lisp and Prolog aid a few procedural constructs, but the middle of both those languages is non-procedural. In idea, non-procedural languages are simpler to study, due to the fact they permit the programmer deal with what desires to be performed, in place of a way to do it. This isn't always actual in practice, especially if procedural languages are learned first.

Question 7. List Six Major Steps That You Would Take In Setting Up A Database For A Particular Enterprise?

Answer :

Six foremost steps in setting up a database for a specific company are:

Define the excessive level requirements of the agency (this step generates a record referred to as the machine necessities specification.)
Define a version containing all appropriate sorts of data and information relationships.
Define the integrity constraints at the facts.
Define the bodily level.
For every regarded trouble to be solved on a normal foundation (e.G., obligations to be achieved by using clerks or Web customers) outline a consumer interface to perform the task, and write the vital software packages to put in force the user interface.
Create/initialize the database.
Teradata Tutorial Adv Java Interview Questions
Question 8. Consider A Two-dimensional Integer Array Of Size N ×m That Is To Be Used In Your Favorite Programming Language. Using The Array As An Example, Illustrate The Difference?

Between The Three Levels Of Data Abstraction, And
between A Schema And Instances?
Answer :

Let tgrid be a -dimensional integer array of length n × m.

The bodily level would simply be m × n (likely consecutive) garage locations of whatever length is detailed via the implementation (e.G., 32 bits each).
The conceptual stage is a grid of packing containers, every likely containing an integer, that's n packing containers excessive through m containers extensive.
There are 2m×n viable perspectives. For instance, a view might be the complete array, or precise row of the array, or all n rows but best columns 1 thru i.
Consider the subsequent Pascal declarations: kind tgrid = array[1..N, 1..M] of integer; var vgrid1, vgrid2 : tgrid Then tgrid is a schema, while the price of variables vgrid1 and vgrid2 are instances.
To illustrate in addition, don't forget the schema array[1..2, 1..2] of integer. Two instances of this scheme are:
Question nine. Explain The Distinctions Among The Terms Primary Key, Candidate Key, And Super Key?

Answer :

A super key is a fixed of 1 or extra attributes that, taken collectively, allows us to perceive uniquely an entity inside the entity set. A first rate key may comprise extraneous attributes. If K is a incredible key, then so is any remarkable set of K. A terrific key for which no right subset is also a exquisite secret's known as a candidate key. It is feasible that numerous distinct sets of attributes ought to serve as candidate keys. The primary key's one of the candidate keys this is selected by way of the database fashion designer because the essential method of figuring out entities within an entity set.

SQL Database Interview Questions
Question 10. Construct An E-r Diagram For A Car-coverage Company Whose Customers Own One Or More Cars Each. Each Car Has Associated With It Zero To Any Number Of Recorded Accidents?

Answer :

 Submit

Adv Java Tutorial
Question 11. We Can Convert Any Weak Entity Set To A Strong Entity Set By Simply Adding Appropriate Attributes.Why, Then, Do We Have Weak Entity Sets?

Answer :

We have susceptible entities for several motives:

We need to keep away from the statistics duplication and consequent viable inconsistencies resulting from duplicating the important thing of the robust entity.
Weak entities replicate the logical structure of an entity being depending on some other entity.
Weak entities can be deleted automatically whilst their strong entity is deleted.
Weak entities may be saved physically with their robust entities.
Computer Science Engineering Interview Questions
Question 12. An E-r Diagram Can Be Viewed As A Graph.What Do The Following Mean In Terms Of The Structure Of An Enterprise Schema?

The Graph Is Disconnected.<
the Graph Is Acyclic.
Answer :

If a couple of entity sets are linked via a direction in an E-R diagram, the entity sets are associated, though possibly indirectly. A disconnected graph means that there are pairs of entity sets that are unrelated to every different. If we break up the graph into linked additives, we've got, in impact, a separate database similar to every linked thing.
As indicated within the answer to the preceding part, a route inside the graph between a pair of entity sets shows a (possibly indirect) courting among the 2 entity sets. If there is a cycle in the graph then every pair of entity sets on the cycle are related to every other in at the least two wonderful ways. If the E-R diagram is acyclic then there is a completely unique course among each pair of entity sets and, thus, a completely unique relationship between each pair of entity units.
Python Interview Questions
Question thirteen. A Weak Entity Set Can Always Be Made Into A Strong Entity Set By Adding To Its Attributes The Primary Key Attributes Of Its Identifying Entity Set. Outline What Sort Of Redundancy Will Result If We Do So?

Answer :

The primary key of a susceptible entity set may be inferred from its courting with the robust entity set. If we upload primary key attributes to the weak entity set, they'll be found in both the entity set and the relationship set and that they should be the identical. Hence there might be redundancy.

SQL Database Tutorial
Question 14. Explain The Distinction Between Condition-defined And User-defined Constraints. Which Of These Constraints Can The System Check Automatically?

Answer :

In a generalization–specialization hierarchy, it need to be feasible to decide which entities are participants of which decrease level entity sets. In a situation defined layout constraint, club in the lower degree entity-sets is evaluated on the premise of whether or not or no longer an entity satisfies an explicit circumstance or predicate.User-described decrease-stage entity sets aren't limited via a club circumstance; instead, entities are assigned to a given entity set by way of the database user.

Condition-described constraints on my own can be automatically dealt with by way of the system. Whenever any tuple is inserted into the database, its club within the various lower level entity-sets can be automatically determined through evaluating the respective membership predicates. Similarly whilst a tuple is up to date, its club in the numerous entity sets may be re-evaluated mechanically.

Question 15. Explain The Distinction Between Total And Partial Constraints?

Answer :

In a total layout constraint, each better-level entity have to belong to a lower-stage entity set. The same want no longer be actual in a partial layout constraint. For instance, a few employees may also belong to no work-group.

SQL Interview Questions
Question 16. Design A Relational Database For A University Registrar's Office. The Office Maintains Data About Each Class, Including The Instructor, The Number Of Students Enrolled, And The Time And Place Of The Class Meetings. For Each Student-elegance Pair, A Grade Is Recorded?

Answer :

Underlined attributes suggest the primary key.

Scholar (pupil-identity, name, program)
course (courseno, identify, syllabus, credits)
direction-providing (courseno, secno, 12 months, semester, time, room)
teacher (teacher-identity, call, dept, identify)
enrols (scholar-identification, courseno, secno, semester, yr, grade)
teaches (courseno, secno, semester, 12 months, teacher-identification)
calls for (maincourse, prerequisite)

JBOSS Tutorial
Question 17. List Two Reasons Why We May Choose To Define A View?

Answer :

Security situations may additionally require that the whole logical database be no longer seen to all users.
We can also desire to create a customized series of relations that is higher matched to a sure user’s instinct than is the actual logical model.
MYSQL DBA Interview Questions
Question 18. List Two Major Problems With Processing Update Operations Expressed In Terms Of Views?

Answer :

Views present considerable problems if updates are expressed with them. The trouble is that a modification to the database expressed in phrases of a view ought to be translated to a amendment to the real relations in the logical model of the database.

Since the view may not have all the attributes of the underlying tables, insertion of a tuple into the view will insert tuples into the underlying tables, with the ones attributes now not participating inside the view getting null values. This won't be appropriate, specifically if the characteristic in query is part of the primary key of the table.
If a view is a be a part of of numerous underlying tables and an insertion consequences in tuples with nulls in the join columns, the preferred effect of the insertion will now not be executed. In different words, an update to a view may not be expressible at all as updates to base members of the family.
Teradata Interview Questions
Question 19. List Two Reasons Why Null Values Might Be Introduced Into The Database?

Answer :

Nulls may be added into the database due to the fact the real price is either unknown or does not exist. For example, an employee whose deal with has modified and whose new cope with isn't but recognised ought to be retained with a null deal with. If employee tuples have a composite attribute dependents, and a selected worker has no dependents, then that tuple’s dependents characteristic must be given a null price.

JSON (JavaScript Object Notation) Tutorial
Question 20. Show That, In Sql, <> All Is Identical To Not In?

Answer :

Let the set S denote the result of an SQL subquery. We compare (x <> all S) with (x now not in S). If a specific fee x1 satisfies (x1 <> all S) then for all elements y of S x1 = y. Thus x1 isn't always a member of S andmust satisfy (x1 now not in S). Similarly, think there's a specific price x2 which satisfies (x2 now not in S). It can't be equal to any element w belonging to S, and as a result (x2 <> all S)
can be glad. Therefore the 2 expressions are equal.

Computer structure Interview Questions
Question 21. Consider The Relational Database Of Figure four.13. Using Sql, Define A View Consisting Of Manager-call And The Average Salary Of All Employees Who Work For That Manager. Explain Why The Database System Should Not Allow Updates To Be Expressed In Terms Of This View?

Answer :

create view salinfo as
choose supervisor-name, avg(profits)
from manages m, works w
where m.Worker-call = w.Employee-name
institution by means of supervisor-call
Updates need to not be allowed on this view due to the fact there is no way to determine how to alternate the underlying facts. For instance, suppose the request is “alternate the average earnings of employees operating for Smith to $2 hundred”. Should anybody who works for Smith have their earnings changed to $200? Or must the first (or extra, if essential) employee discovered who works for Smith have their income adjusted in order that the common is $two hundred?Neither method sincerely makes feel.

Question 22. Suppose There Are Two Relations R And S, Such That The Foreign Key B Of R References The Primary Key A Of S. Describe How The Trigger Mechanism Can Be Used To Implement The On Delete Cascade Option, When A Tuple Is Deleted From S?

Answer :

We outline triggers for each relation whose primary-secret is referred to via the foreign-key of a few other relation. The cause might be activated on every occasion a tuple is deleted from the referred-to relation. The movement done by using the cause would be to visit all the referring members of the family, and delete all of the tuples in them whose overseas-key attribute fee is the same as the primary-key attribute value of the deleted tuple within the referred-to relation. These set of triggers will cope with the on delete cascade operation.

Question 23. Write An Assertion For The Bank Database To Ensure That The Assets Value For The Perry Ridge Branch Is Equal To The Sum Of All The Amounts Lent By The Perry Ridge Branch?

Answer :

The statement-call is bigoted. We have selected the call Perry. Note that because the declaration applies only to the Perry ridge department we have to limit attention to only the Perry ridge tuple of the branch relation rather than writing a constraint on the whole relation.

Create statement perry take a look at
(not exists (pick out *
from department
in which branch-call = ’Perryridge’ and
assets = (choose sum (amount)
from loan
in which department-call = ’Perryridge’)))
JBOSS Interview Questions
Question 24. Write An Sql Trigger To Carry Out The Following Action: On Delete Of An Account, For Each Owner Of The Account, Check If The Owner Has Any Remaining Accounts, And If She Does Not, Delete Her From The Depositor Relation?

Answer :

create trigger check-delete-trigger after delete on account referencing vintage row as row for every row delete from depositor

in which depositor.Customer-name now not in
( pick out customer-call from depositor
in which account-variety <> orow.Account-variety )
quit
Adv Java Interview Questions
Question 25. For Each Of The Views, Explain How Updates Would Be Performed (if They Should Be Allowed At All)?

Answer :

To insert (account-wide variety, call) into the view deer-park we insert the tuple (Deer Park, account-number, null) into the account relation and the tuple (name, account-number) into the depositor relation.

Updates to the views no-debt and avg-bal present critical problems. If we insert into the no-debt view, the device ought to reject the insertion if the client has a loan. The overhead of updating through this view is so high that most systems might disallow update. The avg-bal view cannot be updated since the result of an aggregate operation depends on numerous tuples, now not just one.

Question 26. We Described The Use Of Views To Simplify Access To The Database By Users Who Need To See Only Part Of The Database. The Use Of Views As A Security Mechanism. Do These Two Purposes For Views Ever Conflict?

Answer :

Usually, a well-designed view and security mechanism can keep away from conflicts among ease of access and safety. However, as the following instance shows, the two functions do conflict in case the mechanisms aren't designed carefully.

Suppose we have a database of worker data and a person whose view includes worker facts for employees earning less than $10,000. If this user inserts employee Jones, whose profits is $nine,000, however accidentally enters $90,000, numerous current database structures will accept this update as a legitimate update through a view. However, the person will be denied get right of entry to to delete this erroneous tuple by way of the safety mechanism.

Machine learning Interview Questions
Question 27. What Is The Purpose Of Having Separate Categories For Index Authorization And Resource Authorization?

Answer :

Index and useful resource authorization need to be unique classes to allow sure users to create family members (and the indices to operate on them) even as stopping these time-eating and schema-converting operations from being available to many customers. Separating index and useful resource authorization lets in a consumer to build an index on existing family members, say, for optimization purposes, however permits us to disclaim that person the proper to create new relations.

SQL Database Interview Questions
Question 28. Database Systems That Store Each Relation In A Separate Operating System File May Use The Operating System's Security And Authorization Scheme, Instead Of Defining A Special Scheme Themselves. Discuss An Advantage And A Disadvantage Of Such An Approach?

Answer :

Database systems have special requirements that are normally extra subtle than maximum operating structures. For instance, a single user may additionally have exclusive privileges on different documents at some stage in the machine, consisting of converting indices and attributes which document structures usually don’t screen. The advantage of using the operating machine’s protection mechanism is that it simplifies the database gadget and can be used for simple (examine/write) security features.

Question 29. What Are Two Advantages Of Encrypting Data Stored In The Database?

Answer :

Encrypted statistics permits legal customers to get admission to information with out traumatic about different users or the device administrator gaining any data.
Encryption of information may also simplify or maybe improve other authorization mechanisms. For instance, distribution of the cryptographic key among only trusted customers is both, a simple way to manipulate examine access, and an introduced layer of protection above that presented through perspectives.
JSON (JavaScript Object Notation) Interview Questions
Question 30. Perhaps The Most Important Data Items In Any Database System Are The Passwords That Control Access To The Database. Suggest A Scheme For The Secure Storage Of Passwords.Be Sure That Your Scheme Allows The System To Test Passwords Supplied By Users Who Are Attempting To Log Into The System?

Answer :

A scheme for storing passwords would be to encrypt each password, after which use a hash index on the user-identity. The user-identity may be used to without problems get admission to the encrypted password. The password being utilized in a login try is then encrypted and in comparison with the saved encryption of the perfect password. An gain of this scheme is that passwords aren't saved in clean text and the code for decryption want not even exist!

Question 31. Explain What Is Meant By Repetition Of Information And Inability To Represent Information. Explain Why Each Of These Properties May Indicate A Bad Relational Database Design?

Answer :

Repetition of information is a circumstance in a relational database where the values of 1 attribute are determined via the values of another characteristic in the equal relation, and each values are repeated during the relation. This is a awful relational database design as it will increase the storage required for the relation and it makes updating the relation more tough.
Inability to symbolize facts is a circumstance where a relationship exists amongst simplest a right subset of the attributes in a relation. This is awful relational database design because all the unrelated attributes must be packed with null values otherwise a tuple without the unrelated records can't be inserted into the relation.
Loss of data is a situation of a relational database which ends from the decomposition of one relation into two relations and which cannot be blended to recreate the unique relation. It is a bad relational database layout due to the fact sure queries can't be answered the usage of the reconstructed relation that could have been spoke back the use of the authentic relation.
Question 32. Why Are Certain Functional Dependencies Called Trivial Functional Dependencies?

Answer :

Certain useful dependencies are referred to as trivial useful dependencies because they may be glad by means of all members of the family.

Question 33. In Designing A Relational Database, Why Might We Choose A Non-bcnf Design?

Answer :

BCNF is not always dependency retaining. Therefore, we can also need to select any other regular shape (specially, 3NF) that allows you to make checking dependencies less difficult throughout updates. This might keep away from joins to check dependencies and increase device overall performance.

Computer Science Engineering Interview Questions
Question 34. Explain Why 4nf Is A Normal Form More Desirable Than Bcnf?

Answer :

4NF is more proper than BCNF as it reduces the repetition of statistics. If we remember a BCNF schema no longer in 4NF (see Exercise 7.28), we have a look at that decomposition into 4NF does no longer lose facts provided that a loss much less be part of decomposition is used, but redundancy is reduced.

Question 35. Explain How Dangling Tuples May Arise. Explain Problems That They May Cause?

Answer :

Dangling tuples can get up when one tuple is inserted right into a decomposed relation however no corresponding tuple is inserted into the alternative members of the family within the decomposition. They can cause incorrect values to be lower back by means of queries which shape the be a part of of a decomposed relation since the dangling tuple won't be blanketed. Dangling tuples can be averted by using the specification of referential integrity constraints.

Question 36. For Each Of The Following Application Areas, Explain Why A Relational Database System Would Be Inadequate. List All Specific System Components That Would Need To Be Modified?

Laptop-aided Design
multimedia Databases
Answer :

Each of the packages consists of massive, specialized statistics gadgets (e.G., a program module, a image photograph, digitized voice, a file). These information objects have operations specific to them (e.G., bring together, rotate, play, layout) that cannot be expressed in relational question languages. These facts gadgets are of variable length making it impractical to keep them inside the brief fields which might be allowed in information for such database systems. Thus, the information model, facts manipulation language, and records definition language want to be changed. Also, long-period and nested transactions are usual of those packages. Changes to the concurrency and restoration subsystems are possibly to be wished.

SQL Interview Questions
Question 37. How Does The Concept Of An Object In The Object-orientated Model Differ From The Concept Of An Entity In The Entity-relationship Model?

Answer :

An entity is in reality a collection of variables or data items. An item is an encapsulation of data in addition to the techniques (code) to operate at the records. The statistics participants of an object are directly seen simplest to its techniques. The outdoor international can gain get right of entry to to the item’s records most effective via passing pre-defined messages to it, and those messages are applied via the methods.

Question 38. Explain Why Ambiguity Potentially Exists With Multiple Inheritance?

Answer :

A class inherits the variables and strategies of all its instant tremendous training. Thus it may inherit a variable or approach of the identical call from multiple remarkable-elegance. When that unique variable or technique of an item of the sub-class is referenced, there is an ambiguity regarding which of the extraordinary classes gives the inheritance.

For example, allow there be lessons trainer and student, each having a variable department. If a category teaching Assistant inherits from both of these lessons, any reference to the department variable of a teaching Assistant item is ambiguous.

Question 39. Explain How The Concept Of Object Identity In The Object-orientated Model Differs From The Concept Of Tuple Equality In The Relational Model?

Answer :

Tuple equality is determined by way of information values. Object identity is unbiased of facts values, considering object-oriented systems use built-in identity.

Question 40. Explain The Distinction In Meaning Between Edges In A Dag Representing Inheritance And A Dag Representing Object Containment?

Answer :

An aspect from elegance A to class B inside the DAG representing inheritance means that an object of class B is also an item of class A. It has all the homes that items of class A have, plus additional ones of its very own. In particular, it inherits all the variables and strategies of sophistication A. It can of route provide its very own implementations for the inherited techniques. And side from elegance A to elegance B within the item containment DAG manner that an object of class A includes an item of class B. There want no longer be any similarities in the houses of A and B. Neither B nor A inherit something from the opposite. They characteristic as unbiased types, to the extent that an object of sophistication A can get right of entry to the variables of the B object contained in it best thru the B item’s strategies.

MYSQL DBA Interview Questions
Question forty one. Why Do Persistent Programming Languages Allow Transient Objects? Might It Be Simpler To Use Only Persistent Objects, With Unneeded Objects Deleted At The End Of An Execution?

Answer :

Creation, destruction and access will typically be greater time eating and costly for persistent items saved in the database, than for transient objects inside the transaction’s nearby reminiscence. This is due to the over-heads in keeping transaction semantics, protection and integrity. Since a temporary object is purely neighborhood to the transaction which created it and does now not input the database, these types of over-heads are avoided. Thus, on the way to offer efficient get entry to to only nearby and temporary statistics, transient objects are furnished via chronic programming languages.

Question 42. Explain How A Persistent Pointer Is Implemented. Contrast This Implementation With That Of Pointers As They Exist In General-reason Languages, Such As C Or Pascal?

Answer :

Persistent tips can be applied as Abstract Data Types (ADTs). These ADTs should offer the typical pointer operations like incrementing and dereferencing, so their usage and ordinary pointer usage is uniform. Regular hints then again are commonly integrated sorts, applied as part of the language.

Computer structure Interview Questions
Question forty three. If An Object Is Created Without Any References To It, How Can That Object Be Deleted?

Answer :

If an item is created without any references to it, it is able to neither be accessed nor deleted via a software. The most effective manner is for the database device to locate and delete such objects by using itself. This is called rubbish collection. One way to do rubbish collection is through the technique of mark and sweep. First, the gadgets noted without delay via packages are marked. Then references from these objects to different gadgets are followed, and those referred objects are marked. This system is accompanied again and again until no extra unmarked gadgets can be reached by way of following reference chains from the marked objects. At this point, these kinds of final unmarked gadgets are deleted. This approach is accurate; we are able to show that if no new objects are marked after a round of mark and sweep, the remaining unmarked items are certainly unreferenced.

Question 44. Consider A System That Provides Persistent Objects. Is Such A System Necessarily A Database System?

Answer :

A database machine ought to provide for such features as transactions, queries (associative retrieval of objects), protection, and integrity. A chronic object device might not provide such functions.

Question 45. Explain The Distinction Between A Type X And A Reference Type Ref(x). Under What Circumstances Would You Choose To Use A Reference Type?

Answer :

If the type of an attribute is x, then in each tuple of the table, corresponding to that characteristic, there is an real item of type x . If its type is ref(x), then in each tuple, corresponding to that attribute, there may be a connection with some object of type x. We pick a reference kind for an attribute, if that characteristic’s supposed cause is to refer to an independent item.

Question forty six. Compare The Use Of Embedded Sql With The Use In Sql Of Functions Defined In A General-cause Programming Language. Under What Circumstances Would You Use Each Of These Features?

Answer :

SQL features are commonly a mechanism for extending the strength of SQL to deal with attributes of complicated information sorts (like pictures), or to carry out complicated and non-wellknown operations. Embedded SQL is beneficial when vital actions like showing outcomes and interacting with the user are wanted. These can't be finished with ease in an SQL most effective surroundings. Embedded SQL can be used in place of SQL capabilities by using retrieving facts after which performing the function’s operations at the SQL end result. However a downside is that a whole lot of question-evaluation functionality may emerge as getting repeated inside the host language code.

Question forty seven. List The Physical Storage Media Available On The Computers You Use Routinely. Give The Speed With Which Data Can Be Accessed On Each Medium?

Answer :

Your solution may be based totally on the computer systems and garage media which you use. Typical examples would be tough disk, floppy disks and CD-ROM drives.

Question forty eight. How Does The Remapping Of Bad Sectors By Disk Controllers Affect Data-retrieval Rates?

Answer :

Remapping of horrific sectors by means of disk controllers does lessen information retrieval rates because of the lack of sequentiality amongst the sectors. But that is better than the lack of statistics in case of no remapping!

Question forty nine. Raid Systems Typically Allow You To Replace Failed Disks Without Stopping Access To The System. Thus, The Data In The Failed Disk Must Be Rebuilt And Written To The Replacement Disk While The System Is In Operation. With Which Of The Raid Levels Is The Amount Of Interference Between The Rebuild And Ongoing Disk Accesses Least?

Answer :

RAID degree 1 (mirroring) is the only which allows rebuilding of a failed disk with minimum interference with the on-going disk accesses. This is because rebuilding in this example entails copying facts from simply the failed disk’s replicate. In the other RAID levels, rebuilding includes analyzing the whole contents of all of the different disks.

Question 50. Give An Example Of A Database Application In Which The Reserved-area Method Of Representing Variable-period Records Is Preferable To The Pointer Method.

Answer :

In the reserved space technique, a question comparing the remaining present area in a document to a few value calls for most effective one read from the disk. This unmarried read is most advantageous to the potentially many reads had to chase down the pointers to the remaining discipline if the pointer method is used.

Question fifty one. Give An Example Of A Database Application In Which The Pointer Method Of Representing Variable-length Records Is Preferable To The Reserved-area Method?

Answer :

Using the pointer technique, a be part of operation on attributes which are most effective inside the anchor block can be performed on simplest this smaller quantity of facts, in preference to on the complete relation, as will be the case the usage of the reserved space approach. Therefore, on this be part of instance, the pointer approach is most efficient.

Question fifty two. Explain Why The Allocation Of Records To Blocks Affects Database-device Performance Significantly?

Answer :

If we allocate associated facts to blocks, we are able to frequently retrieve most, or all, of the asked statistics via a query with one disk get admission to. Disk accesses have a tendency to be the bottlenecks in databases; for the reason that this allocation method reduces the variety of disk accesses for a given operation, it drastically improves performance.

Question 53. If Possible, Determine The Buffer-control Strategy Used By The Operating System Running On Your Local Computer System, And What Mechanisms It Provides To Control Replacement Of Pages. Discuss How The Control On Replacement That It Provides Would Be Useful For The Implementation Of Database Systems?

Answer :

The regular OS makes use of LRU for buffer replacement. This is often a bad approach for databases. As explained in Section eleven.5.2 of the textual content, MRU is the pleasant approach for nested loop join. In general no single strategy handles all scenarios properly, and preferably the database system should take delivery of its own buffer cache for which the replacement policy takes into account all the performance associated issues.

Question 54. In The Sequential File Organization, Why Is An Overflow Block Used Even If There Is, At The Moment, Only One Overflow Record?

Answer :

An overflow block is utilized in sequential record organisation due to the fact a block is the smallest space which can be examine from a disk. Therefore, the use of any smaller vicinity would now not be beneficial from a overall performance perspective. The area stored by allocating disk storage in file units would be overshadowed with the aid of the performance fee of permitting blocks to contain facts of multiple files.

Question 55. Explain Why A Physical Oid Must Contain More Information Than A Pointer To A Physical Storage Location?

Answer :

A bodily OID wishes to have a unique identifier in addition to a pointer to a physical garage area. This is needed to prevent dereferences of dangling tips.

Question 56. If Physical Oids Are Used, An Object Can Be Relocated By Keeping A Forwarding Pointer To Its New Location. In Case An Object Gets Forwarded Multiple Times, What Would Be The Effect On Retrieval Speed? Suggest A Technique To Avoid Multiple Accesses In Such A Case?

Answer :

If an item gets forwarded a couple of instances, the retrieval speed will decrease due to the fact gaining access to it'll require accessing the series of places from which the object has been successively forwarded to the contemporary region. Multiple accesses may be averted by using usually keeping within the oldest area the trendy deal with of the item. This can be achieved by means of checking at the same time as forwarding whether this item has already been forwarded and if so updating the forwarding address on the oldest location. Thus, atmost two accesses may be required.

Question fifty seven. Define The Term Dangling Pointer. Describe How The Unique-id Scheme Helps In Detecting Dangling Pointers In An Object-orientated Database?

Answer :

A dangling pointer is a pointer to an area which no longer carries valid records.

In the precise-identity scheme to detect dangling hints, physical OIDsmay contain a completely unique identifier which is an integer that distinguishes the OID from the identifiers of different gadgets that came about to be stored at the equal region earlier, and have been deleted or moved some other place. The precise identifier is likewise stored with the item, and the identifiers in an OID and the corresponding object ought to healthy. If the particular identifier in a physical OID does no longer suit the particular identifier in the object to which that OID factors, the gadget detects that the pointer is a dangling pointer, and alerts an mistakes.

Question 58. When Is It Preferable To Use A Dense Index Rather Than A Sparse Index?

Answer :

It is premier to use a dense index in preference to a sparse index whilst the document isn't always sorted on the listed discipline (such as while the index is a secondary index) or when the index report is small as compared to the dimensions of memory.

Question 59. Since Indices Speed Query Processing, Why Might They Not Be Kept On Several Search Keys?

Answer :

Reasons for now not preserving several seek indices encompass:

Every index requires extra CPU time and disk I/O overhead at some stage in inserts and deletions.
Indices on non-number one keys might should be modified on updates, even though an index on the number one key may not (that is because updates commonly do no longer adjust the primary key attributes).
Each greater index calls for additional garage area.
For queries which involve conditions on several seek keys, efficiency might not be bad even if just a few of the keys have indices on them. Therefore database overall performance is stepped forward much less via adding indices while many indices exist already
Question 60. What Is The Difference Between A Primary Index And A Secondary Index?

Answer :

The number one index is on the sphere which specifies the sequential order of the file. There may be most effective one primary index even as there may be many secondary indices.

Question 61. Is It Possible In General To Have Two Primary Indices On The Same Relation For Different Search Keys?

Answer :

In widespread, it isn't always feasible to have  primary indices at the equal relation for exceptional keys due to the fact the tuples in a relation would need to be saved in one-of-a-kind order to have identical values stored collectively.We could accomplish this by means of storing the relation two times and duplicating all values, however for a centralized device, this isn't always efficient.

Question 62. Explain The Distinction Between Closed And Open Hashing. Discuss The Relative Merits Of Each Technique In Database Applications?

Answer :

Open hashing may location keys with the identical hash function price in unique buckets. Closed hashing constantly locations such keys collectively within the same bucket. Thus in this example, specific buckets may be of different sizes, although the implementation may be by linking together fixed length buckets the usage of overflow chains. Deletion is hard with open hashing as all the buckets may also have to inspected before we will verify that a key value has been deleted, while in closed hashing handiest that bucket whose address is received by means of hashing the important thing price need be inspected. Deletions are greater not unusual in databases and therefore closed hashing is extra appropriate for them. For a small, static set of data lookups may be greater green the use of open hashing. The symbol desk of a compiler could be an excellent instance.

Question sixty three. What Are The Causes Of Bucket Overflow In A Hash File Organization? What Can Be Done To Reduce The Occurrence Of Bucket Overflows?

Answer :

The causes of bucket overflow are :-

Our estimate of the number of information that the relation may have was toolow, and consequently the range of buckets allocated turned into no longer enough.
Skew within the distribution of data to buckets. This may additionally happen both becausethere are many records with the same search key value, or becausethe the hash characteristic chosen did no longer have the acceptable houses of uniformityand randomness.
To reduce the prevalence of overflows, we can :-

Choose the hash characteristic extra cautiously, and make better estimates of therelation length.
If the envisioned length of the relation is nr and quantity of information according to block isfr, allocate (nr/fr) ∗ (1 + d) buckets rather than (nr/fr) buckets. Here d is afudge aspect, usually around zero.2. Some area iswasted: About 20 percentof the distance in the buckets could be empty. But the advantage is that some of theskew is handled and the possibility of overflow is decreased.
Question sixty four. Why Is A Hash Structure Not The Best Choice For A Search Key On Which Range Queries Are Likely?

Answer :

A variety query cannot be responded correctly the usage of a hash index, we can should examine all of the buckets. This is due to the fact key values in the variety do now not occupy consecutive places inside the buckets, they're distributed uniformly and randomly during all the buckets.

Question sixty five. Consider A Grid File In Which We Wish To Avoid Overflow Buckets For Performance Reasons. In Cases Where An Overflow Bucket Would Be Needed, We Instead Reorganize The Grid File. Present An Algorithm For Such A Reorganization?

Answer :

Let us do not forget a two-dimensional grid array. When a bucket overflows, we can break up the tiers corresponding to that row and column into , in each the linear scales. Thus the linear scales will get one extra access each, and the bucket is split into four buckets. The levels must be split in one of these manner as to ensure that the 4 resultant buckets have nearly the identical wide variety of values.

There can be several different heuristics for finding out a way to reorganize the tiers, and hence the linear scales and grid array.

Question sixty six. Show How To Compute Existence Bitmaps From Other Bitmaps. Make Sure That Your Technique Works Even In The Presence Of Null Values, By Using A Bitmap For The Value Null?

Answer :

The lifestyles bitmap for a relation may be calculated with the aid of taking the union (logical-or) of all of the bitmaps on that characteristic, inclusive of the bitmap for fee null.

Question 67. How Does Data Encryption Affect Index Schemes? In Particular, How Might It Affect Schemes That Attempt To Store Data In Sorted Order?

Answer :

Note that indices have to operate on the encrypted statistics or someone could benefit get admission to to the index to interpret the statistics.Otherwise, the index could need to be restrained so that handiest certain customers ought to access it. To keep the statistics in sorted order, the index scheme would must decrypt the information at each stage in a tree. Note that hash structures might not be affected.

Question sixty eight. Why Is It Not Desirable To Force Users To Make An Explicit Choice Of A Query Processing Strategy? Are There Cases In Which It Is Desirable For Users To Be Aware Of The Costs Of Competing Query-processing Strategies?

Answer :

In widespread it is not desirable to pressure users to pick a query processing method due to the fact naive customers might pick an inefficient method. The purpose users might make bad choices approximately processing queries is that they could now not recognize how a relation is stored, nor approximately its indices. It is unreasonable to force customers to be privy to those info on the grounds that ease of use is a prime item of database question languages. If customers are privy to the prices of different strategies they may write queries correctly, for this reason assisting performance. This could take place if professionals have been the use of the device.

Question sixty nine. What Are The Advantages And Disadvantages Of Hash Indices Relative To B+-tree Indices? How Might The Type Of Index Available Influence The Choice Of A Query Processing Strategy?

Answer :

Hash indices allow us to carry out factor look up (eg. σA=r(relation)) operations very rapid, but for variety searches the B+-tree index might be a great deal greater green. If there's a range query to be evaluated, and simplest a hash index is to be had, the higher method might be to carry out a report test as opposed to the use of that index.

Question 70. Design A Variant Of The Hybrid Merge-be part of Algorithm For The Case Where Both Relations Are Not Physically Sorted, But Both Have A Sorted Secondary Index On The Join Attributes?

Answer :

We merge the leaf entries of the first looked after secondary index with the leaf entries of the second looked after secondary index. The result report consists of pairs of addresses, the first deal with in every pair pointing to a tuple within the first relation, and the second address pointing to a tuple in the second relation. This result report is first looked after on the first relation’s addresses. The relation is then scanned in physical storage order, and addresses inside the end result document are changed by means of the real tuple values. Then the end result file is looked after on the second one relation’s addresses, permitting a scan of the second relation in physical storage order to complete the join.

Question seventy one. The Indexed Nested-loop Join Algorithm Can Be Inefficient If The Index Is A Secondary Index, And There Are Multiple Tuples With The Same Value For The Join Attributes. Why Is It Inefficient? Describe A Way, Using Sorting, To Reduce The Cost Of Retrieving Tuples Of The Inner Relation. Under What Conditions Would This Algorithm Be More Efficient Than Hybrid Merge-be part of?

Answer :

If there are a couple of tuples within the internal relation with the same fee for the be a part of attributes, we may have to get right of entry to that many blocks of the internal relation for every tuple of the outer relation. That is why it is inefficient. To reduce this price we are able to perform a be part of of the outer relation tuples with simply the secondary index leaf entries, postponing the inner relation tuple retrieval. The end result report acquired is then sorted on the inner relation addresses, permitting an efficient bodily order test to complete the be a part of.

Hybrid merge–be a part of requires the outer relation to be taken care of. The above algorithm does no longer have this requirement, but for every tuple within the outer relation it wishes to carry out an index research on the internal relation. If the outer relation is plenty larger than the internal relation, this index lookup value may be much less than the sorting price, for this reason this set of rules could be greater green.

Question 72. List The Acid Properties. Explain The Usefulness Of Each?

Answer :

The ACID properties, and the need for every of them are:-

Consistency: Execution of a transaction in isolation (that is, with out a different transaction executing simultaneously) preserves the consistency of the database. This is normally the duty of the utility programmer who codes the transactions.
Atomicity: Either all operations of the transaction are reflected well inside the database, or none are. Clearly loss of atomicity will cause inconsistency within the database.
Isolation: When more than one transactions execute concurrently, it need to be the case that, for every pair of transactions Ti and Tj , it appears to Ti that both Tj finished execution before Ti started, or Tj began execution after Ti completed. Thus, each transaction is blind to other transactions executing simultaneously with it. The consumer view of a transaction system calls for the isolation belongings, and the assets that concurrent schedules take the device from one constant state to every other. These requirements are happy by way of ensuring that handiest serializable schedules of in my opinion consistency maintaining transactions are allowed.
Durability: After a transaction completes efficaciously, the adjustments it has made to the database persist, although there are system disasters.
Question 73. Suppose That There Is A Database System That Never Fails. Is A Recovery Manager Required For This System?

Answer :

Even in this example the healing supervisor is wanted to perform roll-returned of aborted transactions.

Question seventy four. Consider A File System Such As The One On Your Favorite Operating System.

What Are The Steps Involved In Creation And Deletion Of Files, And In Writing Data To A File?
Provide an explanation for How The Issues Of Atomicity And Durability Are Relevant To The Creation And Deletion Of Files, And To Writing Data To Files.
Answer :

There are numerous steps within the introduction of a record. A storage region is assigned to the record within the report machine, a completely unique i-wide variety is given to the document and an i-node access is inserted into the i-list. Deletion of document involves exactly opposite steps.

For the file gadget consumer in UNIX, durability is crucial for apparent motives, but atomicity is not relevant commonly because the report machine doesn’t guide transactions. To the file machine implementor even though, among the internal file device moves need to have transaction semantics. All the steps involved in advent/deletion of the record must be atomic, in any other case there can be unreferenceable files or unusable areas inside the report system.

Question 75. Database-machine Implementers Have Paid Much More Attention To The Acid Properties Than Have File-machine Implementers. Why Might This Be The Case?

Answer :

Database systems usually perform crucial duties whose outcomes want to be atomic and durable, and whose outcome affects the real global in a everlasting way. Examples of such tasks are economic transactions, seat bookings etc. Hence the ACID houses need to be ensured. In comparison, most users of record systems could not be willing to pay the rate (financial, disk space, time) of supporting ACID houses.

Question seventy six. During Its Execution, A Transaction Passes Through Several States, Until It Finally Commits Or Aborts. List All Possible Sequences Of States Through Which A Transaction May Pass. Explain Why Each State Transition May Occur.

Answer :

The viable sequences of states are:-

lively→in part committed→committed. This is the ordinary sequence a successful transaction will follow. After executing all its statements it enters the partly devoted state. After enough healing data has been written to disk, the transaction sooner or later enters the committed kingdom.
Lively → partly committed → aborted. After executing the closing announcement of the transaction, it enters the partially devoted nation. But earlier than sufficient restoration statistics is written to disk, a hardware failure may additionally arise destroying the memory contents. In this example the modifications which it made to the database are undone, and the transaction enters the aborted nation.
Energetic → failed → aborted. After the transaction begins, if it's far observed sooner or later that normal execution cannot continue (either due to inner program mistakes or external mistakes), it enters the failed kingdom. It is then rolled lower back, after which it enters the aborted kingdom.
Question seventy seven. Justify The Following Statement: Concurrent Execution Of Transactions Is More Important When Data Must Be Fetched From (slow) Disk Or When Transactions Are Long, And Is Less Important When Data Is In Memory And Transactions Are Very Short.

Answer :

If a transaction may be very long or when it fetches records from a gradual disk, it takes a long time to finish. In absence of concurrency, different transactions will have to wait for longer time frame. Average reaction time will boom. Also when the transaction is analyzing statistics from disk, CPU is idle. So sources are not nicely utilized. Hence concurrent execution will become important in this case. However, when the transactions are quick or the data is to be had in memory, these problems do no longer occur.

Question seventy eight. Explain The Distinction Between The Terms Serial Schedule And Serializable Schedule.

Answer :

A schedule wherein all the instructions belonging to at least one single transaction appear collectively is called a serial schedule. A serializable schedule has a weaker restrict that it ought to be equal to some serial schedule. There are two definitions of agenda equivalence – conflict equivalence and examine equivalence. Both of those are defined in the bankruptcy.

Question seventy nine. Since Every Conflict-serializable Schedule Is View Serializable, Why Do We Emphasize Conflict Serializability Rather Than View Serializability?

Answer :

Most of the concurrency manipulate protocols (protocols for ensuring that only serializable schedules are generated) used in practice are based on war serializability—they truly permit simplest a subset of warfare serializable schedules. The popular shape of view serializability is very pricey to check, and best a very limited shape of it is used for concurrency manipulate.

Question eighty. What Is A Recoverable Schedule? Why Is Recoverability Of Schedules Desirable? Are There Any Circumstances Under Which It Would Be Desirable To Allow Nonrecoverable Schedules?

Answer :

A recoverable time table is one wherein, for each pair of transactions Ti and Tj such that Tj reads data objects formerly written with the aid of Ti, the commit operation of Ti seems before the dedicate operation of Tj. Recoverable schedules are suited because failure of a transaction may otherwise convey the gadget into an irreversibly inconsistent state. Nonrecoverable schedules might also every now and then be wished whilst updates have to be made seen early because of time constraints, even though




CFG