Reference no: EM13779347

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	[email protected]
E8544399	Joe	Jenkins	(303)221-9875	[email protected]
E8843211	Amy	Tang	(303)556-4321	[email protected]
E9345771	Colin	White	(303)221-4453	[email protected]
E9884325	Thomas	Johnson	(303)556-9987	[email protected]
E9954302	Mary	Hill	(303)556-9871	[email protected]

 

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.

 

15) 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.

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

17) 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.

18) 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").

19) 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.

20) 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.

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

 

24) 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?

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

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