tag:blogger.com,1999:blog-53508340418461507172024-03-13T03:54:01.401-07:00DbmsEnotesA complete collection of Database management system notes,tools and project..Royhttp://www.blogger.com/profile/06335373269654004062noreply@blogger.comBlogger72125tag:blogger.com,1999:blog-5350834041846150717.post-49525493284114851482014-10-20T09:06:00.002-07:002015-03-13T09:58:25.261-07:00Special relational operations | Relational Model<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Verdana,sans-serif;"><b>SPECIAL RELATIONAL OPERATIONS</b></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-DhMmNWQaCjc/VQMRtGUh0wI/AAAAAAAAK9g/QUFjLFC_tmE/s1600/huge-full-outer-join.gif" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" src="http://2.bp.blogspot.com/-DhMmNWQaCjc/VQMRtGUh0wI/AAAAAAAAK9g/QUFjLFC_tmE/s1600/huge-full-outer-join.gif" height="161" width="200" /></a></div>
<span style="font-family: Verdana,sans-serif;"><b><br /></b></span>
<span style="font-family: Verdana,sans-serif;"><b>These includes operations like</b></span><br />
<span style="font-family: Verdana,sans-serif;"> </span>
<br />
<ol style="text-align: left;">
<li><span style="font-family: Verdana,sans-serif;"><b>Selection</b></span></li>
<li><span style="font-family: Verdana,sans-serif;"><b>Projection</b></span></li>
<li><span style="font-family: Verdana,sans-serif;"><b>Join</b></span></li>
<li><span style="font-family: Verdana,sans-serif;"><b>Division</b></span></li>
</ol>
<div>
<span style="font-family: Verdana,sans-serif;"><b>1. Selection-</b>To
identify a set of tuples which is a part of a relation and to extract only
these tuples out. The select operation selects tuples that satisfy a given
predicate or condition. It is also known as restriction operation. </span><br />
<a name='more'></a></div>
<br />
<ol style="text-align: left;">
</ol>
<div>
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;"><span style="line-height: 15.4pt; text-indent: -0.25in;">It is a unary operation
defined on a single relation.</span></span></span><br />
<!--more--></div>
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;"><br /></span></span>
<br />
<div class="MsoNormal" none repeat scroll 0% 0% ; line-height: 15.4pt; margin: 0in; text-indent: -0.25in;">
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">·<span style="line-height: normal;"> It is denoted as <b>s</b>.</span></span></span></div>
<span style="font-family: Verdana,sans-serif;"><b><br /></b></span>
<span style="font-family: Verdana,sans-serif;"><b>REPRESENTATION- </b><span style="line-height: 20.53333282470703px; text-indent: -24px;"><span style="font-size: 11.5pt;"><b>s (CONDITION) (RELATION)</b></span></span></span><br />
<span style="font-family: Verdana,sans-serif;"><br /></span>
<span style="font-family: Verdana,sans-serif;"><b>EXAMPLE- </b>Consider a relation "EMP"</span><br />
<span style="font-family: Verdana,sans-serif;"><br /></span>
<br />
<table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: none; mso-border-alt: solid windowtext .5pt; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;">
<tbody>
<tr>
<td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 41.4pt;" valign="top" width="55"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;"><b>ID</b></span></div>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="top" width="72"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;"><b>NAME</b></span></div>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 49.5pt;" valign="top" width="66"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;"><b>SALARY</b></span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 41.4pt;" valign="top" width="55"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">1</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="top" width="72"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">Rahul</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 49.5pt;" valign="top" width="66"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">5000</span></div>
</td>
</tr>
<tr style="height: 14.35pt; mso-yfti-irow: 2;">
<td style="border-top: none; border: solid windowtext 1.0pt; height: 14.35pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 41.4pt;" valign="top" width="55"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">2</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 14.35pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="top" width="72"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">Arjun</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 14.35pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 49.5pt;" valign="top" width="66"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">10000</span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 41.4pt;" valign="top" width="55"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">3</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="top" width="72"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">Abay</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 49.5pt;" valign="top" width="66"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">4000</span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 41.4pt;" valign="top" width="55"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">4</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="top" width="72"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">Avinash</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 49.5pt;" valign="top" width="66"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">8000</span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 41.4pt;" valign="top" width="55"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">5</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="top" width="72"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">Anu</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 49.5pt;" valign="top" width="66"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">10000</span></div>
</td>
</tr>
</tbody></table>
<span style="font-family: Verdana,sans-serif;"><br /></span>
<span style="font-family: Verdana,sans-serif;"><span style="font-size: 15px; line-height: 20.53333282470703px; text-indent: -24px;"><b>Ex-1 Select all employees whose salary>=8000</b></span></span><br />
<div>
<div style="text-indent: -24px;">
<span style="font-family: Verdana,sans-serif;"><span style="font-size: 15px; line-height: 20.53333282470703px;"><b><br /></b></span></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><span style="font-size: 15px; line-height: 20.53333282470703px; text-indent: -24px;"><b>s (salary>8000) (EMP)</b></span></span></div>
</div>
<div>
<span style="font-family: Verdana,sans-serif;"><span style="font-size: 15px; line-height: 20.53333282470703px; text-indent: -24px;"><br /></span></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><b>OUTPUT- </b></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><b><br /></b></span></div>
<table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: none; mso-border-alt: solid windowtext .5pt; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;">
<tbody>
<tr>
<td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 41.4pt;" valign="top" width="55"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;"><b>ID</b></span></div>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="top" width="72"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;"><b>NAME</b></span></div>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 49.5pt;" valign="top" width="66"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;"><b>SALARY</b></span></div>
</td>
</tr>
<tr style="height: 14.35pt; mso-yfti-irow: 1;">
<td style="border-top: none; border: solid windowtext 1.0pt; height: 14.35pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 41.4pt;" valign="top" width="55"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">2</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 14.35pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="top" width="72"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">Arjun</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 14.35pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 49.5pt;" valign="top" width="66"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">10000</span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 41.4pt;" valign="top" width="55"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">4</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="top" width="72"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">Avinash</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 49.5pt;" valign="top" width="66"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">8000</span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 41.4pt;" valign="top" width="55"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">5</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="top" width="72"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">Anu</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 49.5pt;" valign="top" width="66"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">10000</span></div>
</td>
</tr>
</tbody></table>
<span style="font-family: Verdana,sans-serif;"><br /></span>
<br />
<div>
<span style="font-family: Verdana,sans-serif;"><b>2. Projection</b>- It results in a new relation that contains a subset of the columns of a relation and eliminate any duplicate node.</span></div>
<div class="MsoNormal" line-height: 15.4pt; margin-bottom: .0001pt; margin: 0in; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -.25in;">
<span style="font-family: Verdana,sans-serif;"><span style="font-size: x-small;">·</span><span style="font-size: 7pt; line-height: normal;"> </span><span style="line-height: normal;"><span style="font-size: x-small;">
</span></span><span style="font-size: small;">It is a unary operation
defined on a single relation</span></span></div>
<div>
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;"><span style="line-height: 15.4pt; text-indent: -0.25in;"><span style="line-height: normal;"> </span></span><span style="line-height: 15.4pt; text-indent: -0.25in;">It is denoted as</span><span style="line-height: 15.4pt; text-indent: -0.25in;"> </span><b style="line-height: 15.4pt; text-indent: -0.25in;">?</b><span style="line-height: 15.4pt; text-indent: -0.25in;">.</span></span></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><b>REPRESENTATION- <span style="font-size: 11.5pt; line-height: 15.4pt; text-indent: -0.25in;"> </span><span style="line-height: 15.4pt; text-indent: -0.25in;"><span style="font-size: 11.5pt;">?</span></span> <span style="line-height: 20.53333282470703px; text-indent: -24px;"><span style="font-size: 11.5pt;"> (COLUMN) (RELATION)</span></span></b></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><span style="line-height: 20.53333282470703px; text-indent: -24px;"><span style="font-size: 11.5pt;"><br /></span></span></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><span style="line-height: 20.53333282470703px; text-indent: -24px;"><span style="font-size: 11.5pt;"><b>EX-2 select all employees name and their salary from the relation "EMP"</b></span></span></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><span style="line-height: 20.53333282470703px; text-indent: -24px;"><span style="font-size: 11.5pt;"><br /></span></span></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><b><span style="font-size: 11.5pt; line-height: 15.4pt; text-indent: -0.25in;"> </span><span style="line-height: 15.4pt; text-indent: -0.25in;"><span style="font-size: 11.5pt;">?</span></span> <span style="line-height: 20.53333282470703px; text-indent: -24px;"><span style="font-size: 11.5pt;"> (NAME,SALARY) (EMP)</span></span></b></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><b>OUTPUT-</b></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><b><br /></b></span></div>
<div>
<table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: none; mso-border-alt: solid windowtext .5pt; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;">
<tbody>
<tr>
<td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="top" width="72"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;"><b>NAME</b></span></div>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 49.5pt;" valign="top" width="66"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;"><b>SALARY</b></span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="top" width="72"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">Rahul</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 49.5pt;" valign="top" width="66"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">5000</span></div>
</td>
</tr>
<tr style="height: 14.35pt; mso-yfti-irow: 2;">
<td style="border-top: none; border: solid windowtext 1.0pt; height: 14.35pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="top" width="72"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">Arjun</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 14.35pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 49.5pt;" valign="top" width="66"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">10000</span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="top" width="72"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">Abay</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 49.5pt;" valign="top" width="66"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">4000</span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="top" width="72"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">Avinash</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 49.5pt;" valign="top" width="66"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">8000</span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="top" width="72"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">Anu</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 49.5pt;" valign="top" width="66"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">10000</span></div>
</td>
</tr>
</tbody></table>
</div>
<div>
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><b>3. Join- </b>To
restrict the no of rows obtained from the Cartesian product we used
joins.Join is performed on 2 relations having one or more attributes in
common and they should have some datatype.<span style="font-size: small;">It<span style="background-color: ;"> is a
binary operation and a combination of certain selections and a Cartesian
product into one operation.</span></span></span></div>
<div class="MsoNormal" line-height: 15.4pt; margin-bottom: .0001pt; margin: 0in; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -.25in;">
<span style="font-family: Verdana,sans-serif;"><span style="font-size: 10pt;">·<span style="font-size: 7pt; line-height: normal;">
</span></span>It is denoted as |X| .</span></div>
<div class="MsoNormal" line-height: 15.4pt; margin-bottom: .0001pt; margin: 0in; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -.25in;">
<span style="font-family: Verdana,sans-serif;"><span style="font-size: 10pt;">·<span style="font-size: 7pt; line-height: normal;">
</span></span>It is associative.</span></div>
<div class="MsoNormal">
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><b>REPRESENTATION- (P)<span \ line-height: 20.53333282470703px; text-indent: -24px;">| X|</span><span style="font-size: 11.5pt; line-height: 15.4pt; text-indent: -0.25in;"> </span><span style="line-height: 20.53333282470703px; text-indent: -24px;"><span style="font-size: 11.5pt;"> (JOIN CONDITION) (Q)</span></span></b></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><b><span style="line-height: 20.53333282470703px; text-indent: -24px;"><span style="font-size: 11.5pt;"><br /></span></span></b></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><span style="line-height: 20.53333282470703px; text-indent: -24px;"><span style="font-size: 11.5pt;">where P,Q are the names of the relation and where condition is of the form<b> pi Q qi</b></span></span></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><span style="line-height: 20.53333282470703px; text-indent: -24px;"><span style="font-size: 11.5pt;">where pi is the attributes of the relation P and qi is the attribute of the relation Q.</span></span></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><b>EXAMPLE- Consider 2 relations</b></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><b><br /></b></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><b>1 relation "EMP"</b></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><b><br /></b></span></div>
<div>
<table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: none; mso-border-alt: solid windowtext .5pt; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;">
<tbody>
<tr>
<td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .95in;" valign="top" width="91"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;"><b>EMP_ID</b></span></div>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 63.0pt;" valign="top" width="84"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;"><b>ENAME</b></span></div>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="top" width="72"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;"><b>DEPTID</b></span></div>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 45.0pt;" valign="top" width="60"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;"><b>SAL</b></span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .95in;" valign="top" width="91"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">123</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 63.0pt;" valign="top" width="84"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">Balwinder</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="top" width="72"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">1</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 45.0pt;" valign="top" width="60"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">5000</span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .95in;" valign="top" width="91"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">122</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 63.0pt;" valign="top" width="84"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">Simar</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="top" width="72"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">2</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 45.0pt;" valign="top" width="60"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">10000</span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .95in;" valign="top" width="91"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">134</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 63.0pt;" valign="top" width="84"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">Anmol</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="top" width="72"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">3</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 45.0pt;" valign="top" width="60"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">20000</span></div>
</td>
</tr>
</tbody></table>
</div>
<div>
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><b>2 relation "DEPT"</b></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><b><br /></b></span></div>
<div>
<table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: none; mso-border-alt: solid windowtext .5pt; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;">
<tbody>
<tr>
<td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .95in;" valign="top" width="91"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;"><b>DEPTNO</b></span></div>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 67.5pt;" valign="top" width="90"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;"><b>DNAME</b></span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .95in;" valign="top" width="91"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">1</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 67.5pt;" valign="top" width="90"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">CSE</span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .95in;" valign="top" width="91"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">2</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 67.5pt;" valign="top" width="90"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">ECE</span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .95in;" valign="top" width="91"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">3</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 67.5pt;" valign="top" width="90"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">CSE</span></div>
</td>
</tr>
</tbody></table>
</div>
<div>
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><b>Ex- Display the employee information with department name</b></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><b>EMP <span line-height: 20.53333282470703px; text-indent: -24px;">| X|</span><span style="font-size: 11.5pt; line-height: 15.4pt; text-indent: -0.25in;"> </span><span style="line-height: 20.53333282470703px; text-indent: -24px;"><span style="font-size: 11.5pt;"> (DEPID=DEPTNO) DEPT</span></span></b></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><b><span style="line-height: 20.53333282470703px; text-indent: -24px;"><span style="font-size: 11.5pt;"><br /></span></span></b></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><b><span style="line-height: 20.53333282470703px; text-indent: -24px;"><span style="font-size: 11.5pt;">OUTPUT-</span></span></b></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><b><span style="line-height: 20.53333282470703px; text-indent: -24px;"><span style="font-size: 11.5pt;"><br /></span></span></b></span></div>
<div>
<table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: none; mso-border-alt: solid windowtext .5pt; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;">
<tbody>
<tr>
<td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .95in;" valign="top" width="91"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;"><b>EMP_ID</b></span></div>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 63.0pt;" valign="top" width="84"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;"><b>ENAME</b></span></div>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="top" width="72"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;"><b>DEPTID</b></span></div>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 45.0pt;" valign="top" width="60"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;"><b>SAL</b></span></div>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 46.65pt;" valign="top" width="62"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;"><b>DNAME</b></span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .95in;" valign="top" width="91"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">123</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 63.0pt;" valign="top" width="84"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">Balwinder</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="top" width="72"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">1</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 45.0pt;" valign="top" width="60"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">5000</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 46.65pt;" valign="top" width="62"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">CSE</span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .95in;" valign="top" width="91"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">122</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 63.0pt;" valign="top" width="84"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">Simar</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="top" width="72"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">2</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 45.0pt;" valign="top" width="60"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">10000</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 46.65pt;" valign="top" width="62"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">ECE</span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .95in;" valign="top" width="91"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">134</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 63.0pt;" valign="top" width="84"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">Anmol</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="top" width="72"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">3</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 45.0pt;" valign="top" width="60"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">20000</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 46.65pt;" valign="top" width="62"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">CSE</span></div>
</td>
</tr>
</tbody></table>
</div>
<div>
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><b>4. Division-</b>
The division operator divides a dividend relation "r" of degree (m+n) by
a divisor relation "s" of degree (m) and produces a resultant relation
of degree "n".</span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><b>REPRESENTATION- </b><span style="background-color: ;">Let r(R) and s(S) be relations</span></span></div>
<div class="MsoNormal">
<span style="font-family: Verdana,sans-serif;"><b>r<span class="apple-converted-space"> </span>÷
s: -</b></span></div>
<span class="apple-converted-space"> The result
consists of the restrictions of tuples in r to the attribute names unique to R,
i.e. in the Header of r but not in the Header of s, for which it holds that all
their combinations with tuples in s are present in r.</span>
<br />
<div>
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><b>EXAMPLE- Consider 2 relations "r" and "s"</b></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><b><br /></b></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><b>Relation"r"</b></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><b><br /></b></span></div>
<div>
<table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: none; mso-border-alt: solid windowtext .5pt; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;">
<tbody>
<tr style="height: 13.0pt; mso-yfti-firstrow: yes; mso-yfti-irow: 0;">
<td style="border: solid windowtext 1.0pt; height: 13.0pt; mso-border-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 54.9pt;" valign="top" width="73"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;"><b>S.NO</b></span></div>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; height: 13.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 49.5pt;" valign="top" width="66"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;"><b>P.NO</b></span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 54.9pt;" valign="top" width="73"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">S1 </span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 49.5pt;" valign="top" width="66"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">P1</span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 54.9pt;" valign="top" width="73"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">S1</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 49.5pt;" valign="top" width="66"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">P2</span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 54.9pt;" valign="top" width="73"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">S3</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 49.5pt;" valign="top" width="66"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">P1</span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 54.9pt;" valign="top" width="73"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">S4</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 49.5pt;" valign="top" width="66"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">P2</span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 54.9pt;" valign="top" width="73"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">S2</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 49.5pt;" valign="top" width="66"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">P3</span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 54.9pt;" valign="top" width="73"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">S2</span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 49.5pt;" valign="top" width="66"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">P1</span></div>
</td>
</tr>
</tbody></table>
</div>
<div>
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><b>Relation "s"</b></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
<div>
<table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: none; mso-border-alt: solid windowtext .5pt; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;">
<tbody>
<tr>
<td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 36.9pt;" valign="top" width="49"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;"><b>P.NO</b></span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 36.9pt;" valign="top" width="49"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">P1</span></div>
</td>
</tr>
</tbody></table>
</div>
<div>
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
<div>
<span style="font-family: Verdana,sans-serif;">Now <b> </b><b> </b><b><span ; font-size: 11pt; line-height: 115%;">r<span class="apple-converted-space"> </span>÷ s</span></b><b> </b><b> </b><b> </b></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><b><br /></b></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><b>OUTPUT-</b></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><b><br /></b></span></div>
<div>
<table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: none; mso-border-alt: solid windowtext .5pt; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;">
<tbody>
<tr style="height: 13.0pt; mso-yfti-firstrow: yes; mso-yfti-irow: 0;">
<td style="border: solid windowtext 1.0pt; height: 13.0pt; mso-border-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 54.9pt;" valign="top" width="73"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;"><b>S.NO</b></span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 54.9pt;" valign="top" width="73"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">S1 </span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 54.9pt;" valign="top" width="73"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">S3</span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 54.9pt;" valign="top" width="73"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">S2</span></div>
</td>
</tr>
</tbody></table>
</div>
<div>
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
</div>
Royhttp://www.blogger.com/profile/06335373269654004062noreply@blogger.com3tag:blogger.com,1999:blog-5350834041846150717.post-18037197872463492262014-08-06T12:45:00.000-07:002015-08-05T10:49:20.787-07:00Relational Model | Relational calculas<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><b>RELATIONAL CALCULUS</b></span></div>
<br />
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;">Relational algebra is concerned with a procedural language in which the user has to write the steps or procedure to obtain the required result.In this the user is not concerned with the detail of how to obtain information.</span></div>
<span style="font-family: Verdana,sans-serif;">
</span>
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"> In relational calculus the user tell his requirement and the output is available without knowing the method about its retrieval.</span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><b>a.</b>) It is a non procedural language.</span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><b>b.) </b>This concept was first expressed by codd. It is based on the predicate calculus.</span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><b>c.) </b>It is a formal language used to symbolize local argument in mathematics.Proposition specifying a property consist of an expression that names an individual object and an other expression called the predicate,that stands for the property that an individual object possesses.</span></div>
<br />
<div style="text-align: justify;">
<b style="font-family: Verdana, sans-serif;">In relational calculus we can make the following operations</b></div>
<div style="text-align: justify;">
<b style="font-family: Verdana, sans-serif;">Example-</b><span style="font-family: Verdana, sans-serif;">Let x and y are preposition then we can build other preposition like</span></div>
<br />
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><b>"Not x"</b></span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><b>"x and y"</b></span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><b> "x or y"m and so on.</b></span></div>
<br />
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;">Consider the following statement</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;">India is a country.</span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;">We can write it.</span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;">" is a country (India)"</span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;">we can drop is a then preposition will be</span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;">" Country (India)"</span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;">Finally if we use symbols for both predicates and objects then we can rewrite the statement X (a).Lower case letter denotes the variables beginning letters {a,b,c......}denotes the constants.</span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;">Upper case letter denotes the predicate.</span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;">P{X} where X is the argument.</span></div>
<br />
<div style="text-align: justify;">
<b style="font-family: Verdana, sans-serif;">It can be categorized into 2 parts:-</b></div>
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><b>1,) Tuple Oriented Relational calculus</b></span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><b>2.) Domain Oriented Relational calculus.</b></span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
</div>
Khushboo Arorahttp://www.blogger.com/profile/15520169114094502231noreply@blogger.com0tag:blogger.com,1999:blog-5350834041846150717.post-4559251056016622892014-08-06T12:44:00.000-07:002015-08-05T10:46:26.595-07:00Relational Model | Integrity constraints over relation<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><b>INTEGRITY CONSTRAINTS OVER RELATION</b></span></div>
<br />
<div style="text-align: justify;">
<b style="font-family: Verdana, sans-serif;">INTRODUCTION</b></div>
<div style="text-align: justify;">
</div>
<div class="MsoNormal" style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;">Database
integrity refers to the validity and consistency of stored data. Integrity is
usually expressed in terms of constraints, which are consistency rules
that the database is not permitted to violate. Constraints may apply to each
attribute or they may apply to relationships between tables.</span></div>
<div class="MsoNormal" style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;">Integrity
constraints ensure that changes (update deletion, insertion) made to the
database by authorized users do not result in a loss of data consistency. Thus,
integrity constraints guard against accidental damage to the database.</span></div>
<div class="MsoNormal" style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><o:p></o:p></span></div>
<div class="MsoNormal" style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
<div class="MsoNormal" style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><b>EXAMPLE- </b>A
brood group must be ‘A’ or ‘B’ or ‘AB’ or ‘O’ only (can not any other values
else).</span></div>
<div class="MsoNormal" style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
<div class="MsoNormal" style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><b>TYPES OF INTEGRITY CONSTRAINTS</b></span></div>
<div class="MsoNormal" style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><b><br /></b></span></div>
<div class="MsoNormal" style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;">Various types of integrity constraints are-</span></div>
<ol>
<li style="text-align: justify;"><span style="font-family: Verdana,sans-serif;">Domain Integrity</span></li>
<li style="text-align: justify;"><span style="font-family: Verdana,sans-serif;">Entity Integrity Constraint</span></li>
<li style="text-align: justify;"><span style="font-family: Verdana,sans-serif;">Referential Integrity Constraint</span></li>
<li style="text-align: justify;"><span style="font-family: Verdana,sans-serif;">Key Constraints</span></li>
</ol>
<div>
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><b>1. Domain Integrity- </b><span style="color: #330033; text-align: left;">Domain integrity means the definition of a
valid set of values for an attribute. You define</span><span class="apple-converted-space" style="color: #330033; text-align: left;"> </span><span style="color: #330033; text-align: left;">data type,</span><span class="apple-converted-space" style="color: #330033; text-align: left;"> </span><span style="color: #330033; text-align: left;">length or size, </span><span style="color: #330033; text-align: left;">is null value allowed ,</span><span color:="" left="" text-align:=""> is the value unique or not</span><span style="color: #330033; text-align: left;"> </span><span color:="" left="" text-align:="">for an attribute ,</span><span style="color: #330033; text-align: left;">the default value, the range (values in
between) and/or specific values for the attribute.</span><span class="apple-converted-space" style="color: #330033; text-align: left;"> </span></span></div>
</div>
<div>
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><span class="apple-converted-space" style="color: #330033; text-align: left;"><br /></span></span></div>
</div>
<div>
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><b>2. Entity Integrity Constraint- </b>This rule states that in any database relation value of attribute of a primary key can't be null.</span></div>
</div>
<div>
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
</div>
<div>
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><b>EXAMPLE- </b>Consider a relation "STUDENT" Where "Stu_id" is a primary key and it must not contain any null value whereas other attributes may contain null value e.g "Branch" in the following relation contains one null value.</span></div>
</div>
<div>
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
</div>
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><br /></span>
</div>
<table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: none; mso-border-alt: solid windowtext .5pt; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;">
<tbody>
<tr style="height: 13.3pt; mso-yfti-firstrow: yes; mso-yfti-irow: 0;">
<td style="border: solid windowtext 1.0pt; height: 13.3pt; mso-border-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 50.6pt;" valign="top" width="67"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;"><b>Stu_id<o:p></o:p></b></span></div>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; height: 13.3pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 38.8pt;" valign="top" width="52"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;"><b>Name<o:p></o:p></b></span></div>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; height: 13.3pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 42.15pt;" valign="top" width="56"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;"><b>Branch<o:p></o:p></b></span></div>
</td>
</tr>
<tr style="height: 12.65pt; mso-yfti-irow: 1;">
<td style="border-top: none; border: solid windowtext 1.0pt; height: 12.65pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 50.6pt;" valign="top" width="67"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">11255234<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 12.65pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 38.8pt;" valign="top" width="52"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">Aman<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 12.65pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 42.15pt;" valign="top" width="56"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">CSE<o:p></o:p></span></div>
</td>
</tr>
<tr style="height: 12.65pt; mso-yfti-irow: 2;">
<td style="border-top: none; border: solid windowtext 1.0pt; height: 12.65pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 50.6pt;" valign="top" width="67"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">11255369<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 12.65pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 38.8pt;" valign="top" width="52"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">Kapil<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 12.65pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 42.15pt;" valign="top" width="56"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">ECE<o:p></o:p></span></div>
</td>
</tr>
<tr style="height: 12.65pt; mso-yfti-irow: 3;">
<td style="border-top: none; border: solid windowtext 1.0pt; height: 12.65pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 50.6pt;" valign="top" width="67"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">11255324<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 12.65pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 38.8pt;" valign="top" width="52"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">Ajay<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 12.65pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 42.15pt;" valign="top" width="56"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
</td>
</tr>
<tr style="height: 12.65pt; mso-yfti-irow: 4;">
<td style="border-top: none; border: solid windowtext 1.0pt; height: 12.65pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 50.6pt;" valign="top" width="67"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">11255237<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 12.65pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 38.8pt;" valign="top" width="52"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">Raman<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 12.65pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 42.15pt;" valign="top" width="56"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">CSE<o:p></o:p></span></div>
</td>
</tr>
<tr style="height: 3.2pt; mso-yfti-irow: 5; mso-yfti-lastrow: yes;">
<td style="border-top: none; border: solid windowtext 1.0pt; height: 3.2pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 50.6pt;" valign="top" width="67"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">11255678<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 3.2pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 38.8pt;" valign="top" width="52"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">Aastha<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 3.2pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 42.15pt;" valign="top" width="56"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">ECE<o:p></o:p></span></div>
</td>
</tr>
</tbody></table>
<div>
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
</div>
<div>
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><b>3.</b><b>Referential Integrity Constraint-</b>It states that if a foreign key exists in a relation then either the foreign key value must match a primary key value of some tuple in its home relation or the foreign key value must be null.</span></div>
</div>
<div style="text-align: justify;">
<br /></div>
<div class="MsoNormal">
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;">The rules are:</span></div>
<span style="font-family: Verdana,sans-serif;"></span><br />
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><span style="color: #330033;">1. You can't delete
a record from a primary table if matching records exist in a related table.</span></span></div>
<span style="font-family: Verdana,sans-serif;">
<span style="color: #330033;"><div style="text-align: justify;">
2. You can't change
a primary key value in the primary table if that record has related records.</div>
<div style="text-align: justify;">
3. You can't enter
a value in the foreign key field of the related table that doesn't exist in the
primary key of the primary table.</div>
</span></span></div>
<div class="MsoNormal">
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><span color:="">4. However, you can enter a Null value in the
foreign key, specifying that the records are unrelated.</span><o:p></o:p></span></div>
</div>
<div class="MsoNormal">
<span style="font-family: Verdana,sans-serif;"><b style="text-align: justify;"><br /></b></span></div>
<div class="MsoNormal">
<span style="font-family: Verdana,sans-serif;"><b style="text-align: justify;">EXAMPLE-</b><span style="text-align: justify;">Consider 2 relations "stu" and "stu_1" Where "Stu_id " is the primary key in the "stu" relation and foreign key in the "stu_1" relation.</span></span></div>
<div class="MsoNormal">
<span style="font-family: Verdana,sans-serif;"><b style="text-align: justify;"><br /></b></span></div>
<div class="MsoNormal">
<span style="font-family: Verdana,sans-serif;"><b style="text-align: justify;">Relation "stu"</b></span></div>
<div class="MsoNormal">
<span style="font-family: Verdana,sans-serif;"><b style="text-align: justify;"><br /></b></span></div>
<table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: none; mso-border-alt: solid windowtext .5pt; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;">
<tbody>
<tr style="height: 13.3pt; mso-yfti-firstrow: yes; mso-yfti-irow: 0;">
<td style="border: solid windowtext 1.0pt; height: 13.3pt; mso-border-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 50.6pt;" valign="top" width="67"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;"><b>Stu_id<o:p></o:p></b></span></div>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; height: 13.3pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 38.8pt;" valign="top" width="52"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;"><b>Name<o:p></o:p></b></span></div>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; height: 13.3pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 42.15pt;" valign="top" width="56"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;"><b>Branch<o:p></o:p></b></span></div>
</td>
</tr>
<tr style="height: 12.65pt; mso-yfti-irow: 1;">
<td style="border-top: none; border: solid windowtext 1.0pt; height: 12.65pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 50.6pt;" valign="top" width="67"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">11255234<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 12.65pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 38.8pt;" valign="top" width="52"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">Aman<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 12.65pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 42.15pt;" valign="top" width="56"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">CSE<o:p></o:p></span></div>
</td>
</tr>
<tr style="height: 12.65pt; mso-yfti-irow: 2;">
<td style="border-top: none; border: solid windowtext 1.0pt; height: 12.65pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 50.6pt;" valign="top" width="67"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">11255369<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 12.65pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 38.8pt;" valign="top" width="52"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">Kapil<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 12.65pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 42.15pt;" valign="top" width="56"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">ECE<o:p></o:p></span></div>
</td>
</tr>
<tr style="height: 12.65pt; mso-yfti-irow: 3;">
<td style="border-top: none; border: solid windowtext 1.0pt; height: 12.65pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 50.6pt;" valign="top" width="67"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">11255324<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 12.65pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 38.8pt;" valign="top" width="52"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">Ajay<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 12.65pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 42.15pt;" valign="top" width="56"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">ME<o:p></o:p></span></div>
</td>
</tr>
<tr style="height: 12.65pt; mso-yfti-irow: 4;">
<td style="border-top: none; border: solid windowtext 1.0pt; height: 12.65pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 50.6pt;" valign="top" width="67"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">11255237<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 12.65pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 38.8pt;" valign="top" width="52"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">Raman<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 12.65pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 42.15pt;" valign="top" width="56"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">CSE<o:p></o:p></span></div>
</td>
</tr>
<tr style="height: 3.2pt; mso-yfti-irow: 5; mso-yfti-lastrow: yes;">
<td style="border-top: none; border: solid windowtext 1.0pt; height: 3.2pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 50.6pt;" valign="top" width="67"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">11255678<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 3.2pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 38.8pt;" valign="top" width="52"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">Aastha<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 3.2pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 42.15pt;" valign="top" width="56"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">ECE<o:p></o:p></span></div>
</td>
</tr>
</tbody></table>
<div class="MsoNormal">
<span style="font-family: Verdana,sans-serif;"><b style="text-align: justify;"><br /></b></span></div>
<div class="MsoNormal">
<span style="font-family: Verdana,sans-serif;"><b style="text-align: justify;">Relation "stu_1"</b></span></div>
<div class="MsoNormal">
<span style="font-family: Verdana,sans-serif;"><b style="text-align: justify;"><br /></b></span></div>
<table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: none; mso-border-alt: solid windowtext .5pt; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184; width: 218px;">
<tbody>
<tr>
<td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 60.25pt;" valign="top" width="80"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;"><b>Stu_id<o:p></o:p></b></span></div>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 49.65pt;" valign="top" width="66"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;"><b>Cource<o:p></o:p></b></span></div>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 53.6pt;" valign="top" width="71"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;"><b>Duration<o:p></o:p></b></span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 60.25pt;" valign="top" width="80"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">11255234<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 49.65pt;" valign="top" width="66"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">B.TECH<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 53.6pt;" valign="top" width="71"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">4 years<o:p></o:p></span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 60.25pt;" valign="top" width="80"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">11255369<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 49.65pt;" valign="top" width="66"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">B.TECH<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 53.6pt;" valign="top" width="71"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">4 years<o:p></o:p></span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 60.25pt;" valign="top" width="80"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">11255324<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 49.65pt;" valign="top" width="66"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">B.TECH<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 53.6pt;" valign="top" width="71"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">4 years<o:p></o:p></span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 60.25pt;" valign="top" width="80"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">11255237<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 49.65pt;" valign="top" width="66"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">B.TECH<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 53.6pt;" valign="top" width="71"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">4 years<o:p></o:p></span></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 60.25pt;" valign="top" width="80"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">11255678<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 49.65pt;" valign="top" width="66"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">B.TECH<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 53.6pt;" valign="top" width="71"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana,sans-serif;">4 years<o:p></o:p></span></div>
</td>
</tr>
</tbody></table>
<div>
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
</div>
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><b style="color: #330033;">Examples</b></span></div>
<div class="MsoNormal">
<span style="font-family: Verdana,sans-serif;"></span><br />
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><b style="color: #330033;">Rule 1</b><span style="color: #330033;">. You can't
delete any of the rows in the ”stu” relation that are visible since all the ”stu” are in use in the “stu_1”
relation.</span></span></div>
<span style="font-family: Verdana,sans-serif;">
<span style="color: #330033;"><div style="text-align: justify;">
<b>Rule 2. </b>You can't
change any of the ”Stu_id” in the “stu” relation since all the “Stu_id” are
in use in the ”stu_1” relation.</div>
<div style="text-align: justify;">
<b>Rule 3</b>. The values
that you can enter in the” Stu_id” field in the “stu_1” relation must be in the” Stu_id”
field in the “stu” relation.</div>
<span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"><div style="text-align: justify;">
<b>Rule 4 </b>You can enter a null value in the "stu_1" relation if the records are unrelated.</div>
<o:p></o:p></span></span></span></div>
<div class="MsoNormal">
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><span style="color: #330033;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"><br /></span></span></span></span></div>
</div>
<div class="MsoNormal">
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><b><span style="color: #330033;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;">4.</span></span></span><span style="text-align: justify;">Key Constraints- </span></b><span color:="">A<span class="apple-converted-space"> </span></span><span style="border: 1pt none windowtext; padding: 0in;">Key Constraint<span class="apple-converted-space"> </span></span>is a statement that a certain<span class="apple-converted-space"> </span><span style="outline: 0px;"><span style="border: none windowtext 1.0pt; mso-border-alt: none windowtext 0in; padding: 0in;">minimal</span></span><span class="apple-converted-space"> subset
of the fields of a relation is a unique identifier for a tuple.</span></span></div>
</div>
<div class="MsoNormal">
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><span class="apple-converted-space">There are 4 types of key constraints-</span></span></div>
</div>
<ol style="text-align: left;">
<li style="text-align: justify;"><span style="font-family: Verdana,sans-serif;">Candidate key.</span></li>
<li style="text-align: justify;"><span style="font-family: Verdana,sans-serif;">Super key</span></li>
<li style="text-align: justify;"><span style="font-family: Verdana,sans-serif;">Primary key</span></li>
<li style="text-align: justify;"><span style="font-family: Verdana,sans-serif;">Foreign key</span></li>
</ol>
<div class="MsoNormal">
<span style="font-family: Verdana,sans-serif;"><o:p></o:p></span></div>
<div class="MsoNormal" style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><o:p></o:p></span></div>
</div>
Khushboo Arorahttp://www.blogger.com/profile/15520169114094502231noreply@blogger.com7tag:blogger.com,1999:blog-5350834041846150717.post-69041833943291372192014-08-06T12:42:00.002-07:002015-03-13T10:07:45.387-07:00Relational Model | ER to Relational model conversion<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: left;">
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
<div style="text-align: left;">
<span style="font-family: Verdana,sans-serif;"><b>ER TO RELATIONAL MODEL CONVERSION</b></span></div>
<div style="text-align: left;">
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
<div style="text-align: left;">
<span style="font-family: Verdana,sans-serif;"><span color: #4e4e54; text-align: justify;"><b>INTRODUCTION</b>- In the E-R model, data is represented using
entities, and relationships are defined between these entities. However, with
the relational model, the entities and their relationships follow strict
guidelines. Usually, an E-R model is first developed, and then it is
transformed into a relational model.</span></span></div>
<div style="text-align: left;">
<span style="font-family: Verdana,sans-serif;"><span color: #4e4e54; text-align: justify;"><br /></span></span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><span style="color: #4e4e54;"><span "><b>RULES TO CONVERT ER TO RELATIONAL MODEL CONVERSION-</b></span></span></span></div>
<ul style="text-align: justify; text-indent: -24px;">
<li><span style="font-family: Verdana,sans-serif;"><span style="text-indent: -0.25in;"><span style="font-size: 7pt;"> </span></span><span style="text-indent: -0.25in;">Build a table for each entity set</span></span></li>
<li><span style="font-family: Verdana,sans-serif;"><span style="text-indent: -0.25in;"><span style="font-size: 7pt;"> </span></span><span style="text-indent: -0.25in;">Build a table for each relationship set if necessary (more on this later)<a name='more'></a></span></span></li>
<li><span style="font-family: Verdana,sans-serif;"><span style="text-indent: -0.25in;"><span style="font-size: 7pt;"> </span></span><span style="text-indent: -0.25in;">Make a column in the table for each attribute in the entity set</span></span></li>
<li><span style="font-family: Verdana,sans-serif;"><span style="text-indent: -0.25in;"><span style="font-size: 7pt;"> </span></span><span style="text-indent: -0.25in;">Indivisibility Rule and Ordering Rule</span></span></li>
<li><span style="font-family: Verdana,sans-serif;"><span style="text-indent: -0.25in;"><span style="font-size: 7pt;"> </span></span><span style="text-indent: -0.25in;">Primary Key</span></span></li>
</ul>
<div style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><b style="text-indent: -0.25in;">EXAMPLE- </b><span color: #4e4e54;">An E-R diagram is used to represent the E-R
model. It contains all known entities for the given scenario, their attributes,
identifiers, and the relationships that exist among the entities.</span><span color: #4e4e54;">Unique identifiers are indicated with an
asterisk (*), and non-unique identifiers with a plus (+). </span></span></div>
<div class="MsoNormal" style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><o:p></o:p></span></div>
<div class="MsoNormal" style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><o:p></o:p></span></div>
<div>
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Verdana,sans-serif;"><a href="http://1.bp.blogspot.com/-GASzcKRbTbA/U7U41Umgw9I/AAAAAAAAAJI/7thibf3ODkA/s1600/KK.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/-GASzcKRbTbA/U7U41Umgw9I/AAAAAAAAAJI/7thibf3ODkA/s1600/KK.png" height="224" width="400" /></a></span></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Verdana,sans-serif;"><b>CONVERSION-</b></span></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Verdana,sans-serif;"><b><br /></b></span></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Verdana,sans-serif;"><b>STEP 1-</b><span color: #4e4e54; text-align: justify;">Clearly identify the primary key and attributes
for each entity defined in the E-R model, and ensure that it is in accordance
with the rules of the relational model.</span><span color: #4e4e54; font-size: 11pt; line-height: 12.7pt;">Each entity, with its clearly identified
primary key (indicated by PK), and attributes satisfying the previously
discussed rules, becomes a table in the relational model.</span></span></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Verdana,sans-serif;"><span color: #4e4e54; font-size: 11pt; line-height: 12.7pt;"><br /></span></span></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Verdana,sans-serif;"><a href="http://4.bp.blogspot.com/-12rqmayGGQ4/U7U63P-8nvI/AAAAAAAAAJQ/8jdZO7WKRFs/s1600/K2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/-12rqmayGGQ4/U7U63P-8nvI/AAAAAAAAAJQ/8jdZO7WKRFs/s1600/K2.png" height="87" width="400" /></a></span></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Verdana,sans-serif;"><span color: #4e4e54; font-size: 11pt; line-height: 12.7pt;"><br /></span></span></div>
<div style="background: ; line-height: 12.7pt; margin-bottom: 3.0pt; margin-left: 0in; margin-right: 0in; margin-top: 0in; vertical-align: baseline;">
<span style="font-family: Verdana,sans-serif;"><span style="color: #4e4e54; font-size: 11.0pt;"><o:p></o:p></span></span></div>
<div class="MsoNormal" style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><o:p></o:p></span></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Verdana,sans-serif;"><b>STEP 2- </b><span color: #4e4e54; font-size: 11pt; line-height: 12.7pt; text-align: justify;">Group together tables (formerly
entities) and their relationships that have a cardinality of one relationships
with 0:1 or 1:1 for their opt:card. (Optionality says what can and must happen
in a relationship, and cardinality indicates the number of entity occurrences
in a relationship)That is, absorb relationships where the cardinality is one
into the corresponding tables.</span></span></div>
<div style="background: ; line-height: 12.7pt; margin: 0in 0in 3pt; min-height: 1em; outline: 0px; vertical-align: baseline;">
<span style="font-family: Verdana,sans-serif;"><span style="color: #4e4e54; font-size: 11.0pt;">• Maintain the initial structure for the absorbing table – do
not change its primary key or any of its attributes.<o:p></o:p></span></span></div>
<div class="MsoNormal" style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Verdana,sans-serif;"><a href="http://2.bp.blogspot.com/-1MnRSzMDuPU/U7U80nFZMBI/AAAAAAAAAJY/iLit7X5rnWs/s1600/K3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-1MnRSzMDuPU/U7U80nFZMBI/AAAAAAAAAJY/iLit7X5rnWs/s1600/K3.png" height="92" width="400" /></a></span></div>
<span style="font-family: Verdana,sans-serif;"><br /></span>
<div class="MsoNormal" style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
<div style="background: ; line-height: 12.7pt; margin-bottom: 3.0pt; margin-left: 0in; margin-right: 0in; margin-top: 0in; text-align: justify; vertical-align: baseline;">
<span style="font-family: Verdana,sans-serif;"><span style="color: #4e4e54; font-size: 11.0pt;">• The primary key of the other table in
the relationship becomes a foreign key in the absorbing table. This is
indicated by an FK in the absorbing table.<o:p></o:p></span></span></div>
<div style="background: ; line-height: 12.7pt; margin: 0in 0in 3pt; min-height: 1em; outline: 0px; text-align: justify; vertical-align: baseline;">
<span style="font-family: Verdana,sans-serif;"><span style="color: #4e4e54; font-size: 11.0pt;">• If the
cardinality between the absorbed relationship and the other table in the
relationship was N (0:N or 1:N), then the modified table becomes the many part
of a one-to-many relationship in the new relational model.<o:p></o:p></span></span></div>
<div style="background: ; line-height: 12.7pt; margin: 0in 0in 3pt; min-height: 1em; outline: 0px; vertical-align: baseline;">
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Verdana,sans-serif;"><a href="http://1.bp.blogspot.com/-6obkHL4esFI/U7U9GfwSg9I/AAAAAAAAAJg/CzPUS6y5fzY/s1600/K4.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/-6obkHL4esFI/U7U9GfwSg9I/AAAAAAAAAJg/CzPUS6y5fzY/s1600/K4.png" height="77" width="400" /></a></span></div>
<span style="font-family: Verdana,sans-serif;"><o:p></o:p><br /></span>
<div style="background: ; line-height: 12.7pt; margin: 0in 0in 3pt; min-height: 1em; outline: 0px; vertical-align: baseline;">
<span style="font-family: Verdana,sans-serif;"><span style="color: #4e4e54; font-size: 11.0pt;"><br /></span></span></div>
<div style="background: ; line-height: 12.7pt; margin: 0in 0in 3pt; min-height: 1em; outline: 0px; vertical-align: baseline;">
<span style="font-family: Verdana,sans-serif;"><span style="color: #4e4e54; font-size: 11.0pt;"><br /></span></span></div>
<div style="background: ; line-height: 12.7pt; margin-bottom: 3.0pt; margin-left: 0in; margin-right: 0in; margin-top: 0in; text-align: justify; vertical-align: baseline;">
<span style="font-family: Verdana,sans-serif;"><span style="color: #4e4e54; font-size: 11.0pt;">• Otherwise, if the cardinality between
the absorbed relationship and the other table in the relationship was 1 (0:1 or
1:1), then the new relationship becomes a one-to-one relationship in the new
relational model (not shown).<o:p></o:p></span></span></div>
<div style="background: ; line-height: 12.7pt; margin: 0in 0in 3pt; min-height: 1em; outline: 0px; vertical-align: baseline;">
<span style="font-family: Verdana,sans-serif;"><span style="color: #4e4e54; font-size: 11.0pt;"><b>STEP 3-</b><o:p></o:p></span><span style="color: #4e4e54; text-align: justify;">The remaining relationships whose cardinalities
are N (1:N or 0:N) on both sides become new tables in the new relational model.</span></span></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Verdana,sans-serif;"><a href="http://1.bp.blogspot.com/-UXBP4wMPoco/U7U-i_OMVnI/AAAAAAAAAJo/bT3xzFW35Hs/s1600/K5.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/-UXBP4wMPoco/U7U-i_OMVnI/AAAAAAAAAJo/bT3xzFW35Hs/s1600/K5.png" height="91" width="400" /></a></span></div>
<div style="background: ; line-height: 12.7pt; margin: 0in 0in 3pt; min-height: 1em; outline: 0px; vertical-align: baseline;">
<span style="font-family: Verdana,sans-serif;"><span style="color: #4e4e54; text-align: justify;"><br /></span></span></div>
<div style="background: ; line-height: 12.7pt; margin-bottom: 3.0pt; margin-left: 0in; margin-right: 0in; margin-top: 0in; text-align: justify; vertical-align: baseline;">
<span style="font-family: Verdana,sans-serif;"><span style="color: #4e4e54; font-size: 11.0pt;">• The primary keys from the two tables
involved in the relationship become a composite primary key in the new table,
and the new table usually has a name that is a combined form of the two
original table names.<o:p></o:p></span></span></div>
<div style="background: ; line-height: 12.7pt; margin: 0in 0in 3pt; min-height: 1em; outline: 0px; vertical-align: baseline;">
<span style="font-family: Verdana,sans-serif;"><span style="color: #4e4e54; font-size: 11.0pt;"> •The newly created table becomes the many part of the
relationship between both tables, and thus creates a many-to-many relationship
between the two pre-existing tables. In some instances, the newly created may
have its own attributes, but this is rare.<o:p></o:p></span></span></div>
<div style="background: ; line-height: 12.7pt; margin: 0in 0in 3pt; min-height: 1em; outline: 0px; vertical-align: baseline;">
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
<div style="background: ; line-height: 12.7pt; margin-bottom: 3.0pt; margin-left: 0in; margin-right: 0in; margin-top: 0in; text-align: justify; vertical-align: baseline;">
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Verdana,sans-serif;"><a href="http://3.bp.blogspot.com/-Uf_NOO0n1ew/U7U-qIVuouI/AAAAAAAAAJw/djtd6SxWYL8/s1600/K6.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/-Uf_NOO0n1ew/U7U-qIVuouI/AAAAAAAAAJw/djtd6SxWYL8/s1600/K6.png" height="92" width="400" /></a></span></div>
<span style="font-family: Verdana,sans-serif;"><br /></span>
<div class="MsoNormal" style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"> <b>OUTPUT- </b>The required output</span></div>
<div class="MsoNormal" style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><b><br /></b></span></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Verdana,sans-serif;"><a href="http://4.bp.blogspot.com/-vXNy6NWTs4o/U7U_Jufv20I/AAAAAAAAAJ4/si2RMqenoco/s1600/K7.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/-vXNy6NWTs4o/U7U_Jufv20I/AAAAAAAAAJ4/si2RMqenoco/s1600/K7.png" height="150" width="400" /></a></span></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
<span style="font-family: Verdana,sans-serif;"><br /></span>
<div class="MsoNormal" style="text-align: justify;">
<span style="font-family: Verdana,sans-serif;"><o:p></o:p></span></div>
</div>
Khushboo Arorahttp://www.blogger.com/profile/15520169114094502231noreply@blogger.com1tag:blogger.com,1999:blog-5350834041846150717.post-9373251720917859762014-08-06T12:41:00.001-07:002015-08-05T10:42:01.052-07:00Relational Model | Key constraints | Types of key<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
<b>KEY CONSTRAINTS</b></div>
<br />
<div style="text-align: justify;">
<b>INTRODUCTION-</b><span style="color: #444444; font-family: 'Times New Roman', serif;">A<span class="apple-converted-space"> </span></span><span style="border: 1pt none windowtext; padding: 0in;">Key Constraint<span class="apple-converted-space"> </span></span>is a statement that a certain<span class="apple-converted-space"> </span><span style="outline: 0px;"><span style="border: 1pt none windowtext; padding: 0in;">minimal</span></span><span class="apple-converted-space"> subset
of the fields of a relation is a unique identifier for a tuple.</span></div>
<br />
<div style="text-align: justify;">
<b>TYPES OF KEY CONSTRAINTS- </b>Various types of key constraints are-</div>
<ol style="text-align: left;">
<li style="text-align: justify;">Candidate key</li>
<li style="text-align: justify;">Super key</li>
<li style="text-align: justify;">Primary key</li>
<li style="text-align: justify;">Foreign key</li>
</ol>
<div>
<div style="text-align: justify;">
<b>1. Candidate key- </b>These are those attributes of a relation that contains the properties of uniqueness and irr-reducability. </div>
</div>
<div>
<ul style="text-align: left;">
<li style="text-align: justify;"><b>Uniqueness- </b>At any time no two tuples of a relation should have the same value means no duplicate values. </li>
<li style="text-align: justify;"><b>Irr-reducability- </b>Let "k" be the set of attributes in any relation "R" i.e.it contains composite key then no proper subset of "k" has the uniqueness property.</li>
</ul>
</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<b>2. Super Key- </b><b> </b>These are those attributes of a relation that contains the properties of uniqueness but not necessary that it satisfies irr-reducability property.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<b>3. Primary Key- </b>Mixture of uniqueness and not null is known as the primary key.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<b>Properties of Primary key-</b></div>
<div style="text-align: justify;">
<b>1. Stable- </b>It should be stable means value must not change or it should not be null throughout the life of an entity.</div>
<div style="text-align: justify;">
<b>2. Minimal- </b>It should be minimal means it should be composite of minimum no of fields that ensures that the occurrence are unique.</div>
<div style="text-align: justify;">
<b>3. Definitive- </b>It should be definitive means a value must exist for every record at the creation time.</div>
<div style="text-align: justify;">
<b>4. Accessible-</b>It should be accessible means any one who wants to create,read or delete a record mus be able to see the primary key values.</div>
<div style="text-align: justify;">
<br /></div>
<div class="MsoNormal" style="line-height: 150%; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 9.0pt 13.5pt; text-align: justify;">
<div style="text-align: justify;">
<b><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 150%;">SYNTAX-
</span></b><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 150%;">create
table <table name> (column name1 datatype (size), column name2
datatype(size) constraint (constraint name) primary key);</span></div>
</div>
<div class="MsoNormal" style="line-height: 150%; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 9.0pt 13.5pt; text-align: justify;">
<div style="text-align: justify;">
<b><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 150%;">EG-
</span></b><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 150%;">create table
mydoc3 ( username varchar (20) ,password
number(10) constraint
rollno_pk primary key);<o:p></o:p></span></div>
</div>
<div class="separator" style="clear: both; text-align: justify;">
<a href="http://1.bp.blogspot.com/-8TnCQd3ZFa4/VcJJBrWLRyI/AAAAAAAABuc/6eU5ORZt6mc/s1600/primary.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="376" src="http://1.bp.blogspot.com/-8TnCQd3ZFa4/VcJJBrWLRyI/AAAAAAAABuc/6eU5ORZt6mc/s640/primary.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<b>4. Foreign key-</b>Primary key of one table is the foreign key of some other table.</div>
<br />
<div class="MsoNormal" style="line-height: 18.35pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<div style="text-align: justify;">
<b><span style="border: none windowtext 1.0pt; color: #444444; font-family: "Times New Roman","serif"; mso-border-alt: none windowtext 0in; mso-fareast-font-family: "Times New Roman"; padding: 0in;">Features of foreign key</span></b><span style="border: none windowtext 1.0pt; color: #444444; font-family: "Times New Roman","serif"; mso-bidi-font-weight: bold; mso-border-alt: none windowtext 0in; mso-fareast-font-family: "Times New Roman"; padding: 0in;">:</span><span style="color: #444444; font-family: "Times New Roman","serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div>
</div>
<div class="MsoNormal" style="line-height: 18.35pt; margin-bottom: .0001pt; margin-bottom: 0in;">
<div style="text-align: justify;">
<span style="color: #444444; font-family: "Times New Roman","serif"; mso-fareast-font-family: "Times New Roman";"><b>1</b>. Records cannot
be <span style="border: none windowtext 1.0pt; mso-bidi-font-weight: bold; mso-border-alt: none windowtext 0in; padding: 0in;">inserted</span> into a <span style="border: none windowtext 1.0pt; mso-bidi-font-weight: bold; mso-border-alt: none windowtext 0in; padding: 0in;">detail
table</span> if
corresponding records in the master table do not exist.</span></div>
<div style="text-align: justify;">
<span style="color: #444444; font-family: "Times New Roman","serif"; mso-fareast-font-family: "Times New Roman";"><span style="border: none windowtext 1.0pt; color: #444444; font-family: "Times New Roman","serif"; mso-border-alt: none windowtext 0in; mso-fareast-font-family: "Times New Roman"; padding: 0in;"><b>2</b>
.</span><span style="color: #444444; font-family: "Times New Roman","serif"; mso-fareast-font-family: "Times New Roman";">Records of the <span style="border: none windowtext 1.0pt; mso-bidi-font-weight: bold; mso-border-alt: none windowtext 0in; padding: 0in;">master
table</span> cannot be <span style="border: none windowtext 1.0pt; mso-bidi-font-weight: bold; mso-border-alt: none windowtext 0in; padding: 0in;">deleted</span> or <span style="border: none windowtext 1.0pt; mso-bidi-font-weight: bold; mso-border-alt: none windowtext 0in; padding: 0in;">updated</span> if corresponding records in the
detail table actually exist.</span></span></div>
<div style="text-align: justify;">
<br /></div>
<span style="color: #444444; font-family: "Times New Roman","serif"; mso-fareast-font-family: "Times New Roman";"></span>
<span style="color: #444444; font-family: "Times New Roman","serif"; mso-fareast-font-family: "Times New Roman";"></span>
<span style="color: #444444; font-family: "Times New Roman","serif"; mso-fareast-font-family: "Times New Roman";"></span></div>
<div class="MsoNormal" style="line-height: 18.35pt; margin-bottom: .0001pt; margin-bottom: 0in; text-indent: -1.0in;">
<div style="text-align: justify;">
<span style="color: #444444; font-family: "Times New Roman","serif"; mso-fareast-font-family: "Times New Roman";"></span><b><span style="font-family: 'Times New Roman', serif;"> SYNTAX- </span></b><span style="font-family: "Times New Roman","serif";">create
table <table name> (column name1 datatype (size), column name2
datatype(size) constraint (constraint name) primary key);<o:p></o:p></span></div>
</div>
<div class="MsoNormal" style="line-height: 150%; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 9.0pt 13.5pt; text-align: justify;">
<div style="text-align: justify;">
<span style="font-family: "Times New Roman","serif";">create
table <table name> (column name1 datatype (size), column name2
datatype(size) references <tablename which contain primary key < primary
key attribute>);<o:p></o:p></span></div>
</div>
<div class="MsoNormal" style="tab-stops: 9.0pt 13.5pt; text-align: justify;">
<div style="text-align: justify;">
<b><span style="font-family: 'Times New Roman', serif;"> EG- </span></b><span style="font-family: 'Times New Roman', serif;">create table
doc9 (bname varchar(20),brollno number(20) constraint vn_o primary key); <o:p></o:p></span></div>
</div>
<div style="text-align: justify;">
<br /></div>
<div class="MsoNormal" style="tab-stops: 9.0pt 13.5pt; text-align: justify;">
<div style="text-align: justify;">
<span style="font-family: 'Times New Roman', serif;">create table
doc10 (sname varchar(20),srollno number(20) references
doc9(brollno)); <o:p></o:p></span></div>
</div>
<div style="text-align: justify;">
<br /></div>
<div class="separator" style="clear: both; text-align: justify;">
<a href="http://3.bp.blogspot.com/-ex_pReAoU_I/VcJJYcTJzoI/AAAAAAAABuk/lFeesxwXuII/s1600/foreign2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="426" src="http://3.bp.blogspot.com/-ex_pReAoU_I/VcJJYcTJzoI/AAAAAAAABuk/lFeesxwXuII/s640/foreign2.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Times New Roman","serif";"><o:p></o:p></span></div>
</div>
Khushboo Arorahttp://www.blogger.com/profile/15520169114094502231noreply@blogger.com0tag:blogger.com,1999:blog-5350834041846150717.post-67317131931226575952014-08-06T12:40:00.000-07:002015-08-05T10:31:50.379-07:00Relational Query Languages| Basic sql query language<div dir="ltr" style="text-align: left;" trbidi="on">
<b>BASIC SQL QUERY LANGUAGE</b><br />
<b><br /></b>
<b>INTRODUCTION-</b><br />
<ul style="text-align: left;">
<li>SQL (Structured Query Language) is a special-purpose programming language designed for managing data held in a relational database management system (RDBMS).</li>
<li>SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standards (ISO) in 1987. </li>
</ul>
<br />
<div>
<b>WHAT CAN SQL DO?</b></div>
<div>
<b><br /></b></div>
<div>
<div style="text-align: left;">
•<span class="Apple-tab-span" style="white-space: pre;"> </span>SQL can execute queries against a database<br />
•<span class="Apple-tab-span" style="white-space: pre;"> </span>SQL can retrieve data from a database<br />
•<span class="Apple-tab-span" style="white-space: pre;"> </span>SQL can insert records in a database<br />
•<span class="Apple-tab-span" style="white-space: pre;"> </span>SQL can update records in a database<br />
<br />
•<span class="Apple-tab-span" style="white-space: pre;"> </span>SQL can delete records from a database<br />
•<span class="Apple-tab-span" style="white-space: pre;"> </span>SQL can create new databases<br />
•<span class="Apple-tab-span" style="white-space: pre;"> </span>SQL can create new tables in a database<br />
• SQL can create stored procedures in a database<br />
• SQL can create views in a database<br />
•<span class="Apple-tab-span" style="white-space: pre;"> </span>SQL can set permissions on tables, procedures, and views</div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
<div>
<b>SQL LANGUAGES</b></div>
<div>
<b><br /></b></div>
<div>
1. Data Definition Language (DDL)</div>
<div>
2. Data Manipulation Language (DML)</div>
<div>
3. Data Control Language (DCL)</div>
<div>
<br /></div>
<div>
<b>1)</b> <b>DATA DEFINITION LANGUAGE- </b>The Data Definition Language (DDL) manages table and index structure.</div>
</div>
<div>
<b>COMMAND USED</b>- CREATE, ALTER, DROP,RENAME</div>
<div>
<b>CREATE- </b>creates an object (a table) in the database.</div>
<div>
<b>ALTER </b>modifies the structure of an existing object in various ways, for example, adding a column to an existing table or a constraint.</div>
<div>
<b>DROP</b> deletes an object in the database, usually irretrievably, i.e., it cannot be rolled back.</div>
<div>
<b>RENAME</b> rename scheme object</div>
</div>
<div>
<br /></div>
<div>
<div style="margin: 0in 0in 0.0001pt; text-align: justify; text-indent: 0in;">
<span style="line-height: 24px;"><b>2) DATA MANIPULATION LANGUAGE- </b></span><span style="line-height: 24px; text-indent: 0in;">The Data Manipulation Language (DML) is the subset of SQL used to add, update,select and delete data.</span></div>
<div style="margin: 0in 0in 0.0001pt; text-align: justify; text-indent: 0in;">
<b style="line-height: 24px; text-indent: 0in;">COMMAND USED-</b><span style="line-height: 24px; text-indent: 0in;">INSERT, UPDATE, DELETE,SELECT</span></div>
<div style="margin: 0in 0in 0.0001pt; text-align: justify; text-indent: 0in;">
<span style="line-height: 24px;"><b>INSERT </b>adds rows (formally tuples) to an existing table.</span></div>
<div style="margin: 0in 0in 0.0001pt; text-align: justify; text-indent: 0in;">
<span style="line-height: 24px;"><b>UPDATE </b>modifies a set of existing table rows.</span></div>
<div style="margin: 0in 0in 0.0001pt; text-align: justify; text-indent: 0in;">
<b style="line-height: 24px; text-indent: 0in;">DELETE </b><span style="line-height: 24px; text-indent: 0in;">removes existing rows from a table.</span></div>
<div style="margin: 0in 0in 0.0001pt; text-align: justify; text-indent: 0in;">
<span style="line-height: 24px;"><b>SELECT </b>Retrieve data from one or more table</span></div>
<div style="margin: 0in 0in 0.0001pt; text-align: justify; text-indent: 0in;">
<span style="line-height: 24px;"><br /></span></div>
<div style="margin: 0in 0in 0.0001pt; text-align: justify; text-indent: 0in;">
<span style="line-height: 24px;"><b>3) DATA CONTROL LANGUAGE-</b></span><span style="line-height: 24px; text-indent: 0in;">The Data Control Language (DCL) authorizes users to access and manipulate data. </span></div>
<div style="margin: 0in 0in 0.0001pt; text-align: justify; text-indent: 0in;">
<span style="line-height: 24px;"><b>COMMAND USED</b>- GRANT, REVOKE, COMMENT</span></div>
<div style="margin: 0in 0in 0.0001pt; text-align: justify; text-indent: 0in;">
<span style="line-height: 24px;"><b>GRANT</b> authorizes one or more users to perform an operation or a set of operations on an object.</span></div>
<div style="margin: 0in 0in 0.0001pt; text-align: justify; text-indent: 0in;">
<span style="line-height: 24px;"><b>REVOKE</b> eliminates a grant, which may be the default grant.</span></div>
<div style="margin: 0in 0in 0.0001pt; text-align: justify; text-indent: 0in;">
</div>
<div style="margin: 0in 0in 0.0001pt; text-align: justify; text-indent: 0in;">
<span style="line-height: 24px;"><b>COMMENT</b> Adding the element into data dictionary</span></div>
</div>
<div class="MsoNormal">
<br /></div>
</div>
Khushboo Arorahttp://www.blogger.com/profile/15520169114094502231noreply@blogger.com0tag:blogger.com,1999:blog-5350834041846150717.post-69747719076378955922014-08-06T12:38:00.002-07:002015-08-05T10:29:56.258-07:00Relational Query Languages | How to create table in sql<div dir="ltr" style="text-align: left;" trbidi="on">
<b>CREATING TABLES AND VIEWS</b><br />
<ul style="text-align: left;">
<li><b>CREATING TABLES</b></li>
</ul>
<span style="font-family: 'Times New Roman', serif; font-size: 14pt; font-weight: bold; line-height: 115%; text-indent: 0in;">CREATE:</span><span style="font-family: 'Times New Roman', serif; font-size: 18pt; font-weight: bold; line-height: 115%; text-indent: 0in;"> </span><span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%; text-indent: 0in;">This command is
used when we want to create a new table.</span><br />
<div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<span style="font-family: 'Times New Roman', serif; font-size: 14pt; line-height: 115%; text-transform: uppercase;"><b>syntax:</b></span><b><span style="font-family: "Times New Roman","serif"; font-size: 14.0pt; line-height: 115%;"> </span></b><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 115%;">create table
<tname> (col.1 data type (size)
constraint <constraint name> not null , col.2 data type (size));</span><b><span style="font-family: "Times New Roman","serif"; font-size: 14.0pt; line-height: 115%;"><o:p></o:p></span></b></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%; text-transform: uppercase;"><b>E.g.:</b></span><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 115%;">
create table mydoc1 (username varchar (20) constraint name_mn not null,
password number(10));<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 115%;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-kAldJRkYhEc/VcJH-lJD7GI/AAAAAAAABuQ/91AtG_CcbE0/s1600/create.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="334" src="http://4.bp.blogspot.com/-kAldJRkYhEc/VcJH-lJD7GI/AAAAAAAABuQ/91AtG_CcbE0/s640/create.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
</div>
<ul style="text-align: left;">
<li><b>VIEWS</b></li>
</ul>
<div>
We can use views to display the particular values from a table as separate sub table. for example a table emp details has 8 fields and we can build a view with 3 fields from the table.</div>
<br />
<div class="separator" style="clear: both; text-align: left;">
<b>SYNTAX- </b></div>
<div class="separator" style="clear: both; text-align: left;">
</div>
<ul style="text-align: left;">
<li><b>For creating a view -c</b>reate or replace view <view name> as <basic query for retrieval> with read only;</li>
<li><b>For dropping a view</b>- Drop view view name;</li>
</ul>
</div>
</div>
Khushboo Arorahttp://www.blogger.com/profile/15520169114094502231noreply@blogger.com0tag:blogger.com,1999:blog-5350834041846150717.post-38657566708296218402014-08-06T12:37:00.002-07:002015-08-05T10:26:15.871-07:00Relational Query Languages |Data Manipulation Language(DML)<div dir="ltr" style="text-align: left;" trbidi="on">
<b>DML (Data Manipulation Language)</b><br />
<br />
<div style="text-align: justify;">
<span style="line-height: 24px;">The Data Manipulation Language (DML) is the subset of SQL used to add, update and delete data and select or retrieval.</span></div>
<div style="text-align: justify;">
<span style="line-height: 24px;"><br /></span></div>
<div style="text-align: justify;">
<div>
<b>COMMAND USED</b>-INSERT, UPDATE, DELETE,SELECT</div>
<div>
<b>INSERT</b> adds rows (formally tuples) to an existing table.</div>
<div>
<b>UPDATE </b>modifies a set of existing table rows.</div>
<div>
<b>DELETE</b> removes existing rows from a table.</div>
<div>
<b>SELECT </b>Retrieve data from one or more table</div>
<div>
<br /></div>
<div>
<b>1. INSERT COMMAND- </b>This command is used when we want to insert a data in a new existing table.This syntax is used for inserting a single value in a each column of a table at a time.</div>
<div>
<div>
<b>SYNTAX:</b> insert into <tname> (col1,col2,col3) values (‘v1’, ‘v2’ , ‘v3’);</div>
<div>
<b>E.G.</b>: insert into hotel (ename , salary, place) values (‘Akash’, ‘10000’, ‘chd’);</div>
<div>
<br /></div>
<div>
<b>When we want to insert a multiple values in each column of a existing table then we used a following syntax:</b></div>
<div>
<b>SYNTAX:</b> insert into <tname> values(‘&1’, ‘&2’);</div>
<div>
You have to enter the values and inspite of writing this command again you can use only [(/) enter].</div>
<div>
<br /></div>
<div>
<b>2.UPDATE COMMAND- </b><span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%; text-indent: 0in;">This command is used when we want to
update a data of existing table.</span></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif"; font-size: 14.0pt; line-height: 115%; text-transform: uppercase;">syntax</span></b><b><span style="font-family: "Times New Roman","serif"; font-size: 16.0pt; line-height: 115%; text-transform: uppercase;">:</span></b><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 115%;">
update <table name> set <condition1> where <condition2>;</span><b><span style="font-family: "Times New Roman","serif"; font-size: 16.0pt; line-height: 115%;"><o:p></o:p></span></b></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 115%; text-transform: uppercase;">E.g.:</span></b><b><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 115%;"> </span></b><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 115%;">update emp set
<id=525> where name=’raman’ ;<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 115%;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-_9Am5yVbIEk/VcJGh6ZKXAI/AAAAAAAABt8/wdA1c3W9dR0/s1600/update.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="494" src="http://3.bp.blogspot.com/-_9Am5yVbIEk/VcJGh6ZKXAI/AAAAAAAABt8/wdA1c3W9dR0/s640/update.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<b>3.DELETE COMMAND-</b><span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%; text-align: justify; text-indent: 0in;">When we want to delete the data from the
existing table.</span></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif"; font-size: 14.0pt; line-height: 115%; text-transform: uppercase;">syntax</span></b><b><span style="font-family: "Times New Roman","serif"; font-size: 16.0pt; line-height: 115%; text-transform: uppercase;">:</span></b><b><span style="font-family: "Times New Roman","serif"; font-size: 16.0pt; line-height: 115%;"> </span></b><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 115%;">delete <table name> where <condition>;</span><b><span style="font-family: "Times New Roman","serif"; font-size: 16.0pt; line-height: 115%;"><o:p></o:p></span></b></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 115%; text-transform: uppercase;">E.g.:</span></b><b><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 115%;"> </span></b><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 115%;">delete emp where name = (‘pahul’);<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 115%;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-fyvNh-EODsw/VcJGzR6M9JI/AAAAAAAABuE/lnfXNFZyvfc/s1600/pahul.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="516" src="http://2.bp.blogspot.com/-fyvNh-EODsw/VcJGzR6M9JI/AAAAAAAABuE/lnfXNFZyvfc/s640/pahul.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 115%;"><br /></span></div>
<div class="separator" style="clear: both; text-align: left;">
<b>4. SELECT COMMAND- </b><span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%; text-align: justify; text-indent: 0in;">Select
statement is used when we want to retrieve a data from a existing table.</span></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif"; font-size: 14.0pt; line-height: 115%; text-transform: uppercase;">syntax:</span></b><b><span style="font-family: "Times New Roman","serif"; font-size: 14.0pt; line-height: 115%;">
</span></b><span style="font-family: "Times New Roman","serif"; font-size: 16.0pt; line-height: 115%;"> </span><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 115%;">select
* from <table name>;</span><b><span style="font-family: "Times New Roman","serif"; font-size: 14.0pt; line-height: 115%;"><o:p></o:p></span></b></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 115%; text-transform: uppercase;">E.g</span></b><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 115%; text-transform: uppercase;">.</span><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 115%;"> select * from emp</span><span style="font-family: "Times New Roman","serif"; font-size: 16.0pt; line-height: 115%;">;<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 115%;"><b>When
we want to retrieve a specific column from a existing table then we use a
another syntax.</b><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif"; font-size: 14.0pt; line-height: 115%; text-transform: uppercase;">syntax</span></b><b><span style="font-family: "Times New Roman","serif"; font-size: 16.0pt; line-height: 115%; text-transform: uppercase;">:</span></b><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 115%;">
select <colname1> ,<colname2> from <tname>;</span><span style="font-family: "Times New Roman","serif"; font-size: 16.0pt; line-height: 115%;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 115%; text-transform: uppercase;">E.g.:</span></b><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 115%;">
select name , rollno. from emp;<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 115%;"><b>When
we want to retrieve a specific row from the existing table then we use
following syntax:</b><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif"; font-size: 14.0pt; line-height: 115%; text-transform: uppercase;">syntax:</span></b><b><span style="font-family: "Times New Roman","serif"; font-size: 14.0pt; line-height: 115%;"> </span></b><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 115%;">select * from
<table name> where <condition>;</span><b><span style="font-family: "Times New Roman","serif"; font-size: 14.0pt; line-height: 115%;"><o:p></o:p></span></b></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 115%; text-transform: uppercase;">E.g.:</span></b><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 115%;">
select * from emp where depno. = 10;<o:p></o:p></span></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="MsoListParagraphCxSpFirst" style="line-height: 115%; margin-left: 0in; text-indent: 0in;">
<!--[if !supportLists]--><span style="font-family: Symbol; font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;">·<span style="font-family: 'Times New Roman'; font-size: 7pt; line-height: normal;"> </span></span><span style="font-family: Symbol; font-size: 12pt; line-height: 115%; text-indent: 0in;"><span style="font-family: 'Times New Roman'; font-size: 7pt; line-height: normal;"> </span></span><b style="line-height: 115%; text-indent: 0in;"><span style="font-family: "Times New Roman","serif";">Arithmetic operation perform on a
table using select command</span></b></div>
<div class="MsoListParagraph" style="line-height: 115%; margin-left: 0in; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif";"><o:p></o:p></span></b></div>
<div class="MsoListParagraphCxSpFirst" style="line-height: 115%; margin-left: 0in; text-indent: 0in;">
<!--[if !supportLists]--><span style="font-family: Wingdings; font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: Wingdings; mso-fareast-font-family: Wingdings;">Ø<span style="font-family: 'Times New Roman'; font-size: 7pt; line-height: normal;"> </span></span><!--[endif]--><b><span style="font-family: "Times New Roman","serif";">Add
(+):</span></b><span style="font-family: "Times New Roman","serif";"> This is
used when we want to add some more content in any row of the existing table.<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpLast" style="line-height: 115%; margin-left: 0in; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif"; text-transform: uppercase;">syntax:</span></b><span style="font-family: "Times New Roman","serif";">
select <condition> from <table name>;<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif"; text-transform: uppercase;">E.g.:</span></b><span style="font-family: "Times New Roman","serif";">
select sal+1000 from emp;<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpFirst" style="line-height: 115%; margin-left: 0in; text-indent: 0in;">
<!--[if !supportLists]--><span style="font-family: Wingdings; font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: Wingdings; mso-fareast-font-family: Wingdings;">Ø<span style="font-family: 'Times New Roman'; font-size: 7pt; line-height: normal;"> </span></span><!--[endif]--><b><span style="font-family: "Times New Roman","serif";">Between
</span></b><span style="font-family: "Times New Roman","serif";">: This command
is used when we want to retrieve a data in some particular range.<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpLast" style="line-height: 115%; margin-left: 0in; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif";">SYNTAX-
</span></b><span style="font-family: "Times New Roman","serif";">select * from
<table name> where
<condition>;<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif"; text-transform: uppercase;">E.g.:</span></b><b><span style="font-family: "Times New Roman","serif";"> </span></b><span style="font-family: "Times New Roman","serif";">select * from dept where deptno..
between 10 and 30;<b><o:p></o:p></b></span></div>
<div class="MsoListParagraphCxSpFirst" style="line-height: 115%; margin-left: 0in; text-indent: 0in;">
<!--[if !supportLists]--><span style="font-family: Wingdings; font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: Wingdings; mso-fareast-font-family: Wingdings;">Ø<span style="font-family: 'Times New Roman'; font-size: 7pt; line-height: normal;"> </span></span><!--[endif]--><b><span style="font-family: "Times New Roman","serif";">In:
</span></b><span style="font-family: "Times New Roman","serif";">This command is
used when we want to retrieve a particular data.<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpLast" style="line-height: 115%; margin-left: 0in; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif";">SYNTAX-</span></b><span style="font-family: "Times New Roman","serif";"> select * from <table name> where
<condition>;<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif"; text-transform: uppercase;">E.g.:</span></b><b><span style="font-family: "Times New Roman","serif";"> </span></b><span style="font-family: "Times New Roman","serif";">select * from dept where deptno.
IN(10,20,30);<b><o:p></o:p></b></span></div>
<div class="MsoListParagraphCxSpFirst" style="line-height: 115%; margin-left: 0in; text-indent: 0in;">
<!--[if !supportLists]--><span style="font-family: Wingdings; font-size: 14.0pt; line-height: 115%; mso-bidi-font-family: Wingdings; mso-fareast-font-family: Wingdings;">Ø<span style="font-family: 'Times New Roman'; font-size: 7pt; line-height: normal;"> </span></span><!--[endif]--><b><span style="font-family: "Times New Roman","serif";">Null:</span></b><span style="font-family: "Times New Roman","serif";"> This command is used when we
want to select a null value from the existing table.<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpLast" style="line-height: 115%; margin-left: 0in; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif";">SYNTAX-</span></b><span style="font-family: "Times New Roman","serif";"> select*from where <column
name> is null;<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif";">E.g.:</span></b><span style="font-family: "Times New Roman","serif";"> select * from where dname is
null;<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<br /></div>
<div class="MsoListParagraphCxSpFirst" style="line-height: 115%; margin-left: 0in; text-indent: 0in;">
<!--[if !supportLists]--><span style="font-family: Symbol; font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;">·<span style="font-family: 'Times New Roman'; font-size: 7pt; line-height: normal;">
</span></span><!--[endif]--><b><span style="font-family: "Times New Roman","serif";">Logic condition using select command.<o:p></o:p></span></b></div>
<div class="MsoListParagraphCxSpLast" style="line-height: 150%; margin-left: 0in; text-indent: 0in;">
<!--[if !supportLists]--><span style="font-family: Wingdings; font-size: 12.0pt; line-height: 150%; mso-bidi-font-family: Wingdings; mso-fareast-font-family: Wingdings;">Ø<span style="font-family: 'Times New Roman'; font-size: 7pt; line-height: normal;"> </span></span><!--[endif]--><b><span style="font-family: "Times New Roman","serif";">AND
</span></b><span style="font-family: "Times New Roman","serif";"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 150%; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif";">SYNTAX-</span></b><span style="font-family: "Times New Roman","serif";">select * from <table name>
where <condition1> <b>and </b><condition2>;</span></div>
<div class="MsoListParagraphCxSpFirst" style="line-height: 150%; margin-left: 0in; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif";">EG-
</span></b><span style="font-family: "Times New Roman","serif";">select * from
dept where deptno= 10 <b>and</b> dname=
‘sales’;<b><o:p></o:p></b></span></div>
<div class="MsoListParagraphCxSpLast" style="line-height: 150%; margin-left: 0in; text-indent: 0in;">
<!--[if !supportLists]--><span style="font-family: Wingdings; font-size: 12.0pt; line-height: 150%; mso-bidi-font-family: Wingdings; mso-fareast-font-family: Wingdings;">Ø<span style="font-family: 'Times New Roman'; font-size: 7pt; line-height: normal;"> </span></span><!--[endif]--><b><span style="font-family: "Times New Roman","serif";">OR</span></b><span style="font-family: "Times New Roman","serif";"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 150%; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif";">SYNTAX-</span></b><span style="font-family: "Times New Roman","serif";"> select * from <table name>
where <condition1 > <b>or </b><condition2
>;<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpFirst" style="line-height: 150%; margin-left: 0in; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif";">EG-
</span></b><span style="font-family: "Times New Roman","serif";">select * from
dept where deptno.=10 <b>or</b> dname =
‘sales’;<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle" style="line-height: 150%; margin-left: 0in; text-indent: 0in;">
<!--[if !supportLists]--><span style="font-family: Wingdings; font-size: 12.0pt; line-height: 150%; mso-bidi-font-family: Wingdings; mso-fareast-font-family: Wingdings;">Ø<span style="font-family: 'Times New Roman'; font-size: 7pt; line-height: normal;"> </span></span><!--[endif]--><b><span style="font-family: "Times New Roman","serif";">NOT</span></b><span style="font-family: "Times New Roman","serif";"><o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle" style="line-height: 150%; margin-left: 0in; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif";">SYNTAX-
</span></b><span style="font-family: "Times New Roman","serif";">select*from <table
name> where <condition>;<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle" style="line-height: 150%; margin-left: 0in; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif";">EG-</span></b><span style="font-family: "Times New Roman","serif";"> select * from dept where deptno <b>not</b>
in (10,20,30);<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle" style="line-height: 115%; margin-left: 0in; text-indent: 0in;">
<br /></div>
<div class="MsoListParagraphCxSpMiddle" style="line-height: 115%; margin-left: 0in; text-indent: 0in;">
<!--[if !supportLists]--><span style="font-family: Symbol; font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;">·<span style="font-family: 'Times New Roman'; font-size: 7pt; line-height: normal;">
</span></span><!--[endif]--><b><span style="font-family: "Times New Roman","serif";">Sorting commands for table<o:p></o:p></span></b></div>
<div class="MsoListParagraphCxSpLast" style="line-height: 115%; margin-left: 0in; text-indent: 0in;">
<!--[if !supportLists]--><span style="font-family: Wingdings; font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: Wingdings; mso-fareast-font-family: Wingdings;">Ø<span style="font-family: 'Times New Roman'; font-size: 7pt; line-height: normal;"> </span></span><!--[endif]--><b><span style="font-family: "Times New Roman","serif";">Ascending
order:<o:p></o:p></span></b></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif"; text-transform: uppercase;">syntax:</span></b><span style="font-family: "Times New Roman","serif";">
select * from <tname> order by <col name> ;<b><o:p></o:p></b></span></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif"; text-transform: uppercase;">E.g:</span></b><span style="font-family: "Times New Roman","serif";">
select * from dept order by depno;<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpFirst" style="line-height: 115%; margin-left: 0in; text-indent: 0in;">
<!--[if !supportLists]--><span style="font-family: Wingdings; font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: Wingdings; mso-fareast-font-family: Wingdings;">Ø<span style="font-family: 'Times New Roman'; font-size: 7pt; line-height: normal;"> </span></span><!--[endif]--><b><span style="font-family: "Times New Roman","serif";">Descending
order: <o:p></o:p></span></b></div>
<div class="MsoListParagraphCxSpLast" style="line-height: 115%; margin-left: 0in; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif"; text-transform: uppercase;">syntax:</span></b><span style="font-family: "Times New Roman","serif";">
select * from <tname> order by <col name> desc;<b><o:p></o:p></b></span></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif"; text-transform: uppercase;">E.g.:</span></b><span style="font-family: "Times New Roman","serif";">
select * from dept order by depno dese;<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle" style="line-height: 115%; margin-left: 0in; text-indent: 0in;">
</div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<br /></div>
</div>
</div>
</div>
Khushboo Arorahttp://www.blogger.com/profile/15520169114094502231noreply@blogger.com0tag:blogger.com,1999:blog-5350834041846150717.post-12898041333197672022014-08-06T12:36:00.000-07:002015-08-05T10:20:20.874-07:00Relational Query Languages | Data Definition language(DDL)<div dir="ltr" style="text-align: left;" trbidi="on">
<b>DDL(Data Definition Language)</b><br />
<br />
The Data Definition Language (DDL) manages table and index structure.<br />
<b>COMMAND USED- </b>CREATE, ALTER, DROP,RENAME<br />
<b>CREATE </b>creates an object (a table) in the database.<br />
<b>ALTER</b> modifies the structure of an existing object in various ways, for example, adding a column to an existing table or a constraint.<br />
<b>DROP</b> deletes an object in the database, usually irretrievably, i.e., it cannot be rolled back.<br />
<b>RENAME</b> rename scheme object<br />
<div>
<br /></div>
<div>
<b>1. CREATE COMMAND-</b><span style="font-family: 'Times New Roman', serif; line-height: 115%; text-indent: 0in;">This
command is used when we want to create a new table.</span></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif"; text-transform: uppercase;">syntax:</span></b><b><span style="font-family: "Times New Roman","serif";"> </span></b><span style="font-family: "Times New Roman","serif";">create table <tname> (col.1 data type (size) constraint
<constraint name> not null , col.2 data type (size));<b><o:p></o:p></b></span></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif"; text-transform: uppercase;">E.g.:</span></b><span style="font-family: "Times New Roman","serif";">
create table mydoc1 (username varchar (20) constraint name_mn not null,
password number(10));<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-ruxHDChFm-g/VcJFXNU6ouI/AAAAAAAABto/qZOb2Lbrra4/s1600/1fh.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="334" src="http://2.bp.blogspot.com/-ruxHDChFm-g/VcJFXNU6ouI/AAAAAAAABto/qZOb2Lbrra4/s640/1fh.png" width="640" /></a></div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<span style="font-family: "Times New Roman","serif";"><br /></span></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<span style="font-family: "Times New Roman","serif";"><b>2. ALTER COMMAND- </b></span><span style="font-family: 'Times New Roman', serif; line-height: 115%; text-indent: 0in;">This
command is used when we want to delete a particular column from a existing
table.</span></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<span style="font-family: "Times New Roman","serif";">We use three clauses with alter
command:<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<span style="font-family: "Times New Roman","serif";">1. ADD<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<span style="font-family: "Times New Roman","serif";">2. MODIFY<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<span style="font-family: "Times New Roman","serif";">3. DROP<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<br /></div>
<div class="MsoListParagraph" style="line-height: 115%; margin-left: 0in; mso-add-space: auto; mso-list: l1 level1 lfo2; tab-stops: 9.0pt .25in; text-indent: 0in;">
<!--[if !supportLists]--><span style="font-family: Symbol; font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;">·<span style="font-family: 'Times New Roman'; font-size: 7pt; line-height: normal;"> </span></span><!--[endif]--><b><span style="font-family: "Times New Roman","serif";">ADD:</span></b><span style="font-family: "Times New Roman","serif";"> This command is used when we
want to create a new column.<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif"; text-transform: uppercase;">syntax</span></b><span style="font-family: "Times New Roman","serif"; text-transform: uppercase;">:</span><span style="font-family: "Times New Roman","serif";">
alter table tname add (col. name data type (size));<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 115%; tab-stops: .25in; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif"; text-transform: uppercase;">E.g.:</span></b><span style="font-family: "Times New Roman","serif";">
alter table student add (Roll no. number(100));<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 115%; tab-stops: .25in; text-indent: 0in;">
<br /></div>
<div class="MsoListParagraph" style="line-height: 115%; margin-left: 0in; mso-add-space: auto; mso-list: l1 level1 lfo2; tab-stops: 9.0pt 13.5pt .25in; text-indent: 0in;">
<!--[if !supportLists]--><span style="font-family: Symbol; font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;">·<span style="font-family: 'Times New Roman'; font-size: 7pt; line-height: normal;"> </span></span><!--[endif]--><b><span style="font-family: "Times New Roman","serif";">MODIFY:</span></b><span style="font-family: "Times New Roman","serif";"> This command is used when we
want to modify the table.<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 115%; tab-stops: .25in 141.0pt; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif"; text-transform: uppercase;">syntax:</span></b><b><span style="font-family: "Times New Roman","serif";"> </span></b><span style="font-family: "Times New Roman","serif";">alter table tname modify (col.
name data type(size));<b><o:p></o:p></b></span></div>
<div class="MsoNormal" style="line-height: 115%; tab-stops: .25in; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif"; text-transform: uppercase;">E.g.:</span></b><span style="font-family: "Times New Roman","serif";">
alter table emp modify (Branch
varchar(20));<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 115%; tab-stops: .25in; text-indent: 0in;">
<br /></div>
<div class="MsoListParagraph" style="margin-left: 0in; text-indent: 0in;">
<b><span style="font-family: Symbol;"><span style="line-height: 18.399999618530273px;">3.</span></span><span style="font-family: 'Times New Roman'; font-size: 7pt; line-height: normal;"> </span><!--[endif]--><span style="line-height: 115%;"><span style="font-family: "Times New Roman","serif";">DROP COMMAND-</span></span></b><span style="font-family: 'Times New Roman', serif; line-height: 115%;"> This command is used when we
want to drop a table.<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 115%; tab-stops: .25in; text-indent: 0in;">
<b><span style="font-family: "Times New Roman","serif"; text-transform: uppercase;">syntax:</span></b><b><span style="font-family: "Times New Roman","serif";"> </span></b><span style="font-family: "Times New Roman","serif";">alter table tname drop column
(col. name);<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 115%; tab-stops: .25in; text-indent: 0in;">
<b style="line-height: 115%; text-indent: 0in;"><span style="font-family: "Times New Roman","serif"; text-transform: uppercase;">E.g.:</span></b><span style="font-family: 'Times New Roman', serif; line-height: 115%; text-indent: 0in;">
alter table emp drop column salary; </span></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<span style="font-family: "Times New Roman","serif";"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-sv00jVnFRNs/VcJFt8RP3wI/AAAAAAAABtw/28No9l0V-tQ/s1600/drop.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="390" src="http://1.bp.blogspot.com/-sv00jVnFRNs/VcJFt8RP3wI/AAAAAAAABtw/28No9l0V-tQ/s640/drop.png" width="640" /></a></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<span style="font-family: "Times New Roman","serif";"><br /></span></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<span style="font-family: "Times New Roman","serif";"><b>4.RENAME COMMAND-</b>R</span>ename scheme object</div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<b>SYNTAX- </b>alter table <tname> RENAME COLUMN (old column name to new column name);</div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<span style="font-family: Times New Roman, serif;"><b>E.G</b>.- alter table emp RENAME COLUMN (name to empname);</span></div>
<div class="MsoNormal" style="line-height: 115%; text-indent: 0in;">
<br /></div>
</div>
Khushboo Arorahttp://www.blogger.com/profile/15520169114094502231noreply@blogger.com0tag:blogger.com,1999:blog-5350834041846150717.post-73046039701096462262014-08-06T12:34:00.000-07:002015-08-05T10:15:06.768-07:00Relational Query Languages | Data control language (DCL)<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
<b>DCL(Data Control Language)</b></div>
<br />
<div style="text-align: justify;">
The Data Control Language (DCL) authorizes users to access and manipulate data. </div>
<br />
<div style="text-align: justify;">
<b>COMMAND USED</b>- GRANT, REVOKE, COMMENT</div>
<div style="text-align: justify;">
<b>GRANT</b> authorizes one or more users to perform an operation or a set of operations on an object.</div>
<div style="text-align: justify;">
<b>REVOKE</b> eliminates a grant, which may be the default grant.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<b>COMMENT </b>Adding the element into data dictionary</div>
<div>
<div style="text-align: justify;">
<br /></div>
</div>
<div>
<div style="text-align: justify;">
<b>1.GRANT COMMAND- </b>It authorizes one or more users to perform an operation or a set of operations on an object.</div>
</div>
<div>
<div style="text-align: justify;">
<b>2.REVOKE COMMAND-</b>It eliminates a grant, which may be the default grant.</div>
</div>
<div>
<div style="text-align: justify;">
<br /></div>
</div>
<div>
<div style="text-align: justify;">
<b>EXAMPLE OF GRANT AND REVOKE COMMAND-</b></div>
</div>
<div>
<ul style="text-align: left;">
<li style="text-align: justify;">Suppose that DBA(Data base administer) creates four accounts A1,A2,A3 and A4 and wants only A1 to be able to create base relations, then the DBA must issue the following GRANT command in SQL. </li>
</ul>
</div>
<div>
<div style="text-align: justify;">
<b>GRANT CREATE TABLE TO A1;</b></div>
</div>
<div>
<ul style="text-align: left;">
<li style="text-align: justify;">Suppose that A1 creates two base relations EMPLOYEE AND DEPARTMENT.Hence A1 is then the owner of these two relations.<b> </b></li>
</ul>
<div>
<div style="text-align: justify;">
<b>EMPLOYEE RELATION</b><b> </b></div>
</div>
<table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: none; mso-border-alt: solid windowtext .5pt; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;">
<tbody>
<tr>
<td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 79.8pt;" valign="top" width="106"><div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="font-family: "Times New Roman","serif";">NAME<o:p></o:p></span></div>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 79.8pt;" valign="top" width="106"><div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="font-family: "Times New Roman","serif";">SSN<o:p></o:p></span></div>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 79.8pt;" valign="top" width="106"><div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="font-family: "Times New Roman","serif";">BDATE<o:p></o:p></span></div>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 79.8pt;" valign="top" width="106"><div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="font-family: "Times New Roman","serif";">ADDRESS<o:p></o:p></span></div>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 79.8pt;" valign="top" width="106"><div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="font-family: "Times New Roman","serif";">SALARY<o:p></o:p></span></div>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 79.8pt;" valign="top" width="106"><div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="font-family: "Times New Roman","serif";">DNO<o:p></o:p></span></div>
</td>
</tr>
</tbody></table>
<div>
<div style="text-align: justify;">
<b> </b></div>
</div>
<div>
<div style="text-align: justify;">
<b>DEPARTMENT RELATION</b></div>
</div>
<div>
<table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: none; mso-border-alt: solid windowtext .5pt; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;">
<tbody>
<tr>
<td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 79.8pt;" valign="top" width="106"><div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="font-family: "Times New Roman","serif";">DNUMBER<o:p></o:p></span></div>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 79.8pt;" valign="top" width="106"><div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="font-family: "Times New Roman","serif";">DNAME<o:p></o:p></span></div>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 79.8pt;" valign="top" width="106"><div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="font-family: "Times New Roman","serif";">MGR_SSN<o:p></o:p></span></div>
</td>
</tr>
</tbody></table>
</div>
<div>
<ul style="text-align: left;">
<li style="text-align: justify;">Next suppose that A1 wants to grant to account A2 ,the privilege to insert and delete tuples in both of these relations.However A1 does not want A2 to be able to propagate these privileges to additional accounts then A1 can issue the following command.</li>
</ul>
<div>
<div style="text-align: justify;">
<b>GRANT INSERT ,DELETE ON EMPLOYEE,DEPARTMENT TO A2;</b></div>
</div>
<div>
<div style="text-align: justify;">
<b><br /></b></div>
</div>
<div>
<div style="text-align: justify;">
Here A2 has not given the Grant Option.</div>
</div>
<div>
<ul style="text-align: left;">
<li style="text-align: justify;">Now suppose that A1 wants to allow account A3 to retrieve information from either of the two tables. A1 can issue the following command.</li>
</ul>
<div>
<div style="text-align: justify;">
<b>GRANT SELECT ON EMPLOYEE ,DEPARTMENT TO A3 WITH GRANT OPTION;</b></div>
</div>
</div>
<div>
<div style="text-align: justify;">
<br /></div>
</div>
<div>
<div style="text-align: justify;">
The clause with GRANT OPTION means that A3 can now propagate the privileges to other accounts by using GRANT.</div>
</div>
<div>
<div style="text-align: justify;">
E.G.- A3 can grant the SELECT privilege on the EMPLOYEE relation to A4 by issuing the following command. </div>
</div>
<div>
<div style="text-align: justify;">
<br /></div>
</div>
</div>
</div>
<div>
<div style="text-align: justify;">
<b>GRANT SELECT ON EMPLOYEE TO A4;</b></div>
</div>
<div>
<ul style="text-align: left;">
<li style="text-align: justify;">Now suppose that A1 decides to revoke the SELECT privilege on the EMPLOYEE relation from A3 .A1 then can issue this command.</li>
</ul>
<div>
<div style="text-align: justify;">
<b>REVOKE SELECT ON EMPLOYEE FROM A3;</b></div>
</div>
</div>
<div>
<div style="text-align: justify;">
<b><br /></b></div>
</div>
<div>
<div style="text-align: justify;">
<b> </b> The DBMS must now automatically revoke the SELECT privilege on EMPLOYEE from A4 too because A3 granted that privilege to A4 and A3 does not have the privilege any more.</div>
</div>
<div>
<div style="text-align: justify;">
<b><br /></b></div>
</div>
<div>
<div style="text-align: justify;">
<br /></div>
</div>
<div>
<div style="text-align: justify;">
<b>3. COMMENT COMMAND-</b>Adding the element into data dictionary.A comment can appear between any keywords, parameters, or punctuation marks in a statement. You can include a comment in a statement in two ways:</div>
</div>
<div>
<div>
<ul style="text-align: left;">
<li style="text-align: justify;">Begin the comment with a slash and an asterisk (/*). Proceed with the text of the comment. This text can span multiple lines. End the comment with an asterisk and a slash (*/). The opening and terminating characters need not be separated from the text by a space or a line break.</li>
<li style="text-align: justify;">Begin the comment with -- (two hyphens). Proceed with the text of the comment. This text cannot extend to a new line. End the comment with a line break.</li>
</ul>
</div>
</div>
<div>
<div style="text-align: justify;">
<b>EXAMPLE- <span style="font-family: 'Times New Roman', serif; text-align: justify;"> </span></b><b><span style="font-family: 'Times New Roman', serif; text-transform: uppercase;">SELECT
last_name, salary </span></b><b><span style="font-family: 'Times New Roman', serif;">/* Select the names of the employee and their salary<span style="text-transform: uppercase;"> </span>*/</span></b></div>
</div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<div style="text-align: justify;">
<b><span style="font-family: 'Times New Roman', serif;">FROM EMPLOYEE /*The
EMPLOYEE relation is used from where data is retrieved*/;<span style="text-transform: uppercase;"><o:p></o:p></span></span></b></div>
</div>
<div class="MsoNormal" style="text-align: justify;">
<br /></div>
</div>
Khushboo Arorahttp://www.blogger.com/profile/15520169114094502231noreply@blogger.com0tag:blogger.com,1999:blog-5350834041846150717.post-48596243552696835072014-08-06T12:32:00.002-07:002015-08-05T10:12:13.137-07:00Relational Query Languages | INTEGRITY CONSTRAINT IN SQL<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
<b>INTEGRITY CONSTRAINT IN SQL</b></div>
<div style="text-align: justify;">
<b><br /></b>
</div>
<div style="text-align: justify;">
<b>CONSTRAINTS- </b>Constraints are used to prevent the valid values in the table.</div>
<div style="text-align: justify;">
Several Types of Constraints-</div>
<div style="text-align: justify;">
1. Not null</div>
<div style="text-align: justify;">
2. Unique</div>
<div style="text-align: justify;">
3. Primary key constraint</div>
<div style="text-align: justify;">
4. Check constraint</div>
<div class="MsoNormal" style="line-height: 150%; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 9.0pt 13.5pt; text-align: justify;">
<div style="text-align: justify;">
<span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 150%;">5. Foreign key<o:p></o:p></span></div>
</div>
<div style="text-align: justify;">
<span class="Apple-tab-span" style="white-space: pre;"> </span></div>
<div style="text-align: justify;">
<b>1. NOT NULL-</b> NOT NULL constraints in Microsoft SQL Server allow you to specify that a column may not contain NULL values.</div>
<div style="text-align: justify;">
<b>SYNTAX- </b>create table <table name> (column name1 datatype(size) constraint <constraint name> not null, column name2 datatype(size));</div>
<div style="text-align: justify;">
<b>EG-</b> create table mydoc1 ( username varchar (20) constraint name_nn not null ,password number(10));</div>
<div style="text-align: justify;">
<br /></div>
<div class="separator" style="clear: both; text-align: justify;">
<a href="http://4.bp.blogspot.com/-ty9YKVWtfic/VcJCkUQmYwI/AAAAAAAABs8/rn3zMZXY4UA/s1600/not%2Bnull.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="344" src="http://4.bp.blogspot.com/-ty9YKVWtfic/VcJCkUQmYwI/AAAAAAAABs8/rn3zMZXY4UA/s640/not%2Bnull.png" width="640" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div>
<div>
<div style="text-align: justify;">
<b>2. UNIQUE- </b>You can use UNIQUE constraints to make sure that no duplicate values are entered in specific columns that do not participate in a primary key. </div>
</div>
<div>
<div style="text-align: justify;">
<b>SYNTAX</b>- create table <table name> (column name1 datatype(size) , column name2 datatype(size) unique);</div>
</div>
<div>
<div style="text-align: justify;">
<b>EG-</b> create table mydoc2 ( username varchar (20) ,password number(10) unique ); </div>
</div>
</div>
<div>
<div style="text-align: justify;">
<br /></div>
<div class="separator" style="clear: both; text-align: justify;">
<a href="http://2.bp.blogspot.com/-wfQq55C9hNQ/VcJC4f7Na1I/AAAAAAAABtE/3HDLz7pegi0/s1600/unique.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="344" src="http://2.bp.blogspot.com/-wfQq55C9hNQ/VcJC4f7Na1I/AAAAAAAABtE/3HDLz7pegi0/s640/unique.png" width="640" /></a></div>
</div>
<div class="separator" style="clear: both; text-align: justify;">
<br /></div>
<div>
<div>
<div style="text-align: justify;">
<b>3. PRIMARY KEY CONSTRAINT-</b></div>
</div>
<div>
<div style="text-align: justify;">
It is the mixture of unique and no null constraint. The primary key of a relational table uniquely identifies each record in the table. </div>
</div>
<div>
<div style="text-align: justify;">
<b>SYNTAX- </b>create table <table name> (column name1 datatype (size), column name2 datatype(size) constraint rollno_pk primary key);</div>
</div>
<div>
<div style="text-align: justify;">
<b>EG-</b> create table mydoc3 ( username varchar (20) ,password number(10) constraint rollno_pk primary key);</div>
<div style="text-align: justify;">
<br /></div>
</div>
</div>
<div class="separator" style="clear: both; text-align: justify;">
<a href="http://2.bp.blogspot.com/-B1q6nK1azrc/VcJDLvTVVOI/AAAAAAAABtM/JGAWGPXyvxw/s1600/dex.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="376" src="http://2.bp.blogspot.com/-B1q6nK1azrc/VcJDLvTVVOI/AAAAAAAABtM/JGAWGPXyvxw/s640/dex.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: justify;">
<br /></div>
<div>
<div>
<div style="text-align: justify;">
<b>4.CHECK CONSTRAINT-</b></div>
</div>
<div>
<div style="text-align: justify;">
A check constraint is applied to each row in the table.[clarification needed] The constraint must be a predicate. It can refer to a single or multiple columns of the table. The result of the predicate can be either TRUE, FALSE, or UNKNOWN, depending on the presence of NULLs. If the predicate evaluates to UNKNOWN, then the constraint is not violated and the row can be inserted or updated in the table. This is contrary to predicates in WHERE clauses in SELECT or UPDATE statements.</div>
</div>
<div>
<div style="text-align: justify;">
<b>SYNTAX-</b> create table <table name> (column name1 datatype(size) , column name2 datatype(size) , column name3 datatype(size) check (condition);</div>
</div>
<div>
<div style="text-align: justify;">
<b>EG-</b> create table student (sname varchar2(20) , rollno number(12) , attendance integer check (attendance between 0 and 100));</div>
</div>
</div>
<div>
<div style="text-align: justify;">
<br /></div>
</div>
<div class="separator" style="clear: both; text-align: justify;">
<a href="http://2.bp.blogspot.com/-6fTasCN-bMU/VcJDd4w6WtI/AAAAAAAABtU/mqe2_t9lnHI/s1600/check.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="388" src="http://2.bp.blogspot.com/-6fTasCN-bMU/VcJDd4w6WtI/AAAAAAAABtU/mqe2_t9lnHI/s640/check.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: justify;">
<br /></div>
<div>
<div style="text-align: justify;">
<br /></div>
</div>
<div>
<div>
<div style="text-align: justify;">
<b>4.FOREIGN KEY-</b></div>
</div>
<div>
<div style="text-align: justify;">
A foreign key is a field in a relational table that matches a candidate key of another table. The foreign key can be used to cross-reference tables. </div>
</div>
<div>
<div style="text-align: justify;">
<b>SYNTAX-</b> create table <table name> (column name1 datatype (size), column name2 datatype(size) constraint rollno_pk primary key);</div>
</div>
<div>
<div style="text-align: justify;">
create table <table name> (column name1 datatype (size), column name2 datatype(size) references <tablename which contain primary key < primary key attribute>);</div>
</div>
<div>
<div style="text-align: justify;">
<b>EG-</b> create table doc9 (bname varchar(20),brollno number(20) constraint vn_o primary key); </div>
</div>
<div>
<div style="text-align: justify;">
create table doc10 (sname varchar(20),srollno number(20) references doc9(brollno)); </div>
<div style="text-align: justify;">
<br /></div>
</div>
</div>
<div class="separator" style="clear: both; text-align: justify;">
<a href="http://2.bp.blogspot.com/-ij1r8nScZ6A/VcJDtfVKtuI/AAAAAAAABtc/ASJDg8dy0hk/s1600/foreign.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="426" src="http://2.bp.blogspot.com/-ij1r8nScZ6A/VcJDtfVKtuI/AAAAAAAABtc/ASJDg8dy0hk/s640/foreign.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: justify;">
<br /></div>
</div>
Khushboo Arorahttp://www.blogger.com/profile/15520169114094502231noreply@blogger.com0tag:blogger.com,1999:blog-5350834041846150717.post-21652184330344529162014-08-06T12:31:00.001-07:002015-08-05T10:02:26.537-07:00Relational Query Languages |NESTED QUERIES OR SUB-QUERY<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
<b>NESTED QUERIES OR SUB-QUERY</b></div>
<div style="text-align: justify;">
<b><br /></b>
</div>
<div style="text-align: left;">
<div style="text-align: justify;">
<b>DEFINITION</b></div>
</div>
<div style="text-align: left;">
</div>
<ul style="text-align: left;">
<li style="text-align: justify;">A sub query or nested queries is a query that appears within another SQL command. SELECT, DELETE, and UPDATE all support sub queries, though the rules and reasons for using them vary. </li>
<li style="text-align: justify;">A sub query is a complete query, but cannot specify a destination using TO or INTO. Sub queries are enclosed in parentheses in the containing query. Sub queries can appear in the WHERE clause of SELECT, UPDATE and DELETE.</li>
<li style="text-align: justify;">sub queries can also be used in the field list of SELECT, in the SET clause of UPDATE, and in the FROM clause of SELECT, UPDATE and DELETE.</li>
</ul>
<div>
<div style="text-align: justify;">
<b>TYPES OF NESTED QUERIES OR SUB QUERY</b></div>
<div style="text-align: justify;">
<b><br /></b></div>
</div>
<pre style="line-height: 150%; tab-stops: 0in 7.1pt 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt; text-align: justify;"><span lang="EN-IN" style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 150%;">There are three types of sub-Queries or nested queries<o:p></o:p></span></pre>
<div>
<pre style="line-height: 150%; tab-stops: 7.1pt 14.2pt 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt; text-align: justify;"><span lang="EN-IN" style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 150%;">1. Single row sub – query.<o:p></o:p></span></pre>
<pre style="line-height: 150%; tab-stops: 7.1pt 14.2pt 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt; text-align: justify;"><span lang="EN-IN" style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 150%;">2. Multiple row sub- queries.<o:p></o:p></span></pre>
<pre style="line-height: 150%; tab-stops: 7.1pt 14.2pt 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt; text-align: justify;"><span lang="EN-IN" style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 150%;">3. Multiple column sub- queries.</span></pre>
<pre style="line-height: 150%; tab-stops: 7.1pt 14.2pt 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt; text-align: justify;"><span lang="EN-IN" style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 150%;">
</span></pre>
<pre style="text-align: justify;"><span lang="EN-IN"><pre><span lang="EN-IN" style="font-family: 'Times New Roman', serif; line-height: 24px;"><b><span style="font-size: small;">1. Single row sub – query-</span><span style="font-size: medium;"> </span></b></span><span style="font-family: Times New Roman, serif;"><span style="line-height: 24px;">Queries that return a single value or row from the inner select statement.</span></span></pre>
<pre><span style="font-family: Times New Roman, serif;"><span style="line-height: 24px;"><b>Syntax:</b> select colname from tname where colname=(another query);
<b>Example: </b>Select losal from salgrade where grade=(select grade from salgrade where losal=700);
</span></span></pre>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-xLhpD7v-sLo/VcJBKGrtu1I/AAAAAAAABso/TUJe5GmaLi0/s1600/losal.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="369" src="http://1.bp.blogspot.com/-xLhpD7v-sLo/VcJBKGrtu1I/AAAAAAAABso/TUJe5GmaLi0/s640/losal.png" width="640" /></a></div>
<pre><span style="font-family: Times New Roman, serif;"><span style="line-height: 24px;"><b>2. Multiple row sub-query-</b> Queries that return more than one rows from the inner select statement.
<b>Syntax:</b> select colname1 , colname2 , colname3 from tname where colname in (another query);
<b>Example:</b> select grade , losal , hisal from salgrade where grade IN(select min (grade) from salgrade);</span></span></pre>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-LGrNh-TMQDM/VcJBf0aVKzI/AAAAAAAABsw/rVaLJFXK0KI/s1600/2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="392" src="http://4.bp.blogspot.com/-LGrNh-TMQDM/VcJBf0aVKzI/AAAAAAAABsw/rVaLJFXK0KI/s640/2.png" width="640" /></a></div>
<pre>
</pre>
<pre><pre style="line-height: 150%;"><span lang="EN-IN" style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 150%;"><b>3. Multiple column sub query:</b> Queries that return more than one columns from the inner select statement.<o:p></o:p></span></pre>
</pre>
<div>
</div>
</span></pre>
</div>
</div>
Khushboo Arorahttp://www.blogger.com/profile/15520169114094502231noreply@blogger.com0tag:blogger.com,1999:blog-5350834041846150717.post-87346491311190169292014-08-06T12:30:00.000-07:002015-08-05T09:55:10.519-07:00Relational Query Languages |AGGREGATE FUNCTIONS OR OPERATIONS<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
<b>AGGREGATE FUNCTIONS OR OPERATIONS</b></div>
<br />
<div style="text-align: justify;">
<b>DEFINITION-</b>The SQL Aggregate Functions are functions that provide mathematical operations. If you need to add, count or perform basic statistics, these functions will be of great help. In (Database Management) an aggregate function is a function where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning or measurement such as a set, a bag or a list.</div>
<br />
<div style="line-height: 150%; margin-bottom: .0001pt; margin: 0in; mso-list: l0 level1 lfo1; tab-stops: 9.0pt 13.5pt; text-align: justify; text-indent: 0in;">
<div style="text-align: justify;">
<span style="font-family: Wingdings;"><span style="font-family: 'Times New Roman'; font-size: 7pt; line-height: normal;"> </span></span><!--[endif]--><b>USEFUL AGGREGATE FUNCTIONS<o:p></o:p></b></div>
</div>
<div style="line-height: 150%; margin-bottom: .0001pt; margin: 0in; text-align: justify;">
<div style="text-align: justify;">
AVG () - Returns the average value form the the given table<o:p></o:p></div>
</div>
<div style="line-height: 150%; margin-bottom: .0001pt; margin: 0in; text-align: justify;">
<div style="text-align: justify;">
COUNT () - Returns the number of rows<o:p></o:p></div>
</div>
<div class="MsoNormal" style="line-height: 150%; tab-stops: .25in 22.5pt; text-align: justify; text-indent: 0in;">
<div style="text-align: justify;">
<span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 150%;">MAX () - Returns the largest
value<o:p></o:p></span></div>
</div>
<div class="MsoNormal" style="line-height: 150%; text-align: justify; text-indent: 0in;">
<span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 150%;">MIN () - Returns the smallest value<o:p></o:p></span></div>
<div style="text-align: justify;">
<span style="font-family: 'Times New Roman', serif; font-size: 12pt;">SUM () - Returns the sum</span></div>
<br />
<div style="text-align: justify;">
<b style="font-family: 'Times New Roman', serif;">1. AVG()-</b><span style="font-family: 'Times New Roman', serif;"> The AVG Function returns the average value for the column when applied to a column containing numeric data. The following is the syntax for the AVG Function.</span></div>
<br />
<div style="text-align: justify;">
<span style="font-family: Times New Roman, serif;"><b>SYNTAX-</b> select avg (column name) from <table name>;</span></div>
<div style="text-align: justify;">
<span style="font-family: Times New Roman, serif;"><b>EG-</b> select avg (losal) from salgrade;</span></div>
<div style="text-align: justify;">
<span style="font-family: 'Times New Roman', serif;"><br /></span></div>
<div style="text-align: justify;">
<b style="font-family: 'Times New Roman', serif;">2.COUNT()- </b><span style="font-family: 'Times New Roman', serif;">The COUNT Function returns the number of rows in a specified result set. The following syntax is one form of the COUNT Function.</span></div>
<br />
<div style="text-align: justify;">
<span style="font-family: Times New Roman, serif;"><b>SYNTAX-</b> select count(*) from <table name>;</span></div>
<div style="text-align: justify;">
<span style="font-family: Times New Roman, serif;"><b>EG-</b> select count (*) from salgrade;</span></div>
<div style="text-align: justify;">
<span style="font-family: Times New Roman, serif;"><br /></span></div>
<div class="separator" style="clear: both; text-align: justify;">
<a href="http://4.bp.blogspot.com/-2zbUBM7zq50/VcI_PgkYv3I/AAAAAAAABsU/9uQ9dnEd_JM/s1600/salgrade.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="457" src="http://4.bp.blogspot.com/-2zbUBM7zq50/VcI_PgkYv3I/AAAAAAAABsU/9uQ9dnEd_JM/s640/salgrade.png" width="640" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div style="text-align: justify;">
<span style="font-family: Times New Roman, serif;"><br /></span>
</div>
<div>
<div>
<div style="text-align: justify;">
<b>3.MAX() </b>- The MAX Function returns the data item with the highest value for a column when applied to a column containing numeric data. If you apply the MAX Function to a CHARACTER value, it returns the last value in the sorted values for that column. The following syntax is for the MAX Function.</div>
</div>
<div>
<div style="text-align: justify;">
<b>SYNTAX- </b>select max (column name) from <table name> group by <col name>;</div>
</div>
<div>
<div style="text-align: justify;">
<b>EG</b>- select max (salary) from emp group by id;</div>
</div>
<div>
<div style="text-align: justify;">
<br /></div>
</div>
<div>
<div style="text-align: justify;">
<b>4. MIN()- </b>The MIN Function returns the data item with the lowest value for a column when applied to a column containing numeric data. If you apply the MIN Function to a CHARACTER value, it returns the first value in the sorted values for that column. The following syntax is for the MIN Function.</div>
</div>
<div>
<div style="text-align: justify;">
<b>SYNTAX-</b> select min (column name) from <table name> group by <col name>;</div>
</div>
<div>
<div style="text-align: justify;">
<b>EG-</b> select min (salary) from emp group by name;</div>
</div>
<div>
<div style="text-align: justify;">
<br /></div>
</div>
<div>
<div style="text-align: justify;">
<b>5.SUM()- </b>The SUM Function returns the sum of all values in the specified column. The result of the SUM Function has the same precision as the column on which it is operating. The following syntax is for the SUM Function.</div>
</div>
<div>
<div style="text-align: justify;">
<b>SYNTAX-</b> select sum (column name) from <table name> group by <col name>;</div>
</div>
<div>
<div style="text-align: justify;">
<b>EG-</b> select sum (salary) from emp group by name;</div>
<div style="text-align: justify;">
<br /></div>
<div class="separator" style="clear: both; text-align: justify;">
<a href="http://4.bp.blogspot.com/-J5uq2EvBvdI/VcI_p3ZaC3I/AAAAAAAABsc/P9R05sJ4oPA/s1600/sum.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="564" src="http://4.bp.blogspot.com/-J5uq2EvBvdI/VcI_p3ZaC3I/AAAAAAAABsc/P9R05sJ4oPA/s640/sum.png" width="640" /></a></div>
</div>
</div>
<div>
<div style="text-align: justify;">
<br /></div>
</div>
</div>
Khushboo Arorahttp://www.blogger.com/profile/15520169114094502231noreply@blogger.com0tag:blogger.com,1999:blog-5350834041846150717.post-60792882619670003952014-08-06T12:28:00.000-07:002015-08-05T09:45:39.189-07:00Relational Query Languages |RELATIONAL COMPLETENESS<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
<b>RELATIONAL COMPLETENESS</b></div>
<br />
<div style="text-align: justify;">
The notion of relational completeness was propounded by codel in 1972 as a basis for evaluating the power of different query language.A language is relation-ally complete if the basic relational algebra operation can be performed.The basic relational algebra operations are-</div>
<ul style="text-align: left;">
<li style="text-align: justify;">UNION</li>
<li style="text-align: justify;">CROSS PRODUCT</li>
<li style="text-align: justify;">SELECTION</li>
<li style="text-align: justify;">DIFFERENCE</li>
<li style="text-align: justify;">PROJECTION</li>
</ul>
<div>
<div style="text-align: justify;">
Query language that are actually used in practice provides features in addition to one mentioned above.</div>
</div>
<div>
<div style="text-align: justify;">
<br /></div>
</div>
<div>
<div style="text-align: justify;">
<b>FOR EXAMPLE</b>-They provides the facilities for-</div>
</div>
<div>
<ul style="text-align: left;">
<li style="text-align: justify;">Modification,storage and deletion information</li>
<li style="text-align: justify;">Printing relation</li>
<li style="text-align: justify;">Assigning relation to some relation name</li>
<li style="text-align: justify;">Computing aggregate function like Sum and Max</li>
<li style="text-align: justify;">Performing Arithmetic operation</li>
</ul>
</div>
</div>
Khushboo Arorahttp://www.blogger.com/profile/15520169114094502231noreply@blogger.com1tag:blogger.com,1999:blog-5350834041846150717.post-69670188882176358402014-08-06T12:27:00.000-07:002015-08-05T09:44:21.484-07:00Database Protection | THREATS TO DATABASE <div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
<b>THREATS TO DATABASE</b></div>
<br />
<div style="text-align: justify;">
Threats to database result in the loss or degradation of some accepted security goals.</div>
<ul style="text-align: left;">
<li style="text-align: justify;"><b>Integrity</b></li>
<li style="text-align: justify;"><b>Availability</b></li>
<li style="text-align: justify;"><b>Confidentiality</b></li>
</ul>
<div>
<div style="text-align: justify;">
<b>1. </b><b>LOSS OF INTEGRITY-</b></div>
</div>
<div>
<ul style="text-align: left;">
<li style="text-align: justify;">Database integrity refers to the requirement that information be protected from improper modification.</li>
<li style="text-align: justify;">Modification of data include insertion,deletion,creation and changing the status of data.</li>
<li style="text-align: justify;">Integrity is lost if unauthorized changes are made to the data by either intentional or accidental facts. If the loss of system or data integrity is not corrected,it could result in inaccuracy,fraud or erroneous decisions.</li>
</ul>
<div>
<div style="text-align: justify;">
<b>2.LOSS OF AVAILABILITY-</b></div>
</div>
</div>
<div>
<ul style="text-align: left;">
<li style="text-align: justify;">Database availability refers to making objects available to a human user or a program to which they have a legitimate right.</li>
</ul>
<div>
<div style="text-align: justify;">
<b>3. LOSS OF CONFIDENTIALITY-</b></div>
</div>
</div>
<div>
<ul style="text-align: left;">
<li style="text-align: justify;">Database confidentiality refers to the protection of data from an unauthorized disclosure.</li>
<li style="text-align: justify;">Unauthorized ,unanticipated or unintentional disclosure could result in loss of public confidence,embarrassment or legal action against the organization.</li>
</ul>
</div>
</div>
Khushboo Arorahttp://www.blogger.com/profile/15520169114094502231noreply@blogger.com0tag:blogger.com,1999:blog-5350834041846150717.post-39387700752921419632014-08-06T12:26:00.000-07:002015-08-05T09:41:20.832-07:00Database Protection<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
<b>ACCESS CONTROL MECHANISM</b></div>
<div>
<div style="text-align: justify;">
<b><br /></b></div>
</div>
<div>
<div style="text-align: justify;">
<b>INTRODUCTION-</b></div>
</div>
<div>
<div>
<div style="text-align: justify;">
Access Control is any mechanism by which a system grants or revokes the right to access some data, or perform some action. Normally, a user must first Login to a system, using some Authentication system. Next, the Access Control mechanism controls what operations the user may or may not make by comparing the User ID to an Access Control database.</div>
</div>
<div>
<div style="text-align: justify;">
<br /></div>
</div>
<div>
<div style="text-align: justify;">
<b>Access Control systems include:</b></div>
</div>
<div>
<div style="text-align: justify;">
File permissions, such as create, read, edit or delete on a file server.</div>
</div>
<div>
<div style="text-align: justify;">
Program permissions, such as the right to execute a program on an application server.</div>
</div>
<div>
<div style="text-align: justify;">
Data rights, such as the right to retrieve or update information in a database.</div>
</div>
<div>
<div style="text-align: justify;">
<br /></div>
</div>
<div>
<div style="text-align: justify;">
<b>TYPES OF ACCESS CONTROL MECHANISM-</b>There are 2 types of access control mechanism-</div>
</div>
</div>
<div>
<div style="text-align: justify;">
1. Discretionary access control mechanism</div>
</div>
<div>
<div style="text-align: justify;">
2. Mandatory access control mechanism</div>
</div>
<div>
<div style="text-align: justify;">
<br /></div>
</div>
<div>
<div style="text-align: justify;">
<b>1. Discretionary access control mechanism-</b></div>
</div>
<div>
<ul style="text-align: left;">
<li style="text-align: justify;">These are used to grant privileges to the user</li>
<li style="text-align: justify;">Including the capability to access specific data files ,records or fields in a specified mode such as read,insert,delete or update.</li>
</ul>
<div>
<div style="text-align: justify;">
<b>2. Mandatory access control mechanism-</b></div>
</div>
<div>
<ul style="text-align: left;">
<li style="text-align: justify;">These are used to enforce multilevel security by classifying the data and users into various security classes or levels and then implementing the appropriate security policy of the organization </li>
<li style="text-align: justify;">Example- A typical security policy is to permit users at a certain classification level to see only the data items classified at the users own classification level.</li>
<li style="text-align: justify;">An extension of this is role based security which enforces policies and privileges based on the concept of roles.</li>
</ul>
</div>
</div>
</div>
Khushboo Arorahttp://www.blogger.com/profile/15520169114094502231noreply@blogger.com0tag:blogger.com,1999:blog-5350834041846150717.post-67491546393195760852014-08-06T12:25:00.005-07:002015-08-05T09:40:10.908-07:00Database Protection | DISCRETIONARY ACCESS CONTROL | access control<div dir="ltr" style="text-align: left;" trbidi="on">
<b>DISCRETIONARY ACCESS CONTROL</b><br />
<b><br /></b>
The typically method of enforcing discretionary access control in a database system is based on the granting and revoking privileges.<br />
<br />
<b>1. GRANTING PRIVILEGES</b><br />
The grant command is used to provide system level privileges .System level privileges are those privileges that you need to actually do something on the system.<br />
<b>EXAMPLE- </b>Your grant system privileges to a user so that they can<br />
<ul style="text-align: left;">
<li>Connect to the database</li>
<li>Create objects(create table,index)</li>
<li>Perform DBA activities like backup and database </li>
<li>Alter session related parameters</li>
</ul>
<div>
<b>2 .REVOKING PRIVILEGES- </b></div>
<div>
The revoke command is used to revoke system level privileges that were previously granted with the grant command. Simple enter the privilege you wish to revoke in the body of the revoke command. </div>
<div>
<br /></div>
<div>
<b>EXAMPLE OF GRANT AND REVOKE PRIVILEGES-</b><br />
<div>
<ul>
<li>Suppose that DBA(Data base administer) creates four accounts A1,A2,A3 and A4 and wants only A1 to be able to create base relations, then the DBA must issue the following GRANT command in SQL. </li>
</ul>
</div>
<div>
<b>GRANT CREATE TABLE TO A1;</b></div>
<div>
<ul>
<li>Suppose that A1 creates two base relations EMPLOYEE AND DEPARTMENT.Hence A1 is then the owner of these two relations.<b> </b></li>
</ul>
<div>
<b>EMPLOYEE RELATION</b><b> </b></div>
<table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: none;"><tbody>
<tr><td style="border: 1pt solid windowtext; padding: 0in 5.4pt; width: 79.8pt;" valign="top" width="106"><div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="font-family: "Times New Roman","serif";">NAME<o:p></o:p></span></div>
</td><td style="border: 1pt solid windowtext; padding: 0in 5.4pt; width: 79.8pt;" valign="top" width="106"><div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="font-family: "Times New Roman","serif";">SSN<o:p></o:p></span></div>
</td><td style="border: 1pt solid windowtext; padding: 0in 5.4pt; width: 79.8pt;" valign="top" width="106"><div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="font-family: "Times New Roman","serif";">BDATE<o:p></o:p></span></div>
</td><td style="border: 1pt solid windowtext; padding: 0in 5.4pt; width: 79.8pt;" valign="top" width="106"><div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="font-family: "Times New Roman","serif";">ADDRESS<o:p></o:p></span></div>
</td><td style="border: 1pt solid windowtext; padding: 0in 5.4pt; width: 79.8pt;" valign="top" width="106"><div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="font-family: "Times New Roman","serif";">SALARY<o:p></o:p></span></div>
</td><td style="border: 1pt solid windowtext; padding: 0in 5.4pt; width: 79.8pt;" valign="top" width="106"><div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="font-family: "Times New Roman","serif";">DNO<o:p></o:p></span></div>
</td></tr>
</tbody></table>
<div>
<b> </b></div>
<div>
<b>DEPARTMENT RELATION</b></div>
<div>
<table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: none;"><tbody>
<tr><td style="border: 1pt solid windowtext; padding: 0in 5.4pt; width: 79.8pt;" valign="top" width="106"><div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="font-family: "Times New Roman","serif";">DNUMBER<o:p></o:p></span></div>
</td><td style="border: 1pt solid windowtext; padding: 0in 5.4pt; width: 79.8pt;" valign="top" width="106"><div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="font-family: "Times New Roman","serif";">DNAME<o:p></o:p></span></div>
</td><td style="border: 1pt solid windowtext; padding: 0in 5.4pt; width: 79.8pt;" valign="top" width="106"><div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;">
<span style="font-family: "Times New Roman","serif";">MGR_SSN<o:p></o:p></span></div>
</td></tr>
</tbody></table>
</div>
<div>
<ul>
<li>Next suppose that A1 wants to grant to account A2 ,the privilege to insert and delete tuples in both of these relations.However A1 does not want A2 to be able to propagate these privileges to additional accounts then A1 can issue the following command.</li>
</ul>
<div>
<b>GRANT INSERT ,DELETE ON EMPLOYEE,DEPARTMENT TO A2;</b></div>
<div>
<b><br /></b></div>
<div>
Here A2 has not given the Grant Option.</div>
<div>
<ul>
<li>Now suppose that A1 wants to allow account A3 to retrieve information from either of the two tables. A1 can issue the following command.</li>
</ul>
<div>
<b>GRANT SELECT ON EMPLOYEE ,DEPARTMENT TO A3 WITH GRANT OPTION;</b></div>
</div>
<div>
<br /></div>
<div>
The clause with GRANT OPTION means that A3 can now propagate the privileges to other accounts by using GRANT.</div>
<div>
E.G.- A3 can grant the SELECT privilege on the EMPLOYEE relation to A4 by issuing the following command. </div>
<div>
<br /></div>
</div>
</div>
<div>
<b>GRANT SELECT ON EMPLOYEE TO A4;</b></div>
<div>
<ul>
<li>Now suppose that A1 decides to revoke the SELECT privilege on the EMPLOYEE relation from A3 .A1 then can issue this command.</li>
</ul>
<div>
<b>REVOKE SELECT ON EMPLOYEE FROM A3;</b></div>
</div>
<div>
<br /></div>
<div style="-webkit-text-stroke-width: 0px; color: black; font-family: 'Times New Roman'; font-size: medium; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: left; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px;">
<div style="margin: 0px;">
<b> </b> The DBMS must now automatically revoke the SELECT privilege on EMPLOYEE from A4 too because A3 granted that privilege to A4 and A3 does not have the privilege any more.</div>
</div>
</div>
</div>
Khushboo Arorahttp://www.blogger.com/profile/15520169114094502231noreply@blogger.com0tag:blogger.com,1999:blog-5350834041846150717.post-50462777637451217422014-08-06T12:23:00.000-07:002015-08-05T09:38:08.240-07:00Database Protection |access control mechachanism | mandatory access control mechanism<div dir="ltr" style="text-align: left;" trbidi="on">
<b>MANDATORY ACCESS CONTROL MECHANISM</b><br />
<b><br /></b>
<b>INTRODUCTION-</b><br />
<ul style="text-align: left;">
<li>In many applications an additional security policy is needed that classifies data and users based on security classes.This approach is known as mandatory access control.</li>
<li>The need for multilevel security exists in government military and intelligence applications as well as many industrial and corporate applications.</li>
</ul>
<div>
<b>TYPICAL SECURITY CLASSES</b></div>
<div>
<ul style="text-align: left;">
<li>TS(Top Secret)</li>
<li>Secret(S)</li>
<li>Confidential(C)</li>
<li>Unclassified(U) </li>
</ul>
<div>
<b> TS>=S>=C>=U</b></div>
<div>
<ul style="text-align: left;">
<li>TS is the highest level</li>
<li>U is the lowest level</li>
</ul>
<div>
<b>The commonly used model for multilevel security known as BELL - LAPADULA MODEL</b></div>
</div>
<div>
<b><br /></b></div>
<div>
<b>BELL - LAPADULA MODEL</b></div>
<div>
<ul style="text-align: left;">
<li>Classifies each subject(user,account,program) and object (relation,tuple,column,view,operation) into one of the security classifications TS,S,U or C.</li>
<li>We will refer to the clearance of a subject S as class(S) and to the classification of an object O as class(O).</li>
</ul>
<div>
<b>Two restrictions are enforced on data access based on the subject/ object classifications.</b></div>
<div>
<ol style="text-align: left;">
<li>A subject S is not allowed read access to an object O unless <b>class(S) >= class(O)</b>. This is known as simple security property and intuitive .</li>
<li>A subject S is not allowed to write an object O unless <b>class(S) <= class(O)</b>.This is known as star property and is less intuitive.</li>
</ol>
<div>
<br /></div>
</div>
</div>
<div>
A multilevel relation scheme R with n attributes would be represented as-</div>
<div>
<b>R ( A1,C1,A2,C2-------------------An ,Cn,TC)</b></div>
<div>
<br /></div>
</div>
</div>
Khushboo Arorahttp://www.blogger.com/profile/15520169114094502231noreply@blogger.com0tag:blogger.com,1999:blog-5350834041846150717.post-88854446831056846812014-08-06T12:08:00.004-07:002015-08-05T09:35:41.275-07:00Database Protection | access control | ROLE BASED ACCESS CONTROL<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
<b>ROLE BASED ACCESS CONTROL</b></div>
<div style="text-align: justify;">
<b><br /></b>
</div>
<div style="text-align: justify;">
1. Role based access control (RBAC) emerged rapidly in the 1990s as a proven technology for managing and enforcing security in large scale enterprise wide systems.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
2 Permissions are associated with roles and users are assigned to appropriate roles.Roles can be created using the CREATE ROLE and DESTROY ROLE command.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
3. The GRANT and REVOKE commands discussed under DAC can then be used to assign and revoke privileges from roles.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
4. RBAC ensures that only authorized user are given access to certain data and resources.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
5. Roles models have several desirable features such as flexibility,policy neutrality ,better support for security management and administration and others aspects that make them attractive candidates for developing secure wed based applications.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
6. RBAC model provides a natural mechanism for addressing the security issues related to the execution of tasks and workflows.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
7. Easier deployment over the internet has been another reason for the success of RBAC models.</div>
<div style="text-align: justify;">
<br /></div>
</div>
Khushboo Arorahttp://www.blogger.com/profile/15520169114094502231noreply@blogger.com0tag:blogger.com,1999:blog-5350834041846150717.post-5152320892302503682014-08-06T12:07:00.002-07:002015-08-05T09:33:58.313-07:00Database Protection |ENCRYPTION<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
<b>ENCRYPTION</b></div>
<br />
<div style="text-align: justify;">
<b>INTRODUCTION- </b>Encryption is a technique for transforming information on a computer in such a way that it becomes unreadable.So, even if someone is able to gain access to a computer with personal data on it ,they likely won't be able to do anything with the data unless they have complicated,expensive software or the original data key.</div>
<br />
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<b>TYPES OF ENCRYPTION- </b>Two types of encryption are-</div>
<ol style="text-align: left;">
<li style="text-align: justify;">SYMMETRIC METHODS</li>
<li style="text-align: justify;">ASYMMETRIC METHODS </li>
</ol>
<div style="text-align: justify;">
<b>1. SYMMETRIC METHODS-</b></div>
<ul style="text-align: left;">
<li style="text-align: justify;">Symmetric encryption is also known as private-key cryptography and is called so because the key used to encrypt and decrypt the message must remain same..</li>
<li style="text-align: justify;">Using this method a sender encrypts the data with one key and then sends the data (CIPHER TEXT) and the receiver use the key to decry-pt the data. </li>
</ul>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-sQ-PXLeOWtE/VcI6nVFyjEI/AAAAAAAABsA/JZLSjYehirk/s1600/symmetric.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/-sQ-PXLeOWtE/VcI6nVFyjEI/AAAAAAAABsA/JZLSjYehirk/s1600/symmetric.png" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<b>2. ASYMMETRIC METHODS- </b></div>
<ul style="text-align: left;">
<li style="text-align: justify;">Asymmetric method or public key cryptography is different than the previous method because it uses two keys for encryption or decryption</li>
<li style="text-align: justify;">.A public key is freely available to everyone and is used to encrypt messages and a different private key is used by the recipient to decry-pt messages.</li>
</ul>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-QLQhRokmHho/VcI6sLZvgaI/AAAAAAAABsI/x-4cYaR9tMg/s1600/des.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/-QLQhRokmHho/VcI6sLZvgaI/AAAAAAAABsI/x-4cYaR9tMg/s1600/des.png" /></a></div>
<div class="separator" style="clear: both; text-align: justify;">
<br /></div>
</div>
Khushboo Arorahttp://www.blogger.com/profile/15520169114094502231noreply@blogger.com0tag:blogger.com,1999:blog-5350834041846150717.post-8889952522889994042014-08-06T12:06:00.003-07:002015-08-05T09:17:38.244-07:00Database Protection | firewalls<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
<b>FIREWALLS</b></div>
<br />
<div style="text-align: justify;">
<b>INTRODUCTION-</b></div>
<ul style="text-align: left;">
<li style="text-align: justify;">The term firewall originally referred to a wall intended to confine a fire or potential fire within a building. Later uses refer to similar structures, such as the metal sheet separating the engine compartment of a vehicle or aircraft from the passenger compartment.</li>
<li style="text-align: justify;">In computing, a firewall is a software or hardware-based network security system that controls the incoming and outgoing network traffic based on applied rule set. </li>
<li style="text-align: justify;">A firewall establishes a barrier between a trusted, secure internal network and another network (e.g., the Internet) that is not assumed to be secure and trusted.</li>
<li style="text-align: justify;">Many personal computer operating systems include software-based firewalls to protect against threats from the public Internet.</li>
<li style="text-align: justify;"> Many routers that pass data between networks contain firewall components and, conversely, many firewalls can perform basic routing functions.</li>
</ul>
<div>
<div style="text-align: justify;">
<br /></div>
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-Oww4cGLrrbg/VcI28QFmgZI/AAAAAAAABr0/5kvrtMmvm18/s1600/firewall.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="215" src="http://3.bp.blogspot.com/-Oww4cGLrrbg/VcI28QFmgZI/AAAAAAAABr0/5kvrtMmvm18/s400/firewall.png" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: justify;">
<br /></div>
<div>
<div style="text-align: justify;">
<br /></div>
</div>
</div>
Khushboo Arorahttp://www.blogger.com/profile/15520169114094502231noreply@blogger.com0tag:blogger.com,1999:blog-5350834041846150717.post-13512881587783043762014-08-06T12:05:00.003-07:002015-08-05T09:14:16.532-07:00Database Protection | digital signatures<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
<b>DIGITAL SIGNATURES</b></div>
<br />
<div style="text-align: justify;">
<b>AT SENDER SIDE</b></div>
<ul style="text-align: left;">
<li style="text-align: justify;">Message is divided into two parts.Firstly through hash function message digest is made.Message digest is the smallest version of the message.</li>
<li style="text-align: justify;">Secondly the message is encrypted i.e. cipher text is made and then the combination of both i.e. encrypted message + message digest is sent to the receiver.</li>
</ul>
<div>
<div style="text-align: justify;">
<b>AT RECEIVER SIDE</b></div>
</div>
<div>
<ul style="text-align: left;">
<li style="text-align: justify;">At receiver side through hash function a new message digest is made and also through decryption method.</li>
<li style="text-align: justify;">If both the message digest are same then we can say that the received message is correct or without error.</li>
</ul>
</div>
<div style="text-align: justify;">
<br /></div>
<div class="separator" style="clear: both; text-align: justify;">
<a href="http://2.bp.blogspot.com/-nXEgT3Sw9xE/VcI17NyZcLI/AAAAAAAABrs/Mv8AyFBWnzM/s1600/digital%2Bsignature.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="244" src="http://2.bp.blogspot.com/-nXEgT3Sw9xE/VcI17NyZcLI/AAAAAAAABrs/Mv8AyFBWnzM/s640/digital%2Bsignature.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<b><br /></b></div>
</div>
Khushboo Arorahttp://www.blogger.com/profile/15520169114094502231noreply@blogger.com1tag:blogger.com,1999:blog-5350834041846150717.post-54774059893068750182014-08-06T12:04:00.000-07:002015-08-05T09:03:35.069-07:00Database management system | DOMAIN KEY NORMAL FORM<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
<b>DOMAIN KEY NORMAL FORM</b></div>
<ul style="text-align: left;">
<li style="text-align: justify;">Domain/key normal form (DKNF) is a normal form used in database normalization which requires that the database contains no constraints other than domain constraints and key constraints.</li>
</ul>
<ul style="text-align: left;">
<li style="text-align: justify;">A domain constraint specifies the permissible values for a given attribute, while a key constraint specifies the attributes that uniquely identify a row in a given table.</li>
</ul>
<ul style="text-align: left;">
<li style="text-align: justify;">The domain/key normal form is achieved when every constraint on the relation is a logical consequence of the definition of keys and domains, and enforcing key and domain restraints and conditions causes all constraints to be met. Thus, it avoids all non-temporal anomalies.</li>
</ul>
<ul style="text-align: left;">
<li style="text-align: justify;">The reason to use domain/key normal form is to avoid having general constraints in the database.</li>
</ul>
<ul style="text-align: left;">
<li style="text-align: justify;">Most databases can easily test domain and key constraints on attributes. General constraints however would normally require special database programming in the form of stored procedures that are expensive to maintain and expensive for the database to execute. Therefore general constraints are split into domain and key constraints.</li>
</ul>
<ul style="text-align: left;">
<li style="text-align: justify;">It's much easier to build a database in domain/key normal form .</li>
</ul>
<ul style="text-align: left;">
<li style="text-align: justify;">While the domain/key normal form eliminates the problems found in most databases, it tends to be the most costly normal form to achieve. </li>
</ul>
<ul style="text-align: left;">
<li style="text-align: justify;">The third normal form, Boyce–Codd normal form, fourth normal form and fifth normal form are special cases of the domain/key normal form. All have either functional, multi-valued or join dependencies that can be converted into (super)keys. </li>
</ul>
<ul style="text-align: left;">
<li style="text-align: justify;">The domains on those normal forms were unconstrained so all domain constraints are satisfied. However, transforming a higher normal form into domain/key normal form is not always a dependency-preserving transformation and therefore not always possible.</li>
</ul>
<div style="text-align: justify;">
<br /></div>
</div>
Khushboo Arorahttp://www.blogger.com/profile/15520169114094502231noreply@blogger.com2tag:blogger.com,1999:blog-5350834041846150717.post-78019495823937561882014-08-04T01:01:00.003-07:002014-10-14T12:27:19.605-07:00Database management software microsoft access | download microsoft access<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-j-vBrVQZpGE/U989NR7sRXI/AAAAAAAACq0/-TSYRbWrpQw/s1600/images.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/-j-vBrVQZpGE/U989NR7sRXI/AAAAAAAACq0/-TSYRbWrpQw/s1600/images.jpg" /></a></div>
Microsoft Access, also known as Microsoft Office Access, is a database management system from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools. It is a member of the Microsoft Office suite of applications, included in the Professional and higher editions or sold separately.<br />
<br />
Microsoft Access stores data in its own format based on the Access Jet Database Engine. It can also import or link directly to data stored in other applications and databases.<br />
<br />
Software developers and data architects can use Microsoft Access to develop application software, and "power users" can use it to build software applications. Like other Office applications, Access is supported by Visual Basic for Applications, an object-oriented programming language that can reference a variety of objects including DAO (Data Access Objects), ActiveX Data Objects, and many other ActiveX components. Visual objects used in forms and reports expose their methods and properties in the VBA programming environment, and VBA code modules may declare and call Windows operating-system functions.<br />
<br />
you can download microsoft access here.<br />
<span style="font-size: large;">download link:<a href="http://www.softpedia.com/progDownload/Access-Database-Engine-Download-190964.html" target="_blank">click here</a> </span><br />
<br /></div>
Royhttp://www.blogger.com/profile/06335373269654004062noreply@blogger.com1tag:blogger.com,1999:blog-5350834041846150717.post-12686002323741692942014-08-04T00:43:00.002-07:002014-08-04T00:43:57.884-07:00Database management software informix | download informix<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-fw57Kz7m8y0/U9846w5OsmI/AAAAAAAACqo/AnKD2VP1kmQ/s1600/Untitled.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-fw57Kz7m8y0/U9846w5OsmI/AAAAAAAACqo/AnKD2VP1kmQ/s1600/Untitled.jpg" height="213" width="400" /></a></div>
IBM Informix is a product family within IBM's Information Management division that is centered on several relational database management system (RDBMS) offerings. The Informix products were originally developed by Informix Corporation, whose Informix Software subsidiary was acquired by IBM in 2001.<br /><br />IBM has continued active development and marketing of the key Informix products, the current version of which (12.1) forms the basis of several product editions with variation in capacity and functionality. The Informix database has been used in many high transaction rate OLTP applications in the retail, finance, energy and utilities, manufacturing and transportation sectors. More recently the server has been enhanced to support data warehouse workloads.<br /><br />The Informix server supports the object-relational model, which has permitted IBM to offer extensions that support data types that are not a part of the SQL standard. The most widely used of these are the time series and spatial extensions, which provide both data type support and language extensions that permit high performance domain specific queries and efficient storage for datasets based around time series and spatial data.<br />
<br />
Download informix here.<br />
<br />
<span style="font-size: large;">Download link:<a href="http://www.softpedia.com/progDownload/SQLData-Express-for-Informix-to-IBM-DB2-Download-224940.html" target="_blank">click here</a></span></div>
Royhttp://www.blogger.com/profile/06335373269654004062noreply@blogger.com1