SQL Server Mentalist

Hi Folks,

This post is part of Series Database Management Systems

Currently running topic for this series is listed as below :

Series of Database Management Systems

>>Chapter 1 : DBMS [Database Management Systems]

>>Chapter 2 : Database Core Concepts and  Applications

>>Chapter 3 : Record Storage and Primary File Organization

>>Chapter 4 : Index Structures of Files

>>Chapter 5 : Entity-Relationship Model

>>Chapter 6 : Relational Algebra <You are Here>

Continuing from my previous post on this series.

We are going to Cover the Following Points in this article

• Introduction
• Relational Model Constraints

Introduction

In this will study the mathematical operations of database management systems. Relational model has established itself as the primary data model for commercial data processing application.

The relational model represents the database as a collection of relations having a set of rows and columns, each of which is assigned a unique name. Relation consists of a relational schema [structure of table] and relational instance [data in a table at the particular time]; there is a close correspondence between the concept of table and the mathematical concept of relation.

In relational model we use certain conventions. For instance, a row is called a tuple and a column is termed as an attribute. The domain of a relational schema is a pool of legal values.

Student [Reg. No, name, Addr, Phone, Dbirth, GPA]

In this example, Student is a relation and the attributes [columns] are RegNo, Name, Addr, Phone, Dbirth. A possible tuple for the Student relation is [„MBA02C1101′, ‘ Nupur Rastogi‟, ‘440, 1-main, 2nd cross, Airport Road, Kodenahalli, Bangalore-560008’, 25256789, 11-Jan-1986].

The domain of each attribute is as follows:

RegNo. : 10 alphanumeric characters

Name : characters

Addr : Alphanumeric characters

Phone : 7 digits

Dbirth : Date

Characteristics of a relation:

· The tuples in the relation need not be ordered.

· Each tuple in the relation is an entity.

Domain:

A domain D is a set of atomic values. For each attribute there is a set of permitted values called the domain of that attribute.

For e.g.: For the attribute empname, the domain is the set of all empnames.

Let D1 denotes the attributes of empnames. D2 – > Set of all empnames

D3 – > Set of all addr. D4 – > set of al phone D5 – > set of all salary.

In general a table of ‘n’ columns must be subset of D1X D2X D3DD4X Dn-1 X Dn.

In relational model terminology the data type describing the type of values that can appear in each column is called a domain.

1) Since a relation is a set of tuples we use the mathematical notation of t r

to denote that tuple t is in relation r.

2) A domain is atomic it‟s not divisible, if elements of the domain are considered to be individual units.

For e.g.: the set of integers is an atomic domain.

Degree of relation is defined as the number of attributes of its relation schema. Relation Database Schema is, made up of a relation name R and list of attributes. Where each attributes is the name of a role played by some domain in the relation schema. Relation database state is the set of total number of tuples.

Tuple is a collection of components in a sequence. The components type depends upon the data type specified. The component may be a person name, address, Date of birth, age, etc. Each component of a tuple is a value of specified type. A tuple containing „n components are “n-tuple”. For example, a quadruple contains 4 tuples, containing components of specified type STUDENT, CLASS, MARKS and SUB, implies that certain student of the class obtained certain marks in the certain subject.

Entity set:

The number of tuples in a relation is called an entity set. Database schema or relational schema:

1) Denoted by R [A, A3w, Ae……..An] is made up of a relation name R and a list of attributes A1, A2, A3….An

2) Database instance is the data in DATABASE at a particular moment of time.

3) D is called domain of A1 and denoted by dom[A1]

4) A relational schema is a list of attributes and their corresponding domains [set of values]

5) To represent incomplete tuples, we must use NULL values; e.g.:

Apartment number.

6) Candidate keys are other keys [except primary key].

7) Primary attribute is one of the candidate keys, where values of their attribute is unique and NOT NULL

8) If we denote cardinality of a domain D by | D |, and assume that all domains are finite, the total Number of tuples in the Cartesian product is

| dom(A1) | * |dom(A2) | * ………… * | dom(An) |

9) Current relation state reflects only the valid tuples that represent a particular state of the real world.

Tables must be normalized. Relational Model Notation:

Ø A relational schema R of degree ‘n’ is denoted by R[A1, A2. A3…….An],

known as degree of relation [total number of attributes].

Ø An n-tuple t in a relation r(R) is denoted by t = [V1, V2. V3…..Vn] where

Vi is the value corresponding to attribute Ai

Ø The letters Q, R, S denote relation names.

Ø The letter q, r, s denote relation status

Ø The letters t, u, v denote tuples.

Ø In general the name of a relation such as STUDENT indicates the current set of tuples in that relation – where STUDENT (name, SSN…..) refers to the relation schema.

Relational Model Constraints

These include

Ø Domain constraints

Ø Key constraints

Ø Entity integrity constraints

Ø Referential integrity constraints

Ø Data dependencies [functional and multi valued dependencies]

Domain constraints:

It specifies that the value of each attribute A must be an atomic value from the domain dom (A) for that attribute. It also specifies that integer type holds only integer values but not float values.

Ø It is possible for several attributes to have the same domain.

For e.g: Customer name and employee name must have the same domain.

Ø The SQL standard supports a restricted set of domain types.

Characters, fixed-length string, and variable-length with user specified length (char (20) and varchar2 (20)) are also available.

Numeric data types for integers and real number (e.g. Number (4) and

Number (5,2))

Ø Other possible domains maybe described by a sub range of values from a data type or as an enumerated data type where all possible values are explicitly listed.

Ø Standard SQL, allow the domain of the attribute to include the specification NOT NULL. This prohibits the insertion of a NULL value for this attribute. Any DATABASE modification that would cause a NULL to be inserted in a NOT NULL domain generates error. By default, primary keys are of type NOT NULL.

Key constraints:

Ø A relation is defined as a set of tuples.

Ø All tuples in a relation must also be distinct. This means that no 2 tuples can have the same value [maintains uniqueness]. Tl[sk] !=2[sk], here sk is a super key.

Ø In any schema there will be a super key [pk] to distinguish tuples.

Ø Every relation can have at least 1 super key.

Ø In general, a relation schema may have more than one key. In this case each of these keys is called a candidate key.

Relational database schema and integrity constraints:

Ø A relational DATABASE schema S is a set of relation schema S = {R1,

R2…..Rn} and a set of integrity constraints (IC).

Ø A relational DATABASE instance DB of S is a set of relation instances DB = {r1, r2…..rm} such that ri is an instance of Ri and such that each ri relations satisfy the integrity constraint specified in IC.

Ø The relational DATABASE schema for company is shown below:

Employee:

Department:

Integrity constraints are specified on a DATABASE schema and are expected to hold on every database instance of the schema.

Entity constraints:

It states that no PK value can be NULL, because PK value is used to identify individual tuple in a relation. Having NULL values for PK implies that we cannot identify some tuples.

Referential integrity:

Tables can be related by common columns; a referential integrity constraint requires that the value in the foreign key matches a value in the parent key [unique or primary key of the same or different table referenced key]

For e.g: The attribute DNO of emp gives the dept. number for which each employee works, hence its value in every employee tuple must match the DNUM value of some tuple in the department relation.

Foreign key:

The condition for a foreign key specifies a referential integrity constraint between the two relation schemas R1 andR2.

An attribute A is a foreign key, if it satisfies the following rules.

A rule defined on a column in one table that allows insert or update of a row, only if the value for the column dependent table matches a value in a column of the referenced table. That is, the attribute in FK must have the same domain (values) as the primary key attribute PK of another relation (parent table).

Foreign key reference, a parent key of the same table, is called self referential integrity constraint.

For eg: DNUM is a foreign key of emp relation referring to the DNUM as a foreign key of emp relation, referring to the DNUM of dept relation.

Hope you will like Series of Database Management Systems series !

If you have not yet subscribe this Blog , Please subscribe it from “follow me” tab !

So that you will be updated @ real time and all updated knowledge in your mail daily for free without any RSS subscription OR news reading !!

Happy Learning and Sharing !!

For More information related to BI World visit our all Mentalist networks Blog

SQL Server Mentalist … SQL Learning Blog

Business Intelligence Mentalist … Business Intelligence World

Microsoft Mentalist … MVC,ASP.NET, WCF & LinQ

MSBI Mentalist … MS BI and SQL Server

NMUG Bloggers …Navi Mumbai User Group Blog

Architectural Shack … Architectural implementation and design patterns

DBA Mentalist …Advance SQL Server Blog

MVC Mentalist … MVC Learning Blog

Link Mentalist … Daily Best link @ your email

Infographics Mentalist … Image worth explaining thousand Words

Hadoop Mentalist … Blog on Big Data

BI Tools Analysis … BI Tools

Connect With me on

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