DBMS: CHAPTER -3

An Introduction to Database Management Systems
A database is a collection of related files that are usually integrated, linked or cross-referenced to one another. The advantage of a database is that data and records contained in different files can be easily organized and retrieved using specialized database management software called a database management system (DBMS) or database manager.After reading this lesson, you should be able to:Define the term database management system (DBMS).Describe the basic purpose and functions of a DBMS.Discuss the advantages and disadvantages of DBMSs.
DBMS Fundamentals
A database management system is a set of software programs that allows users to create, edit and update data in database files, and store and retrieve data from those database files. Data in a database can be added, deleted, changed, sorted or searched all using a DBMS. If you were an employee in a large organization, the information about you would likely be stored in different files that are linked together. One file about you would pertain to your skills and abilities, another file to your income tax status, another to your home and office address and telephone number, and another to your annual performance ratings. By cross-referencing these files, someone could change a person's address in one file and it would automatically be reflected in all the other files. 
DBMSs are commonly used to manage:Membership and subscription mailing listsAccounting and bookkeeping informationThe data obtained from scientific researchCustomer informationInventory informationPersonal recordsLibrary informationDBMSs and File Management Systems
Computerized file management systems (sometimes called file managers) are not considered true database management systems because files cannot be easily linked to each other. However, they can serve as useful data management functions by providing a system for storing information in files. For example, a file management system might be used to store a mailing list or a personal address book. When files need to be linked, a relational database should be created using database application software such as Oracle, Microsoft Access, IBM DB2, or FileMaker Pro.
The Advantages of a DBMS


Improved availability: One of the principle advantages of a DBMS is that the same information can be made available to different users.  

Minimized redundancy: The data in a DBMS is more concise because, as a general rule, the information in it appears just once. This reduces data redundancy, or in other words, the need to repeat the same data over and over again. Minimizing redundancy can therefore significantly reduce the cost of storing information on hard drives and other storage devices. In contrast, data fields are commonly repeated in multiple files when a file management system is used.

Accuracy: Accurate, consistent, and up-to-date data is a sign of data integrity. DBMSs foster data integrity because updates and changes to the data only have to be made in one place. The chances of making a mistake are higher if you are required to change the same data in several different places than if you only have to make the change in one place.

Program and file consistency: Using a database management system, file formats and system programs are standardized. This makes the data files easier to maintain because the same rules and guidelines apply across all types of data. The level of consistency across files and programs also makes it easier to manage data when multiple programmers are involved.

User-friendly: Data is easier to access and manipulate with a DBMS than without it. In most cases, DBMSs also reduce the reliance of individual users on computer specialists to meet their data needs.Improved security: As stated earlier, DBMSs allow multiple users to access the same data resources. This capability is generally viewed as a benefit, but there are potential risks for the organization. Some sources of information should be protected or secured and only viewed by select individuals. Through the use of passwords, database management systems can be used to restrict data access to only those who should see it.

The Disadvantages of a DBMS

There are basically two major downsides to using DBMSs. One of these is cost, and the other the threat to data security.Cost: Implementing a DBMS system can be expensive and time-consuming, especially in large organizations. Training requirements alone can be quite costly.Security: Even with safeguards in place, it may be possible for some unauthorized users to access the database. In general, database access is an all or nothing proposition. Once an unauthorized user gets into the database, they have access to all the files, not just a few. Depending on the nature of the data involved, these breaches in security can also pose a threat to individual privacy. Steps should also be taken to regularly make backup copies of the database files and store them because of the possibility of fires and earthquakes that might destroy the system.

SQL: Chapter 2

SQL Commands

Database

A database consists of one or more tables. A table is identified by its name. A table is made up of columns and rows. Columns contain the column name and data type. Rows contain the records or data for the columns.

Basic SQL

Each record has a unique identifier or primary key. SQL, which stands for Structured Query Language, is used to communicate with a database. Through SQL one can create and delete tables. Here are some commands:

• CREATE TABLE - creates a new database table
• ALTER TABLE - alters a database table
• DROP TABLE - deletes a database table
• CREATE INDEX - creates an index (search key)
• DROP INDEX - deletes an index

SQL also has syntax to update, insert, and delete records.

• SELECT - get data from a database table
• UPDATE - change data in a database table
• DELETE - remove data from a database table
• INSERT INTO - insert new data in a database table

SELECT

The SELECT is used to query the database and retrieve selected data that match the specific criteria that you specify:

SELECT column1 [, column2, ...]
FROM tablename
WHERE condition

The conditional clause can include these operators

• = Equal
• > Greater than
• < Less than
• >= Greater than or equal
• <= Less than or equal
• <> Not equal to
• LIKE pattern matching operator

SELECT * FROM tablename

returns all the data from the table.

Use single quotes around text values (most database systems will also accept double quotes). Numerical values should not be enclosed in quotes.
LIKE matches a pattern. The wildcard % is used to denote 0 or more characters.

• 'A%' : matches all strings that start with A
• '%a' : matches all strings that end with a
• '%a%' : matches all strings that contain an a

CREATE TABLE

The CREATE TABLE statement is used to create a new table. The format is:

CREATE TABLE tablename
(column1 data type,
column2 data type,
column3 data type);

• char(size): Fixed length character string.
• varchar(size): Variable-length character string. Max size is specified in parenthesis.
• number(size): Number value with a max number of columns specified in parenthesis
• date: Date value
• number(size,d): A number with a maximum number of digits of "size" and a maximum number of "d" digits to the right of the decimal

INSERT VALUES

Once a table has been created data can be inserted using INSERT INTO command.

INSERT INTO tablename
(col1, ... , coln)
VALUES (val1, ... , valn)

UPDATE

To change the data values in a pre existing table, the UPDATE command can be used.

UPDATE tablename
SET colX = valX [, colY = valY, ...]
WHERE condition

DELETE

The DELETE command can be used to remove a record(s) from a table.

DELETE FROM tablename
WHERE condition

To delete all the records from a table without deleting the table do

DELETE * FROM tablename

DROP

To remove an entire table from the database use the DROP command.

DROP TABLE tablename

ORDER BY

ORDER BY clause can order column name in either ascending (ASC) or descending (DESC) order.

ORDER BY col_name ASC

AND / OR

AND and OR can join two or more conditions in a WHERE clause. AND will return data when all the conditions are true. OR will return data when any one of the conditions is true.

IN

IN operator is used when you know the exact value you want to return for at least one of the columns

SELECT * FROM table_name WHERE col_name IN (val1, val2, ...)

BETWEEN / AND

The BETWEEN ... AND operator selects a range of data between two values. These values can be numbers, text, or dates.

SELECT * FROM table_name WHERE col_name BETWEEN val1 AND val2

JOIN

There are times when we need to collate data from two or more tables. That is called a join. Tables in a database are related to each other through their keys. We can associate data in various tables without repeating them. For example we could have a table called Customers which could have information about customers like their name, address, phone numbers. We could have another table called Products that has information regarding the products like part number, product name, manufacturer, number in stock, unit price. A third table called Orders could have information regarding what product was ordered, by whom, the date the order was placed, and quantity. Here are the tables:

Customers

Cust_ID	FirstName	LastName	Address	Phone
01	Mickey	Mouse	123 Gouda St.	456-7890
02	Donald	Duck	325 Eider Ln.	786-2365

Products

Part_No	Name	Manufacturer	In_Stock	Price
20-45	Hammer	Stanley	57	3.50
21-68	ScrewDriver	DeVries	84	2.75

Orders

Order_No	Part_No	Cust_ID	Date	Quantity
2005-27	21-68	02	31 Oct 2005	2
2005-34	20-45	01	02 Nov 2005	3

We can obtain information on who has ordered what:

SELECT Customers.FirstName, Customers.LastName, Products.Name
FROM Customers, Products, Orders
WHERE Customers.Cust_ID = Orders.Cust_ID AND Products.Part_No = Orders.Part_No
We can select data from two tables with INNER JOIN. The INNER JOIN returns all rows from both tables where there is a match. If there are rows in Customers that do not have matches in Orders, those rows will not be listed.
SELECT Customers.FirstName, Customers.LastName, Orders.Date
FROM Customers
INNER JOIN Orders
ON Customers.Cust_ID = Orders.Cust_ID
The LEFT JOIN returns all the rows from the first table (Customers), even if there are no matches in the second table (Orders). If there are rows in Customers that do not have matches in Orders, those rows also will be listed.
SELECT Customers.FirstName, Customers.LastName, Orders.Date
FROM Customers
LEFT JOIN Orders
ON Customers.Cust_ID = Orders.Cust_ID
The RIGHT JOIN returns all the rows from the second table (Orders), even if there are no matches in the first table (Customers). If there had been any rows in Orders that did not have matches Customers, those rows also would have been listed.
SELECT Customers.FirstName, Customers.LastName, Orders.Date
FROM Customers
RIGHT JOIN Orders
ON Customers.Cust_ID = Orders.Cust_ID

ALTER TABLE

With ALTER TABLE you can add or delete columns in an existing table. When you add a column you must specify a data type.
ALTER TABLE table_name
ADD col_name datatype

ALTER TABLE table_name
DROP COLUMN col_name

UNION

The UNION command is used to select data from two tables very similar to the JOIN command. But the UNION command can be used only with columns having the same datatype. With UNION only distinct values are selected, i.e. if there are common data in the two tables only one instance of that data is returned.

SELECT Name FROM Customers_USA
UNION
SELECT Name FROM Customers_Asia

This will select all the customers from USA and Asia but if there is a name that occurs in both the tables it will return only one such name. To get all the names use UNION ALL instead.

SQL Functions

There are several built-in functins in SQL. The basic function types are:

• Aggregate Functions: These are functions that operate against a collection of values, but return a single value.
• Scalar Functions: These functions operate against a single value, and return a single value.

To use a built-in function the syntax is:

SELECT function (col_name) FROM table_name

GROUP BY

The GROUP BY was added to SQL so that aggregate functions could return a result grouped by column values.

SELECT col_name, function (col_name) FROM table_name GROUP BY col_name
HAVING keyword was introduced because the WHERE keyword could not be used. HAVING states a condition.

SELECT clo_name, function (col_name) FROM table_name
GROUP BY col_name
HAVING function (col_name) condition value

CREATE VIEW

A view is a virtual table that is a result of SQL SELECT statement. A view contains fields from one or more real tables in the database. This virtual table can then be queried as if it were a real table.

CREATE VIEW view_name AS
SELECT col_name(s)
FROM table_name
WHERE condition

A view could be used from inside a query, a stored procedure, or from inside another view. You can add functions and joins to a view and present the data you want to the user.

UNIX: chapter 1

Still Now I have learned some basic concepts of  UNIX,SQL

Basic UNIX commands

Note: not all of these are actually part of UNIX itself, and you may not find them on all UNIX machines. But they can all be used on turing in essentially the same way, by typing the command and hitting return. Note that some of these commands are different on non-Solaris machines - see SunOS differences.
If you've made a typo, the easiest thing to do is hit CTRL-u to cancel the whole line. But you can also edit the command line (see the guide to More UNIX).
UNIX is case-sensitive.

Files

• ls --- lists your files
  ls -l --- lists your files in 'long format', which contains lots of useful information, e.g. the exact size of the file, who owns the file and who has the right to look at it, and when it was last modified.
  ls -a --- lists all files, including the ones whose filenames begin in a dot, which you do not always want to see.
  There are many more options, for example to list files by size, by date, recursively etc.
• more filename --- shows the first part of a file, just as much as will fit on one screen. Just hit the space bar to see more or q to quit. You can use /pattern to search for a pattern.
• emacs filename --- is an editor that lets you create and edit a file. See the emacs page.
• mv filename1 filename2 --- moves a file (i.e. gives it a different name, or moves it into a different directory (see below)
• cp filename1 filename2 --- copies a file
• rm filename --- removes a file. It is wise to use the option rm -i, which will ask you for confirmation before actually deleting anything. You can make this your default by making an alias in your .cshrc file.
• diff filename1 filename2 --- compares files, and shows where they differ
• wc filename --- tells you how many lines, words, and characters there are in a file
• chmod options filename --- lets you change the read, write, and execute permissions on your files. The default is that only you can look at them and change them, but you may sometimes want to change these permissions. For example, chmod o+r filename will make the file readable for everyone, and chmod o-r filename will make it unreadable for others again. Note that for someone to be able to actually look at the file the directories it is in need to be at least executable. See help protection for more details.
• File Compression
  • gzip filename --- compresses files, so that they take up much less space. Usually text files compress to about half their original size, but it depends very much on the size of the file and the nature of the contents. There are other tools for this purpose, too (e.g. compress), but gzip usually gives the highest compression rate. Gzip produces files with the ending '.gz' appended to the original filename.
  • gunzip filename --- uncompresses files compressed by gzip.
  • gzcat filename --- lets you look at a gzipped file without actually having to gunzip it (same as gunzip -c). You can even print it directly, using gzcat filename | lpr
• printing
  • lpr filename --- print. Use the -P option to specify the printer name if you want to use a printer other than your default printer. For example, if you want to print double-sided, use 'lpr -Pvalkyr-d', or if you're at CSLI, you may want to use 'lpr -Pcord115-d'. See 'help printers' for more information about printers and their locations.
  • lpq --- check out the printer queue, e.g. to get the number needed for removal, or to see how many other files will be printed before yours will come out
  • lprm jobnumber --- remove something from the printer queue. You can find the job number by using lpq. Theoretically you also have to specify a printer name, but this isn't necessary as long as you use your default printer in the department.
  • genscript --- converts plain text files into postscript for printing, and gives you some options for formatting. Consider making an alias like alias ecop 'genscript -2 -r \!* | lpr -h -Pvalkyr' to print two pages on one piece of paper.
  • dvips filename --- print .dvi files (i.e. files produced by LaTeX). You can use dviselect to print only selected pages. See the LaTeX page for more information about how to save paper when printing drafts.

Directories

Directories, like folders on a Macintosh, are used to group files together in a hierarchical structure.

• mkdir dirname --- make a new directory
• cd dirname --- change directory. You basically 'go' to another directory, and you will see the files in that directory when you do 'ls'. You always start out in your 'home directory', and you can get back there by typing 'cd' without arguments. 'cd ..' will get you one level up from your current position. You don't have to walk along step by step - you can make big leaps or avoid walking around by specifying pathnames.
• pwd --- tells you where you currently are.

Finding things

• ff --- find files anywhere on the system. This can be extremely useful if you've forgotten in which directory you put a file, but do remember the name. In fact, if you use ff -p you don't even need the full name, just the beginning. This can also be useful for finding other things on the system, e.g. documentation.
• grep string filename(s) --- looks for the string in the files. This can be useful a lot of purposes, e.g. finding the right file among many, figuring out which is the right version of something, and even doing serious corpus work. grep comes in several varieties (grep, egrep, and fgrep) and has a lot of very flexible options. Check out the man pages if this sounds good to you.

About other people

• w --- tells you who's logged in, and what they're doing. Especially useful: the 'idle' part. This allows you to see whether they're actually sitting there typing away at their keyboards right at the moment.
• who --- tells you who's logged on, and where they're coming from. Useful if you're looking for someone who's actually physically in the same building as you, or in some other particular location.
• finger username --- gives you lots of information about that user, e.g. when they last read their mail and whether they're logged in. Often people put other practical information, such as phone numbers and addresses, in a file called .plan. This information is also displayed by 'finger'.
• last -1 username --- tells you when the user last logged on and off and from where. Without any options, last will give you a list of everyone's logins.
• talk username --- lets you have a (typed) conversation with another user
• write username --- lets you exchange one-line messages with another user
• elm --- lets you send e-mail messages to people around the world (and, of course, read them). It's not the only mailer you can use, but the one we recommend. See the elm page, and find out about the departmentalmailing lists (which you can also find in /user/linguistics/helpfile).

About your (electronic) self

• whoami --- returns your username. Sounds useless, but isn't. You may need to find out who it is who forgot to log out somewhere, and make sure *you* have logged out.
• finger & .plan files
  of course you can finger yourself, too. That can be useful e.g. as a quick check whether you got new mail. Try to create a useful .plan file soon. Look at other people's .plan files for ideas. The file needs to be readable for everyone in order to be visible through 'finger'. Do 'chmod a+r .plan' if necessary. You should realize that this information is accessible from anywhere in the world, not just to other people on turing.
• passwd --- lets you change your password, which you should do regularly (at least once a year). See the LRB guide and/or look at help password.
• ps -u yourusername --- lists your processes. Contains lots of information about them, including the process ID, which you need if you have to kill a process. Normally, when you have been kicked out of a dialin session or have otherwise managed to get yourself disconnected abruptly, this list will contain the processes you need to kill. Those may include the shell (tcsh or whatever you're using), and anything you were running, for example emacs or elm. Be careful not to kill your current shell - the one with the number closer to the one of the ps command you're currently running. But if it happens, don't panic. Just try again :) If you're using an X-display you may have to kill some X processes before you can start them again. These will show only when you use ps -efl, because they're root processes.
• kill PID --- kills (ends) the processes with the ID you gave. This works only for your own processes, of course. Get the ID by using ps. If the process doesn't 'die' properly, use the option -9. But attempt without that option first, because it doesn't give the process a chance to finish possibly important business before dying. You may need to kill processes for example if your modem connection was interrupted and you didn't get logged out properly, which sometimes happens.
• quota -v --- show what your disk quota is (i.e. how much space you have to store files), how much you're actually using, and in case you've exceeded your quota (which you'll be given an automatic warning about by the system) how much time you have left to sort them out (by deleting or gzipping some, or moving them to your own computer).
• du filename --- shows the disk usage of the files and directories in filename (without argument the current directory is used). du -s gives only a total.
• last yourusername --- lists your last logins. Can be a useful memory aid for when you were where, how long you've been working for, and keeping track of your phonebill if you're making a non-local phonecall for dialling in.

Connecting to the outside world

• nn --- allows you to read news. It will first let you read the news local to turing, and then the remote news. If you want to read only the local or remote news, you can use nnl or nnr, respectively. To learn more about nn type nn, then \tty{:man}, then \tty{=.*}, then \tty{Z}, then hit the space bar to step through the manual. Or look at the man page. Or check out the hypertext nn FAQ - probably the easiest and most fun way to go.
• rlogin hostname --- lets you connect to a remote host
• telnet hostname --- also lets you connect to a remote host. Use rlogin whenever possible.
• ftp hostname --- lets you download files from a remote host which is set up as an ftp-server. This is a common method for exchanging academic papers and drafts. If you need to make a paper of yours available in this way, you can (temporarily) put a copy in /user/ftp/pub/TMP. For more permanent solutions, ask Emma. The most important commands within ftp are get for getting files from the remote machine, and put for putting them there (mget and mput let you specify more than one file at once). Sounds straightforward, but be sure not to confuse the two, especially when your physical location doesn't correspond to the direction of the ftp connection you're making. ftp just overwrites files with the same filename. If you're transferring anything other than ASCII text, use binary mode.
• lynx --- lets you browse the web from an ordinary terminal. Of course you can see only the text, not the pictures. You can type any URL as an argument to the G command. When you're doing this from any Stanford host you can leave out the .stanford.edu part of the URL when connecting to Stanford URLs. Type H at any time to learn more about lynx, and Q to exit.

Miscellaneous tools

• webster word --- looks up the word in an electronic version of Webster's dictionary and returns the definition(s)
• date --- shows the current date and time.
• cal --- shows a calendar of the current month. Use e.g., 'cal 10 1995' to get that for October 95, or 'cal 1995' to get the whole year.

You can find out more about these commands by looking up their manpages:
man commandname --- shows you the manual page for the command

HELLO Friends

Hai Friends

       After many days i am writing in our blog, I am happy to meet you again, i hope you all will be doing good, In gods grace i am also good enough.....

I like to share one thing with you..
Whatever we expect will not come to us all the time.. even though you works hard also in some situation you may get disappointment for not getting what you have expected.. In that situation don't get fed up.....
after finishing my engineering course i liked to do Movie Direction Kind of work... I got a chance too as a assistant director but my family situation doesn't allow me to do that...
 So i decided to get a job with my graduation and i got a job.. But it is not satisfied my expectation so i have joined Software testing course to get a good position as a software tester in reputed company..

So i planed to discuss about the testing concepts if you are interested you can also join with me, you can also improve with me, i am planned to discuss from the basics...


REGARDS
Thamizan Vinayagamoorthy
9500798808
Personal mail id: thamizan.vinayagam@gmail.com 

hai friends today is good day for all of you.