100+ Important DBMS Concepts for SDE Interviews(Part-I)
In my last article, I had given a brief list of important concepts of OOPs. Since many students found it helpful and requested one article for DBMS, I decided to create a similar list. Here is a list of important concepts in DBMS which are likely to be asked in an SDE interview.
Link for OOPs:- https://medium.com/@srushtisangawar25/list-of-important-oops-concepts-for-sde-interviews-2e1833a03eb8
1. Database: A database is an organized collection of structured information, or data, typically stored electronically in a computer system
2. DBMS: Database Management System (DBMS) is a software for storing and retrieving users’ data while considering appropriate security measures
3. Database System: Together, the data and the DBMS, along with the applications that are associated with them, are referred to as a database system
4. Advantages of DBMS: Reducing Data Redundancy, Sharing of Data, Data Integrity, Data Security, Privacy, Backup and Recovery, Data Consistency.
5. Three Levels of Abstraction: Physical, Logical, and View
6. Physical Level: This is the lowest level of data abstraction. It tells us how the data is actually stored in memory.
7. Logical Level: This level comprises the information that is actually stored in the database in the form of tables. It also stores the relationship among the data entities in relatively simple structures
8. View Level: This is the highest level of abstraction. Only a part of the actual database is viewed by the users. This level exists to ease the accessibility of the database by an individual user
9. Entity Integrity: Entity Integrity is the mechanism the system provides to maintain primary keys. The primary key serves as a unique identifier for rows in the table. The primary key for a row is unique; it does not match the primary key of any other row in the table
10. Referential Integrity: Referential integrity is a property of data stating that all its references are valid. In the context of relational databases, it requires that if a value of one attribute of a relation references a value of another attribute, then the referenced value must exist
11. Extension: It is the number of tuples present in a table at any instance
12. Intention: The intension of a database is the set of definitions of the data structures for the particular database (also called schema)
13. Data Independence: Data Independence is defined as a property of DBMS that helps you to change the Database schema at one level of a database system without requiring to change the schema at the next higher level. Data independence helps you to keep data separated from all programs that make use of it
14. Physical Data Independence: Physical Data Independence is defined as the ability to make changes in the structure of the lowest level of the Database Management System (DBMS) without affecting the higher-level schemas. Hence, modification in the Physical level should not result in any changes in the Logical or View levels
15. Logical Data Independence: Logical Data Independence is defined as the ability to make changes in the structure of the middle level of the Database Management System (DBMS) without affecting the highest-level schema or application programs
16. View: Views in SQL are considered as a virtual table. A view also contains rows and columns. There are two types of database views: dynamic views and static views
17. Data Model: Data Model gives us an idea that how the final system will look like after its complete implementation. It defines the data elements and the relationships between the data elements. Data Models are used to show how data is stored, connected, accessed, and updated in the database management system.
18. E-R Model: An entity-relationship model (or ER model) describes interrelated things of interest in a specific domain of knowledge. A basic ER model is composed of entity types (which classify the things of interest) and specifies relationships that can exist between entities (instances of those entity types).
19. Entity: In terms of DBMS, an entity is a table or attribute of a table in the database, so by showing relationships among tables and their attributes, ER diagram shows the complete logical structure of a database.
20. Entity Type: An Entity Type defines a collection of similar entities
21. Entity Set: An entity set is a group of similar entities and these entities can have attributes
22. Weak Entity Set: A weak entity can only exist when owned by another one
23. Strong Entity Set: A strong entity set is an entity set that contains sufficient attributes to uniquely identify all its entities. In other words, a primary key exists for a strong entity set. The primary key of a strong entity set is represented by underlining it.
24. Attribute: In a database management system (DBMS), an attribute refers to a database component, such as a table. It also may refer to a database field. Attributes describe the instances in the column of a database
25. Simple Attribute: Simple attributes are atomic values, which cannot be divided further. For example, a student’s phone number is an atomic value of 10 digits
26. Composite Attribute: An attribute composed of many other attributes is called a composite attribute. For example, the Address attribute of student Entity type consists of Street, City, State, and Country
27. Derived Attribute: Derived attributes are the attributes that do not exist in the physical database, but their values are derived from other attributes present in the database. For example, average_salary in a department should not be saved directly in the database, instead, it can be derived.
28. Single-Valued Attribute: Attributes that can have a single value at a particular instance of time are called single-valued. A person can’t have more than one age value. Therefore, the age of a person is a single-values attribute.
29. Relation Schema: A Relational schema can be described as a blueprint of a database that outlines the way data is organized into tables.
30. Relation: A relationship, in the context of databases, is a situation that exists between two relational database tables when one table has a foreign key that references the primary key of the other table
31. Degree of a Relation: The degree of a relation is the number of attributes it contains
32. Relationship Set: A relationship set is a set of relationships of the same type. A relationship set may be a unary relationship set or binary relationship set or ternary
33. DDL: Data Definition Language actually consists of the SQL commands that can be used to define the database schema.
34. DQL: DQL statements are used for performing queries on the data within schema objects. The purpose of the DQL Command is to get some schema relation based on the query passed to it
35. DML: The SQL commands that deals with the manipulation of data present in the database belong to DML or Data Manipulation Language and this includes most of the SQL statements.
36. Procedural DML: the user specifies what data is needed and how to get it
37. Non-Procedural DML: the user only specifies what data is needed.
38. DCL: DCL includes commands such as GRANT and REVOKE which mainly deal with the rights, permissions, and other controls of the database system.
39. TCL: TCL commands deal with the transaction within the database.
40. VDL: This language is used to specify user views and their mapping to the conceptual schema. It defines the subset of records available to classes of users. It creates virtual tables and the view appears to users at the conceptual level. It specifies user interfaces
41. SDL: SDL stands for Storage Definition Language. SDL matter is almost anything that’s not specified by SQL standards. It is different in every DBMS which specifies anything to do with how or where data in the relevant table is stored
42. Relational Algebra: Relational algebra is a procedural query language, which takes instances of relations as input and yields instances of relations as output
43. Relational Calculus: Relational calculus is a non-procedural query language that tells the system what data to be retrieved but doesn’t tell how to retrieve it.
44. Tuple-oriented Calculus: Tuple relational calculus works on filtering the tuples based on the specified conditions.TRC is the variable range over the tuples and is a type of simple subset of the first-order logic.TRC considers tuples as equal status as variables, and field referencing can be used to select the tuple parts
45. Domain-oriented Calculus: The domain regional calculus works based on filtering the domain and the related attributes.DRC is the variable range over the domain elements or the filed values. It is a type of simple subset of first-order logic. It is domain-dependent compared to TRC is tuple dependent
46. Normalization: Normalization is the process of minimizing redundancy from a relation or set of relations. Redundancy in relation may cause insertion, deletion, and update anomalies. So, it helps to minimize the redundancy in relations. Normal forms are used to eliminate or reduce redundancy in database tables
47. Functional Dependency: Functional Dependency (FD) is a constraint that determines the relation of one attribute to another attribute in a Database Management System (DBMS). Functional Dependency helps to maintain the quality of data in the database. It plays a vital role to find the difference between good and bad database design
48. Trivial Functional Dependency: The dependency of an attribute on a set of attributes is known as trivial functional dependency if the set of attributes includes that attribute. Symbolically: A ->B is a trivial functional dependency if B is a subset of A.
49. Non-Trivial Functional Dependency: If a functional dependency X->Y holds where Y is not a subset of X then this dependency is called non-trivial Functional dependency.
50. Lossless join property: In database design, a lossless join decomposition is a decomposition of a relation into relations. such that a natural join of the two smaller relations yields back the original relation.
51. 1NF: A relation will be 1NF if it contains an atomic value. It states that an attribute of a table cannot hold multiple values. It must hold only a single-valued attribute
52. Fully functional Dependency: If X and Y are an attribute set of a relation, Y is fully functional dependent on X, if Y is functionally dependent on X but not on any proper subset of X
53. 2NF: Second normal form (2NF) is a normal form used in database normalization. A relation is in the second normal form if it fulfills the following two requirements: It is in the first normal form. It does not have any non-prime attribute that is functionally dependent on any proper subset of any candidate key of the relation
54. 3NF: A table is in a third normal form when the following conditions are met –It's in 2NF and all nonprimary fields are dependent on the primary key
55. BCNF or 3.5NF: BCNF is the advanced version of 3NF. A table is in BCNF if every functional dependency X → Y, X is the super key of the table. For BCNF, the table should be in 3NF, and for every FD, LHS is super key
56. Partial Key: The set of attributes that are used to uniquely identify a weak entity set is called the Partial key. It is just a part of the key as only a subset of the attributes can be identified using it.
57. Alternate Key: Alternate Key or Secondary Key is the key that has not been selected to be the primary key, but are candidate keys.
58. Artificial Key: An artificial key has no meaning to the business or organization. An artificial key is an extra attribute added to the table that is seen by the user. It does not exist in the external reality but can be verified for syntax or check digits inside itself
59. Compound Key: Compound keys are always made up of two or more primary keys from other tables
60. Natural Key: A natural key (also known as a business key or domain key) is a type of unique key in a database formed of attributes that exist and are used in the external world outside the database
61. Foreign Key: A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables
62. Super Key: We can define a super key as a set of keys that uniquely identify a row or a tuple. The word super denotes the superiority of a key. Thus, a super key is the superset of a key known as a Candidate key (discussed in the next section). It means a candidate key is obtained from a super key only.
63. Primary Key: a primary key is a specific choice of a minimal set of attributes (columns) that uniquely specify a tuple (row) in a relation (table). Informally, a primary key is “which attributes identify a record”
64. Candidate key: The minimal set of attributes that can uniquely identify a tuple is known as a candidate key.
I will post the second part of the list soon. Stay tuned.
Thank you for reading!
