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...
-
In the early 1980s, New England Electric System (NEES) was deciding how much to bid for the salvage rights to a grounded ship, the SS Kuniang. If the bid were successful, the ship could be repaired...
-
You are the audit senior who has recently been assigned to the year-end audit of Ontario Agra Corporation (OAC). After the end of the audit fieldwork, the senior on the engagement resigned suddenly...
-
Pete Hollingsworth is currently taking an accounting course and is confused about what his professor told the class about analyzing financial statements. Pete would like you to lead a study session...
-
The ledger and trial balance of Sweetwater Services Co. as of July 31, 2012, the end of the first month of its current fiscal year, are presented in the working papers. Date needed to determine the...
-
Statement of Cash Flows (Indirect Method) The Sweet Company's income statement and comparative balance sheets as of December 31 of the previous year and the current year are presented below: SWEET...
-
Use mesh analysis to find ix in Fig. 13.80, Where is = 4 cos(600t) A and vs = 110 cos(600t + 30º) 12 F 150 800 mH 600 mH
-
A business plan is a written narrative that describes what a new business/venture intends to accomplish and how it intends to accomplish it. You have identified/recognized a viable opportunity...
-
One of the most important contracts that many employees sign is a non-compete agreement. A number of states are restricting what can be contained in a non-compete agreement and putting more limits on...
-
A car accelerates from rest to a velocity of 25 m/s in 5 seconds. What is its acceleration during this time period?
-
Show that any n+ 1 solutions of (2.3) on (c, d) are linearly dependent on (c, d). 2 Show that any \( n+1 \) solutions of \( (2.3) \) on \( (c, d) \) are linearly dependent on \( (c, d) \) To solve...
-
Healthcare professionals needs to compare their standards with international providers who have been recognized tom offer the best care. Outline the procedure involved in the comparison and its...
-
Olsen, who was seventeen, and had a parttime job, was in the market for a new computer system. He found a new Macintosh system for $2,900 at Macrotex, Inc. Beth Skidmore, an uthorized sales...
-
What is the difference between 2019 FYA revenues and LTM revenues (2019 FYA minus LTM)? "Reported" Year-to-date Revenues in Income Statement: Q1 2019: $100.0m Q2 2019: $250.0m Q3 2019: $500.0m Q4...
-
PC Contractors, Inc., was an excavating business in Kansas City, Missouri. Union Bank made loans to PC, subject to a perfected security interest in its equipment and other assets, including...
-
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 distillation column operating at total reflux is separating acetone and ethanol at \(1.0 \mathrm{~atm}\). The height of packing is \(2.0 \mathrm{~m}\). The column has a partial reboiler and a total...
-
We wish to strip \(\mathrm{SO}_{2}\) from water using pure air at \(20.0^{\circ} \mathrm{C}\). Outlet water contains \(0.0060 \mathrm{~mol} \% \mathrm{SO}_{2}\), and inlet water contains \(0.112...
-
If 1-in. metal Raschig rings are used instead of 2-in. rings in Example \(16-2\) : Example 16-2 Example 4-3 Example 16-1 a. Recalculate the flooding velocity and the required diameter. b. Recalculate...
Study smarter with the SolutionInn App