When the table is reduced into three BCNF tables, CAMPUS_CUISINE , CAMPUS_PLACE

Question

When the table is reduced into three BCNF tables, CAMPUS_CUISINE, CAMPUS_PLACE and PLACE_CUISINE, write a stored procedure ADD_PLACE(camp,cuis,plac,@msg) that will

a) add a place to the BCNF tables only if the place can be inserted into CAMPUS_CUISINE and CAMPUS_PLACE successfully

b) if the place already exists in the PLACE_CUISINE table, the cuisine is the same, or the place can be added successfully into PLACE_CUISINE.

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

Follwing will be the structure of table after normalization

CAMPUS_PLACE

CampusID

CampusName

CuisineID

CAMPUS_CUISINE

CuisineID

Cuisine

PlaceID

PLACE_CUISINE

PlaceID

PlaceName

create table PLACE_CUISINE
(PlaceID Integer Primary Key,
PlaceName varchar(20));

Create Table CAMPUS_CUISINE
(CuisineID Integer primary key,
Cuisine Varchar(20),
PlaceID Integer references PLACE_CUISINE(PlaceID));

Create Table CAMPUS_PLACE
(CampusID Integer Primary Key,
CampusName Varchar(20),
CuisineID Integer references CAMPUS_CUISINE(CuisineID),
);

create or replace PROCEDURE add_place(

camp In varchar,

cuis In varchar,

plac In varchar,

msg In varchar)

As p number;

c number;

campus number;

BEGIN

select placeId into p from PLACE_CUISINE where placeId = p;

if p is not null then

select count(*) into p from place_cuisine;

select count(*) into campus from CAMPUS_PLACE;

select count(*) into c from CAMPUS_CUISINE;

p:=p+1;

campus:=campus+1;

c:=c+1;

insert into place_cuisine values(p,plac);

insert into campus_place values(campus,camp,c);

insert into campus_cuisine values(c,cuis,p);

commit;

end if;

END;

/

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

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