Make sure all tables resulting from your design are in Boyce-Codd Normal Form. W

Question

Make sure all tables resulting from your design are in Boyce-Codd Normal Form. Write CREATE TABLE statements for each of the tables. Use FOREIGN KEY constraints to create appropriate referential integrity constraints. Set ON DELETE [CASCADE SET NULL] behavior in accordance with your defined referential integrity actions. Note that Oracle does not support ON UPDATE [NO ACTION | CASCADE] The feeling at Oracle is that Primary keys should never be changed. They are supposed to be immutable. Write INSERT statements to insert at least three rows into each table. Create a view that hides columns to simplify results or to prevent the display of sensitive data. Create a view that hides complicated SQL syntax. Create a view that could be used to limit read access to specific rows and columns

Explanation / Answer

Q1) Insert data into the Table

INSERT INTO RECOGNITION VALUES ('RECOGNITION1', 'REASON1');

INSERT INTO RECOGNITION VALUES ('RECOGNITION2', 'REASON2');

INSERT INTO RECOGNITION VALUES ('RECOGNITION3', 'REASON3');

INSERT INTO DEGREE VALUES ('B-TECH', 'BACHELOR OF TECHNOLOGY');

INSERT INTO DEGREE VALUES ('M-TECH', 'MASTER OF TECHNOLOGY');

INSERT INTO DEGREE VALUES ('BSC', 'BACHELOR OF SCIENCE');

INSERT INTO EMPLOYER VALUES (12345, 'ADDRESS1', 'GOPAL');

INSERT INTO EMPLOYER VALUES (54321, 'ADDRESS2', 'BROWN');

INSERT INTO EMPLOYER VALUES (112233, 'ADDRESS3', 'JACK SPARROW');

Q2) Create a view to display only the sensitive data

CREATE VIEW ALUMINI_RECOGNITION AS SELECT alumID, firstName, lastName, recogID FROM ALUMNI;

CREATE VIEW DONOR_VIEW AS SELECT donorID, donorLastName, donorFirstName, donationType FROM DONOR;

CREATE VIEW eventID, eventName, eventDate, eventTime FROM EVENTS;

Q3) Create a view that hides Complicated SQL Query

CREATE VIEW ALUMINI_RECOGNITION AS SELECT alumID, firstName, lastName, recogID FROM ALUMNI JOIN RECOGNITION ON ALUMINI.recogID = RECOGNITION.recogID;

CREATE VIEW COMMITTEES_VIEW AS SELECT CommitteeID, CommitteeMemberID, CommitteePosition FROM

COMMITTEES JOIN DONOR ON COMMITTEES.DONORID = DONOR.donorID

JOIN RECOGNITION ON DONOR.recogID = RECOGNITION.recogID;

Q4) Create view to limit read access to specific rows and columns

CREATE VIEW COMMITTEES_VIEW AS SELECT CommitteeID, CommitteeMemberID, CommitteePosition FROM

COMMITTEES JOIN DONOR ON COMMITTEES.DONORID = DONOR.donorID

JOIN RECOGNITION ON DONOR.recogID = RECOGNITION.recogID

where RECOGNITION.recogID = 'RECOGNITION1';

CREATE VIEW ALUMINI_RECOGNITION AS SELECT alumID, firstName, lastName, recogID FROM ALUMNI JOIN RECOGNITION ON ALUMINI.recogID = RECOGNITION.recogID where RECOGNITION.recogID = 'RECOGNITION1';

Please let me know in case of any clarifications required. Thanks!

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

---

---

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