Category Archives: DBMS

Choose between MongoDB and MySQL?

As there is a huge surge in the on-demand application market, businesses are looking to build feature-rich applications across platforms. Database management of such high functioning application is complex and tedious. With large coding structures and programming to incorporate all the changes and updates that will be integrated at various stages of app development.

There are two major DBMS in the market, which are open-sourced, free and most popular too. They are MongoDB and MySQL. MongoDB is developed by MongoDB inc. and MySQL is developed by Oracle. It is important to know them very well when it comes to choosing the right DBMS.

MongoDB: An Introduction

MongoDB was conceptualised in 2007 and launched in 2010 by MongoDB inc. It employs key-value pairs known as a document store. These document stores are created and maintained in BSON files, which are a modified version of JSON files. The use of modified JSON files allows the exchange of data between web applications and human-readable servers. There is no schema implementation, providing flexibility in the design of code structure.

It allows hierarchal relationships representation and changes in records onboard. With NoSQL or un-structured query language, MongoDB provides facilities of auto-sharding and embedding and mirroring.

MySQL: An Introduction

Originally, designed as MySQL AB by Oracle, this DBMS is a relational database management system or RDBMS. MySQL employs the storage of data in vertical columns and horizontal rows. It uses structured query language for data access and transfers through commands like SELECT, INSERT, UPDATE, DELETE.

Related information is stored in different tables reducing the possibility of data duplication. MySQL uses pr-defined schema designs, which can’t be changed.

Performance Comparison

MongoDB has a single most important edge over MySQL and that is its ability to handle large unstructured data. It is magically faster because it allows users to query in a different manner that is more sensitive to workload. It also allows more RAM and CPU slice for queries to run faster.

In the MongoDB database, one can partition and shard the database with the In-built sharding feature. You can specify a shard key and sharding is enabled, which partitions the data evenly according to the shard key. MongoDB also possess an auto replica feature which allows replicating of data and imparting them primary and secondary role according to the scenarios at any point, in the entire process, It also improves the flow rate of operations by providing built-in special functions that help in finding specific data from specific locations with accuracy to boost up the overall process.

MongoDB uses security features, which include authentication, auditing, and authorization and you can also use Transport Layer Security (TLS) and Secure Sockets Layer (SSL) for encryption purposes. This ensures that it is only accessible and readable by the intended client.

While MySQL authenticates a user and facilitates it with user privileges on a particular database such as CREATE, SELECT, INSERT, UPDATE, and in doing so, it fails to explain why a given user is denied specific access.

When to use MongoDB?

MongoDB can be used when you need to build a cloud-based application for multi-platforms. When there is a need for instant and fast data recovery, totally automated. When you need to reduce your schema migration cost without a database administrator.

When to use MySQL?

MySQL can be used if your database is not scaled yet and want a low maintenance setup. If you want high performance on a limited budget and with a fixed schema. There will be no major changes in the near future and there would be high transactions. The most important parameter is data security as MySQL is a totally secure database.

Scalability Comparison

Scaling is done in two ways, vertically and horizontally. Vertical scaling includes adding more powerful RAM and CPU processing power to the same machine. While horizontal scaling is to add more machines to your pool adding RAM power and CPU processing power by unilateral distribution.

MySQL databases are vertically scalable, which means that you can increase the load on a single server by increasing things like CPU, RAM or SSD. MongoDB databases are horizontally scalable. You can handle more traffic by sharding, or adding more servers in your MongoDB database.

Spread the love

Normalization in DBMS

Normalization in Database Management System is a structured and well defined systematic, step-by-step process of organising the data in the database to minimises the redundancy. It is also used to eliminate unwanted anomalies during Insertion, Update, and Deletion.

This Section is able to tech and Answer various DBMS Interview Questions related to Normalization. After reading this Article you will able to answer the DBMS Interview Questions related to Normalization, Normal Form, Functional Dependency, 1NF,2NF,3NF and BCNF.

What is the role of Normalization in database design?

To reduce redundancy from the database or individual table.

How to achieve Normalization?

By dividing the larger table into the smaller table and linking them using relationship.

What are the normal forms and their types?

For a table to be in certain normal form it has followed certain rules; if it does so then the table is said to be in that particular normal form. Its types are 1st, 2nd, 3rd & Boyce-Codd Normal form.

What are anomalies and their types?

Relations that have redundant data may have problems called anomalies. They can of type: Insertion, Update, and Deletion Anomalies.

Explain different types of anomalies in DBMS?

  • Insertion anomalies: When a tuple/record is tried to be inserted in referencing relation without it being present in the referenced table; it will cause an Insertion Anomaly.
  • Deletion & Update/ Modification anomalies: When a tuple is deleted or updated from referenced relation and the referenced attribute value is used by referencing attribute in referencing relation, it will not allow deleting the tuple from referenced relation. To avoid such a situation: ON UPDATE/DELETE (SET NULL/CASCADE) is used for setting referenced attribute null or update/delete affected record/row/tuple.

To completely understand the concept of Normalization and normal forms, we need to understand what functional dependency is. So, let’s dive in:

What is Functional Dependency in DBMS?

Functional Dependency describes the interrelation of columns within a table; i.e. when values in one field depend on 1 or more other fields within the same table.

For example, there’s a table/ relation to store data of people within a locality who are eligible to get a driving license. The structure of the table is: (name, age, eligibility). It is clearly known that the field eligibility depends on the age of the person, so “eligibility” is functionally dependent on “age”. (age-> eligibility)

How many types of Functional Dependencies are there in DBMS?

FD can be of types:

  1. Trivial FD – where 1 is a subset of other
  2. Non-trivial FD – when related field/s are not subsets of other
  3. Transitive FD – col1 ->col2 -> col3 indicates that if we know col1, col2 could be known, and if we know col2, col3 could be known

What is Normal Form and how many types of Normal Form are there in DBMS?

The state of the Data Table when there is no redundancy and table is the best organized is known as Normal Form.

Following types of Normal Forms are there:

  1. 1st Normal Form or 1 NF
  2. 2nd Normal Form or 2 NF
  3. 3rd Normal Form or 3 NF
  4. Boyce-Codd Normal Form or BCNF

1st Normal Form or 1 NF

A relation is said to be in 1NF if it contains an atomic value for every attribute in a record, i.e. no attribute can be multi-valued.

Conversion Step needed: To enter multi-valued attributes in multi-rows by copying other single valued attributes.

2nd Normal Form or 2 NF

2 NF is a relation that is in 1 NF and every non-primary-key attribute is fully functionally dependent on the primary key. This conversion involves the removal of partial dependencies.

Conversion Step needed: Remove FD attributes from the table and place them in a new table.

3rd Normal Form or 3 NF

A relation is said to be in 3NF if it is in 2 NF and no non-primary-key attribute is transitively dependent on the primary key. This conversion involves removal of transitive dependencies if any.

Conversion Step needed: To remove transitive dependency. Eg: address (Line 1,2,3, Street, City, State, PIN code) need to be made as different table and indexed as a unique value and not included directly into Person/ Student/ Employee table.

Boyce-Codd Normal Form or BCNF

A relation is said to be in BCNF if it is in 3 NF and for each functional dependency (X → Y), X should be a super Key.

Conversion Step needed: Decompose FD table further if needed. Eg: for a dependency A → B, A cannot be a non-prime attribute Health Fitness Articles, if B is a prime attribute.

Hope you Enjoyed reading this article based on DBMS Interview Questions Related to normalization of database and normal forms. If you have any query related to DBMS Interview please let us know we will find best suitable answers by our experts and publish here.

Spread the love