1 / 60

RDBMS and SQL

RDBMS and SQL. Saras M Srivastva PGT (Comp. Sc) Kendriya Vidyalaya Tenga Valley, Dist. Kameng (W) Arunachal Pradesh – 790 116. What is a Database?. A collection of information generally stored on and accessed using a computer Databases generally are made up of various objects

kelsey-lamb
Download Presentation

RDBMS 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. RDBMS and SQL Saras M SrivastvaPGT (Comp. Sc)Kendriya VidyalayaTenga Valley, Dist. Kameng (W) Arunachal Pradesh – 790 116

  2. What is a Database? • A collection of information generally stored on and accessed using a computer • Databases generally are made up of various objects • These objects provides structured access to the information stored • In addition, databases usually contain meta-data—data about the data storedin the database Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  3. Purpose of Database • To Reduce Redundancy • To Control Data Inconsistency • To Facilitate Data Sharing • To Enforce Standards • To Ensure Data Security • To Maintain Data Integrity Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  4. Data Base Abstraction • Internal Level • Conceptual Level • External Level / View Lavel • Internal schema at the internal level to describe physical storage structures and access paths. Typically uses a physical data model. • Conceptual schema at the conceptual level to describe the structure and constraints for the whole database for a community of users. Uses a conceptual or an implementation data model. • External schemas at the external level to describe the various user views. Usually uses the same data model as the conceptual level. Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  5. View of Data An architecture for a database system

  6. Data Independance • Physical Data Independence: The capacity to change the internal schema without having to change the conceptual or view schema. • Logical Data Independence: The capacity to change the conceptual schema without having to change the view schema. Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  7. Data Models • A collection of tools for describing • data • data relationships • data semantics • data constraints • Entity-Relationship model • Relational model • Other models: • object-oriented model • semi-structured data models • Older models: network model and hierarchical model

  8. Entity-Relationship Model Example of schema in the entity-relationship model

  9. Entity Relationship Model (Cont.) • E-R model of real world • Entities (objects) • E.g. customers, accounts, bank branch • Relationships between entities • E.g. Account A-101 is held by customer Johnson • Relationship set depositor associates customers with accounts • Widely used for database design • Database design in E-R model usually converted to design in the relational model (coming up next) which is used for storage and processing

  10. Relational Model Attributes • Example of tabular data in the relational model customer- street customer- city account- number customer- name Customer-id Johnson Smith Johnson Jones Smith 192-83-7465 019-28-3746 192-83-7465 321-12-3123 019-28-3746 Alma North Alma Main North A-101 A-215 A-201 A-217 A-201 Palo Alto Rye Palo Alto Harrison Rye

  11. A Sample Relational Database

  12. Relational Model • Introduced by E.F. Ted Codd • Based upon “relational algebra” • Relations actually refer to what are more commonly referred to as tables • Tuples are rows • Columns are fields Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  13. Network Model • The Data is represented by collection of records and the relationship among the data are represented by links. • In a n/w database the collection of records are connected to one another by means of links. A record is a collection of fields, each of which contain only one data value. Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  14. Hierarchical Model • The Data is represented by collection on records and the relationship among the data are represented by links. • This is true of Hierarchical model. • Records are organized as tree rather than arbitrary graph. • It represents the relationship among the records through parent child relationship represented by tree. Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  15. What Is A DBMS? • A DBMS is a database management system • Software that allows for the creation and management of databases and their related components • SQL Server 2000, Oracle Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  16. Common DBMS Components • Databases • Tables • User-Defined Data Types • Indexes • Relations • Views • Stored Procedures Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  17. Terminology • Relation • Domain • Tuple • Attribute • Degree • Cardinality Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  18. Keys • Primary Key • Candidate Key • Alternate Key • Foreign Key Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  19. Relational Algebra • The Select Operation • The Project Operation • Cartesian Product • Union • Intersect • Set Difference Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  20. SQL • A standardized language used to retrieve and manipulate information in a database • Formally meant Structured Query Language Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  21. SQL as a DDL • DDL = Data Definition Language • SQL supports commands that allow the creation, alteration, or deletion of database objects • CREATE • ALTER • DROP Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  22. Data Modeling • Data modeling is the process of planning the logical design of your database • Use Data modeling to define the relationships between your data and processes Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  23. Normalization • Normalization is the process of using formal methods to separate data to be stored in a database into multiple, related tables • Normalization helps maintain the consistency and integrity of your data • Done primarily for on-line transaction processing (OLTP) systems • In contrast, on-line analytical processing (OLAP) systems are often heavily denormalized Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  24. Entity Integrity • Entities are single objects or concepts from the real world • Entity integrity means that you must be able to uniquely identify each entity you store in a database • Each entity in a table should have a primary key • This can be a single column or multiple columns • Multiple columns would be compositekey Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  25. Domain Integrity • Domain integrity restricts what can be stored in the database • Domain integrity represents your business rules • You can enforce them in your application • Either at the presentation layer or business logic layer (or both!) Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  26. Referential Integrity • Referential integrity preserves the defined relationships between tables when records are entered or deleted • Tables can have relationships based upon keys • Foreign keys and primary keys • Foreign keys and unique keys Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  27. User-Defined Integrity • Applies when a business rule does not fit into one of the three other forms • Implement using constraints, triggers, or stored procedures Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  28. Normal Forms • Codd defined various “normal forms”, or levels of normalization • Generally you want to get to at least third normal form Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  29. Denormalization • The deliberate process of introducing redundant data into your database • Generally done to increase application performance • Often occurs when one database is both a Transaction Processing and Decision Support System source Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  30. Databases • A database is logically a collection of components and data • Physically represented by one or more operating system files • Use the CREATE DATABASE command to define new databases Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  31. Tables • Tables are database objects that contain all of the data in a database • Tables have one or more columns • Each column has a specific data type • Tables have zero or more rows • Define using CREATE TABLE command Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  32. Indexes • Indexes provide a way to access data in a table quickly • Indexes can be applied to one or more columns in a table or view • Create using the CREATE INDEX command • Can also be created as part of table creation Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  33. Index Characteristics • Clustered versus nonclustered • Unique versus non-unique • Single-column versus multi-column • Ascending or descending order on the columns in the index • Covering or non-covering Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  34. Views • Views are virtual tables whose contents are defined by a query • Views are defined using SQL SELECT statements • JOINS are used to bring data from more than one table together • SQL Server supports indexed views Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  35. Stored Procedures • Stored procedures are SQL programs stored in your database • Stored procedures can increase the data access and manipulation performance of your application • Stored procedures are more secure than dynamic SQL execution • Define using the CREATE PROCEDURE command Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  36. Constraints • Rules that enforce entity integrity • Can be applied to columns or tables • SQL Server supports five classes: • NOT null • CHECK • UNIQUE • PRIMARY key • FOREIGN key Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  37. Defaults • Specify values placed in a column if you do not specify a value for the column when inserting a row • Can be anything that evaluates to a constant value • Static value • Built-in function • Mathematical expression Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  38. Triggers • Special class of stored procedure • Execute automatically when an UPDATE, INSERT, or DELETE statement is issues against a table or view • Tables can have multiple triggers • Define using CREATE TRIGGER Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  39. SQL As A DML • DML = Data Manipulation Language • DML commands allow you to perform standard CRUD operations • Create data • Read data • Update data • Delete data Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  40. SQL’s CRUD Commands • INSERT • SELECT • UPDATE • DELETE Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  41. SELECT • The SELECT command allows you to retrieve one or more rows from a table or view • Can use JOIN statements to access data in multiple tables/views Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  42. Select (simple form) • You read data from the database by using a select • The simple form of the syntax is • This looks at the tables, finds the rows that satisfy the where predicates, and returns the columns from those rows select columns from tables where predicates Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  43. Select select owner from accounts; select * from accounts; select owner from accounts where balance > 1000; select time, amount from transactions where account = 'Bill' and deposit = 1; select owner from accounts where balance between 1000 and 50000; select * from accounts where owner in (‘Bill’, ‘Bob’, ‘Tom’) order by balance; select * from accounts where owner in (‘Bill’, ‘Bob’, ‘Tom’) order by balance, owner; Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  44. Set Functions • You can compute an aggregate function on the return value of a select • You can use ordinary functions in where clauses, but not aggregate functions select count(*) from accounts; select avg(balance) from accounts; select sum(amount) from transactions where account = ‘Bob’ and deposit = 1; Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  45. Grouping for Set Functions • Grouping controls how the set functions are computed • no “group by” clause ? Compute one value for entire result set • Otherwise, use columns in a group by clause to control aggregations • group by can take a sequence of column names (like order by) select sum(amount) from transactions where deposit = 1 group by account; Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  46. INSERT • The SQL INSERT command adds a new row to a table or view • Can be used with views, however, only one table will be updated • Can perform multiple-row INSERT statements Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  47. Insert • Occasionally, you also have to put data into the database. This is done using insert • The syntax is • This can fail, if any of the database constraints are violated by the insert • No where, order by, ... clauses in inserts insert into table (column-1, column-2, ..., column-k) values (value-1, value-2, ..., value-k) Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  48. Update • Update is a way to change existing rows • The where clause functions exactly as in the select statement • No where clause ? Change every row • Still no need for order by or group by update table set column-1 = value-1, column-2 = value-2, ... where predicates; Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  49. UPDATE • Update allows you to change data in a table or view • Changes can affect a single row or multiple rows Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

  50. DELETE • The SQL DELETE statement allows you to remove one or more rows from a table or view Saras M Srivastava, PGT(CS), KV, IISc, Bangalore -12

More Related