DATABASE CONCEPT Class XI Presented by Dinesh Patel

DATABASE CONCEPT Class XI Presented by : Dinesh Patel PGT [Comp. Sc. ] KV IIT Powai

Introduction Database is the collection of interrelated data / tables. Database Tables Database Management System : S/w that manage data on computer for proper utilization on Computer. Fields Data Character

Role of Database To reduce Data Redundancy To Control Data Inconsistency To Share Data among different applications. To enforce Standards [ date format, number To Secure Data [ Password protection, required facility] To maintain Integrity [ check validity of data] formats]

Database Abstraction Providing only required data to the user. Student_Info Admn. No. Student_Info Name Class DOB Father’s Name Mother’s Name Address Father’s Occupation Category Last School Name Abstraction Class DOB Father’s Name Category

Various Level of Database Implimantation Admn_No Name int char float Admn_No Name [30] Fee [ 02 bytes, [30 bytes, [04 bytes, External / View Level Conceptual Level Internal / Physical Level Location : FF 00101 F ] Location : F 0101 F 01 ] Location : F 001 F ]

Concept of Data Independance Changing the Data definition at any one level will not affect to another level called Data Independance. It is of Two Types Physical Data Independence [ Changing on Physical Level not effect to Conceptual Level. ] Logical Data Independence [ Changing on Conceptual Level not effect to Physical Level. ]

Data Models External and Conceptual Level use certain Data Structure to use database efficiently with its all support. To manage the database some structures are described called Database Model. It is of Three types : Relational Data Model Network Data Model Hierarchical Data Model

Relation Data Model In this model Data is being stored in form of Rows and Columns. The collection of Row & Columns called Table / Relation. Fields Admn_no Name Class DOB Sex 1212 Kapil XII 12/05/1980 M 1587 Seema XII 19/12/1982 F 2145 Kiran XI 25/09/1990 F 1385 Chaman XI 14/02/1992 M 1141 Anubhav VI 02/11/1999 M Record

Network Data Model The collection of Records connected to one other with the Links. The structure known as Arbitrary Graph. 1254 Seema Singh 12/05/1990 F Link XII Father_Name Science Mother_Mane

Hierarchial Data Model The collection of Records connected to one other with the Links. The structure known as Tree. 1254 Seema Singh 12/05/1990 F Link XII Science Father_Name Mother_Mane

Comparision of Data Models Comparison Key Relational Data Model Network Data Model Hierarchical Data Model Relationship of Data Rows and Columns By Links Structure Table Arbitrary Graph Tree

Database Terminoogies Relation or Tuple : It is the collection of data in form of Rows & Columns. Domain : It is the Database server. Attribute : The columns /Field of table. Degree : Number of Columns in a table. Cardinality : Number of records in a table. Views : (i) Virtual table that does not really exist. (ii) Created from more than one table (iii) Does not occupies any memory.

Keys Primary Key : Key or the combination of Keys that can identify a record uniquely. Candidate Key : The Key or the Combination of Keys that can serve as primary key for a Table is called Candidate Key. Alternate Key : In case of More than one candidate key which is not selected as Primary key, is called Alternate Key. Foreign Key : The primary key of a parent table which is derived in to child table [One to Many ] called Foreign table.

Candidate Key Admn_ Name no Both can be Primary Key Roll_ no Class DOB Sex 1212 Kapil 1 XII 12/05/1980 M 1587 Seema 2 XII 19/12/1982 F 2145 Kiran 3 XI 25/09/1990 F 1385 Chaman 4 XI 14/02/1992 M 1141 Anubhav 5 VI 02/11/1999 M

Alternate Key Primary Key Admn_ Name no Roll_ no Class DOB Sex 1212 Kapil 1 XII 12/05/1980 M 1587 Seema 2 XII 19/12/1982 F 2145 Kiran 3 XI 25/09/1990 F 1385 Chaman 4 XI 14/02/1992 M 1141 Anubhav 5 VI 02/11/1999 M

Primary Key Foreign Key Admn_ Name no Roll_ no Class DOB Sex 1212 Kapil 1 XII 12/05/1980 M 1587 Seema 2 XII 19/12/1982 F 2145 Kiran 3 XI 25/09/1990 F 1385 Chaman 4 XI 14/02/1992 M 1141 Anubhav 5 VI 02/11/1999 M Admn no Subject Marks 1212 Physics 80 1212 Chemistry 85 1385 Physics 95 1385 CS 99 1385 Chemistry 90 1212 CS 90 1212 Maths 85 Parent Table OR Master Table Child Table OR Detail Table

Relational Algebra Relation algebra is a collection of operation on relation that works on relation and produce result as relation. Operations are of following Types Select Project Cartesian Product Union Difference Intersections etc.

'Select' Operation Horizontal subset of table selection Where Condition Select All Table Name σ price > 20. 00 (item) Select all from item table where price > 20. 00. ITEM Qty Price Item no 1212 Pencil 12 10. 50 1587 Note Book 25 25. 00 2145 Pen 35 12. 00 1385 Ink 4 18. 50 1141 Book 15 50. 55 Item no Name Qty Price 1587 Note Book 25 25. 00 1141 Book 15 50. 55 Name

'Project' Operation Vertical subset of table selection Where Condition Select All Table Name π Name, Price (item) Select Name and Price from item table. ITEM Item no Name Qty Price 1212 Pencil 12 10. 50 1587 Note Book 25 25. 00 2145 Pen 35 12. 00 1385 Ink 4 18. 50 1141 Book 15 50. 55 Name Price Note Book 25. 00 Book 50. 55

'Cartesian Product' Operation Table Item x Shop Qty Price Pencil 12 10. 50 1587 Note Book 25 25. 00 2145 Pen 35 12. 00 Item no I_Name 1212 Shop no Result Item no S_Name 101 R. K. Stationary 502 Maya Books Address 5 A/12 M. G. 125, Uran No. of rows x No. of rows I_Name Qty Price Shop no S_Name 1212 Pencil 12 10. 50 101 R. K. Stationary 1212 Pincil 12 10. 50 502 Maya Books 1587 Note Book 25 25. 00 101 R. K. Stationary 1587 Note Book 25 25. 00 502 Maya Books 2145 Pen 35 12. 00 101 R. K. Stationary 2145 Pen 35 12. 00 502 Maya Books Address 5 A/12 M. G. 125, Uran

'Union' Operation 20 15 35 25 30 11 25 40 A B 20 15 35 25 11 30 40 AUB Result : 11, 15, 20, 25, 30, 35, 40

'Union' Operation Table Shop 1 U Shop 2 Shop 1 Shop 2 Item no I_Name Price 1212 Pencil 10. 50 101 Books 65. 00 1587 Note Book 25. 00 1212 Pencil 10. 50 2145 Pen 12. 00 Result Item no Common will display once only. I_Name Price 101 Books 65. 00 1212 Pencil 10. 50 1587 Note Book 25. 00 2145 Pen 12. 00 Shop 1 U Shop 2

'Intersection' Operation 20 15 35 25 30 11 25 40 A B 20 15 35 25 11 30 40 AПB Result : 25

'Intersection' Operation Table Shop 1 П Shop 2 Shop 1 Shop 2 Item no I_Name Price 1212 Pencil 10. 50 101 Books 65. 00 1587 Note Book 25. 00 1212 Pencil 10. 50 2145 Pen 12. 00 Result Common will display only. Item no I_Name Price 1212 Pencil 10. 50 Shop 1 П Shop 2

'Difference' Operation 20 15 20 35 25 30 11 25 40 A B 20 15 25 30 11 B 20 30 40 A-B Result : 11, 15, 20 25 40 A 35 25 11 15 35 25 11 30 40 B-A Result : 30, 35, 40

'Difference' Operation Table Shop 1 - Shop 2 Shop 1 Shop 2 Item no I_Name Price 1212 Pencil 10. 50 101 Books 65. 00 1587 Note Book 25. 00 1212 Pencil 10. 50 2145 Pen 12. 00 Result Item no I_Name Price 1587 Note Book 25. 00 2145 Pen 12. 00 Shop 1 - Shop 2

'Difference' Operation Table Shop 2 - Shop 1 Shop 2 Item no I_Name Price 1212 Pencil 10. 50 101 Books 65. 00 1587 Note Book 25. 00 1212 Pencil 10. 50 2145 Pen 12. 00 Result Item no I_Name Price 101 Books 65. 00 Shop 2 - Shop 1

Normalization Process to : Reduce data redundancies Eliminate data anomalies Produce controlled redundancies to linked table

Types of Normalization 1. First Normal Form (1 NF) • All attributes should have atomic values • No repeating group in a table • All attributes dependent on primary key 2. Second Normal Form (2 NF) • In 1 NF • No partial dependencies • Transitive in nature 3. Third Normal Form (3 NF) • In 2 NF • No Transitive in nature

Working of DBMS 1. Front End Back End 2. Back End Front End Connection 3. Connection

Thanks…………. Prepared & Presented by : Dinesh Patel PGT [CS] KV Karanja