Home > Link, Query, SQL, ZeroToBasic > BISQL #97 – Laymen to SQL Developer # 9 – Assignment #2 – Relational Model, Schema, Attribute, Database, Key & Relational Query

BISQL #97 – Laymen to SQL Developer # 9 – Assignment #2 – Relational Model, Schema, Attribute, Database, Key & Relational Query

Hi Folks ,image

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 

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

image

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 :

image

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

  • A1, A2, …, An are attributes
  • R = (A1, A2, …, An ) is a relation schema Example:instructor = (ID, name, dept_name, salary)
  • Formally, given sets D1, D2, …. Dn a relation r is a subset of
    D1 x D2 x … x Dn
    Thus, a relation is a set of n-tuples (a1, a2, …, an) where each ai Î Di
  • 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

clip_image002

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

image

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

    image

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

    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 |

About these ads
Categories: Link, Query, SQL, ZeroToBasic

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,114 other followers

%d bloggers like this: