What does the business rule in the context of a database?
A business rule is a declaration that places restrictions on a certain feature of the database, such as the details of a field’s field specification or the properties of a given relationship.
What is normalization?
By reducing the redundant data, normalization is a rigorous procedure that enhances data integrity in the table.
In other words, Data that is not structured is organized into structured data through database normalization. Database normalization is a process to arrange the tables and their columns in a way that should lessen data complexity and redundancy while enhancing data integrity.
What are the advantages of normalization?
- It greatly reduces the duplicated data. minimizes needless storage use as a result.
- Within the database, it ensures data consistency.
- It offers a database design that is more flexible.
- High likelihood of increased database security.
- Better and faster execution.
- This logically groups the data.
- It makes sure that data has referential integrity.
What is denormalization?
By adding redundant data to one or more tables, denormalization is a database optimization approach. In a relational database, this can help us avoid expensive joins.
In other words, Denormalization is a method for combining data from various tables into one quick-querying table.
Is denormalization is opposite to normalization?
No. Denormalization does not imply “reversing normalization” or “not to normalize,” as some may believe. It is an optimization method used following normalization.
What are the advantages of denormalization?
- We perform fewer joins, which speeds up data retrieval.
- Since we need to look at fewer tables, retrieval queries may be simpler (and therefore less likely to include problems).
What are the drawbacks to denormalization?
- Insertions and updates are expensive.
- Difficult to write updates and inserts
- Data may not be accurate.
- More storage is required because of data redundancy.
What is the difference between SQL and SQL Server?
SQL is a query language. It is used to create queries that access or alter data stored in relational databases. It is a programming language with specialized functionality for managing data in relational database management systems or relational data stream management systems.
Microsoft created the relational database management system known as Microsoft SQL Server. It is a software product known as a database server, and its main job is to store and retrieve data as needed by other software applications. These applications may run on the same computer or on a different machine over a network. In short, SQL Server, on the other hand, is a proprietary program or an RDBMS tool that carries out the SQL statements.
How many different sorts of instance types exist in SQL Server?
Two. Default instance and Named instance.
What are the benefits of or distinctions between a named instance and a default instance?
The advantage of the default instance is that it only requires the server name such as MSSQLSERVER to connect, as opposed to named instances, which also require an instance name such as MSSQLSERVER/DUBAI.
You can install numerous named instances but you can only install one default instance.
A default instance and a named instance differ fundamentally mostly in terms of network connectivity. Clients can connect to the default instance via the well-known 1433 port by using just the hostname. Clients must supply the host and instance name in order to connect to a named instance, and the SQL Server Browser service will then return the port the named instance is listening on.
What is the system database? What they are used for?
The databases that are loaded with SQL Server are known as system databases. These are used to store system-level data, metadata data, data about scheduled jobs and alerts, etc.
How many system databases are there?
There are five system databases; four of them will be shown in the object explorer.
- master Database
Records all the system-level information for an instance of SQL Server.
- msdb Database
is used by SQL Server Agent for scheduling alerts and jobs.
- model Database
is used as the model for all databases built on the SQL Server instance. All subsequent databases are established with the same settings as the model database, including database size, collation, recovery model, and other database variables.
- Resource Database
contains system objects that come with SQL Server and is a read-only database. System objects are logically present in every database’s sys schema, but they are physically persisted in the Resource database. User data and user metadata are not present in the Resource database. - tempdb Database
is a workspace for holding temporary objects or intermediate result sets.
What SQL Server DDL command was used to alter the objects?
ALTER
What is the difference between DROP, DELETE and TRUNCATE? Which one is faster?
The database table’s records can be deleted using the DELETE command. The rows that should be eliminated can be specified using the WHERE clause.
The DROP Command removes the specified database item or the entire table from the database.
All the rows in the current table are deleted using the TRUNCATE command.
When the delete statement is used within a transaction, deleted rows can be restored using the ROLLBACK command. Since TRUNCATE is an auto-commit, we are unable to recover the table using the “ROLLBACK” command.
The TRUNCATE command is faster than the DROP and DELETE.
Big Data and SQL 
One comment