Download Relational Database Management System Notes and more Study notes Computer science in PDF only on Docsity!
Notes for RDBMS(Session 2022-23) Database concepts: introduction to database concepts and its need Database A database may be defined as collection of interrelated data stored together. Purpose of database 1. Databases reduces redundancy. Redundancy means duplicacy. ae al controls inconsistency. By controlling redundancy, the inconsistency is also 3. Databases facilitates sharing of data. 4. Databases ensures data security. Security refers to protection of data against accidental or intentional disclosure to unauthorized person, or unauthorized modification or destruction. Relational data model: relation, attribute, tuple, domain, degree, cardinality, keys (candidate key.primary key, alternate key, foreign key) Relational Data Model In relational Model the data is stored in the form of relations, ie. tables. Tables is an object that consists of data in the form of attributes(columns) and tuples(rows). Some related terms of Relation: (Table) Tuples: rows in a relation contain the actual data itself. Attributes: Columns in a relation specify the type of data. Domain: Pool of values from which a column extracts the data . Degree: Total number of attributes in a relation. Cardinality: Total number of tuples in a relation. Relation: student details In the above example, the name of the ADMNO ] RNO | NAME T ADDRESS relation is student_details, degree is (total 101 rz ASE ASASDA cols, attribute} 4, and cardinality is (tota! - —— Renee rows, tuple) 4, RNO is a domain which 102} 2 | ALOK KKK contains the roll numbers of all the students 103 i3 CHAITANYA | KKJKJKJ in the relation. 104 4 FAIZAN LKLKJLK Key: specifies how rows in a relation are distinguished. Candidate Key: All attribute combinations inside a relation that can serve:as primary key are termed as candidate keys as they are the candidates for the primary key position: Primary Key:is a set of one or more attributes that can uniquely identify the tuples within the relation In some tables, combination of more than one attribute provides a unique value for each row. In such cases, the primary key consists of more than one attribute; it is called composite- primary key. : Alternate Key: a candidate key that is not a primary key is called alternate key. Relation: Stu_det In the above example, the name of the relation is stu_det. The [ADMNO] BNO | NAME [ADDRESS | keys ADMNO and RNO are the candidate keys. If ADMNO Is the 1 ASIF LASASDA _| | primary key the RNO becomes the altetnate key. (2 [ALOK _| KKIKJ _ [3 | CHAITANYA | KKIKIKI 4 | FAIZAN | LKLKILK_| Foreign Key: A non-key attribute, whose values are derived from the primary key of some other table, is known as foreign key in this current table. The table in which this non-key attribute ie. the foreign key exists, is called a foreign table or detail table,.and the table that defines the primary key exists, which the foreign key of detail table refers to, is called primary table or master table. Relation : stu_marks_ det f 1 the above example, the ratation stu det is | [bean | Marks1 | Marks2__| Marks3 tiesnacies tausiisn de ahics +100 70 tsa | the master celation in which ADNINO i the _{ 10 | [5 | | primary key. The AOMNO is referred to asa | 90 {98 || non-key attripute in another relation | 67 165 | | stu_marks det. Thus AOMNO in the relation | | as 56 | | stu_marks_det is known a 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 Structured Query Language(SQL) is the language that enables you to create and operation relational databases, which are the set of related information stored in tables. SQL contains set of commands and use it to create, retrieve, alter and transfer information stored in one or more tables of the database. Data Definition Lanquage(DDL) commands allows you to perform tasks related to data definition. Through these commands you can create, alter and drop schema objects(tables,databases,etc.) Eg.create table, alter table, drop table, create database, drop database Data Manipulation Language(DML) commands are used to manipulate data in existing schema objects. Eg.Select, Insert into, delete, update, show databases, show tables. Datatype: specifies what type of data can be stored in an attribute and associated operations for handling it. Char(n): fixed length string between 1 to 255 characters, right padded with spaces till the specified length. Specifying the length is not compulsory. By default it is 1 For eg. if a column name char(10) is defined , and user enters the value of length 4 bytes, the mysql stores space in the remaining bytes. le mysql ensures that the length if the column is 10 bytes. Varchar(n): variable length string between 1 to 255 characters, Specifying the length is compulsory. For eg. if a column name varchar(10) is defined , and user enters the value of length 4 bytes, then mysql frees the remaining memory. Int datatype can store values of width upto 11 digits. It may be signed or unsigned. !t does not allow to store decimal numbers Float(M,D): it can store decimal numbers of length M and precision D. M,D is not compulsory to give. Default is 10,2 ie total digits the column can hold is 10 and precision is upto 2 decimal places. Precision can go upto 24 places. Date: this datatype stores the date in ‘YYYY-MM-DD’ format. For eg 30th December, 1973 is stored as ‘1973-12-30' Constraints: it is the condition or the check applicable on the field or set of fields. There are two basic types of constraints. Column Constraints and Table constraints. Column constraints are applicable to individual columns, while Table constraints are applicable to group of one or more columns. Constraints are given while creating the table. not null: This constraint ensures that a given column of a table is never assigned the null value unique: this constraint ensures that no two rows have the same value for the specified column . show tables: command is use to view all the tables created in the current database. employee employees empsalary rows in set (0.00 sec) create table: command is used to create a tabe in a database. Syntax: Create table tablename( column1 datatype(size Imysql> create table employee(empid varchar(5), ename varchar(10), salary float(8,2)); Query OX, @ rows affected (0.08 sec) describe table: command is used to display the structure of the table. poe | -varchar(5) | Yes —| [NULL | ename=-| varchar(10) | YES | NULL |-salary-| float(8,2) ~|-veS [> | NULL alter table (add and remove an attribute, add and remove primary key) command is used to change the structure of the table. «It may add a new column or change the column name, its datatype, its size. e It may also add or drop the primary key constraint. To add a new column Syntax: Alter table tablename add columnname datatype(size), Eg. Alter table employee add phno char(5); mysql> alter table employee add phno varchar(5)3 Query OK, @ rows affected (0.02 sec) Records: @ Duplicates: @ Warnings: 2 mysql> desc employee 3 Frees | varchar(5) | ename_ -| varchar(1@) é | | salary.’ | float(8,2) | phno; | varchar(5) om moe: span m errors 4 rows in set (@.00 sec To change the datatype and size of the existing column Syntax: Alter table tablename modify columnname datatype(size); Eg. Alter table employee modify phno char(20); mysql> Alter table employee modify phno char(20); Query OK, @ rows affected (0.02 sec) Records: @ Duplicates: @ Warnings: mysql> desc employee; on oe -n---e or | Null | Key | Default | Extra | watenceeee Se i on varchar(5) | YES varchar(10) | YES float(8,2) | YES char(20) | Yes Wh 4 rows in set (@.08 sec) To change the column name Syntax: Alter table tablename change oldcolumnname newcolumnname datatype(size); #datatype(size) is compulsory. Eg. Alter table employee change phno phonenumber char(10); imysql> Alter table employee change phno phonenumber char(10); Query OK, @ rows affected (0.02 sec) Records: @ Duplicates: @ Warnings: @ mysql> desc employee; [-varchar(5)~- | YES. -| varchar(10)_ | YES $91 | float(8,2)--| YES | phonenumber | char(4@) =~ --| “YES To add the primary key constraint Syntax: Alter table tablename add primary key(column name) Eg. Alter table employee add primary key(empid), mysql> Alter-table employee. add primary key(empid); queryOK, @ rows affected (0.02 sec) z Records: @. Duplicates: @ Warnings: @ Sask. efault | Extra | “yvarchar(5) varchar(10)* | “YES float(8,2) | YES chan(1@) | YES 4 rows in set (@.@2 sec) To remove the primary key constraint Syntax: Alter table tablename drop primary key Eg. Alter table employee drop primary key; Select: command is used to display the contents of the table(s) Syntax: Select column list from tablename where condition. is written in column list section if we want to display all the columns of the table(s) . bidbie clause is used to apply condition for the rows in the table. Only those rows will be Isp! jayed which matches the condition given in where clause. Eg. Select * from employee where salary >10000; ename from employee; went | Aditya | | Abhay | | Anish mysql> select .* from employee; bea ee Ho eee non een ener ann e-- + | empid-| ename= | salary | phonenumber | eee noone | Aditya | 1¢0000.0@ | 1004200 | Abhay ~ | 12¢000.0@ | 11114200 | Anish 10000.00. | 145698200 pees ee ee -| Anish | 10090.00 | 145698200 Operators: Operators are used to specifying a condition in a statement in MySQL. Mathematical Operators: The MySQL mathematical Operators are used to perform Arithmetic operations on column data such as Addition, Subtraction, Multiplication, Division, and Modulus. The following table shows you the list of available MySQL Arithmetic operators. MySQL Arithmetic Operators Operation Example Addition Operator SELECT 10 + 2 = 12 Subtraction Operator SELECT 10-2=8 Multiplication Operator SELECT 10 * 2 = 20 Division Operator SELECT 10/2=5 DIV Integer Division SELECT 10/2 =5 SELECT 10% 2=0 % or MOD era Modulus 0) itor odulus OP SELECT 10 % 3 = mysql> select *,salary+salary*.10 "Incremented Salary" from employee; poe $--------. pence | empid | ename — 2 | Aditya | 1900¢0.e@ | 1004200 119000.00 | | Abhay 120¢00.00 | 11114200 132000.¢0 | | Anish 1¢900.e9 | 145698200 11000.60 | rt t-------- pan sann- 3 rows in set (@:@0 sec) | empid Fe ename | salary |. phonenumber | salary*12 as-5---- ets Sete 9 ~| Aditya | 100000.00 | 1004200 | 12¢0000.e@ | _| Abhay | 120000.0@ | 11114200 | 144¢000.e0 | _| Anish | 1000.00 | 145698200 | 120000.00 | poss aaa ee ae ae Sees. Br ent otra Gest es bar otl gS e ie + 3 rows in set (@.@@ sec) Relational: The relational operators in MySql are used to compare values between operands and return true or false according to the condition specified in the statement. eecae Penn nc eee s ese ey | empid | ename | Salary | phonenumber | Teassd-------- | empid ename | salary “ detente tet ed iz i Aditya | 100000.00 1 1004200 [52 | Abhay |
[email protected]@ | 11114200 |-Anish—_| 10000.00 | 145698200 = "Aditya'; Sarge apes | empid | ename | sala | phonenumber | —anH- apne at | Abhay i 120000.¢0 | 11114200 | Anish |. 1¢@00.00 | 145698200 aliasing: means iving another name to a particular column or the table only for output purpose. Aliasing a column: In column aliasing the alias having more than one word are enclosed within quotes. oe | salary | Aditya | 100000.¢@ | 1004200
[email protected] | | Abhay |-120000.¢0 | 11114200 132000.22 | | Anish | 10000.00 | 145698200
[email protected] | 3 rows in set (@.@0 sec) Aliasing a table: Select * from employee e, empsal s where e.eid=s.eid; mysql> Pelee’: * from employee e, salary em where e.empid=em.empid; oe eee trecceee ee eo ee See tence fanenn poneeeee beeeee ot | empid i name J depid | gender’ | qualification | evil [|-basic | da _ | hra_ | bonus |-total_sal ! ‘ele ta a eel ata ae eels lee eek Seo re oe Coreen 5 hate 1 | Deepa i 101 j F | Mca 6029 | 2000 | 2300 | 200 | 1050.00 a 2 {| Rajat | 1012 | ™ | BCA 2000 | 300 | 300 | 30 | 2630.00 | 3 {Hari | 102 | A. 1000 | 300 | 300 | 40 | 1649.00 | 4| Harry | 102 | “A, 1s0@ | 399 | 499| 30| 2410,00 | 5 | sumit |" 103 | M -Tech, 800g | 900 | 900 | 80] 9880.00 | 6 | Jyoti | ; Fy 3a0:| 490 | 89 | 10879.00 | [te tw wren nate nnn nnn Heccercn- hem ern een ene nn fnew anne 6 rows in set (0.00 sec) distinct clause eliminates duplicate row Syntax: SELECT DISTH S from the result of select statement NCT colum inname from TABLENAME MCA BCA B.A. M.A. B.Tech. from employee 5 3 rows in set- (2. i) sec) = where clause: in select statement is used to specify the criteria for selection of rows to be returned. When a where clause is encountered, the database program goes through the entire table one row at a time, if the condition for that row is true, it is displayed in the output, and the process continues for all other rows of the table. SELECT ~* FROM employee WHERE department = ‘IT’ AND age = 28; mysqi> select * from-employee where émpid!=1 and =Salary>s0000 or Salary>19000; | phonenumber i EE =p 3 | Aditya ‘CIEE 9 | 1004200 {| Abhay | 120000.00 | 11114200 | Anish 1¢000.00 | 145698200 2 ch ; ore by: clause is used to display the contents of the table either in ascending or descending eg. Select * from emplo ascending order of nam Select * from employe descending order of n yee order by name; #displays the content of the employee table in e& column. Itis by default. © order by name desc; #displays the content of the employee table in ame column. meaning of null, The NULL value means "no data." NULL can be written in any lettercase. We cannot perform any operation on NULL values. is null: the NULL value in a column can be searched in a table using IS NULL in the WHERE clause. Eg. Select * from emp where name is NULL; is not_null: IS NOT NULL condition is used to test for a NOT NULL value in a SELECT. INSERT, UPDATE, or DELETE statement. Eg. Select * from emp where name is NOT NULL; Like: command in select statement is used to perform pattern matching on strings. Patterns are described using two special wild card characters: Percent(%): matches any number of characters. Underscore(_): matches one single character. Eg. select * from emp where name like “%n%”; #displays the data where name contains n anywhere select * from emp where name like "n%"; #displays the data where name starts with n select * from emp where name like “%n”; #displays the data where name ends with n select * from emp where name like “_n%"; #displays the data where name contains n as second character. select * from emp where name like “__ _ _"; #displays the data where name contains 4 characters ; select * from emp where name like “_ _ _n”; #displays the data where name contains 4 characters and last character is n. update command: is used to change the contents of the table. Syntax: Update tablename Set column name= new value Where condition; delete command: is used to delete row(s) in the table. Syntax: Delete from Tablename where condition; L. Aggregate functions (max, min, avg, sum, count), group by, having clause, joins : Cartesian product on two tables, equi-join and natural join Aggregate functions: are those functions that work upon group of rows rather than single rows. Max: the function computes maximum value from the given data. Min: the function computes minimum value from the given data. avg: the function computes average of values of the given data. Sum: the function computes sum of values of the given data. Select max(salary),min(salary),sum(salary),avg(salary) from employee; Count: the function counts the total number of rows which matches the specific criteria. Select count(*) from employee;#displays total number of rows from the table employee Select count(*) from em name begins with N. Select count (distinct job) f - # di ignoring duplicate ilesinueee Ee eres ee ape roup by: clause i : Corkean ee ae osement is used to divide the entire table into smaller groups. co i i produces a value for each ie Rie OF Share gate hiner white: ire: esr ‘nein Eg. Select desig sum(sala ‘ 9, ty) from employee group by desig; having clause: is used to apply condition a each group. 5 Eg. Select desig sum(sala | 2 ty) from employee group by desig having count(*) >2; Select desig, sum(salary) from employee group by desig having desig="DIRECTOR’: ployee where name like’N%"; # displays total number of rows whose Cartesian Product tis the binary operation and is denoted by a cross(X). ie the cartesian product between two relations A and B can be denoted as AXB. The cartesian product yields a new relation which has a degree(cols) equal to the sum of number of degrees of the two relation. The number of tuples (cardinality) of the new relation is the product of the tuples of the two relations. All the tuples of the first relation is concatenated with all the tuples of the second relation to form the - 48 Relation: Stu_det new? LADMNO | RNO | NAME ADDRESS | Relation : stu_marks_det Pour . oD 101 a ASIF ASASDA 102 2 ALOK KKJK) ADMNO | Marks1 | Marks2_| Marks3 103 3 CHAITANYA | KKJKJKS 101 100 29 54 104 4 FAIZAN LKLKILK 102 89 90 98 The output of stu_det - stu_marks_det will be [ADMNo_| RNO NAME ADDRESS [ADMNO [Marksi__| Marks2__| Marks3 1101 1 ASIF ASASDA | 101 100 29 54 (101 1 ASIF ASASDA _ | 102 89 90 98 | 102 Fs ALOK KKIKI 101 100 29 54 | 402 2 ALOK KKIK 102 89 90 98 103 3 CHAITANYA | KKIKIKI | 102 100 29 54 103 3 CHAITANYA | KKIKIKI [102 89 90 98 | 104 4 FAIZAN LKLKJLK 101 100 29 54 | 104 Ea FAIZAN LKLKILK [102 89 90 98 Equi join : * Itis the condition which contains only equality condition. e The column according to which the tables are joined displays exactly same values. Gh lat chu. ane ” Depae> yy Dey ve? Cortvalityy? a o< & ee 1> select * from employees natural shy, wend de mpid | fname | Iname | addr i | benefits | designation | os he eee +. + Nee ie Gandhi nagar | 81250.¢0 | 1000.00 | Manager 440 US.' 110 | Upton —_ | 9375.00 | 12500.00 | Manag Join empsalary; + 105 ] harry | Waltor | 215 | Sarah. | Ackerman | k 24a | manila | Sengupta | 24 Friends street | Nenoelhi | 62500.00 | 1200.00 | Cle p= == === $ocnn eee ++ Ue ee ery a er ene peceecensen pita Interface of p update, nt, creating delete queries using Cursor, display data by using fetchone(), fetchall(), rowcou database connectivity applications ; ing insert, ython with an SQL database: connecting SQL with Python, perform Interface of python with an SQL database: connecting SQL with Python, performing insert, update, delete queries using cursor, display data by using fetchone(), fetchall(), rowcount, creating database connectivity applications
