CBSE Class 11 Informatics Practices Chapter 3 Relational Database Management System

Class Notes of Chapter 3: Relational Database Management System

Class 11th Informatics Practices

Relational Database Management System

      Topics:

• Keyword
• DataType in MySQL
• Difference between CHAR and VARCHAR
• SQL Constraints/ Integrity Constraints
• Accessing Database in MySql

Keywords

• Database Management System(DBMS): It is a computer based record keeping system that stores the data centrally and manages data efficiently.
• Relational Data Model: In this model, the data is organized into tables called relations.T he relationship is established between 2 tables on the basis of the common column.
• Network Data Model In this model the data is represented by collections of records and relationships among data are represented by links.
• Hierarchical Data Model In this model records is organized in the form of parent-child trees.
• Object-Oriented Data Model in this model objects represent the data and associated operations where an object is an identifiable entity with some characteristics and behavior.
• Normalization Is a process of attaining good database design by removing/reducing data anomalies.
• DDL: Data Definition Language o Part of the SQL that facilitates defining creation/modification etc. of the database object such as tables, indexes, sequences etc.
• DML: Data Manipulation Language. o Part of the SQL that facilitates manipulation (additions/deletions/modification) of data which resides in the database tables.
• Meta Data: Facts/data about the data stored in the table.
• Data Dictionary: A file containing facts/data about the data stored in a table.
• Relational Data Model: In this model, data is organized into tables i.e. rows and columns. These tables are called relations.
• The Network Data model: In this model data are represented by the collection of records & relationships among data. The collections of records are connected to one another by means of links.
• The Hierarchical Data Model: In this model records are organized as trees rather than arbitrary graphs.
• Object-Oriented Data Model: Data and associated operations are represented by objects. An object is an identifiable entity with some characteristics and behavior.
• Relation: Table in Database
• Domain: Pool of values from which the actual values appearing
• Tuple: Any single row of a relation
• Attribute: Any column of the relation
• Degree: Number of attributes(fields) in a relation
• Cardinality: Number of tuples(rows) in a relation
• View: the Virtual table that does not really exist in its own right but can be used to vies
• Primary Key: Set of one or more attributes that can uniquely identify tuples within the relation.
• Candidate Key: A Candidate Key is the one that is capable of becoming Primary key i.e., a field or attribute that has the unique value for each row in the relation.
• Alternate Key: A candidate key that is not primary key is called alternate key.
• Foreign Key: A non-key attribute, whose values are derived from the primary key of some other table.
• Integrity Constraints: Integrity Constraints are the rules that a database must comply all the times. It determines what all changes are permissible to a database.

Datatype in MySQL

Class	Data Type	Description	Format	Example
Text	CHAR(size)	A fixed-length string between 1 and 255 characters in length right-padded with spaces to the specified length when stored. Values must be enclosed in single quotes or double quotes.	CHAR(size)	‘COMPUTER’ ‘CBSE’
	VARCHAR(size)	A variable-length string between 1 and 255 characters in length; for example VARCHAR(20).	VARCHAR (size)	‘SCIENCE’ ‘Informatics’
NUMERIC	DECIMAL(p,s)	It can represent a number with or without the fractional part. The size argument has two parts: precision and scale. Precision (p) indicates the number of significant digits and scale (s) the maximum number of digits to the right of the decimal point.	Number(p,s)	58.63
	INT	It is used for storing integer values	INT	164
Date	DATE	It represents the date including day, month and year between 1000-01-01 and 9999-12-31	YYYY-MMDD	2014-08-27

Difference between CHAR and VARCHAR

The CHAR data-type stores fixed length strings such that strings having length smaller than the field size are padded on the right with spaces before being stored. The VARCHAR on the other hand supports variable length strings and therefore stores strings smaller than the field size without modification.


SQL Constraints/ Integrity Constraints

1. SQL Constraint is a condition or check applicable on a field or set of fields.
2. They can also be defined or modified after creating the tables.
3. When constraints are defined any data entering in the table is first checked to satisfy the condition specified in particular constraint if it is, only then table data set can be updated. If data updation/ insertion is violating the defined constraints, database rejects the data (entire record is rejected).
4. When a constraint is applied to a single column, it is called a column level constraint but if a constraint is applied on a combination of columns it is called a table constraint. Following constraints can be defined on a table in SQL:

Constraints Name	Description
Primary Key	Used to create a primary key
Unique	Used to create a unique Key
Not Null	Used to define that column will not accept null values.
Foreign Key/ References	Used to define referential integrity with another table.
Default	Used to define the columns default value.
Check	Used to define the custom rule.

Not Null and Default constraints can be applied only at column level rest all constraints can be applied on both column level and table levels.


Accessing Database in MySql

Through USE keyword we can start any database Syntax:
USE <database Name>;
Example: USE ADDRESS;

CREATING TABLE IN MYSQL

Through Create table command we can define any table.
CREATE TABLE <tablename>
(<columnname><datatype>[(<Size>)], ......... );
CREATE TABLE ADDRESS(SNo integer, City char(25));

INSERTING DATA INTO TABLE

The rows are added to relations using INSERT command.
INSERT INTO <tablename>[<columnname>]
VALUES (<value>, <value>...);
INSERT INTO ADDRESS (SNo, City)
VALUES (100,’JAIPUR’);

SELECT COMMAND:

The SELECT command is used to make queries on the database. A query is a command that is given to produce certain specified information from the database table(s). The SELECT command can be used to retrieve a subset of rows or columns from one or more tables. The syntax of Select Command is:
SELECT <Column-list>
FROM <table_name>
[Where <condition>]
[GROUP BY <column_list>]
[Having <condition>]
[ORDER BY <column_list [ASC|DESC ]>]
Example: SELECT * FROM ADDRESS WHERE SNo=100;

Eliminating Redundant Data

DISTINCT keyword eliminates redundant data SELECT DISTINCT City FROM ADDRESS;

Selecting from all the rows

SELECT * FROM ADDRESS;

Viewing structure of table:

DESCRIBE/DESC <tablename>;
DESCRIBE ADDRESS;

Using column aliases:

SELECT <column name> AS [columnalias][,...]
FROM <tablename>;
SELECT SNo, City AS “STUDENTCITY”
FROM ADDRESS;

Condition based on a range:

Keyword BETWEEN used for making range checks in queries. SELECT SNo, CITY FROM ADDRESS WHERE SNo BETWEEN 10 AND 20;

Condition based on a list:

Keyword IN used for selecting values from a list of values. SELECT rno, sname FROM student WHERE rno IN (10, 20, 60);

Condition based on a pattern matches:

Keyword LIKE used for making character. comparison using strings percent(%) matches any substring underscore(_) matches any character SELECT SNo, City FROM ADDRESS WHERE City LIKE ‘%ri’;

Searching for NULL

The NULL value in a column is searched for in a table using IS NULL in the WHERE clause (Relational Operators like =,<> etc cannot be used with NULL).
For example, to list details of all employees whose departments contain NULL (i.e., novalue), you use the command:
SELECT empno, ename
FROM emp
Where Deptno IS NULL;

ORDER BY clause:
It is used to sort the results of a query.
SELECT <column name> [, <column name>, .]
FROM <table name>
[WHERE <condition>] [ORDER BY <column name>];
SELECT * FROM ADDRESS WHERE SNo>50 ORDER BY City;

Creating tables with SQL Constraint :
CREATE TABLE command is used to CREATE tables , the syntax is:
CREATE TABLE <Table_name>
( column_name 1 data_type1 [(size) column_constraints],
column_name 1 data_type1 [(size) column_constraints],
:
:
[<table_constraint> (column_names)] );

SQL Constraint:
A Constraint is a condition or check applicable on a field or set of fields.

NOT NULL/UNIQUE/DEFAULT/CHECK/PRIMARY KEY/FOREIGN KEY

Constraint:
CREATE TABLE student (rollno integer NOT NULL );
CREATE TABLE student (rollno integer UNIQUE );
CREATE TABLE student (rollno integer NOT NULL, Sclass integer, Sname varchar(30),
Sclass DEFAULT 12 );
CREATE TABLE student (rollno integer CHECK (rollno>0), Sclass integer, Sname
varchar(30));
CREATE TABLE student (rollno integer NOT NULL PRIMARY KEY, Sclass integer,
Sname varchar(30));
CREATE TABLE teacher (Tid integer NOT NULL, FOREIGN KEY (Studentid )
REFRENCES student (Sid));

Modifying data in tables:
Existing data in tables can be changed with UPDATE command. The Update command is use to change the value in a table. The syntax of this command is:
UPDATE <table_name>
SET column_name1=new_value1 [,column_name2=new_value2,……]
WHERE <condition>;
UPDATE student SET Sclass=12 WHERE Sname=’Rohan’;

Deleting data from tables:
The DELETE command removes rows from a table. This removes the entire rows, not individual field values. The syntax of this command is
DELETE FROM <table_name>
[WHERE <condition>];
e.g., to delete the tuples from EMP that have salary less than 2000, the following command is
used:
DELETE FROM emp WHERE sal<2000;
To delete all tuples from emp table:
DELETE FROM emp;

MySQL functions:
A function is a special type of predefined command set that performs some operation and returns a single value. Single-row functions return a single result row for every row of a queried table. They are categorized into: Numeric functions, String functions, and Date and Time functions.
1) Numeric Functions

• POWER( ) : Returns the argument raised to the specified power. POW ( ) works the
same way.
Example:
(i) POW(2,4); Result:16
(ii) POW(2,-2); Result:0.25
(iii) POW(-2,3) Result: -8

• ROUND( ) : ROUND(X) Rounds the argument to the zero decimal place, Where as ROUND(X,d) Rounds the argument to d decimal places.
Example :
(i) ROUND(-1.23); Result: -1
(ii) ROUND(-1.58); Result: -2
(iii) ROUND(1.58); Result: 2
(iv)ROUND(3.798, 1);Result: 3.8
(v) ROUND(1.298, 0); Result: 1
(vi) ROUND(23.298, -1); Result: 20

• TRUNCATE( ) : Truncates the argument to specified number of decimal places.
Example:
(i) TRUNCATE(7.29,1 )Result: 7.2
(ii) TRUNCATE(27.29,-1) Result: 20
2) Character/String Functions

• LENGTH( ) : Returns the length of a string in bytes/no.of characters in string.
Example: LENGTH(‘INFORMATICS’);
Result:11

• CHAR( ) : Returns the corresponding ASCII character for each integer passed.
Example : CHAR(65) ;
Result : A

• CONCAT( ): Returns concatenated string i.e. it adds strings.
Example : CONCAT(‘Informatics’,’ ‘,‘Practices’);
Result : Informatics Practices

• INSTR( ): Returns the index of the first occurrence of substring.
Example : INSTR(‘Informatics’,’ mat’);
Result : 6(since ‘m’ of ‘mat’ is at 6th place)

• LOWER( )/ LCASE( ): Returns the argument after converting it in lowercase.
Example: LOWER(‘INFORMATICS’);
Result : informatics

• UPPER( )/ UCASE( ): Returns the argument after converting it in uppercase.
Example: UCASE(‘informatics’);
Result :INFORMATICS

• LEFT( ) : Returns the given number of characters by extracting them from the left side of the given string.

Example : LEFT(‘INFORMATICS PRACTICES’, 3);
Result : INF

• MID( )/SUBSTR( ) : Returns a substring starting from the specified position in a given string.
Example: MID(‘INFORMATICS PRACTICES’,3,4);
Result : FORM

• LTRIM( ) : Removes leading spaces.
Example : LTRIM(' INFORMATICS');
Result: 'INFORMATICS’

• RTRIM( ): Removes trailing spaces.
Example : RTRIM('INFORMATICS ');
Result: 'INFORMATICS’

• TRIM( ) : Removes leading and trailing spaces.
Example: TRIM(' INFORMATICS ');
Result: 'INFORMATICS’ 3) Date/Time Functions

· CURDATE( ) : Returns the current date
Example: CURDATE( );
Result:'2014-07-21'

· NOW( ): Returns the current date and time
Example: NOW( );
Result: '2014-07-21 13:58:11'

· SYSDATE( ) : Return the time at which the function executes
Example: SYSDATE( );
Result:'2014-07-21 13:59:23’

· DATE( ): Extracts the date part of a date or date time expression
Example: DATE('2003-12-31 01:02:03');
Result::'2003-12-31'

· MONTH( ) :Returns the month from the date passed
Example: MONTH('2010-07-21');
Result: 7

· YEAR( ): Returns the year
Example: YEAR('2010-07-21');
Result: 2010

· DAYNAME( ): Returns the name of the weekday
Example: DAYNAME('2010-07-21');
Result: WEDNESDAY

Returns the day of the month (0-31)
Example: DAYOFMONTH('2010-07-21');
Result: 21

· DAYOFWEEK( ): Returns the weekday index of the argument
Example: DAYOFWEEK('2010-07-21');
Result: 4 (Sunday is counted as 1)

· DAYOFYEAR( ): Return the day of the year(1 -366)
Example: DAYOFYEAR('2010-07-21');
Result: 202

· Aggregate or Group functions: MySQL provides Aggregate or Group functions which work on a number of values of a column/expression and return a single value as the result.
Some of the most frequently used Aggregate functions in MySQL are:

No.	Name of the Function	Purpose
1	MAX()	Returns the MAXIMUM of the values under the specified column/expression.
2	MIN()	Returns the MINIMUM of the values under the specified column/expression.
3	AVG()	Returns the AVERAGE of the values under the specified column/expression
4	SUM()	Returns the SUM of the values under the specified column/expression.
5	COUNT()	Returns the COUNT of the number of values under the specified column/expression.

The GROUP BY clause groups the rows in the result by columns that have the same values. Grouping can be done by column name, or with aggregate functions in which case the aggregate produces a value for each group.The HAVING clause place conditions on groups in contrast to WHERE clause that place conditions on individual rows. While WHERE condition cannot include aggregate functions, HAVING conditions can do so.

ALTER TABLE COMMAND:

The ALTER Table command is used to change the definition (structure) of existing table. Usually , it can:
(i) Add columns to a table
(ii) Delete columns
(iii) Modify a column
The syntax of this command is:
For Add or modify column:
ALTER TABLE <Table_name> ADD/MODIFY <Column_defnition>;
For Delete column
ALTER TABLE <Table_name> DROP COLUMN <Column_name>;

Example :

1. To add a new column address in EMP table command will be : ALTER TABLE EMP ADD (address char (30));
2. To modify the size of sal column in EMP table, command will be: ALTER TABLE EMP MODIFY (sal number(9,2) );
3. To delete column Address from Table EMP the command will be: ALTER TABLE EMP DROP COLUMN address;