DATA INTIGRITY:
The concept of enforcing business rules into database is called “data integrity”
We have three types of data integrity
- entity integrity
- domain integrity
- referential integrity
Data integrity can be achieved by using constrains
1. Entity integrity: can be achieved with the help of primary key
And unique constraint
Primary key is nothing but collection of one or more columns that can be used to
Identify a row in the table
A table can have only one primary key and it doesn’t allow duplicate values
Unique constrain doesn’t allow duplicates but allow one null value
Syntax:- col name data type primary key
Col name data type unique
2. Domain integrity:
Domain integrity can be achieved by using not null, default , check constraint
Not null: to restrict null in a column
Syntax:- col name data type not null
Default: to insert a default values into a column
Syntax: - col name data type default default value
Ex:- sage data type default 26
Check: check constraint is used to check the values in al column with user defined condition
Composite primary key: primary key constructs with multiple columns is called as composite primary key
Max number of column is included in primary key 16
3. Referential integrity: referential integrity can be achieved by using foreign key
Constraint, foreign key is used to maintain the relation among the tables
Relation ship is nothing but associated or dependency among the tables
We have 3 relation ships: 1. one to one 2. One to many 3. Many to many
Foreign key: is nothing but a column referencing values from some other table primary key column of unique constraint column
Foreign key column allows duplicate values
A table can have 253 foreign key
Foreign key column data type should be same as primary key
Note: in composite primary key each column allows duplicates but combination should be unique
Deference between delete and truncate
Delete:
- delete statement can be specified with where condition
- delete statement is logged operations so we can roll back
- delete is slow process
- delete statement will activate the trigger
Truncate:
1. truncate statement can not specified with where condition
2. truncate non logged operation so we can not roll back
3. truncate is faster than delete statement
4. truncate will not activate the triggers
Deference between deny and revoke
- revoke statement can not cancel the permissions inherited from the role
But deny statement can cancel the permissions inherited from the role
Deference between Group by and having clause
Group is used to group the rows based on one or more columns in a table
Where as having is used to check the condition after grouping the rows
Sub-Queries:
A transact sql statement nested with other transaction sql statement is called “sub-query”
Generally a select statement nested with other select statement is called “sub-query”
- Nested Sub-Queries: in a sub query outer join depends on inner is called “Nested sub-queries”
2. Co-related sub-queries: in a sub-query inner query depends on outer query is called
Co-relate sub query
Generally alias names will be used while writing the co-related sub-query
Locks
Types of locks:
1. shared lock
2. update lock
3. exclusive lock
4. intent lock
1.
2.
3. Exclusive lock: a transaction applied exclusive lock on the data until the transition
Completed. No other transaction can access the data
4. Intent lock:
A transaction is applied lock on the higher level object at the same time other transaction is applied lock on the lower level object is call intent lock
To see locks:
Exec Sp_lock
Ex: --- select *from emp
Exec sp_lock
Isolation levels:
1. Read uncommitted
2. Read committed
3. Repeatable read
4. Serialized
With respect to the performance recommended isolation levels are Read committed
And Read uncommitted
To the isolation level programmability
Set transaction isolation
Set read uncommitted
What is distributed transaction?
A transaction is involved in multiple servers is called “distributed transaction”
Normalization:
The process of applying normal forms sequentially on a database design is called “Normalization” , Normal form is nothing but a rule
Types of Normal forms:
- First normal form
- second normal form
- third normal form
- BCNP(Boyce—codd normal form)
- fourth normal form
- fifth normal form
- six normal form
- first normal form: to say the table is first normal form
Rule:-- No multi value columns
Ex: --- Customers Customers
Cust id (pk) after customization Cust id (pk)
Cust name Cust name
Address Cust h.no
Street
City
State
Cust id cust name address
100 Ravi H.No: 49-128\17\A
Hyderabad (This is not a first normal form)
Chintal
101 Basker H.NO: 22-19/B
Hyderabad
Alwal
- Second Normal form:
To say a table is second normal form it should follow the rules
Rules:
- It should be first normal form
- Every non key column should depend on complete primary key
(No partial dependency)
Non key column means column not having primary key and foreign key
Ex: --- Products
Prod id (pk)
Product description (Second normal form)
Unit price
Stock
Orders Orders
Order no (pk) order no
Order date prod id (composite primary key)
Cust id Quantity
Price (This is not second normal form)
Order no Order date
Prod id Cust id (fk)
Partial dependency: ---- A non key column depends on part of the primary key is called
“Partial depency”
- third normal form:
To say a table is third normal form it should follow rules
Rules:
A. It should second normal form
B. No transitive dependency
Transitive Dependency
A non key column depends on other non key column in a table is “Transitive dependency”
Ex Employees
Ecode Ename Esal Dept no Dname
(This table is third normal form because there is a transitive
Dependency)
Dept no Dname after customization Ecode Ename Esal Dept no
(This is third normal form)
Advantage of Normalization
1. Normalization eliminate data redundancy
2. Eleminate functional dependency problems like partial dependency ,
Transitive dependency
3. Faster index creation and sorting the data
4. Normalization will improve the performance while performing insert,
Delete, update operations into tables
De-normalization:
The reveres process of the normalization is called “De-normalization”
De-normalization will prove the performance of while retrieving the data
From the tables
Draw backs of De-normalization:
De-normalization will introduce the problem called “Data Redundancy”
(Duplicate data)
Draw backs of the Normalization:
Normalization will reduce the performance while retrieving the data from the
No of tables
Identity property:
Identity property is similar to auto increment in ms-access
This will apply only numeric column
* It is used to generate the sequence numbers
* A table can have one identity column
Deference between “Where clause and having clause”
Where condition is executed before group by
But having condition verified after group by
Condition having aggregate function can not be specified with “where clause” Condition having aggregate function can be specified with “having clause”
No comments:
Post a Comment