WELCOME TO GTU MATERIAL

GTU MATERIAL PROVIDE YOU ALL TYPE OF EDUCATION MATERIAL | DOWNLOAD FREE MATERIAL | ALL TYPE OF EDUCATION SOFTWARES | EXAM ALERTS | EBOOKS | EXAM PAPERS | TIME TABLE | ALL TYPE OF SYLLABUS | MBA | MCA | ENGINEERING | BE | FREE MATERIAL PROVEDE

SHARE YOUR MATERIAL ALSO.. PLEASE SEND ME YOUR MATERIAL WHO SHARE WITH PEOPLE WE PUBLISH IN GTU MATERIAL WITH YOU NAME.. PLEASE SEND US YOUR NAME, COLLEGE NAME, AND STREAM SO THAT WE CAN PUBLISH WITH YOUR NAME..

THANKS YOU SO MUCH.....

Wednesday 22 December 2010

SYSTEM SOFTWARE UNI QUESTIONs

Monday 20 December 2010

Assignment – III

Subject
: Structured & Object Oriented Analysis & Design Methodology
Subject Code
: 630001
Department
: MCA
Assignment Date
: 22th Aug, 2010
: 30th Aug, 2010
  1. List advantages of using data flow approaches.
  2. What is the difference between Logical DFD and Physical DFD?
  3. Define Following terms
(a)    Event Response Table
(b)   Trigger
(c)    Base Element
(d)   Derived Element
(e)    Primitive Process
  1. What is partitioning? List reasons for partitioning DFD.
  2. Define the terms
(a)    Data Dictionary
(b)   Metadata
(c)    Data Repository
(d)   DTD
  1. List need for understanding the Data Dictionary.
  2. Explain with Example
(a)    Structured English
(b)   Decision Table
(c)    Decision Tree
  1. Define Process Specification. List reasons for producing it.
  2. What is horizontal balancing? Why it is desirable to balance each process?

Wednesday 15 December 2010

ACCOUNT(LEDGER)

LEDGER

The complete set of accounts for a business entry is called a general ledger. It is the “reference book” of the accounting system and is used to classify and summarize transactions and to prepare data for financial statements. It is also a valuable source of information for managerial purposes, giving for example the amount of sales for the period or the cash balance at the end of the period.

SPECIMEN RULING OF LEDGER


From the above specimen rulings of ledger account, we can observe the following points:

1.    Ledger Account is usually in the “T” form which contains two sides-Debit side and Credit side.
2.    Left hand side is called Debit Side (Dr.)
3.    Right hand side is called Credit Side (Cr.)
4.    Each side further divided into four columns:
a.      Column 1 meant for date, month and year.
b.      Column 2 meant for particulars.
c.      ‘F’ Stands for Folio (Page Number) of the Journal or Subsidiary Books.
d.      Account to be Debited of Credited.
5.    The names of accounts to be debited find an entry on the left side.
6.    The names of accounts to be credited find an entry on the right side.


BALANCING OF LEDGER ACCOUNT
The Following procedure to be adopted, while balancing of various accounts in the ledger.
1.      Debit and Credit sides of an accounts are totalled separately.
2.      Find the difference between the total of both sides.
3.      The difference is entered on the side on which the total is smaller and this difference is the closing balance shown by the account and this will be carried forward to the next years as the “Opening balance” in the account.
4.      If the debit side of an amount is more, it is called Debit Balance and it entered on the credit side to close the account and written as by balance c/d.
5.      If the credit side of an amount is more, it is called Credit Balance and it entered on the debit side to close the account and written as by balance c/d.
JOURNAL Vs LEDGER

Journal
Ledger
1.    Journal is the book of Original Entry of First Entry
2.    It is the book of Chronological Record
3.    The process of recording in the journal is called journalizing
4.    Journal as a book supported by greater sources of evidence.
5.    Journal lays focus on recording transactions
6.    The process of Journalizing is a continuous one.
1.    Ledger is the book of Second entry
2.    It is the book of Analytical Record.
3.    The process of recording in ledger is posting.
4.    Ledger is dependent on journal.
5.    Ledger focuses on process of classification of grouping of different heads of accounts.
6.    The process of posting in ledger to be done according to the needs and convenience.

Joining Tables in SQL Queries

Joining Tables in SQL Queries

Types of Joins

Sometimes it is necessary to work with multiple tables as though they were a single entity. Then a single SQL sentence can manipulate data from all the tables. Joins are used to achieve this. Tables are joined on columns that have the same data type and data width in the tables.

While different implementations have many ways of joining tables, the most common  types of joins are

EQUIJOINS / INNER JOINS
NATURAL JOINS
NON-EQUIJOINS
OUTER JOINS
SELF JOINS
CROSS JOIN

Syntax:     

ANSI-style

SELECT<(columnName1)>,<columnName2>,<(columnName N)>

           FROM<Table Name1>

                        INNER JOIN <TableName2>I
ON<TableName1>.<columnName1>=<TableName2>.<column     ame2>
WHERE<condition>
ORDER  BY
<columnName1>,<columnName2>,<columnNameN>

Theta-style

SELECT<( columnName1)>,<columnName2>,<(columnName N)>

           FROM<Table Name1>,<TableName2>

  WHERE<TableName1>.<columnName1>=<TableName2>.
               <columnName2>
   AND<condition>
   ORDER  BY
   <columnName1>,<columnName2>,<columnNameN>

In the above syntax:
q  ColumnName1 in TableNmae1 is usually that table’s Primary key
q  ColumnName2 in TableNmae2 is a Foreign Key in that table
q  ColumnName1 and ColumnName2 must have the same data type and for certain data types, the same size

Component Locations of a Join Condition     

As you have learned from previous hours, the SELECT and FROM clauses are both required SQL statement elements; the WHERE clause is a required element of a SQL statement when joining tables. The tables being joined are listed in the FROM clause. The join is performed in the WHERE clause. Several operators can be used to join tables, such as =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT; they can all be used to join tables. However, the most common operator is the equal symbol.

Joins of Equality

Perhaps the most used and important of the joins is the EQUIJOIN, also referred to as an INNER JOIN. The EQUIJOIN joins two tables with a common column in which each is usually the primary key.

THETA style:

The syntax for an EQUIJOIN is
SELECT TABLE1.COLUMN1, TABLE2.COLUMN2...    
FROM TABLE1, TABLE2 [, TABLE3 ]
WHERE TABLE1.COLUMN_NAME = TABLE2.COLUMN_NAME
[ AND TABLE1.COLUMN_NAME = TABLE3.COLUMN_NAME ]
NOTE

Take note of the sample SQL statements. Indentation is used in the SQL statements to improve overall readability. Indentation is not required, but is recommended.
Look at the following example:
SELECT EMPLOYEE_TBL.EMP_ID,
    EMPLOYEE_PAY_TBL.DATE_HIRE
FROM EMPLOYEE_TBL,
    EMPLOYEE_PAY_TBL
WHERE EMPLOYEE_TBL.EMP_ID = EMPLOYEE_PAY_TBL.EMP_ID;
This SQL statement returns the employee identification and the employee's date of hire. The employee identification is selected from the EMPLOYEE_TBL (although it exists in both tables, you must specify one table), whereas the hire date is selected from the EMPLOYEE_PAY_TBL. Because the employee identification exists in both tables, both columns must be justified with the table name. By justifying the columns with the table names, you tell the database server where to get the data.

Data in the following example is selected from tables EMPLOYEE_TBL and EMPLOYEE_PAY_TBL tables because desired data resides in each of the two tables. An equality join is used.
SELECT EMPLOYEE_TBL.EMP_ID, EMPLOYEE_TBL.LAST_NAME,
    EMPLOYEE_PAY_TBL.POSITION
FROM EMPLOYEE_TBL, EMPLOYEE_PAY_TBL
WHERE EMPLOYEE_TBL.EMP_ID = EMPLOYEE_PAY_TBL.EMP_ID;
EMP_ID    LAST_NAM POSITION
--------- -------- -------------
311549902 STEPHENS MARKETING
889 PLEW     TEAM LEADER442346
213764555 GLASS    SALES MANAGER
313782439 GLASS    SALESMAN
220984332 WALLACE  SHIPPER
443679012 SPURGEON SHIPPER
6 rows selected.
Notice that each column in the SELECT clause is preceded by the associated table name in order to identify each column. This is called qualifying columns in a query. Qualifying columns is only necessary for columns that exist in more than one table referenced by a query. You usually qualify all columns for consistency and to avoid any questions when debugging or modifying SQL code.


Natural Joins

A NATURAL JOIN is nearly the same as the EQUIJOIN; however, the NATURAL JOIN differs from the EQUIJOIN by eliminating duplicate columns in the joining columns. The JOIN condition is the same, but the columns selected differ.
The syntax is as follows:
SELECT TABLE1.*, TABLE2.COLUMN_NAME
    [ TABLE3.COLUMN_NAME ]
FROM TABLE1, TABLE2 [ TABLE3 ]
WHERE TABLE1.COLUMN_NAME = TABLE2.COLUMN_NAME
[ AND TABLE1.COLUMN_NAME = TABLE3.COLUMN ]
Look at the following example:
SELECT EMPLOYEE_TBL.*, EMPLOYEE_PAY_TBL.SALARY
FROM EMPLOYEE_TBL,
   EMPLOYEE_PAY_TBL
WHERE EMPLOYEE_TBL.EMP_ID = EMPLOYEE_PAY_TBL.EMP_ID;
This SQL statement returns all columns from EMPLOYEE_TBL and SALARY from the EMPLOYEE_PAY_TBL. The EMP_ID is in both tables, but is retrieved only from the EMPLOYEE_TBL because both contain the same information and do not need to be selected.
The following example selects all columns from the EMPLOYEE_TBL table and only one column from the EMPLOYEE_PAY_TBL table. Remember that the asterisk (*) represents all columns of a table.
SELECT EMPLOYEE_TBL.*, EMPLOYEE
SELECT EMPLOYEE_TBL.*, EMPLOYEE_PAY_TBL.POSITION
FROM EMPLOYEE_TBL, EMPLOYEE_PAY_TBL
WHERE EMPLOYEE_TBL.EMP_ID = EMPLOYEE_PAY_TBL.EMP_ID;



Using Table Aliases

The use of table aliases means to rename a table in a particular SQL statement. The renaming is a temporary change. The actual table name does not change in the database. As you will learn later in this hour, giving the tables aliases is a necessity for the SELF JOIN. Giving tables aliases is most often used to save keystrokes, which results in the SQL statement being shorter and easier to read. In addition, fewer keystrokes means fewer keystroke errors. Also, programming errors are typically less frequent if you can refer to an alias, which is often shorter in length and more descriptive of the data with which you are working. Giving tables aliases also means that the columns being selected must be qualified with the table alias. The following are some examples of table aliases and the corresponding columns:
SELECT E.EMP_ID, EP.SALARY, EP.DATE_HIRE, E.LAST_NAME
FROM EMPLOYEE_TBL E,
   EMPLOYEE_PAY_TBL EP
WHERE E.EMP_ID = EP.EMP_ID
AND EP.SALARY > 20000;
The tables have been given aliases in the preceding SQL statement. The EMPLOYEE_TBL has been renamed E. The EMPLOYEE_PAY_TBL has been renamed EP. The choice of what to rename the tables is arbitrary. The letter E is chosen because the EMPLOYEE_TBL starts with E. Because the EMPLOYEE_PAY_TBL also begins with the letter E, you could not use E again. Instead, the first letter (E) and the first letter of the second word in the name (PAY) are used as the alias. The selected columns were justified with the corresponding table alias. Note that SALARY was used in the WHERE clause and must also be justified with the table alias.

Joins of Non-Equality

NON-EQUIJOIN joins two or more tables based on a specified column value not equaling a specified column value in another table. The syntax for the NON-EQUIJOIN is
FROM TABLE1, TABLE2 [, TABLE3 ]
WHERE TABLE1.COLUMN_NAME != TABLE2.COLUMN_NAME
[ AND TABLE1.COLUMN_NAME != TABLE2.COLUMN_NAME ]
An example is as follows:
SELECT EMPLOYEE_TBL.EMP_ID, EMPLOYEE_PAY_TBL.DATE_HIRE
FROM EMPLOYEE_TBL,
   EMPLOYEE_PAY_TBL
WHERE EMPLOYEE_TBL.EMP_ID != EMPLOYEE_PAY_TBL.EMP_ID;
The preceding SQL statement returns the employee identification and the date of -hire for all employees who do not have a corresponding record in both tables. The following example is a join of non-equality:
SELECT E.EMP_ID, E.LAST_NAME, P.POSITION
FROM EMPLOYEE_TBL E,
   EMPLOYEE_PAY_TBL P
WHERE E.EMP_ID <> P.EMP_ID;
EMP_ID    LAST_NAM POSITION
--------- -------- -------------
442346889 PLEW     MARKETING
213764555 GLASS    MARKETING
30 rows selected.
You may be curious why 30 rows were retrieved when only 6 rows exist in each table. For every record in EMPLOYEE_TBL, there is a corresponding record in EMPLOYEE_PAY_TBL. Because non-equality was tested in the join of the two tables, each row in the first table is paired with all rows from the second table, except for its own corresponding row. This means that each of the 6 rows are paired with 5 unrelated rows in the second table; 6 rows multiplied by 5 rows equals 30 rows total.
In the previous section's test for equality example, each of the six rows in the first table were paired with only one row in the second table (each row's corresponding row); six rows multiplied by one row yields a total of six rows.

CAUTION
When using NON-EQUIJOINs, you may receive several rows of data that are of no use to you. Check your results carefully.

Outer Joins

A join that includes rows even if they do not have related rows in the joined table. You can create three variations of an outer join to specify the unmatched rows to be included:
·         Left outer join   All rows from the first-named table (the "left" table, which appears leftmost in the JOIN clause) are included. Unmatched rows in the right table do not appear.[*the left join can be used which returns all the rows from the first table,even if there are no matches in the second  table*] For example, the following SQL statement illustrates a left outer join between the titles and publishers tables to include all titles, even those you dont have publisher information for:
·         SELECT titles.title_id, titles.title,
·            publishers.pub_name
·         FROM titles LEFT OUTER JOIN publishers ON 
·            titles.pub_id = publishers.pub_id
{Notice the keyword LEFT JOIN in the first solution (Ansi-style) and the (+) in the second solution (Thetastyle). This indicates that all rows from the first table will be displayed even though there exists no matching rows in the second table.}
·         Right outer join   All rows in the second-named table (the "right" table, which appears rightmost in the JOIN clause) are included. Unmatched rows in the left table are not included. For example, a right outer join between the titles and publishers tables will include all publishers, even those who have no titles in the titles table.
{Notice the keyword RIGHT JOIN in the first solution (Ansi-style) and the (+) in the second solution (Theta-style). This indicates that all rows from the second table will be displayed even though there exists no matching rows in the first table.}
·         Full outer join   All rows in all joined tables are included, whether they are matched or not. For example, a full outer join between titles and publishers shows all titles and all publishers, even those that have no match in the other table.
Note   Some databases, such as Oracle, do not support full outer joins. 

An OUTER JOIN is used to return all rows that exist in one table, even though corresponding rows do not exist in the joined table. The (+) symbol is used to denote an OUTER JOIN in a query. The (+) is placed at the end of the table name in the WHERE clause. The table with the (+) should be the table that does not have matching rows. In many implementations, the OUTER JOIN is broken down into joins called LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN. The OUTER JOIN in these implementations is normally optional.
NOTE
You must check your particular implementation for exact usage and syntax of the OUTER JOIN. The (+) symbol is used by some major implementations, but is non-standard.
The general syntax is
ANSI-style

SELECT<(t1.columnName1)>,<t2.columnName2>,<(t2.columnName N)>

           FROM  <t1>

{RIGHT | LEFT | FULL} [OUTER] JOIN

ON t2;


THETA Style
FROM TABLE1, TABLE2 [, TABLE3 ]
WHERE TABLE1.COLUMN_NAME[(+)] = TABLE2.COLUMN_NAME[(+)]
[ AND TABLE1.COLUMN_NAME[(+)] = TABLE3.COLUMN_NAME[(+)]]
NOTE
The OUTER JOIN can only be used on one side of a join condition; however, you can use an OUTER JOIN on more than one column of the same table in the join condition.
The concept of the OUTER JOIN is explained in the next two examples. In the first example, the product description and the quantity ordered are selected; both values are extracted from two separate tables. One important factor to keep in mind is that there may not be a corresponding record in the ORDERS_TBL table for every product. A regular join of equality is performed:
SELECT P.PROD_DESC, O.QTY
FROM PRODUCTS_TBL P,
   ORDERS_TBL O
WHERE P.PROD_ID = O.PROD_ID;
PROD_DESC                        QTY
-------------------------------- ---
WITCHES COSTUME                    1
PLASTIC PUMPKIN 18 INCH           25
PLASTIC PUMPKIN 18 INCH            2
LIGHTED LANTERNS                  10
FALSE PARAFFIN TEETH              20
KEY CHAIN                          1
6 rows selected.
Only six rows were selected, but there are 10 distinct products. You want to display all products, whether the products have been placed on order or not.
The next example accomplishes the desired output through the use of an OUTER JOIN. Oracle's syntax is used for the OUTER JOIN.
SELECT P.PROD_DESC, O.QTY
FROM PRODUCTS_TBL P,
   ORDERS_TBL O
WHERE P.PROD_ID = O.PROD_ID(+);
PROD_DESC                        QTY
-------------------------------        -------
WITCHES COSTUME                    1
ASSORTED MASKS
12 rows selected.
All products were returned by the query, even though they may not have had a quantity ordered. The outer join is inclusive of all rows of data in the PRODUCTS_TBL table, whether a corresponding row exists in the ORDERS_TBL table or not.

Self Joins

The SELF JOIN is used to join a table to itself, as if the table were two tables, temporarily renaming at least one table in the SQL statement. The syntax is as follows:
SELECT A.COLUMN_NAME, B.COLUMN_NAME, [ C.COLUMN_NAME ]
FROM TABLE1 A, TABLE2 B [, TABLE3 C ]
WHERE A.COLUMN_NAME = B.COLUMN_NAME
[ AND A.COLUMN_NAME = C.COLUMN_NAME ]
The following is an example:
SELECT A.LAST_NAME, B.LAST_NAME, A.FIRST_NAME
FROM EMPLOYEE_TBL A,
   EMPLOYEE_TBL B
WHERE A.LAST_NAME = B.LAST_NAME;
The preceding SQL statement returns the employees' first name for all the employees with the same last name from the EMPLOYEE_TBL. Self joins are useful when all of the data you want to retrieve resides in one table, but you must somehow compare records in the table to other records in the table.
Another common example used to explain a self join is as follows. Suppose you have a table that stores an employee identification number, the employee's name, and the employee identification number of the employee's manager. You may want to produce a list of all employees and their managers' names. The problem is that the manager name does not exist in the table, only the employee name:
SELECT * FROM EMP;
ID   NAME      MGR_ID
---- --------- ------
1    JOHN          0
2    MARY          1
3    STEVE         1
4    JACK          2
5    SUE           2
In the following example, we have included the table EMP twice in the FROM clause of the query, giving the table two aliases for the purpose of the query. By providing two aliases, it is as if you are selecting from two distinct tables. All managers are also employees, so the join condition between the two tables compares the value of the employee identification number from the first table with the manager identification number in the second table. The first table acts as a table that stores employee information, whereas the second table acts as a table that stores manager information:
SELECT E1.NAME, E2.NAME
FROM EMP E1, EMP E2
WHERE E1.MGR_ID = E2.ID;
 
NAME      NAME
--------- ---------
MARY      JOHN
STEVE     JOHN
JACK      MARY
SUE       MARY

Joining on Multiple Keys

Most join operations involve the merging of data based on a key in one table and a key in another table. Depending on how your database has been designed, you may have to join on more than one key field to accurately depict that data in your database. You may have a table that has a primary key that is comprised of more than one column. You may also have a foreign key in a table that consists of more than one column, which references the multiple column primary key.
Consider the following Oracle tables that are used here for examples only:
SQL> desc prod
 Name                                       Null?   Type
 ----------------------------------------- -------- ----------------------------
 SERIAL_NUMBER                             NOT NULL NUMBER(10)
 VENDOR_NUMBER                             NOT NULL NUMBER(10)
 PRODUCT_NAME                              NOT NULL VARCHAR2(30)
 COST                                      NOT NULL NUMBER(8,2)
SQL> desc ord
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ORD_NO                                    NOT NULL NUMBER(10)
 PROD_NUMBER                               NOT NULL NUMBER(10)
 VENDOR_NUMBER                             NOT NULL NUMBER(10)
 QUANTITY                                  NOT NULL NUMBER(5)
 ORD_DATE                                  NOT NULL DATE  
The primary key in PROD is the combination of the columns SERIAL_NUMBER and VENDOR_NUMBER. Perhaps two products can have the same serial number within the distribution company, but each serial number is unique per vendor.
The foreign key in ORD is also the combination of the columns SERIAL_NUMBER and VENDOR_NUMBER.
When selecting data from both tables (PROD and ORD), the join operation may appear as follows:
SELECT P.PRODUCT_NAME, O.ORD_DATE, O.QUANTITY
FROM PROD P, ORD O
WHERE P.SERIAL_NUMBER = O.SERIAL_NUMBER
 AND P.VENDOR_NUMBER = O.VENDOR_NUMBER;
CROSS Joins
          A cross join returns what’s known as a Cartesian product. This means that the join combines every row from the left table with every row in the right table. As can be imagined, sometimes this join produces a mess, but under the right circumstances, it can be very useful. This type of join can be used in situations where it is desired, to select all possible combinations of rows and columns from both tables. This kind of join is usually not preferred as it may run for a very long time and produce a huge result set that may not be useful.

ANSI-style:
           
            SELECT e.ename, e.emp_no, e.dept_no, d.deptnm
                        FROM emp e CROSS JOIN dept d;



Guidelines for creating joins

q  When writing a select statement that joins tables, precede the column name with the table with the table name for clarity.

q  If the same column name appears in more then one table, the column name must be prefixed with the table name.

q  The WHERE clause, is the most critical clause in a join select statement. Always make sure to include the WHERE clause.

Twitter Delicious Facebook Digg Stumbleupon Favorites More

 
Design by Free WordPress Themes | Bloggerized by Lasantha - Premium Blogger Themes | Grants For Single Moms