What is a Database system?
The database and DBMS software together is called as Database system.
Describe the three levels of data abstraction?
Physical level: The lowest level of abstraction describes how data are stored.
Logical level: The next higher level of abstraction, describes what data are stored in database and what relationship among those data.
View level: The highest level of abstraction describes only part of entire database.
Define the "integrity rules"?
Entity Integrity: States that "Primary key cannot have NULL value"
Referential Integrity: States that "Foreign Key can be either a NULL value or should be Primary Key value of other relation.
What is extension and intension?
Extension: It is the number of tuples present in a table at any instance. This is time dependent.
Intension: It is a constant value that gives the name, structure of table and the constraints laid on it.
What is Data Independence?
The ability to modify the schema definition in one level should not affect the schema definition in the next higher level.
Physical Data Independence: Modification in physical level should not affect the logical level.
Logical Data Independence: Modification in logical level should affect the view level.
What is an Entity?
It is a 'thing' in the real world with an independent existence.Entity type is a collection (set) of entities that have same attributes.Entity set is a collection of all entities of particular entity type in the database.An entity set may not have sufficient attributes to form a primary key, and its primary key compromises of its partial key and primary key of its parent entity, then it is said to be Weak Entity set. An attribute is a particular property, which describes the entity.
What is a Relation Schema and a Relation?
A relation Schema denoted by R(A1, A2, ..., An) is made up of the relation name R and the list of attributes Ai that it contains. A relation is defined as a set of tuples. Let r be the relation which contains set tuples (t1, t2, t3, ..., tn). Each tuple is an ordered list of n-values t=(v1,v2, ..., vn).Degree of a Relation is the number of attribute of its relation schema.
Relationship set-The collection (or set) of similar relationships.
Relationship type-Relationship type defines a set of associations or a relationship set among a given set of entity types.
degree of Relationship type-is the number of entity type participating.
Define:
SQL -Structured Query Language
SDL - Storage Definition Language
DML - Data Manipulation Language
What is Record-at-a-time?
The Low level or Procedural DML can specify and retrieve each record from a set of records. This retrieve of a record is said to be Record-at-a-time.
What is Set-at-a-time or Set-oriented?
The High level or Non-procedural DML can specify and retrieve many records in a single DML statement. This retrieve of a record is said to be Set-at-a-time or Set-oriented.
What is normalization?
It is a process of analysing the given relation schemas based on their Functional Dependencies (FDs) and primary key to achieve the properties
(1).Minimizing redundancy, (2). Minimizing insertion, deletion and update anomalies
What is Functional Dependency?
Functional dependency (FD) is a set of constraints between two attributes in a relation. Functional dependency says that if two tuples have same values for attributes A1, A2,..., An, then those two tuples must have to have same values for attributes B1, B2, ..., Bn. Functional dependency is represented by an arrow sign (→) that is, X→Y, where X functionally determines Y.
What is Fully Functional dependency?
A functional dependency X Y is full functional dependency if removal of any attribute A from X means that the dependency does not hold any more.
What is Lossless join Decomposition?
It is the ability to ensure that any instance of the original relation can be identified from corresponding instances in the smaller relations.
Let R = (A, B, C, D) be the relation schema, with A, B, C and D attributes.
Let F = { A -> BC } be the set of functional dependencies.
Decomposition into R1 = (A, B, C) and R2 = (A, D) is lossless under F because R1 intersection R2 = (A), A is a superkey in R1 ( A -> BC ) so R1 intersection R2 -> R1.
Normal Forms:
First Normal Form (1NF):
Every attribute must have value. It should not be empty. In RDBMS a column without
value has NULL value that can be manipulated or accesses like a normal value
Everyattribute must have atomic value that cannot be shorten further. For example:
Name is non-atomic attribute as it can be broken into First Name, Middle Name
and Surname
Second Normal Form (2NF):
An entity must be in First Normal Form
Entity must have a Primary Key or Composite Primary Key
Every attribute must be fully and functionally dependent upon Primary Key
Third Normal Form (3NF):
An entity must be Second Normal Form
There must not be any dependency among non-key attributes (other that Primary Key
Boyce-Codd Normal Form (BCNF):
An entity must be in Third Normal Form
If the entity has a Composite Primary Key & Alternate Primary Key with one or
more attribute common to both, in such case entity must be broken into three
entities
Fourth Normal Form (4NF):
An entity must be in Boyce-Codd Normal Form
If an attribute is based on Value List must be taken out as a separate entity
Fifth Normal Form (5NF):
An entity must be in Fourth Normal Form
If an attribute is multi-valued attribute then it must be taken out as a separate entity
What is Domain-Key Normal Form?
A relation is said to be in DKNF if all constraints and dependencies that should hold on the the constraint can be enforced by simply enforcing the domain constraint and key constraint on the relation.
What are partial, alternate,, artificial, compound and natural key?
Partial Key: It is a set of attributes that can uniquely identify weak entities and that are related to same owner entity. It is sometime called as Discriminator.
Alternate Key: All Candidate Keys excluding the Primary Key are known as Alternate Keys.
Artificial Key: If no obvious key, either stand alone or compound is available, then the last resort is to simply create a key, by assigning a unique number to each record or occurrence. Then this is known as developing an artificial key.
Compound Key: If no single data element uniquely identifies occurrences within a construct, then combining multiple elements to create a unique identifier for the construct is known as creating a compound key.
Natural Key: When one of the data elements stored within a construct is utilized as the primary key, then it is called the natural key.
What is indexing and what are the different kinds of indexing?
Indexing is a technique for determining how quickly specific data can be found.
Types:
Binary search style indexing
B-Tree indexing
Inverted list indexing
Memory resident table
Table indexing
ACID properties −
Atomicity − This property states that a transaction must be treated as an atomic unit, that is, either all of its operations are executed or none. There must be no state in a database where a transaction is left partially completed. States should be defined either before the execution of the transaction or after the execution/abortion/failure of the transaction.
Consistency − The database must remain in a consistent state after any transaction. No transaction should have any adverse effect on the data residing in the database. If the database was in a consistent state before the execution of a transaction, it must remain consistent after the execution of the transaction as well.
Durability − The database should be durable enough to hold all its latest updates even if the system fails or restarts. If a transaction updates a chunk of data in a database and commits, then the database will hold the modified data. If a transaction commits but the system fails before the data could be written on to the disk, then that data will be updated once the system springs back into action.
Isolation − In a database system where more than one transaction are being executed simultaneously and in parallel, the property of isolation states that all the transactions will be carried out and executed as if it is the only transaction in the system. No transaction will affect the existence of any other transaction.
What is a Phantom Deadlock?
In distributed deadlock detection, the delay in propagating local information might cause the deadlock detection algorithms to identify deadlocks that do not really exist. Such situations are called phantom deadlocks and they lead to unnecessary aborts.
What do you mean by Correlated subquery?
Depending on how the subquery is written, it can be executed once for the parent query or it can be executed once for each row returned by the parent query. If the subquery is executed for each row of the parent, this is called a correlated subquery.
What are the unary operations in Relational Algebra?
PROJECTION and SELECTION.
Are the resulting relations of PRODUCT and JOIN operation the same?
PRODUCT: Concatenation of every row in one relation with every row in another.
JOIN: Concatenation of rows from one relation and related rows from another.
Name the sub-systems of a RDBMS.
I/O, Security, Language Processing, Process Control, Storage Management, Logging and Recovery, Distribution Control, Transaction Control, Memory Management, Lock Management.
What is the job of the information stored in data-dictionary?
The information in the data dictionary validates the existence of the objects, provides access to them, and maps the actual physical storage location.
Name the three major set of files on disk that compose a database in Oracle.
There are three major sets of files on disk that compose a database. All the files are binary. These are
1.) Database files
2.) Control files
3.) Redo logs
What is database Trigger?
A database trigger is a PL/SQL block that can defined to automatically execute for insert, update, and delete statements against a table. The trigger can e defined to execute once for the entire statement or once for every row that is inserted, updated, or deleted.
What are stored-procedures?
A stored procedure is a group of sql statements that has been created and stored in the database. Stored procedure will accept input parameters so that a single procedure can be used over the network by several clients using different input data. Stored procedure will reduce network traffic and increase the performance. If we modify stored procedure all the clients will get the updated stored procedure
Managers:
Buffer Manager: It is a program module, which is responsible for fetching data from disk storage into main memory and deciding what data to be cache in memory.
Transaction Manager:It is a program module, which ensures that database, remains in a consistent state despite system failures and concurrent transaction execution proceeds without conflicting.
File Manager: It is a program module, which manages the allocation of space on disk storage and data structure used to represent information stored on a disk.
Authorization and Integrity manager: It is the program module, which tests for the satisfaction of integrity constraint and checks the authority of user to access data.
What are cursors give different types of cursors?
PL/SQL uses cursors for all database information accesses statements. The language supports the use two types of cursors
1.) Implicit Cursors: Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement.
2.) Explicit Cursors: Explicit cursors are programmer defined cursors for gaining more control over the context area.
What is cold backup and hot backup (in case of Oracle)?
Cold Backup: It is copying the three sets of files (database files, redo logs, and control file) when the instance is shut down. This is a straight file copy, usually from the disk directly to tape. You must shut down the instance to guarantee a consistent copy. If a cold backup is performed, the only option available in the event of data file loss is restoring all the files from the latest backup. All work performed on the database since the last backup is lost.
Hot Backup: Some sites (such as worldwide airline reservations systems) cannot shut down the database while making a backup copy of the files. The cold backup is not an available option.
What is meant by Proactive, Retroactive and Simultaneous Update.
Proactive Update: The updates that are applied to database before it becomes effective in real world.
Retroactive Update: The updates that are applied to database after it becomes effective in real world.
Simulatneous Update: The updates that are applied to database at the same time when it becomes effective in real world.
The database and DBMS software together is called as Database system.
Describe the three levels of data abstraction?
Physical level: The lowest level of abstraction describes how data are stored.
Logical level: The next higher level of abstraction, describes what data are stored in database and what relationship among those data.
View level: The highest level of abstraction describes only part of entire database.
Define the "integrity rules"?
Entity Integrity: States that "Primary key cannot have NULL value"
Referential Integrity: States that "Foreign Key can be either a NULL value or should be Primary Key value of other relation.
What is extension and intension?
Extension: It is the number of tuples present in a table at any instance. This is time dependent.
Intension: It is a constant value that gives the name, structure of table and the constraints laid on it.
What is Data Independence?
The ability to modify the schema definition in one level should not affect the schema definition in the next higher level.
Physical Data Independence: Modification in physical level should not affect the logical level.
Logical Data Independence: Modification in logical level should affect the view level.
What is an Entity?
It is a 'thing' in the real world with an independent existence.Entity type is a collection (set) of entities that have same attributes.Entity set is a collection of all entities of particular entity type in the database.An entity set may not have sufficient attributes to form a primary key, and its primary key compromises of its partial key and primary key of its parent entity, then it is said to be Weak Entity set. An attribute is a particular property, which describes the entity.
What is a Relation Schema and a Relation?
A relation Schema denoted by R(A1, A2, ..., An) is made up of the relation name R and the list of attributes Ai that it contains. A relation is defined as a set of tuples. Let r be the relation which contains set tuples (t1, t2, t3, ..., tn). Each tuple is an ordered list of n-values t=(v1,v2, ..., vn).Degree of a Relation is the number of attribute of its relation schema.
Relationship set-The collection (or set) of similar relationships.
Relationship type-Relationship type defines a set of associations or a relationship set among a given set of entity types.
degree of Relationship type-is the number of entity type participating.
Define:
SQL -Structured Query Language
SDL - Storage Definition Language
DML - Data Manipulation Language
What is Record-at-a-time?
The Low level or Procedural DML can specify and retrieve each record from a set of records. This retrieve of a record is said to be Record-at-a-time.
What is Set-at-a-time or Set-oriented?
The High level or Non-procedural DML can specify and retrieve many records in a single DML statement. This retrieve of a record is said to be Set-at-a-time or Set-oriented.
What is normalization?
It is a process of analysing the given relation schemas based on their Functional Dependencies (FDs) and primary key to achieve the properties
(1).Minimizing redundancy, (2). Minimizing insertion, deletion and update anomalies
What is Functional Dependency?
Functional dependency (FD) is a set of constraints between two attributes in a relation. Functional dependency says that if two tuples have same values for attributes A1, A2,..., An, then those two tuples must have to have same values for attributes B1, B2, ..., Bn. Functional dependency is represented by an arrow sign (→) that is, X→Y, where X functionally determines Y.
What is Fully Functional dependency?
A functional dependency X Y is full functional dependency if removal of any attribute A from X means that the dependency does not hold any more.
What is Lossless join Decomposition?
It is the ability to ensure that any instance of the original relation can be identified from corresponding instances in the smaller relations.
Let R = (A, B, C, D) be the relation schema, with A, B, C and D attributes.
Let F = { A -> BC } be the set of functional dependencies.
Decomposition into R1 = (A, B, C) and R2 = (A, D) is lossless under F because R1 intersection R2 = (A), A is a superkey in R1 ( A -> BC ) so R1 intersection R2 -> R1.
Normal Forms:
First Normal Form (1NF):
Every attribute must have value. It should not be empty. In RDBMS a column without
value has NULL value that can be manipulated or accesses like a normal value
Everyattribute must have atomic value that cannot be shorten further. For example:
Name is non-atomic attribute as it can be broken into First Name, Middle Name
and Surname
Second Normal Form (2NF):
An entity must be in First Normal Form
Entity must have a Primary Key or Composite Primary Key
Every attribute must be fully and functionally dependent upon Primary Key
Third Normal Form (3NF):
An entity must be Second Normal Form
There must not be any dependency among non-key attributes (other that Primary Key
Boyce-Codd Normal Form (BCNF):
An entity must be in Third Normal Form
If the entity has a Composite Primary Key & Alternate Primary Key with one or
more attribute common to both, in such case entity must be broken into three
entities
Fourth Normal Form (4NF):
An entity must be in Boyce-Codd Normal Form
If an attribute is based on Value List must be taken out as a separate entity
Fifth Normal Form (5NF):
An entity must be in Fourth Normal Form
If an attribute is multi-valued attribute then it must be taken out as a separate entity
What is Domain-Key Normal Form?
A relation is said to be in DKNF if all constraints and dependencies that should hold on the the constraint can be enforced by simply enforcing the domain constraint and key constraint on the relation.
What are partial, alternate,, artificial, compound and natural key?
Partial Key: It is a set of attributes that can uniquely identify weak entities and that are related to same owner entity. It is sometime called as Discriminator.
Alternate Key: All Candidate Keys excluding the Primary Key are known as Alternate Keys.
Artificial Key: If no obvious key, either stand alone or compound is available, then the last resort is to simply create a key, by assigning a unique number to each record or occurrence. Then this is known as developing an artificial key.
Compound Key: If no single data element uniquely identifies occurrences within a construct, then combining multiple elements to create a unique identifier for the construct is known as creating a compound key.
Natural Key: When one of the data elements stored within a construct is utilized as the primary key, then it is called the natural key.
What is indexing and what are the different kinds of indexing?
Indexing is a technique for determining how quickly specific data can be found.
Types:
Binary search style indexing
B-Tree indexing
Inverted list indexing
Memory resident table
Table indexing
ACID properties −
Atomicity − This property states that a transaction must be treated as an atomic unit, that is, either all of its operations are executed or none. There must be no state in a database where a transaction is left partially completed. States should be defined either before the execution of the transaction or after the execution/abortion/failure of the transaction.
Consistency − The database must remain in a consistent state after any transaction. No transaction should have any adverse effect on the data residing in the database. If the database was in a consistent state before the execution of a transaction, it must remain consistent after the execution of the transaction as well.
Durability − The database should be durable enough to hold all its latest updates even if the system fails or restarts. If a transaction updates a chunk of data in a database and commits, then the database will hold the modified data. If a transaction commits but the system fails before the data could be written on to the disk, then that data will be updated once the system springs back into action.
Isolation − In a database system where more than one transaction are being executed simultaneously and in parallel, the property of isolation states that all the transactions will be carried out and executed as if it is the only transaction in the system. No transaction will affect the existence of any other transaction.
What is a Phantom Deadlock?
In distributed deadlock detection, the delay in propagating local information might cause the deadlock detection algorithms to identify deadlocks that do not really exist. Such situations are called phantom deadlocks and they lead to unnecessary aborts.
What do you mean by Correlated subquery?
Depending on how the subquery is written, it can be executed once for the parent query or it can be executed once for each row returned by the parent query. If the subquery is executed for each row of the parent, this is called a correlated subquery.
What are the unary operations in Relational Algebra?
PROJECTION and SELECTION.
Are the resulting relations of PRODUCT and JOIN operation the same?
PRODUCT: Concatenation of every row in one relation with every row in another.
JOIN: Concatenation of rows from one relation and related rows from another.
Name the sub-systems of a RDBMS.
I/O, Security, Language Processing, Process Control, Storage Management, Logging and Recovery, Distribution Control, Transaction Control, Memory Management, Lock Management.
What is the job of the information stored in data-dictionary?
The information in the data dictionary validates the existence of the objects, provides access to them, and maps the actual physical storage location.
Name the three major set of files on disk that compose a database in Oracle.
There are three major sets of files on disk that compose a database. All the files are binary. These are
1.) Database files
2.) Control files
3.) Redo logs
What is database Trigger?
A database trigger is a PL/SQL block that can defined to automatically execute for insert, update, and delete statements against a table. The trigger can e defined to execute once for the entire statement or once for every row that is inserted, updated, or deleted.
What are stored-procedures?
A stored procedure is a group of sql statements that has been created and stored in the database. Stored procedure will accept input parameters so that a single procedure can be used over the network by several clients using different input data. Stored procedure will reduce network traffic and increase the performance. If we modify stored procedure all the clients will get the updated stored procedure
Managers:
Buffer Manager: It is a program module, which is responsible for fetching data from disk storage into main memory and deciding what data to be cache in memory.
Transaction Manager:It is a program module, which ensures that database, remains in a consistent state despite system failures and concurrent transaction execution proceeds without conflicting.
File Manager: It is a program module, which manages the allocation of space on disk storage and data structure used to represent information stored on a disk.
Authorization and Integrity manager: It is the program module, which tests for the satisfaction of integrity constraint and checks the authority of user to access data.
What are cursors give different types of cursors?
PL/SQL uses cursors for all database information accesses statements. The language supports the use two types of cursors
1.) Implicit Cursors: Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement.
2.) Explicit Cursors: Explicit cursors are programmer defined cursors for gaining more control over the context area.
What is cold backup and hot backup (in case of Oracle)?
Cold Backup: It is copying the three sets of files (database files, redo logs, and control file) when the instance is shut down. This is a straight file copy, usually from the disk directly to tape. You must shut down the instance to guarantee a consistent copy. If a cold backup is performed, the only option available in the event of data file loss is restoring all the files from the latest backup. All work performed on the database since the last backup is lost.
Hot Backup: Some sites (such as worldwide airline reservations systems) cannot shut down the database while making a backup copy of the files. The cold backup is not an available option.
What is meant by Proactive, Retroactive and Simultaneous Update.
Proactive Update: The updates that are applied to database before it becomes effective in real world.
Retroactive Update: The updates that are applied to database after it becomes effective in real world.
Simulatneous Update: The updates that are applied to database at the same time when it becomes effective in real world.
No comments:
Post a Comment