MIS 114 - Database Management Systems for Business



Fall 2005 Tsai

Section:___________ Name:______________

MIS 150 - Database Management Systems for Business

Exam 3

Part II: open books and notes

13. (12 points) School of Business Administration (SBA) hires faculty members to teach classes and staff members to perform the administration work. A faculty member can be an administrator at the same time. SBA offers different types of majors for students to get their degrees. Develop an Enhanced Entity-Relationship diagram to document the database of MIS. (Minus 1 point for each mistake.)

14. Given the following 6 relations:

customer (custid, custname, soucialsecurityno, address, country)

salesperson (salpersid, salpersname, dob, soucialsecurityno)

product (prodid, manufactrid, prodname, cost, salesprice)

sales (salesid, date, custid, salpersid, saleamount)

salesitem (salesid, prodid, manufactrid, qty)

manufacturer (manufactrid, manufactrname, address, country)

(a) (10 points) Use SQL statements to find the average sales amount for each salesperson in September 2005. The report should have salesperson name, salesperson number and average sales amount.

Select salpersname, salpersid, avg(saleamount)

From salesperson p, sales s

Where p. salpersid=s. salpersid

Group by (salpersname, salpersid);

(b) (10 points) Use SQL statements to list the salesperson name that did not make any sale in September 2005.

Select salpersname

From salesperson

Where salpersid not in

(Select salpersid

From sales

Where date between 9/1/05 and 9/30/05);

15. (24points) For each of the following relations with no repeating group, indicate (1) every determinant and the most appropriate primary key for the relation; and (2) the highest normal form that has been violated by the relation. (Minus 2 points for each wrong answer.)

a. CLASS (COURSE NO., ROOM, SECTION NO. CAPACITY)

assumption: ROOM and COURSE NO+ SECTION NO are unique.

determinant(s): ROOM; COURSE NO+ SECTION NO

primary key: COURSE NO+ SECTION NO

lowest normal form violation: 3rd

b. PROJ (PROJECT ID, EMPLOYEE ID, EMPLOYEE ADDRESS, TOTAL HOURS, TASK ID)

Assumptions: many-to-many relationship between PROJECT ID and EMPLOYEE ID, and many-to-many relationship between PROJECT ID and TASK ID.

determinant(s): EMPLOYEE ID; PROJECT ID+EMPLOYEE ID+TASK ID

primary key: PROJECT ID+EMPLOYEE ID+TASK ID

lowest normal form violation: 2nd

c. PROJECT (PROJECT ID, EMPLOYEE SALARY, EMPLOYEE ID)

Assumption: many-to-many relationship between PROJECT ID AND EMPLOYEE ID

determinant(s): EMPLOYEE ID

primary key: PROJECT ID+EMPLOYEE ID

lowest normal form violation: 2nd

d. PART_SUPPLIER (PART NO., PART DESCRIPTION, VENDOR ID, VENDOR NAME, VENDOR ADDRESS, UNIT COST)

Assumption: many-to-many relationship between PART NO and VENDOR ID.

determinant(s): PART NO.; VENDOR ID;

primary key: PART NO+VENDOR ID

lowest normal form violation: 2nd

16. (24 points) Normalize each of the following relations to the fifth normal form. Indicate primary key using single underline, and foreign key using double underlines for the final relations.

b. CLASS (COURSE NO., ROOM, SECTION NO. CAPACITY)

assumption: ROOM and COURSE NO+ SECTION NO are unique.

C1 (COURSE NO., ROOM, SECTION NO.)

C2 (ROOM, CAPACITY)

b. PROJ (PROJECT ID, EMPLOYEE ID, EMPLOYEE ADDRESS, TOTAL HOURS, TASK ID)

Assumptions: many-to-many relationship between PROJECT ID and EMPLOYEE ID, and many-to-many relationship between PROJECT ID and TASK ID.

P1 (EMPLOYEE ID, EMPLOYEE ADDRESS)

P2 (PROJECT ID, EMPLOYEE ID, TOTAL HOURS, TASK ID)

c. PROJECT (PROJECT ID, EMPLOYEE SALARY, EMPLOYEE ID)

Assumption: many-to-many relationship between PROJECT ID AND EMPLOYEE ID

P1 (EMPLOYEE SALARY, EMPLOYEE ID)

P2 (PROJECT ID, EMPLOYEE ID)

d. PART_SUPPLIER (PART NO., PART DESCRIPTION, VENDOR ID, VENDOR NAME, VENDOR ADDRESS, UNIT COST)

Assumption: many-to-many relationship between PART NO and VENDOR ID.

PART_SUPPLIER1 (PART NO., PART DESCRIPTION)

PART_SUPPLIER2 (VENDOR ID, VENDOR NAME, VENDOR ADDRESS)

PART_SUPPLIER (PART NO., VENDOR ID, UNIT COST)

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download