INT306 – DBMS ETE Question Paper

Database Management System –  INT306, Previous year’s paper is here. There is Important opics are –

Concurrancy control
Recoverability
PL/SQL
Distributed Database Concept
ER Diagram

Paper Goes Here –

1NF, 2NF, 3NF, BCNF ,4NF : NORMAL FORMS

Normal forms are use to decompose our table in such form which prevent our database to suffer from various type of anomalies as we discussed before.

Normalization

Normalization : Normalization is the process in which we decompose the big amount of data relations into smaller relation by eleminating unusual characteristics, which are :

VARIOUS CLAUSES

CLAUSES ARE USED TO APPLY SOME CONDITION ON OUR DATABASE AT VIEW LEVEL SCHEMA.
THERE ARE VARIOUS CLAUSES ARE AVAILABLE READ MORE TO VIEW.

DML COMMANDS

DML : Data manipulation commands are used to manage data in a record which can be manipulated using some query as

1:INSERT
2:UPDATE
3:DELETE
4:SELECT

INSERT COMMAND : Insert command is used to insert values in a table :
Syntax :

INSERT into table-name Values( data1, data2, data3, ……………. );

or

INSERT into table-name Values(&ATR1,&ATR2,&ATR3,………..);

Where ATR1,ATR2,ATR3……. are the attribute of table.

__________________________________________

Delete commands : Used to delete all records from the table or a particular row from a table :

Syntax :

DELETE from table-name;

or

DELETE from table-name WHERE condition;

____________________________________________

UPDATE COMMAND : – Used to update data of a row in a record:

Syntax :

UPDATE table-name SET column-name = ‘value’ WHERE condition;

For Multiple values –

UPDATE table-name SET column1-name = ‘value’,column2-name = ‘value’ WHERE condition;

_______________________________________________

SELECT QUERY :-

Use to fetch data to display unit from database:  It can retrieve data from database and view it as temporary level :

Syntax :  to view all records

SELECT * from table-name

to view individual columns –

SELECT column1,column2,columnN from table-name

to view records based on condition :

SELECT * from table-name WHERE condition

__________________________

EXAMPLES

Table 1 : T1

Name

Id

Address

INSERT into T1 Values(‘Sherlock’,’205’,’221b’ );
INSERT into T1 Values(‘Jhon’,’305’,’221b’ );
INSERT into T1 Values(‘Lestrade’,’405’,’DI12’ );
INSERT into T1 Values(‘Molly’,’505’,’B_House’ );

Name	Id	Address
Sherlock	205	221b
Jhon	305	221b
Lestrade	405	DI12
Molly	505	B_House

DELETE from T1 WHERE Id=’405’;

Name	Id	Address
Sherlock	205	221b
Jhon	305	221b
Molly	505	B_House

UPDATE T1 SET Id= ‘105’ WHERE Name=’Sherlock’;

Name	Id	Address
Sherlock	105	221b
Jhon	305	221b
Molly	505	B_House

SELECT * from T1 WHERE Id=’105’;

 

Name	Id	Address
Sherlock	105	221b

TRUNCATE, DROP AND RENAME

Truncate Command- This command removes all entries from a table but doesn’t remove the table structure.
This command is different from DELETE command which can delete only one row present in table.
Using truncate command is like to initialize a table as new.

truncate table table-name

this command delete all the data present in table-name without removing its attribute.

_______________________________________________________________________

DROP Command – Drop command is used to remove a complete table from from database.
Syntax will be-

drop table table-name

This will remove complete table structure and its records from database.

_____________________________________________________________________

RENAME Command – This command is used to rename a particular table :
Syntax follows –

rename table old-table-name to new-table-name

Database Models

Data Models – Data Models can be defined as an integrated collection or concept for describing and manipulating data, relationship b/w data, and constraints. On the data is an organisation.
Structural Part – Set of rules requires to construct Database.
Manipulation Part – Type of operation we apply to data to manipulate, access or update the data.

Object Base  Data Models
– ENTITY RELATIONSHIP  MODEL
– Object Oriented Data Models
Record Base  Data Models –
– Heirarichal Models
– Network Models
– Relational Models

CLICK ON LINK TO READ COMPLETE ARTICLE –

DATABASE MANAGEMENT SYSTEM

A database  is a collection of data in which we can manipulate data and store them in various way. For example you have to store your name, age and school you previously attended so that data would be stored in database.
Related software by which we can access data manage them, creation and manipulation done in known as DATABASE MANAGEMENT SYSTEM ( DBMS ).

How it is diffeerent from file system –

1. File system have problem of  Redundant data (duplicity) We can make data consistant by removing duplicity.
2. File system have Integrity problem  which tends to have multiple file format, duplicate meta data so that we need to write new program for accessing each file.
3. Data isolation is again a main problem in file sytem in which we have multiple file format.
   Invalid data may be inserted by a human error or a bug.
4. Failure between the accessing of data may leave data in inconsistant state with partial update carried out.
5. Concurrent access by multiple user can lead to slow performance and redundat data or inconsistancy.

Level Of Abstraction – Tells us how data is stored and accessed, let examine figure below –

ADVANTAGE –

Controlling data redundancy
Data consistancy
Data Integity
Data security
Backup
Data independence

Schema  – Overall structure of database
Logical schema – Such as type of data, size, attribute
Physical Schema – Hardware

In next post we will discuss about the DATA MODELS –

ALTER QUERY

ALTER query is used to alter a previous table.

1. Add a new column;
2. Rename a particular column
3. Change the name of column
4. Delete that column using drop commands.

ADDING A NEW COLUMN –

ALTER TABLE <table_name> ADD(column_name data_type);

NAME	ADDRESS
VIVEK	ETAWAH
PRINCE	MEERUT

ALTER TABLE T25 ADD(AGE int);

NAME	ADDRESS	AGE
VIVEK	ETAWAH
PRINCE	MEERUT

This will add a new column age with NULL value;

CREATE QUERY

Wireshark Cookie Dump:OKCancel

To create a table in database RDBMS we need certain informations such as table name, attributes, datatypes, column size.

It is a DATABASE DEFINITION LANGUAGE.
A table name must be starting with a character. “T25” is a valid name while “25T” is not a valid name.

DATA TYPES ARE –
int –> define integer types
char –> character type
varchar –> Variable character( more memory efficient in compare to char )
float –> use to define floating values.

TABLE CREATION –

Syntax :

CREATE TABLE <table_name> ( <attr1> <data_type> , <attr2> <data_type> );

EXAMPLE –>

CREATE TABLE  T25 ( UID int, NAME varchar(10),AGE int );

Above syntax is used to create a database with attribute UID, NAME, AGE;

UID

NAME

AGE

In next post we will discuss how to insert value in table :

Wireshark Cookie Dump:

OKCancel