Database Management System LICT 3011 Eyad H Elshami
Database Management System LICT 3011 Eyad H. Elshami
Relational DBMS objects • RDBMS has many objects: – Table – View – Synonym – Sequence – Index – Procedure/Function
SQL • Structured Query Language (SQL) – Data Definition Language (DDL) • Create • Alter • Drop – Data Manipulation Language (DML) • insert • update • Delete – Select
Create Table Create table TABLENAME( Col 1 Name Col 2 Name Col 3 Name. . . Colx. Name ); datatype constraint default, default, datatype constraint default
Data types Data Type Use to store number(n) number(p, s) an integer number within size n digits char(n) varchar 2(n) date raw Long raw a fixed length character BLOB binary file with size 4 G a real number within size p digits s of them after the floating point. a variable length character date and time binary data file with size 2000 B binary file with size 2 G
Constraints • Not null – Attribute could not be empty • Unique – Attribute value could not be duplicated • Check – Attribute value must satisfies some condition • Primary key – Attribute value satisfies not null, unique, and indexing • Foreign key (reference) – Attribute value must be in the reference table
Create table example Create Table College( CID Cname ); number(2) primary key, varchar 2(25) unique not null
Create table example Create table students( SID number(5) primary key, Sname varchar 2(25) not null, Sgender char(1) default ‘m’ check(Sgender in(‘m’, ’f’)), Sbdate, CID number(2) references College(CID), Saverage ); number(5, 2)
Alter Table • Alter Table. Name – Add Column. Name datatype default Constraint; – Add Constraint. Name Constrain. Type; – Modify Column. Name newdatatype; – Drop column Column. Name; – Drop Constraint. Name;
Alter Examples • Alter table students add Sphoto long raw; _________________ • Alter table students modify sname varchar 2(20); _________________ • Alter table students drop column sphoto;
Alter Examples • Alter table students add constraint sname_u unique(sname); _________________ • Alter table students drop constraint sname_u; _________________
http: //itc-shami. iugaza. edu: 5560/isqlplus/
Create User, change you password • create user User. Name identified by Password default tablespace users temporary tablespace temp; • alter user User. Name identified by Newpassword;
Grant privileges to user Grant srole, unlimited tablespace to USERNAME;
Change you password • alter user User. Name identified by Newpassword;
Describe a table • describe tablename To see the tables name –Select * from tab;
- Slides: 16