Explore topic-wise InterviewSolutions in Current Affairs.

Let US consider a two-dimensional grid array. When a bucket overflows, we can split the RANGES corresponding to that row and COLUMN into two, in both the linear scales. Thus the linear scales will get one additional entry each, and the bucket is split into four buckets. The ranges should be split in such a way as to ensure that the four resultant buckets have nearly the same NUMBER of VALUES.

There can be several other heuristics for deciding how to reorganize the ranges, and hence the linear scales and grid array.

Let us consider a two-dimensional grid array. When a bucket overflows, we can split the ranges corresponding to that row and column into two, in both the linear scales. Thus the linear scales will get one additional entry each, and the bucket is split into four buckets. The ranges should be split in such a way as to ensure that the four resultant buckets have nearly the same number of values.

There can be several other heuristics for deciding how to reorganize the ranges, and hence the linear scales and grid array.

A range query cannot be ANSWERED efficiently using a hash index, we will have to read all the buckets. This is because key VALUES in the range do not OCCUPY consecutive locations in the buckets, they are distributed UNIFORMLY and randomly throughout all the buckets.

A range query cannot be answered efficiently using a hash index, we will have to read all the buckets. This is because key values in the range do not occupy consecutive locations in the buckets, they are distributed uniformly and randomly throughout all the buckets.

The causes of bucket overflow are :-

1. Our estimate of the number of records that the relation will have was toolow, and hence the number of buckets allotted was not sufficient.
2. Skew in the distribution of records to buckets. This may happen either becausethere are many records with the same search key value, or becausethe the hash function CHOSEN did not have the desirable properties of uniformityand randomness.

To reduce the occurrence of OVERFLOWS, we can :-

1. Choose the hash function more carefully, and make better ESTIMATES of therelation size.
2. If the ESTIMATED size of the relation is nr and number of records per block isfr, allocate (nr/fr) ∗ (1 + d) buckets instead of (nr/fr) buckets. Here d is afudge factor, typically around 0.2. Some space iswasted: About 20 percentof the space in the buckets will be empty. But the benefit is that some of theskew is handled and the probability of overflow is reduced.

The causes of bucket overflow are :-

To reduce the occurrence of overflows, we can :-

Open hashing may place keys with the same HASH function VALUE in different buckets. Closed hashing always places such keys together in the same bucket. Thus in this case, different buckets can be of different sizes, though the implementation may be by linking together fixed size buckets using overflow chains. Deletion is difficult with open hashing as all the buckets may have to inspected before we can ascertain that a key value has been deleted, whereas in closed hashing only that bucket whose address is obtained by hashing the key value need be inspected. DELETIONS are more common in databases and hence closed hashing is more appropriate for them. For a small, static set of DATA lookups may be more efficient using open hashing. The symbol table of a COMPILER would be a good example.

Open hashing may place keys with the same hash function value in different buckets. Closed hashing always places such keys together in the same bucket. Thus in this case, different buckets can be of different sizes, though the implementation may be by linking together fixed size buckets using overflow chains. Deletion is difficult with open hashing as all the buckets may have to inspected before we can ascertain that a key value has been deleted, whereas in closed hashing only that bucket whose address is obtained by hashing the key value need be inspected. Deletions are more common in databases and hence closed hashing is more appropriate for them. For a small, static set of data lookups may be more efficient using open hashing. The symbol table of a compiler would be a good example.

In general, it is not possible to have two primary indices on the same relation for different keys because the tuples in a relation would have to be STORED in different ORDER to have same values stored together.We COULD ACCOMPLISH this by storing the relation twice and duplicating all values, but for a centralized system, this is not efficient.

In general, it is not possible to have two primary indices on the same relation for different keys because the tuples in a relation would have to be stored in different order to have same values stored together.We could accomplish this by storing the relation twice and duplicating all values, but for a centralized system, this is not efficient.

The primary INDEX is on the field which specifies the sequential ORDER of the FILE. There can be only one primary index while there can be MANY secondary indices.

The primary index is on the field which specifies the sequential order of the file. There can be only one primary index while there can be many secondary indices.

REASONS for not keeping several search indices include:

1. Every index requires additional CPU time and disk I/O overhead during inserts and deletions.
2. Indices on non-primary KEYS might have to be changed on updates, although an index on the primary key might not (this is because updates typically do not modify the primary key attributes).
3. Each extra index requires additional storage space.
4. For queries which involve CONDITIONS on several search keys, efficiency might not be bad even if only some of the keys have indices on them. Therefore database performance is improved less by adding indices when many indices already EXIST

Reasons for not keeping several search indices include:

It is PREFERABLE to use a dense index instead of a sparse index when the file is not sorted on the indexed field (such as when the index is a SECONDARY index) or when the index file is SMALL compared to the SIZE of memory.

It is preferable to use a dense index instead of a sparse index when the file is not sorted on the indexed field (such as when the index is a secondary index) or when the index file is small compared to the size of memory.

A dangling pointer is a pointer to an AREA which no longer contains valid data.

In the unique-id scheme to detect dangling pointers, physical OIDsmay contain a unique identifier which is an integer that distinguishes the OID from the IDENTIFIERS of other objects that happened to be STORED at the same location EARLIER, and were DELETED or moved elsewhere. The unique identifier is also stored with the object, and the identifiers in an OID and the corresponding object should match. If the unique identifier in a physical OID does not match the unique identifier in the object to which that OID points, the system detects that the pointer is a dangling pointer, and signals an error.

A dangling pointer is a pointer to an area which no longer contains valid data.

In the unique-id scheme to detect dangling pointers, physical OIDsmay contain a unique identifier which is an integer that distinguishes the OID from the identifiers of other objects that happened to be stored at the same location earlier, and were deleted or moved elsewhere. The unique identifier is also stored with the object, and the identifiers in an OID and the corresponding object should match. If the unique identifier in a physical OID does not match the unique identifier in the object to which that OID points, the system detects that the pointer is a dangling pointer, and signals an error.

If an object gets forwarded multiple times, the retrieval speed will decrease because accessing it will require accessing the SERIES of LOCATIONS from which the object has been successively forwarded to the current location. Multiple ACCESSES can be avoided by always keeping in the OLDEST location the latest address of the object. This can be done by checking while forwarding whether this object has already been forwarded and in that case updating the forwarding address at the oldest location. Thus, atmost two accesses will be required.

If an object gets forwarded multiple times, the retrieval speed will decrease because accessing it will require accessing the series of locations from which the object has been successively forwarded to the current location. Multiple accesses can be avoided by always keeping in the oldest location the latest address of the object. This can be done by checking while forwarding whether this object has already been forwarded and in that case updating the forwarding address at the oldest location. Thus, atmost two accesses will be required.

A physical OID NEEDS to have a unique identifier in addition to a pointer to a physical storage location. This is REQUIRED to PREVENT dereferences of DANGLING pointers.

A physical OID needs to have a unique identifier in addition to a pointer to a physical storage location. This is required to prevent dereferences of dangling pointers.

An overflow BLOCK is used in sequential file organization because a block is the smallest SPACE which can be READ from a disk. Therefore, using any smaller region would not be useful from a performance STANDPOINT. The space SAVED by allocating disk storage in record units would be overshadowed by the performance cost of allowing blocks to contain records of multiple files.

An overflow block is used in sequential file organization because a block is the smallest space which can be read from a disk. Therefore, using any smaller region would not be useful from a performance standpoint. The space saved by allocating disk storage in record units would be overshadowed by the performance cost of allowing blocks to contain records of multiple files.

The typical OS uses LRU for buffer replacement. This is OFTEN a bad STRATEGY for databases. As explained in Section 11.5.2 of the text, MRU is the BEST strategy for nested loop join. In general no SINGLE strategy handles all scenarios well, and ideally the database system should be given its own buffer cache for which the replacement policy takes into account all the performance related issues.

The typical OS uses LRU for buffer replacement. This is often a bad strategy for databases. As explained in Section 11.5.2 of the text, MRU is the best strategy for nested loop join. In general no single strategy handles all scenarios well, and ideally the database system should be given its own buffer cache for which the replacement policy takes into account all the performance related issues.

If we allocate related records to blocks, we can often retrieve most, or all, of the requested records by a query with one disk access. Disk ACCESSES TEND to be the bottlenecks in databases; since this allocation strategy reduces the number of disk accesses for a GIVEN OPERATION, it significantly IMPROVES performance.

If we allocate related records to blocks, we can often retrieve most, or all, of the requested records by a query with one disk access. Disk accesses tend to be the bottlenecks in databases; since this allocation strategy reduces the number of disk accesses for a given operation, it significantly improves performance.

USING the POINTER method, a join operation on attributes which are only in the anchor block can be performed on only this smaller amount of data, RATHER than on the entire relation, as would be the case using the RESERVED space method. Therefore, in this join EXAMPLE, the pointer method is preferable.

Using the pointer method, a join operation on attributes which are only in the anchor block can be performed on only this smaller amount of data, rather than on the entire relation, as would be the case using the reserved space method. Therefore, in this join example, the pointer method is preferable.

In the reserved space method, a query comparing the last existing field in a RECORD to some VALUE REQUIRES only one read from the disk. This single read is preferable to the potentially MANY reads needed to chase down the pointers to the last field if the POINTER method is used.

In the reserved space method, a query comparing the last existing field in a record to some value requires only one read from the disk. This single read is preferable to the potentially many reads needed to chase down the pointers to the last field if the pointer method is used.

RAID level 1 (mirroring) is the ONE which facilitates rebuilding of a failed disk with MINIMUM INTERFERENCE with the on-going disk accesses. This is because rebuilding in this case INVOLVES copying data from just the failed disk’s mirror. In the other RAID levels, rebuilding involves READING the entire contents of all the other disks.

RAID level 1 (mirroring) is the one which facilitates rebuilding of a failed disk with minimum interference with the on-going disk accesses. This is because rebuilding in this case involves copying data from just the failed disk’s mirror. In the other RAID levels, rebuilding involves reading the entire contents of all the other disks.

REMAPPING of BAD sectors by disk controllers does REDUCE data retrieval rates because of the loss of sequentiality amongst the sectors. But that is BETTER than the loss of data in CASE of no remapping!

Remapping of bad sectors by disk controllers does reduce data retrieval rates because of the loss of sequentiality amongst the sectors. But that is better than the loss of data in case of no remapping!

Your ANSWER will be based on the computers and storage media that you use. Typical EXAMPLES would be HARD disk, floppy disks and CD-ROM DRIVES.

Your answer will be based on the computers and storage media that you use. Typical examples would be hard disk, floppy disks and CD-ROM drives.

SQL functions are primarily a MECHANISM for extending the power of SQL to handle attributes of complex data types (like IMAGES), or to perform complex and non-standard operations. Embedded SQL is USEFUL when imperative actions like displaying results and interacting with the user are needed. These cannot be done conveniently in an SQL only environment. Embedded SQL can be used instead of SQL functions by retrieving data and then PERFORMING the function’s operations on the SQL result. However a drawback is that a lot of query-evaluation functionality may end up getting repeated in the HOST language code.

SQL functions are primarily a mechanism for extending the power of SQL to handle attributes of complex data types (like images), or to perform complex and non-standard operations. Embedded SQL is useful when imperative actions like displaying results and interacting with the user are needed. These cannot be done conveniently in an SQL only environment. Embedded SQL can be used instead of SQL functions by retrieving data and then performing the function’s operations on the SQL result. However a drawback is that a lot of query-evaluation functionality may end up getting repeated in the host language code.

If the type of an ATTRIBUTE is X, then in each tuple of the table, corresponding to that attribute, there is an ACTUAL object of type x . If its type is ref(x), then in each tuple, corresponding to that attribute, there is a reference to some object of type x. We choose a reference type for an attribute, if that attribute’s intended PURPOSE is to refer to an independent object.

If the type of an attribute is x, then in each tuple of the table, corresponding to that attribute, there is an actual object of type x . If its type is ref(x), then in each tuple, corresponding to that attribute, there is a reference to some object of type x. We choose a reference type for an attribute, if that attribute’s intended purpose is to refer to an independent object.

A database system MUST provide for such FEATURES as transactions, queries (associative RETRIEVAL of objects), security, and integrity. A persistent object system MAY not offer such features.

A database system must provide for such features as transactions, queries (associative retrieval of objects), security, and integrity. A persistent object system may not offer such features.

If an object is created without any references to it, it can neither be accessed nor deleted via a program. The only way is for the DATABASE system to locate and delete such objects by itself. This is called garbage collection. One way to do garbage collection is by the method of MARK and sweep. First, the objects referred to directly by programs are MARKED. Then references from these objects to other objects are followed, and those referred objects are marked. This procedure is followed repeatedly until no more unmarked objects can be reached by following reference chains from the marked objects. At this point, all these remaining unmarked objects are deleted. This method is correct; we can prove that if no new objects are marked after a round of mark and sweep, the remaining unmarked objects are INDEED unreferenced.

If an object is created without any references to it, it can neither be accessed nor deleted via a program. The only way is for the database system to locate and delete such objects by itself. This is called garbage collection. One way to do garbage collection is by the method of mark and sweep. First, the objects referred to directly by programs are marked. Then references from these objects to other objects are followed, and those referred objects are marked. This procedure is followed repeatedly until no more unmarked objects can be reached by following reference chains from the marked objects. At this point, all these remaining unmarked objects are deleted. This method is correct; we can prove that if no new objects are marked after a round of mark and sweep, the remaining unmarked objects are indeed unreferenced.

Persistent pointers can be implemented as Abstract Data Types (ADTs). These ADTs should provide the TYPICAL pointer operations like incrementing and dereferencing, so their USAGE and regular pointer usage is uniform. Regular pointers on the other hand are usually built-in types, implemented as PART of the language.

Persistent pointers can be implemented as Abstract Data Types (ADTs). These ADTs should provide the typical pointer operations like incrementing and dereferencing, so their usage and regular pointer usage is uniform. Regular pointers on the other hand are usually built-in types, implemented as part of the language.

Creation, DESTRUCTION and access will typically be more time consuming and expensive for persistent objects STORED in the database, than for transient objects in the TRANSACTION’s local memory. This is because of the over-heads in preserving transaction semantics, security and integrity. Since a transient object is purely local to the transaction which created it and does not enter the database, all these over-heads are avoided. Thus, in order to provide EFFICIENT access to purely local and temporary data, transient objects are provided by persistent programming languages.

Creation, destruction and access will typically be more time consuming and expensive for persistent objects stored in the database, than for transient objects in the transaction’s local memory. This is because of the over-heads in preserving transaction semantics, security and integrity. Since a transient object is purely local to the transaction which created it and does not enter the database, all these over-heads are avoided. Thus, in order to provide efficient access to purely local and temporary data, transient objects are provided by persistent programming languages.

An edge from class A to class B in the DAG representing inheritance means that an object of class B is ALSO an object of class A. It has all the properties that objects of class A have, plus additional ones of its own. In particular, it INHERITS all the variables and methods of class A. It can of COURSE provide its own implementations for the inherited methods. And edge from class A to class B in the object containment DAG means that an object of class A contains an object of class B. There need not be any similarities in the properties of A and B. Neither B nor A inherit ANYTHING from the other. They function as independent types, to the extent that an object of class A can access the variables of the B object contained in it only via the B object’s methods.

An edge from class A to class B in the DAG representing inheritance means that an object of class B is also an object of class A. It has all the properties that objects of class A have, plus additional ones of its own. In particular, it inherits all the variables and methods of class A. It can of course provide its own implementations for the inherited methods. And edge from class A to class B in the object containment DAG means that an object of class A contains an object of class B. There need not be any similarities in the properties of A and B. Neither B nor A inherit anything from the other. They function as independent types, to the extent that an object of class A can access the variables of the B object contained in it only via the B object’s methods.

Tuple equality is DETERMINED by data values. OBJECT IDENTITY is independent of data values, since object-oriented SYSTEMS use built-in identity.

Tuple equality is determined by data values. Object identity is independent of data values, since object-oriented systems use built-in identity.

A class inherits the variables and methods of all its immediate super classes. Thus it COULD inherit a variable or method of the same name from more than ONE super-class. When that particular variable or method of an object of the sub-class is referenced, there is an ambiguity regarding which of the super classes provides the INHERITANCE.

For INSTANCE, let there be classes teacher and student, both having a variable department. If a class teaching Assistant inherits from both of these classes, any REFERENCE to the department variable of a teaching Assistant object is ambiguous.

A class inherits the variables and methods of all its immediate super classes. Thus it could inherit a variable or method of the same name from more than one super-class. When that particular variable or method of an object of the sub-class is referenced, there is an ambiguity regarding which of the super classes provides the inheritance.

For instance, let there be classes teacher and student, both having a variable department. If a class teaching Assistant inherits from both of these classes, any reference to the department variable of a teaching Assistant object is ambiguous.

An ENTITY is simply a collection of variables or data items. An object is an encapsulation of data as WELL as the methods (CODE) to OPERATE on the data. The data MEMBERS of an object are directly visible only to its methods. The outside world can gain access to the object’s data only by passing pre-defined messages to it, and these messages are implemented by the methods.

An entity is simply a collection of variables or data items. An object is an encapsulation of data as well as the methods (code) to operate on the data. The data members of an object are directly visible only to its methods. The outside world can gain access to the object’s data only by passing pre-defined messages to it, and these messages are implemented by the methods.

Each of the applications includes large, specialized data items (e.g., a PROGRAM module, a GRAPHIC image, DIGITIZED voice, a document). These data items have operations specific to them (e.g., compile, rotate, play, format) that cannot be expressed in relational query languages. These data items are of variable length making it impractical to store them in the short fields that are allowed in records for such database systems. Thus, the data model, data manipulation language, and data definition language need to be changed. Also, long-duration and NESTED transactions are typical of these applications. Changes to the concurrency and RECOVERY subsystems are likely to be needed.

Each of the applications includes large, specialized data items (e.g., a program module, a graphic image, digitized voice, a document). These data items have operations specific to them (e.g., compile, rotate, play, format) that cannot be expressed in relational query languages. These data items are of variable length making it impractical to store them in the short fields that are allowed in records for such database systems. Thus, the data model, data manipulation language, and data definition language need to be changed. Also, long-duration and nested transactions are typical of these applications. Changes to the concurrency and recovery subsystems are likely to be needed.

Dangling tuples can ARISE when one tuple is inserted into a decomposed relation but no corresponding tuple is inserted into the other relations in the decomposition. They can CAUSE incorrect values to be returned by QUERIES which form the join of a decomposed relation since the dangling tuple might not be included. dangling tuples can be AVOIDED by the specification of referential integrity constraints.

Dangling tuples can arise when one tuple is inserted into a decomposed relation but no corresponding tuple is inserted into the other relations in the decomposition. They can cause incorrect values to be returned by queries which form the join of a decomposed relation since the dangling tuple might not be included. dangling tuples can be avoided by the specification of referential integrity constraints.

4NF is more desirable than BCNF because it reduces the repetition of information. If we consider a BCNF schema not in 4NF (see Exercise 7.28), we OBSERVE that decomposition into 4NF does not lose information provided that a loss less join decomposition is used, yet redundancy is REDUCED.

4NF is more desirable than BCNF because it reduces the repetition of information. If we consider a BCNF schema not in 4NF (see Exercise 7.28), we observe that decomposition into 4NF does not lose information provided that a loss less join decomposition is used, yet redundancy is reduced.

BCNF is not always dependency preserving. Therefore, we MAY want to CHOOSE another normal FORM (specifically, 3NF) in ORDER to make checking DEPENDENCIES easier during updates. This would avoid joins to check dependencies and increase system performance.

BCNF is not always dependency preserving. Therefore, we may want to choose another normal form (specifically, 3NF) in order to make checking dependencies easier during updates. This would avoid joins to check dependencies and increase system performance.

Certain FUNCTIONAL DEPENDENCIES are called TRIVIAL functional dependencies because they are SATISFIED by all relations.

Certain functional dependencies are called trivial functional dependencies because they are satisfied by all relations.

• Repetition of information is a condition in a relational database where the values of one attribute are DETERMINED by the values of another attribute in the same relation, and both values are repeated throughout the relation. This is a bad relational database design because it increases the storage required for the relation and it MAKES updating the relation more difficult.
• Inability to REPRESENT information is a condition where a relationship exists among only a proper subset of the attributes in a relation. This is bad relational database design because all the unrelated attributes must be filled with null values otherwise a TUPLE without the unrelated information cannot be inserted into the relation.
• Loss of information is a condition of a relational database which results from the decomposition of one relation into two relations and which cannot be combined to recreate the original relation. It is a bad relational database design because certain queries cannot be ANSWERED using the reconstructed relation that could have been answered using the original relation.

A scheme for storing passwords would be to encrypt each password, and then use a hash index on the user-id. The user-id can be USED to easily access the ENCRYPTED password. The password being used in a login attempt is then encrypted and compared with the STORED encryption of the correct password. An advantage of this scheme is that passwords are not stored in CLEAR text and the code for decryption NEED not even exist!

A scheme for storing passwords would be to encrypt each password, and then use a hash index on the user-id. The user-id can be used to easily access the encrypted password. The password being used in a login attempt is then encrypted and compared with the stored encryption of the correct password. An advantage of this scheme is that passwords are not stored in clear text and the code for decryption need not even exist!

1. Encrypted data allows authorized users to access data without worrying about other users or the system administrator gaining any information.
2. Encryption of data may simplify or even STRENGTHEN other authorization mechanisms. For example, distribution of the cryptographic KEY amongst only trusted users is both, a simple WAY to control READ access, and an added LAYER of security above that offered by views.

Database SYSTEMS have special REQUIREMENTS which are typically more refined than most operating systems. For example, a single user MAY have different privileges on different files throughout the system, including changing indices and attributes which file systems typically don’t monitor. The advantage of USING the operating system’s security mechanism is that it SIMPLIFIES the database system and can be used for simple (read/write) security measures.

Database systems have special requirements which are typically more refined than most operating systems. For example, a single user may have different privileges on different files throughout the system, including changing indices and attributes which file systems typically don’t monitor. The advantage of using the operating system’s security mechanism is that it simplifies the database system and can be used for simple (read/write) security measures.

Index and resource AUTHORIZATION should be special categories to allow certain users to create relations (and the indices to OPERATE on them) while preventing these time-consuming and schema-changing operations from being available to many users. Separating index and resource authorization allows a user to BUILD an index on existing relations, say, for optimization purposes, but allows us to DENY that user the right to create new relations.

Index and resource authorization should be special categories to allow certain users to create relations (and the indices to operate on them) while preventing these time-consuming and schema-changing operations from being available to many users. Separating index and resource authorization allows a user to build an index on existing relations, say, for optimization purposes, but allows us to deny that user the right to create new relations.

Usually, a well-designed view and security mechanism can avoid conflicts between ease of ACCESS and security. However, as the following example shows, the two purposes do conflict in case the MECHANISMS are not designed carefully.

SUPPOSE we have a database of employee data and a user WHOSE view involves employee data for employees earning less than $10,000. If this user inserts employee Jones, whose salary is $9,000, but accidentally enters $90,000, several existing database systems will accept this update as a valid update through a view. However, the user will be denied access to delete this erroneous tuple by the security mechanism.

Usually, a well-designed view and security mechanism can avoid conflicts between ease of access and security. However, as the following example shows, the two purposes do conflict in case the mechanisms are not designed carefully.

Suppose we have a database of employee data and a user whose view involves employee data for employees earning less than $10,000. If this user inserts employee Jones, whose salary is $9,000, but accidentally enters $90,000, several existing database systems will accept this update as a valid update through a view. However, the user will be denied access to delete this erroneous tuple by the security mechanism.

To insert (account-number, name) 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 SERIOUS problems. If we insert into the no-debt view, the system must reject the insertion if the customer has a LOAN. The OVERHEAD of updating through this view is so high that most systems would disallow UPDATE. The avg-bal view cannot be updated since the result of an aggregate operation depends on several tuples, not just one.

To insert (account-number, name) 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 serious problems. If we insert into the no-debt view, the system must reject the insertion if the customer has a loan. The overhead of updating through this view is so high that most systems would disallow update. The avg-bal view cannot be updated since the result of an aggregate operation depends on several tuples, not just one.

CREATE TRIGGER check-delete-trigger after delete on account referencing old ROW as row for each row delete from depositor

create trigger check-delete-trigger after delete on account referencing old row as row for each row delete from depositor

The ASSERTION-name is ARBITRARY. We have CHOSEN the name Perry. Note that since the assertion applies only to the Perry ridge branch we must RESTRICT attention to only the Perry ridge tuple of the branch relation rather than writing a constraint on the entire relation.

The assertion-name is arbitrary. We have chosen the name Perry. Note that since the assertion applies only to the Perry ridge branch we must restrict attention to only the Perry ridge tuple of the branch relation rather than writing a constraint on the entire relation.

We define TRIGGERS for each relation whose primary-key is REFERRED to by the foreign-key of some other relation. The trigger would be activated WHENEVER a tuple is deleted from the referred-to relation. The action PERFORMED by the trigger would be to visit all the referring relations, and delete all the tuples in them whose foreign-key attribute value is the same as the primary-key attribute value of the deleted tuple in the referred-to relation. These SET of triggers will take care of the on delete cascade operation.

We define triggers for each relation whose primary-key is referred to by the foreign-key of some other relation. The trigger would be activated whenever a tuple is deleted from the referred-to relation. The action performed by the trigger would be to visit all the referring relations, and delete all the tuples in them whose foreign-key attribute value is the same as the primary-key attribute value of the deleted tuple in the referred-to relation. These set of triggers will take care of the on delete cascade operation.

Answer : create view salinfo as SELECT manager-name, avg(salary) from manages m, works w where m.EMPLOYEE-name = w.employee-name group by manager-name

UPDATES should not be allowed in this view because there is no way to determine how to change the underlying data. For example, suppose the request is “change the average salary of employees working for Smith to $200”. Should everybody who works for Smith have their salary CHANGED to $200? Or should the first (or more, if necessary) employee found who works for Smith have their salary adjusted so that the average is $200?NEITHER approach really makes sense.

Updates should not be allowed in this view because there is no way to determine how to change the underlying data. For example, suppose the request is “change the average salary of employees working for Smith to $200”. Should everybody who works for Smith have their salary changed to $200? Or should the first (or more, if necessary) employee found who works for Smith have their salary adjusted so that the average is $200?Neither approach really makes sense.

LET the SET S denote the result of an SQL subquery. We compare (x <> all S) with (x not in S). If a particular VALUE x1 satisfies (x1 <> all S) then for all elements y of S x1 ?= y. Thus x1 is not a member of S andmust SATISFY (x1 not in S). Similarly, suppose there is a particular value x2 which satisfies (x2 not in S). It cannot be EQUAL to any element w belonging to S, and hence (x2 <> all S)
will be satisfied. Therefore the two expressions are equivalent.

Let the set S denote the result of an SQL subquery. We compare (x <> all S) with (x not in S). If a particular value x1 satisfies (x1 <> all S) then for all elements y of S x1 ?= y. Thus x1 is not a member of S andmust satisfy (x1 not in S). Similarly, suppose there is a particular value x2 which satisfies (x2 not in S). It cannot be equal to any element w belonging to S, and hence (x2 <> all S)
will be satisfied. Therefore the two expressions are equivalent.

Nulls may be introduced into the database because the actual value is either unknown or does not exist. For example, an EMPLOYEE whose address has CHANGED and whose NEW address is not yet known should be retained with a null address. If employee tuples have a composite attribute dependents, and a particular employee has no dependents, then that TUPLE’s dependents attribute should be given a null value.

Nulls may be introduced into the database because the actual value is either unknown or does not exist. For example, an employee whose address has changed and whose new address is not yet known should be retained with a null address. If employee tuples have a composite attribute dependents, and a particular employee has no dependents, then that tuple’s dependents attribute should be given a null value.

Views present significant problems if updates are expressed with them. The difficulty is that a modification to the database expressed in terms of a view MUST be translated to a modification to the actual relations in the logical model of the database.

1. 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 those attributes not participating in the view getting null values. This may not be desirable, especially if the attribute in question is part of the primary key of the table.
2. If a view is a join of several underlying tables and an insertion results in tuples with nulls in the join columns, the desired effect of the insertion will not be ACHIEVED. In other words, an update to a view may not be expressible at all as updates to base relations.

Views present significant problems if updates are expressed with them. The difficulty is that a modification to the database expressed in terms of a view must be translated to a modification to the actual relations in the logical model of the database.

1. Security conditions may require that the entire logical database be not visible to all users.
2. We may WISH to CREATE a personalized COLLECTION of RELATIONS that is better matched to a certain user’s intuition than is the actual logical model.

UNDERLINED attributes indicate the primary key.

student (student-id, NAME, PROGRAM)
course (courseno, title, syllabus, credits)
course-offering (courseno, secno, year, semester, time, room)
instructor (instructor-id, name, dept, title)
enrols (student-id, courseno, secno, semester, year, GRADE)
TEACHES (courseno, secno, semester, year, instructor-id)
requires (maincourse, prerequisite)

Underlined attributes indicate the primary key.

student (student-id, name, program)
course (courseno, title, syllabus, credits)
course-offering (courseno, secno, year, semester, time, room)
instructor (instructor-id, name, dept, title)
enrols (student-id, courseno, secno, semester, year, grade)
teaches (courseno, secno, semester, year, instructor-id)
requires (maincourse, prerequisite)