IT 802 Class 12 Database Concept Notes

Get latest, qualitative and comprehensive notes of database concept it 802 class 12. it covers all topics very optimally that certainly help students to secure high marks.

What is data?

Generally, data is collection of characters, numbers and other symbols that represents values of some situations or variables. It is raw facts which have not been processed to reveal useful information. Data is plural or singular of the word data is “datum”.

Some examples of data we come across are:

• Name, age, gender, contact details etc. of a person
• Images, graphics, animations, audio, video
• Online post, comment and messages,
• Signals generated by sensors
• Document and web pages etc

What is database?

Collection of logically related data that has been recorded, organized and made available for searching is called Database.

Properties of database

• It represents real world aspects
• It is well structured and populated with specific data
• It can be of any size and complexity
• It can be managed manually or automatically
• It is atomic (entire transaction takes place at once)
• It is secured and durable (data never lost)

Use of database in real life applications

• Banking
• Online shopping
• Payroll
• Inventory management
• Loan and investment
• Reservation system And many more

What are the needs for a database?

• To manage large amount of data without redundancy (repetition).
• To manage large amount of data with lucidity (without complexity)
• To maintain data uniformly in different places or file
• Easy and convenient retrieval of data from any place or file
• Controlled data sharing at different user level

What is DBMS?

• DBMS stands for Database Management System.
• It is application software that can be used to create and manage database efficiently.
• it enable user or application to create, store, update, delete and retrieve data from database itself.
• It lets user to relate data in database so that it can be managed and updated consistently.
• It serves as an interface between the database and end user or application program.
• Examples of DBMS are MySQL, Oracle, MongoDB, MS Access, MS SQL Server etc.

DBMS Environment

What are characteristics of DBMS?

• Self Describing: DBMS not only contains the database but also the description of the data that it stores.
• Insulation between program and data: program can access data uninterruptedly even when structure of data stored in DBMS is modified.
• Data Sharing: DBMS includes concurrency control software to allow simultaneous access of data in the database without any inconsistency problems.
• Backup and Recovery: DBMS has provision of Backup and recovery of data that helps user to protect the data from damage or loss.

What are different types of users of DBMS?

• End users: users who use database for retrieving, updating and creating reports as per requirement.
• Database Administrator (DBA): DBA is responsible for granting data access permission to user at different level, monitoring database usage and provide technical support.
• Application Programmer: One who write programs to create application to interact with database. Application programmer uses high level languages and SQL to do so.
• System Analyst: they determine and specify user requirements that help in designing efficient and reliable database.

What are the advantages of DBMS approach?

• Reduction in Redundancy: Data in a DBMS is more concise because of the central repository of data. All the data is stored at one place. There is no repetition of the same data. This also reduces the cost of storing data on hard disks or other memory devices.
• Improved Consistency: The chances of data inconsistencies in a database are also reduced as there is a single copy of data that is accessed or updated by all the users.
• Improved Availability: Same information is made available to different users. This helps sharing of information by various users of the database.
• Improved Security: Though there is improvement in the availability of information to users, it may also be required to restrict the access to confidential information. By making use of passwords and controlling users’ database access rights, the DBA can provide security to the database.
• User Friendly: Using a DBMS, it becomes very easy to access, modify and delete data.  It reduces the dependency of users on computer specialists to perform various data related operations in a DBMS because of its user friendly interface.

What are the limitations of DBMS approach?

High Cost: The cost of implementing a DBMS System is very high.

Time Consuming: It also takes long time to implement DBMS as it involve analyzing user requirement, database designing and developing applications.

Security and Recovery Overheads: unauthorized access to database can lead to threat to the individual or organization also data must be regularly backed up to prevent its loss due natural disasters

What is RDBMS?

Stands for Relational Database Management System. It was developed by E.F. Codd at IBM 1970. It is used to organize collection of data as a collection of relations where each relation corresponds to a table of values. Each row in the table corresponds to a unique instance of data and each column name is used to interpret the meaning of that data in each row.

What are different RDBMS Terminologies? Explain.

Relation: a table is called relation. Another way we can say that collection of tuple is called relation.

Tuple: A row of table is called tuple. In another way we can say that a single record is called tuple

Attribute: a column of table is called attribute.

Domain: the data type of values in each column is called the Domain.

Degree: The total number of attributes in a relation is called the Degree of relation.

Cardinality: the total number of records (tuples) is called the Cardinality of relation.

Relation Schema: it structure of a relation that includes attributes, domains and constraints are called Relational Schema.

Relation State: the current status of a relation in terms of total numbers of tuples at a time.

What are Characteristics of Relation?

• Sequence of attributes in relation is immaterial (not important).
• Sequence of tuples in relation is immaterial.
• Every tuple of relation should be unique.
• The value in each tuple is an atomic value (indivisible).
• “NULL” is used to represent values that are unknown or non applicable to certain attributes.

What is a constraint in RDBMS?

Constraints are restrictions that when applied ensure accuracy, validity and reliability of data in a database. 

What are types of constraints in RDBMS?

There are 5 types of constraints mentioned below:

Domain constraint: it specifies that the value in every attribute in each tuple must be from the domain of that attribute.

Key Constraint: there are 3 key constraints defined below:

Super Key: this is a set of attribute in a relation, for which no two tuples in a relation state have the same combination of values.

Candidate key: keys which are candidate for super key

Primary Key: one of the candidate key is designated as primary key. Primary key is used to identify tuples in relation. only one candidate key can become primary key.

Null Value Constraints: sometimes an attribute can not have null values. That attribute can constrained to be Not Null.

Entity Integrity Constraint: it ensures that all tuples are unique in a table. Primary key implements entity integrity constraint.

Referential Integrity Constraint: it ensures relationship between relations. The main purpose of this constraint is to check that data entered in one relation is consistent with the data entered in another relation. Foreign key is used to establish relationship between relations.

What is Foreign Key?

• The key which is Primary key in another related table is called Foreign Key.
• A table can have more than Foreign keys.
• Foreign key can have null values.

What is SQL?

SQL stands for Structured Query Language. It is developed to manage database. SQL enable us to create database, insert data, update data as per requirement and retrieve data.

All SQL commands are categorized in two types i.e. Data Definition Language (DDL) and Data Manipulation language (DML) where DDL is used to define structure and constraints of data and DML is used to insert, modify and delete data in a database.

What are the Datatypes used in SQL?

Given table shows data types commonly used in SQL.

Data type	Use	Example
Char(n)	Fixed length character string. n denotes max no of characters.	Char(6) : sanjay, harish
Varchar(n)	Variable length character string. n denotes max no of characters.	Varchar(10): ram, arjun
Date	Date as YYYY-MM-DD	‘2022-07-23’
Integer	Whole number	45, 4539
Decimal(m,d)	Fixed point number. M denotes no of digits stored for values and d denotes no of decimal points.	Decimal(3,2): 34.89 Decimal(4): 2813
Float	Real number	456.23

Create Table command

This command is used to create a new table or relation.

Syntax:

CREATE TABLE<table name>
(
<column 1><data type> [constraint] ,
<column 2><data type>[constraint],
<column 3><data type>[constraint]
);
Note: [Constraint] is optional

Example:

CREATE TABLE Item
(
Itemno char(5) primary key,
Itemname varchar(20),
Rate float NOT NULL,
Qty int
);

Screenshot:

What are types of Database Constraints?

Database enforces 5 types of constraints mentioned hereunder:

Not Null Constraint

Sometimes an attribute may not be allowed to leave blank. Hence Not Null constraints can be associated in this case.

Example:

CREATE TABLE TEACHER
(
Teacher_ID INTEGER,
First_NameVARCHAR(20) NOT NULL,
Last_NameVARCHAR(20),
Gender CHAR(1),
Date_of_Birth DATE,
Salary FLOAT,
);

Screenshot:

Default Constraint

To make a value bydefault  for an attribute Default constraint can be used.

CREATE TABLE TEACHER
(
Teacher_ID INTEGER,
First_NameVARCHAR(20) NOT NULL,
Last_NameVARCHAR(20),
Gender CHAR(1),
Date_of_Birth DATE,
Salary FLOAT DEFAULT 20000,
);

Screenshot:

Check Constraint

In order to restrict the values of an attribute within a range, CHECK constraint may be used.

CREATE TABLE EMPLOYEE
(
Emp_ID INTEGER,
Emp_NameVARCHAR(20) NOT NULL,
Gender CHAR(1),
Date_of_Birth DATE,
Salary FLOAT,
Dept varchar(20) CHECK (Dept IN (‘Store’, ‘Operation’, ‘Maintenance’)
);

Screenshot:

Primary Key

it ensures followings when applied:

• Duplicate data is not allowed in the attribute.
• Attribute can not be left blank
• A relation can have only primary key

Example:

CREATE TABLE EMPLOYEE
(
Emp_ID INTEGER PRIMARY KEY,
Emp_NameVARCHAR(20) NOT NULL,
Gender CHAR(1),
Date_of_Birth DATE,
Salary FLOAT,
Dept varchar(20)
);

Screenshot:

What is Referential Integrity Constraint?

it ensures relationship between relations. The main purpose of this constraint is to check that data entered in one relation is consistent with the data entered in another relation. Foreign key is used to establish relationship between relations.

Example:

Consider the following tables:
Item (Itemno, Iname, rate)
Order (Ono, Odate, Ino, qty, total)
Here in this example Ino is the foreign key that references Ino of Item table which is Primary key. The SQL command for making Ino to foreign key in Order table is as follows:

Create Order
(
Ono int Primary Key,
Odate date,
Itemno int,
Qty int,
Total int,
FOREIGN KEY (Itemno) REFERENCES Item (Itemno)
);

Screen Shot:

How to Name constraints in SQL?

Constraints can be named in the Create Table command. The advantage is that named constraints can be easily deleted or updated using the Alter Table command. A constraint can be named by using the keyword CONSTRAINT followed by the name of the constraint and its specification.

Example:

Create Order
(
Ono int Primary Key,
Odate date,
Itemno int,
Qty int,
Total int,
FOREIGN KEY (Itemno) REFERENCES Item (Itemno)
);

Screenshot:

Drop table

This command is used to delete a table completely from database. It deletes structure and data both of table from database.

Syntax:

DROP TABLE <table name>;

Example:

DROP TABLE Orders;

Alter table

It is used to modify structure of a table. Following are the alteration can be done using this command:

Adding a column:

we can add a new column in existing table.

Example:

ALTER TABLE Item ADD date_of_purchase date NOT NULL;

Dropping a column

A column can be dropped using alter table command as given below:

Example:

ALTER TABLE Item DROP qty;

Modifying a table:

A column can also be modified using Alter Table command as given below:

ALTER TABLE item ALTER qty SET DEFAULT 100;

How to drop primary key?

ALTER TABLE Item DROP Primary key;

Insert command

This command is used to insert a tuple in a relation. We must specify the name of the relation in which tuple is to be inserted and the values. The values must be in the same order as specified during the Create Table command.

Syntax:

INSERT INTO <tablename> VALUES (<values >);

Example:

Inserting values in table Item (Ino, Iname, Rate, Qty);

INSERT INTO Item Values (1, ‘soap’,44,120);

Screenshot:

Update command

Update command is used to modify the attribute values of one or more tuples in a table.

Syntax:

UPDATE <tablename>
SET  <expression>
WHERE <criteria>

Example:

UPDATE Item
SET Rate = 30
WHERE itemno = ‘i0001’;

Screenshot:

Delete command

To delete one or more tuples (rows) in a realtion DELETE command is used.

Syntax:

DELETE FROM <tablename>
WHERE <criteria>;

Example:

DELETE FROM item WHERE itemno = ‘i0002’;

Screenshot:

Select command

Select command is used to access or retrieve specific or complete set of records from database.

Syntax:

SELECT <attribute list> FROM <table name>;

Query1: Display all records from Item table.

Code:

Select * from item;

Screenshot:

Query2: Display itemname with its rate from item table.

Code:

Select itemname,rate from item;

Screenshot:

Query3: display name of items which rate is more than 100.

Code:

Select itemname from item where rate > 100;

Screenshot:

Query4: display item records which name begins with s.

Code:

Select * from item where itemname like ‘s%’;

Screenshot:

Query5: retrieve item details which rate is less than 100 and qty is more than 100.

Code:

Select * from item where rate < 100 and qty > 100;

Screenshot: