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