Structured Query Language (SQL) is a computer language for creating database and manipulating data. SQL is an ANSI (American National Standard Institute) standard and is supported by almost all Relational Data Base Management Systems (RDBMS) like Oracle, MySQL, SQLServer, MS Access, PostGreSQL etc. SQL has two parts:
* Data Definition Language (DDL): to create, alter, or drop tables and indexes.
* Data Manipulation Language (DML): to insert, update, retrieve or delete the data in the tables.
Here's how to use SQL.
1.Install an RDBMS package. You can download MySQL from http://www.mysql.org for your operating system (OS) and install it using the given instructions. For Windows OS, it can be installed by double-clicking the installer and choosing the default values on each stage.
2.Start Mysql service. In command prompt window, change directory to C:\mysql\bin (if you have installed MySQL under C:) and issue following command to start MySQL service:NET START mysql
3.Start Mysql client. In command prompt window, change directory to C:\mysql\bin and issue the command mysql to get mysql prompt.
4.Create a database. On MySQL prompt, enter command 'create database' followed by any database name. Remember to put semi-colon at the end of command:
create database emp;
5.Set the created database as active one. To do this, issue the 'USE' command followed by database name on mysql prompt:
use emp;
6.Create a table. To do this, use 'CREATE TABLE' command with name and data-type of each table field. You can also specify PRIMARY KEY and any other constraint like NOT NULL. For example:
CREATE TABLE person
(NAME VARCHAR(80) PRIMARY NOT NULL,
DSGN VARCHAR(5),
AGE INTEGER,
PAY INTEGER
);
7.Insert some data into the created table. This is accomplished through 'INSERT INTO' command followed by table name and values to be inserted.
* If you need to insert a row with values for all columns, then use the following command:
INSERT INTO person VALUES ('Kakul','MGR',26,35000);
* If you need to insert values for selected columns only, then you need to specify those column names also in the command as shown:
INSERT INTO person (NAME) VALUES ('Feroz');
Notice that a character value is enclosed within single quotes and each command is terminated with a semi-colon.
8.Update the table.
* Use UPDATE command with SET and name-value pairs like:
UPDATE person SET pay=50000;
* For updating a particular row, use WHERE clause in UPDATE command like:
UPDATE person SET pay = 20000 WHERE NAME = 'Kakul';
9.Retrieve the stored data. Use SELECT command to retrieve the data. For conditional retrieval, you may use WHERE clause. Try following queries:
* To retrieve all COLUMNS and all rows:
SELECT * FROM person;
* To get the sorted list, use ORDER BY clause:
SELECT * FROM person ORDER BY name;
* To retrieve few columns of all rows:
SELECT name FROM person;
* To retrieve all columns of a particular row:
SELECT * FROM person where name = 'Feroz';
* To retrieve selected columns of a particular row:
SELECT pay FROM person WHERE name = 'Kakul';
* To retrieve a row with columns having a particular pattern (i.e. pay of all those employees whose name starts with K):
SELECT pay FROM person WHERE name like 'K%';
* To count number of records in the table (say you want to know number of employees):
SELECT COUNT(*) FROM person;
* To get the sum of a column (say you need to know total pay to be paid):
SELECT SUM(PAY) FROM person;
* Use AND/OR in WHERE clause to retrieve data based on multiple condition:
SELECT * FROM person WHERE name LIKE 'K%' AND pay > 5000;
* To group the results, use GROUP BY as in following:
SELECT * FROM person GROUP BY dsgn;
* To show groups satisfying a criteria, use HAVING as illustrated below:
SELECT * FROM person GROUP BY dsgn HAVING pay > 12000;
* To get results if a field has any of the given value, use IN clause:
SELECT * FROM person where name IN ('Feroz','Kakul');
You may try querying with other functions also like AVG, DISTINCT, BETWEEN etc.
10.Add a column to the table. This is done through ALTER command like:
ALTER TABLE person ADD experience INTEGER;
11.Set an alias for person table using few columns only. To do this, use AS as illustrated below:
SELECT NAME,DSGN FROM person AS employees;
12.Delete records from the table.
* To delete a particular record, use DELETE command with WHERE clause like:
DELETE * FROM person WHERE name='Feroz';
* To delete all records, use:
DELETE FROM person;
All records can also be deleted using TRUNCATE command such as:
TRUNCATE TABLE person;
13.Drop the column added in step 10 above. You have to again use ALTER command with DROP like this:
ALTER TABLE person DROP experience;
Note that with ADD you have to specify data-type of the column also which is obviously not required with DROP.
14.Drop the created table. Use DROP TABLE command followed by table name.
DROP TABLE person;
15.Drop database also. Use DROP DATABASE command followed by database name.
DROP DATABASE emp;
16.Try advance SQL topics like creating views, stored procedures, cursors, join etc.
www.howtodothings.com
Thursday, November 19, 2009
Saturday, October 24, 2009
border="0"/>
Link Exchange
1.Link2Me SEO Link Exchange Directory
Quality directory of webmasters actively seeking link exchange. Improve your search engine rankings and link popularity the easy way. Work clever not hard.
2.Link Market - Free Link Exchange, Link Swap and Link Trade Directory
Have you ever tried to exchange links, swap links, or trade links? Was it hard? Use link market instead; - it is easy to use, free and very smart. It will save you hours of work.
3.The Link Exchange - Your ultimate resource for link exchange!
Quality directory of webmasters actively seeking link exchange. Improve your search engine rankings and link popularity the easy way. Work clever not hard.
2.Link Market - Free Link Exchange, Link Swap and Link Trade Directory
Have you ever tried to exchange links, swap links, or trade links? Was it hard? Use link market instead; - it is easy to use, free and very smart. It will save you hours of work.
3.The Link Exchange - Your ultimate resource for link exchange!
Sunday, July 12, 2009
User-Named Exception Handlers
The technique that is used to bind a numbered exception handler to a name using 'Pragma Exception_init()'.This binding of a numbered exception handler,to a name string, is done in the declare section of the PL/SQL block.
All objects declared in the declare section of a PL/SQL block are not created until actually required within the PL/SQL block. However, the binding of a numbered exception handler to a name must be done exactly when declared not when the exception handler is invoked due to an exception condition.
The Pragma action word is a call to a pre-compile, which immediately binds the numbered exception handler to a name when encountered.
The function Exception_init() takes two parameters the first is the user defined exception name the second is the Oracle engine's exception number. These lines will be included in the Declare section of the PL/SQL block.
Note: The user defined exception name must be the statement that immediately preceds the Pragma Exception_init() statement.
Syntax:
Declare exception_name EXCEPTION;
PRAGMA EXCEPTION_INIT(exception_name,error_code_no);
BEGIN
Using this technique it is possible to bind appropriate numbered exception handlers to names and use these names in the exception section of a PL/SQL block. When this is done the default exception handling code of the exception handler is overridden and the user-defined exception handling code is executed.
Syntax:
DECLARE
exception_name EXCEPTION;
PRAGMA EXCEPTION_INIT(exception_name,error_code_no);
BEGIN
--------
--------
EXCEPTION
WHEN exception_name THEN
END;
All objects declared in the declare section of a PL/SQL block are not created until actually required within the PL/SQL block. However, the binding of a numbered exception handler to a name must be done exactly when declared not when the exception handler is invoked due to an exception condition.
The Pragma action word is a call to a pre-compile, which immediately binds the numbered exception handler to a name when encountered.
The function Exception_init() takes two parameters the first is the user defined exception name the second is the Oracle engine's exception number. These lines will be included in the Declare section of the PL/SQL block.
Note: The user defined exception name must be the statement that immediately preceds the Pragma Exception_init() statement.
Syntax:
Declare exception_name EXCEPTION;
PRAGMA EXCEPTION_INIT(exception_name,error_code_no);
BEGIN
Using this technique it is possible to bind appropriate numbered exception handlers to names and use these names in the exception section of a PL/SQL block. When this is done the default exception handling code of the exception handler is overridden and the user-defined exception handling code is executed.
Syntax:
DECLARE
exception_name EXCEPTION;
PRAGMA EXCEPTION_INIT(exception_name,error_code_no);
BEGIN
--------
--------
EXCEPTION
WHEN exception_name THEN
END;
Pre-determined Internal PL/SQL exceptions
1. DUP_VAL_ON_INDEX: Raised when an insert or update attempts to create two rows with duplicate values in columns constrained by a unique index.
2. LOGIN_DENIED: Raised when an invalid username/password was used to log onto Oracle.
3. NO_DATA_FOUND: Raised when a select statement returns zero rows.
4. NOT_LOGGED_ON:Raised when PL/SQL issues an oracle call without being logged onto Oracle.
5. PROGRAM_ERROR: Raised when PL/SQL has an internal problem.
6. TIMEOUT_ON_RESOURCE: Raised when Oracle has been waiting to access a resource beyond the user-defined timeout limit.
7. TOO_MANY_ROWS: Raised when a select statement returns more than one row.
8. VALUE_ERROR: Raised when the data type or data size is invalid.
9. OTHERS: stands for all other exceptions not explicitly named
2. LOGIN_DENIED: Raised when an invalid username/password was used to log onto Oracle.
3. NO_DATA_FOUND: Raised when a select statement returns zero rows.
4. NOT_LOGGED_ON:Raised when PL/SQL issues an oracle call without being logged onto Oracle.
5. PROGRAM_ERROR: Raised when PL/SQL has an internal problem.
6. TIMEOUT_ON_RESOURCE: Raised when Oracle has been waiting to access a resource beyond the user-defined timeout limit.
7. TOO_MANY_ROWS: Raised when a select statement returns more than one row.
8. VALUE_ERROR: Raised when the data type or data size is invalid.
9. OTHERS: stands for all other exceptions not explicitly named
Exception Handler
An Exception Handler is nothing but a code block in memory that will attempt to resolve the current exception condition. The Oracle engine can recognize every exception condition that occurs in memory. To handle very common and repetitive exception conditions the Oracle engines uses Named Exception Handlers.
The Oracle engine has about fifteen to twenty named exception handlers. In addition to this the Oracle engine uses more than twenty thousand numbered exception handlers. These exception handlers are identified not by names but by four integers preceded by a hypen that is -1414. These exception handler names are actually a set of negative signed integers.
Each exception handler, irrespective of how it is identified (i.e. by Name or Number) has code attached that will attempt to resolve an exception condition. This is how Oracle's Default Exception-Handling strategy works.
The exception handler scans the PL/SQL block for the existence of an Exception section within the PL/SQL block. If an exception section within the PL/SQL block exists the exception handler scans the first word,after the action word When,within this exception section.
If the first word after the action word When,is the exception handler's name then the exception handler executes the code contained in the Thensection of the construct as follows.
Exception
When {Exception Name} Then {User defined action to be carried out}
The first word that follows the action word When must be 'String'.Hence this technique will work well for the fifteen to twenty 'named exception handlers'. In addition to these the Oracle engine has twenty thousand,numbered exception handlers,which are raised automatically and appropriately when the Oracle engine recognizes and exception condition. User defined exception handling code must be permitted even for these (numbered) exception handlers.
The Oracle engine has about fifteen to twenty named exception handlers. In addition to this the Oracle engine uses more than twenty thousand numbered exception handlers. These exception handlers are identified not by names but by four integers preceded by a hypen that is -1414. These exception handler names are actually a set of negative signed integers.
Each exception handler, irrespective of how it is identified (i.e. by Name or Number) has code attached that will attempt to resolve an exception condition. This is how Oracle's Default Exception-Handling strategy works.
The exception handler scans the PL/SQL block for the existence of an Exception section within the PL/SQL block. If an exception section within the PL/SQL block exists the exception handler scans the first word,after the action word When,within this exception section.
If the first word after the action word When,is the exception handler's name then the exception handler executes the code contained in the Thensection of the construct as follows.
Exception
When {Exception Name} Then {User defined action to be carried out}
The first word that follows the action word When must be 'String'.Hence this technique will work well for the fifteen to twenty 'named exception handlers'. In addition to these the Oracle engine has twenty thousand,numbered exception handlers,which are raised automatically and appropriately when the Oracle engine recognizes and exception condition. User defined exception handling code must be permitted even for these (numbered) exception handlers.
Error Handling In PL/SQL
Every PL/SQL block of code encountered by the Oracle is accepted as a client. Hence the Oracle engine will make an attempt to execute every SQL sentence within the PL/SQL block. However while executing the SQL sentence anything can go wrong and the SQL sentence can fail.
When an SQL sentence fails the Oracle engine is the first to recognize this as an Exception condition.The Oracle engine immediately tries to handle the exception condition and resolve it. This is done by raising a built-in Exception Handler.
When an SQL sentence fails the Oracle engine is the first to recognize this as an Exception condition.The Oracle engine immediately tries to handle the exception condition and resolve it. This is done by raising a built-in Exception Handler.
Sunday, June 7, 2009
Logical Operators - Pattern Matching
The use of the LIKE predicate:
The comparison operators discussed so far have compared one value, exactly to one other value. Such a precision may not always be desired or necessary. For this purpose Oracle provides a predicate LIKE.
The LIKE depreciate allows for a comparison of one string value with another string value, which is not identical. This is achieved by using wildcard characters. Two wildcard characters that are available are:
For character data types:
1. The Percent sign (%) matches any string.
2. The Underscore (_) matches any single character.
Example:
1.
Retrieve all information about suppliers whose names begin with the letters 'ja' from supplier_master.
SELECT * FROM supplier_master WHERE supplier_name LIKE 'ja %':
2.
Retrieve all information about suppliers where the second character of names are either 'r' or 'h'.
SELECT * FROM supplier_master WHERE supplier_name LIKE '_R%' OR supplier_name LIKE '_h%';
The IN and NOT IN predicates:
The arithmetic operator (=) compares a single value to another single value. In case a value needs to be compared to a list of values the the IN predicate is used. One can check a single value against multiple values by using the IN predicate.
Example:
Retrieve the supplier_name,address1,address2,city and pincode from the table supplier_master where the supplier_name is either Murali or Mahesh or yatish or leela.
SELECT supplier_name,address1,address2,city,pincode FROM supplier_master WHERE supplier_name IN('murali','mahes','yatish','leela');
The NOT IN predicate is the opposite of the IN predicate. This will select all the rows where values do not match all of the values in the list.
The comparison operators discussed so far have compared one value, exactly to one other value. Such a precision may not always be desired or necessary. For this purpose Oracle provides a predicate LIKE.
The LIKE depreciate allows for a comparison of one string value with another string value, which is not identical. This is achieved by using wildcard characters. Two wildcard characters that are available are:
For character data types:
1. The Percent sign (%) matches any string.
2. The Underscore (_) matches any single character.
Example:
1.
Retrieve all information about suppliers whose names begin with the letters 'ja' from supplier_master.
SELECT * FROM supplier_master WHERE supplier_name LIKE 'ja %':
2.
Retrieve all information about suppliers where the second character of names are either 'r' or 'h'.
SELECT * FROM supplier_master WHERE supplier_name LIKE '_R%' OR supplier_name LIKE '_h%';
The IN and NOT IN predicates:
The arithmetic operator (=) compares a single value to another single value. In case a value needs to be compared to a list of values the the IN predicate is used. One can check a single value against multiple values by using the IN predicate.
Example:
Retrieve the supplier_name,address1,address2,city and pincode from the table supplier_master where the supplier_name is either Murali or Mahesh or yatish or leela.
SELECT supplier_name,address1,address2,city,pincode FROM supplier_master WHERE supplier_name IN('murali','mahes','yatish','leela');
The NOT IN predicate is the opposite of the IN predicate. This will select all the rows where values do not match all of the values in the list.
Saturday, June 6, 2009
Range Searching
In order to select data that is within a range of values, the BETWEEN operator is used. The BETWEEN operator allows the selection of rows that contain values within a specified lower and upper limit. The range coded after the word BETWEEN is inclusive.
The lower value must be coded first. The two values in between the range must be linked with the keyword AND. A BETWEEN operator can be used with both character and numeric data types. However, one cannot mix the data types that is the lower value of a range of values from a character column and the other from a numeric column.
Example:
1. Retrieve product_no,description,profit_percent,sell_price from the table product_master where the values contained within the field profit_percent is Between 10 and 20 both inclusive.
SELECT product_no,description,profit_percent,sell_price FROM Product_Master WHERE profit_percent BETWEEN 10 AND 20;
The above select will retrieve all the records from the product_master table where the profit_percent is in between 10 and 20 (both values inclusive).
2. Retrieve product_no,description,profit_percent, and sell_price from the produt_master table where the values contained in the field profit_percent are not between 10 and 15 both inclusive
SELECT product_no,description,profit_percent,sell_price FROM product_master WHERE profit_percent NOT BETWEEN 10 AND 15;
The above select will retrieve all the records from the product_master table expect where the profit_percent is in between 10 and 15 (both values inclusive).
The lower value must be coded first. The two values in between the range must be linked with the keyword AND. A BETWEEN operator can be used with both character and numeric data types. However, one cannot mix the data types that is the lower value of a range of values from a character column and the other from a numeric column.
Example:
1. Retrieve product_no,description,profit_percent,sell_price from the table product_master where the values contained within the field profit_percent is Between 10 and 20 both inclusive.
SELECT product_no,description,profit_percent,sell_price FROM Product_Master WHERE profit_percent BETWEEN 10 AND 20;
The above select will retrieve all the records from the product_master table where the profit_percent is in between 10 and 20 (both values inclusive).
2. Retrieve product_no,description,profit_percent, and sell_price from the produt_master table where the values contained in the field profit_percent are not between 10 and 15 both inclusive
SELECT product_no,description,profit_percent,sell_price FROM product_master WHERE profit_percent NOT BETWEEN 10 AND 15;
The above select will retrieve all the records from the product_master table expect where the profit_percent is in between 10 and 15 (both values inclusive).
Logical Operators - NOT Operator
The Oracle engine will process all rows in a table and display the result only when row of the conditions specified using the NOT operator are satisfied.
Example: Retrieve specified client information for the clients who are NOT in "Hyderabad" OR "Bangalore".
SELECT client_no,name,address1,address2,city,pincode FROM client_master WHERE NOT ( city='Hyderabad' or city='Bangalore');
The oracle engine will not display the rows from the client_master table where the value of the field city is either Hyderabad or Bangalore.
Example: Retrieve specified client information for the clients who are NOT in "Hyderabad" OR "Bangalore".
SELECT client_no,name,address1,address2,city,pincode FROM client_master WHERE NOT ( city='Hyderabad' or city='Bangalore');
The oracle engine will not display the rows from the client_master table where the value of the field city is either Hyderabad or Bangalore.
Logical Operators - OR Operator
The Oracle engine will process all rows in a table and display the result only when any of the conditions specified using the OR operator are satisfied.
Ex: Retrieve client information like client_no,address1,address2,city and pincode for all the clients where the field pincode has the value 400065 or 500089;
SELECT client_no,name,address1,address2,city,pincode From client_master WHERE (pincode=400065 OR picode=500089);
The contents of the fields client_no,name,address1,address2,city,pincode satisfying the condition of pincode being 400065 OR 500089 in the client_master table will be displayed on the screen.
Ex: Retrieve client information like client_no,address1,address2,city and pincode for all the clients where the field pincode has the value 400065 or 500089;
SELECT client_no,name,address1,address2,city,pincode From client_master WHERE (pincode=400065 OR picode=500089);
The contents of the fields client_no,name,address1,address2,city,pincode satisfying the condition of pincode being 400065 OR 500089 in the client_master table will be displayed on the screen.
Logical Operators - AND Operator
Logical Operators that can be used in SQL sentence are:
AND Operator: Oracle engine will process all rows in a table and display the result only when all of the conditions specified using and AND operator are satisfied.
Example: Retrieve the contents of the columns product_no,description,profit_percent,sell_price from the table product_master where the values contained in the field profit_percent is between 10 and 20 both inclusive.
SELECT product_no,description,profit_percent,sell_price From product_master Where profit_percent >=10 AND profit_percent <= 20;
AND Operator: Oracle engine will process all rows in a table and display the result only when all of the conditions specified using and AND operator are satisfied.
Example: Retrieve the contents of the columns product_no,description,profit_percent,sell_price from the table product_master where the values contained in the field profit_percent is between 10 and 20 both inclusive.
SELECT product_no,description,profit_percent,sell_price From product_master Where profit_percent >=10 AND profit_percent <= 20;
ASCII EDITOR Running the SQL file using SQL Prompt
Running the .sql file using Get at the SQL Prompt:
1. To run this .sql file, after saving the file and exiting from notepad, at the sql >Prompt, type the command get
Ex: SQL > get
2. To execute the .sql file at the SQL > prompt type, /
Ex: SQL > /
3. The contents of the .sql file can be edited and the file run as required.
Running the .sql file using start at the SQL Prompt:
The .sql file can be compiled and executed using start or run at the SQL > Prompt.
Ex: SQL > start;
Saving into an ASCII file from the SQL Prompt
The steps involved in saving an SQL statement into an ASCII file from the SQL prompt are:
1. At the SQL > Prompt, You can execute any valid SQL statement.
Ex: Select * from client_master;
2. If you want to save the SQL statement in a file, at the SQL > Prompt type-in save. This command automatically creates a file by the in the current working directory.
SQL > save;
1. To run this .sql file, after saving the file and exiting from notepad, at the sql >Prompt, type the command get
Ex: SQL > get
2. To execute the .sql file at the SQL > prompt type, /
Ex: SQL > /
3. The contents of the .sql file can be edited and the file run as required.
Running the .sql file using start at the SQL Prompt:
The .sql file can be compiled and executed using start
Ex: SQL > start
Saving into an ASCII file from the SQL Prompt
The steps involved in saving an SQL statement into an ASCII file from the SQL prompt are:
1. At the SQL > Prompt, You can execute any valid SQL statement.
Ex: Select * from client_master;
2. If you want to save the SQL statement in a file, at the SQL > Prompt type-in save
SQL > save
ASCII EDITOR
Working With an ASCII EDITOR: SQL is a single sentence language. The sentence once typed at the SQL > Prompt cannot be retrieved and corrected if a spelling or syntax error was discovered after it was dispatched to the Oracle engine for processing.
The technique used to overcome this constant retyping of sentences is to create an ASCII file. Place the SQL sentence as the contents of the ASCII file. When the sentence has to be executed, appropriate commands in SQL * Plus opens the ASCII file and passes its contents to the Oracle Server for execution.
Any ASCII editor can be used. Once convenient editor to use will be Windows Notepad Editor, which is a part of the MS Windows Operating System. Hence, Notepad can be invoked while the SQL prompt is on the screen as the editor of choice.
1. To invoke Notepad, Window's full page ASCII editor, from SQL * plus type Ed followed by the file name at the SQL prompt. If the file does not exist then the system comes up with a message asking the user to create a new file.
2. At this point an ASCII file of the filename specified is opened in memory. Start to type in SQL sentences in the ASCII file. Each SQL sentence is to be terminated with a / (backslash) in the first column of the next line.
3. Save the file using the Notepad Menu choices File,Save.
4. This file can be invoked and used repeatedly at the SQL > Prompt.
5. To exit from the tool click on File,Exit.
The technique used to overcome this constant retyping of sentences is to create an ASCII file. Place the SQL sentence as the contents of the ASCII file. When the sentence has to be executed, appropriate commands in SQL * Plus opens the ASCII file and passes its contents to the Oracle Server for execution.
Any ASCII editor can be used. Once convenient editor to use will be Windows Notepad Editor, which is a part of the MS Windows Operating System. Hence, Notepad can be invoked while the SQL prompt is on the screen as the editor of choice.
1. To invoke Notepad, Window's full page ASCII editor, from SQL * plus type Ed followed by the file name at the SQL prompt. If the file does not exist then the system comes up with a message asking the user to create a new file.
2. At this point an ASCII file of the filename specified is opened in memory. Start to type in SQL sentences in the ASCII file. Each SQL sentence is to be terminated with a / (backslash) in the first column of the next line.
3. Save the file using the Notepad Menu choices File,Save.
4. This file can be invoked and used repeatedly at the SQL > Prompt.
5. To exit from the tool click on File,Exit.
Saturday, May 23, 2009
Data Base System models or Types of DBMS
1. Hierarchical Model
2. Network Model
3. Relational Model
1. Hierarchical Model or HDMS:
One of the earliest database management system was based on the Hierarchical model. Here data can be organized in the form of free structure or level-by-level manner with one limitation that is "every sub node or child node should have only one parent node".
2.Network Model or NDBMS:
To overcome the problems proposed by the hierarchical data model, the network data model was developed. It is also known as communication oriented database management system. Cross communication is possible in NDBMS.
Here data can be organized in the form of tree structure or level by level manner with cross communication. It cannot provide proper query facility,so that we have to write big programs even for small operation.It is complex in structure.
3. Relational DBMS:
RDBMS are most important database system used in the software industry today. It was Exclusively used to establish the relation the relation ship between two-database objects. One of the database objects is one table.
The Relation ship may be
One - One
One - Many
Many - One
Many - Many
Entity-Relationship Model (E-R Model):
An Entity is nothing but an object, which is physically existed in the real world.
Example: car,computer, chair
An Object in the database is a table so that an entity is nothing but a table.
Every Entity contains characters specifies its attributes simply it is a column in the table.
2. Network Model
3. Relational Model
1. Hierarchical Model or HDMS:
One of the earliest database management system was based on the Hierarchical model. Here data can be organized in the form of free structure or level-by-level manner with one limitation that is "every sub node or child node should have only one parent node".
2.Network Model or NDBMS:
To overcome the problems proposed by the hierarchical data model, the network data model was developed. It is also known as communication oriented database management system. Cross communication is possible in NDBMS.
Here data can be organized in the form of tree structure or level by level manner with cross communication. It cannot provide proper query facility,so that we have to write big programs even for small operation.It is complex in structure.
3. Relational DBMS:
RDBMS are most important database system used in the software industry today. It was Exclusively used to establish the relation the relation ship between two-database objects. One of the database objects is one table.
The Relation ship may be
One - One
One - Many
Many - One
Many - Many
Entity-Relationship Model (E-R Model):
An Entity is nothing but an object, which is physically existed in the real world.
Example: car,computer, chair
An Object in the database is a table so that an entity is nothing but a table.
Every Entity contains characters specifies its attributes simply it is a column in the table.
Fundamentals of RDBMS
Data is the most important component in any work that we do. Data means collection of information or collection of raw facts.
Data base Management system is the management system or maintenance system on database.
or
It is a suit of software program for creating,maintaining & manipulating the data in database.
or
It allows the users to insert the data,to retrieve the data,to modify the data and to delete the data.
The first databases of kind existed in the form of files.
A file is nothing but collection of records.
A record is nothing but collection of information or data.
But the file system was not very efficient,it was crippled by slow data search speed. It includes the following Drawbacks.
1.Security
2.Data Redundancy
3.Data Integrity
4.Concurrency Control
5.Slow in Process
1.Security: No Login name No Password.
2.Data Redundancy: Data reputation or data duplication is very high.
3.Data Integrity: No Data Validation process.
4.Concurrency Control: No control in files system when concurrency exists.
5.Slow in Process: File System follows Sequential-searching process
Data base Management system is the management system or maintenance system on database.
or
It is a suit of software program for creating,maintaining & manipulating the data in database.
or
It allows the users to insert the data,to retrieve the data,to modify the data and to delete the data.
The first databases of kind existed in the form of files.
A file is nothing but collection of records.
A record is nothing but collection of information or data.
But the file system was not very efficient,it was crippled by slow data search speed. It includes the following Drawbacks.
1.Security
2.Data Redundancy
3.Data Integrity
4.Concurrency Control
5.Slow in Process
1.Security: No Login name No Password.
2.Data Redundancy: Data reputation or data duplication is very high.
3.Data Integrity: No Data Validation process.
4.Concurrency Control: No control in files system when concurrency exists.
5.Slow in Process: File System follows Sequential-searching process
Sunday, April 12, 2009
Third Normal Form
According to second normal form table should be in Second Normal Form and we should have to remove Transitive Functional Dependency.
In the above Emp-Info table non-key column DName dependent part on the other non-key column that is DeptNo. It means there existed Transitive functional dependency.
To make the table into third normal form we have to divide the table into multiple tables.
Proj-Info:
ProjNo:p1
Pname:pn1
Bud:--
Emp-Info:
EmpNo:11
Ename:------
sal:------
Dept-Info:
DeptNo:10
DName:----
Loc:------
In the above Emp-Info table non-key column DName dependent part on the other non-key column that is DeptNo. It means there existed Transitive functional dependency.
To make the table into third normal form we have to divide the table into multiple tables.
Proj-Info:
ProjNo:p1
Pname:pn1
Bud:--
Emp-Info:
EmpNo:11
Ename:------
sal:------
Dept-Info:
DeptNo:10
DName:----
Loc:------
Second Normal Form
According to second normal form table should be in First Normal form and we should have to remove Partial Functional Dependency.
In the above table DeptNo non-key column dependent part of the Primary Key column that is EmpNo.It means there existed Partial functional dependency.
To make the table into second normal form we have to divide the table into multiple tables.
Proj-Info:
ProjNo:p1
Pname:Pn1
Bud:----
Emp-Info:
EmpNo:11
EName:----
Sal:-------
DeptNo:-----
DeptName:-----
Loc:-----
In the above table DeptNo non-key column dependent part of the Primary Key column that is EmpNo.It means there existed Partial functional dependency.
To make the table into second normal form we have to divide the table into multiple tables.
Proj-Info:
ProjNo:p1
Pname:Pn1
Bud:----
Emp-Info:
EmpNo:11
EName:----
Sal:-------
DeptNo:-----
DeptName:-----
Loc:-----
First Normal Form
According to first normal form table should contain only single values columns.But in the above mentioned un-normalized table see this:http://sqlserver-guide.blogspot.com/2009/04/normalization.html
the columns ProjNo and PName contain multiple values.
To make the table into first normal form we should have to split the multiple values into single values
EmpNo:11
ProjNo:p1
EName:----
PName:Pn1
Sal:----
Bud:-----
DeptNo:10
DName:--------
Loc:------
the columns ProjNo and PName contain multiple values.
To make the table into first normal form we should have to split the multiple values into single values
EmpNo:11
ProjNo:p1
EName:----
PName:Pn1
Sal:----
Bud:-----
DeptNo:10
DName:--------
Loc:------
Friday, April 10, 2009
Normalization
Normalization is process of splitting the base table into multiple tables based on the theory of Functional Dependency.
Or
Normalization is repetitive process in order to identify the functional dependencies among the columns and to remove them. If any functional dependency is occurred after the normalization process again we have to start the same process until all functional dependencies have been removed.
To do this Normalization we have to follow rules or conditions called Normal Forms.
Un-Normalized Table:
EmpNo=11
ProjNo=p1,p2
EName=-----
PName=Pn1,Pn2
Sal =-----
Bud=------
DeptNo=10
Dname=------
Loc=------
EmpNo and ProjNo are Primary keys called 'COMPOSITE PRIMARY KEY'
Or
Normalization is repetitive process in order to identify the functional dependencies among the columns and to remove them. If any functional dependency is occurred after the normalization process again we have to start the same process until all functional dependencies have been removed.
To do this Normalization we have to follow rules or conditions called Normal Forms.
Un-Normalized Table:
EmpNo=11
ProjNo=p1,p2
EName=-----
PName=Pn1,Pn2
Sal =-----
Bud=------
DeptNo=10
Dname=------
Loc=------
EmpNo and ProjNo are Primary keys called 'COMPOSITE PRIMARY KEY'
Subscribe to:
Posts (Atom)
