DBMS LAB 5

-
Database Schema for a student-Lab scenario

Student(stud_no: integer, stud_name: string, class: string)
Class(class: string, descrip: string)
Lab(mach_no: integer, Lab_no: integer, description: String)
Allotment(Stud_no: Integer, mach_no: integer, dayof week: string)

For the above schema, perform the following—
a)   Create the tables with the appropriate integrity constraints
b)   Insert around 10 records in each of the tables
c)   List all the machine allotments with the student names, lab and machine numbers
d)   List the total number of lab allotments day wise
e)   Give a count of how many machines have been allocated to the ‘CSIT’ class
f)    Give a machine allotment etails of the stud_no 5 with his personal and class details
g)   Count for how many machines have been allocatedin Lab_no 1  for the day of the week as “Monday”
h)   How many students class wise have allocated machines in the labs
i)    Create a view which lists out the stud_no, stud_name, mach_no, lab_no, dayofweek
j)    Create a view which lists the machine allotment details for “Thursday”
AIM: Create the tables with the appropriate integrity constraints
              Insert around 10 records in each of the tables

HW/SW requirements:
Processor                   :       AMD Athelon ™ 1.67 GHz
RAM                          :       256 MB
Hard Disk                   :       40 GB
       Software               :      Oracle

SQL>create table stu(stud_no number(5) primary key,stud_nam varchar2(20),class varchar2(20));

SQL> desc stu;

Name                                    null?              Type
STUD_NO                          NOT NULL         NUMBER(5)
STUD_NAM                                               VARCHAR2(20)
CLASS                                                      VARCHAR2(20)

Valid Data Sets:

SQL> insert into stu values(&stud_no,’&stud_nam’,’&class’);
SQL> select * from stu;

STUD_NO                    STUD_NAM         CLASS
39                             LEON                 CSE
34                              VIKAS                 CSIT 
18                              MATHEW             ECE
8                                HANSEN             MECH
24                             ALEXIS              EEE

SQL> Create table class (class varchar2(20), descript varchar2(10));
SQL> Describe class;

Name                                         null                               type

CLASS                                                        VARCHAR2(10)
DESCRIPT                                                  VARCHAR2(20)

SQL> create table lab(match_no number(5), lab_no number(5), description varchar2(20));

SQL> desc lab;

Name                                       null                                    type

MACH_NO                          NOT NULL                  NUMBER(5)
LAB_NO                                                             NUMBER(5)
DESCRIPTION                                                     VARCHAR2(20)

SQL> insert into lab values(&mach_no,&lab_no,’&description’);

SQL> select * from lab;

MATCH_NO LAB_NO     DESCRIPTION
---------------       ---------      --------------------
23                     7              physics
78                     2              chemistry
87                     1              edc
12                     10             cds
8                      3              java lab

SQL> create table allotment(stud_no number(5) references stu(stud_no), match_no number(5) references lab(mach_no),
Doweek varchar2(20));

SQL> desc allotment;

Name                Null?          Type
--------------        -------        ---------
STUD_NO                           NUMBER(5)
MACH_NO                          NUMBER(5)
DOWEEK                            VARCHAR2(20)


SQL>select * from allotment;

STUD_NO           MACH_NO          DOWEEK
------------- --    ------------          ------------
39                     23                     sat
34                     87                     mon
18                     78                     tue
8                      12                     wed
24                     12                     thu


c)   List all the machine allotments with the student names, lab and machine numbers

SQL>select s.studname,l.machno from student1 s,lab l,allotment a where a.machno=l.machno and a.studno=s.studno;

STUDNAME      MACHNO
………………………………………..
ABHIJEET           1
KALYAN             22
ASHWIN             3
ARKA                 4
ARVIND             5


d)   List the total number of lab allotments day wise

SQL>select l.machno,l.descrip,a.day from lab l,allotment a where a.machno=l.machno;

MACHNO     DESCRIP           DAY
……………………………………………………………………
1               UNIX                  MONDAY
22             UNIX                 TUESDAY
3              XP                     WEDNESDAY
4              WINDOWS          THRUSDAY
5              ME                    FRIDAY

e)   Give a count of how many machines have been allocated to the ‘CSIT’ class

SQL>select count(machno)from allotment where studno in(select studno from student1 where class=’CSIT’);

COUNT (MACHNO)
……………………..
      1

f)    Give a machine allotment etails of the stud_no 5 with his personal and class details

SQL>select a.studno,a.machno,s.studname,s.class from allotment a,student1 s where a.studno=s.studno and a.studno=503;
  
STUDNO       MACHNO          STUDNAME         CLASS
………………………………………………………………………………………………………
503                   5              ARVIND             CSE

g)   Count for how many machines have been allocatedin Lab_no 1  for the day of the week as “Monday”


h)   How many students class wise have allocated machines in the labs

SQL>select count(studno) “allocated students in the labs”,class from student1 where studno in(select studno from allotment) group by class;

allocated students in the lab              CLASS
……………………………………………………………………………
       2                                                CSE
       1                                                ECE  
       1                                                EEE
       1                                                IT

i)    Create a view which lists out the stud_no, stud_name, mach_no, lab_no, dayofweek


j)    Create a view which lists the machine allotment details for “Thursday”



Comments

Post a Comment

Popular posts from this blog

PROCEDURE TO CREATE AN ANIMATION TO REPRESENT THE GROWING MOON.

-
Image
1.       Open flash 8 software -> click on flash document ->go to windows->properties ->select the properties tool-> choose the Background to black. 2.       Go to fill color under tool bar-> select the white color. 3.       Select the oval tool in order to draw the moon. u will get a white circle. 4.       Select the oval tool in order to draw the moon. u will get a white circle. 5.       Select the white circle on the worksheet using the selection tool ->right click- >convert to symbol ->select movie clip ->give suitable name e g: moon->click ok. 6.       Go to filter ->click on the + symbol->select glow to apply glowing effect-> select the color to white under glow and adjust the blur x/blur y values. 7.       Click on the...
Read more

PROCEDURE TO CREATE AN ANIMATION TO INDICATE A BALL BOUNCING ON STEPS.

-
Image
1.     Go to start- macromedia- click on flash document 2.     Select the line tool and draw the steps. color it using the paint bucket tool 3.     Select the circle from the tool bar and create a circle on the work area. 4.     Now fill the color to the circle using the paint bucket tool from the tool bar. 5.     Go to frames right click on the first frame and choose insert key frame. Slightly move the ball. Repeat the same procedure by adding new key frames to show the ball change the shape of the ball slightly when it touches the surface. 6.     In order to change the shape use the free transform tool. 7.     Go to control and click on test movies .you will observe the ball bouncing on steps.
Read more

PROCEDURE TO SIMULATE MOVEMENT OF A CLOUD.

-
Image
1.       go to start- macromedia- click on flash document 2.       create a blue background in layer 1 3.       Now insert a layer 2 and draw the clouds in this layer. 4.       in order to create the clouds, go to tool bar and select pencil option, draw the cloud in layer2 5.       fill the color to the cloud, right click on it- choose convert to symbol option- give the name as cloud 6.       select the movie clip option and click ok. 7.       Go to filter ->click on the + symbol->select glow to apply glowing effect-> select the colour to white 8.       under glow and adjust the blur x/blur y values. 9.       give the appropriate blur effect to the cloud. 10.   go to frames, insert key frame on both ...
Read more