The table holds the recommended closest place by cuisine depending to a campus.

Question

The table holds the recommended closest place by cuisine depending to a campus.

CREATE TABLE CAMPUS_CUISINE_PLACE(

CUISINE varchar(20) NOT NULL,

CAMPUS varchar(10) NOT NULL,

PLACE varchar(50) NOT NULL,

PRIMARY KEY (CAMPUS,CUISINE)

)

write the CREATE, INDEX, CONSTRAINT and INSERT SELECT statements (in a valid order) to create three populated BCNF tables from the above table.

write the statement that creates a view of the original table using your BCNF tables.

  

CREATE TABLE CAMPUS_CUISINE_PLACE(

CUISINE varchar(20) NOT NULL,

CAMPUS varchar(10) NOT NULL,

PLACE varchar(50) NOT NULL,

PRIMARY KEY (CAMPUS,CUISINE)

)

write the CREATE, INDEX, CONSTRAINT and INSERT SELECT statements (in a valid order) to create three populated BCNF tables from the above table.

write the statement that creates a view of the original table using your BCNF tables.

CAMPUS CUISINE PLACE UHM Burgers Burger King Manoa UHMPiz Boston's Pizza Manoa UHM Afgan Afganistan Love KCC Burgers McDonald's Kahala KCCChinese Lam's Garden on Waialae KCC Afgan Afganistan Love

Explanation / Answer

For creating index, you would need to mention the columns for which the index need to be created. For now, I am creating index of all the columns as shown below.

CREATE INDEX CAMPUS_CUISINE_PLACE_INDEX
ON CAMPUS_CUISINE_PLACE (CUISINE, CAMPUS, PLACE);

For contraint creation, kindly specify the constraint you need. For time being, I am adding a unique constraint for column CAMPUS.

ALTER TABLE CAMPUS_CUISINE_PLACE
ADD UNIQUE (CAMPUS);

Below are the insert queries:

INSERT INTO CAMPUS_CUISINE_PLACE VALUES ('UHM', 'Burgers', 'Burger King Manoa');

INSERT INTO CAMPUS_CUISINE_PLACE VALUES ('UHM', 'Pizza', 'Boston's Pizza Manoa');

INSERT INTO CAMPUS_CUISINE_PLACE VALUES ('UHM', 'Afgan', 'Afganistan Love');

INSERT INTO CAMPUS_CUISINE_PLACE VALUES ('KCC', 'Burgers', 'McDonald's Kahala');

INSERT INTO CAMPUS_CUISINE_PLACE VALUES ('KCC', 'Chinese', 'Lams' Garden on Waialai');

INSERT INTO CAMPUS_CUISINE_PLACE VALUES ('KCC', 'Afgan', 'Afganistan Love');

VIEW Command

==================

CREATE VIEW CAMPUS_CUISINE_PLACE_VIEW AS SELECT * FROM CAMPUS_CUISINE_PLACE;

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

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