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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
Related searches
- free client database management software
- nonprofit database management software
- database management best practices
- database management system textbook pdf
- database management system book pdf
- client database management free
- free school database management software
- database management system pdf books
- common database management systems
- access database management system
- database management pdf
- database management systems list