Project #122591 - HOMEWORK

1)     Indicate the normal form of the following relation. If the relation is not in 3NF, then decompose it into relations that are in 3NF and render a complete relational model using either Visio or the textbook notation. Your model MUST include relationship(s), minimum and maximum cardinalities, primary key notation, and foreign key notation. (worth 4 points).

 

 

PATIENT

____________ NF

 

 

 

 

PK

Patient_ID

Last_Name

First_Name

Bed_Number

Bed_Type

 

 

 

 

 

Patient_ID

Last_Name

 

1234

1235

1236

1237

1238

-----

1239

1240

 

Roberts

West

Hightower

Johnson

Cheney

-----

Croasdell

Hilmer

 

First_Name

 

Tom

Larry

Ross

Richard

Paul

-----

Dave

Kelly

 

11

12

27

58

12

23

11

27          

 

Bed_Type

 

Bed_Number

 

Reg

Rolling

Networked

Reg

Rolling

Pneumatic

Reg

Networked

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2) Convert the E-R diagram below to a relational model. If the relation is not in 3NF, then decompose it into relations that are in 3NF and render a complete relational model using either Visio or the textbook notation. Draw the relational model using Visio notation or the textbook notation. Your model MUST include relationship(s), minimum and maximum cardinalities, primary key notation, and foreign key notation. (worth 6 points).

 

 

Product

 (PK) Product_ID

            Product_Name

Contains

 

Quotes

 

Order

  (PK) Order_No

        Order_Date

 

Supplier

  (PK) Supplier_ID

          Supplier_Name

 

fills

 

Hint:

See Ch.4 for this question

There are quite a few examples in the text: Figure 4-11 – 4-17 on page 168 - 177

 

If the relationship is many-to-many, you would need to convert the relationship to an associative entity. For example, between Supplier and Product, it’s many-to-many, so Quotes should be converted into an associative entity, just like “Property Percentage Owned” in Exam 1, part 2.

 

 

 

 

 

 

 

 

 

 

 

 

 


 

 

 

Use the following tables to answer questions 3 through 9. Be sure to provide only the fields and records requested in the questions.

 

 

 

 

 

 

Important note: Assume that all data types are character (text) except for ESalary (currency) and DelQuantity (integer).

 

EMPLOYEE                                                                                                  DEPARTMENT

ENum

EName

ESalary

DeptName

SupervisorNum

 

DeptName

DeptFloor

DeptPhone

ENum

1

Alice

75000

Management

 

 

Accounting

5

35

5

2

Ned

45000

Marketing

1

 

Books

1

81

4

3

Andrew

25000

Marketing

2

 

Clothes

2

24

4

4

Clare

22000

Marketing

2

 

Equipment

3

57

3

5

Todd

38000

Accounting

1

 

Furniture

4

14

3

6

Nancy

22000

Accounting

5

 

Management

5

34

1

7

Brier

43000

Purchasing

1

 

Marketing

5

38

2

8

Sarah

56000

Purchasing

7

 

Navigation

1

41

3

9

Sophie

35000

Personnel

1

 

Personnel

5

37

9

10

Sanjay

15000

Navigation

3

 

Purchasing

5

36

7

11

Rita

15000

Books

4

 

Recreation

2

29

4

12

Gigi

16000

Clothes

4

 

 

 

 

 

13

Maggie

16000

Clothes

4

 

 

 

 

 

14

Paul

11000

Equipment

3

 

 

 

 

 

15

James

15000

Equipment

3

 

 

 

 

 

16

Pat

15000

Furniture

3

 

 

 

 

 

17

Mark

15000

Recreation

4

 

 

 

 

 

 

ITEM                                                                                                                             SUPPLIER

ItemName

ItemType

ItemColor

 

SupNum

SupName

Boots - snakeproof

C

Green

 

101

Global Books & Maps

Camel saddle

R

Brown

 

102

Nepalese Corp.

Compass

N

-

 

103

All Sports Manufacturing

Elephant polo stick

R

Bamboo

 

104

Sweatshops Unlimited

Exploring in 10 Easy Lessons

B

-

 

105

All Points, Inc.

Geo positioning system

N

-

 

106

Sao Paulo Manufacturing

Hammock

F

Khaki

 

 

 

Hat - polar explorer

C

White

 

 

 

How to Win Foreign Friends

B

-

 

 

 

Map case

E

Brown

 

 

 

Map measure

N

-

 

 

 

Pith helmet

C

Khaki

 

 

 

Pocket knife - Avon

E

Brown

 

 

 

Pocket knife - Nile

E

Brown

 

 

 

Safari chair

F

Khaki

 

 

 

Safari cooking kit

F

-

 

 

 

Sextant

N

-

 

 

 

Stetson

C

Black

 

 

 

Tent - 2 person

F

Khaki

 

 

 

Tent - 8 person

F

Khaki

 

 

 

 

 

DELIVERY

DelNum

DelQuantity

ItemName

DeptName

SupNum

51

50

Pocket knife - Nile

Navigation

105

52

10

Pocket knife - Nile

Books

105

53

10

Pocket knife - Nile

Clothes

105

54

10

Pocket knife - Nile

Equipment

105

55

10

Pocket knife - Nile

Furniture

105

56

10

Pocket knife - Nile

Recreation

105

57

50

Compass

Navigation

101

58

10

Geo positioning system

Navigation

101

59

10

Map measure

Navigation

101

60

25

Map case

Navigation

101

61

2

Sextant

Navigation

101

62

1

Sextant

Equipment

105

63

20

Compass

Equipment

103

64

1

Geo positioning system

Books

103

65

15

Map measure

Navigation

103

66

1

Sextant

Books

103

67

5

Sextant

Recreation

102

68

3

Sextant

Navigation

104

69

5

Boots - snakeproof

Clothes

105

70

15

Pith helmet

Clothes

105

71

1

Pith helmet

Clothes

101

72

1

Pith helmet

Clothes

102

3) Worth 6 pts. Write the SQL statement that will list the employee name and salary for all employees that work on the 2nd floor. Give the salary field the alias of “Current Salary”.

 

 

 

 

 

 

 

 

 

 

4) Worth 6 pts. Write the SQL statement that will increase the salaries of the employees assigned to the Clothes or Equipment departments by $3,000.

 

 

 

 

 

 

 

 

 

5) Worth 8 points. Write the SQL statement that will list supplier number, supplier name, department name, and floor for all deliveries where the quantity delivered (DelQuantity) is > 20.

 

 

 

 

 

 

 

 

 

 

 

6) Worth 8 pts. Write the SQL statement that will list the employee number, employee name, department name and department phone for all employees that make more than $2,000 per month.  Assume that the ESalary field represents each employee’s annual salary.

 

 

 

 

 

 

 

 

 

7) Worth 8 pts. Write the SQL statement that will list the department name, average salary, and number of employees for all departments that have more than two employees.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

8) Worth 6 pts. Write the SQL statement that will retrieve all fields from the department table and sort the results in descending order by floor number.  Use a ‘symbol’ to retrieve all fields.

 

 

 

 

 

 

 

 

 

 

 

 

9) Worth 8 pts. Write the SQL statement that will retrieve the supplier name and delivery number for all deliveries included in the delivery table. Based upon the data provided, will all suppliers be included in the results of this query? Circle Yes or No.

Subject Business
Due By (Pacific Time) 04/21/2016 12:00 am
Report DMCA
TutorRating
pallavi

Chat Now!

out of 1971 reviews
More..
amosmm

Chat Now!

out of 766 reviews
More..
PhyzKyd

Chat Now!

out of 1164 reviews
More..
rajdeep77

Chat Now!

out of 721 reviews
More..
sctys

Chat Now!

out of 1600 reviews
More..
sharadgreen

Chat Now!

out of 770 reviews
More..
topnotcher

Chat Now!

out of 766 reviews
More..
XXXIAO

Chat Now!

out of 680 reviews
More..
All Rights Reserved. Copyright by AceMyHW.com - Copyright Policy