Docstoc

dbms 1 DBMS

Document Sample
dbms 1 DBMS Powered By Docstoc
					                                                                                       1


                     DBMS I unit and SQL Questions and Answers
                            Part A (2 Marks Questions)

1. Explain Referential Integrity.
             Referential Integrity means relationship between tables. Foreign keys are
     used. Foreign key is the column whose values are derived from the Primary key
     of the same or some other table.

      Format for creating a foreign key is given below.
         Syntax: create table <table name>(columnname data type (size) constraint
      constraint_name references parent table name);

2. Define Instances and schemas.
     Instances:
             The collection of information stored in the database at a particular moment
     is called the instance of the database.
     Schemas:
             The overall design of the database is called the schema of the database.

3. Define and explain the two types of Data Independence.
             Two types of data independence are
                  i. Physical data independence
                 ii. Logical data independence
     Physical data independence:
             The ability to modify the physical schema without causing application
     programs to be rewritten in the higher levels. Modifications in physical level
     occur occasionally whenever there is a need to improve performance.
     Logical data independence:
             The ability to modify the logical schema without causing application
     programs to be rewritten in the higher level (external level). Modifications in
     physical level occur frequently more than that in physical level, whenever there is
     an alteration in the logical structure of the database.

4. Define transaction.
             A transaction is a collection of operations that performs a single logical
     function in a database application. Each transaction is a unit of both atomicity and
     consistency. Properties of transaction are atomicity, consistency, isolation, and
     durability.

5. Define the type types of DML.
            Two types:
                         Procedural DML
                         Non-procedural DML
     Procedural DML:
            It requires a user to specify what data are needed and how to get those
     data.
                                                                                         2


      Non-Procedural DML:
              It requires a user to specify what data are needed without specifying how
      to get those data.

6. List out the functions of DBA.
                  Schema definition
                  Storage structure and access-method definition
                  Schema and physical modification
                  Granting of authorization for data access
                  Integrity constraint specification

7. What is the need for DBA?
            The need of DBA is to have central control of both the data and the
            programs that access those data. The person who has such central control
            over the system is called the DataBase Administrator.

8. Explain DML pre-compiler.
            DML precompiler converts DML statements embedded in an application
            program to normal procedure calls in the host language. The precompiler
            must interact with the DML compiler to generate the appropriate code.

9. Define file manager and buffer manager.
     File manager:
             File manager manages the allocation of space on disk storage and the data
     structures used to represent information stored on disk.
     Buffer manager:
             Buffer manager is responsible for fetching data from the disk storage into
     the main memory, and deciding what data to cache in memory.

10. Define Data Dictionary.
               DDL statements are compiled into a set of tables that is stored in a special
      file called data dictionary. Data dictionary contains the meta-data, which in turn is
      data about the data.

11. Give the syntax for creating the table with composite primary key.
              Multicolumn primary key is called composite primary key.
      Syntax: create table <table name>(columnname1 data type (size), columnname2
      data type (size), constraint constraint_name primary key (columnname1,
      columnname2));

12. Write a query to update the account table to replace value 500 to 450 for the
    account number ‘A-101’.
      Query: update account set balance=450 where acc_no=’A-101’;
                                                                                      3


13. Write a query to display loan number, branch name where loan amount is
    between 500 and 1000 using comparison operators.
      Query: select loan_no, branch_name from loan where amount>=500 and
      amount<=1000;

14. Find all customers having a loan, an account or both at the bank.
      select cust_name from depositor union select cust_name from borrower;

15. Find all customers who have an account but no loan at the bank.
      select cust_name from depositor minus select cust_name from borrower;

16. Find the names and loan numbers of all the customers who have a loan at the
    Perryridge branch.
      Query: select distinct cust_name, borrower.loan_no as LID from borrower, loan
      where borrower.loan_no=loan.loan_no and branch_name='perryridge';

17. Find the number of depositors for each branch.
      Query: select branch_name, count (distinct cust_name) from depositor, account
      where depositor.acc_no=account.acc_no Group by branch_name;

18. Find the names of all branches with customers who have an account in the bank
    and who live in the Harrison city using Equi-join.
      Query: select branch_name from customer,account,depositor where
      cust_city='harrison'   and     customer.cust_name=depositor.cust_name   and
      depositor.acc_no=account.acc_no;

19. Find all customers who are borrowers from the bank and who appear in the list
    of depositors.
       Query: select distinct cust_name from borrower where cust_name in (select
       cust_name from depositor);

20. Find the names of all branches with customers who have an account in the bank
    and who live in the Harrison city using Sub-Queries.
      Query: select branch_name from account where acc_no in(select acc_no from
      depositor where cust_name in(select cust_name from customer where
      cust_city='harrison'));

21. Select the rows from borrower such that the loan numbers are lesser than any
    loan number where the branch name is Downtown.
       Query: select * from borrower where loan_no < any (Select loan_no from loan
       where branch_name='downtown');

22. Explain the use of sequence database object and give an example query to
    create a sequence.
               Sequence is used for the automatic generation of primary or unique key
       integer value.
                                                                                           4


      Query: create sequence seq1 increment by 1 start with 100 maxvalue 105
      minvalue 100 cycle cache 4;

23. Define self-join and give an example query to illustrate self-join with a sample
    table.Joining of a table to itself is called self-join. i.e., it joins one row in a table
    to another row.
       Query: select emp.emp_name, mngr.emp_name from employ emp, employ mngr
       where emp.manager_no=mngr.emp_no;

      Sample table: Employ

      Emp_no      Emp_name         Manager_no
      E001        Basu navindgi    E002
      E002        Rukmini          E005
      E003        Carol            E004
      E004        Cynthia           -
      E005        Ivan              -

24. Give the query for updating a new table, consumer with the street as ‘South’ if
    the customer is living in ‘North’.
       Query: Update consumer set cust_street = ‘south’ where cust_name =(select
       cust_name from customer where cust_street=’north’);

25. What is a view and give an example query to create a view from an existing
    table.
               Any relation that is not a part of the logical model but which is made
       visible to the user as a virtual (imaginary) relation is called a view.

      Query: create view custall (name, city) as (Select cust_name, cust_city from
      customer);

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:160
posted:11/15/2011
language:English
pages:4