Database Concepts

Database : Collection of information about an organization  in form of tables. These tables are logically related with each other

Flat database: all the data is stored in one file  then such a database is called Flat Database. A Spreadsheet is an example of flat database

Relational Database: When data is stored in multiple tables that are linked via common filed or column, then such database called Relational database

RDBMS : Relational Database Management System. Software or computer program that manages the relational  database effectively and efficiently. E.g MS Access, Base. Oracle, MySQL.

Relation: In RDBMS table is called Relation

Redundancy : duplication  of data

Data Inconsistency : Multiple mismatch copy of same data

( .odb)  : the file extension of base database is .odb

Components of table:

Data Item/Field/Column/Attribute: Column headers containing one type of information

Record/Row/Tuple: Row in a table . Record contain a complete unit of information.

Primary key:  Primary key is a key that can uniquely identifies the records /tuples in a relation. This key can never be duplicated and NULL. 

Composite Primary key: Primary key  that consist  of combination of  two or more columns

Alternative keys : The columns that have the unique  values for each record but are not selected as the primary keys are called alternative keys

All the candidate keys other than the primary key of a relation 

Candidate keys: Primary keys and alternative keys together are called the candidate keys.In other words all attributes that are eligible to be a primary key , are called candidate keys.

This key can never be duplicated and NULL. For example rollno field can be primary key for a table student.

Attributes of a table which can serve as a primary key are called candidate keys. 

What is DBMS ?

DBMS is a software program that is used to create and manage databases effectively and efficiently.

The database system stores data centrally at one place where all applications can get data required. Its advantages : 1. Reduce redundancy 2. Enhance data security 3.Reduce inconsistency 4. Sharing data.

Assignment:

1. Computer based record keeping system called .......

2. Duplication of data is called.......

3. Multiple mismatch  copies of same data is called .........

4. A field that uniquely identify record in a table called.........

5. An interface is user defined layout that lets users can view, view ,enter or change data in tables, know as ...........

6. Named collection of  fields which represent a complete unit of information is called ......

7. A statement that gives you filtered data according to your condition and specification , is called

8.  A  row in a table is called.....

9. When a primary key contains more than one filed , it is know as...........

10. ........holds a single piece of data.

Ans 1. DBMS 2.  data redundancy 3. data inconsistancy 4.Primary key  5.  form 6. record 7. Query 8. tuple 9.  Comosite primary key  10. Field or attribute or column

DATABASE is a collection of information in form of tables  it can easily be accessed, managed, and updated. /A database management system (DBMS) is system software  for creating and managing databases effectively and efficiently.. /A DBMS makes it possible for end users to create, read, update and delete data in a database./The database system stores data centrally at one place where all applications can get data required. Its advantages : 1. Reduce redundancy 2. Enhance data security 3.Reduce inconsistency 4. Sharing data.

Flat database: Data is stored in one file E.g Spreadsheet /Relational Database:  data is stored in multiple tables  are linked via common filed or column

RDBMS : Relational Database Management System. Software that  is used to manages  database E.g MS Access, Base. Oracle, MySQL./Relation: table is called Relation/  Attribute: Field /Column/ headers in table containing one type or peace of information/Record/Row/Tuple: Row in a table . Record contain a complete unit of information about a person or thing. /Redundancy : duplication  of data/Data Inconsistency : Multiple mismatch copy of same data/( .odb)  : the file extension of base database is .odb

Primary key:  Column that can uniquely identify  record in a table /This key can never be duplicated and NULL. /Composite Primary key: Primary key  that composite of   two or more columns/Alternative keys : All the candidate keys other than the primary key of a relation /Candidate keys: Primary keys and alternative keys together are called the candidate keys. Attributes of a table which can serve as a primary key are called candidate keys. /Foreign key : Non key attribute that is defined as primary key in another table called foreign key. This key is used to enforce referential integrity/

Referential integrity refers to the relationship between tables. Four features of referential integrity are: (a) It is used to maintain the accuracy and consistency of data in a relationship. (b) It saves time as there is no need to enter the same data in separate tables. (c) It reduces data-entry errors. (d) It summarizes data from related tables. 

SQL structured query language set of commands that is used to  create, manipulate and process the database. 

1. Data Definition Language (DDL) Commands: create, alter, drop, grant, revoke /2. Data Manipulation Language (DML)   Select, Insert, Delete, Update etc. /3. Transaction Control Language (TCL)

1. VIEW EXISTING DATABASE

To view existing database names, the command is : SHOW DATABASES ;

2. CREATING DATABASE IN MYSQL

For creating the database in MySQL, we write the following command :

CREATE DATABASE <databasename> ;

e.g. In order to create a database Student, command is :

CREATE DATABASE Student ;

3. ACCESSING DATABASE

For accessing already existing database , we write :

USE <databasename> ;

e.g. to access a database named Student , we write command as :

USE Student ;

4. DELETING DATABASE

For deleting any existing database , the command is :

DROP DATABASE <databasename> ;

e.g. to delete a database , say student, we write command as ;

DROP DATABASE Student ;

5. VIEWING TABLES IN DATABASE

In order to view tables present in currently accessed database , command is : SHOW TABLES ;

CREATE TABLE: 

CREATE TABLE employee 

(ECODE int , ENAME varchar(20) , GENDER char(1) , GRADE char(2) , GROSS int ) ;

Inserting record: 

INSERT INTO employee VALUES(1001 , ‘Ravi’ , ‘M’ , ‘E4’ , 50000); 

SELECT Records 

To select only ENAME, GRADE and GROSS column from student table 

SELECT ENAME , GRADE , GROSS FROM EMPLOYEE ;

/Select all records from table with all columns :SELECT * FROM student ;

To display details of employee whose name ends with ‘y’. 

SELECT * FROM EMPLOYEE WHERE ENAME LIKE ‘%y’ ;

To display names of employee whose name starts with R in EMPLOYEE table, the command is : 

SELECT ENAME FROM EMPLOYEE WHERE ENAME LIKE ‘R%’ ;

To display details of employee whose second character in name is ‘e’

 SELECT * FROM EMPLOYEE WHERE ENAME LIKE ‘_e%’ ; 

To display details of employee whose  grade value is A1 or A2 : 

SELECT * FROM EMPLOYEE  WHERE GRADE IN (‘A1’ , ‘A2’); 

To display ECODE, ENAME and GRADE of those employees whose salary is between 40000 and 50000, command is:

SELECT ECODE , ENAME ,GRADE FROM EMPLOYEE WHERE GROSS BETWEEN 40000 AND 50000 ;

To display the details of employees in EMPLOYEE table in alphabetical order, we use command :

SELECT * FROM EMPLOYEE ORDER BY ENAME ;

Display list of employee in descending alphabetical order whose salary is greater than 40000.

SELECT ENAME FROM EMPLOYEE 

WHERE GROSS > 40000 ORDER BY ENAME desc ;

UPDATE Records

Change the salary of employee in EMPLOYEE table having employee code 1009 to 55000. 

UPDATE EMPLOYEE SET GROSS = 55000 WHERE ECODE = 1009 ;

 Update the salary to 58000 and grade to B2 for those employee have employee code is 1001.

UPDATE EMPLOYEE SET GROSS = 58000, GRADE=’B2’ WHERE ECODE = 1001 ; 

Double the salary of employees having grade as ‘A1’ or ‘A2’ . 

UPDATE EMPLOYEE SET GROSS = GROSS * 2 WHERE GRADE=’A1’ OR GRADE=’A2’ ;

DELETE RECORDS

To remove the details of those employee from EMPLOYEE table whose grade is A1.  

DELETE FROM EMPLOYEE WHERE GRADE =’A1’ ;

To delete all records from employee table : 

DELETE FROM EMPLOYEE ;

ADD COLUMN

To add a new column ADDRESS to the EMPLOYEE table, we can write command as :
 ALTER TABLE EMPLOYEE ADD ADDRESS VARCHAR(50);

In table EMPLOYEE , change the datatype of GRADE column from CHAR(2) to VARCHAR(2). 

ALTER TABLE EMPLOYEE MODIFY GRADE VARCHAR(2);

In table EMPLOYEE , change the column ENAME to EM_NAME and data type from VARCHAR(20) to VARCHAR(30).

ALTER TABLE EMPLOYEE CHANGE ENAME EM_NAME VARCHAR(30);

DELETE COLUMN

To delete column GRADE from table EMPLOYEE, we will write :

     ALTER TABLE EMPLOYEE DROP GRADE ;

GROUP BY :Clause is used with SELECT to divide the table into groups. Groups can be done by a column name. If you wish to perform calculations on that column. You can use AGGRIGATE FUNCTIONS SUCH AS COUNT, SUM, AVG, MAX, MIN on columns etc., functions on the grouped column. Aggregate function produces a value for each group.

Count no of students class wise

Select class, count(*) from student group by class;

The HAVING Clause: is used to give condition with GROUP BY clause. The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

Count no of students class wise where no of students greater then 5;

Select class, count(*) from student group by class having count(*)>5;

keywords or Operators are used to specify Criteria in SQL Query ?

LIKE, NOT LIKE, IN, NOT IN, AND,OR , NOT, BETWEEN, >,>=,<.<=,!= etc

What do you mean by Aggregate Functions ?

They produce aggregate values of records. Functions produce a single value from a set of values. They are also called summary functions. These functions are used in summary Queries to group data in Queries

Some Functions are :COUNT, SUM, MAX,MIN,AVG

Constraints: conditions that can be enforced on the attributes of a relation. The constraints come in play whenever we are trying to insert, delete or update a record in a relation// Unique :Means that the values under that column are always unique. E.g.Roll_no number (3) unique /Primary key :  means that a column cannot have duplicate values and not even a null value. e.g. Roll_no number (3) primary key / The main difference between unique and primary key constraint is that a column specified as unique may have null value but primary key constraint does not allow null values in the column. /Check constraint : limits the values that can be inserted into a column of a table. e.g. marks number(3) check(marks>=0) The above statement declares marks to be of type number and while inserting or updating the value in marks it is ensured that its value is always greater than or equal to zero. /Default constraint :Is used to specify a default value to a column of a table automatically. This default value will be used when user does not enter any value for that column. e.g. balance number(5)default = 0

JOIN ::Clause is used to combine rows from two or more tables, based on a related/common column between them.  The join clause is used to combine tables based on a common column and a join condition.

Cross join (UNRESTRICTED JOIN) OR CARTISIAN JOIN : This query will give you the Cartesian product i.e. all possible concatenations are formed of all row sof both the tables EMPL and DEPT. Such an operation is also known as Unrestricted Join. It returns

N1 x N2 rows where N1 is number of rows in first table and N2 is number of rows in second table.

It returns  c1+c2 columns where c1 is numbers of columns in first table and c2 is number of columns in second table  select * from emp,dept;

An equi join is a type of join that combines tables based on matching values in specified columns. The resultant table contains repeated columns. It is possible to perform an equi join on more than two tables.

The join in which columns are compared for equality, is called Equi - Join. In equi-join, all the columns from joining table appear in the output even if they are identical.

e.g. SELECT * FROM empl, dept

WHERE empl.deptno = dept.deptno ;

 The SQL NATURAL JOIN is a type of EQUI JOIN and is structured in such a way that, columns with the same name of associated tables will appear once only.

 /data type:INT: to store integer values/DECIMAL: to store  real or decimal values with  exact precision./CHAR: to store fixed length  string with a maximum size of 255./VARCHAR: to store variable length string a maximum size of 65,536./DATE: store date values in the format YYYY-MM-DD/DATETIME: store combined date/time values in the YYYY-MM-DD HH:MM:SS format/Binary type: used  to store binary data  files such as music,image and video files./  Sorting is a process of arranging records in ascending or descending order on the basis of one or more columns . Sorting makes data easier to understand, analyse and visualise /Relationship option in Tools menu is used to create relationship among tables. There are three type of relation one to one, one to many and many to many/Aggregate(summary) Functions : Functions produce a single value from a set of values.  Some Functions are :COUNT, SUM, MAX,MIN,AVG/ 

DISTINCT:  keyword is used to remove duplicate column values from table E.g SELECT DISTINCT city from student:/WHERE:  used with SELECT command to specify the condition  in  SQL Query  

BETWEEN:  The BETWEEN operator defines a range of values The range includes both lower value and upper value.

e.g. to display ECODE, ENAME and GRADE of those employees whose salary is between 40000 and 50000, command is: 

SELECT ECODE , ENAME ,GRADE 

 FROM EMPLOYEE 

WHERE GROSS BETWEEN 40000 AND 50000 ; 

LIKE:

LIKE operator is used for pattern matching in SQL. Patterns are described using two special wildcard characters:

 1. percent(%) –The % character matches any substring.

 2. Underscore ( _ ) –The _ character matches any character. 

e.g. to display names of employee whose name starts with R in EMPLOYEE table, the command is :  SELECT ENAME  FROM EMPLOYEE  WHERE ENAME LIKE ‘R%’ ; 

IN : To specify a list of values, the IN operator is used. The IN operator selects values that match any value in a given list of values. E.g. 

SELECT * FROM EMPLOYEE 

     WHERE GRADE IN (‘A1’ , ‘A2’);

NULL:  column having no value is said to have NULL value.

ORDER BY : clause is used to order the records in Ascending or descending order. ASC keyword for ascending and DESC keyword is used for descending order

DESC OR describe: To show the structure of table DESC  STUDENT

Components of table:

Data Item/Field/Column/Attribute: Column headers containing one type of information /Record/Row/Tuple: Row in a table . Record contain a complete unit of information./Primary key:  Primary key is a key that can uniquely identifies the records /tuples in a relation. This key can never be duplicated and NULL. /Composite Primary key: Primary key  that consist  of combination of  two or more columns /Alternative keys : The columns that have the unique  values for each record but are not selected as the primary keys are called alternative keys .All the candidate keys other than the primary key of a relation 

Candidate keys: Primary keys and alternative keys together are called the candidate keys. In other words all attributes that are eligible to be a primary key , are called candidate keys. This key can never be duplicated and NULL. For example rollno field can be primary key for a table student. Attributes of a table which can serve as a primary key are called candidate keys. 

Basically the process of transfer data between python programs and MySQL database is known as Python Database Connectivity. There few steps you have to follow to perform Python Database Connectivity. These steps are as follow:

1.Import the required packages 2.Establish a connection 3.Execute SQL command

4.Perform operations as per the requirements

import mysql.connector as msql

cn=msql.connect(host='localhost',user='root',passwd=‘ ',database=‘jnvchandigarh')

cur=cn.cursor()

cur.execute("insert into students values(1111,'Asmita',78.50,'B1')

cn.commit()

Difference between fetchone()/fetchmany() and fetchall():  They all are method of cursor object 1.fetchall(): It will retrieve all records from a database table as tuple .

2.fetchone(): It will retrieve one record from the resultset as a tuple or a list. It returns the records in a specific order like first record, the next time next record and so on. If records are not available then it will return None.

3.fetchmany(): It will retrieve n number of records from the database. If records are not available then it will return an empty tuple.

4.rowcount: It is one of the properties of cursor object that return number of rows fetched from the cursor object.

TABLE ALIAS- A table alias is a temporary label given along with table name in FROM clause. e.g.

SELECT E.DEPTNO, DNAME,EMPNO,ENAME,JOB,SAL

FROM EMPL E, DEPT D

WHERE E.DEPTNO = DEPT.DEPTNO

ORDER BY E.DEPTNO;

SELECT QUERY

qry="select * from student" mycursor.execute() records=mycursor.fetchall()

 for i in records:

print(i)

 INSER QUERY

qry="insert into student values(2,'VIJAY',17,'CHANDIGARH') "

 mycursor.execute(qry)

mydb.commit() 

print(mycursor.rowcount,"record inserted")

 UPDATE QUERY

roll=int(input("enter ROLLNO of student")) 

age=int(input("enter new age")) data=(age,roll)

qry="update student set age=%s where rollno=%s"

 mycursor.execute(qry,data)

mydb.commit() 

print(mycursor.rowcount,"Record updated")

UPDATE QUERY

qry="update student set age=25 where rollno=2 " 

mycursor.execute(qry)

mydb.commit() 

print(mycursor.rowcount,"Record updated")

DELETE QUERY

qry="delete from student where name='vinay' "

 mycursor.execute()

mydb.commit() 

print(mycursor.rowcount,"Record deleted")

Parametrize Queries

INSERT QUERY

rollno=int(input("enter your rollno")) 

nm=input("enter name of student") 

age=int(input("enter age")) 

cty=input("enter your city")

 data=(rollno,nm,age,cty)

qry="insert into student values(%s,%s,%s,%s)“

mycursor.execute(qrydata)

mydb.commit()

print(mycursor.rowcount,"Record updated")