Data retrieval Experiment

Q-1> Write a query in sql to create a table employee and department.

Employee(empno,ename,deptno,job,hiredate)

Dept(deptno,dname,loc)

Include the following constraints on column of emp table.

a) to make the empno as primary key of the table and

b) to ensure that the ename column does not contain NULL values and

c) the job column to have only UPPERCASE entries and

d) to put the current date as default date in hire date column in case data is not supplied for the column.

Include the following constraints on column of dept table.

a) to make deptno as primary key.

b) to ensure dname,loc coloumns does not contain NULL values

Also enforce REFERENTIAL INTEGRITY, declare deptno field of dept table as primary key and deptno field of emp table as foreign key.

Q-2> Modify the above table to add three more columns salary,mgr,comm to the emp table. add salary column with constraint greater than zero and modify the width of sal column.

Q-3> Insert following values in employee table.

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

Q-4> Edit the commission of JONES to 1000.

Q-5> Retrieve all rows of employee table.

Q-6> Delete row from employee table where deptno is 10.

Q-7> Delete all rows from employee table then Drop the emp table.

DBMS Basics & DBMS Vs File System

DBMS Basics

GUIDBM subject is nearly in all the computer related graduation field so i thought to share my knowledge in the subject as from last five years i am teaching the same subject.

First i will start with the DBMS latter i will cover GUI.

Lets Start with the basics:

Database Basics:

Databases and database systems have become an essential component of everyday life.Daily in our life, most of us encounter several activities that involve some interaction with a database. for example we are checking our account balance on the internet, booking online ticket, online railway,airplane reservation , taking admission at university though internet, checking the result on the net etc.
As the list illustrates you can yourself figure out, databases form an essential part of almost all enterprises today.

Definition:
"A database-management system (DBMS) is a collection of interrelated data and a
set of programs to access those data" (Korth volume 1)

"A database is a collection of related data (Note 1). By data, we mean known facts that can be recorded and that have implicit meaning" (Navathe 3rd Edition)

Note:both the definition i have taken from book mentioned in the bracket.

in the first definition the collection of data can be anything like information related to an enterprise or information related to an account etc. and data means consider the names, telephone numbers, and addresses of the people you know. You may have stored it on a personal computer and software such as ACCESS or EXCEL. This is a collection of related data with an implicit meaning and hence is a database.


After the database basics we must know the problem arise in traditional file system. One can say that database is invented to solve the problems of the file system.

Database Systems versus File Systems:

• Data redundancy and inconsistency
This is the major problem of file system.the same information may be duplicated in
several places (files).
• Difficulty in accessing data
File processing system not allowed the convenient way of data retrieval.
• Data isolation
In file processing system writing new application is very difficult because here data is scattered in different file.
• Integrity problems
Sometime constraint are require on the data item, example balance of account should be greater than 5000. Giving such type of constraint in the file system is very difficult.
• Atomicity problems
In file processing system it is very difficult to maintain the atomicity.
Atomicity means if transaction fails because of some reason all the file must be consistence.
• Concurrent-access anomalies
There is consistency problem in the file system if many user accessing the same file simultaneously.
• Security problems
In the file system every user have the access to other user file. So one user can easily delete or see the details of other user.

Widget

RADIO AND CHECK BUTTONS: RADIO and CHECK BUTTONS are binary selection widgets. These are widgets which represent an on/off state. The difference between the two classifications is in a set of linked Radio Buttons only one of the associated buttons is allowed to be in the on state, where as with check buttons any or all of the buttons may be in the on state.
SLIDERS: SLIDERS are scroll buttons which are generally associated with List and Text Boxes.
ENTRY BOX: ENTRY BOXES define areas where text can be manually inserted.
BUTTONS: BUTTONS covers all classes of buttons outside the Check and Radio button classes. This includes, Menu, Push, and Toggle Buttons.
MENU OPTIONS: MENU OPTIONS are the list of options popped up whenever a menu button is activated.
LIST BOX: LIST BOXES are lists of text from which items can be selected (highlighted) and deselected (un-highlighted).
LABELS: LABELS are simple text widgets.
TEXT BOX: TEXT BOXES are areas in which text or information is displayed.
FRAMES: FRAMES define areas of a menu into which various widgets have been placed.

Murphy's Law

Murphy's law is a popular adage(an edge is a short) in Western culture that most likely originated at Edwards Air Force Base in 1948.
"The Law broadly states that things will go wrong in any given situation, if you give them a chance"
"If there's more than one way to do a job, and one of those ways will result in disaster, then somebody will do it that way."
It is most often cited as "Whatever can go wrong, will go wrong"
or alternately
"Whatever can go wrong will go wrong, and at the worst possible time,"
or
"Anything that can go wrong, will,"
or even
"If anything can go wrong, it will, and usually at the most inopportune moment"

The correct, original Murphy's Law reads:
"If there are two or more ways to do something, and one of those ways can result in a catastrophe, then someone will do it"
This is a principle of defensive design.
For example, you don't make a two-pin plug symmetrical and then label it THIS WAY UP; if it matters which way it is plugged in, then you make the design asymmetrical.

Features of GUI

Graphical user interfaces, such as Microsoft Windows and the one used by the Apple Macintosh, feature the following basic components:
Pointer : A symbol that appears on the display screen and that you move to select objects and commands. Usually, the pointer appears as a small angled arrow. Text - processing applications, however, use an I-beam pointer that is shaped like a capital I.
Pointing device : A device, such as a mouse or trackball, that enables you to select objects on the display screen.
Icons : Small pictures that represent commands, files, or windows. By moving the pointer to the icon and pressing a mouse button, you can execute a command or convert the icon into a window. You can also move the icons around the display screen as if they were real objects on your desk.
Desktop : The area on the display screen where icons are grouped is often referred to as the desktop because the icons are intended to represent real objects on a real desktop.
Windows: You can divide the screen into different areas. In each window, you can run a different program or display a different file. You can move windows around the display screen, and change their shape and size at will.
Menus : Most graphical user interfaces let you execute commands by selecting a choice from a menu.

Graphical User Interface

Abbreviated GUI (pronounced GOO-ee). A program interface that takes advantage of the computer’s graphic capabilities to make the program easier to use. Well-designed graphical user interfaces can free the user from learning complex command languages. On the other hand, many users find that they work more effectively with a command-driven interface, especially if they already know the command language.
OR
A GUI (usually pronounced GOO-ee) is a graphical (rather than purely textual) user interface to a computer. As you read this, you are looking at the GUI or graphical user interface of your particular Web browser. The term came into existence because the first interactive user interfaces to computers were not graphical; they were text-and-keyboard oriented and usually consisted of commands you had to remember and computer responses that were infamously brief. The command interface of the DOS operating system (which you can still get to from your Windows operating system) is an example of the typical user-computer interface before GUIs arrived. An intermediate step in user interfaces between the command line interface and the GUI was the non-graphical menu-based interface, which let you interact by using a mouse rather than by having to type in keyboard commands. Graphical user interfaces, such as Microsoft windows and the one used by the Apple Machine tosh, feature the following basic components: Pointer: A symbol that appears on the display screen and that you move to select objects and commands. Usually, the pointer appears as a small angled arrow. Text-processing applications, however, use an I-beam-pointer that is shaped like a capital I. pointing device: A device, such as a mouse or trackball, that enables you to select objects on the display screen. icons: Small pictures that represent commands, files, or windows. By moving the pointer to the icon and pressing a mouse button , you can execute a command or convert the icon into a window. You can also move the icons around the display screen as if they were real objects on your desk. desktop: The area on the display screen where icons are grouped is often referred to as the desktop because the icons are intended to represent real objects on a real desktop. windows: You can divide the screen into different areas. In each window, you can run a different program or display a different file. You can move windows around the display screen, and change their shape and size at will. menus: Most graphical user interfaces let you execute commands by selecting a choice from a menu.
Today's major operating systems provide a graphical user interface. Applications typically use the elements of the GUI that come with the operating system and add their own graphical user interface elements and ideas. A GUI sometimes uses one or more metaphors for objects familiar in real life, such as the desktop, the view through a window, or the physical layout in a building. Elements of a GUI include such things as: windows, pull-down menus, buttons, scroll bars, iconic images, wizards, the mouse, and no doubt many things that haven't been invented yet. With the increasing use of multimedia as part of the GUI, sound, voice, motion video, and virtual reality interfaces seem likely to become part of the GUI for many applications. A system's graphical user interface along with its input devices is sometimes referred to as its "look-and-feel." The first graphical user interface was designed by Xerox corporation Palo Alto Research Center in the 1970s, but it was not until the 1980s and the emergence of the Apple Macintosh that graphical user interfaces became popular. One reason for their slow acceptance was the fact that they require considerable CPU power and a high-quality monitor, which until recently were prohibitively expensive. In addition to their visual components, graphical user interfaces also make it easier to move data from one application to another. A true GUI includes standard formats for representing text and graphics. Because the formats are well-defined, different programs that run under a common GUI can share data. This makes it possible, for example, to copy a graph created by spreadsheet program into a document created by a word processor.
When creating an application, many object-oriented tools exist that facilitate writing a graphical user interface. Each GUI element is defined as a class widgets from which you can create object instances for your application. You can code or modify prepackaged methods that an object will use to respond to user stimuli. Many DOS programs include some features of GUIs, such as menus, but are not graphics based. Such interfaces are sometimes called graphical character-based user interfaces to distinguish them from true GUIs.