SQL Session 1 - Epitome IT Coaching

Contains SQL Introduction, Codd's Rule and Normal forms

Transcript

1. Structured Query Language Epitome Coaching Classes
2. Course Contents • Day-1  Introduction to DBMS  The concept of normalized database  SQL Statements – what & how it works?  SQL Processing Architecture
3. Introduction to DBMS
4. 1. Introduction to DBMS • What is a database? • A database can be defined as a collection of coherent, meaningful data. • What is a DBMS? • DBMS is a system which allows inserting,updating,deleting and processing of data.eg: Oracle,Sybase,MS Access
5. What is a RDBMS? • A RDBMS is a Database management system that is based on the relational model as introduced by Codd. • RDBMS Stores data in form of tables and they are powerful as only few assumptions are required to understand how data is related or how it is extracted.
6. RDBMS – Key Concepts • Primary Key: A column or a group of columns which define each row in a table uniquely is called the primary key. • Foreign Key: A column in one table that matches the primary key in some other table is called a foreign key. • Together a primary key and a foreign key creates a parent child relationship!
7. Codd’s 12 Rules • In 1985, Ted Codd presented 12 rules that a DBMS system must follow if it is considered to be a RDBMS. • Though in present world there is no RDBMS which follows all the 12 rules but its important to have a look to these rules which comes out of the Codd’s theoretical work on relational model.
8. Codd’s 12 rules – continue. 1. Information Rule: All information in a relational database is represented explicitly at the logical level and in exactly one way. 2. Guaranteed access rule: Each and every datum in a RDBMS is guaranteed to be access by combination of table name,primary key and column name.
9. Codd’s 12 rules – continue. 3. Systematic treatment of NULL values: NULL values are supported in a fully relational DBMS for representing missing information and inapplicable information in a systematic way. 4. Comprehensive data sublanguage rule: A RDBMS can support many languages but there must be at least one language whose statements are expressible per well defined standards.
10. Codd’s 12 rules – continue. 5. Dynamic online catalog based on relational model: database description is represented at the logical level in the same way as ordinary data. 6. View updating rule: All views that are theoretically updateable are also updateable by the system.
11. Codd’s 12 rules – continue. 7. High-level insert, update and delete: The capability of handling a base relation or a derived relation as a single operand applies not only to the retrieval of data but also to the insert, update & delete operation. 8. Physical data independence: Application programs & terminal activities remain logical unimpaired whenever any changes are made in either storage representations or access methods.
12. Codd’s 12 rules – continue. 9. Logical data independence: Application programs and terminal activities remain logically unimpaired when information- preserving changes of any kind that theoretically permit unimpairment are made to the base tables. 10. Integrity Independence: Integrity constraints specific to a particular RDBMS must be definable in the relational data sublanguage and storable in the catalog, not in the application programs.
13. Codd’s 12 rules – continue. 11. Distribution independence: A relational DBMS should have distribution independence. 12. Nonsubversion rule: if a relational system has a low level language, the low level language cannot be used to bypass the integrity constraints of high level language.
14. Normalization • It is a process that helps analysts or database designers to design the table structures for an application. • The focus of normalization is to attempt to reduce redundant table data. • The various levels of normalization varies from 1NF to 5NF
15. 1st Normal form • When a table is decomposed into two dimensional tables with all the repeating groups of data eliminated, the table data is said to be in 1st normal form.
16. 2nd Normal form • A table is said to be in 2nd normal form when each record in the table is in the first normal form and each column in the record is fully dependent on its primary key. • Hence, technically a table is in 2nd normal form if: if its in 1st normal form it includes no partial dependencies
17. 3rd Normal form • A table is said to be in the 3rd normal form when all the transitive dependencies are removed from this data • The table is in 3rd normal form if: its in 2nd normal form. • It contains no transitive dependencies.
18. Introduction to SQL • SQL is a language that provides an interface to relational database systems. • SQL was developed by IBM in 1970s for use in System R and is the de facto standard as well as an ISO and ANSI standard.
19. Functions of SQL • SQL is used to perform following functions for its users: – Data Definition : Define structure &organization of stored data. – Data Retrieval : Allows a user or an application program to retrieve stored data. – Data Manipulation : Update database by adding new data, deleting old data & modifying previously stored data. – Access Control : Restrict user’s ability to retrieve, add and modify data. – Data Sharing : Coordinate data sharing by concurrent users ensuring that they don’t interfere with each other. – Data Integrity : Defines integrity constraints in the database, protecting it from corruption.
20. 2. SQL Statements ANSI/ISO SQL: 1999 standards + database specific extensions
21. 3. SQL Processing Architecture • Parser • Optimizer • Plan Generation • Rows retrieval • Output
22. SQL Processing Architecture
23. END of Part1. SQL – EPITOME IT COACHING