Be aware that some of the problems are dependent on each other; therefore, completing the requirements...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Be aware that some of the problems are dependent on each other; therefore, completing the requirements out of order can create errors. Use comments to appropriately number your answers. In requirements where an object name is given for you to create (table, attribute, trigger, procedure, sequence), you must create it with the exact name that is given because that is the name the front-end application programmers have been told to expect. 1. 2. Use the necessary commands to modify the APARTMENT table to enforce Apart_Num as the primary key. Using only a CREATE TABLE command (no ALTER TABLE allowed), create the WORKER table as specified below in the RAD schema, including all specified constraints. Attribute Work ID Work FName Work LName Data Type Requirement Integer values Variable length character data up to 30 characters Variable length character data up to 30 characters Work Specialty Variable length character data up to 15 characters Constraints PK No nulls allowed No nulls allowed Using only a CREATE TABLE command (no ALTER TABLE allowed), create the INSPECT table as specified below in the RAD schema, including all specified constraints. Attribute Work_ID Apart_Num Inspect Date Inspect_Result Data Type Requirement Integer values Integer numbers up to 4 digits (limit the digits not the values, as we did in the practice) Date Variable length character data up to 15 characters Constraints PK FK from WORKER PK FK from APARTMENT Use a command to create a single constraint that enforces a rule for the Inspect Result attribute in the INSPECT table. The value for the result must be PASS or FAIL only. table. Work ID 101 102 103 table. Work ID 101 101 102 Write the SQL commands to add the following data to the WORKER Work FName Frank Greta Lily Work LName Work Specialty DeLuca Rayne Van Horn Commercial Residential Write the SQL commands to add the following data to the INSPECT Apart Num 1001 Inspect Result PASS 1002 FAIL 1001 Inspect Date July 10, 2021 July 10, 2022 June 28, 2022 Begin a transaction Write a single SQL command to modify the inspection result for apartment 1001 on June 28, 2022 to PASS. Write a single SQL command to remove all records from the INSECT table that a related to apartment 1001. Rollback the transaction Create a stored procedure in the RAD schema that meets the following requirements. a. The procedure should be named spNew Lease. b. The user will provide values for Renter_ID, Apart_Num, and Lease_Begin as parameters. The parameters must be in that order. Be certain that your code accepts the first two as numbers, and the third as a date. c. Determine if the apartment number is valid (i.e., the apart_num already exists in the APARTMENT table). If the apart_num is not valid, output a message stating the apartment cannot be found. d. If the apartment number is valid, determine if the renter ID is valid (i.e., the renter_ID already exists in the RENTER table). If the renter_ID is not valid, output a message stating the renter cannot be found. (Note that your code should not be checking if the renter is valid if it already determined that the apartment is not valid.) e. If both the apartment number and renter ID are valid, check to ensure that there is not already a row in the LEASE table for this apartment on the date provided in the parameter. If there is a lease that begins before the provided date that ends after the provided date, then the apartment is already leased. If there is, then display a message that there is a scheduling conflict. For example, "Scheduling conflict for Apartment 1001 on 2022-09-15". Be aware that some of the problems are dependent on each other; therefore, completing the requirements out of order can create errors. Use comments to appropriately number your answers. In requirements where an object name is given for you to create (table, attribute, trigger, procedure, sequence), you must create it with the exact name that is given because that is the name the front-end application programmers have been told to expect. 1. 2. Use the necessary commands to modify the APARTMENT table to enforce Apart_Num as the primary key. Using only a CREATE TABLE command (no ALTER TABLE allowed), create the WORKER table as specified below in the RAD schema, including all specified constraints. Attribute Work ID Work FName Work LName Data Type Requirement Integer values Variable length character data up to 30 characters Variable length character data up to 30 characters Work Specialty Variable length character data up to 15 characters Constraints PK No nulls allowed No nulls allowed Using only a CREATE TABLE command (no ALTER TABLE allowed), create the INSPECT table as specified below in the RAD schema, including all specified constraints. Attribute Work_ID Apart_Num Inspect Date Inspect_Result Data Type Requirement Integer values Integer numbers up to 4 digits (limit the digits not the values, as we did in the practice) Date Variable length character data up to 15 characters Constraints PK FK from WORKER PK FK from APARTMENT Use a command to create a single constraint that enforces a rule for the Inspect Result attribute in the INSPECT table. The value for the result must be PASS or FAIL only. table. Work ID 101 102 103 table. Work ID 101 101 102 Write the SQL commands to add the following data to the WORKER Work FName Frank Greta Lily Work LName Work Specialty DeLuca Rayne Van Horn Commercial Residential Write the SQL commands to add the following data to the INSPECT Apart Num 1001 Inspect Result PASS 1002 FAIL 1001 Inspect Date July 10, 2021 July 10, 2022 June 28, 2022 Begin a transaction Write a single SQL command to modify the inspection result for apartment 1001 on June 28, 2022 to PASS. Write a single SQL command to remove all records from the INSECT table that a related to apartment 1001. Rollback the transaction Create a stored procedure in the RAD schema that meets the following requirements. a. The procedure should be named spNew Lease. b. The user will provide values for Renter_ID, Apart_Num, and Lease_Begin as parameters. The parameters must be in that order. Be certain that your code accepts the first two as numbers, and the third as a date. c. Determine if the apartment number is valid (i.e., the apart_num already exists in the APARTMENT table). If the apart_num is not valid, output a message stating the apartment cannot be found. d. If the apartment number is valid, determine if the renter ID is valid (i.e., the renter_ID already exists in the RENTER table). If the renter_ID is not valid, output a message stating the renter cannot be found. (Note that your code should not be checking if the renter is valid if it already determined that the apartment is not valid.) e. If both the apartment number and renter ID are valid, check to ensure that there is not already a row in the LEASE table for this apartment on the date provided in the parameter. If there is a lease that begins before the provided date that ends after the provided date, then the apartment is already leased. If there is, then display a message that there is a scheduling conflict. For example, "Scheduling conflict for Apartment 1001 on 2022-09-15".
Expert Answer:
Related Book For
Management Accounting Information for Decision-Making and Strategy Execution
ISBN: 978-0137024971
6th Edition
Authors: Anthony A. Atkinson, Robert S. Kaplan, Ella Mae Matsumura, S. Mark Young
Posted Date:
Students also viewed these databases questions
-
Carter Paint Company has plants in four provinces. Sales last year were $100 million, and the balance sheet at year-end is similar in percent of sales to that of previous years (and this will...
-
Assets Liabilities and Capital Cash $ 55,000 Liabilities $ 46,500 Adams, Loan 13,000 Adams, Capital 71,500 Other Assets 230,000 Peters, Capital 97,500 Blake, Capital 82,500 Total Assets $ 298,000...
-
Holmes Corporation is a leading designer and manufacturer of material handling and processing equipment for heavy industry in the United States and abroad. Its sales have more than doubled, and its...
-
For the following project please follow the below guidelines unless otherwise stated by your instructor. Worth 20% of the total grade. Read and follow all instructions in order to receive full...
-
Calculate the price of a three-month American put option on a non-dividend-paying stock when the stock price is $60, the strike price is $60, the risk-free interest rate is 10% per annum, and the...
-
Derive the probability distribution of the 1-year HPR on a 30-year U.S. Treasury bond with an 8% coupon if it is currently selling at par and the probability distribution of its yield to maturity a...
-
Explain how window placement in a building could be defined as (a) a passive solar feature, (b) an energy conservation technique, (c) both of these.
-
On January 1, 2018, the general ledger of Big Blast Fireworks includes the following account balances: The $30,000 beginning balance of inventory consists of 300 units, each costing $100. During...
-
Anderson Publishing has two divisions: Book Publishing and Magazine Publishing. The Magazine division has been losing money for the last five years and Anderson is considering eliminating that...
-
Show that if G is a CFG in Chomsky normal form, then for any string w L(G) of length n 1, exactly 2n 1 steps are required for any derivation of w.
-
Swatch, Inc. is thinking of acquiring Timex, Inc., and Swatch is attempting to determine the highest price per share it should be willing to pay for Timex. It expects Timex's FCF will equal $200...
-
How can e-commerce aid strategy implementation?
-
Many companies have focused on cost savings and outsourced manufacturing to China. However, many global supply chains were impacted when COVID-19 shut the Chinese borders. What are some lessons...
-
Given the challenges in implementing strategic alliances, what are some guidelines for successful alliances?
-
What are some reasons that motivate companies to engage in global and cross-border alliances?
-
While forming an economic group creates trade, it can also have some drawbacks. What are the potential costs of economic integration?
-
Calculate Accrued Interests for Each Bond by the Date of 01/01/2023 Calculate the price for the Bonds for the First day of every year till Maturity Year Calculate Macaulay's Duration and Modified...
-
Consider the activities undertaken by a medical clinic in your area. Required 1. Do you consider a job order cost accounting system appropriate for the clinic? 2. Identify as many factors as possible...
-
The nature of intrinsic and extrinsic rewards Do you believe that people value intrinsic rewards? Give an example of an intrinsic reward that you would value and explain why. Why are extrinsic...
-
What are some pros and cons of tying an individuals pay to performance?
-
Strathcona Paper rewards its managers on the basis of the after-tax return on investment of the assets that they managethe higher the reported return on investment, the higher the reward. The company...
-
A food processor claims that at most \(10 \%\) of her jars of instant coffee contain less coffee than claimed on the label. To test this claim, 16 jars of her instant coffee are randomly selected and...
-
Refer to Exercise 4.2. (a) Determine the cumulative probability distribution \(F(x)\). (b) Graph the probability distribution of \(f(x)\) as a bar chart and below it graph \(F(x)\). Data From...
-
Four emergency radios are available for rescue workers but one does not work properly. Two randomly selected radios are taken on a rescue mission. Let \(X\) be the number that work properly between...
Study smarter with the SolutionInn App