DBMS Notes
Database Management System
Data Modeling
Database Architecture
Relational Model
Relational Algebra
Entity Relationship Model
Functional Dependencies
Normalization
Transaction And Concurrency Control
Deadlock
Files and Storage
Indexing
Heading
Q
1
What are fields and records in a table?
Ans
A field is an area in a fixed or known location in a unit of data such as a record, message header, or computer instruction that has a purpose and usually a fixed size. In some contexts, a field can be subdivided into smaller fields.
Q
2
Write all the commands used to create a table .
Ans
create table "tablename"
("column1" "data type",
"column2" "data type",
"column3" "data type",
...
"columnN" "data type");
Q
3
Define DCL,TCL,DML,DDL.
Ans
DDL
DDL is short name of Data Definition Language, which deals with database schemas and descriptions, of how the data should reside in the database.
CREATE - to create a database and its objects like (table, index, views, store procedure, function, and triggers)
ALTER - alters the structure of the existing database
DROP - delete objects from the database
TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
COMMENT - add comments to the data dictionary
RENAME - rename an object
DML
DML is short name of Data Manipulation Language which deals with data manipulation and includes most common SQL statements such SELECT, INSERT, UPDATE, DELETE, etc., and it is used to store, modify, retrieve, delete and update data in a database.
SELECT - retrieve data from a database
INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - Delete all records from a database table
MERGE - UPSERT operation (insert or update)
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - interpretation of the data access path
LOCK TABLE - concurrency Control
DCL
DCL is short name of Data Control Language which includes commands such as GRANT and mostly concerned with rights, permissions and other controls of the database system.
GRANT - allow users access privileges to the database
REVOKE - withdraw users access privileges given by using the GRANT command
TCL
TCL is short name of Transaction Control Language which deals with a transaction within a database.
COMMIT - commits a Transaction
ROLLBACK - rollback a transaction in case of any error occurs
SAVEPOINT - to rollback the transaction making points within groups
SET TRANSACTION - specify characteristics of the transaction
Q
4
Explain various optimization technique?
Ans
A trigger is a procedure which is automatically invoked by the DBMS in response to changes to the database, and is specified by the database administrator (DBA). A database with a set of associated triggers is generally called an active database.
Parts of trigger
A triggers description contains three parts, which are as follows −
Event − An event is a change to the database which activates the trigger.
Condition − A query that is run when the trigger is activated is called as a condition.
Action −A procedure which is executed when the trigger is activated and its condition is true.
Use of trigger
Triggers may be used for any of the following reasons −
To implement any complex business rule, that cannot be implemented using integrity constraints.
Triggers will be used to audit the process. For example, to keep track of changes made to a table.
Trigger is used to perform automatic action when another concerned action takes place.
Types of triggers
The different types of triggers are explained below −
Statement level trigger − It is fired only once for DML statement irrespective of number of rows affected by statement. Statement-level triggers are the default type of trigger.
Before-triggers − At the time of defining a trigger we can specify whether the trigger is to be fired before a command like INSERT, DELETE, or UPDATE is executed or after the command is executed. Before triggers are automatically used to check the validity of data before the action is performed. For instance, we can use before trigger to prevent deletion of rows if deletion should not be allowed in a given case.
After-triggers − It is used after the triggering action is completed. For example, if the trigger is associated with the INSERT command then it is fired after the row is inserted into the table.
Row-level triggers − It is fired for each row that is affected by DML command. For example, if an UPDATE command updates 150 rows then a row-level trigger is fired 150 times whereas a statement-level trigger is fired only for once.
Create database trigger
To create a database trigger, we use the CREATE TRIGGER command. The details to be given at the time of creating a trigger are as follows −
Name of the trigger.
Table to be associated with.
When trigger is to be fired: before or after.
Command that invokes the trigger- UPDATE, DELETE, or INSERT.
Whether row-level triggers or not.
Condition to filter rows.
PL/SQL block is to be executed when trigger is fired.
The syntax to create database trigger is as follows −
CREATE [OR REPLACE] TRIGGER triggername
{BEFORE|AFTER}
{DELETE|INSERT|UPDATE[OF COLUMNS]} ON table
[FOR EACH ROW {WHEN condition]]
[REFERENCE [OLD AS old] [NEW AS new]]
BEGIN
PL/SQL BLOCK
END.
Q
5
Explain auto increment with reference to sql table.
Ans
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.
Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
Syntax for MySQL
The following SQL statement defines the "Personid" column to be an auto-increment primary key field in the "Persons" table:
CREATE TABLE Persons (
Personid int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (Personid)
);