Advanced SQL tutorial pdf, Define below transformation in DFD?, Difference between Cached Report and Snapshot Report, Download SQL Questions, Download SQL Server Interview Question in PDF, Download SQL SERVER Interview questions, Download SQL Server Interview questions and answers, download sql server interview questions and answers pdf, download sql server interview questions by Pawan Khowal, download sql server interview questions by Pawan Kumar, download sql server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, Free Download SQL SERVER Interview questions, How to make connection with a FTP server?, How to show "No Data Found" Message to end user?, SQL, SQL Common Interview Questions, SQL Common Interview Questions and answers, SQL FAQ, SQL FAQs, SQL Interview Q & A, SQL Interview Questions, SQL Queries asked in interviews, SQL Questions, SQL Server - General Interview Questions and Answers, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL SERVER Indexes, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, sql server interview questions and answers for net developers, SQL SERVER Tips, SQL Tips & Tricks, SQL Tips and Tricks, SQL Tricks, T-SQL Server Interview Questions, What are Bags & Sets?
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.
Pawan Kumar Khowal
Pingback: SQL Server Question – Why does SUM of an empty table returns NULL & Not 0 ? | Enhance your SQL Server & MSBI Stack Skills