Unit III: Database Management (20 marks)
Database concepts: introduction to database concepts and its need
● Relational data model: relation, attribute, tuple, domain, degree, cardinality, keys (candidate key, primary key, alternate key, foreign key) /● Structured Query Language: introduction, Data Definition Language and Data Manipulation Language, data type (char(n), varchar(n), int, float, date), constraints (not null, unique, primary key), create database, use database, show databases, drop database, show tables, create table, describe table, alter table (add and remove an attribute, add and remove primary key), drop table, insert, delete, select, operators (mathematical, relational and logical), aliasing, distinct clause, where clause, in, between, order by, meaning of null, is null, is not null, like, update command, delete command, aggregate functions (max, min, avg, sum, count), group by, having clause, joins: Cartesian product on two tables, equi-join and natural join /● Interface of python with an SQL database: connecting SQL with Python, performing insert, update, delete queries using cursor, display data by using connect(), cursor(), execute(), commit(), fetchone(), fetchall(), rowcount, creating database connectivity applications, use of %s format specifier or format() to perform queries
(5 marks)
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. /Degree: No of columns in a table/ Cardinality: No of records in a table/ Redundancy : duplication of data/Data Inconsistency : Multiple mismatch copy of same data /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./ It can accept NULL values/We can have more then one foreign key in a table but must have only one primary key / duplicate value allowed in foreign key / This key is used to enforce referential integrity/ Referential integrity refers to the relationship between two or more 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. /1. Data Definition Language (DDL) Commands: create, alter, drop, /2. Data Manipulation Language (DML) Select, Insert, Delete, Update etc. /3. Transaction Control Language (TCL): grant, revoke ,commit/
JOIN ::Clause is used to combine rows from two or more tables, based on a common column between them and a join condition.
Cross join (UNRESTRICTED JOIN) OR CARTISIAN JOIN (A*B) : This query will give you the Cartesian product i.e. all possible concatenations are formed of all row from 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; /
The join in which columns are compared for equality, is called Equi - Join. The resultant table contains repeated columns. It is possible to perform an equi join on more than two tables./
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.
(5 marks)
fetchone()/fetchmany() and fetchall() , rowcount : They all are method of cursor object 1.fetchall(): It fetch all records or rows from the result set as list of tuples . /2.fetchone(): It fetch next row or record(one record at a time) from the resultset as a tuple or a list. If records are not available then it will return None./3.fetchmany(): It will fetch next 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/
Import the required packages
To perform the Python MySQL Database Connectivity you need to install mysql-connector-python or pymysql package using pip command
Python Database Connectivity :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()
Display all records from table student
qry="select * from student"
mycursor.execute()
records=mycursor.fetchall()
for i in records:
print(i)
INSERT QUERY(ADD RECORDS)
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(qry,data)
mydb.commit()
print(mycursor.rowcount,"Record updated")
UPDATE Record
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")