LanguageCopy

Relational Calculus Query It is a non-procedure query language used to  retrieve records from relations. In this type of language we do not specify the procedure to retrieve the records as we specify in relational algebra. In this language we simply specify the description along with the conditions. Consider the following example: Relational Algebra:    Select all the employees with their details if sales is more quota.  The same statement can be stated as in Relational calculus as follow:  Get all employee such that their sales is more than quota . Based on relational calculus various languages has been design such as LISP (List Processor), PROLOG and other language used in artificial intelligence. Relational calculus has been divided into following two categories; 1. Tuple Relational Calculus 2.  Domain Relation Calculus Tuple Relational Calculus:   A tuple relational calculus is rational calculus which is based on tuples of a relations. Example: Get...

Extended Relational Algebra: Extended operators relational algebra are given as follows: 1. Natural Join  (⋈) 2. Assignment   ( è ) 3. Intersection  (∩) 4. Division  (÷) Natural Join: It is a binary operators which takes two relations R and S and it is denoted as: R   ⋈   S and it will give the tuple in the tuple in the output relation related with primary key and foreign key relationship. In the expression R and S are the relation where we perform natural join , It does Cartesian  product and then it extracts the tuple from the Cartesian product having parent and child relationship. Example1:  Write an expression using Natural join to ,list product details along with order details. Syntax: σ   (Product X orders); Write above expression using Cartesian Product: σ   (   σ prod = product_id, mfr =  mfr_id (product X orders)); SQL: SELECT *, * FROM orders, product WHERE mfr = mfr_id and prod = prod_id; Example2...

Cartesian Product:   It is a binary operator which takes two relation r and s and it is denoted as   r X s ,  in Cartesian Product the output will consists of every possible combinations of tuples in r and s i.e each tuple in r will multiply with every tuple in s . Example:  Find out the Cartesian Product of the tables salesrep and orders. salesrep X orders; Consider  two relations as employee and department : Table:Department Table: Employee Table : Employee X Department Example: Write an expression to find out all the employees with their name, sales, quota, order_date, amount if  they have sales < 50% of their quota. SELECT name, sales, quota, order_date, amount  FROM salesrep, Orders WHERE rep= emp_id and sales < 0.5 * quota; ∏                              ( σ sales < 0.5 * quota( σ rep = emp_id (salesrep X orders)))     name, sales, quota, order_dat...

Relation Algebra: It is  a procedure language used to query different relations in a Relational Database. It takes instances of relation as input and gives instances of relation as output, which means the input to every relational algebraic expression will be a relational and output of expression will also be a relation. Relation algebra provides a mathematical background using different mathematical operators along with relation from a database . #Relation: Table #Relationship: Natural Association  It consists of following basic operators used to query over the relation: 1. SELECTION    (σ) 2. PROJECTION    (∏) 3. UNION   (∪) 4. SET DIFFERENCE    (−) 5. CARTESIAN PRODUCT   (Χ) 6. RENAME    (ρ) SELECTION   (σ):  It is a unary operators used to select tuples from a relation. It is define as follow : σ      (r)      ρ   Where  σ  is operator    r...

Exception: Exception are define as runtime error which may occurs due to semantically problems in a program. If runtime error occurs, program terminate abnormal and we do not know the problem in program . Hence to terminate the program gracefully we handle those error which is known as runtime error handling or exception handling. when exception handling done in program , program terminates by displaying proper message. Exception are of following two types: 1. System define exception 2. User define exception System Define Exception: Such exception are identified by system and hence known as System define exception. It is again two types: 1.Named System Define Exception 2.Unnamed System Define Exception Named System Define Exception: Such exception are well known to the system for which system has assign a proper name. Such exception are define and fired by the system and we need to simply handled them. Following are the sum of named system exception: i. Cursor-already-open ii. Invalid-...

Triggers: Triggers are defines as stored procedure which acts as object ,it executes automatically based on event taking place specified in trigger creation. It is used for maintain constraints. Benefits:  1. Trigger are used to enforce integrity constraints in the database. 2. Since trigger model is based on ECA model (Event Condition Action) and hence it execute on based on the event taking placed and it does not required a human intervention. 3. Since it does not required any human intervention , it will maintain the accuracy of value in the table, as every time event places , trigger is fired on its own. 4. As triggers are always present in main memory of database , if it is run time (dynamic) features of database if it is implement by taking needed numbers of trigger only, will optimize performance of database . Drawbacks:       Extensive use of trigger may slow down the database performance as trigger when created , remains in primary memory.    ...

Packages: Packages are defined as database objects  use to bundle various sub programs together. It is act as Container which may consists of various datatype, various Variable, Cursor, Procedure , Function etc. It is created in two separate steps given as follows;   1. Package Specification 2.Package Body Package Specification : Package  specification acts as an interface to the specification body. It consists of various types of declaration, such as variable, cursor, procedure , function without any coding details. Anything specify inside package specification by  default it taken  as a public member which means the number can be used directly from outside of the package.    Any coding done in package body but not specification in package specification are known as private member which means such coding can be used only by the member of  the package. Syntax of package specification:   CREATE PACKAGE package_name  IS|AS  DEC...