SQL Server - Joins

In Structured Query Language (SQL), a JOIN is a method of linking two data tables in a single query, allowing the database to return a set that contains data from both tables at once, or using data from one table to be used as a Filter on the second table. There are several types of JOINs defined within the ANSI SQL standard.

Here explains how to use JOINS, both INNER and OUTER JOINS, in SQL Server (Transact-SQL) with syntax, visual illustrations, and examples.

Data Independence

If a database system is not multi-layered, then it becomes difficult to make any changes in the database system. Database systems are designed in multi-layers as we learnt earlier.

Data Independence
A database system normally contains a lot of data in addition to users’ data. For example, it stores data about data, known as metadata, to locate and retrieve data easily.

DBMS - Data Schemas

Database Schema
A database schema is the skeleton structure that represents the logical view of the entire database. It defines how the data is organized and how the relations among them are associated. It formulates all the constraints that are to be applied on the data.

A database schema defines its entities and the relationship among them. It contains a descriptive detail of the database, which can be depicted by means of schema diagrams.

DBMS - Data Models

A database model is a type of data model that determines the logical structure of a database and fundamentally determines in which manner data can be stored, organized and manipulated. The most popular example of a database model is the relational model, which uses a table-based format.

Data models define how the logical structure of a database is modeled. Data Models are fundamental entities to introduce abstraction in a DBMS. Data models define how data is connected to each other and how they are processed and stored inside the system.

The very first data model could be flat data-models, where all the data used are to be kept in the same plane. Earlier data models were not so scientific, hence they were prone to introduce lots of duplication and update anomalies.

Entity-Relationship Model
Entity-Relationship (ER) Model is based on the notion of real-world entities and relationships among them. While formulating real-world scenario into the database model, the ER Model creates entity set, relationship set, general attributes and constraints.

ER Model is best used for the conceptual design of a database.

ER Model is based on −

• Entities and their attributes.
• Relationships among entities.

These concepts are explained below.

• Entity − An entity in an ER Model is a real-world entity having properties called attributes. Every attribute is defined by its set of values called domain. For example, in a school database, a student is considered as an entity. Student has various attributes like name, age, class, etc.
• Relationship − The logical association among entities is called relationship. Relationships are mapped with entities in various ways. Mapping cardinalities define the number of association between two entities.

Mapping cardinalities −

• one to one
• one to many
• many to one
• many to many

Relational Model
The most popular data model in DBMS is the Relational Model. It is more scientific a model than others. This model is based on first-order predicate logic and defines a table as an n-ary relation.

The main highlights of this model are −

• Data is stored in tables called relations.
• Relations can be normalized.
• In normalized relations, values saved are atomic values.
• Each row in a relation contains a unique value.
• Each column in a relation contains values from a same domain.

DBMS - Architecture

The design of a DBMS depends on its architecture. It can be centralized or decentralized or hierarchical. The architecture of a DBMS can be seen as either single tier or multi-tier. An n-tier architecture divides the whole system into related but independent n modules, which can be independently modified, altered, changed, or replaced.

In 1-tier architecture, the DBMS is the only entity where the user directly sits on the DBMS and uses it.

DBMS - Overview

Database is a collection of related data and data is a collection of facts and figures that can be processed to produce information.

Mostly data represents recordable facts. Data aids in producing information, which is based on facts. For example, if we have data about marks obtained by all students, we can then conclude about toppers and average marks.

SQL Server - Restoring Databases

Restoring is the process of copying data from a backup and applying logged transactions to the data. Restore is what you do with backups. Take the backup file and turn it back into a database.

The Restore database option can be done using either of the following two methods.

Method 1 – T-SQL
Syntax
Restore database <Your database name> from disk = '<Backup file location + file name>'

SQL Server - Monitor Database

Monitoring refers to checking database status, settings which can be the owner’s name, file names, file sizes, backup schedules, etc.

SQL Server databases can be monitored mainly through SQL Server Management Studio or T-SQL, and also can be monitored through various methods like creating agent jobs and configuring database mail, third party tools, etc.

SQL Server - Assign Permissions

Permissions refer to the rules governing the levels of access that principals have to securables. You can grant, revoke and deny permissions in MS SQL Server.

To assign permissions either of the following two methods can be used.

SQL Server - Creating Backups

Backup is a copy of data/database, etc. Backing up MS SQL Server database is essential for protecting data. MS SQL Server backups are mainly three types − Full or Database, Differential or Incremental, and Transactional Log or Log.

Backup database can be done using either of the following two methods.

SQL Server - Drop Database

To remove your database from MS SQL Server, use drop database command. Following two methods can be used for this purpose.

Method 1 – Using T-SQL Script
Following is the basic syntax for removing database from MS SQL Server.

Computer Programming - Summary

Computer programming is defined as telling a computer what to do through a special set of instructions which are then interpreted by the computer to perform some task(s). These instructions can be specified in one or more programming languages including Java, Python, C, and C++.

A computer goes through a set of steps whose purpose is to achieve something. These steps are instructed to the computer by computer programs. Essentialy, computer programming is the process by which these programs are designed and implemented.

Computer Programming - File I/O

Computer Files
A computer file is used to store data in digital format like plain text, image data, or any other content. Computer files can be organized inside different directories. Files are used to keep digital data, whereas directories are used to keep files.

Computer files can be considered as the digital counterpart of paper documents. While programming, you keep your source code in text files with different extensions, for example, C programming files end with the extension .c, Java programming files with .java, and Python files with .py.

Computer Programming - Functions

A function is a block of organized, reusable code that is used to perform a single, related action. Functions provide better modularity for your application and a high degree of code reusing. You have already seen various functions like printf() and main(). These are called built-in functions provided by the language itself, but we can write our own functions as well and this tutorial will teach you how to write and use those functions in C programming language.

Good thing about functions is that they are famous with several names. Different programming languages name them differently, for example, functions, methods, sub-routines, procedures, etc.

Computer Programming - Strings

During our discussion about characters, we learnt that character data type deals with a single character and you can assign any character from your keyboard to a character type variable.

Now, let's move a little bit ahead and consider a situation where we need to store more than one character in a variable. We have seen that C programming does not allow to store more than one character in a character type variable. So the following statements are invalid in C programming and produce syntax errors −