What relational algebra operator to find products

Assignment Help Basic Computer Science
Reference no: EM13770345

Problems

The problems use the customer, orderTbl, and Employee tables of the simplified Order Entry database. Chapters 4 and 10 extend the database to increase its usefulness. The customer table contains clients who have placed orders. The orderTbl contains basic facts about customer orders. The Employee table contains facts about employees who take orders. The primary keys of the tables are CustNo for Customer, EmpNo, for Employee, and OrdNo for orderTbl.

Customer

CustNo

CustFirstName

CustastName

CustCity

CustState

Custzip

CustBal

C0954327

Sheri

Gordon

Littleton

CO

80129-5543

$230.00

C1010398

Jim

Glussman

Danver

CO

80111-0033

$200.00

C2388597

Beth

Taylor

Seattle

WA

98103-1121

$500.00

C3340959

Betty

Wise

Seattle

WA

98178-3311

$200.00

C3499503

Bob

Mann

Monroe

WA

98013-1095

$0.00

C8543321

Ron

Thompson

Renton

WA

98666-1289

$85.00

Employee

EmpNo

EmpFirstName

EmpLastName

EmpPhone

EmpEmail

E1329594

Landi

Santos

(303)789-1234

LSantos@bigco.com

E8544399

Joe

Jenkins

(303)221-9875

JJenkins@bigco.com

E8843211

Amy

Tang

(303)556-4321

ATang@bigco.com

E9345771

Colin

White

(303)221-4453

CWhite@bigco.com

E9884325

Thomas

Johnson

(303)556-9987

TJohnson@bigco.com

E9954302

Mary

Hill

(303)556-9871

MHill@bigco.com

OrderTbl

OrdNo

OrdDate

CustNo

EmpNo

01116324

01/23/2010

C0954327

E8544399

02334661

01/14/2010

C0954327

E1329594

03331222

01/13/2010

C1010398

 

02233457

01/12/2010

C2388597

E9884325

04714645

01/11/2010

C2388597

E1329594

05511365

01/22/2010

C3340959

E9884325

07989497

01/16/2010

C3499503

E9345771

01656777

01/11/2010

C8543321

 

07959898

01/19/2010

C8543321

E8544399

1) Write a create table statement for the customer table. Choose data types appropriate  for the DBMS used in your course. Note that the custBalcolumn contain numeric data. The currency symbols are not stored in the database. The custFirstName and custLast     Name columns are required (not null).

2) write a create table statement for the Employee  table. Choose data types appropriate for the DBMS used in your course. The EmpFirstNames, EmpLastName, and EmpEMail columns are required (not null).

3)  write a create table statement for the orderTbl table. Choose data types appropriate for the DBMS  used in your course. The ordData column is required (not null).

4) Identify the foreign keys  and draw a relationship draw a relationship diagram for the simplified order entry database. The CustNo column references the customer table and the EmpNo column references the Employee table. For each relationship, identify the parent table and the child table.

5) Extend your create table statement from problem (3) with referential integrity constraints. Updates and deletes on related rows are restricted.

6) From examination of the sample data and your common understanding of  order entry business, are null values allowed for the foreign keys in the orderTbl table? Why or why not? Extend the create table statement in problem (5) to enforce the null value restrictions if any.

7) Extend your create table statement for the employee table (problem 2) with a unique constraint for EmpEMail. Use a named constraint clause for the unique constraint.

9) Show the result of a restrict operation that lists the customers residing in seattle, WA.

10) Show the result of a project operation that lists the CustNo, CustFirstName, CustLastName, column of the customer table.

12) Show the result of a natural join that combines the customer and orderTbltables.

13) Show the result of a one-sided outer join between the Employee and orderTbl table. Preserve the rows of the orderTbl table in the result.

14) Show the result of a full outer join between the Employee and orderTbl table.

15) Show the result of the restrict operation on customers where the condition is CustCity equals "Denver" or "Seattle" followed by a project operation to retain the CustNo. CustFirstName, CustLastName, and CustCity columns.

16) Show the result of a natural join that combines the customer and orderTbl tables followed by a restrict operation to retain only the Colorado customers (CustState="CO").

17) Show the result of a summarize operation on customer. The grouping column is CustState and the aggregate calculate is COUNT. COUNT shows the number of rows with the same value for the grouping column.

18) Show the result of a summarized operation on customer. The grouping column is CustState and the aggregate calculations are the minimum and maximum CustBal values.

19) What tables are required to show the CustLastName, EmpLastName, and OrdNo columns. In the result table?

20) What relational algebra operator do you use to find products contained in every order? What relational algebra operator do you use to find products contained in any order?

21) Are the customer and Employee tables union compatible?  Why or why not?

22) Using the database after problem (23), what tables must be combined to list the products names ordered by customer number C 09543227

Reference no: EM13770345

Find that the user has disabled the internet filter software

As you run tests after making the upgrade, you are surprised to find that the user has disabled the Internet filter software that is supposed to be standard on all corporate

Modify the insertion sort so that it is more balanced

Modify the insertion sort so that it is more balanced. To do this allow insertions at both ends of the array. You may also wish to include an exchange mechanism to speed up

Case of healthy people pharmacy

Consider the case of Healthy People Pharmacy. Healthy People Pharmacy is a small town pharmacy that not only sells pharmaceuticals but also sells items ranging from milk to

Global management studies

Game theory (a) examines the choice of optimal strategies in conflict situations, (b) seeks to predict the behavior of players, (c) can be used to analyze oligopolistic inte

Constraint stated in the above business rule

Consider the following business rule: "For a nurse to be appointed nurse-in-charge of a care center, that nurse must posses an RN certificate." Draw an EER diagram to captur

Assignment on computer memory hacking

Describe what hacking of memory or hacking of RAM means. Examine the common tools that hackers use to hack into memory of computing devices. Detail two (2) real-life examples

Display a count of the number of letters in the word

Write a program that accepts a word into a character array string variable. Display a count of the number of letters in the word. Also display a count of the number of vowel

Diagram of the finite automaton

Determine the DFA that will accept those words from Σ = {a b , } where the number of b's is divisible by three. Sketch the state table diagram of the finite Automaton M also

Reviews

Write a Review

 
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd