SLIDES_RDBMS_3.pdf - Education and Research We enable you to leverage knowledge anytime anywhere RDBMS \u2013 Part 3 o f In ER\/CORP\/CRS\/DB92 Ver No 1.0 s y

SLIDES_RDBMS_3.pdf - Education and Research We enable you...

This preview shows page 1 out of 58 pages.

Unformatted text preview: Education and Research We enable you to leverage knowledge anytime, anywhere! RDBMS – Part 3 o f In ER/CORP/CRS/DB92 Ver. No.: 1.0 s y s Confidential Copyright © 2008, Infosys Technologies Ltd. General Guideline © (2008) Infosys Technologies Ltd. This document contains valuable confidential and proprietary information of Infosys. Such confidential and proprietary information includes, amongst others, proprietary intellectual property which can be legally protected and commercialized. Such information is furnished herein for training purposes only. Except with the express prior written permission of Infosys, this document and the information contained herein may not be published, disclosed, or used for any other purpose. o f In Copyright © 2008, Infosys Technologies Ltd. 2 s y s Confidential 2 Confidential Information   This Document is confidential to Infosys Technologies Limited. This document contains information and data that Infosys considers confidential and proprietary (“Confidential Information”). Confidential Information includes, but is not limited to, the following: s y s  Corporate and Infrastructure information about Infosys  Infosys’ project management and quality processes  Project experiences provided included as illustrative case studies     Any disclosure of Confidential Information to, or use of it by a third party, will be damaging to Infosys. Ownership of all Infosys Confidential Information, no matter in what media it resides, remains with Infosys. Confidential information in this document shall not be disclosed, duplicated or used – in whole or in part – for any purpose other than reading without specific written permission of an authorized representative of Infosys. This document also contains third party confidential and proprietary information. Such third party information has been included by Infosys after receiving due written permissions and authorizations from the party/ies. Such third party confidential and proprietary information shall not be disclosed, duplicated or used – in whole or in part – for any purpose other than reading without specific written permission of an authorized representative of Infosys. o f In Copyright © 2008, Infosys Technologies Ltd. 3 Confidential 3 Recap  Converting ER Diagrams to relational schema  Normalization 1NF, 2NF, 3NF  Merits and Demerits of Normalization o f In Copyright © 2008, Infosys Technologies Ltd. 4 s y s Confidential 4 Session Plan  Introduction to SQL  DDL Statements  Create Table  Alter Table  Drop Table  Truncate Table  Relational Operators  DML Statements  Insert into  Delete from  Select …  Update … o f In Copyright © 2008, Infosys Technologies Ltd. 5 s y s Confidential 5 SQL  SQL is used to make a request to retrieve data from a Database.  On receiving the request the Database Management System processes the request by retrieving the data from the database. s y s  This process of requesting data from a Database is called a Database Query and hence the name SQL(Structured Query Language). o f In Copyright © 2008, Infosys Technologies Ltd. 6 Confidential 6 SQL  SQL is a language that all commercial RDBMS implementations understand.  SQL is a non-procedural language s y s  We will be discussing SQL with respect to oracle syntax o f In Copyright © 2008, Infosys Technologies Ltd. 7 Confidential You can’t write programs like the ones you would have done using C language You can only write questions in English like language called queries which will fetch some data rows from the database. 7 Structured Query Language (SQL) o f In Copyright © 2008, Infosys Technologies Ltd. 8 s y s Confidential 8 Structured Query Language (SQL)  1979 Oracle Corporation introduces the first commercial RDBMS  1982 ANSI (American National Standards Institute) forms SQL Standards Committee  1983 IBM (International Business Machine) announces DB2 (a Database)  1986 ANSI (American National Standards Institute) SQL1 standard is approved  1987 ISO (International Organization for Standardization) SQL1 standard is approved  1992 ANSI (American National Standards Institute) SQL2 standard is approved  2000 Microsoft Corporation introduces SQL Server 2000, aimed at enterprise applications  2004 SQL: 2003 standard is published o f In Copyright © 2008, Infosys Technologies Ltd. 9 s y s Confidential 9 Statements  DDL (Data Definition Language)     Create Alter Drop Truncate  DML (Data Manipulation Language)     Insert Update Delete Select o f In  DCL (Data Control Language)     Grant Revoke Commit Rollback Copyright © 2008, Infosys Technologies Ltd. 10 s y s Confidential SQL has three flavors of statements. The DDL, DML and DCL. DDL is Data Definition Language statements. Some examples: CREATE – to create objects in the database ALTER – to alter the structure of the database DROP – to delete objects from the database TRUNCATE – to remove all records from a table. The space allocated for the records is also removed DML is Data Manipulation Language statements. Some examples: SELECT – to retrieve data from the database INSERT – to insert data into a table UPDATE – to update existing data within a table DELETE – to delete all records from a table. The space allocated for the records remains intact DCL is Data Control Language statements. Some examples: GRANT – to give user access privileges to database objects REVOKE – to withdraw access privileges given with the GRANT command COMMIT – to save the work done ROLLBACK – to restore database to original since the last COMMIT 10 Data types      Number Char Varchar2 Long date o f In Copyright © 2008, Infosys Technologies Ltd. 11 s y s Confidential SQL supports various data types Integers Decimal numbers--- NUMBER, INTEGER . Number is an oracle data type. Integer is an ANSI data type. Integer is equivalent of NUMBER(38) The syntax for NUMBER is NUMBER(P,S) p is the precision and s is the scale. P can range from 1 to 38 Floating point numbers---- FLOAT Fixed length character strings---- CHAR [<size>] The CHAR data type is fixed length, with the maximum size of the column specified in parentheses. Specifying the size is optional, and the default size is 1 byte. The maximum allowed size in a CHAR data type column is 2000 bytes. Variable length character strings --- VARCHAR2(<size>) A maximum size for the column should be defined. Unlike CHAR columns, VARCHAR2 columns are not blank-padded with trailing spaces if the column value is shorter than its maximum specified length. The range is values allowed for size is from 1 to 4000 bytes. Dates-----DATE The DATE data type stores date and time information. You can store the dates from January 1, 4712 BC to December 31, 9999 AD. LONG LONG columns can store up to 2GB of data. There can be only one LONG column in the table definition. 11 NULL  Missing/unknown/inapplicable data represented as a NULL value  NULL is not a data value. It is just an indicator that the value is unknown o f In Copyright © 2008, Infosys Technologies Ltd. 12 s y s Confidential 12 Operators  Arithmetic operators: +, -, *, /  Logical operators: AND, OR, NOT s y s  Relational operators: =, <=, >=, < >, < , > o f In Copyright © 2008, Infosys Technologies Ltd. 13 Confidential The Arithmetic operators are used to calculate something like given in the example below: Select * from employee where sal * 1.1 > 1000 ; The logical operators are used to combine conditions like: Select * from employee where (sal > 1000 AND age > 25); The above two examples also illustrate the use of relational operators. 13 Education and Research We enable you to leverage knowledge anytime, anywhere! SQL DATA DEFINITION LANGUAGE o f In Copyright © 2008, Infosys Technologies Ltd. s y s Types Of Constraints  Column Level  Table level o f In Copyright © 2008, Infosys Technologies Ltd. 15 s y s Confidential 15 Types Of Constraints  Primary Key Constraint  Foreign Key Constraint  Unique Constraint  Check Constraint o f In  Not Null Constraint Copyright © 2008, Infosys Technologies Ltd. 16 s y s Confidential 16 SQL CREATE TABLE Implementing PRIMARY KEY ,NOT NULL and UNIQUE Customer Table Colum name Datatype Description CustomerId Varchar2(6) Unique id generated for each customer o f In Constraints s y s CustomerName Varchar2(30) Name of the customer DateOfReg Date Date on which the customer registered UserId Varchar2(15) Password Varchar2(15) Decided at the time of registration Decided at the time of registration Copyright © 2008, Infosys Technologies Ltd. 17 Primary Key, Should start with ‘C’ Not null It should be unique Not Null Confidential 17 SQL - CREATE TABLE (contd.) Implementing PRIMARY KEY ,CHECK,NOT NULL and UNIQUE constraints EXAMPLE : CREATE TABLE Customer (CustomerId varchar2(6) CONSTRAINT customer_pk PRIMARY KEY CONSTRAINT customer_cid CHECK (CustomerId like 'C%'), CustomerName varchar2(30) CONSTRAINT customer_customername_nnull NOT NULL, NULL DateofReg date , UserId varchar2(15) CONSTRAINT customer_userid_uq UNIQUE, UNIQUE Password varchar2(15) CONSTRAINT customer_passwd NOT NULL); NULL o f In Copyright © 2008, Infosys Technologies Ltd. 18 s y s Confidential Constraints: NOT NULL: Prevents NULL values from being entered into the column. These types of constraints are defined on a single column. By default, Oracle allows NULL values in any column. A NOT NULL constraint is defined at the column level; it cannot be defined at the table level. PRIMARY KEY: Uniquely identifies each row of the table and prevents NULL values. A table can have only one primary key constraint. UNIQUE: Any unique column will not allow duplicate values to be present in it. However there can be two or more than two NULL in the unique column (because Null is not equal to Null). 18 SQL - CREATE TABLE (contd.) Implementation of Composite Primary Key and Foreign Key Constraints BankInfo Table Colum name Data type AccountNo Number(10) CustomerId Constraints Unique id provided to each customer when he/she is registered to purchase items Foreign key Composite referring to Primary key customer table o f In Varchar2(6) s y s Description Account no of customer Copyright © 2008, Infosys Technologies Ltd. 19 Confidential 19 SQL - CREATE TABLE (contd.) Implementation of Composite Primary Key and Foreign Key Constraints EXAMPLE : CREATE TABLE Bankinfo (AccountNo number(10), CustomerId varchar2(6) CONSTRAINT bankinfo_fk REFERENCES Customer(CustomerId), CONSTRAINT bankinfo_pk PRIMARY KEY(AccountNo, CustomerId)); KEY( o f In Copyright © 2008, Infosys Technologies Ltd. 20 s y s Confidential Constraints: 20 SQL - CREATE TABLE (contd.) Implementation of Self Referencing Foreign key in Employee_Details Table Column Name Data type Constraints Employee_ID Number(6) Employee_Last_Name Varchar2(20) s y s Primary key of the table o f In Employee_Mid_Name Varchar2(3) Employee_First_Name Varchar2(20) Employee_Email Varchar2(30) Employee_Dept Number(2) Default ‘HR’ Manager_ID Varchar2(30) It can take only those values which are present in Employee_ID column Copyright © 2008, Infosys Technologies Ltd. 21 Confidential 21 SQL - CREATE TABLE (contd.)  Implementing Self Referential Foreign Key EXAMPLE : CREATE TABLE Employee_Details( Number(5) Employee_ID KEY CONSTRAINT Employee_PKey PRIMARY KEY, Employee_Last_Name Varchar2(20), Employee_Mid_Name Char(3), Employee_First_Name Varchar2(20), Employee_Email Varchar2(30), Department Varchar2(10) DEFAULT ‘HR’, Manager_ID Number(5) CONSTRAINT Manager_FKey REFERENCES Employee_Details(Employee_ID) ); o f In Copyright © 2008, Infosys Technologies Ltd. 22 s y s Confidential Constraints: SelfRefrencing Foregn Key: When a foreign key refers the columns of the same table then it is called self referencing foreign key Default Values: The default value specified will be used when you do not specify any value for the column while inserting data. The default value specified in the definition should satisfy the data type and length of the column. If a default value is not explicitly set, the default for the column is implicitly set to NULL. 22 SQL - CREATE TABLE (contd.) Item Table Colum name Data type Description Constraints ItemId ItemName Varchar2(6) Varchar2(30) Unique Id provided for each item. (eg STN001 for stationery items) Name of the item QtyOnHand UnitPrice Class Number(3) Number(6,2) Char(1) s y s Current availability of item in the shop Sell price of item per unit Depending on the UnitPrice, items belongs to various Classes. eg: A,B,C etc. o f In UnitOfMeasur ement Varchar2(12) Primary Key Not Null Should be greater than ReorderLevel (Table level constraint) Greater than 0 Class of Item is ‘A’ if UnitPrice is less than 100, ‘B’ if UnitPrice is less than 1000, ‘ C’ if UnitPrice is 1000 and above (Table level constraint) Unit used to measure the quantity ( eg Kilogram, dozen etc) Minimum Quantity after which the supplier ReOrderLevel Number(3) must be ordered for new stock Greater than 0 Minimum Quantity that can be ordered to ReorderQty Number(3) the supplier Greater than 0 Percentage discount on the item to the Discount Copyright Number(2) customer © 2008, Infosys Technologies Ltd. 23 Confidential 23 SQL - CREATE TABLE (contd.) Implementing Check constraint EXAMPLE : CREATE TABLE Item KEY (ItemId varchar2(6) CONSTRAINT Item_pk PRIMARY KEY, NULL ItemName varchar2(30) CONSTRAINT Item_Itemname_Nnull NOT NULL, QtyOnHand Number(3) , UnitPrice Number(6,2) CONSTRAINT Item_unitprice_chk CHECK( CHECK UnitPrice > 0 ), Class char(1), UnitofMeasurement varchar2(12) , ReOrderLevel Number(3) CONSTRAINT item_reorderlevel CHECK(ReOrderLevel > 0), CHECK ReOrderQty number(3) CONSTRAINT item_reorderqty CHECK(ReOrderQty >0), CHECK Discount Number(2) , CONSTRAINT item_reorder CHECK (QtyOnHand > ReOrderLevel), CONSTRAINT item_Class CHECK((Class='A' AND UnitPrice<=100) OR CHECK (Class='B‘ AND (UnitPrice BETWEEN 101 AND 1000)) OR (Class='C‘ AND UnitPrice>1000)) ); o f In Copyright © 2008, Infosys Technologies Ltd. 24 s y s Confidential Constraints: Column level ( highlighted in orange rectangle) Table level (highlighted in blue rectangle signifies) 24 Create Table (Contd…) Implementing Composite Foreign key constraint Billing Table Colum name Datatype BillId Number(4) AccountNo CustomerId BillDate PaymentType o f In Number(10) Varchar2(6) Date Varchar2(12) Copyright © 2008, Infosys Technologies Ltd. s y s Description Unique Id generated for each bill Account no which is used to pay the bill Customer id of the customer who does the purchase of items The date of payment of bill Type of Payment 25 Constraints Primary key Composite Foreign key to Bank info table Default SYSDATE Either Creditcard or Debitcard Confidential In the above example the Foreign Key has been specified as Table level constraint 25 Create Table (Contd…) Implementing Composite Foreign key constraint EXAMPLE : s y s CREATE TABLE Billing KEY (BillId number(4) CONSTRAINT billing_billid_pk PRIMARY KEY, Accountno number(10) , CustomerId varchar2(6) , NULL BillAmt number(7,2) CONSTRAINT billing_billamt_Nnull NOT NULL, Billdate date DEFAULT sysdate, PaymentType varchar2(12) CONSTRAINT billing_paymenttype CHECK(PaymentType in (‘Creditcard', ‘Debitcard') ), CHECK CONSTRAINT billing_composite_fk FOREIGN KEY(AccountNo, CustomerId) KEY REFERENCES BankInfo (AccountNo, CustomerId)); o f In Copyright © 2008, Infosys Technologies Ltd. 26 Confidential In the above example the Foreign Key has been specified as Table level constraint 26 SQL SQL -- ALTER ALTER TABLE– TABLE– ADD/DROP/MODIFY ADD/DROP/MODIFY Column Column  Add/Drop/Modify Column ALTER TABLE Customer ADD Contact_Phone s y s Char(10); o f In ALTER TABLE Customer MODIFY Contact_Phone Char(12); ALTER TABLE Customer DROP (Contact_Phone); Copyright © 2008, Infosys Technologies Ltd. 27 Confidential Used to modify the structure of a table by adding and removing columns The ALTER TABLE statement cannot be used to change the name of a column or table Column to be modified should be empty to decrease column length Column to be modified should be empty to change the data type If the table has only one column, the ALTER TABLE statement cannot be used to drop that column because that would render the table definition invalid. 27 ALTER TABLE—ADD/DROP/MODIFY Column  Used to modify the structure of a table by adding and removing columns s y s  The ALTER TABLE statement with MODIFY option cannot be used to change the name of a column or table.  Column to be modified should be empty to decrease column length o f In  Column to be modified should be empty to change the data type  If the table has only one column, the ALTER TABLE statement cannot be used to drop that column because that would render the table definition invalid. Copyright © 2008, Infosys Technologies Ltd. 28 Confidential 28 SQL - ALTER TABLE—ADD/DROP Constraint  Add/Drop Constraint ALTER TABLE Customer_Account_Details ADD CONSTRAINT Pkey1 PRIMARY KEY (Account_No); s y s ALTER TABLE Customer_Account_Details ADD CONSTRAINT Pkey2 PRIMARY KEY (Account_No, Cust_ID); o f In ALTER TABLE Customer_Account_Details KEY DROP PRIMARY KEY; OR ALTER TABLE Customer_Account_Details DROP CONSTRAINT Pkey1; Copyright © 2008, Infosys Technologies Ltd. 29 Confidential ALTER TABLE statement can be used to Add or Drop primary key constraint to / from a table ALTER TABLE statement can be used to Add or Drop foreign key constraint to / from a table ALTER TABLE statement can be used to Add or Drop Unique constraint to / from a table ALTER TABLE statement can be used to Add or Drop check constraint to / from a table If a table already has a primary key, then adding a primary key using the ALTER TABLE statement results in an error. The RDBMS will not allow a PRIMARY KEY constraint (using the ALTER TABLE statement) on column(s) if the column(s) has NULL or duplicate values . 29 SQL - ALTER TABLE—ADD/DROP Constraint ALTER TABLE Customer_Transaction s y s ADD CONSTRAINT Fkey1 FOREIGN KEY (Cust_ID) REFERENCES Customer_Account_Details (Cust_ID); ALTER TABLE Customer_Transaction o f In DROP CONSTRAINT Fkey1; •A table can have one or more Foreign keys •Adding a foreign key constraint using ALTER TABLE command will result in an error if the existing data in master or child table does not support the foreign key restriction. Copyright © 2008, Infosys Technologies Ltd. 30 Confidential A table can have one or more Foreign key Adding a foreign key constraint using ALTER TABLE command will result in error if the existing data in master or child table does not support the foreign key restriction 30 SQL - ALTER TABLE—ADD/DROP Constraint  ALTER TABLE statement can be used to Add or Drop primary key constraint to / from a table  ALTER TABLE statement can be used to Add or Drop foreign key constraint to / from a table s y s  ALTER TABLE statement can be used to Add or Drop Unique constraint to / from a table  ALTER TABLE statement can be used to Add or Drop check constraint to / from a table o f In  If a table already has a primary key, then adding a primary key using the ALTER TABLE statement results in an error.  The RDBMS will not allow a PRIMARY KEY constraint (using the ALTER TABLE statement) on column(s) if the column(s) has NULL or duplicate values Copyright © 2008, Infosys Technologies Ltd. 31 Confidential 31 SQL - DROP TABLE  DROP TABLE    Deletes table structure Cannot be recovered Use with caution DROP TABLE UnqTable; o f In Copyright © 2008, Infosys Technologies Ltd. 32 s y s Confidential Most RDBMS restrict the droping of a table if it has attribute(s) being refered to by attribute(s) of another table. 32 Truncate Table  Deleting All Rows of a table TRUNCATE TABLE Customer; o f In Copyright © 2008, Infosys Technologies Ltd. 33 s y s Confidential TRUNCATE: The TRUNCATE statement is similar to a DELETE statement without a WHERE clause, except for the following: •TRUNCATE is very fast whether the table is large or small . DELETE generate s undo information, in case a rollback ...
View Full Document

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture