воотн P. BOOTHCODE * RESTAURANT_ID VARCHAR2 (10) VARCHAR2 (30) SMOKE_SECTION_INDICATOR CHAR (1) NUMBER (2) BILL воотн_CAPACTY P* BILL ID * BOOTHCODE F DESSERT CODE • DESSERT OFFERED_DATE_RANGE F...



  • You are asked to normalize the database shown in ER diagram below (Figure1) up to at least 3NF.



  • Hand In:







    • A complete set of functional dependencies identified




воотн<br>P. BOOTHCODE<br>* RESTAURANT_ID<br>VARCHAR2 (10)<br>VARCHAR2 (30)<br>SMOKE_SECTION_INDICATOR<br>CHAR (1)<br>NUMBER (2)<br>BILL<br>воотн_CAPACTY<br>P* BILL ID<br>* BOOTHCODE<br>F DESSERT CODE<br>• DESSERT OFFERED_DATE_RANGE<br>F RESTAURANT_ID<br>VARCHAR2 (10)<br>BOOTHTYP_CODE<br>CHAR (1)<br>VARCHAR2 (100)<br>F<br>VARCHAR2 (10)<br>BOOTHTYP_TEXT<br>VARCHAR2 (10)<br>VARCHAR2 (40)<br>- PK_BOOTH (BOOTHCODE)<br>DESSERT<br>VARCHAR2 (30)<br>DATE<br>* DESSERT_CODE<br>* DESSERT_OFFERED DATE_RANGE<br>VARCHAR2 (10)<br>BILL_DATE_AND_TIME<br>VARCHAR2 (40)<br>VARCHAR2 (25)<br>BILL_AMOUNT<br>NUMBER (6,2)<br>NUMBER (6,2)<br>DESSERT_NAME<br>GRATUITY_AMOUNT<br>DESSERT DESCR<br>VARCHAR2 (150)<br>PK_BILL (BILL_ID)<br>DESSERT_CATEGORY_CODE<br>DRINK_NAME<br>VARCHAR2 (50)<br>VARCHAR2 (25)<br>TOPPNING NAME<br>VARCHAR2 (20)<br>DESSERT_PRICE_AMOUNT<br>NUMBER (6,2)<br>- PK_DESSERT (DESSERT_CODE, DESSERT_OFFERED_DATE_RANGE)<br>RESTAURANT<br>P• RESTAURANT_ID<br>VARCHAR2 (30)<br>RESTAURANT_OWNER1_NAME<br>VARCHAR2 (30)<br>RESTAURANT_OWNER2_NAME<br>VARCHAR2 (30)<br>OFFER<br>VARCHAR2 (30)<br>PF DESSERT_CODE<br>PF DESSERT_OFFERED_DATE_RANGE<br>PF RESTAURANT_ID<br>- PK_OFFER (DESSERT_CODE, DESSERT_OFFERED_DATE_RANGE, RESTAURANT_ID)<br>RESTAURANT_OWNER3_NAME<br>VARCHAR2 (10)<br>RESTAURANT REGION_CODE<br>VARCHAR2 (10)<br>VARCHAR2 (40)<br>RESTAURANT_REGION_NAME<br>VARCHAR2 (30)<br>VARCHAR2 (30)<br>RESTAURANT_MAX_CAPACITY<br>NUMBER (4)<br>PK_RESTAURANT (RESTAURANT_ID)<br>

Extracted text: воотн P. BOOTHCODE * RESTAURANT_ID VARCHAR2 (10) VARCHAR2 (30) SMOKE_SECTION_INDICATOR CHAR (1) NUMBER (2) BILL воотн_CAPACTY P* BILL ID * BOOTHCODE F DESSERT CODE • DESSERT OFFERED_DATE_RANGE F RESTAURANT_ID VARCHAR2 (10) BOOTHTYP_CODE CHAR (1) VARCHAR2 (100) F VARCHAR2 (10) BOOTHTYP_TEXT VARCHAR2 (10) VARCHAR2 (40) - PK_BOOTH (BOOTHCODE) DESSERT VARCHAR2 (30) DATE * DESSERT_CODE * DESSERT_OFFERED DATE_RANGE VARCHAR2 (10) BILL_DATE_AND_TIME VARCHAR2 (40) VARCHAR2 (25) BILL_AMOUNT NUMBER (6,2) NUMBER (6,2) DESSERT_NAME GRATUITY_AMOUNT DESSERT DESCR VARCHAR2 (150) PK_BILL (BILL_ID) DESSERT_CATEGORY_CODE DRINK_NAME VARCHAR2 (50) VARCHAR2 (25) TOPPNING NAME VARCHAR2 (20) DESSERT_PRICE_AMOUNT NUMBER (6,2) - PK_DESSERT (DESSERT_CODE, DESSERT_OFFERED_DATE_RANGE) RESTAURANT P• RESTAURANT_ID VARCHAR2 (30) RESTAURANT_OWNER1_NAME VARCHAR2 (30) RESTAURANT_OWNER2_NAME VARCHAR2 (30) OFFER VARCHAR2 (30) PF DESSERT_CODE PF DESSERT_OFFERED_DATE_RANGE PF RESTAURANT_ID - PK_OFFER (DESSERT_CODE, DESSERT_OFFERED_DATE_RANGE, RESTAURANT_ID) RESTAURANT_OWNER3_NAME VARCHAR2 (10) RESTAURANT REGION_CODE VARCHAR2 (10) VARCHAR2 (40) RESTAURANT_REGION_NAME VARCHAR2 (30) VARCHAR2 (30) RESTAURANT_MAX_CAPACITY NUMBER (4) PK_RESTAURANT (RESTAURANT_ID)
mysql> select * from Bill;<br>| BILL ID | BOOTHCODE | |<br>DESSERT CODE I DESSERT OFFERED DATE RANGE<br>RESTAURANT ID | BILL DATE AND TIME<br>| BILL AMOUNT | GRATUITY AMOUNT |<br>1 воотн АВ<br>| BOOTH_AE | EE<br>| BOOTH_AB<br>| BOOTH_AD<br>| BOOTH BC | AB<br>| 1999-10-1 - 1999-10-31<br>| 2001-6-1 - 2001-9-1<br>| 1999-10-1 - 1999-10-31<br>| 1996-1-1 - 2002-12-31<br>| 1999-10-1 -1999-10-31<br>320 |<br>| 20<br>| 27<br>| 29<br>| 77<br>| AB<br>| 454<br>| 789<br>| 454<br>| 789<br>| 454<br>| 1999-10-12 20:34:59 |<br>| 2000-10-21 15:30:12 |<br>| 1999-10-12 12:34:00 |<br>| 2000-10-15 20:15:19 |<br>| 1999-10-10 22:25:09 |<br>20 I<br>0.5 I<br>30 I<br>80 I<br>400.5 |<br>330 I<br>| AB<br>| BC<br>1500.99 |<br>310.5 I<br>10.5 |<br>mysql> select from Offer;<br>| DESSERT_CODE | DESSERT_OFFERED_DATE_RANGE | RESTAURANT_ID |<br>| AB<br>| BC<br>| EE<br>| 1999-10-1 - 1999-10-31<br>| 1996-1-1 - 2002-12-31<br>| 2001-6-1 - 2001-9-1<br>| 454<br>| 789<br>I 789<br>mysql> select<br>* from Booth;<br>| BOOTHCODE | RESTAURANT ID I SMOKE SECTION INDICATOR I BOOTH CAPACITY | BOOTHTYP CODE | BOOTHTYP TEXT |<br>| BOOTH_AB | 123<br>| BOOTH AD | 125<br>| BOOTH AE<br>4 | T<br>2 | T<br>8 | T<br>6 | E<br>| Table<br>| Table<br>| Table<br>| Bench<br>I N<br>| 124<br>| BOOTH BC<br>воотH вс I 123<br>

Extracted text: mysql> select * from Bill; | BILL ID | BOOTHCODE | | DESSERT CODE I DESSERT OFFERED DATE RANGE RESTAURANT ID | BILL DATE AND TIME | BILL AMOUNT | GRATUITY AMOUNT | 1 воотн АВ | BOOTH_AE | EE | BOOTH_AB | BOOTH_AD | BOOTH BC | AB | 1999-10-1 - 1999-10-31 | 2001-6-1 - 2001-9-1 | 1999-10-1 - 1999-10-31 | 1996-1-1 - 2002-12-31 | 1999-10-1 -1999-10-31 320 | | 20 | 27 | 29 | 77 | AB | 454 | 789 | 454 | 789 | 454 | 1999-10-12 20:34:59 | | 2000-10-21 15:30:12 | | 1999-10-12 12:34:00 | | 2000-10-15 20:15:19 | | 1999-10-10 22:25:09 | 20 I 0.5 I 30 I 80 I 400.5 | 330 I | AB | BC 1500.99 | 310.5 I 10.5 | mysql> select from Offer; | DESSERT_CODE | DESSERT_OFFERED_DATE_RANGE | RESTAURANT_ID | | AB | BC | EE | 1999-10-1 - 1999-10-31 | 1996-1-1 - 2002-12-31 | 2001-6-1 - 2001-9-1 | 454 | 789 I 789 mysql> select * from Booth; | BOOTHCODE | RESTAURANT ID I SMOKE SECTION INDICATOR I BOOTH CAPACITY | BOOTHTYP CODE | BOOTHTYP TEXT | | BOOTH_AB | 123 | BOOTH AD | 125 | BOOTH AE 4 | T 2 | T 8 | T 6 | E | Table | Table | Table | Bench I N | 124 | BOOTH BC воотH вс I 123
Jun 07, 2022
SOLUTION.PDF
SOLUTION.PDF

Get Answer To This Question

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here
Have files?