PL I – ASSIGNMENT 3

ASSIGNMENT NO.3

Title:

            Design and Develop SQL DDL statements which demonstrate the use of SQL objects such as Table, View , Index using Client-Data sever(two tier).

Requirements:

  1. Computer System with Linux/Open Source Operating System.
  2. Mysql Server
  3. JDK
  4. JDBC Connector
  5. Eclipse

Theory:

Two Tier Architecture:

 exp1

 

Data Definition Language

DDL(Data Definition Language) statements are used to create, delete, or change the objects of a database.

Typically a database administrator is responsible for using DDL statements or production databases in a

large database system. The commands used are:

  • Create – It is used to create a table.
  • Alter – This command is used to add a new column, modify the existing column definition and to

include or drop integrity constraint.

  • Drop – It will delete the table structure provided the table should be empty.
  • Truncate – If there is no further use of records stored in a table and the structure has to be retained,

then the records alone can be deleted.

  • Describe – This is used to view the structure of the table.

 

Table Creation

Rules:

  • Reserved words cannot be used.
  • Underscore, numerals, letters are allowed but not blank space.
  • Maximum length for the table name is 30 characters.
  • 2 different tables should not have same name.
  • We should specify a unique column name.
  • We should specify proper data type along with width.
  • We can include “not null” condition when needed. By default it is ‘null’.

Syntax

create table <table name>

{

fieldname-1 datatype constraints if any,

fieldname-2 datatype constraints if any,

…….

fieldname-n datatype constraints if any,

};

create table <table name> as

(

select(att-list) from <existing table name>

);

 

Sample Code:

//STEP 2: Register JDBC driver

Class.forName(“com.mysql.jdbc.Driver”);

//STEP 3: Open a connection

System.out.println(“Connecting to a selected database…”);

conn = DriverManager.getConnection(DB_URL, USER, PASS);

System.out.println(“Connected database successfully…”);

//STEP 4: Execute a query

System.out.println(“Creating table in given database…”);

stmt = conn.createStatement();

String sql = “CREATE TABLE REGISTRATION ” +

“(id INTEGER not NULL, ” +

” first VARCHAR(255), ” +

” last VARCHAR(255), ” +

” age INTEGER, ” +

” PRIMARY KEY ( id ))”;

stmt.executeUpdate(sql);

System.out.println(“Created table in given database…”);

}catch(SQLException se){

//Handle errors for JDBC

 

ALTER TABLE – syntax

alter table <table name> add/modify

(

fieldname-1 datatype,

fieldname-2 datatype,

…..

fieldname-n datatype

);

alter table drop column column name;

 

DESCRIBING TABLE

Describe  <tablename>;

CHANGING NAME OF AN OBJECT

To change the name of a table, view, sequence, or synonym, execute the rename statement.

Syntax: rename old name to new name;

 

TRUNCATE:The truncate table statement

  • Removes all rows from a table
  • Release the storage space used by that table
  • We cannot rollback row removal when using truncate.

 

Syntax:truncate table <table name>;

 

DROP TABLE

  1. All data and structure in the table is deleted
  2. Any pending transactions are committed.
  3. All indexes are dropped.
  4. We can not rollback the drop table statement.

 

Syntax:drop table <table name>;

 

VIEW:

A view is the tailored presentation of data contained in one or more table and can also be said as restricted view to the data‟s in the tables. A view is a “virtual table” or a “stored query” which takes the output of a query and treats it as a table. The table upon which a view is created is called as base table.

A view is a logical table based on a table or another view. A view contains no data of its own but is like a window through which data from tables can be viewed or changed. The tables on which a view is based are called base tables. The view is stored as a SELECT statement in the data dictionary

 

Advantages of a view:

  1. Additional level of table security.
  2. Hides data complexity.
  3. Simplifies the usage by combining multiple tables into a single table.
  4. Provides data‟s in different perspective.

 

Creating and dropping view:

Syntax:

Create view <view name> [column alias names] as <query> [with <options> conditions]; Drop view <view name>;

 

Example: Create view empview as select * from emp;

 

Drop view empview;

 

INDEX:

Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. If a table has 1,000 rows, this is at least 100 times faster than reading sequentially. If you need to access most of the rows, it is faster to read sequentially, because this minimizes disk seeks.

Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Exceptions are that indexes on spatial data types use R-trees, and that MEMORY tables also support hash indexes.

MySQL uses indexes for these operations:

  • To find the rows matching a WHERE clause quickly.
  • To eliminate rows from consideration. If there is a choice between multiple indexes, MySQL normally uses the index that finds the smallest number of rows.
  • To retrieve rows from other tables when performing joins. MySQL can use indexes on columns more efficiently if they are declared as the same type and size. In this context, VARCHAR and CHAR are considered the same if they are declared as the same size. For example, VARCHAR(10) and CHAR(10) are the same size, but VARCHAR(10) and CHAR(15) are not.

 

 

CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name

[index_type]

ON tbl_name (index_col_name,…)

[index_option] …

 

index_col_name:

col_name [(length)] [ASC | DESC]

 

index_type:

USING {BTREE | HASH | RTREE}

 

index_option:

KEY_BLOCK_SIZE [=] value

| index_type

| WITH PARSER parser_name

 

Example of Index:

The statement shown here creates an index using the first 10 characters of the name column:

CREATE INDEX part_of_name ON customer (name(10));

 Conclusion:

Thus we have design and developed SQL DDL statements which demonstrate the use of SQL objects such as Table, View , Index using Client-Data sever(two tier).

 ********************** PROGRAM ************************

AIM: Design and Develop SQL DDL statements which demonstrate
 the use of SQL objects such as Table, View, 
Index using Client-Data sever(two tier)


SQL> create table student_te_a(rno varchar2(10),fname varchar2(10),lname varchar2(10),deptno number(2),percent number(5,2),dob date);
Table created.


SQL> alter table student_te_a add constraint pk_1 primary key(rno);
Table altered.

SQL>create table dept(dname varchar2(10),dno number(2),location varchar2(10));
Table created.


SQL> alter table dept add constraint pk_dept_1 primary key(dno);
Table altered.

SQL> alter table student_te_a add constraint fk_stud_1 foreign key(deptno) references dept(dno);
Table altered.


SQL> insert into student_te_a values(1,'Sandesh','Gangwal',10,60.23,'12/Jul/1992');
ORA-02291: integrity constraint (SYSTEM.FK_STUD_1) violated - parent key not found

SQL> alter table dept add constraint unique_1 unique (dname);
Table Altered

SQL> alter table dept add constraint cheque_1 check(dno >0 and dno < 100);
Table Altered

SQL> alter table dept modify dname not null;
Table altered.

SQL> alter table student_te_a add address varchar2(10);
Table altered.

SQL> desc student_te_a;

SQL> alter table student_te_a drop column dob;

Table dropped.

SQL> desc student_te_a;

SQL> drop table dept;
TABLE DROPPED

SQL> create index ind_1 on student_te_a(percent);
Index created.

SQL> create or replace view deptview as select dno,dname,rno,fname from dept,
student_te_a where student_te_a.deptno=dept.dno;
View created.

SQL> select * from deptview;


Leave a Reply

Your email address will not be published. Required fields are marked *