SQL Server Mentalist

Hi Folks ,

It had been months, I was not able to write a single post on Laymen to SQL Developer series. No more wait for this series now, will post regularly on this from now.

We have completed Assignment One which is covering 8 different topics with following topics 

• Introduction to brand New SQL Server Zero to Basics Series
• Assignment #1 – Part #1–Introduction to Data and database Why Database exist ?
• Assignment #1 – Part #2 – Why DBMS or What are Limitations of file processing systems
• Assignment #1 – Part #3 – What is Data Abstraction and All information related to Data Abstraction in DBMS
• Assignment #1 – Part #4 – What is Data Independence and All information related to Data Independence in DBMS
• Assignment #1 – Part #5 – Database Schemes ,Database Instances and Some Basic Definitions
• Assignment #1 – Part #6 – Storage manager, Query Processing ,Transaction Management and Internals Sooner My focused will shifted to SQL Server 2008 R2  as this is only our destination where we can study all DB related stuff in more simple and easy manner.

Now we are starting Assignment 2 in which we will cover terminologies , Knowledge gathering  and understanding deep dive of Database System with respect SQL Server also there will be introduction to SQL Server from this series.In this article we are going to cover following points

• Introduction of Relational model
• Why Study Relational Model?
• What are Attribute Types?
• Relation Schema and Instance
• What are Attribute Types?
• Relations are Unordered
• Database Basics
• Keys
• Summary of Relational Model
• Relational Query Languages

 Introduction of Relational model

Name it self suggest us Model of Relational entities or framework of Relationship

The Relational Model was the first theoretically founded and well thought out Data Model, proposed by EfCodd in 1970

The relational database model puts a firm conceptual foundation under both the database and the DBMS features. Most of the modern DBMS are relational, Simple and elegant model with a mathematical basis.

Lead to the development of a theory of data dependencies and database design. Relational algebra operations –crucial role in query optimization and execution.

• The data structure model defines how to represent data.
• The relational model of data is the most widely used model today.
• Main concept: relation, basically a table with rows and columns.
• Every relation has a schema, which describes the columns, or fields. Data Independence: The Big Breakthrough of the Relational Model

Which we have already discussed on :Assignment #1 – Part #4 – What is Data Independence and All information related to Data Independence in DBMS

Why Study Relational Model?

Relational model development is basic block building of any Database Development

Most widely used model currently.

–>DB2, MySQL, Oracle, PostgreSQL, SQLServer, …

—->>Note: some “Legacy systems” use older models e.g., IBM’s IMS

Object-oriented concepts have recently merged in

–>object-relational model

—>>Informix, IBM DB2, Oracle 8i

Some graphical explanation for Relational Model :

What are Attribute Types?

• The set of allowed values for each attribute is called the domain of the attribute
• Attribute values are (normally) required to be atomic; that is, indivisible
• The special value null is a member of every domain
• The null value causes complications in the definition of many operations

Relation Schema and Instance

• A₁, A₂, …, A_n are attributes
• R = (A₁, A₂, …, A_n ) is a relation schema Example:instructor = (ID, name, dept_name, salary)
• Formally, given sets D₁, D₂, …. D_n a relation r is a subset of
  D₁ x D₂ x … x D_n
  Thus, a relation is a set of n-tuples (a₁, a₂, …, a_n) where each a_i Î D_i
• The current values (relation instance) of a relation are specified by a table
• An element t of r is a tuple, represented by a row in a table

Relations are Unordered

• Order of tuples is irrelevant (tuples may be stored in an arbitrary order)
• Example: instructor relation with unordered tuples

DATABASE

• A database consists of multiple relations
• Information about an enterprise is broken up into parts –instructor, student, advisor
• Bad design: univ (instructor -ID, name, dept_name, salary, student_Id, ..)
  results in
• Repetition of information (e.g., two students have the same instructor)
• The need for null values (e.g., represent an student with no advisor)
• Normalization – “good” relational schemas
• A database, we learned last time, is a large collection of data, organized for efficient search and retrieval.
• The For example, a bank would normally want to keep track of all of its account holders, their account numbers, and account balances.
• At the lowest level, this account information is really a bunch of bits on a disk that have some logical reason to be grouped together.
• A file system imposes a structure on these bits of data, and assign meaning to different groups of bits.

Following example of Relational Database based on BlogDB

Keys

• Let K C – R
• K is a superkey of R if values for K are sufficient to identify a unique tuple of each possible relation r(R) Example: {ID} and {ID,name} are both superkeys of instructor.
• Superkey K is a candidate key if K is minimal Example: {ID} is a candidate key for Instructor
• One of the candidate keys is selected to be the primary key. Ex :which one?
• Foreign key constraint: Value in one relation must appear in another ,Referencing relation, Referenced relation
  Following example of Key Relational Database based on BlogDB

  

Relational Query Languages

• Procedural vs.non-procedural, or declarative
• Relational algebra
• Tuple relational calculus
• Domain relational calculus
• Relational operators

Summary of Relational Model

• A tabular representation of data.
• Simple and intuitive, currently the most widely used – Object-relational variant gaining ground
• Integrity constraints can be specified by the DBA, based on application semantics. DBMS checks for violations. Two important ICs: primary and foreign keys and In addition, we always have domain constraints.
• Powerful query languages exist. Example with Database Language  SQL is the standard commercial one of which i m Die Heart fan of this.
• SQL = DDL + DML Where DDL = Data Definition Language and DML = Data Manipulation Language

You may also interested in reading topics like
• SQL Server Team – SQL Server 2008 R2 Service Pack 2 Customer Technology Preview (CTP) Available–Updates and Downloads
• BISQL #96 – SQL Server 2012 – Restore Database Features and Simplicity
• MSBI #45 – SQL Server 2012 #1 – Now SQL Server ‘Denali’ is 2012 !
• BISQL # 51 : SQL Server Denali Feature # 2 – Sequence number –All Explanation !!
• BISQL # 53 : SQL Server Denali Feature # 3 – Always On (Concept ,Architecture)
• BISQL # 50 : SQL Server Denali Feature # 1- File Tables # 2 – Demo and Scripting
• BISQL # 49 : SQL Server Denali Feature # 1–File Tables #1 – Introduction & Theory

Hope you will like this post on Relational Model.

If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog :

Link Resource Website

For More information related to BI World visit my Mentalist Blog

Link Resource Blog >> Daily Interesting links

SQL Server Mentalist >> SQL Learning Blog

Business Intelligence Mentalist >> BI World

Connect With me on

| Facebook |Twitter | LinkedIn| Google+ | Word Press | RSS |