Sunday 18 September 2011

DESIGN OF DATABASE USING ER MODELING AND NORMALIZATION


EX.NO:10(a)           DESIGN OF DATABASE USING ER MODELING AND
DATE:                                               NORMALIZATION

Professors consider the following information about the university database.

Professors have SSN number, name, age, rank and a research specialist.
Projects have project number, a sponsor name(DSD, DRDO,MOHRD,NSF) a starting date, an ending date and a budget.
Graduate students has SSN number, name, age and a degree programme.(MS,ME,M.Tech,Ph.D)
Each project is managed by one professor known as Project Principal Investigator.
Each project can be worked by one or more professors known as Project Co-investigator.
A professor can manage and/or on multiple projects.
Each project is worked on by one or more graduate students known as Project Research Assistants (PRA).
When a graduate student works on a project, a professor must supervise their work on the project.
Graduate students can work on multiple projects in which case they will have different supervisors for each one.
Departments have department number, department name and a main office.
Departments have professors known as chairman who runs the department.
Professors work in one or more departments and for each department that they work in the time percentage is associated with their job.
 Graduate students have one major department in which they are working on a degree.
Each graduate student has another more senior graduate student known as student advisor who advises him or her on what decision to take.

Design and draw a ER diagram  that captures the information about the University. Construct their respective tables.

ER MODEL

CREATING TABLES:

BEGIN
create table professors(prof_ssn char(10),age number(3),rank char(10),speiciality char(10),primary key(prof_ssn),name char(10))

create table depart(dno number(5),dname char(10),office char(10), primary key(dno))
create table proj(pid number(5),sponsor char(10),sdate date, edate date,budget number(10), primary key(pid))
create table graduate(grad_ssn char(10),age number(3),degree char(10) ,primary key(grad_ssn))
create table runs(prof_ssn char(10),dno number(10),primary key(prof_ssn,dno),foreign key(prof_ssn) references professors,foreign key(dno) references depart)

create table work_dept(prof_ssn char(10),dno number(10),pc_time number(10),primary key(prof_ssn,dno),foreign key(prof_ssn) references professors,foreign key(dno) references depart)

create table WORK_IN(prof_ssn char(10),pid number(10),primary key(prof_ssn,pid),foreign key(prof_ssn) references professors,foreign key(pid) references proj)

create table manage(prof_ssn char(10),pid number(10),primary key(prof_ssn,pid),foreign key(prof_ssn) references professors,foreign key(pid) references proj)

create table advisor(senior_ssn char(10) ,grad_ssn char(10),primary key(senior_ssn,grad_ssn),foreign key(senior_ssn) references graduate,foreign key (grad_ssn) references graduate)

create table supervisor(prof_ssn char(10) ,grad_ssn char(10),pid number(10),primary key(prof_ssn,grad_ssn,pid),foreign key(prof_ssn) references professors,foreign key (grad_ssn) references graduate,foreign key(pid) references proj )

end;

SQL>/

SQL>PL/SQL procedure completed successfully.

SQL> select * from professors;

PROF_SSN          AGE RANK       SPEICIALIT NAME
---------- ---------- ---------- ---------- ----------
1                   40 Emiratus   cse        Shashi
2                  38 Asst.Prof  Compiler   Vidhya
3                  30 Lecturer   Comp.Arch  Ravi
4                  38 Asst.Prof  Networks   Kishore
5                  38 Lecturer   Maths      Rani

5 rows selected.

SQL> select * from depart;

       DNO DNAME      OFFICE
---------- ---------- ----------
         1 cse        svce

1 row selected.


SQL> select * from proj;

       PID SPONSOR    SDATE     EDATE         BUDGET
---------- ---------- --------- --------- ----------
         1 DRDO       01-JAN-09 01-DEC-09     100000

1 row selected.

SQL> select * from graduate;

GRAD_SSN          AGE DEGREE     NAME
---------- ---------- ---------- --------------------
1                  20 BE         James
2                  21 ME         Richard
3                  20 BE         Roach
4                  20 BE         Rainey
5                  20 BE         Roland
6                  20 M.Tech     Ronald
7                  22 M.Sc       Rubert

7 rows selected.

SQL> select * from runs;

PROF_SSN          DNO
---------- ----------
1

1 row selected.

SQL> select * from work_dept;

PROF_SSN          DNO    PC_TIME
---------- ---------- ----------
1                   1         50

1 row selected.


SQL> select * from work_in;

PROF_SSN          PID
---------- ----------
1

1 row selected.


SQL> select * from manage;

PROF_SSN          PID
---------- ----------
1                   1

1 row selected.


SQL> select * from advisor;

SENIOR_SSN GRAD_SSN
---------- ----------
2          1
2          3
2          4
2          5
2          6
1          7

6 rows selected.


SQL> select * from supervisor;

PROF_SSN   GRAD_SSN          PID
---------- ---------- ----------
1          1                   1

1 row selected.

List out the names of all chairmans.

SQL> select p.prof_ssn, p.name from professors p, runs r where p.prof_ssn=r.prof_ssn;

PROF_SSN   NAME
---------- ----------
1          Shashi

List out the names of the graduate students who mentor more than 5 students.

SQL> select grad_ssn,name,degree from graduate where grad_ssn=(select senior_ssn from advisor group by senior_ssn having count(senior_ssn)=5);

GRAD_SSN   NAME                 DEGREE
---------- -------------------- ----------
2          Richard              ME

List out the professors who are not chairmans.

SQL> select prof_ssn,name from professors where prof_ssn in(select prof_ssn from  professors minus select prof_ssn from runs);

PROF_SSN   NAME
---------- ----------
2          Vidhya
3          Ravi
4          Kishore
5          Rani

0 comments:

Post a Comment