RDBMS question and answers
Database Link
A database link is a named object that describes a "path" from one database to another.
Types of Database Links
Private Database Link, Public Database Link & Network Database Link.
Types of Database Links
Private Database Link, Public Database Link & Network Database Link.
Private Database Link
Private database link is created on behalf of a specific user. A private database link can be used only when the owner of the link specifies a global object name in a SQL statement or in the definition of the owner's views or procedures.
Public Database Link
Public database link is created for the special user group PUBLIC. A public database link can be used when any user in the associated database specifies a global object name in a SQL statement or object definition.
Public database link is created for the special user group PUBLIC. A public database link can be used when any user in the associated database specifies a global object name in a SQL statement or object definition.
Network Database link
Network database link is created and managed by a network domain service. A network database link can be used when any user of any database in the network specifies a global object name in a SQL statement or object definition.
Data Block
ORACLE database's data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk.
Network database link is created and managed by a network domain service. A network database link can be used when any user of any database in the network specifies a global object name in a SQL statement or object definition.
Data Block
ORACLE database's data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk.
Data Block size
A data block size is specified for each ORACLE database when the database is created. A database users and allocated free database space in ORACLE data-blocks. Block size is specified in INIT.ORA file and can't be changed latter.
A data block size is specified for each ORACLE database when the database is created. A database users and allocated free database space in ORACLE data-blocks. Block size is specified in INIT.ORA file and can't be changed latter.
ACID properties
In computer science, ACID (atomicity, consistency, isolation, durability) is a set of properties that guarantee that database transactions are processed reliably. In the context of databases, a single logical operation on the data is called a transaction.
In computer science, ACID (atomicity, consistency, isolation, durability) is a set of properties that guarantee that database transactions are processed reliably. In the context of databases, a single logical operation on the data is called a transaction.
Internal/external sorting/searching techniques
An internal sort is any data sorting process that takes place entirely within the main memory of a computer. This is possible whenever the data to be sorted is small enough to all be held in the main memory. For sorting larger datasets, it may be necessary to hold only a chunk of data in memory at a time, since it won’t all fit. The rest of the data is normally held on some larger, but slower medium, like a hard-disk. Any reading or writing of data to and from this slower media can slow the sortation process considerably. This issue has implications for different
Consider a Bubble sort where adjacent records are swapped in order to get them into the right order, so that records appear to “bubble” up and down through the dataspace. If this has to be done in chunks, then when we have sorted all the records in chunk 1, we move on to chunk 2, but we find that some of the records in chunk 1 need to “bubble through” chunk 2, and vice versa (i.e., there are records in chunk 2 that belong in chunk 1, and records in chunk 1 that belong in chunk 2 or later chunks). This will cause the chunks to be read and written back to disk many times as records cross over the boundaries between them, resulting in a considerable degradation of performance. If the data can all be held in memory as one large chunk, then this performance hit is avoided.
On the other hand, some algorithms handle external sorting rather better. A merge sort breaks the data up into chunks, sorts the chunks by some other algorithm (maybe Bubble sort or Quick sort) and then recombines the chunks two by two so that each recombined chunk is in order. This approach minimises the number or reads and writes of data-chunks from disk, and is a popular external sort method.
Dense/sparse indexes
A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records. The disk space required to store the index is typically less than that required by the table (since indices usually contain only the key-fields according to which the table is to be arranged, and exclude all the other details in the table), yielding the possibility to store indices in memory for a table whose data is too large to store in memory.
An internal sort is any data sorting process that takes place entirely within the main memory of a computer. This is possible whenever the data to be sorted is small enough to all be held in the main memory. For sorting larger datasets, it may be necessary to hold only a chunk of data in memory at a time, since it won’t all fit. The rest of the data is normally held on some larger, but slower medium, like a hard-disk. Any reading or writing of data to and from this slower media can slow the sortation process considerably. This issue has implications for different
Consider a Bubble sort where adjacent records are swapped in order to get them into the right order, so that records appear to “bubble” up and down through the dataspace. If this has to be done in chunks, then when we have sorted all the records in chunk 1, we move on to chunk 2, but we find that some of the records in chunk 1 need to “bubble through” chunk 2, and vice versa (i.e., there are records in chunk 2 that belong in chunk 1, and records in chunk 1 that belong in chunk 2 or later chunks). This will cause the chunks to be read and written back to disk many times as records cross over the boundaries between them, resulting in a considerable degradation of performance. If the data can all be held in memory as one large chunk, then this performance hit is avoided.
On the other hand, some algorithms handle external sorting rather better. A merge sort breaks the data up into chunks, sorts the chunks by some other algorithm (maybe Bubble sort or Quick sort) and then recombines the chunks two by two so that each recombined chunk is in order. This approach minimises the number or reads and writes of data-chunks from disk, and is a popular external sort method.
Dense/sparse indexes
A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records. The disk space required to store the index is typically less than that required by the table (since indices usually contain only the key-fields according to which the table is to be arranged, and exclude all the other details in the table), yielding the possibility to store indices in memory for a table whose data is too large to store in memory.
In a relational database, an index is a copy of one part of a table. Some databases extend the power of indexing by allowing indices to be created on functions or expressions. For example, an index could be created on upper(last_name), which would only store the upper case versions of the last_name field in the index. Another option sometimes supported is the use of "filtered" indices , where index entries are created only for those records that satisfy some conditional expression. A further aspect of flexibility is to permit indexing on user-defined functions, as well as expressions formed from an assortment of built-in functions.
Dense index
Dense index
A dense index in databases is a file with pairs of keys and pointers for every record in the data file. Every key in this file is associated with a particular pointer to a record in the sorted data file. In clustered indices with duplicate keys, the dense index points to the first record with that key
Sparse index
Sparse index
A sparse index in databases is a file with pairs of keys and pointers for every block in the data file. Every key in this file is associated with a particular pointer to the block in the sorted data file. In clustered indices with duplicate keys, the sparse index points to the lowest search key in each block. primary key is a sparse index.
Aggregation
Aggregation is a special kind of association that specifies a whole/part relationship between the aggregate (whole) and a component part. When compared to an ordinary association, some distinguishing characteristics of aggregation include:
Aggregation
Aggregation is a special kind of association that specifies a whole/part relationship between the aggregate (whole) and a component part. When compared to an ordinary association, some distinguishing characteristics of aggregation include:
Since aggregation is not symmetric, it is important to visually distinguish which class is the aggregate and which is a component part. Therefore UML adorns the association line with a small open diamond at the aggregate end.
DDL
A Data Definition Language or Data Description Language (DDL) is a computer language for defining data structures. SQL uses a collection of imperative verbs whose effect is to modify the schema of the database by adding, changing, or deleting definitions of tables or other objects. These statements can be freely mixed with other SQL statements, so the DDL is not truly a separate language. The most commonly encountered statement is CREATE TABLE.
Create - To make a new database, table, index, or stored query.
Drop - To destroy an existing database, table, index, or view.
Alter - To modify an existing database object.
A Data Definition Language or Data Description Language (DDL) is a computer language for defining data structures. SQL uses a collection of imperative verbs whose effect is to modify the schema of the database by adding, changing, or deleting definitions of tables or other objects. These statements can be freely mixed with other SQL statements, so the DDL is not truly a separate language. The most commonly encountered statement is CREATE TABLE.
Create - To make a new database, table, index, or stored query.
Drop - To destroy an existing database, table, index, or view.
Alter - To modify an existing database object.
Referential integrity statements: implemented as primary key and foreign key tags in some columns of the tables.
VDL
It specifies user views and their mappings to the conceptual schema.
It specifies user views and their mappings to the conceptual schema.
SDL
The Simple Declarative Language (SDL) is a cross-platform declarative programming language used for defining basic data structures such as lists, maps, and trees of typed data in a compact, easy to read representation.
Data Storage - Definition Language
The storage structures and access methods used by database system are specified by a set of definition in a special type of DDL called data storage-definition language.
DML
Data Manipulation Language (DML) is a family of computer languages used by computer programs and/or database users to insert, delete and update data in a database. Read-only querying, i.e. SELECT, of this data may be considered to be either part of DML or outside it, depending on the context.
Some DML commands
SELECT ... FROM ... WHERE ...
INSERT INTO ... VALUES ...
UPDATE ... SET ... WHERE ...
DELETE FROM ... WHERE ...
There are two types of data manipulation languages:
* Procedural
* Declarative
Three levels of data abstraction
Physical level :
How the data is stored physically and where it is stored in database.
- How the data are stored.
- E.g. index, B-tree, hashing.
- Lowest level of abstraction.
- Complex low-level structures described in detail.
Logical level :
What information or data is stored in the database (like what is the datatype or what is format of data)
- Next highest level of abstraction.
- Describes what data are stored.
- Describes the relationships among data.
- Database administrator level.
View level :
End users work on view level. if any amendment is made it can be.
End users work on view level. if any amendment is made it can be.
- Highest level.
- Describes part of the database for a particular group of users.
- Can be many different views of a database.
- E.g. tellers in a bank get a view of customer accounts, but not of payroll data.
Integrity rules
Null Rule : A null rule is a rule defined on a single column that allows or disallows inserts or updates of rows containing a null (the absence of a value) in that column.
Unique Column Values: A unique value rule defined on a column (or set of columns) allows the insert or update of a row only if it contains a unique value in that column (or set of columns).
Primary Key Values: A primary key value rule defined on a key (a column or set of columns) specifies that each row in the table can be uniquely identified by the values in the key.
Referential Integrity Rules: A referential integrity rule is a rule defined on a key (a column or set of columns) in one table that guarantees that the values in that key match the values in a key in a related table (the referenced value).
Referential integrity also includes the rules that dictate what types of data manipulation are allowed on referenced values and how these actions affect dependent values. The rules associated with referential integrity are:
Restrict: Disallows the update or deletion of referenced data.
Set to Null: When referenced data is updated or deleted, all associated dependent data is set to NULL.
Set to Default: When referenced data is updated or deleted, all associated dependent data is set to a default value.
Cascade: When referenced data is updated, all associated dependent data is correspondingly updated. When a referenced row is deleted, all associated dependent rows are deleted.
No Action: Disallows the update or deletion of referenced data. This differs from RESTRICT in that it is checked at the end of the statement, or at the end of the transaction if the constraint is deferred. (Oracle uses No Action as its default action.)
Complex Integrity Checking
Complex integrity checking is a user-definIn linguistics, logic, philosophy, and other fields, an intension is any property or quality or state of affairs connoted by a word, phrase or other symbol. In case of a word, it is often implied by its definition. The term may also refer to the complete set of meanings or properties that are implied by a concept, although the term comprehension is technically more correct for this.ed rule for a column (or set of columns) that allows or disallows inserts, updates, or deletes of a row based on the value it contains for the column (or set of columns). Oracle enables you to define and enforce each type of data integrity rule defined in the previous section. Most of these rules are easily defined using integrity constraints or database triggers.
Integrity Constraints Description
An integrity constraint is a declarative method of defining a rule for a column of a table. Oracle supports the following integrity constraints:
NOT NULL constraints for the rules associated with nulls in a column
UNIQUE key constraints for the rule associated with unique column values
PRIMARY KEY constraints for the rule associated with primary identification values
FOREIGN KEY constraints for the rules associated with referential integrity. Oracle supports the use of FOREIGN KEY integrity constraints to define the referential integrity actions, including:
Update and delete No Action
Delete CASCADE
Delete SET NULL
CHECK constraints for complex integrity rules
It is important that data adhere to a predefined set of rules, as determined by the database administrator or application developer.
Extension and intension
In linguistics, logic, philosophy, and other fields, an intension is any property or quality or state of affairs connoted by a word, phrase or other symbol. In case of a word, it is often implied by its definition. The term may also refer to the complete set of meanings or properties that are implied by a concept, although the term comprehension is technically more correct for this.
In any of several studies that treat the use of signs, for example, linguistics, logic, mathematics, semantics, and semiotics, the extension of a concept, idea, or sign consists of the things to which it applies, in contrast with its comprehension or intension, which consists very roughly of the ideas, properties, or corresponding signs that are implied or suggested by the concept in question.
Unique Column Values: A unique value rule defined on a column (or set of columns) allows the insert or update of a row only if it contains a unique value in that column (or set of columns).
Primary Key Values: A primary key value rule defined on a key (a column or set of columns) specifies that each row in the table can be uniquely identified by the values in the key.
Referential Integrity Rules: A referential integrity rule is a rule defined on a key (a column or set of columns) in one table that guarantees that the values in that key match the values in a key in a related table (the referenced value).
Referential integrity also includes the rules that dictate what types of data manipulation are allowed on referenced values and how these actions affect dependent values. The rules associated with referential integrity are:
Restrict: Disallows the update or deletion of referenced data.
Set to Null: When referenced data is updated or deleted, all associated dependent data is set to NULL.
Set to Default: When referenced data is updated or deleted, all associated dependent data is set to a default value.
Cascade: When referenced data is updated, all associated dependent data is correspondingly updated. When a referenced row is deleted, all associated dependent rows are deleted.
No Action: Disallows the update or deletion of referenced data. This differs from RESTRICT in that it is checked at the end of the statement, or at the end of the transaction if the constraint is deferred. (Oracle uses No Action as its default action.)
Complex Integrity Checking
Complex integrity checking is a user-definIn linguistics, logic, philosophy, and other fields, an intension is any property or quality or state of affairs connoted by a word, phrase or other symbol. In case of a word, it is often implied by its definition. The term may also refer to the complete set of meanings or properties that are implied by a concept, although the term comprehension is technically more correct for this.ed rule for a column (or set of columns) that allows or disallows inserts, updates, or deletes of a row based on the value it contains for the column (or set of columns). Oracle enables you to define and enforce each type of data integrity rule defined in the previous section. Most of these rules are easily defined using integrity constraints or database triggers.
Integrity Constraints Description
An integrity constraint is a declarative method of defining a rule for a column of a table. Oracle supports the following integrity constraints:
NOT NULL constraints for the rules associated with nulls in a column
UNIQUE key constraints for the rule associated with unique column values
PRIMARY KEY constraints for the rule associated with primary identification values
FOREIGN KEY constraints for the rules associated with referential integrity. Oracle supports the use of FOREIGN KEY integrity constraints to define the referential integrity actions, including:
Update and delete No Action
Delete CASCADE
Delete SET NULL
CHECK constraints for complex integrity rules
It is important that data adhere to a predefined set of rules, as determined by the database administrator or application developer.
Extension and intension
In linguistics, logic, philosophy, and other fields, an intension is any property or quality or state of affairs connoted by a word, phrase or other symbol. In case of a word, it is often implied by its definition. The term may also refer to the complete set of meanings or properties that are implied by a concept, although the term comprehension is technically more correct for this.
In any of several studies that treat the use of signs, for example, linguistics, logic, mathematics, semantics, and semiotics, the extension of a concept, idea, or sign consists of the things to which it applies, in contrast with its comprehension or intension, which consists very roughly of the ideas, properties, or corresponding signs that are implied or suggested by the concept in question.
Data Independence
Data independence is the type of data transparency that matters for a centralized DBMS. It refers to the immunity of user applications to make changes in the definition and organization of data.
Physical data independence deals with hiding the details of the storage structure from user applications. The application should not be involved with these issues, since there is no difference in the operation carried out against the data.
Data Model
Data modeling is the formalization and documentation of existing processes and events that occur during application software design and development. Data modeling techniques and tools capture and translate complex system designs into easily understood representations of the data flows and processes, creating a blueprint for construction and/or re-engineering.
Data independence is the type of data transparency that matters for a centralized DBMS. It refers to the immunity of user applications to make changes in the definition and organization of data.
Physical data independence deals with hiding the details of the storage structure from user applications. The application should not be involved with these issues, since there is no difference in the operation carried out against the data.
Data Model
Data modeling is the formalization and documentation of existing processes and events that occur during application software design and development. Data modeling techniques and tools capture and translate complex system designs into easily understood representations of the data flows and processes, creating a blueprint for construction and/or re-engineering.
Relationship set
The collection (or set) of similar relationships. A relationship is an association between several entities. A relationship set is a set of relationships of the same type. Formally it is a mathematical relation on n 2 (possibly non-distinct) sets. If E1,E2....En are entity sets, then a relationship set R is a subset of if{(e1,e2. . . .. en) |e1∈ E1; e2 ∈ E2; . . . ∈ En} where (e1; e2; . . .; en) is a relationship.
Relationship type
One-to-one: Both tables can have only one record on either side of the relationship. Each primary key value relates to only one (or no) record in the related table. They're like spouses—you may or may not be married, but if you are, both you and your spouse have only one spouse. Most one-to-one relationships are forced by business rules and don't flow naturally from the data. In the absence of such a rule, you can usually combine both tables into one table without breaking any normalization rules.
One-to-many: The primary key table contains only one record that relates to none, one, or many records in the related table. This relationship is similar to the one between you and a parent. You have only one mother, but your mother may have several children.
Many-to-many: Each record in both tables can relate to any number of records (or no records) in the other table. For instance, if you have several siblings, so do your siblings (have many siblings). Many-to-many relationships require a third table, known as an associate or linking table, because relational systems can't directly accommodate the relationship.
Degree of Relationship type
Relationships exhibit certain characteristics like degree, connectivity, and cardinality. Once the relationships are identified their degree and cardinality are also specified.
Degree: The degree of a relationship is the number of entities associated with the relationship. The n-ary relationship is the general form for degree n. Special cases are the binary, and ternary, where the degree is 2, and 3, respectively.
Binary relationships, the association between two entities are the most common type in the real world.
Connectivity and Cardinality:
By connectivity we mean how many instances of one entity are associated with how many instances of other entity in a relationship. Cardinality is used to specify such connectivity. The connectivity of a relationship describes the mapping of associated entity instances in the relationship. The values of connectivity are "one" or "many". The cardinality of a relationship is the actual number of related occurrences for each of the two entities. The basic types of connectivity for relations are: one-to-one, one-to-many, and many-tomany.
A one-to-one (1:1) relationship is when at most one instance of an entity A is associated with one instance of entity B. For example, take the relationship between board members and offices, where each office is held by one member and no member may hold more than one office.
A one-to-many (1:N) relationship is when for one instance of entity A, there are zero, one, or many instances of entity B but for one instance of entity B, there is only one instance of entity A. An example of a 1:N relationships is a department has many employees; each employee is assigned to one department.
A many-to-many (M:N) relationship, sometimes called non-specific, is when for one instance of entity A, there are zero, one, or many instances of entity B and for one instance of entity B there are zero, one, or many instances of entity A. An example is employees may be assigned to no more than three projects at a time; every project has at least two employees assigned to it.
Here the cardinality of the relationship from employees to projects is three; from projects to employees, the cardinality is two. Therefore, this relationship can be classified as a many-to-many relationship.
If a relationship can have a cardinality of zero, it is an optional relationship. If it must have a cardinality of at least one, the relationship is mandatory. Optional relationships are typically indicated by the conditional tense. For example,
An employee may be assigned to a project. Mandatory relationships, on the other hand, are indicated by words such as must have. For example, a student must register for at least three courses in each semester.
The collection (or set) of similar relationships. A relationship is an association between several entities. A relationship set is a set of relationships of the same type. Formally it is a mathematical relation on n 2 (possibly non-distinct) sets. If E1,E2....En are entity sets, then a relationship set R is a subset of if{(e1,e2. . . .. en) |e1∈ E1; e2 ∈ E2; . . . ∈ En} where (e1; e2; . . .; en) is a relationship.
Relationship type
One-to-one: Both tables can have only one record on either side of the relationship. Each primary key value relates to only one (or no) record in the related table. They're like spouses—you may or may not be married, but if you are, both you and your spouse have only one spouse. Most one-to-one relationships are forced by business rules and don't flow naturally from the data. In the absence of such a rule, you can usually combine both tables into one table without breaking any normalization rules.
One-to-many: The primary key table contains only one record that relates to none, one, or many records in the related table. This relationship is similar to the one between you and a parent. You have only one mother, but your mother may have several children.
Many-to-many: Each record in both tables can relate to any number of records (or no records) in the other table. For instance, if you have several siblings, so do your siblings (have many siblings). Many-to-many relationships require a third table, known as an associate or linking table, because relational systems can't directly accommodate the relationship.
Degree of Relationship type
Relationships exhibit certain characteristics like degree, connectivity, and cardinality. Once the relationships are identified their degree and cardinality are also specified.
Degree: The degree of a relationship is the number of entities associated with the relationship. The n-ary relationship is the general form for degree n. Special cases are the binary, and ternary, where the degree is 2, and 3, respectively.
Binary relationships, the association between two entities are the most common type in the real world.
Connectivity and Cardinality:
By connectivity we mean how many instances of one entity are associated with how many instances of other entity in a relationship. Cardinality is used to specify such connectivity. The connectivity of a relationship describes the mapping of associated entity instances in the relationship. The values of connectivity are "one" or "many". The cardinality of a relationship is the actual number of related occurrences for each of the two entities. The basic types of connectivity for relations are: one-to-one, one-to-many, and many-tomany.
A one-to-one (1:1) relationship is when at most one instance of an entity A is associated with one instance of entity B. For example, take the relationship between board members and offices, where each office is held by one member and no member may hold more than one office.
A one-to-many (1:N) relationship is when for one instance of entity A, there are zero, one, or many instances of entity B but for one instance of entity B, there is only one instance of entity A. An example of a 1:N relationships is a department has many employees; each employee is assigned to one department.
A many-to-many (M:N) relationship, sometimes called non-specific, is when for one instance of entity A, there are zero, one, or many instances of entity B and for one instance of entity B there are zero, one, or many instances of entity A. An example is employees may be assigned to no more than three projects at a time; every project has at least two employees assigned to it.
Here the cardinality of the relationship from employees to projects is three; from projects to employees, the cardinality is two. Therefore, this relationship can be classified as a many-to-many relationship.
If a relationship can have a cardinality of zero, it is an optional relationship. If it must have a cardinality of at least one, the relationship is mandatory. Optional relationships are typically indicated by the conditional tense. For example,
An employee may be assigned to a project. Mandatory relationships, on the other hand, are indicated by words such as must have. For example, a student must register for at least three courses in each semester.
No comments:
Post a Comment