ppt

Document Sample
ppt Powered By Docstoc
					RELATIONAL CALCULUS:
   - It is a formal query language where the queries are expressed as variables
 and formulas on these varaibles.
   - The formula describes the properties required result relation without
 specifying the method of evaluating it..

 The relational calculus comes in two ways :
 1. Tuple relational calculus (TRC)
 2. Domain relational calculus(DRC)

 1. Tuple relational calculus (TRC)
       - It is a non procedural query language . It describes the desired
 information without giving a specific procedure for obtaining that information.
    A query in the tuple relational calculus is expressed as
                         {t | P (t) }
    - It is the set of all tuples t such that predicate P is true for t
    - t is a tuple variable, t[A] denotes the value of tuple t on attribute A
     - t  r denotes that tuple t is in relation r
       Formal Definition of tuple relational calculus :
      A tuple relational calculus is expressed as
                            {t | P (t) }
      where
      P - is a formula, several tuple variable may appear in a formula.
    - A tuple variable is said to be free variable unless it is quantified by a or  (
    there exits,  : for all ).

    - Thus in
     t loan   s  Customer [t[Branch name]=s[branch name]
     - t is a free variable.
     - s – is a bound variable.
.
•      A tuple relational formula is built up out of atoms. An atom has one of
      the following forms
      1. s  r , where s is a tuple variable and r is a relation.
      2. s[x] θ u[y], where s and u are tuple , x is an attribute on which s is
      defined , y is an attribute on which u is defined,
       θ - is a comparison operator. (, , , , , ); we require that
      attribute x and y have domains whose member can be compared by θ.

     3. s[x] θ c, where s is a tuple variable , x is an attribute on which s is
       defined, θ is a comparison operator , and c is a constant in the domain
       of attributes x.
       we build up formula from atoms by using the following rules.
    1. an atom is a formula.
    2. if P1 is a formula, then so are  P1 and P2.
    3. if P1 and P2 are formulae then so are P1 v P2, P1 P2 and P1 P2.
    4. IF P1(s) IS A FORMULA CONTAINING A FREE TUPLE VARIABLE S AND R IS
       A RELATION, t r (Q(t)) and t r (Q(t)) .
    eg: 1. find all tuples from the depositor relation.
               {t | t depositor}
      2. Find the loan-number, branch-name, and amount for loans of over $1200
              {t | t  loan  t [amount] >1200}
     3. Find the loan number for each loan of an amount greater than $1200
         {t | s  loan (t[loan-number] = s[loan-number]  s [amount] > 1200)}

     Safety of Expressions :
     - A tuple relation calculus expression may generate an infinite relation.
     consider the expression,
        {t | t loan} - means, tuples that are not in loan. There are infinite tuples that are not in loan
      relation. Hence such tuples of expression should not allowed.

•     To guard against the problem, we restrict the set of allowable expressions to safe
      expressions.

•     An expression {t | P(t)} in the tuple relational calculus is safe if every component
      of t appears in one of the relations, tuples, or constants that appear in P
       – NOTE: this is more than just a syntax condition.
             • E.g. { t | t[A]=5  true } is not safe --- it defines an infinite set with
               attribute values that do not appear in any relation or tuples or constants
               in P.
 expressive power of language :
   - it is restricted to safe expression is equivalent to the basic relational algebra
   operation.( with the operator u,-,x).

2. Domain relational calculus(DRC) .
     - It is uses domain variables that take on values from an attributes domain ,
   rather than for an entire tuple.

   FORMAL DEFINITION :
     - An expression in the domain relational calculus is of the form.
  { <x1,x2…..,xn> | P(x1,x2…..,xn)}.
Where x1,x2…..,xn - represent domain variables.
           P – formula composed of atom.
An atom in the domain relational calculus has one of the following forms.
1. <x1,x2…..,xn>  r , where r is a relation on n attributes and x1,x2…..,xn are
   domain variables or domain constants.
2. X Θ Y , where x and y are domain variables and Θ is a comparision operator
   (, , , , , ). Attributes x and y have domains that can be compared by Θ.
 3. X Θ C , where x are domain variables and Θ is a comparision operator and C
    is a constant in the domain of the attribute for which x is a domain variable.
we build up formula from atoms by using the following rules.
 1. an atom is a formula.
 2. if P1 is a formula, then so are  P1 and P2.
 3. if P1 and P2 are formulae then so are P1 v P2, P1 P2 and P1 P2.
 4. IF P1(s) IS A FORMULA CONTAINING A FREE TUPLE VARIABLE S AND R IS A
    RELATION, t r (Q(t)) and t r (Q(t).
 eg :
 1. find the loan number , branch and amount for loans of over $ 1200 .
  {<l,b,a> | <l,b,a>  loan  a>1200}
 2.find the laon number for loans with an amount greater than $1200.
       {<1> |  b,a (<l,b,a>  loan a > 1200}
 TRIGGERS :
   - A triggers is a statement that the system executes automatically as a side
  effect of a modification to the database.
   To design a trigger mechanism we must meet two requirements.
 1. Specify when a trigger is to executed. This is broken up in to an event that
  causes the trigger to be checked and as condition that must be satisfied for
  trigger execution to proceed .
 2. specify the actions to be taken when the trigger executes.

- The database stores triggers just as if they were regular data , so that they are
   persistant and are accessible to all database operation.
- once we enter a trigger in to the database , the database system takes on the
   resposibility of executing it whenever the specified event occurs and the
   corresponding condition is satisfied.
 NEED FOR TRIGGER :
   - Triggers are useful mechanism for altering humans for starting certain tasks
  automatically when certain condition are met .
 eg : 1. instead of allowing negative account balance , bank deals with
  overdrafts by setting the account balance to zero .
      2. creating a loan in the amount of the overdraft . The bank gives this loan
  a loan no identical to the account number of the overdrawn account .
     3. the condition for executing the trigger is an update to the account
  relation that result in a negative balance value.

eg: - suppose that jones withdrawal of some money from an account made the
   account balance negative.
    - Let t denote the account tuple with a negative balance value . The action
   are taken .
 1. insert a new tuples s in the loan relation with
    s[loan no]       =     t[account no]
    s[branch name] =       [branch name]
    s[amount]         = - t[balance]
  2. insert a new tuple u in the borrower relation with .
       u[customer name] = jones
       u[loan no] = t[account no]
    3. Set t[balance] to 0 .
     TRIGGERS IN SQL :
     Eg of SQL trigger is given below.
    Create trigger overdraft_trigger after update on account new row as n row
 for each row
   when nrow. Balance <0
   begin atomic
   insert in to borrower
    (select custname, acc_no from depositor where nrow.account_no =
      depositor.account _no);
Insert in to loan values
  (nrow.account_no, nrow.branch name –nrow . Balance);
  update account set balance =0 where account. Account no =nrow.account no;
end
When Not to Use Trigger :
  - Trigger should be written with great care, since a trigger error detected at run
   time causes the failure of the inset/delete/update statement that set off the
   trigger.
 - The action of one trigger can set off another trigger .
in worst case , this could lead to an infinite chain of triggering.

 STORED PROCEDURES :
   - PL/SQL supports two types of subprograms. They are:
    1. Procedures
    2. Functions.
  1. Procedures
       - It is a subprogram that performs a specific action.
  syntax : create or replace procedure<proc_name>[parameters list] is
    <local declaration>
 begin
(executable statement)
[exception](exception handlers)
End.
Procedure has two parts :
  1. Specification
  2. Body
  - PROCEDURE SPECIFICATION BEGINS WITH THE KEYWORD PROCEDUREW AND
   ENDS WITH THE PROCEDURE NAME OR PARAMETER LIST .
 - The procedure body begins and ends with the keyword end.
  syntax : sql>exec<proce_name>(parameters);
 Types of parameters passed to subprogram.
 1. In parameter - it is used to pass values to the subprograms when invoked.
 2.out parameter - used to return values to the caller of a subprogram.
 3. in out parameter – used to pass initial values to subprogram when invoked
                        and it also returns updated values to the caller.
FUNCTIONS:
    - It is a subprogram that computes a value .
     <local declaration>
      begin
     (executable statement)
  [exception](exception handlers)
   End.
SECURITY :
   - It is a protection from malicious attempts to steal or modify data .
SECURITY SHOULD BE PROVIDED AT FOLLOWING LEVELS :
 1. Database system level :
        - Use authentication and authorization mechanisms to allow specific users
  access only to required data.
 2. Operating system level :
        - operating system super users can do anything they want to the database
  good operating system level security is required.
 3. Network level :
        - Use encryption to prevent eavesdropping(unauthorized reading of
  messages )
       - masquerading (pretending to be authorized user)
 4. Physical level :
         - physical access to computer allows destruction of data by intruders;
  traditional lock and key security is needed.
        - Computer must also be protected from floods , fire, etc.
 Human level :
    - Users must be screened to ensure that an authorized users do not give
  access to intruders.
    - users should be trained on password selection and secreacy.



DYNAMIC SQL :
   - The dynamic SQL components of SQL allows program to construct and
  submit SQL queries at run time.
  - Using dynamic SQL , programs can create Sql Queries as strings at run time
  and can either have them executed immediately. Or have them prepared for
  susequent use.
  TYPES OF CLIENT/SERVER ARCHITECTURE :
 There are two types of client/server architectures.
 1. Two tiered architecture.
 2. Three tiered architecture.

     1. Two tiered architecture.
        - Client /server environment that divides the application in to the graphical
    user interface(client) and the Data(server) .
         - The GUI can be developed using a product like Visual basic or power
    builder and the DATA can be setup using the a database management system.
 1. The front end (client) of the client/server system consists of tools such as
    forms, report writer and GUI facilities.
 2. The back end(server) manages access to structure,query evaluation and
    optimization, concurrency control and recovery.
3. The interface between the client and the server is through SQL or through an
    application program.
4. In two tiered architecure , business rules are either stored at client side or at
    server side.
2. THREE TIERED ARCHITECTURE :
    - Today the most widely accepted form of client/server system is the
   three – tiered architecture. The component of this architecture are.
  1. The presentation (GUI) or user services.
  2. Busineess rule or business services.
  3.Data server or Data services.

1. All layers interact with each other . The client works as query head and
   rules define how the query ought to be addressed and worked on.
2. The database provides the answer and solutions to the client's query as
   determined by the rule base and the content of the database.
INTEGRITY :
   - Integrity constraints guard against accidental damage to the database , by
  ensuring that authorized changes to the database do not result in a loss of
  data consistency.
Eg: 1. An account balance cannot be null .
     2. No two students can have same roll no.
TYPES OF CONSTRAINTS :
1. Null value concept :
       - While creating table, if a row lacks a data value for a particular column
  ,that values is said to be null.
      - column of any data types may contain null values unless the column
  was defined as not null when the table was created.
PRINCIPLES OF NULL VALUES :
1. Setting a null value is appropriate when the actual value is unknown , or
  when a value would not be meaningful .
2. A null value is not equivalent to a value of zero.
3. A null value will evaluate to null in any expression . Eg: null multiplied by 10
  is null .
4. When a column name is defined as not null, then that column becomes a
  mandatory column. It implies that the user is forced to enter data into that
  column.
PRIMARY KEY CONCEPT :
    - A primary key is one or more column in atable used to uniquely identify
  each row in the table.
    - primary key values must not be null and must be unique across the
  column.
  - A multicolumn primary key is called a composite primary key .
 eg: create table student (roll no number(5) primary key, name varchar(25) not
  null,address varchar(25) not null, ph no varchar(15));

  UNIQUE KEY CONCEPT :
       - Unique key is used to ensure that the information in the column for each
  record is unique as with licence number.
     - A table may have many unique keys.
 eg: create table special_customer(customer_code number(5) Primary key,
  customer_name varcahr(25) NOT NULL, customer_address varchar(30) Not
  null,License _no varchar(15) constraint uk_license _no UNIQUE);
Default value concept :
  - At the time of column creation a default value can be assigned to it. When
  the user is loading a record with values and leaves this column empty.
  - The DBA will automatically load this column with the default value
  specified. The data type of the default value should match the data type of
  the column.
 - use can use the default clause to specify any default value you want.
 eg: Create table special_customer(emp_code number(5) Primary key,
  emp_name varcahr(25) NOT NULL, emp_address varchar(30) ph_no varchar
  (15), married char (1) DEFAULT ‘M’; PRIMARY KEY (Emp _code));

FOREIGN KEY CONCEPT :
 - Foreign key represents relationship between tables.
  - The existence of a foreign key implies that the table with the foreign key is
  related to the primary key table from which the foreign key is derived.
  CHECK INTEGRITY CONSTRAINTS :
     - It defines a condition that every row must satisfy . There can be more
    than one CHECK constraint on a column and the CHECK constraint can be
    defined at column as well as the table level.
At the column level, the constraints is defined by
   eg: DeptID Number (2) CONSTRAINT Ck-deptID
        CHECK((DeptID>=10) and(DeptID<=99));
 and at the table level by
 CONSTRAINT ck –DepID
 CHECK((DepID>=10)and (DepID<=99));

 RESTRICTION ON CHECK CONSTRAINTS :
    - The condition must be a boolean expression that can be evaluated using
   the values in the row being inserted or updated.
   - The condition cannot contain subqueries or sequences.
  - The condition cannot include the SYS DATE, UID,USER or USER ENV SQL
   function .
REFERENTIAL INTEGRITY :
  -A value that appears in one relation for a given set of attributes also appears for
  a certain set of attributes in another relation. Called referential integrity.

  REFERENTIAL INTEGRITY in the E –R model :
      - Referential integrity constraints arise frequently. If we derive our relational
  database scheme by constructing tables from E-R diagrams then every relation
  arising from a relationship set has referential integrity constraint.

                         E1
                                                       E2


                         R

                                                        EN-1
                        EN
                  N – ARY RELATIONSHIP SET :
  - N - ARY relationship set R, relating entity sets E1,E2,…. EN.
 - Let ki denote the primary key of Ei . The attribute of the relation scheme for
     relationship set R include K1 U K2 U ….. Ukn.
 - Each Ki in the scheme for R is a foreign key that leads to a refential integrity
     constraint.
-Another source of referential integrity constraint are weak entity sets . The
     relation scheme for a weak entity set must include the primary key of the
     entity set on which it depends.
  - Thus the relation scheme for each weak entity set includes a foreign key that
     leads to a refential integrity constraint .
  Referential integrity in Sql.
      - Using SQL , primary key, candidate key, and foreign key are defined as part
     of the create table statement .
    create table deposit(branch_name char(15), acc_no char(10), cust_ name
     char(20),not null, balance integer, primary key (acc_no, cust_name), foreign
     (branch name )reference branch, foreign key (cust_name) reference
     customer );
Views
    - A view is object that gives the user a logical view of data from an
underlying table or tables.
   - you can restrict what users can view by allowing them to see only a few
attributes/column from a table.
  Views may be created for the following reasons:
  1. simplifies queries .
  2. can be queried as a base table.
  3. provides data security.
 Creation of views :
     syn : CREATE VIEW viewname as SELECT Columnname, column FROM table
name WHERE columnname = EXPRESSION LIST;
 EG : create view V_Book as select title , author_name from book
       view created
  SELECTING DATA FROM A VIEW :
   EG: sql>select title from v_book where author_name =basu
UPDATABLE VIEWS :
    - Views can also be used for data manipulation. i.e. the user can perform
   insert, update, and the delete operations on the view.
     - The view on which data manipulation can be done called updatable views.
For the view to be updatable , it should meet following criteria.
1. The view must be created on a single table.
2. The primary key column of the table should be included in the view.
3. The select statement used for creating a view should not include
   Dinstint,Group by, or Having CLAUSE
4. The select statement used for creating a view should not include subqueries.

DESTROYING A VIEW :
 - A view can be dropped by using the DROP VIEW command.
  syntax : DROP VIEW viewname;
  eg : DROP VIEW V_BOOK;
 JOIN :
   - Join is a query in which data is retrieved from two or more table. A join
  matches data from two or more table ,based on the values of one or more
  columns in each table.
 DIFFERENT TYPES OF JOIN :
 1. INNER JOIN
 2. OUTER JOIN
  3. NATURAL JOIN

1. INNER JOIN:
       - Inner join returns the matching rows from the tables that are being
    joined.
                        UNIT -3
NORMALIZATION :
    - It is an essential part of database design. A good understanding of the
 semantics of data helps the designer to build efficient design using the concept
 of normalization .

Purpose of Normalization :
- Minimize redundancy in data.
- Remove insert, delete and update anamoly during database activities.
- Reduce the need to reorganize data when it is modified or enhanced.
- Normalization reduces a complex user view to a set of small and stable
  subgroups of fields/relations.
- This purpose helps to design a logical data model knowh as conceptual data
  model .
Normalization forms :
Different normalization forms are
1) First normal form (1NF): A relation is said to be in the first normal form if it is
 already in unnormalized form and it has no repeating group.
2). Second normal form (2NF) : A relation is said to be in the second normal form if
 it is already in the first normal form and it has no partial dependency.
3) Third normal form (3NF) : A relation is said to be in the THIRD normal form if it
 is already in second normal form and it has no transitive dependency .

4) Boyce –codd normal form (BCNF) : A relation is said to be in boyce-codd normal
 form if it is already in the third normal form and every deteminant is a candidate
 key. It is a stronger version of 3NF.

5. Fourth normal form (4NF): A relation is said to be in the fourth normal form if
 it is already in BCNF and it has no multivalued dependency .
6. Fifth normal form (5nf): A relation is said to be in 5NF if it is already in 3NF and
 it has no join dependency.
TERMINOLOGIES USED IN NORMAL FORMS :
 The different technologies used in various normal form are
1. Partial dependency :
     - In the relation having more than one key field . A subset of non –key fields
   may depend on all the key fields but another subset/ a particular non – key
   field may depend on only one of the key fields (i.e. may not depend on all the
   key fields). Such dependency called partial dependency.

2. Transitive :
      - In a relation , there may be dependency among non-key fields .such
   dependency called transitive dependency.
3. Determinant :
      - A determinant is any field (simple field or composite field) on which
   some other field is fully functionally dependent.
Case problem :
 Let us consider the invoice report of alpha book house
                         ALPHA BOOK HOUSE
                           PUNE - 413001
    CUSTOMER NO    : 1052
    CUSTOMER NAME : BETA SCHOOL OF COMPUTER SCIENCE
    ADDRESS        : SHIVAJI NAGAR , PUNE -01
    ISBN      BOOK     Author    Author_ QTY       Price(Rs   Amount
              TITLE    _name     country           )
    81-203-   DOS      PK.SINH   INDIA     5       250        1250
    5                  A
    0-112-6   DBMS     KORTH     U.S.A     6       300        1800
    1-213-9   SIMULA   GORDO     USA       5       100        500
              TION     N
                                           GRAND TOTAL        3550
 Normalization of invoice report upto 3NF.
  - The invoice report is represented in a form of relation The relation is named
as invoice .this is in unnormalized form.
 - 1) invoice (cust_no,cust_name,cust_add, ISBN, title, authorname,
author_country ,qty, unit price).
FIRST NORMAL FORM (1NF) :
   - The relation is in INF if it has no repeating groups.
   - In the invoice relation , the fields in the inner most of parentheses put
    together is known as a repeating group.
    - This will result in redundancy of data for the first three fields . This
    redundancy willl lead to inconsistency of data.
    - hence it is divided in to two sub relation shown below to remove the
    repeating group.
   2.) Customer(Cust_No,Cust_Name,Cust_Add)
   3). Customer_Book (Cust_No,ISBN, Title, Author_Name,
    Author_Country,Qty,Unit_Price)
  - Now each of the above relations (i.e. relatio 2 and relation 3)is in 1NF.
SECOND NORMAL FORM(2NF):
   - 2NF removes partial dependency among attributes of relation .
    - In relation 2, the number of key fields is only one and hence there is no
  scope for partial dependency.
     - The absence of partial dependency in relation 2 takes it into 2NF without
  any modification.
    - In relation 3, the number of key fields are two.

                                  CUST_NO                          TiTLE

           QTY
                                                                  Author_
                                                                   name

                                                                   Author_
                                     ISBN                          country


            DEPENDENCY DIAGRAM OF RELATION 3                      Unit_price
- QTY depends on Cust_no and ISBN , But remaining non-key fields
   (Title,author_name,author_country, unit_price)depend only on ISBN . Thus
   there exits partial dependency.
 - The existence of partial dependency will result in to insertion update, and
   deletion anomaly.
  1. insertion anomaly :
          - In relation 3 , if we want to insert data of a new book
   (ISBN,TITLE,AUTHOR_NAME,AUTHOR_COUNTRY,UNIT_PRICE) there must be
   atleast one customer.
         - This means that the data of a new book can be entered into relation 3
   only when the first customer buys the book.
 2. Update anomaly :
        - In relation 3, if we want to change any of the non-key fields , like Title
   Author_name, it will result into inconsistency because the same is available
   in more than one record.
Deletion anomaly :
    - In relation 3, if book is purchased by only one customer, then the book data
   will be lost when we delete that record after fully satisfying that customer
   order.
Hence relation 3 is divided in to two relations.
4. Sales (Cust_no,ISBN,QTY)
5. Book_Author(ISBN,TITLE,AUTHOR_NAME,AUTHOR_COUNTRY,UNIT_PRICE)
- Above two relations (i.e.relation 4 and 5) are now in 2NF.

THIRD NORMAL FORM :
   - Third normal form removes transitive dependency among attributes of
  relation .
               DEPENDENCY DIAGRAM FOR RELATION 5
                                                                          Author
                                                         Author_
          ISBN                     TITLE                                  _count
                                                          name
                                                                            ry

                      UNIT_PRICE
  - In relation 4, there is only one non key fields . So there is no question of
dependency between non key fields.
   - There is no transitive dependency hence relation 4 is in 3NF.
  - In relation 2, There is no dependency between the non-key fields . This means
that it has no transitive dependency , hence relation 2 is also in 3nf.
  - In relation 5, author countery depends on author name. This means that
relation 5 has transitive dependency.
 - The existence of transitive dependency will result into insert, update and delete
anomaly.
 1. insertion anomaly:
        - Consider the book company has resident authors who are in the process of
developing new books.
       - It is difficult to include the author details in relation 5. this means that there
should be atleast one published book to insert the details of a resident author.
Update anomaly :
   - If author country is to be modified, then it is necessary to modify number
  of tuples as the same data is in number of tuples.

 Deltion anomaly :
    - If the only one book of a resident author is not reprinted , then the
  respective author’s data will be lost. Hence to over come all these anomalies,
  relation 5 is subdivided into two relations.
6.) book (ISBN.title ,unit_price.author_name)
7) Author (Author_ Name, Author_country.)
- In relation 6, Author_Name is underlined with dotted line to indicate that it is
  a foreign key.
BOYCE – CODD NORMAL FORM (BCNF) :
      - It is deals with relational tables which has multiple candidate keys,
  composite candidate keys, and candidate keys that overlapped.
    - BCNF is based on the concept of determinant. A Determinant is a column
  on which some of the columns are fully functional dependent.
   - A relational table is in BCNF IF EVERY DETERMINANT IS CANDIDATE KEY.

Case 1 : Multiple candidate keys.
    - Consider following relational table.

           A#       Aname Aqualif Astatus titled      royalty
                          ication
           100      Arora    PHD      10      T1      3000
           110      Sharma M.TEC      20      T2      4000
                           H
CASE 1 : Multiple candidate keys.
        - Suppose author’s status(Astatus) depends on his/her qualification .if
 author is phd his status (A status) is 10.
      - If M.TECH it is 20 and so on. This relation has three determinants: A#,
 Titled, Aqualification. But only (A# , Titled) combination is a candidate key;
     - so this relation is not in BCNF . For a relation to be in BCNF each
 determinants must be a candidate key.

CASE 2 : Composite candidate keys.
   -

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:31
posted:9/2/2011
language:English
pages:38