Question

PROBLEM STATEMENT: Suppose you have a client that has given you the following business rules to form the basis for a database design. The database must enable the manager of a company dinner club to mail invitations to the club’s members, to plan the meals, to keep track of who attends the dinners, and so on. Each dinner serves many members, and each member may attend many dinners. A member receives many invitations, and each invitation is mailed to many members. A dinner is based on a single entrée, but an entrée may be used as the basis for many dinners. For example, a dinner may be composed of a fish entrée, rice, and corn. Or the dinner may be composed of a fish entrée, a baked potato, and string beans. Because the manager is not a database expert, their first attempt at creating the database uses the structure shown below:

Attribute Name MEMBER NUM MEMBER NAME MEMBER_ADDRESS MEMBER CITY MEMBER ZIPCODE INVITE NUM INVITE DATE ACCEPT DATE DINNER DATE DINNER ATTENDED DINNER CODE DINNER DESCRIPTION ENTRÉE CODE ENTRÉE DESCRIPTION DESSERT CODE DESSERT DESCRIPTION Sample Value 214 Alice B. Vandervoort 325 Meadow Park Murkywater 12345 8 23-Feb-2016 27-Feb-2016 15-Mar-2016 Yes DI5 Glowing sea delight EN3 Stuffed crab DE8 Chocolate mousse with raspberry sauce Sample Value 235 Gerald M. Gallega 123 Rose Court Highlight 12349 9 12-Mar-2016 15-Mar-2016 17-Mar-2016 Yes D15 Glowing sea delight EN4 Roasted duck DE5 Cherries jubilee Sample Value 214 Alice B. Vandervoort 325 Meadow Park Murkywater 12345 10 23-Feb-2016 27-Feb-2016 15-Mar-2016 No DI2 Ranch Superb EN5 Marinated Steak DE2 Apple pie with honey crust

1. ven the table structure illustrated above, illustrate the normalization process. Start by drawing its initial dependency diagram and labeling all possible dependencies. In each step, include the relational schema under the diagram. Make sure that the result is in at least 3NF. It is recommended that you try to improve the design and add/ remove attributes as you see fit while meeting proper design criteria. Include a short explanation for the improvements done.

2. Using the results in the first item, create the Crow’s Foot ERD for the problem.

3.Write the SQL code to create the table structures for the entities created in the lecture part of the project. The structures should contain the attributes specified in the ERD. Use data types that are appropriate for the data that will need to be stored in each attribute. Enforce primary key and foreign key constraints as indicated by the ERD. In addition, write the SQL code that will insert at least 10 records for each of the tables. Your entire SQL script should run without errors.

4. Create 2 views, 1 trigger, and 2 stored procedures based on what might be some usual business transactions that your client encounter. For example, a student enrollment database system might have the following transactions: adding a new student record, updating payment status, computing total tuition, etc. Include a brief explanation of their purposes (views, trigger, stored procedures) and/or the associated business transaction as comments in the script.

NOTE: ANSWER ONLY 3 AND 4 BASED ON THE ANSWERS FROM 1 AND 2.

HERE'S NUMBERS 1 AND 2:

The prices were added to DINNER, ENTRÉE, and DESSERT to improve the structure of the tables. To provide space for all attributes, MEMBER, ENTRÉE, DESSERT, and DINNER are shortened above to fit the attributes.

In the Crow’s Foot ERD, Each member gets invitations therefore it is a one to many relationship, one dinner can have many invitations and each invitation is sent to many members, and the same entrée and dessert can be included in many dinners.

Answer 1

3. SQL code to create tables is as follows -

MEMBER Table

CREATE TABLE MEMBER (

MEM_NUM int NOT NULL,

MEM_NAME varchar(255) NOT NULL,

MEM_ADDRESS varchar(255) NOT NULL,

ZIP_CODE int NOT NULL,

PRIMARY KEY (MEM_NUM),

FOREIGN KEY (ZIP_CODE) REFERENCES ZIP(ZIP_CODE)

);

ZIP Table

CREATE TABLE ZIP (

ZIP_CODE int NOT NULL,

ZIP_CITY varchar(50) NOT NULL,

PRIMARY KEY (ZIP_CODE)

);

INVITATION Table

CREATE TABLE INVITATION(

INVITE_NUM int NOT NULL,

INVITE_DATE Date NOT NULL,

INVITE_ACCEPT varchar NOT NULL,

INVITE_ATTEND varchar NOT NULL,

PRIMARY KEY (INVITE_NUM),

FOREIGN KEY (DIN_CODE) REFERENCES DINNER(DIN_CODE),

FOREIGN KEY (MEM_NUM) REFERENCES MEMBER(MEM_NUM)

);

DINNER Table

CREATE TABLE DINNER (

DIN_CODE nchar NOT NULL,

DIN_DESC varchar(50) NOT NULL,

DIN_PRICE money NOT NULL,

PRIMARY KEY (DIN_CODE),

FOREIGN KEY (ENT_CODE) REFERENCES ENTREE(ENT_CODE),

FOREIGN KEY (DES_CODE) REFERENCES DESSERT(DES_CODE)

);

ENTREE Table

CREATE TABLE ENTREE(

ENT_CODE nchar NOT NULL,

ENT_DESC varchar(50) NOT NULL,

ENT_PRICE money NOT NULL,

PRIMARY KEY (ENT_CODE)

);

DESSERT Table

CREATE TABLE DESSERT(

DES_CODE nchar NOT NULL,

DES_DESC varchar(50) NOT NULL,

DES_PRICE money NOT NULL,

PRIMARY KEY (DES_CODE)

);

Inserting data to table

MEMBER Table

INSERT INTO MEMBER(MEM_NUM,MEM_NAME,MEM_ADDRESS,ZIP_CODE) VALUES('214','Alice','325 Meadow Park','12345');

INSERT INTO MEMBER(MEM_NUM,MEM_NAME,MEM_ADDRESS,ZIP_CODE) VALUES('215','Ram','326 Meadow Park','12346');

INSERT INTO MEMBER(MEM_NUM,MEM_NAME,MEM_ADDRESS,ZIP_CODE) VALUES('216','Gerald M. Gallega','123 Rose Court','12349');

INSERT INTO MEMBER(MEM_NUM,MEM_NAME,MEM_ADDRESS,ZIP_CODE) VALUES('213','Tony','327 Meadow Park','12347');

INSERT INTO MEMBER(MEM_NUM,MEM_NAME,MEM_ADDRESS,ZIP_CODE) VALUES('210','Bob','124 Rose court','12343');

INSERT INTO MEMBER(MEM_NUM,MEM_NAME,MEM_ADDRESS,ZIP_CODE) VALUES('210','Bob','310 Meadow Park','12343');

INSERT INTO MEMBER(MEM_NUM,MEM_NAME,MEM_ADDRESS,ZIP_CODE) VALUES('209','Alan','300 Meadow Park','12330');

INSERT INTO MEMBER(MEM_NUM,MEM_NAME,MEM_ADDRESS,ZIP_CODE) VALUES('209','Alan','300  Meadow Park','12330');

INSERT INTO MEMBER(MEM_NUM,MEM_NAME,MEM_ADDRESS,ZIP_CODE) VALUES('207','Rahim','110 Meadow Park','12300');

INSERT INTO MEMBER(MEM_NUM,MEM_NAME,MEM_ADDRESS,ZIP_CODE) VALUES('205','Mary','100 Meadow Park','12290');

ZIP Table

INSERT INTO ZIP(ZIP_CODE,ZIP_CITY) VALUES('12345','Murkywater');

INSERT INTO ZIP(ZIP_CODE,ZIP_CITY) VALUES('12346','Highlight');

INSERT INTO ZIP(ZIP_CODE,ZIP_CITY) VALUES('12349','Highlight');

INSERT INTO ZIP(ZIP_CODE,ZIP_CITY) VALUES('12347','Murkywater');

INSERT INTO ZIP(ZIP_CODE,ZIP_CITY) VALUES('12343','Murkywater');

INSERT INTO ZIP(ZIP_CODE,ZIP_CITY) VALUES('12343','Murkywater');

INSERT INTO ZIP(ZIP_CODE,ZIP_CITY) VALUES('12330','Murkywater');

INSERT INTO ZIP(ZIP_CODE,ZIP_CITY) VALUES('12330','Murkywater');

INSERT INTO ZIP(ZIP_CODE,ZIP_CITY) VALUES('12300','Murkywater');

INSERT INTO ZIP(ZIP_CODE,ZIP_CITY) VALUES('12290','Murkywater');

INVITATION Table

INSERT INTO INVITATION (INVITE_NUM,INVITE_DATE,INVITE_ACCEPT,INVITE_ATTEND,DIN_CODE,MEM_NUM) VALUES ('8','23-Feb-2016','Yes','Yes','DI5','214');

INSERT INTO INVITATION (INVITE_NUM,INVITE_DATE,INVITE_ACCEPT,INVITE_ATTEND,DIN_CODE,MEM_NUM) VALUES ('9','12-Feb-2016','No','No','DI5','215');

INSERT INTO INVITATION (INVITE_NUM,INVITE_DATE,INVITE_ACCEPT,INVITE_ATTEND,DIN_CODE,MEM_NUM) VALUES ('10','23-Feb-2016','Yes','Yes','DI5','216');

INSERT INTO INVITATION (INVITE_NUM,INVITE_DATE,INVITE_ACCEPT,INVITE_ATTEND,DIN_CODE,MEM_NUM) VALUES ('11','20-Mar-2016','No','No','DI2','213');

INSERT INTO INVITATION (INVITE_NUM,INVITE_DATE,INVITE_ACCEPT,INVITE_ATTEND,DIN_CODE,MEM_NUM) VALUES ('8','23-Feb-2016','Yes','Yes','DI5','210');

INSERT INTO INVITATION (INVITE_NUM,INVITE_DATE,INVITE_ACCEPT,INVITE_ATTEND,DIN_CODE,MEM_NUM) VALUES ('8','23-Feb-2016','Yes','Yes','DI5','210');

INSERT INTO INVITATION (INVITE_NUM,INVITE_DATE,INVITE_ACCEPT,INVITE_ATTEND,DIN_CODE,MEM_NUM) VALUES ('8','23-Feb-2016','Yes','Yes','DI5','209');

INSERT INTO INVITATION (INVITE_NUM,INVITE_DATE,INVITE_ACCEPT,INVITE_ATTEND,DIN_CODE,MEM_NUM) VALUES ('8','23-Feb-2016','Yes','Yes','DI5','209');

INSERT INTO INVITATION (INVITE_NUM,INVITE_DATE,INVITE_ACCEPT,INVITE_ATTEND,DIN_CODE,MEM_NUM) VALUES ('8','23-Feb-2016','Yes','Yes','DI5','207');

INSERT INTO INVITATION (INVITE_NUM,INVITE_DATE,INVITE_ACCEPT,INVITE_ATTEND,DIN_CODE,MEM_NUM) VALUES ('8','23-Feb-2016','Yes','Yes','DI5','205');

ENTREE Table

INSERT INTO ENTREE (ENT_CODE,ENT_DESC,ENT_PRICE) VALUES('EN3','Stuffed crab','20.00');

INSERT INTO ENTREE (ENT_CODE,ENT_DESC,ENT_PRICE) VALUES('EN3','Stuffed crab','20.00');

INSERT INTO ENTREE (ENT_CODE,ENT_DESC,ENT_PRICE) VALUES('EN3','Stuffed crab','20.00');

INSERT INTO ENTREE (ENT_CODE,ENT_DESC,ENT_PRICE) VALUES('EN4','Stuffed crab','20.00');

INSERT INTO ENTREE (ENT_CODE,ENT_DESC,ENT_PRICE) VALUES('EN5','Stuffed crab','20.00');

INSERT INTO ENTREE (ENT_CODE,ENT_DESC,ENT_PRICE) VALUES('EN6','Roasted Duck','30.00');

INSERT INTO ENTREE (ENT_CODE,ENT_DESC,ENT_PRICE) VALUES('EN2','Marinated Steak','10.00');

INSERT INTO ENTREE (ENT_CODE,ENT_DESC,ENT_PRICE) VALUES('EN8','Veg Biriyani','15.00');

INSERT INTO ENTREE (ENT_CODE,ENT_DESC,ENT_PRICE) VALUES('EN3','Stuffed crab','20.00');

INSERT INTO ENTREE (ENT_CODE,ENT_DESC,ENT_PRICE) VALUES('EN9','Roasted Duck','30.00');

DINNER Table

INSERT INTO DINNER(DIN_CODE,DIN_DESC,DIN_PRICE,ENT_CODE,DES_CODE) VALUES ('8','Glowing Sea Delight','EN3','DE8');

INSERT INTO DINNER(DIN_CODE,DIN_DESC,DIN_PRICE,ENT_CODE,DES_CODE) VALUES ('9','Glowing Sea Delight','EN3','DE5');

INSERT INTO DINNER(DIN_CODE,DIN_DESC,DIN_PRICE,ENT_CODE,DES_CODE) VALUES ('8','Ranch Superb','EN3','DE2');

INSERT INTO DINNER(DIN_CODE,DIN_DESC,DIN_PRICE,ENT_CODE,DES_CODE) VALUES ('10','Glowing Sea Delight','EN4','DE5');

INSERT INTO DINNER(DIN_CODE,DIN_DESC,DIN_PRICE,ENT_CODE,DES_CODE) VALUES ('11','Glowing Sea Delight','EN5','DE5');

INSERT INTO DINNER(DIN_CODE,DIN_DESC,DIN_PRICE,ENT_CODE,DES_CODE) VALUES ('8','Glowing Sea Delight','EN6','DE5');

INSERT INTO DINNER(DIN_CODE,DIN_DESC,DIN_PRICE,ENT_CODE,DES_CODE) VALUES ('8','Glowing Sea Delight','EN2','DE5');

INSERT INTO DINNER(DIN_CODE,DIN_DESC,DIN_PRICE,ENT_CODE,DES_CODE) VALUES ('8','Glowing Sea Delight','EN8','DE8');

INSERT INTO DINNER(DIN_CODE,DIN_DESC,DIN_PRICE,ENT_CODE,DES_CODE) VALUES ('8','Glowing Sea Delight','EN3','DE5');

INSERT INTO DINNER(DIN_CODE,DIN_DESC,DIN_PRICE,ENT_CODE,DES_CODE) VALUES ('8','Glowing Sea Delight','EN9','DE5');

DESSERT Table

INSERT INTO DESSERT(DES_CODE,DES_DESC,DES_PRICE) VALUES ('DE8','Chocolate mousse with raspberry sauce','5.00');

INSERT INTO DESSERT(DES_CODE,DES_DESC,DES_PRICE) VALUES ('DE5','Chocolate mousse with raspberry sauce','5.00');

INSERT INTO DESSERT(DES_CODE,DES_DESC,DES_PRICE) VALUES ('DE2','Chocolate mousse with raspberry sauce','5.00');

INSERT INTO DESSERT(DES_CODE,DES_DESC,DES_PRICE) VALUES ('DE5','Chocolate mousse with raspberry sauce','5.00');

INSERT INTO DESSERT(DES_CODE,DES_DESC,DES_PRICE) VALUES ('DE5','Chocolate mousse with raspberry sauce','5.00');

INSERT INTO DESSERT(DES_CODE,DES_DESC,DES_PRICE) VALUES ('DE5','Chocolate mousse with raspberry sauce','5.00');

INSERT INTO DESSERT(DES_CODE,DES_DESC,DES_PRICE) VALUES ('DE5','Chocolate mousse with raspberry sauce','5.00');

INSERT INTO DESSERT(DES_CODE,DES_DESC,DES_PRICE) VALUES ('DE8','Chocolate mousse with raspberry sauce','5.00');

INSERT INTO DESSERT(DES_CODE,DES_DESC,DES_PRICE) VALUES ('DE5','Chocolate mousse with raspberry sauce','5.00');

INSERT INTO DESSERT(DES_CODE,DES_DESC,DES_PRICE) VALUES ('DE5','Chocolate mousse with raspberry sauce','5.00');

4. Views,trigger and stored procedure using SQL query

a) CREATE VIEW [member names] AS

SELECT MEM_NUM,MEM_NAME

FROM

MEMBER

b) CREATE VIEW [show dessert menu] AS

SELECT DES_CODE,DES_DESC,DES_PRICE

FROM

DESSERT

Stored procedure

a) CREATE PROCEDURE SelectAllMembers

AS

SELECT * FROM MEMBER

GO;

EXEC SelectAllMembers // to execute stored procedure

b) CREATE PROCEDURE Showdinner

AS

SELECT * FROM DINNER

GO;

EXEC Showdinner // to execute stored procedure

Trigger

CREATE TRIGGER updatemembername

ON

dbo.MEMBER

FOR EACH ROW

UPDATE MEMBER SET MEM_NAME = : new.MEM_NAME

WHERE MEM_NUM = : new.MEM_NUM;

END;