Q)What are Exclusive Arcs ?
Exclusive arcs are used to identify relationships where an entity is related to two
or more other entities, but only one relationship can exist for a specific entity
occurrence. The exclusive arc is represented by a curved line going through two
or more relationship lines. Figure 1.8 shows an example of an exclusive arc. The
relationships are read as "Each INVENTORY ITEM must be either located at one
and only FACILITY or must be located within one and only one CONTAINER,
but not both." This communicates that inventory items are stored at one of two
types of levels: They are either located at facilities such as a warehouse or stored
within containers such as a bin that is located within a facility.
Q) Different sort of Cardinality?
Cardinality defines the numeric relationships between occurrences of the entities on either end of the relationship line. Here are some examples:
LINK This is a 0:0 optional relationship basically stating that a person can occupy one parking space, that I don't need a person to have a space and I don't need a space to have a person. Although the concept is fairly simple, a database can't express it directly. You would need to nominate one entity to become the dominant table and use triggers or programs to limit the number of related records in the other table.
SubType This is a 1:0 relationship; optional only on one side. This would indicate that a person might be a programmer, but a programmer must be a person. It is assumed that the mandatory side of the relationship is the dominant. Again, triggers or programs must be used to control the database.
Physical Segment This is a 1:1 mandatory relationship and demonstrates a segmentation denormalization. A person must have one and only one DNA pattern and that pattern must apply to one and only one person. This is difficult to implement in a database, since declarative referential integrity will get caught in a "Chicken and the Egg" situation. Basically, this is a single entity.
Possession This is a 0:M (zero to many) optional relationship indicating that a person might have no phone, one phone or lots of phones, and that a phone might be un-owned, but can only be owned by a maximum of one person. This is implemented in a database as a nullable foreign key column in the phone table that references the person table.
Child This is a 1:M mandatory relationship, the most common one seen in databases. A person might be a member or might not, but could be found multiple times (if the member entity represents membership in multiple clubs, for instance). A member must be a person, no questions asked. The foreign key in the member table would be mandatory, or not-null.
Characteristic This is a 0:M relationship that is mandatory on the many side. It indicates that a person must have at least one name, but possibly many names, and that a name might be assigned to a person (might not) but at most to one person. In a database you would have the the name table with a nullable foreign key to the person table and triggers or programs to force a person to have at least one name.
Paradox This is a 1:M relationship mandatory on both sides. As with the physical segment situation, the "Chicken and the Egg" is involved since you have to have a person to have citizenship, but citizenship to have a person.
Association This is a M:M (many to many) optional relationship. Conceptually, it means that a person might or might not work for an employer, but could certainly moonlight for multiple companies. An employer might have no employees, but could have any number of them. Again, not hard to visualize, but hard to implement. Most solutions of this situation involve creating a third "Associative Entity" to resolve the M:M into two 0:M relationships. This might be an entity called employee because it does link the person to the employer the person works for.
Q)Unique Versus Non Unique index in Oracle ?
When a row is inserted into a table or when a PK or UK is modified, Oracle of course needs to ensure that either the PK or UK constraint is not violated. If the constraint is policed via a Unique index, as previously discussed, Oracle knows the value must and can only ever be unique and so performs the constraint validation before the Unique index is actually modified. If the PK or UK is violated, the Unique index can not possibly have been changed as all the associated index entries must always be unique and so only the undo (and redo) of the changes associated with the table data blocks are actually generated and need to be subsequently rolled back.
However, if the PK or UK constraint is policed via a Non-Unique index, the mechanism for applying the changes differs somewhat. As the index is Non-Unique, as previously discussed, Oracle is not quite so certain as to the state of play and performs the constraint validation after the associated changes are made to the Non Unique index. If the PK or UK constraint is violated, both undo and redo of the Non-Unique index has been generated and both changes to the table data blocks and the index blocks need to be rolled back.
This means there’s an extra cost associated with violating a constraint if the constraint is policed via a Non-Unique Index vs. a Unique index
Q)Difference between Delete and Truncate?
TRUNCATE is a DDL command and cannot be rolled back. All of the memory space is released back to the server.
DELETE is a DML command and can be rolled back.
Both commands accomplish identical tasks (removing all data from a table), but TRUNCATE is much faster
TRUNCATE commits after deleting entire table i.e., can not be rolled back. Database triggers do not fire on TRUNCATEDELETE allows the filtered deletion. Deleted records can be rolled back or committed.Database triggers fire on DELETE.
TRUNCATE deletes much faster than DELETE
TRUNCATE
It is a DDL statement
It is a one way trip,cannot ROLLBACK
Doesn't have selective features (where clause)
Doesn't fire database triggers
DELETE
It is a DML statement
One can Rollback
Has conditions
Does fire database triggers
Q)Select sysdate from dual where 5=5 ?
Answer : Return Sysdate only
Q)Select sysdate from dual where 5>NULL ?
Answer : NULL
Q)Difference between Union and Union All ?
The difference between UNION ALL and UNION is that, while UNION only selects distinct values, UNION ALL selects all values.
Q)Select 1 from dual union select a from dual ?
Answer : ORA-01790: expression must have same datatype as corresponding expression. So 1 should be captured as Char in query
Q)Select 1 from dual union all select a from union ?
Same result as above.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment