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
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