Crazy
chicken organization provides dinning services to a major university. It offers
various MENUS for different EVENTS. Attributes of menus are M-ID (identifier),
M-Type, M-Discp, and attributes of events are E-ID (identifier), E-Date,
E-Location and E-Time. Each menu contain number of DISHES, attributes of dishes
are D-ID (identifier), D-Name and Prep-Time. Different staff members work i.e.
Start-Time, End-Time, and Position on events and STAFF supervises different
staff. Attributes of staff are S-ID (identifier), S-Name, and S-salary.
Entity Relationship Diagram (ERD)
Relation Model
MENU:
|
M-ID
|
M-Type
|
M-Discp
|
D-ID
|
E-ID
|
DISHES:
|
D-ID
|
D-Name
|
Prep-Time
|
M-ID
|
EVENT:
|
E-ID
|
E-Date
|
E-Location
|
E-Time
|
M-ID
|
WORK:
|
W.Start-Time
|
W.End-Time
|
W.
Position
|
E-ID
|
S-ID
|
STAFF:
|
S-ID
|
S-Name
|
S-Salary
|
W-ID
|
Physical model
CREATE TABLE MENU
(
M-ID Number NOT NULL UNIQUE;
M-Type VARCHAR
(20) NOT NULL;
M-Discp VARCHAR
(25) NOT NULL;
D-ID Number NOT NULL;
E-ID Number NOT NULL;
CONSTRAINT MENU-PK PRIMARY KEY (M-ID)
CONSTRAINT MENU-FK FOREIGN KEY (D-ID, E-ID)
REFERENCE DISHES (D-ID), EVENTS (E-ID));
CREATE TABLE DISHES
(
D-ID Number NOT NULL UNIQUE;
D-Name VARCHAR
(20) NOT NULL;
Prep-Time VARCHAR
(25) NOT NULL;
M-ID Number NOT NULL;
CONSTRAINT DISHES-PK PRIMARY KEY (D-ID)
CONSTRAINT MENU-FK FOREIGN KEY (M-ID)
REFERENCE DISHES (M-ID));
CREATE TABLE EVENT
(
E-ID Number NOT NULL;
E-Date Date/Time NOT NULL;
E-Location varchar(25) NOT NULL;
E-Time Date/Time NOT NULL;
M-ID Number NOT NULL;
S-ID Number NOT NULL;
CONSTRAINT EVENT-PK PRIMARY KEY (E-ID)
CONSTRAINT EVENT-FK FORIGN KEY (S-ID, M-ID)
REFERENCE MENU (M-ID), STAFF(S-ID));
CREATE TABLE WORK
(
Start-time Date/Time NOT NULL;
End-time Date/Time NOT NULL;
Position Text NOT NULL;
E-ID Number NOT NULL;
S-ID Number NOT NULL;
CONSTRAINT WORK-PK PRIMARY KEY (
CONSTRAINT WORK-FK FOREIGN KEY (E-ID, S-ID)
REFERENCE EVENT (E-ID), STAFF(S-ID));
CREATE TABLE STAFF
(
S-ID Number UNIQUE;
S-Name VARCHAR
(20) NOT NULL;
S-Salary Number NOT NULL;
E-ID Number NOT NULL;
W-ID Number NOT NULL;
CONSTRAINT STAFF-PK PRIMARY KEY(S-ID)
CONSTRAINT STAFF-FK FORIGN KEY (E-ID)
REFERENCE EVENT (E-ID));
Processing DML
INSERT INTO MENU (M-ID,
M-Type, M-Descp)
VALUES
(0201, chicken, chicken kabab);
INSERT INTO MENU (M-ID, M-Type, M-Descp)
VALUES
(1045, mutton, mutton karai);
INSERT INTO MENU (M-ID, M-Type,M-Decp)
VALUE
(1439, Beef, Beef handi);
INSERT INTO DISHES
(D-ID, D-Name, Prep-time)
VALUE
(0045, Chicken tika, 1:00 hour);
INSERT INTO DISHES (D-ID,
D-Name, Prep-time)
VALUE
(0032, Mix Vegetable, 00:30 min);
INSERT INTO DISHES (D-ID, D-Name, Prep-time)
VALUE
(015, Red Chicken, 01:30 hour);
INSERT INTO EVENT
(E-ID, E-Date, E-Location, E-Time)
VALUE (0478,
28/10/2011, Lahore, 07:00pm);
INSERT INTO EVENT (E-ID, E-Date, E-Location, E-Time)
VALUE
(0178, 18/11/2011, Lahore, 06:30pm);
INSERT INTO EVENT (E-ID, E-Date, E-Location, E-Time)
VALUE
(078, 08/10/2011, Lahore, 06:00pm);
INSERT INTO WORK (W.Start-time,
w.End-time, w.position)
VALUE (04:00pm,
10:00pm, Manger);
INSERT INTO WORK (W.Start-time,
w.End-time, w.position)
VALUE
(05:00pm, 09:00pm, Assistant);
INSERT INTO WORK (W.Start-time,
w.End-time, w.position)
VALUE
(03:00pm, 11:00pm, waiter)
INSERT INTO STAFF (S-ID,
S-Name, S-salary)
VALUE (785,
Management, 50,000);
INSERT INTO STAFF (S-ID, S-Name, S-salary)
VALUE (489,
Human resource, 30,000);
INSERT INTO STAFF (S-ID, S-Name, S-salary)
VALUE
(751, Accountant, 50,000);
