Database Management System LICT 3011

1. Database Management SystemLICT 3011 Eyad H. Elshami

2. Relational DBMS objects • RDBMS has many objects: • Table • View • Synonym • Sequence • Index • Procedure/Function

3. SQL • Structured Query Language (SQL) • Data Definition Language (DDL) • Create • Alter • Drop • Data Manipulation Language (DML) • insert • update • Delete • Select

4. Create Table Create table TABLENAME( Col1Name datatypeconstraintdefault, Col2Name datatypeconstraintdefault , Col3Name datatypeconstraintdefault, . . . ColxNamedatatypeconstraintdefault );

5. Data types

6. 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

7. Create table example Create Table College( CID number(2) primary key, Cname varchar2(25) unique not null );

8. Create table example Create table students( SID number(5) primary key, Sname varchar2(25) not null, Sgender char(1) default ‘m’ check(Sgender in(‘m’,’f’)), Sbdate date, CID number(2) references College(CID), Saveragenumber(5,2) );

9. Alter Table • Alter Table TableName • Add ColumnName datatype default Constraint; • Add Constraint ConstraintNameConstrainType; • Modify ColumnName newdatatype; • Drop column ColumnName; • Drop Constraint ConstraintName;

10. Alter Examples • Alter table students add Sphoto long raw; __________________________________ • Alter table students modify sname varchar2(20); _________________________________ • Alter table students drop column sphoto;

11. Alter Examples Alter table students add constraint sname_uunique(sname); __________________________________ Alter table students drop constraint sname_u; _________________________________

12. http://itc-shami.iugaza.edu:5560/isqlplus/

13. Create User, change you password • create user UserName identified by Password default tablespace users temporary tablespace temp; • alter user UserName identified by Newpassword;

14. Grant privileges to user Grant srole, unlimited tablespace to USERNAME;

15. Change you password • alter user UserName identified by Newpassword;

16. Describe a table • describe tablename To see the tables name • Select * from tab;