1 / 23

WELCOME TO DATABASE and SQL

WELCOME TO DATABASE and SQL. By Arun Kumar, PGT CS K.V. No.-1, 2 nd shift, Bhubaneswar. What is Database ?. A database is the collection of data in an organised manner. What is the difference between Database and Databank ?. A database also can be called as a databank Insertion

hei
Télécharger la présentation

WELCOME TO DATABASE and SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. WELCOME TO DATABASE and SQL By Arun Kumar, PGT CS K.V. No.-1, 2nd shift, Bhubaneswar Arun Kumar, PGT CS K.V. No.-1, BBSR

  2. What is Database ? A database is the collection of data in an organised manner Arun Kumar, PGT CS K.V. No.-1, BBSR

  3. What is the difference between Database and Databank ? A database also can be called as a databank Insertion Updation Deletion Selection Arun Kumar, PGT CS K.V. No.-1, BBSR

  4. What is Database Management System (DBMS) ? DBMS = Database + Programs to access those data Arun Kumar, PGT CS K.V. No.-1, BBSR

  5. Advantages of DBMS • Centralisation of Data • Data Independence • Reduction of Data redundancies • Shared Data • Data Integrity • Data Security • Conflict Resolution • Removes Data isolation • Atomicity Arun Kumar, PGT CS K.V. No.-1, BBSR

  6. Disadvantages of DBMS • Cost : S/W Development and H/W upgradation • Processing overhead to implement security, integrity and sharing of the data degrades the response time • Lack of duplication • Centralisation causes downtime and failure • Backup and restore Arun Kumar, PGT CS K.V. No.-1, BBSR

  7. View of DataDATA ABSTRACTION View 1 View 2 View 3 View n Conceptual Level Database Administrator Physical Level Database Creator Arun Kumar, PGT CS K.V. No.-1, BBSR

  8. View of DataInstances and Schema • Instance : The collection of information stored in the database at a particular moment is called an instance. • Schema : The overall design of the database is called the database schema Lowest Level : Physical Schema Intermediate Level : Logical Schema Highest Level : Subschema Arun Kumar, PGT CS K.V. No.-1, BBSR

  9. View of DataData Independence Ability to modify the schema definition at one level without affecting a schema definition in the next higher level is called as data independence • Physical Data Independence : Ability to modify the physical schema without affecting the other levels • Logical Data Independence : Ability to modify the conceptual schema without affecting the other levels. Arun Kumar, PGT CS K.V. No.-1, BBSR

  10. Data ModelsA data model is the collection of conceptual tools for describing data,data relationships, data semantics and consistency constraints • Object Based Logical Models • Entity-Relationship Model • Object-oriented Model • Semantic Data Model • Functional Data Model Data Models • Record Based Logical Models • Relational Model • Network Model • Hierarchical Model • Physical Data Models • Unifying Model • Frame Memory Model Arun Kumar, PGT CS K.V. No.-1, BBSR

  11. Database Administrator DBA has the central control of the database. The functions of the DBA area • Schema definition • Creating/deleting the database users • Giving authorization to access the database • Acquiring s/w and h/w resources as needed • Looking after the security of the database • Performance of the system (poor response time) Arun Kumar, PGT CS K.V. No.-1, BBSR

  12. Database Users • Application Programmers : Writing application programs with embedded statements. • Sophisticated Users : Who thoroughly knows the details of facilities of the DBMS Ex : Engineers,Scientists,Business Analyst etc. • Specialized Users : Sophisticated users who write specialized database applications • Naive Users : Unsophisticated users who use some specific portion of the database but use it constantly. • Casual Users : occasionally use the database Arun Kumar, PGT CS K.V. No.-1, BBSR

  13. Database Languages • Data Definition Language (DDL) : Structuring the database • Data Manipulation Language (DML) : Using the database. • Data Control Language (DCL) : Securing the database. Arun Kumar, PGT CS K.V. No.-1, BBSR

  14. Relational Database Languages Relational Database languages provide notations for deriving information from the permanent relations in the database. • Relational Algebra : Specialized operators are used. • Relational Calculus : Based on the predicate calculus Arun Kumar, PGT CS K.V. No.-1, BBSR

  15. Relational Algebra STUDENT(S#,SNAME,LEVEL)COURSE(C#,TITLE,LNAME) REPORT(S#,C#,MARKS) LECTURER(LNAME,ROOM#) Arun Kumar, PGT CS K.V. No.-1, BBSR

  16. The SELECT Operation SELECT operation is an unary operation and is used to select a subset of the tuples from a relation that satisfy the selection condition Syntax : <selection condition> (R) Sigma -> used to denote the SELECT operator <selection condition> -> is a boolean expression. Arun Kumar, PGT CS K.V. No.-1, BBSR

  17. Query-1 Retrieve from the STUDENT relation the tuples for those students having level >= 2 Result : LEVEL>=2(STUDENT) Arun Kumar, PGT CS K.V. No.-1, BBSR

  18. Query-2 Retrieve tuples from REPORT for Course code is 312 and marks less than 50 or Course code 121 and marks greater than 50 Result :  (REPORT) (C#=312 AND MARKS<50) OR (C#=121 AND MARKS>50) Arun Kumar, PGT CS K.V. No.-1, BBSR

  19. The PROJECT Operation It is a unary operator which projects out a vertical subset of a given relation Syntax : <attribute list> (R) Query : Retrieve the room numbers of lecturers Result : ROOM# (LECTURER) Arun Kumar, PGT CS K.V. No.-1, BBSR

  20. The RENAME Operation When sequence of operations are exercised on the relations, then in many cases, the relation names and attribute names are renamed Query : Rename the STUDENT(S#,SNAME,LEVEL) to LEARNER (ROLL,NAME,DEGREE) Result : LEARNER(ROLL,NAME,DEGREE) (STUDENT) Arun Kumar, PGT CS K.V. No.-1, BBSR

  21. Set Theoretic Operation • UNION : The set of tuples that are in relation R or in relation S, or in both is denoted by R U S or (R union S) • INTERSECTION : The set of tuples that are in R and also in S and is denoted by RS or (R intersect S) • SET DIFFERENCE : The set of tuples that are in R but not in S is denoted by (R minus S) or R-S Arun Kumar, PGT CS K.V. No.-1, BBSR

  22. CARTESIAN PRODUCT Let R and S be relations of degree n1 and n2 respectively. Then the Cartesian Product of R and S, is of degree n1+n2. It is denoted by R X S Suppose relation R is R(A1,A2,…,An) and relation S is S(B1,B2,….,Bm) then Q=R X S is Q(A1,A2,…An,B1,B2,….Bm) If R has k1 tuples and S has k2 tuples then R X S will have k1*k2 tuples. Arun Kumar, PGT CS K.V. No.-1, BBSR

  23. A complete set of Relational Algebra Operations The following set of relational algebra operations {,,,-,X} is a complete set. Any other relational algebra operations can be expressed using the sequence of operations from the above set. Arun Kumar, PGT CS K.V. No.-1, BBSR

More Related