What do you mean by database?- Database refers to a collection logically coherent data.
- It is designed, built and populated with data for a specific purpose.
It possesses following characteristics:
- Some inherent meaning,
- Some aspect of real world
Define stored procedure.Stored procedure is a set of pre-compiled SQL statements, executed when it is called in the program.
Define Trigger.- Triggers are similar to stored procedure with the difference that they get automatically executed when any operations occur in the table.
Explain - a.) Storage Manager b.) Buffer Manager c.) Transaction Manager d.) File Managera.) Storage Manager - A program module providing the interface between low-level data in database, application programs & queries submitted to the system is referred to as a storage manager.
b.) Buffer Manager - A program module responsible to get data from disk storage into main memory and decide on the data that should exist in cache memory.
c.) Transaction Manager - A program module ensuring that database remains in a consistent state even after the system failures and concurrent transaction execution keeps going on without conflicting.
d.) File Manager - A program module that manages space allocation on disk and data structure used to represent information on a disk.
List some advantages of DBMS. DBMS is useful in:
1. Controlling redundancy
2. Restricting unauthorised access
3. Giving backup & recovery
4. Providing multiple user interfaces
5. Getting integrity constraints enforced
Explain the various types of normalization. When we create a database, we include all the required columns in it but we see that there is a lot of redundant data in it. To get rid of this redundant data, the table is split and this process is called normalization.
1. First Normal Form (1NF) - In this state a relation all underlying domains compulsorily contain atomic values only. After 1NF, it is still possible for the system to possess some redundant data.
2. Second Normal Form (2NF) - A relation in this state is
a.) Compulsorily in 1 NF
b.) Every non key attribute is fully dependent on the primary key.
Even after 2NF, there is a possibility of possessing some redundant data.
3. Third Normal Form (3NF) –
a.) A relation in this state is in 2NF
b.) Additionally every non key attribute is non-transitively dependent on the primary key.
What are the advantages of normalizing a database.Advantages of normalizing database are as follows:
1. Prevents duplicate entries
2. Conserves storage space
3. Improves the performance of queries
What is Denormalization?Adding redundant data to the database to get rid of complex data is called as denormalization.
Differentiate between Delete and Truncate table.- Delete logs the deletion of each row whereas Truncate doesn't log deleted rows in the transaction log.
- This makes truncate command a bit faster than Delete command.
Differentiate between Extension & Intension.
Extension is the number of tuples present in a table at any instance while Intension is a constant value that provides the name, structure of table and the constraints on it.
Extension is time dependent.
Explain Data Independence & its types. Data independence means that the application is independent of
a.) storage structure
b.) access strategy of data
It implies that the modification in schema definition at one level does not affect the schema definition at the next higher level.
There are two types of data independence - Physical & Logical.
a.) Physical Data Independence: Any change in physical level does not affect the logical level.
b.) Logical Data Independence: Changes made at logical level do not affect the view level.
Explain a. Entity b. Entity Type c. Entity Seta. Entity - A thing that has got an independent existence is called entity.
b. Entity type - Collection of entities with same attributes.
c. Entity set - Collection of entities of a particular type in the database.