One-to-many relation can be implemented by using two tables.. in two ways :
e.g we have a Department table and a Student Table
1. Department :
DeptID -> Primary Key
DeptName
DeptHead
Student :
StudentID -> Primary Key
StudentName
DeptID -> Foreign Key
2. Department :
DeptID -> Primary Key
DeptName
DeptHead
Student :
StudentID -> Composite Primary Key - 1
StudentName
DeptID -> Composite Primary Key - 2
Showing posts with label database. Show all posts
Showing posts with label database. Show all posts
July 11, 2010
January 7, 2010
How to connect two databases
If the two databases are on the same server you can reference either one in the other by using the fully qualified object name: [database].[owner].[object]
November 19, 2009
SQL questions
How do you implement one-to-one, one-to-many and many-to-many
relationships while designing tables?
One-to-One relationship can be implemented as a single table and
rarely as two tables with primary and foreign key relationships.
One-to-Many relationships are implemented by splitting the data into
two tables with primary key and foreign key relationships.
Many-to-Many relationships are implemented using a junction table with
the keys from both the tables forming the composite primary key of the
junction table.
relationships while designing tables?
One-to-One relationship can be implemented as a single table and
rarely as two tables with primary and foreign key relationships.
One-to-Many relationships are implemented by splitting the data into
two tables with primary key and foreign key relationships.
Many-to-Many relationships are implemented using a junction table with
the keys from both the tables forming the composite primary key of the
junction table.
November 10, 2009
Stored Procedure in DataBase
A stored procedure is a subroutine available to applications accessing a relational database system. Stored procedures (sometimes called a proc, sproc, StoPro, or SP) are actually stored in the database data dictionary.
Typical uses for stored procedures include data validation (integrated into the database) or access control mechanisms. Furthermore, stored procedures are used to consolidate and centralize logic that was originally implemented in applications. Large or complex processing that might require the execution of several SQL statements is moved into stored procedures, and all applications call the procedures only.
Stored procedures are similar to user-defined functions (UDFs). The major difference is that UDFs can be used like any other expression within SQL statements, whereas stored procedures must be invoked using the CALL statement
CALL procedure(…)
or
EXECUTE procedure(…)
Typical uses for stored procedures include data validation (integrated into the database) or access control mechanisms. Furthermore, stored procedures are used to consolidate and centralize logic that was originally implemented in applications. Large or complex processing that might require the execution of several SQL statements is moved into stored procedures, and all applications call the procedures only.
Stored procedures are similar to user-defined functions (UDFs). The major difference is that UDFs can be used like any other expression within SQL statements, whereas stored procedures must be invoked using the CALL statement
CALL procedure(…)
or
EXECUTE procedure(…)
Triggers in DataBase
A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for keeping the integrity of the information on the database. For example, when a new record (representing a new worker) added to the employees table, new records should be created also in the tables of the taxes, vacations, and salaries.
The need and the usage
Triggers are commonly used to:
* prevent changes (e.g. prevent an invoice from being changed after it's been mailed out)
* log changes (e.g. keep a copy of the old data)
* audit changes (e.g. keep a log of the users and roles involved in changes)
* enhance changes (e.g. ensure that every change to a record is time-stamped by the server's clock, not the client's)
* enforce business rules (e.g. require that every invoice have at least one line item)
* execute business rules (e.g. notify a manager every time an employee's bank account number changes)
* replicate data (e.g. store a record of every change, to be shipped to another database later)
* enhance performance (e.g. update the account balance after every detail transaction, for faster queries)
Some systems also support non-data triggers, which fire in response to Data Definition Language (DDL) events such as creating tables, or runtime events such as logon, commit, and rollback, and may also be used for auditing purposes.
The major features of database triggers, and their effects, are:
* do not accept parameters or arguments (but may store affected-data in temporary tables)
* cannot perform commit or rollback operations because they are part of the triggering SQL statement (only through autonomous transactions)
* can cancel a requested operation
* can cause mutating table errors, if they are poorly written.
DML Triggers
There are typically three triggering events that cause data triggers to 'fire':
* INSERT event (as a new record is being inserted into the database).
* UPDATE event (as a record is being changed).
* DELETE event (as a record is being deleted).
Structurally, triggers are either "row triggers" or "statement triggers". Row triggers define an action for every row of a table, while statement triggers occur only once per INSERT, UPDATE, or DELETE statement. DML triggers cannot be used to audit data retrieval via SELECT statements, because SELECT is not a triggering event.
Furthermore, there are "BEFORE triggers" and "AFTER triggers" which run in addition to any changes already being made to the database, and "INSTEAD OF trigger" which fully replace the database's normal activity.
Triggers do not accept parameters, but they do receive information in the form of implicit variables. For row-level triggers, these are generally OLD and NEW variables, each of which have fields corresponding to the columns of the affected table or view; for statement-level triggers, something like SQL Server's Inserted and Deleted tables may be provided so the trigger can see all the changes being made.
For data triggers, the general order of operations will be as follows:
1. a statement requests changes on a row: OLD represents the row as it was before the change (or is all-null for inserted rows), NEW represents the row after the changes (or is all-null for deleted rows)
2. each statement-level BEFORE trigger is fired
3. each row-level BEFORE trigger fires, and can modify NEW (but not OLD); each trigger can see NEW as modified by its predecessor, they are chained together
4. if an INSTEAD OF trigger is defined, it is run using OLD and NEW as available at this point
5. if no INSTEAD OF trigger is defined, the database modifies the row according to its normal logic; for updatable views, this may involve modifying one or more other tables to achieve the desired effect; if a view is not updatable, and no INSTEAD OF trigger is provided, an error is raised
6. each row-level AFTER trigger fires, and is given NEW and OLD, but its changes to NEW are either disallowed or disregarded
7. each statement-level AFTER trigger is fired
8. implied triggers are fired, such as referential actions in support of foreign key constraints: on-update or on-delete CASCADE, SET NULL, and SET DEFAULT rules
In ACID databases, an exception raised in a trigger will cause the entire stack of operations to be rolled back, including the original statement.
The need and the usage
Triggers are commonly used to:
* prevent changes (e.g. prevent an invoice from being changed after it's been mailed out)
* log changes (e.g. keep a copy of the old data)
* audit changes (e.g. keep a log of the users and roles involved in changes)
* enhance changes (e.g. ensure that every change to a record is time-stamped by the server's clock, not the client's)
* enforce business rules (e.g. require that every invoice have at least one line item)
* execute business rules (e.g. notify a manager every time an employee's bank account number changes)
* replicate data (e.g. store a record of every change, to be shipped to another database later)
* enhance performance (e.g. update the account balance after every detail transaction, for faster queries)
Some systems also support non-data triggers, which fire in response to Data Definition Language (DDL) events such as creating tables, or runtime events such as logon, commit, and rollback, and may also be used for auditing purposes.
The major features of database triggers, and their effects, are:
* do not accept parameters or arguments (but may store affected-data in temporary tables)
* cannot perform commit or rollback operations because they are part of the triggering SQL statement (only through autonomous transactions)
* can cancel a requested operation
* can cause mutating table errors, if they are poorly written.
DML Triggers
There are typically three triggering events that cause data triggers to 'fire':
* INSERT event (as a new record is being inserted into the database).
* UPDATE event (as a record is being changed).
* DELETE event (as a record is being deleted).
Structurally, triggers are either "row triggers" or "statement triggers". Row triggers define an action for every row of a table, while statement triggers occur only once per INSERT, UPDATE, or DELETE statement. DML triggers cannot be used to audit data retrieval via SELECT statements, because SELECT is not a triggering event.
Furthermore, there are "BEFORE triggers" and "AFTER triggers" which run in addition to any changes already being made to the database, and "INSTEAD OF trigger" which fully replace the database's normal activity.
Triggers do not accept parameters, but they do receive information in the form of implicit variables. For row-level triggers, these are generally OLD and NEW variables, each of which have fields corresponding to the columns of the affected table or view; for statement-level triggers, something like SQL Server's Inserted and Deleted tables may be provided so the trigger can see all the changes being made.
For data triggers, the general order of operations will be as follows:
1. a statement requests changes on a row: OLD represents the row as it was before the change (or is all-null for inserted rows), NEW represents the row after the changes (or is all-null for deleted rows)
2. each statement-level BEFORE trigger is fired
3. each row-level BEFORE trigger fires, and can modify NEW (but not OLD); each trigger can see NEW as modified by its predecessor, they are chained together
4. if an INSTEAD OF trigger is defined, it is run using OLD and NEW as available at this point
5. if no INSTEAD OF trigger is defined, the database modifies the row according to its normal logic; for updatable views, this may involve modifying one or more other tables to achieve the desired effect; if a view is not updatable, and no INSTEAD OF trigger is provided, an error is raised
6. each row-level AFTER trigger fires, and is given NEW and OLD, but its changes to NEW are either disallowed or disregarded
7. each statement-level AFTER trigger is fired
8. implied triggers are fired, such as referential actions in support of foreign key constraints: on-update or on-delete CASCADE, SET NULL, and SET DEFAULT rules
In ACID databases, an exception raised in a trigger will cause the entire stack of operations to be rolled back, including the original statement.
November 8, 2009
DriverManager vs DataSource
DriverManager is just that: a thin manager class for raw jdbc drivers. A DataSource is both more abstract and more powerful: it is a place where you get your database connections from. This means a couple of things. It means that a DataSource can usually be configured and managed by the application server instead of your application. It means that the connections you get from a DataSource can come from a connection pool for performance. It means that the connections may participate in a container-managed distributed transaction without you having to worry about the nitty gritty of it.
And even if your environment doesn't support DataSources, given that org.apache.struts.util.GenericDataSource can convert any old JDBC driver into a pooled DataSource I don't see why anyone would still want to use a DriverManager and a proprietary connection pool
DataSource and the DriverManager are the two basic ways to connect to a database in a JEE application. The DriverManager is older facility, DataSource is newer. It is recommended to use the new DataSource facility to connect to databases and other resources. DataSource facility has several advantages over DriverManager facility. Using DataSource increases portability. The DataSource enables connection pooling and distributed transactions, the DriverManager does not allow such techniques. Properties of a DataSource are kept in a configuration file. Any changes to the data source or database drivers are made in the configuration file. In case of a DriverManager, these properties are hard coded in the application and for any changes we must recompile the code.
And even if your environment doesn't support DataSources, given that org.apache.struts.util.GenericDataSource can convert any old JDBC driver into a pooled DataSource I don't see why anyone would still want to use a DriverManager and a proprietary connection pool
DataSource and the DriverManager are the two basic ways to connect to a database in a JEE application. The DriverManager is older facility, DataSource is newer. It is recommended to use the new DataSource facility to connect to databases and other resources. DataSource facility has several advantages over DriverManager facility. Using DataSource increases portability. The DataSource enables connection pooling and distributed transactions, the DriverManager does not allow such techniques. Properties of a DataSource are kept in a configuration file. Any changes to the data source or database drivers are made in the configuration file. In case of a DriverManager, these properties are hard coded in the application and for any changes we must recompile the code.
What is a dataSource
Interface : javax.sql.DataSource
A factory for connections to the physical data source that this DataSource object represents. An alternative to the DriverManager facility, a DataSource object is the preferred means of getting a connection. An object that implements the DataSource interface will typically be registered with a naming service based on the JavaTM Naming and Directory (JNDI) API.
The DataSource interface is implemented by a driver vendor. There are three types of implementations:
1. Basic implementation -- produces a standard Connection object
2. Connection pooling implementation -- produces a Connection object that will automatically participate in connection pooling. This implementation works with a middle-tier connection pooling manager.
3. Distributed transaction implementation -- produces a Connection object that may be used for distributed transactions and almost always participates in connection pooling. This implementation works with a middle-tier transaction manager and almost always with a connection pooling manager.
A DataSource object has properties that can be modified when necessary. For example, if the data source is moved to a different server, the property for the server can be changed. The benefit is that because the data source's properties can be changed, any code accessing that data source does not need to be changed.
A driver that is accessed via a DataSource object does not register itself with the DriverManager. Rather, a DataSource object is retrieved though a lookup operation and then used to create a Connection object. With a basic implementation, the connection obtained through a DataSource object is identical to a connection obtained through the DriverManager facility.
A factory for connections to the physical data source that this DataSource object represents. An alternative to the DriverManager facility, a DataSource object is the preferred means of getting a connection. An object that implements the DataSource interface will typically be registered with a naming service based on the JavaTM Naming and Directory (JNDI) API.
The DataSource interface is implemented by a driver vendor. There are three types of implementations:
1. Basic implementation -- produces a standard Connection object
2. Connection pooling implementation -- produces a Connection object that will automatically participate in connection pooling. This implementation works with a middle-tier connection pooling manager.
3. Distributed transaction implementation -- produces a Connection object that may be used for distributed transactions and almost always participates in connection pooling. This implementation works with a middle-tier transaction manager and almost always with a connection pooling manager.
A DataSource object has properties that can be modified when necessary. For example, if the data source is moved to a different server, the property for the server can be changed. The benefit is that because the data source's properties can be changed, any code accessing that data source does not need to be changed.
A driver that is accessed via a DataSource object does not register itself with the DriverManager. Rather, a DataSource object is retrieved though a lookup operation and then used to create a Connection object. With a basic implementation, the connection obtained through a DataSource object is identical to a connection obtained through the DriverManager facility.
Subscribe to:
Posts (Atom)