Download Intro to Database - Lab Manual 1 and more Lecture notes Introduction to Database Management Systems in PDF only on Docsity!
USMAN INSTITUTE OF TECHNOLOGY
Department of Computer Science
CS311 Introduction to Database Systems
Lab#
Objective:
- Overview of the features of SQL and SQL*Plus.
Name of Student: _____________________________________________
Roll No: ______________________________Sec. ___________
Date of Experiment: ___________________________________________
Marks Obtained/Remarks: _____________________________
Signature: _____________________________
THEORY
What is a database?
Every organization needs information for making effective decisions. A library keeps a list of books, members, due dates and fines. A company needs to save information about employees, departments and salaries. The collection of facts about different entities in an organization is called data.
Compiled by: Miss shabina mushtaque
A database is an organized collection of interrelated data. In order to manage databases, we need Database Management Systems (DBMS). A DBMS is a program that stores, retrieves and modifies data in the database on user’s request as illustrated in figure 1.1 below.
Figure 1.1: Users access data in a database through DBMS
Relational Database Concept
A relational database uses relations or two-dimensional tables to store data. Such a database can be accessed and modified by executing Structured Query Language (SQL) statements. Dr. E. F. Codd proposed the relational model for database systems in 1970. Relational Database Management Systems (RDBMS) soon became very popular, especially for their ease of use and flexibility in structure. In addition, a number of innovative vendors such as Oracle, supplemented the RDBMS with a suite of powerful application development and user products, providing a total solution. A table is the basic storage structure of an RDBMS. A table holds all the data necessary about something in the real world – for example, employees, invoices, or customers. Oracle 7 is a relational database management system whereas Oracle 8 and Oracle 9i are object relational database management systems. Oracle 9i includes everything needed to develop, deploy and manage internet applications. There are two products Oracle 9i Application Server and Oracle 9i Database that provide a complete and simple infrastructure for internet applications. The Oracle 9i application server (Oracle 9iAS ) runs all your applications. The Oracle 9i database stores all your data.
Figure 1.2: Databases allow compact and efficient storage of data
The table 1.1 below shows the contents of the EMP table or relation that stores data about employees presently working in an organization. ■ The table has eight columns namely EMPNO, ENAME. JOB, MGR, HIREDATE, SAL, COMM, DEPTNO storing the different attributes of an employee. ■ The table has fourteen rows each representing all data that is required for a particular employee. Each row in a table should be identified by a primary key , which allows no duplicate rows. In table 1.1, EMPNO is the primary key where as in table 1.2, DEPTNO is the primary key. ■ A foreign key is a column or a set of columns that refers to a primary key or a unique key in the same table or another table. In EMP table, DEPTNO is the foreign key. ■ (^) A field can be found at the intersection of a row and a column. There can be only one value in it. ■ A field may have no value in it. This is called null value. In the EMP table, only employees who have a role of salesman have a value in the COMM (Commission) field.
Compiled by: Miss shabina mushtaque
DatabaseDatabase Users
Database Management
System
2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999 Table 1.
JOB_HISTORY
EMPNO JOB START_DATE END_DATE 7698 ASSISTANT 04-MAR-80 30-APR- 7654 RECEPTIONIST 13-JAN-80 09-SEP- 7654 SALESMAN 10-SEP-80 20-SEP- 7788 PROGRAMMER 13-FEB-80 03-DEC- 7876 TYPIST 12-APR-80 13-NOV- 7876 OPERATOR 15-NOV-81 11-JAN- 7839 ANALYST 13-JUN-78 10-OCT- Table 1.
Relating Multiple Tables
Each table contains data that describes exactly one entity. For example, the EMP table contains information about employees. Since data about different entities is stored in different tables, it may be needed to combine two or more tables to answer a particular question. For example, someone may want to know the location of the department where an employee works. In this scenario, information is needed from both the EMP and DEPT table. An RDBMS enables to relate the data in one table to the data in another table by using the foreign keys.
Guidelines for Primary and Foreign Keys
■ No duplicate values are allowed in a primary key. ■ Primary keys generally cannot be changed. ■ Foreign keys are based on data values and are purely logical, not physical pointers. ■ A foreign key value must match an existing primary key value or unique key value, or else be null.
SQL (Structured Query Language)
Most commercial database management systems support a query language, SQL, which is the most influential commercially marketed product. SQL is a nonprocedural language: you specify what information you require, rather than how to get it. In other words, SQL does not require you to specify the access methods to the data. As a result, it doesn’t provide traditional programming structures. Instead, it is a language for specifying the operations at an unusually high level. The details of the implementation are left to the DBMS.
In Oracle, SQL is a language for communication with the Oracle Server from any tool or application. Oracle SQL has many extensions. Although we refer to the SQL language as a query language , it contains many other capabilities besides querying a database. It includes features for defining the structure of the data, for inserting and modifying data in the database, and for specifying security constraints.
Compiled by: Miss shabina mushtaque
It has following advantages:- ■ Efficient ■ Easy to learn and use ■ Functionally complete (SQL allows to define, retrieve, and manipulate data in the tables.)
Background of SQL
The SQL language, originally called SEQUEL, was developed by IBM as part of the extensive work on the relational model in 1970s at their San Jose Research Laboratory, California. In order to test the viability of implementing the relational model in a DBMS, workers at the San Jose Research Laboratory undertook the project of the development of a prototype RDBMS named System R. This project took place from 1974 to 1979. The System R project led to the development of SEQUEL (Structured English Query Language) which was later renamed as SQL. Because System R was well received at the user sites where it was installed, other vendors began developing relational products that used SQL. In the late 1970s, the database management system Oracle was produced by what is now called the Oracle Corporation and was probably the first commercial implementation of a relational DBMS based on SQL. ORACLE is now available in mainframe, client-server and PC-based platforms for many operating systems including DOS, OS/2, various UNIX operating systems, Windows, VAX/ VMS and MVS. Numerous RDBMS products now support the SQL language.
In 1986, the American National Standards Institute (ANSI) and the International Standards Organization (ISO) published an SQL standard, called SQL-86. IBM published its own corporate SQL standard, the Systems Application Architecture Database Interface (SAASQL) in
- An extended standard for SQL, SQL-89, was published in 1989. The ISO and ANSI committees created SQL-92 which was a more extensive expansion of SQL-86. SQL which was in fact a first major revision of the SQL standard is sometimes referred to as SQL2. It was not until 1999 that the next release of the standard was formalized, commonly referred to as SQL3. This release contains additional features that support object-oriented data management.
Scope
SQL is used for all types of database activities by all types of users including: ■ System administrators ■ Database administrators ■ Security administrators ■ Application programmers ■ Decision support system personnel ■ Many other types of end users
Language Components
The SQL language has several parts: - Data Retrieval : The SQL includes a query language based on both the relational algebra and the tuple relational calculus. Data-definition language (DDL) : The SQL DDL provides commands for defining relation schemas, deleting relations, creating indices, and modifying relation schemas. Interactive data-manipulation language (DML) : It includes commands to insert tuples into, delete tuples from, and to modify tuples in the database.
Figure 1.
In contrast to SQL statements, SQLPlus commands do not allow the manipulation of values in the database and are not stored in the SQL buffer. Commands are entered one line at a time and have a dash (-) as a continuation character if the command is longer than one line. It uses commands to format data retrieved through SQL statements. SQLPlus commands can be abbreviated where as SQL statements cannot. A few commands of SQL*Plus are as follows:-
DESC[RIBE] : To display the structure of a table e.g. SQL> DESC EMP SAV[E] filename [.ext]: Saves current contents of SQL buffer to a file e.g. SQL>SAVE D:\DATA\FINDSAL GET filename [.ext]: Writes the contents of a previously saved file to the SQL buffer. The default extension for the file is .sql. e.g. SQL> GET D:\DATA\FINDSAL @ : Runs a previously saved command file e.g. SQL>@ filename
SPO[OL] : Stores query results in a file e.g. SQL>SPOOL filename.ext SPOOL OFF : Closes the spool file SPOOL OUT : Closes the spool file and sends the file results to the system printer ED[IT] : Invokes the editor and saves the buffer contents to a file named afiedt.buf ED[IT] [ filename [.ext] ]: Invokes editor to edit contents of a saved file EXIT : Leaves SQL*Plus
Logging in to SQLPlus* To log in through a windows environment: ■ Click Start/Programs/Oracle for Windows/SQL*Plus ■ Fill in username, password, and database in the window shown in figure 1.4.
