Python Program to Create Constraints and Views Assignment Solution

Instructions

Objective

Write a python homework to create constraints and views.

Requirements and Specifications

Source Code

/* Ex 5 Constraints and Views */ /*The deliverable for this exercise is a Word document. Put your name and the exercise number (Ex 5) as a header in a Word document as you begin the exercise. Name the Word document ex3_lastname_firstname_section#.docx.*/ /*Here in the exercise instructions, you are prompted to (1) complete tasks, (2) take screen shots that document your hands-on work and insert them into the deliverable document, and/or (3) write something in the deliverable document. Use the Windows Snipping Tool or something like it to capture just the portion of the screen you need to document Whenever you capture work with a screenshot, include your name in the screenshot. This guideline applies to any screenshots you take to document your hands-on work throughout the semester. You may not get credit for something if you do not include your name as requested. */ /* The tasks refer to the ITAM schema tables that you created on your own instance of Oracle in the cloud. */ /* Task 1. A default constraint can added to a column to help assure domain integrity. These are added using the alter table command as demonstrated in class. For example, here are some ITAM business rules that can be supported with default constraints. When an employee is added or an employee action is recorded, the action date should be today and should include the current time. When a CI is added to inventory new, the acquired date should be today and should include the current time. When a CI is first added to inventory, the status should be 'WORKING'. When a CI is first added to inventory or when its status changes, the status date should change to today and should include the current time. When an assignment of a CI to an employee for USE or SUPPORT is first made, the date of the assignment should be today and should include the current time. When an assignment of a CI to an employee for USE or SUPPORT is reversed (unassigned), the date of the assignemnt should be today and should include the current time. When a summary record is added to the IT asse inventory summary, the date of the record should be today and should include the current time. In your Word document deliverable for Ex 5, choose one of the stated business rules and add a default constraint to the corresponding column in the appropriate table of the ITAM schema. (Let's not all do the first one, OK?) After adding the constraint, write one or more commands to demonstrate that the default constraint is working. As demonstrated in class, to test that a default constraint is working you must perform an insert that leaves the field null without passing a non-value using the keyword null, followed by a select command that selects the particular record you inserted to show that the default value was inserted even though no value was provided. */ --Ex 5 Task 1 Command 1 Create the Constraint --Type your name here --Implementing: (3) When a CI is first added to inventory, the status should be 'WORKING'. ALTER TABLE ITAM.CI_INVENTORY MODIFY CI_STATUS_CODE DEFAULT (SELECT CI_STATUS_CODE FROM CI_STATUS WHERE UPPER(CI_STATUS_DESCRIPTION) = 'WORKING'); --Ex 5 Task 1 Command(s) 2 Demonstrate the Constraint Working --Type your name here --Implementing: State the business rule you are supporting INSERT INTO ITAM.CI_INVENTORY (CI_INV_ID, ASSET_DESC_ID, PURCHASE_OR_RENTAL, UNIQUE_ID, CI_ACQUIRED_DATE, CI_STATUS_CODE, CI_STATUS_DATE, CHANGED_BY_USER, DATE_UPDATED) VALUES (100, 80, 'PURCHASE', 'Serial No. 112233455', '25/12/2020', NULL, '25/2/2020', 'AOAKINGB', '31/03/2022'); /* Task 2. A unique constraint can be added to a column to help assure entity integrity. These are added using the alter table command as demonstrated in class. For example, here are some ITAM business rules that can be supported with unique constraints. (1) Department names are unique. (2) Once a company email is created, it is never reused, thus, assuring that emails are unique for each employee regardless of employment status. Asset descriptions (comprised of three columns) are unique. In your Word document deliverable for Ex 5, choose one of the stated business rules and add a unique constraint to the corresponding column or set of columns in the appropriate table of the ITAM schema. (Let's not all implement the first one, OK?) After adding the constraint, write one or more commands to demonstrate that the unique constraint is working. As demonstrated in class, if there is data in your table that violates the uniqueness constraint, you won't be able to create it. You either have to fix or remove the data that violates the constraint, or you must add the constraint in disabled mode and then enable it with the novalidate option so that old data will not be checked against the constraint. To demonstrate that a unique constraint is working you must perform an insert or update command that violates it.*/ --Ex 5 Task 2 Command 1 - Create the Constraint --Type your name here --Implementing: Department names are unique. ALTER TABLE ITAM.DEPARTMENT ADD UNIQUE(DEPT_NAME); --Ex 5 Task 2 Command 2 - Demonstrate the Constraint Working --Type your name here --Implementing: State the business rule you are supporting INSERT INTO ITAM.DEPARTMENT (DEPT_CODE, DEPT_NAME, CHANGED_BY_USER, DATE_UPDATED) VALUES ('RECEPTION', 'Electrical Engineering', 'AOAKINGB', '31/03/2022'); /* Task 3. A check constraint can be added to a column to help assure domain integrity. These are added using the alter table command as demonstrated in class. For example, here are some ITAM business rules that can be supported with check constraints. An employee email address should use the company domain. That is, the email address should end with 'abcco.com'. The values allowed for the purchase or rental attribute of the CI inventory are limited to IN-HOUSE, LEASE, or PURCHASE. The values allowed for use or support are either USE or SUPPORT. The date that a CI is unassigned from an employee must be the same as or after the date that an employee is assigned to an employee. In your Word document deliverable for Ex 5, choose one of the stated business rules and add a check constraint to the corresponding column or set of columns in the appropriate table of the ITAM schema. After adding the constraint, write one or more commands to demonstrate that the check constraint is working. As demonstrated in class, if there is data in your table that violates the check constraint, you won't be able to create it. You either have to fix or remove the data that violates the constraint, or you must add the constraint in disabled mode and then enable it with the novalidate option so that old data will not be checked against the constraint. To demonstrate that a check constraint is working you must perform an insert or update command that violates it.*/ --Ex 5 Task 3 Command 1 - Create the Constraint --Type your name here --Implementing: (1) An employee email address should use the company domain. That is, the -- email address should end with 'abcco.com'. ALTER TABLE ITAM.EMPLOYEE ADD CONSTRAINT CompanyEmail CHECK (CO_EMAIL LIKE '%abcco.com'); --Ex 5 Task 3 Command 2 - Demonstrate the Constraint Working --Type your name here --Implementing: Try to insert an email that does not ends with 'abcco.com' and it should throw an error UPDATE ITAM.EMPLOYEE SET CO_EMAIL = 'emailtest@gmail.com' WHERE EMP_ID = 100; --error msg should be returned. /* Task 4. Write and test a query to report what configuration items (CIs) were assigned (or reassigned) for use during the three calendar months prior to the current month. (For example, if today is Oct. 3, 2021, then I want to see items assigned between July 1, 2021 and September 30, 2021, inclusive.) The identifying information for the CIs that you are to retrieve include the date the CI was assigned for use, its make, its model, its type (description), and other details about the CI (such as its serial / number). Capture a screenshot of the query with your name above it and 5 or 6 rows of the result set (after scrolling to the end of the result set in order to update the total rows fetched value). */ -- Ex 5 Task 4 Write the Query --Type your name here SELECT A.DATE_ASSIGNED, B.ASSET_MAKE, B.ASSET_MODEL, C.ASSET_TYPE_DESC, D.UNIQUE_ID FROM ITAM.EMPLOYEE_CI A JOIN ITAM.CI_INVENTORY D ON A.CI_INV_ID = D.CI_INV_ID JOIN ITAM.IT_ASSET_DESC B ON D.ASSET_DESC_ID = B.ASSET_DESC_ID JOIN ITAM.ASSET_TYPE C ON C.ASSET_TYPE_ID = B.ASSET_TYPE_ID WHERE A.DATE_ASSIGNED BETWEEN TO_DATE('31/12/2021') AND TO_DATE('28/02/2022'); -- Ex 5 Task 4 - Class Server Version --Type your name here /* Task 5. The requesting manager is so pleased with your report she wants you to provide the same report no later than the 12th of each month, for the three month period preceding the start of the current month when the report is run. So now you realize that it makes sense to create a view based on the query you used to deliver the first report. The view will be most useful if you generalize the date filter to where it retrieves data for the three month period preceding the start of the current month, regardless of when you run the query to select the data through the view. Here are some steps to help you figure out one way to generalize the where clause condition so that the query returns data in the proper date range. There is more than one way to generalize the condition. You do not have to document your work with these steps.*/ --Step 1 Run this query to see what it returns. select add_months(sysdate,-3) from dual; --Step 2 Run this query to see what it returns. select to_char(add_months(sysdate,-3),'MM-YY') from dual; --Step 3 Use the to_date function to turn the string --literal returned in the previous step into a date value --of the form 'MM-YY'. --Replace X with the expression in the selection list --of the Step 2 query. select to_date(X,'MM-YY') from dual; --Step 4 (Analyze) The value you see from Step 3 --should be the lower limit of the 6 month date --range you want to use as the boundaries for the --rows you want to be returned by your view. --Step 5 Run this query to see what it returns. select to_char(sysdate,'MM-YY') from dual; --Step 6 Use the to_date function to turn the string --literal returned in the previous step as a date value --of the form 'MM-YY'. --Replace X with the expression in the selection list --of the Step 6 query. select to_date(X,'MM-YY') from dual; --Step 7 (Analyze) The value you see from Step 6 --should be one day after the upper limit of the 6 --month date range you want to use as the boundaries --for the rows you want to be returned by your view. --In order to push the upper limit back one day, --subtract 1 from the date value. Run the following --query to check, replacing X with the expression in --the selection list of the Step 6 query. select to_date(X,'MM-YY')-1 from dual; --Step 8 (Apply) Use what you learned to create --a where clause condition that uses the between --operator to limit the rows returned based on --an appropriate stored date value being between --the lower limit and the upper limit expressed --by the calculation of Step 7 and Step 3. /* Once you get the query written to return the requested data from the ITAM tables and it is limited to records in a generalized date range of three month preceding the start of the current month up to the start of the currecnt month, create a view in your schema that is based on the query. Capture your name in a comment, the create statement for the view, and the response in the Script Output window that indicates successful creation of the view.*/ --Ex 5 Task 5 Create the View --Type your name here CREATE VIEW MY_VIEW AS SELECT A.DATE_ASSIGNED, B.ASSET_MAKE, B.ASSET_MODEL, C.ASSET_TYPE_DESC, D.UNIQUE_ID FROM ITAM.EMPLOYEE_CI A JOIN ITAM.CI_INVENTORY D ON A.CI_INV_ID = D.CI_INV_ID JOIN ITAM.IT_ASSET_DESC B ON D.ASSET_DESC_ID = B.ASSET_DESC_ID JOIN ITAM.ASSET_TYPE C ON C.ASSET_TYPE_ID = B.ASSET_TYPE_ID WHERE A.DATE_ASSIGNED BETWEEN add_months(sysdate, -3) AND add_months(sysdate, -1); /* Task 6. Once you have the view created, use it to return the data requested. Take a screenshot of your query where you use the view together with about 5 rows of the results returned after scrolling down to update the number of rows fetched.*/ --Ex 5 Task 6 Use the View --Type your name here SELECT * FROM MY_VIEW; /* Task 7. For the view created in Task 5 and used in Task 6, examine its column details in Oracle SQL Developer by selecting it in your Views folder in the Connections pane. Make a note of where data can be changed in a column of a base table of the the view by changing data through the view. Write a DML command to demonstrate that data in base table can be inserted, updated, or deleted through the view. The command you write will most likely be an update command. Capture your name, the command, and the result of running the command, and the result of running a select command on the base tables that demonstrates the success of the update statement. */ --Ex 5 Task 7 Change Data Through the View --Type your name here --Screenshot 1 The command to insert, update, or delete through the view --and result UPDATE MY_VIEW SET DATE_ASSIGNED = TO_DATE('01-01-1990', 'MM-DD-YYYY') WHERE UNIQUE_ID LIKE '%13CB'; --Screenshot 2 - A select from the base tables that verifies the --success of the change-through-the-view operation /*Task 8. The requesting manager from Task 5 through 7 does not want information returned in the report that is about IT assets that were assigned and then returned during the three month period. Alter the base query of the view to accomodate this requirement and then recreate the view and use it to select the data. Is the updated view updatable? Explain. */ --Ex 5 Task 8 Modify the View and Use the View --Type your name here CREATE OR REPLACE VIEW MY_VIEW AS SELECT A.DATE_ASSIGNED, B.ASSET_MAKE, B.ASSET_MODEL, C.ASSET_TYPE_DESC, D.UNIQUE_ID FROM ITAM.EMPLOYEE_CI A JOIN ITAM.CI_INVENTORY D ON A.CI_INV_ID = D.CI_INV_ID JOIN ITAM.IT_ASSET_DESC B ON D.ASSET_DESC_ID = B.ASSET_DESC_ID JOIN ITAM.ASSET_TYPE C ON C.ASSET_TYPE_ID = B.ASSET_TYPE_ID WHERE A.DATE_ASSIGNED BETWEEN add_months(sysdate, -3) AND add_months(sysdate, -1) AND A.DATE_UNASSIGNED IS NULL; --Ex 5 Task 8 Answer the Question Asked --Type your name here SELECT * FROM MY_VIEW;