What is a Database? A database is a structured collection of information that is organized so that it can easily be accessed, managed, and updated. The information stored in a database has to continue to exist even after the application that saved and manipulated the data have ceased to run.
What is a Relational Database? A Relational Database is a collection of data items organized as a set of formally-described tables from which data can be accessed or reassembled in many different ways without having to reorganize the database tables. Each table contains one or more data categories in columns. Each row contains a unique instance of data for the categories defined by the columns.
What is DBMS ? DBMS is a set of Prewritten programs that controls the organization, storage, management, and retrieval of data in a database. DBMS are categorized according to their data structures or types. The DBMS accepts requests for data from the application program and instructs the operating system to transfer the appropriate data.
What is RDBMS ? An RDBMS is a Relational Data Base Management System. which adds the additional condition that the system supports a tabular structure for the data with enforced relationships between the tables. Database in which all data is stored in Relations which are tables, with rows and columns. Each table is composed of records (called Tuples) and each record is identified by a field (called attribute) containing a unique value. Every table shares at least one field with another table in 'one to one,' 'one to many,' or 'many to many' relationships. These relationships allow the database users to access the data in almost an unlimited number of ways, and to combine the tables as building blocks to create complex and very large databases.
What is the Difference between DBMS and RDBMS?
| DBMS | RDBMS |
| A Set of Prewritten Programs dedicated to control the data storage | A DBMS which Supports Tabular structure of Database. Only way to view the data is as a set of tables |
| Entity Based where all the tables are treated as different entities | Relation Based where tables are dependent and the user can establish various integrity constraints on these tables, so that the ultimate data used by the user remains correct |
| Single User system, Only one user can access one database at a time. | Multi-User system, Multiple users simultaneously access the same database |
| DBMS does not impose any constraints or security with regard to data manipulation | RDBMS is more Secure than DBMS by defining the integrity constraints for the purpose of holding ACID Properties |
| Does not necessarily Support Client/Server Architecture & Distributed Databases | Supports Client/Server Architecture & Distributed Databases |
| Examples: FoxPro data files and earlier Ms Access | Examples: ORACLE, SQL Server and DB2 |
What is Normalization? A Relational database is basically composed of tables that contains Related data. So the Process of organizing this Related data into tables is referred to as Normalization. Alternatively, The process of refining tables, keys, columns, and relationships to create an efficient database is called Normalization.
What are the advantages of Normalization ? Normalization Used to reduce Data Redundancy and permits Simple and rapid retrieval of all necessary information. Some of the benefits of Normalization are:
- Data Integrity (As there is No Redundant Data)
- Optimized Queries (As Normalized tables produce rapid and efficient Joins)
- Faster Index creation and sorting (As the tables have fewer Columns)
- Faster UPDATE Performance (As there are fewer indexes per table)
- Improved Concurrency(As table locks will affect less Data)
- Eliminates Redundancy
What are the Disadvantages of Normalization ? As a result of Normalization, Data is stored in multiple tables.To retrieve or modify the information, we have to establish joins across multiple tables.Joins are expensive from I/O standpoint. Also, Multi table Joins can have adverse impact on the performance of the system.
What are Different levels of Normalization? There are a few rules for database normalization. Each rule is called a "Normal form." If the first rule is observed, the database is said to be in First Normal Form. Similarly, If the first three rules are observed, then the database is considered to be in Third Normal Form. Although other levels of normalization are possible, Third normal form is considered to be the highest level necessary for most applications.
First Normal Form(1NF): Remove Repetitive groups from Record Design.For this,Create a separate Table for each set of Related data and Identify each set of Related data with a primary key.
Second Normal Form(2NF): Each and every field is Functionally Dependant on Primary Key. Here, Create separate tables for sets of values that apply to multiple records and Relate these tables with a foreign key. Records should not depend on anything other than a table's primary key.
Third Normal Form(3NF): Transitive dependencies are removed from Record Design. For this,Eliminate fields that do not depend on the key.
Boyce-Codd Normal Form(BCNF): A relation is in Boyce/Codd Normal Form if and only if the only determinants are candidate key. (A determinant is any attribute on which some other attribute is (fully) functionally dependent.)
4th Normal Form(4NF): A table is in 4NF if it is in BCNF and if it has No multi-valued dependencies.
5th Normal Form(5NF or PJNF): A table is in 5NF, also called "Projection-Join Normal Form", if it is in 4NF and if every join dependency in the table is a consequence of the candidate keys of the table.
Domain/key Normal Form(DKNF): A key uniquely identifies each row in a table. A domain is the set of permissible values for an attribute. By enforcing key and domain restrictions, the database is assured of being freed from modification anomalies. DKNF is the normalization level that most designers aim to achieve.
How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables? One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships. Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.
What is DeNormalization and when would you go for it? DeNormalization is the Reverse process of Normalization. It allows the designers to keep redundant data in the system. This improves the query performance as the number of joins could be reduced. We will go for the DeNormalization, where the data does not change much frequently.
What are all the Basic things we need to observe before DeNormalizing a Database?
- Be sure that we have a good Over all understanding of logical design of system
- Don’t make an attempt to DeNormalize entire Database at a time
- Determine whether we need virtual columns
- Understand Data Integrity issues & storage techniques for the data
- Determine the frequency at which data might change
What are the Essential DeNormalization Techniques?Keeping Redundant Data Using Virtual Columns Performing Horizontal Data Partition Performing Vertical Data Partition
What are Constraints? Explain Different types of Constraints? Constraints enable the RDBMS to enforce the integrity of the database automatically, without creating the triggers, rule or defaults. Again,Constraints can defined as Restrictions Placed either at a Column level or Table level which ensures that the data meets certain Data integrity rules
Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY.
What is Entity Integrity Constraint? Entity Integrity Constraint ensures that each and every row should have a unique value for a column or combination of columns.
What is Domain Integrity Constraint? Domain Integrity Constraint refers to a pool of Data, for a column of a table, from which the column will take its values.
Define Candidate key, Primary Key, Alternate key and Composite key. A Candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest all are called alternate keys. A key formed by combining at least two or more columns is called composite key.
Define Foreign key When Primary key of one table appears as an attribute in another table, then it is called Foreign key in the second table.
What is the Referential Integrity?
Referential integrity refers to the consistency that must be maintained between primary and foreign keys. Referential Integrity ensures that for every value of foreign key, there is a matching value of primary key.
What is the Difference between a Primary Key and a Unique Key? Both primary key and Unique key enforce uniqueness of the column on which they are defined. The differences between these two are as shown below:
| Primary Key | Unique Key |
| Does not Allow Null Values | Allows Null Values |
| Only one Primary key exists in a table | More than one Unique key exists in a table |
| Creates a Clustered index on the column | Creates a Non Clustered Index on the column |
What is DDL, DML, DCL and TCL Commands. Data Definition Language(DDL): Data Definition Language statements are used to define the database structure or schema.
Ex : CREATE,ALTER,DROP,TRUNCATE,RENAME
Data Manipulation Language(DML) : Data Manipulation Language statements are used for manipulate or manage the data within the schema objects
Ex : INSERT,UPDATE,DELETE
Data Control Language(DCL) : Data Control Language statements are used to create roles, permissions, and referential integrity
Ex : GRANT,REVOKE
Transaction Control Language(TCL) : Transaction Control Language statements are used to manage different transactions occurring within a database.
Ex : COMMIT,SAVEPOINT,ROLLBACK
What is a Transaction? Transaction can be defined as a logical unit of work a grouping of one or more DBMS commands that are treated as a whole.
What are ACID Properties. Atomicity Consistency Isolation Durability
Explain Different Isolation Levels An isolation level determines the degree of isolation of data between concurrent transactions. The Isolation levels are :
- Read Committed
- Read Uncommitted
- Repeatable Read
- Serializable
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.