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
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
Study of past mcis and their psychological impact
: The ERM is prepared to handle the incident response with emergency personnel and equipment, but he is unsure if the city is prepared for the psychological aftermath. As his senior advisor, you have been asked to generate a white paper study of pas..
|
Lead the media campaign for isaf
: You have been selected to lead the media campaign for ISAF. Through extensive liaison, you have been allowed to write 1 article that will run simultaneously in the major news publications throughout the United States, Europe, Afghanistan, and Paki..
|
Describe fully your selected county''s central strategy
: List and describe fully your selected county's central strategy or plan. These are often emergency operations plans with various subordinate annexes and appendices, but because plan designs can vary widely by community, research and report your count..
|
Inflation adjusted outcome and the unadjusted outcome
: What has happened to revenue imbalance, unadjusted for inflation, for the NFL over time and what differences do you find between the inflation adjusted outcome and the unadjusted outcome.
|
What relational algebra operator to find products
: 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
|
Resources-kudler opening budget
: Illustrate how your venture would perform by estimating the revenue and expense to calculate operating profit or loss. Include estimates of your venture's main sources of revenue and the expenses expected in the main cost categories such as the co..
|
Brief review of the purpose of the qhsr
: Provide a brief review of the purpose of the QHSR and your assessment as to its potential value for the county as it rewrites old plans and develops new ones
|
Discuss which principles of economics directly relate
: Discuss which principles of economics directly relate to your purchasing decision - compare the marginal benefits and the marginal costs associated with your purchasing decision.
|
List of foreign terrorist organizations
: The U.S. Department of State publishes a list of foreign terrorist organizations. Because of this label, many Americans lack the understanding of what these groups are trying to accomplish
|