Marvel College needs a system which will satisfy the following assumptions and r
ID: 3545604 • Letter: M
Question
Marvel College needs a system which will satisfy the following assumptions and requirements.
1. Each faculty member is assigned to exactly one department.
2. Each student can be employed by more than one department. Each student may work for a given department in only a single capacity, however.
3. Each student graduated from exactly one high school.
4. Each student has exactly one faculty member as an advisor. The advisor must be assigned to the department in which the student is majoring.
5. The system must be able to handle the following queries:
a. For each department, list the number (D2) and the name (C20).
b. For each faculty member, list the number (D4) and name (C20)as well as the number and name of the student
Explanation / Answer
CREATE TABLE DEPT
(
DEPTNUMB NUMBER (4, 0) PRIMARY KEY,
DEPTNAME VARCHAR2 (100)
);
CREATE TABLE FACULTY
(
FACNUMB NUMBER (4, 0) PRIMARY KEY,
FACNAME VARCHAR2 (100),
DEPTNUMB NUMBER (4, 0)
CONSTRAINT fk_faculty_deptnumb REFERENCES dept (deptnumb)
);
CREATE TABLE COURSE
(
CRSECODE VARCHAR2 (30) PRIMARY KEY,
CRSEDESC VARCHAR2 (100),
NUMCRED NUMBER (22, 0)
);
CREATE TABLE HIGHSCHL
(
HSCODE VARCHAR2 (30) PRIMARY KEY,
HSNAME VARCHAR2 (100)
);
DROP TABLE student;
CREATE TABLE STUDENT
(
STUNUMB NUMBER (30, 0) PRIMARY KEY,
STUNAME VARCHAR2 (100),
GPA VARCHAR2 (30),
FACNUMB NUMBER (4, 0)
CONSTRAINT fk_facnumb_faculty REFERENCES faculty (facnumb),
HSCODE VARCHAR2 (30)
CONSTRAINT fk_hscode_highschl REFERENCES highschl (hscode)
);
CREATE TABLE STUDEPT
(
STUNUMB NUMBER (30, 0)
CONSTRAINT fk_studept_stdno REFERENCES student (stunumb),
DEPTNUMB NUMBER (4, 0) CONSTRAINT fk_deptnumb REFERENCES dept (deptnumb),
CAPACITY VARCHAR2 (300)
);
DROP TABLE faccrse;
CREATE TABLE FACCRSE
(
FACNUMB NUMBER (22, 0)
CONSTRAINT fk_faccrse_numb
REFERENCES faculty (facnumb) ON DELETE CASCADE,
CRSECODE VARCHAR2 (30)
CONSTRAINT fk_crsecode_faccrse
REFERENCES course (crsecode) ON DELETE CASCADE
);
CREATE TABLE STUCRSE
(
STUNUMB NUMBER (30, 0)
CONSTRAINT fk_stucrse_numb REFERENCES student (stunumb),
CRSECODE VARCHAR2 (30)
CONSTRAINT fk_stucrse_crsecode REFERENCES course (crsecode),
GRADE VARCHAR2 (10)
);
===========================================================
1.SELECT deptnumb, deptname FROM dept;
2.SELECT S.STUNUMB,
s.stuname,
s.facnumb,
f.facname,
f.deptnumb,
d.deptname
FROM student s, faculty f, dept d
WHERE s.facnumb = f.facnumb AND f.deptnumb = d.deptnumb;
3.SELECT S.STUNUMB,
s.stuname,
s.facnumb,
f.facname,
f.deptnumb,
d.deptname,
sd.capacity
FROM student s,
faculty f,
dept d,
studept sd
WHERE s.facnumb = f.facnumb
AND f.deptnumb = d.deptnumb
AND sd.stunumb = s.stunumb
AND SD.DEPTNUMB = d.deptnumb;
4.SELECT * FROM course;
5.SELECT s.stunumb,
s.stuname,
h.hscode,
hsname
FROM student s, highschl h
WHERE s.hscode = h.hscode AND UPPER (hsname) LIKE '%MARVEL%';
6.SELECT f.facnumb,
f.facname,
d.deptnumb,
d.deptname
FROM dept d, faculty f
WHERE d.deptnumb = f.deptnumb;
7.SELECT S.STUNUMB,
s.stuname,
s.facnumb,
f.facname,
f.deptnumb,
d.deptname,
S.GPA
FROM student s, faculty f, dept d
WHERE s.facnumb = f.facnumb AND f.deptnumb = d.deptnumb;
8.SELECT s.stunumb,
s.stuname,
s.gpa,
f.facnumb,
F.FACNAME
FROM faculty f, student s
WHERE s.facnumb = f.facnumb;
9.SELECT C.CRSECODE,
C.CRSEDESC,
C.NUMCRED,
F.FACNAME,
F.FACNUMB
FROM faculty f, course c, faccrse fc
WHERE C.CRSECODE = FC.CRSECODE AND FC.FACNUMB = F.FACNUMB;
10.SELECT S.STUNUMB,
S.STUNAME,
SD.CAPACITY,
SD.DEPTNUMB,
D.DEPTNAME,
F.FACNAME,
H.HSCODE,
H.HSNAME,
C.CRSECODE,
C.CRSEDESC,
C.NUMCRED,
SC.GRADE
FROM student s,
studept sd,
dept d,
faculty f,
highschl h,
course c,
faccrse fc,
stucrse sc
WHERE S.STUNUMB = SD.STUNUMB
AND SD.DEPTNUMB = D.DEPTNUMB
AND F.DEPTNUMB = D.DEPTNUMB
AND S.HSCODE = H.HSCODE
AND F.FACNUMB = FC.FACNUMB
AND C.CRSECODE = FC.CRSECODE
AND FC.CRSECODE = SC.CRSECODE;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.