SQL Server – What are Bags & Sets?

In this post I will talk about the foundation of relational model of database. The intention is to learn and understand what are sets & bags.

Well there are different ways to organize data in a database, relational databases are one of the most effective. Relational database systems are an application of mathematical set theory to the problem of effectively organizing data. In a relational database, data is collected into tables (called relations in relational theory).

A table represents some class of objects that are important to an organization. For example, a company may have a database with a table for employees, another table for customers, and another for stores. Each table is built of columns and rows (called attributes and tuples in relational theory). Each column represents some attribute of the object represented by the table. For example, an Employee table would typically have columns for attributes such as first name, last name, employee ID, department, pay grade, and job title. Each row represents an instance of the object represented by the table. For example, one row in the Employee table represents the employee who has employee ID 12345.

When organizing data into tables, you can usually find many different ways to define tables. Relational database theory defines a process called normalization, which ensures that the set of tables you define will organize your data effectively.

SQL Server technically has “bags” not “sets”. So let me just explain you some of the concept of relational theory like Sets, Bags, Tables.

1. Set – A set is an unordered collection of elements without duplicates.

2. Bags – A Bag is unordered collections of elements with duplicates. A bag is also called as MultiSets.

3. Tables – It is an associations of keys and values

In bags, the order of insertion is not important. Elements can be inserted and removed entirely at random. By using the name Bag to describe this abstract data type, the intent is to once again to suggest examples of collection that will be familiar to the user from their everyday experience. A bag of balls is a good example. 

Operations you can do with a bag are –

• Inserting a new value
• Removing a value
• Testing if a value is present in the collection
• Find number of elements in the collection

In addition, many problems require the ability to loop over the elements in the container. However, we want to be able to do this without exposing details about how the collection is organized (for example, whether it uses an array or a linked list).

A Set extends the bag in two important ways.

• First the elements in a set must be unique;
• Second the set adds a number of operations that combine two sets to produce a new set.
  • The union – It is the set of values that are present in either collection.
  • The intersection – It  is the set of values that appear in both collections.
  • A set difference includes values found in one set but
    not the other.

Finally, the subset test is used to determine if all the values found in one collection are also found in the second. Some implementations of a set allow elements to be repeated more than once. This is usually termed a multiset.

That’s all folks; I hope you’ve enjoyed the article and I’ll see you soon with more articles.

Thanks!

Pawan Kumar Khowal

MSBISKills.com