Systems Database

Systems Database is a collection of tables that are used to store information about systems in a typical lab.

Relational Tables

The following are the tables used to store the information about system, softwares and components.

Table Description
Vendors Stores information about vendors
Systems Stores information about all systems in the lab.
Components Stores information about components installed into all systems.
Software_master Contains information related to software like code and name.
Softwares Contains information about softwares installed into all system along with space occupied etc.

Script To Create Tables

The following script is used to create the tables mentioned above.


CREATE TABLE VENDORS 
(  VID  NUMBER(3) 
     CONSTRAINT  VENDORS_PK PRIMARY KEY,
   VNAME  VARCHAR2(20) 
     CONSTRAINT  VENDORS_VNAME_NN 
     NOT NULL, 
   VADDRESS  VARCHAR2(30),
   PHONENO   VARCHAR2(10),
   MOBILE       VARCHAR2(10),
   EMAIL          VARCHAR2(30),
   REMARKS    VARCHAR2(50)
);


CREATE TABLE  SYSTEMS 
(  SID   NUMBER(4)  
      CONSTRAINT SYSTEMS_PK PRIMARY KEY,
   VID    NUMBER(3)
      CONSTRAINT SYSTEMS_VID_FK
      REFERENCES  VENDORS(VID),
   BRAND   VARCHAR2(20),
   PROCESSOR VARCHAR2(10),
   RAM        NUMBER(4) 
      CONSTRAINT SYSTEMS_RAM_CHK
      CHECK  (  RAM > 0 ),
   HDD       NUMBER(3),
   DP          DATE,
   WPERIOD  NUMBER(2),
   OS           VARCHAR2(10)
);

CREATE TABLE  COMPONENTS 
(  SID  NUMBER(4)
     CONSTRAINT COMPONENTS_SID_FK
     REFERENCES  SYSTEMS(SID) 
     ON DELETE CASCADE,
   COMPONENT  VARCHAR2(10),
   MAKE              VARCHAR2(10),
   VID    NUMBER(3)
      CONSTRAINT COMPONENTS_VID_FK
      REFERENCES  VENDORS(VID),
   DP                  DATE,
   WPERIOD       NUMBER(2),
    CONSTRAINT COMPONENTS_PK
      PRIMARY KEY (SID,COMPONENT)
);


CREATE TABLE SOFTWARE_MASTER 
(  SCODE  VARCHAR2(5) PRIMARY KEY,
   SNAME VARCHAR2(20)
);

CREATE TABLE SOFTWARES
(  SID  NUMBER(4)  REFERENCES SYSTEMS 
               ON DELETE CASCADE,
   SCODE VARCHAR2(5)  REFERENCES SOFTWARE_MASTER,
   SPACE   NUMBER(4),
   DL         DATE,
   PRIMARY KEY( SID,SCODE));

Queries And Answers

The following are the queries and answers. However note that there may be another way to get the result. What i have given is only one of the possible ways.

Display employees where name starts with S and ends with H
select ename from emp where ename like 'S%H'; 
Display emp no.,name,gross sal (sal+40% on salary)
select empno,ename,sal+0.40*sal grosssal from emp;
Display emp no.,name,job of employees where the job contains letters P&R
select empno,ename,job from emp where ename like'%P%' and ename like '%R%'; 
Display details of employees who joined during 1982 and drawn more than 3000
select * from emp where hiredate between '01-jan-1982' and '31-dec-1982' and sal > 3000        
Display dept no.,empname,hiredate and salary in the ascending order by dept and hire date then the descending order of sal
select deptno,ename,hiredate,sal
from emp order by deptno,hiredate,sal desc;   
Display employees where salary is less than 3000 and job is manager in the ascending order of hire date
select * from emp where job='manager' and sal > 3000 order by hiredate 
Display the softwares that are loaded in last 3 days
select * from softwares  where sysdate-dl < 3;
Display sid,dp and date of expiry of warranty
select sid,dp,add_months(dp,wperiod) from systems;
Display details of softwares by rounding the space occupied to GB
select sid,round(space/1024) from softwares; 
Display software date of loading and date on which the request was made assuming the software is loaded the nextday of request and date of verification assuming all softwares are verified on saturday
select dl-1 requestdate, next_day(dl,'sat') verificationdate from softwares;
Display software date of loading, number of months since loading, space occupied for all softwares that were loaded in last 6 months
select dl, months_between(sysdate,dl) Months 
from softwares
where months_between(sysdate,dl) <= 6
Change date of purchase for system 150 to previous monday
update systems set dp = next_day(dp-8,'mon')
where sid = 150;
Delete software called 'NAV' if it was loaded more than 120 days back
delete from softwares where software  = 'NAV'
and sysdate-dl > 120;    
Display the systems we purchase in the current year
Select * from systems
where to_char(sysdate,'yyyy') = to_char(dp,'yyyy'); 
Display sid,brand, processor,ram ,hdd for systems that contain XP operating system and the price of ram in the system is more than 1000 price is calculated as 750 for each 128 mB
select sid,brand ,processor,ram,hdd,osystems from systems 
where os like '%xp%' and ram/128 * 750 > 1000; 
Display the softwares that are loaded on sunday in the current month
select sid from softwares
where to_char(dl,'mm') = to_char(sysdate,'mm') and to_char(dl,'d') = 1
Display sid,company name and model
select sid, substr(brand,instr(brand,' ')-1) company, substr(brand,instr(brand,' ') +1) model 
from systems;   
Display the softwares that occupy more than 1 GB and software is containing the word 'Anti'
select scode,sum(space) 
from softwares_master sm,softwares s 
where software like '%Anti%'
group by s.scode
having sum(space) > 1024 
Display vid,no. of systems purchased from vendor
select vid,count(*)
from systems group by vid;
Display year and no.of systems purchased from vendor
select to_char(dp,'yyyy'), count(*)
from systems group by to_char(dp,'yyyy');
Display the avg space occupied by vs and also the no. of systems containing vs
select count(*) Count,avg(space) AvgSpace
from softwares where scode='vs'; 
Display sid,total no. of softwares,size of largest software
select sid,count(*) nosoftwares,max(space) largest_software 
from softwares group by sid;            
Display vid,processor and no. of systems we purchase
select vid,proceesor,count(*)
from systems group by vid,processor ;   
Display sid,the date on which most recent software was loaded
select sid,max(dl) from softwares
group by sid;
  

Display the vendor who supplied more than 1 system in a current month
select vid
from systems
where to_char(sysdate,'mmyy') = to_char(dp,'mmyy')
group by vid
having count(*) > 1
Display the day on which most recent software was loaded into machine 3
select to_char(max(dl),'day')
from softwares where sid=3;  
Display sid,brand,software,space for all systems with processor 'p4'
select s.sid,brand,scode, space 
from systems s,softwares so
where processor ='p4' and s.sid = so.sid;
Display vid,component and no. of units
select vid,component,count(*)
from  components
group by vid,component;
Display vname, no. of components supplied in this week
select  v.vname,count(*) 
from vendors v, components c
where  v.vid = c.vid and dp >=next_day(sysdate-7,'mon')  
group by vname; 
Display sid,processor,ram and vname for systems which still have warranty
select sid,processor,ram,vname from
systems s, vendors v
where  add_months(dp,wperiod) > sysdate and  s.vid  = v.vid;
Display sid and total no. of softwares,space of software should include space of os space of os is taken as - XP: 1GB, 2000 server:1.3GB,Win 98:400MB,500MB others
select so.sid, sum(space)+ max( decode(os,'winxp',1024,'win2000',1.3*1024,'win98',400,500)) space
from softwares so,systems s  
where s.sid=so.sid 
group by so.sid;
Display OS and no .of systems with that operating systems and date on which system with os was most recently purchased
select os,count(*),max(dp)
from systems
group by os;
Display systems purchased from vendor who deals with wipro
select * from systems
where sid in (select vid from vendors where remarks like '%WIPRO%');
Display details of vendors who supplied a systems in current year
select * from vendors where vid in
(select vid from systems where to_char(sysdate,'yyyy') = to_char(dp,'yyyy') ); 
Display softwares loaded into systems that was purchased in current month and of brand IBM
select * from softwares where sid in 
  (select sid from systems where brand='IBM' and to_char(dp,'mmyy')=to_char(sysdate,'mmyy') );
Delete details of components related to any systems for which warranty period expired
delete from components where sid in
   in ( select sid from systems  where  Add_months(dp + wperiod) < sysdate) 
                                                                                                   
change the space occupied by vs.net in all systems to the avg space occupied by vs.net
update softwares
      set space=(select avg(space) from softwares where scode='vs.net')
   where scode='vs.net';                                                                                                                          
insert into components with the following details : Component is 'modem', make is 'Dlink',vendor is 'computer needs', dp is '1 st day of current month', system was the one that was purchased is last day of previous month
Insert into components values
((select sid from systems where trunc(dp)=trunc(last_day(add_months(sysdate,-1)))),
'Modem','Dlink',
(select vid from vendors where vname='computer needs'), '1-' || to_char(sysdate,'mon-yy'),
12);
Display the systems into which we haven't installed any software in the last 30 days
select * from systems where sid not in 
   (select sid from softwares where sysdate-dl <= 30);
Display the details of vendors who have supplied either a component or the system in the current month
select * from vendors where vid in 
    (
     select vid from systems where  to_char(sysdate,'mmyy')=to_char(dp,'mmyy') 
     union
     select vid from components where  to_char(sysdate,'mmyy')=to_char(dp,'mmyy')
    );
Display details of vendors who have supplied max .no.of systems
select * from vendors where vid in 
(select vid from systems
 group by vid
 having count(*)  =
   ( select max( count(*)) 
     from systems
     group by vid
   )
);

Display the vendors who supplied more than 1 unit of a component
select * from vendors where vid in 
(select vid from components
group by vid, component
having count(*) > 1);   
Display the systems which contain any components or which contain more than 3 softwares and the system was purchased in the current month
select * from systems where to_char(dp,'mmyy')=to_char(sysdate,'mmyy')
   and sid in (select sid from components)    
   or sid in (select sid from softwares group by sid having count(*)>3);
Find out the percentage of space in system 2 was used and display a message 50% of 20 GB was used
Declare 
     v_hdd systems.hdd%type;
     v_space number(5);
Begin   
     select hdd  into v_hdd from systems where sid= 2;

     select sum(space) into v_space
     from softwares where sid=2;                         
   
     dbms_output.put_line(round(v_space/(hdd*1024)*100) || '% of '|| hdd ||'GB was used');
End;
Display the space occupied by oracle in system 3 if oracle is not found you insert a row for oracle in system 3 with space 1500MB
declare
 v_space  softwares.space%type;
begin
 select  space into v_space
 from softwares where software ='oracle' and sid = 3;
 dbms_output.put_line( v_space);

exception
 when no_data_found then
   insert into softwares values (3,'oracle',1500,sysdate);
 
end;
Change the ram from 256 to 512 for system 3 if system has softwares VS and oracle
Update systems set ram  = 512
where sid = 3 
  and exists
   ( select sid from softwares where sid = 3 and software = 'VS'
     intersect
     select sid from softwres where sid = 3 and software = 'Oracle');
Change the size of hard disk from 40 to 80, 20 to 40 if the space occupied is more than 75%
Declare
 cursor syscur is select sid, hdd from systems where hdd in ( 20,40);
 v_space number(6);
Begin
 for r in syscur
 loop
    -- get total space occupied
    select sum(space) into v_space
    from softwares
    where  sid = r.sid;

    if  v_space / r.hdd > 0.75 then
          update systems hdd = hdd * 2 
          where  sid = r.sid;
    end if;
 end loop;

End;

Display sid,brand for systems 10th to 15throw
Declare 
   cursor syscur  is select sid,brand from systems;
begin
   for R in syscur
   loop
      if syscur%rowcount >= 10 and syscur%rowcount<=15 then
        dbms_output.put_line(R.sid);
        dbms_output.put_line(R.brand);
        exit when  syscur%rowcount = 15
      end if;
   end loop;
end;

create a function that takes scode and returns the sysid in which the software occupies the max space
create or replace function maxspace(pscode varchar) return number
is
 psid   systems.sid%type;
begin
     select sid into psid
     from softwares
     where scode=pscode and
           space = ( select max(space)
                     from softwares
                     where scode=pscode);

     return psid;
end;   
Write a function that takes sid and returns the software loaded into the systems by seperating softwares with comma
create or replace function getsoftwares(psid number) return varchar
is
 cursor softcur is
 select scode from softwares
 where sid=psid;
 softstr varchar(100):=' ';
begin
  for r in softcur
  loop
      softstr:=softstr ||','|| r.scode;
  end loop;
  return substr(softstr,2);
end; 
create a procedure to take sid and scode and remove the software from system and throw an error if software is not present in system
create or replace procedure removesoftware(psid number, pscode varchar2)
is
begin
   delete from softwares where sid = psid and scode = pscode;
   if sql%notfound then
      raise_application_error(-20500,'Software not found in the system');
   end if;
end;

Object Model Of Systems Database

The following is object model of Systems application that we have implemented using relational model earlier.

VENDOR_TYPE - Object type for vendors

create  or replace  type vendor_type as object
( name  varchar2(20),
  address varchar2(50)
);
/

SOFTWARE_TYPE - Object type for software

create or replace type software_type as object
( software  varchar2(20),
  dl        date,
  space     number(5),
  member procedure  changespace(newspace number),
  member procedure  changedl(newdl date)
);
/

create or replace type body software_type is
  member procedure  changespace(newspace number)
  is
  begin
       space := newspace;
  end;
  member procedure  changedl(newdl date)
  is  
  begin
      if  newdl > sysdate then
          raise_application_error(-20100,'Invalid Date');
      else
          dl := newdl;
      end if;
  end;
end;
/

SOFTARES_TABLE - table type that contains a collection of software types

create type software_table as table of software_type;

SYSTEM_TYPE - Object type to store information about system

create or replace type system_type as object
(  sid  number(3),
   brand varchar2(30),
   hdd   number(3),
   dp    date,
   price number(6),
   vendor ref  vendor_type,
   softwares   software_table,
   member function GetAvailSpace return number,
   member function GetSoftwares return varchar,
   member function IsInstalled(software varchar2)  return boolean
);
/

create or replace type body system_type is

   member function GetAvailSpace return number
   is
     space number(5) :=0;
   begin
        for i in 1..softwares.count
        loop
            space := space + softwares(i).space;
        end loop;
        return ( hdd * 1024 - space);
   end;

   member function GetSoftwares return varchar
   is
      st varchar2(100) := '';
   begin
      for i in 1..softwares.count
      loop
            st := st ||  ',' || softwares(i).software ;
      end loop;
      return substr(st,2);
   end;

   member function IsInstalled(software varchar2) return boolean
   is
     
   begin
      for i in 1..softwares.count
      loop
            if softwares(i).software = software then
                 return true;
            end if;
      end loop;
      return false;

   end;

  
     
end;
/

Creating Required Object Tables and Inserted Data

create table  vendors of vendor_type;

insert into  vendors values('computer needs','Dwarakanagar, Vizag');

insert into  vendors values('computer components','Dabagaradens, Vizag');


create table systems of system_type
nested table softwares store as softwares_table;

insert into systems 
  values (1,'Compaq Presario','20','3-feb-04',55000, 
              ( select ref(v) from vendors v where name ='computer needs'),
          software_table ( software_type( 'windows xp prof','3-feb-04',1000),
                           software_type( 'VS.NET','4-feb-04',2000)
                         )
         );



insert into systems 
  values (2,'HP Pavilion','60','1-Mar-04',45000, 
              ( select ref(v) from vendors v where name ='computer needs'),
          software_table ( software_type( 'window 2003 server','1-mar-04',2500),
                           software_type( 'Oracle9i','2-mar-04',3000)
                         )
         );

Queries and DML Commands

select  sid,brand,s.vendor.name from systems s;

select  sid,brand,dp,round(s.GetAvailSpace() / 1024,2) || 'GB Available' AvailSpace, s.getsoftwares() from systems s;

insert into table ( select softwares from systems where sid = 2)
  values ('Oracle Client',sysdate,'30');

Stored Procedures


create or replace  procedure  IsExisting( v_sid number, v_software varchar2) is

sysobj system_type;

begin
   select value(s) into sysobj
   from systems s
   where  sid = v_sid;

   if  sysobj.IsInstalled( v_software) then
       dbms_output.put_line( v_software || ' Is Installed');
   else
       dbms_output.put_line( v_software || ' Is NOT Installed');
   end if;
 

end;
/