Store an N-ary tree structure in Relational database table

I was asked this question during my tower research interview for the position of Senior script developer.
I came up with the solution to store it in a self referring table which has two columns (parent,child). So, in this kind of table he said that finding the depth of any particular node, i would have to write/call queries equaling the depth of the node under consideration. He asked how can you improve the data structure to store this kind of structure in tables ??

http://iamcam.wordpress.com/2006/03/24/storing-hierarchical-data-in-a-database-part-2a-modified-preorder-tree-traversal/
http://www.sitepoint.com/hierarchical-data-database-2/

Other questions:

Linux:

Find all files open under a process -> ls -l /proc/{PID}/fd

How to setup a password-less SSH

Remove the soft-links in a directory and replace them with the copy of the original file.

List all files in a directory recursively which are not owned by a particular user.

Array Based:

Given 2K+1 numbers out of which K numbers occur twice each and one number occurs just once, find this one time occurring number. -> used XOR

Given numbers from 1 to K with 2 numbers missing, find the 2 missing numbers. -> used XOR based partitions.

Java:

When is finally called. What if you return from inside try and have a return inside finally as well ? What if finally throws an exception ?

About the synchronized() keyword.

Database:

How would you store a tree in the relational tables?

Interviewer: Manikant K.

Handling concurrency in a Web application

Reading: http://stackoverflow.com/questions/7455726/handling-the-concurrent-request-while-persisting-in-oracle-database

To handle concurrency in a web site a common practice it to have a column on each record that allows you to check it has not been updated since you got it. Either last update date or a sequential version number (auto incremented by a trigger).

Typically you will read the data (plus the concurrency column)

SELECT seat,etc,version_no
FROM t1
WHERE column = a_value

Then when the user eventually gets round to booking the seat the update will work unless there has been an update.

(the version number or update date will change after every update)

BEGIN
    UPDATE t1
    SET seatTaken = true
    WHERE seatid = .....
    AND version_no = p_version
    RETURNING version_no INTO p_version;
EXCEPTION WHEN NOT_FOUND THEN
    --Generate a custom exception 
    --concurrency viloation the record has been updated alreadyEND;

the trigger to auto update the version number would look a little like this

CREATE OR REPLACE TRIGGER t1_version
AFTER INSERT OR UPDATE ON t1
FOR EACH ROWBEGIN
    IF :new.version_no IS NULL THEN
       :new.version_no  := 0;
    ELSE
       :new.version_no  := :old.version_no  + 1;
    END IF;
END;

SQL breezer

Here is a short SQL breezer i made for revising SQL for interviews.

SQL SELECT command syntax :
SELECT select_list
[ INTO new_table ]
FROM table_source
[ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]

The WHERE clause is applied before the GROUP BY clause.
Because it acts on the results of the GROUP BY clause, aggregation functions can be used in the HAVING clause predicate.

Example Queries:
SELECT Book.title,
       count(*) AS Authors
   FROM  Book JOIN Book_author
      ON Book.isbn = Book_author.isbn
   GROUP BY Book.title;

• INSERT adds rows (formally tuples) to an existing table, e.g.,:

INSERT INTO My_table
       (field1, field2, field3)
   VALUES
       ('test', 'N', NULL);

• UPDATE modifies a set of existing table rows, e.g.,:

UPDATE My_table
   SET field1 = 'updated value'
   WHERE field2 = 'N';

• DELETE removes existing rows from a table, e.g.,:

DELETE FROM My_table
   WHERE field2 = 'N';

Data definition

The Data Definition Language (DDL) manages table and index structure. The most basic items of DDL are the CREATE, ALTER, RENAME, DROP and TRUNCATE statements:

• CREATE creates an object (a table, for example) in the database, e.g.,:

CREATE TABLE My_table(
   my_field1   INT,
   my_field2   VARCHAR(50),
   my_field3   DATE         NOT NULL,
   PRIMARY KEY (my_field1, my_field2)
);

• ALTER modifies the structure of an existing object in various ways, for example, adding a column to an existing table or a constraint, e.g.,:

ALTER TABLE My_table ADD my_field4 NUMBER(3) NOT NULL;

• TRUNCATE deletes all data from a table in a very fast way, deleting the data inside the table and not the table itself. It usually implies a subsequent COMMIT operation, i.e., it cannot be rolled back.

TRUNCATE TABLE My_table;

• DROP deletes an object in the database, usually irretrievably, i.e., it cannot be rolled back, e.g.,:

DROP TABLE My_table;

Null and three-valued logic (3VL)

The idea of Null was introduced into SQL to handle missing information in the relational model. The introduction of Null (or Unknown) along with True and False is the foundation of three-valued logic. Null does not have a value (and is not a member of any data domain) but is rather a placeholder or “mark” for missing information. Therefore comparisons with Null can never result in either True or False but always in the third logical result, Unknown.

p AND q p True False Unknown q True True False Unknown False False False False Unknown Unknown False Unknown

Transaction controls

Transactions, if available, wrap DML operations:

• START TRANSACTION (or BEGIN WORK, or BEGIN TRANSACTION, depending on SQL dialect) mark the start of a database transaction, which either completes entirely or not at all.
• SAVE TRANSACTION (or SAVEPOINT ) save the state of the database at the current point in transaction

CREATE TABLE tbl_1(id int);
 INSERT INTO tbl_1(id) value(1);
 INSERT INTO tbl_1(id) value(2);
COMMIT;
 UPDATE tbl_1 SET id=200 WHERE id=1;
SAVEPOINT id-1upd;
 UPDATE tbl_1 SET id=1000 WHERE id=2;
ROLLBACK TO id-1upd;
 SELECT id FROM tbl_1;

• COMMIT causes all data changes in a transaction to be made permanent.
• ROLLBACK causes all data changes since the last COMMIT or ROLLBACK to be discarded, leaving the state of the data as it was prior to those changes.

Once the COMMIT statement completes, the transaction's changes cannot be rolled back.

ACID properties
1. Atomicity - all operations in a txn fail or succeed as a whole
2. Consistency
3. Isolation - all txns run in isolation.
4. Durability - changes in the DB are permanent.

Data control

The Data Control Language (DCL) authorizes users and groups of users to access and manipulate data. Its two main statements are:

• GRANT authorizes one or more users to perform an operation or a set of operations on an object.
• REVOKE eliminates a grant, which may be the default grant.