Association Table - Create UserRoles Association Table for Users and Roles Table

An association (mapping) table, also called a link table or join table, is a type of table in a relational database that is used to associate(map) records from two or more other tables. Typically used in many-to-many relationships. In this case, any record in one table can be related to multiple records in another table and vice versa.

 An association (mapping) table typically contains a foreign key that references the primary key of the table to which it is mapped. 

Example of an association (mapping) table -

"usersRoles" table have two columns: userId and roleId. This table is used to associate "users" table with "roles" table in a many-to-many relationship. The primary key is a composite key made up of both columns (PRIMARY KEY(userId, roleId)), and there are foreign key constraints to ensure that the "userId" column references the "id" column in the "users" table and the "roleId" column references the "id" column in the roles table.

Depends on the role assigned to user user will be able access or perform the actions.

Please refer below SQL Statement,

 CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL
);

CREATE TABLE roles (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT
);

CREATE TABLE usersRoles (
    userId INTEGER NOT NULL,
    roleId INTEGER NOT NULL,
    PRIMARY KEY(userId, roleId),
    FOREIGN KEY(userId) REFERENCES users(id),
    FOREIGN KEY(roleId) REFERENCES roles(id)
);


-- Inserting data into the users table
INSERT INTO users (name, email, password)
VALUES ('John Doe', 'johndoe@example.com', 'password123'),
       ('Jane Smith', 'janesmith@example.com', 'mypassword'),
       ('Bob Johnson', 'bobjohnson@example.com', '123456');

-- Inserting data into the roles table
INSERT INTO roles (name, description)
VALUES ('Admin', 'Has full access to the system.'),
       ('Editor', 'Can edit and create content.'),
       ('Viewer', 'Can view content but cannot make changes.');
      
-- Inserting data into the usersRoles table
INSERT INTO usersRoles (userId, roleId)
VALUES (1, 1), -- John Doe is an Admin
       (2, 2), -- Jane Smith is an Editor
       (3, 3), -- Bob Johnson is a Viewer
       (1, 2), -- John Doe is also an Editor
       (2, 3); -- Jane Smith is also a Viewer

Read More

SQL LIKE Operator

The LIKE operator in SQL is used with the WHERE clause to get a result set that matches the given string pattern.

There are two wildcards often used with the LIKE operator,

1. % (percent sign) - represents zero, one, or multiple characters
2.  _ (underscore sign) - represents one, single character 

LIKE Operator Syntax

SELECT columnName,...
FROM tableName
WHERE columnName LIKE 'pattern';

Example of SQL LIKE operator & Wildcards

1. SQL Statement selects all Company with CompanyName that have value "Microsoft"

SELECT * FROM Company
WHERE CompanyName LIKE 'Microsoft';

2. SQL Statement selects all Company with CompanyName that have value "M" at the first position

SELECT * FROM Company
WHERE CompanyName LIKE 'M%';

3. SQL Statement selects all Company with CompanyName that have value "soft" at the end

SELECT * FROM Company
WHERE CompanyName LIKE '%soft';

4. SQL Statement selects all Company with CompanyName that have value "web" at any position

SELECT * FROM Company
WHERE CompanyName LIKE '%web%';

5. SQL Statement selects all Company with CompanyName that have value "o" at second position

SELECT * FROM Company
WHERE CompanyName LIKE '_o%';

6. SQL Statement selects all Company with CompanyName that have value "g" at first position and value "e" as last position

SELECT * FROM Company
WHERE CompanyName LIKE 'g%e';

Read More

List of Tables with Row count

By using sys.tables and sys.partitions we can find a list of database tables with number records(rows) in a table.

Query :

SELECT DISTINCT t.NAME AS TableName,
                p.rows AS RowCounts
FROM sys.tables t
INNER JOIN sys.partitions p ON t.object_id = p.OBJECT_ID
ORDER BY t.Name

Read More

Difference between a Database and Schema?

Schema- A schema is the set of metadata (data dictionary) used by the database, typically generated using DDL. A schema defines attributes of the database, such as tables, columns, and properties. A database schema is a description of the data in a database. simply you can consider a schema to be a user account and collection of all objects therein.

Database- A database is a thing that contains all the schemas you've created, and their data (and a bunch of predefined system users, tables, views, etc. that make the whole things work). The database is the widest container, it collects schemas, tablespaces (datafiles are parts of a tablespace) and one or more (RAC) instances.

Read More

Advantages & Disadvantages of Normalizing a Database

In this post, I will explain the Advantages & Disadvantages of Normalizing a Database.

Computer databases are everywhere, from those used by banks to track customer accounts to those used by websites to store content. Databases work best when they are designed well. Normalizing a database means to design the database structure to store data in a logical and related way. It is usual for all databases to be normalized, and normalizing a database has advantages and disadvantages.

Reduces Data Duplication

Databases can hold a significant amount of information, perhaps millions or billions of pieces of data. Normalizing a database reduces its size and prevents data duplication. It ensures that each piece of data is stored only once.

Groups Data Logically

Application developers who create applications to "talk" to a database find it easier to deal with a normalized database. The data they access is organized more logically in a normalized database, often like the way in which the real-world objects that the data represent are organized. That makes the developers' applications easier to design, write and change.

Enforces Referential Integrity on Data

Referential integrity is the enforcement of relationships between data in joined tables. Without referential integrity, data in a table can lose its link to other tables where related data is held. This leads to orphaned and inconsistent data in tables. A normalized database, with joins between tables, can prevent this from happening.

Slows Database Performance

A highly normalized database with many tables and joins between the tables is slower than a database without those attributes. Many people using a normalized database at the same time also can slow down database speed. In some cases, a certain amount of denationalization of the database may be required to improve database speed.

Requires Detailed Analysis and Design

Normalizing a database is a complex and challenging task. Large databases with considerable amounts of information, such as ones run by banks, require careful analysis and design before they are normalized. Knowing the intended use of a database, such as whether it should it be optimized for reading data, writing data or both, also affects how it is normalized. A poorly normalized database may perform badly and store data inefficiently.

Read More

SQL Profiler

In this post, I will explain about SQL Profiler.

SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performances by executing too slowly.

Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.

Read More

What is Primary Key?

In this post, I will explain What is Primary Key?

Primary Key provides uniqueness for a column or set of columns in a table and used to identify a row (a record) in a table. A database table can have only one Primary Key and a column with a Primary Key doesn't allow NULL value (Not nullable). Primary Key generates a Unique Clustered Index.

Example of Primary Key-

Create Primary Key while creating a table
CREATE TABLE tbl_Employee
(
    EmpId INT NOT NULL,
    EmpName VARCHAR(100) NULL,
    CONSTRAINT PK_EmpId PRIMARY KEY (EmpId)
)

Create Primary Key using alter table statement
ALTER TABLE tbl_Employee
ADD CONSTRAINT PK_EmpId PRIMARY KEY (EmpId)

Drop Primary Key using alter table statement
ALTER TABLE tbl_Employee
DROP CONSTRAINT  PK_EmpId
 

Read More

What is Unique Key?

In this post, I will explain What is Unique Key?

Unique Key provides uniqueness for a column or set of columns in a table like a primary key. It is used to prevent duplicate values in a column. A database table can have more than one Unique Key and it allows at least one NULL value in a column( Nullable). Unique Key generates a Unique Non-Clustered Index

Example of Unique Key-

Create a Unique Key while creating a table
CREATE TABLE tbl_Employee
(
    EmpId INT NOT NULL,
    EmpName VARCHAR(100) NULL,
    CONSTRAINT UC_EmpId UNIQUE (EmpId)
)

Create Unique Key using alter table statement
ALTER TABLE tbl_Employee
ADD CONSTRAINT UC_EmpId UNIQUE (EmpId)

Drop Unique Key using alter table statement
ALTER TABLE tbl_Employee
DROP CONSTRAINT  UC_EmpId
 

Read More

What is Foreign Key?

In this post, I will explain What is Foreign Key?

A Foreign Key is a Column (or Set of Columns) that references a column (most often the primary key) of another table. A table containing the Foreign Key is called a child table, and a table containing Primary Key is called a parent table. 

Foreign Key is used to ensure referential integrity of the data in two tables and to prevent actions that would destroy links between tables. A Foreign Key also prevents invalid data from being inserted into a Foreign Key column, because it has to be one of the values contained in a parent table referenced column.

Example of Foreign Key-

Create Foreign Key while creating a table
CREATE TABLE tbl_Student
(
    StudentId INT NOT NULL,
    StudentName VARCHAR(100) NULL,
    CONSTRAINT pk_StudentId PRIMARY KEY (StudentId)
)

CREATE TABLE tbl_Class
(   
    ClassId  INT NOT NULL,
    ClassName VARCHAR(100) NULL,
    StudentId INT NOT NULL,
    CONSTRAINT pk_ClassId PRIMARY KEY (ClassId),
    CONSTRAINT fk_StudentClassId FOREIGN KEY (StudentId)
    REFERENCES tbl_Student(StudentId)
)

Create Foreign Key using alter table statement
ALTER TABLE tbl_Class
ADD CONSTRAINT fk_StudentClassId
FOREIGN KEY (ClassId) REFERENCES tbl_Student(StudentId);

Drop Foreign Key using alter table statement
ALTER TABLE tbl_Class
DROP CONSTRAINT  fk_StudentClassId

Read More

What is Database?

In this post, I will explain What is Database?

A Database is a collection of organized information that is stored in a computer so that it can be easily accessed, managed and updated. Database handlers create Database in such a way that only one set of software program provides access to data to all the users. The main purpose of the database is to operate a large amount of information by storing, retrieving and managing. There are many e-commerce websites on the world-wide web now a day which is handled through databases. For example, a model to checks the availability of rooms in a hotel.

Most databases contain multiple tables, which may each include several different fields. For example, a hotel database may include tables for rooms, staff, and financial records. Each of these tables would have different fields that are relevant to the information stored in the table. By storing website data in a database, the data can be easily searched, sorted, and updated. This flexibility is important for e-commerce sites and other types of dynamic websites.

There are many databases available like MS SQL Server, MySQL, Oracle etc. A cylindrical structure is used to display the image of a database.

Read More

What is Database Schema?

In this post, I will explain what is Database Schema?

A Database Schema represents the logical structure of all or part of a relational database. It can exist both as a visual representation and as a set of formulas known as integrity constraints. These formulas are expressed in a data definition language, such as SQL. A Database Schema indicates how the entities that make up the database relate to one another, including tables, views, stored procedures, and more.

Typically, a database designer creates a database schema to help programmers whose software will interact with the database. The process of creating a Database Schema is called data modelling. Conceptual schema focus on an organization’s informational needs rather than the structure of a database.

There are two main kinds of database schema:

1. A logical database schema conveys the logical constraints that apply to the stored data. It may define integrity constraints, views, and tables.

2. A physical database schema lays out how data is stored physically on a storage system in terms of files and indices.

At the most basic level, a Database Schema indicates which tables or relations make up the database, as well as the fields included on each table.

Read More

Difference between DBMS and RDBMS

In this post, I will explain the difference between DBMS and RDBMS.

DBMS and RDBMS both are used to store information in a physical database but there are some remarkable differences between them. DBMS stands for Database Management System where RDBMS stands for Relational Database Management System. 

1. DBMS (Database Management System)

• DBMS applications store data as a file.
• In DBMS, data is generally stored in either a hierarchical form or a navigational form.
• Normalization is not present in DBMS.
• DBMS does not apply any security with regards to data manipulation.
• DBMS uses a file system to store data, so there will be no relation between the tables.
• DBMS must provide some uniform methods to access the stored information.
• DBMS does not support distributed database.
• DBMS is meant to be for small organization and deal with small data. it supports a single user.
• Examples of DBMS are file systems, XML etc.

2. RDBMS(Relational Database Management System)

• RDBMS applications store data in a tabular form.
• In RDBMS, the tables have an identifier called primary key and the data values are stored in the form of tables.
• Normalization is present in RDBMS.
• RDBMS defines the integrity constraint for the purpose of ACID (Atomicity, Consistency, Isolation and Durability) property.
• In RDBMS, data values are stored in the form of tables, so a relationship between these data values will be stored in the form of a table as well.
• RDBMS system supports a tabular structure of the data and a relationship between them to access the stored information.
• RDBMS supports distributed database.
• RDBMS is designed to handle a large amount of data. it supports multiple users.
• Example of RDBMS is SQL Server, Oracle etc.

Read More

How to backup SQL Server database on local drive and network drive

Backing up SQL databases regularly is a must-do activity. We can take a backup of SQL database on the local drive and network drive.

We will discuss both

Step 1

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

Read More