Database systems 2. (60 POINTS) Following is a database state for a University d

Question

Database systems

2. (60 POINTS) Following is a database state for a University database: STUDENT StdSSN StdFName StdLName StdCityStdState StdZip StdMajor StdYearStdGPA 123456791 Hanes Wilson Savon OK 68123-1113 IT Freshman 3.1 124567892 Blake 234567892 Claire Keller Thomasville OK 69044-3323 ART 345678903 Wayne Keller Saon O 6 Sern Nuke OK 68013-2123 FIL unior Junior 68125-1143 IT 68123-2335 FIL 68116-0023 IT 68118-2346 ART Eckart Savon 567890125 Maggie Diamond Savon OK unior Sophomore unior 789012347 Richard MooreSavon 876543212 Chris Coleman Savon OK 6 890123458 Lance Brave Sav 901234569 Winston Pale Senior 68116-1334IT 98118-0023 IT OK Senior ??) Burwick OK 98115-1887 IT

Explanation / Answer

If you have any doubts, please give me comment...

CREATE TABLE STUDENT(

StdSSN INT NOT NULL PRIMARY KEY,

StdFName VARCHAR(50),

StdLName VARCHAR(50),

StdCity VARCHAR(50),

StdState CHAR(2),

StdZip VARCHAR(10),

StdMajor VARCHAR(15),

StdYear VARCHAR(20),

StdYear DOUBLE,

);

CREATE TABLE FACULTY(

FacSSN INTEGER NOT NULL PRIMARY KEY,

FacFName VARCHAR(50),

FacLName VARCHAR(50),

FacCity VARCHAR(30),

FacState CHAR(2),

FacZip VARCHAR(10),

FacStartDate DATE,

FacDept VARCHAR(10),

FacRank VARCHAR(10),

FacSalary INTEGER,

FacSupervisor INTEGER

);

CREATE TABLE OFFERING(

OfferNo INTEGER NOT NULL PRIMARY KEY,

CourseNo VARCHAR(10),

OfferTerm VARCHAR(5),

OfferYear INTEGER,

OfferLocation CHAR(7),

OfferTime TIME,

OfferDays VARCHAR(10),

FacSSN INTEGER

);

CREATE TABLE COURSE(

CourseNo INTEGER NOT NULL PRIMARY KEY,

CourseDescription VARCHAR(50),

CourseCredit INTEGER

);

CREATE TABLE ENROLLMENT(

OfferNo INTEGER,

StdSSN INTEGER,

Grade DOUBLE

);

INSERT INTO STUDENT VALUES(123456791, 'Hanes', 'Savon', 'OK', '68123-1113', 'IT', 'Freshman', 3.10);

INSERT INTO FACULTY VALUES(09765434, 'Liam', 'Victor', 'Savon', 'OK', '681139923', '10-Apr-2008', 'MATH', 'ASST', 65000, 654321100);

INSERT INTO OFFERING VALUES(1121, 'IT330', 'Sum', 2013, 'AMPH312', '11:30 AM', 'MW', '');

INSERT INTO COURSE VALUES('FIL310', 'Introduction to Philology', 3);

INSERT INTO ENROLLMENT VALUES(1244, 123456791, 3.30);

SELECT * FROM STUDENT;

SELECT * FROM FACULTY;

SELECT * FROM OFFERING;

SELECT * FROM COURSE;

SELECT * FROM ENROLLEMENT;

ALTER TABLE OFFERING ADD FOREIGN KEY(FacSSN) REFERENCES FACULTY(FacSSN);

ALTER TABLE OFFERING ADD FOREIGN KEY(CourseNo) REFERENCES COURSE(CourseNo);

ALTER TABLE ENROLLMENT ADD FOREIGN KEY(OfferNo) REFERENCES OFFERING(OfferNo)ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ENROLLMENT ADD FOREIGN KEY(StdSSN) REFERENCES STUDENT(StdSSN);

ALTER TABLE FACULTY ADD FOREIGN KEY(FacSupervisor) REFERENCES FACULTY(FacSSN);

SELECT FacFName, FacLname, FacStartDate, (FacSalary+FacSalary*0.20) AS newSalary

FROM FACULTY

WHERE YEAR(FacStartDate)>2009;

SELECT OfferNo, CourseNo

FROM OFFERING

WHERE FacSSN IS NULL AND OfferTerm ='Sum' AND OfferYear = 2013;

SELECT StdMajor, AVG(StdGPA)

FROM STUDENT

GROUP BY StdMajor;

SELECT StdFName, StdLName, GPA

FROM STUDENT

WHERE StdYear = 'Senior'

ORDER BY GPA ASC;

SELECT FacFName, FacLName, OfferNo, CourseNo

FROM FACULTY F, OFFERING O

WHERE F.FacSSN = O.FacSSN AND FacRank = 'ASST' AND OfferYear='2012' AND OfferTerm = 'Fall' AND CourseNo LIKE 'IT%';

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.