Search results for 'Chapter'

Murach's MySQL 3rd Edition Chapter 11 Exercise 2 and 4

2. Write a script that contains the CREATE TABLE statements needed to implement the following design in the EX schema:

These tables provide for members of an association, and each member can be registered in one or more groups within the association.
The member_id and committee_id columns are the primary keys of the Members and Committees tables, and these columns are foreign keys in the Members_Committees table.
Include any contraints or default values that you may think are necessary.
Include statements to drop these tables if the already exist.

4. Write an ALTER TABLE statement that adds two new columns to the Members table created in exercise 2.
Add one column for annual dues that provides for three digits to the left of the decimal point and two to the right. This column should have a defualt value 52.50.
Add on column for the payment date.

Murachs MySQL 3rd Edition Chapter 15 Exercise 1 and 2

1. Write a script that creates and calls a stored procedure named insert_glaccount. First, code a statement that creates a procedure that adds a new row to the General_Ledger_Accounts table in the AP schema. To do that this procedure should have two parameters, one for each of the two columns in this table. Then, code a CALL statement that tests this procedure. (Note that this table doesn't allow duplicate account descriptions.)

2. Write a script that creates and calls a stored function named test_glaccounts_description. First, create a function that tests whether an account description is already in the General_Ledger_Accounts table. To do that, this function should accept one parameter for the account description, and it should return a value of 1 if the account description is in the table or 0 if it isn't. (Note: If a SELECT statement doesn't return any data, it raises a NOT FOUND condition that your function can handle.)

Murach's MySQL 3rd Edition Chapter 14 Exercise 1 and 2

1. Write a script that creates and calls a stored procedure named test. This procedure should include a set of three SQL statements coded as a transaction to reflect the following change: United Parcel Service has been purchased by Federal Express Corporation and the new company is named FedUP. Rename one of the vendors and delete the other after updating the vendor_id column in the Invoices table.
If these statements execute successfully, commit the changes. Otherwise, roll back the changes.

2. Write a script that creates and calls a stored procedure named test. This procedure should include a set of two SQL statements coded as a transaction to delete the row with an invoice ID of 114 from the Invoices table. To do this, you must first delete all line items for that invoice from the Invoice_Line_Items table.
If these statements execute successfully, commit the changes. Otherwise, roll back the changes.

MIS582 iLab 4 Week 7 Webserver/Databaseserver Construction Using MySQL and PHP

iLab Overview
Scenario/Summary
In this assignment learn to create and manipulate a MySQL database using the PHP. To complete this assignment, you will need to be able to run MySQL via Omnymbus. You should review Chapter 11's coverage on PHP, the tutorial titled "PHP and MySQL" listed in the Webliography and MySQL's own dialect of the SQL language via the file M10C_KROE8352_13_SE_WC10C.pdf in the Doc-Sharing folder titled My SQL Documents.
Please ensure that you can connect to MySQL/Omnymbus via the account your Professor has emailed to you. Please consult with the document titled MySQLOmnymbusSupport.docx located in the Doc-Sharing folder titled Omnymbus Tutorial Files for instructions on how to get help for any issues that you are having with the MySQL/Omnymbus Environment.

Deliverables
Submit your assignment to the Dropbox, located at the top of this page. For instructions on how to use the Dropbox, read these step-by-step instructions. (See the Syllabus section "Due Dates for Assignments & Exams" for due dates.)
Name your MySQL database Lab4_xxxx where xxxx is your userid. Create and save your MySQL database file. You will be required to submit a web-link to your index.php file, along with 6 php script files and a sql file that will be executed from the Omnymbus public Web page from your Omnymbus account. Please note that your php and sql files will be based upon the templates provided the zip file titled MIS582_Wk7_templates.zip located in the folder titled iLab Documents in Doc Sharing.
You will need to modify the following php scripts and sql file from the MIS582_Wk7_templates.zip file.
• index.php
• insert.php
• insert_data.php
• update.php
• update_data.php
• delete.php
• MyAddressBook_CreateTable.sql

Required Software
Omnymbus – MySQL
Access the software at https://devry.edupe.net:8300.

Lab Steps
STEP 1: Review the Data Dictionary
Review the provided data dictionary below to understand the entity, attributes, and primary key of the supplied MySQL database.
Table Name ATTRIBUTE NAME CONTENTS TYPE FORMAT RANGE REQUIRED PK or FK
MyAddressbook id Contact ID Number Auto-Increment
Int(3) 999 000-999 Y PK
f_name Contact First Name VCHAR(25) Xxxxxx Y
L_name Contact Last Name VCHAR(25) Xxxxxx Y
ADDRESS Contact Address VCHAR(25) Xxxxxx Y
City Contact City VCHAR(25) Xxxxxx 0-9 Y
State Contact State CHAR(2) Xxxxxx Y
zip Contact Zip Code NUMBER(5) 99999 Y
phone Contact Phone NUMBER(10) 9999999999 Y

STEP 2: Create Table, Insert Statement
• Run MySQL via Omnymbus.
• Modify the table creation script that will be used to create the database with the outlined attributes from the Data Dictionary.
• Provide one Insert Statement that will add your contact information to the newly created table.
• Save the file with a name containing Lab7_, your first initial, and your last name (e.g., Lab7_JSmith.sql).
• Execute your script and ensure that the database is created.

STEP 3: Build Index PHP Page
Building Your index.php Web Page
In this step, you will complete your php script page to connect to the Lab7 database and display the MyAddressbook table on the Web page.
• Complete the MySQL connection for the index.php page. Be sure to fill in appropriate host (127.0.0.1), username, password, and database information in the mysqli field.
• Modify the provided php code to display each attribute of your MyAddressbook table. Note that initially, only 1 entry should be present in your table.
• Save your index.php file in your public_html folder in Omnymbus file manager.
• Please note that you will find your public_html folder down the path zdisk/USERID/public html
• Direct your Web browser to your site url (ex. http://devry.edupe.net/~XXXX/index.php).
• Note that you’ll replace XXXX with your Omnymbus login ID.
• Ensure that your file executes and the database table displays on the screen.
• If you have a correct connect command but you get the error message "MySQL ERROR 1045 (28000): Access denied for user" you will have to create a new password with the box "Change password in other modules?" checked.
• There is a supplied image in the Doc Sharing that provides an example of how your index.php page should look. Note: Feel free to add any additional formatting or images to your index.php page.

STEP 4: Build, Update PHP Pages
Building Your update.php and update_data.php Pages
In this step, you will complete two php HTML pages. Your update.php page will provide a form for users to submit data to the database that will be updated. The update_data.php page will provide the actual queries in php to update the data in your database. When this process is complete, your page will redirect the user back to the index.php page.
• Review the update_data.php page template.
• You will need to modify the php code to add additional input forms for the user to enter all appropriate attributes that will need to be updated.
• In the update_data.php file, ensure that your <form action> tag is set equal to update.php
• Character data should have a character data type.
• When the action is complete, your page should redirect back to index.php.

STEP 5: Build Delete PHP Page
Building Your delete.php Page
In this step, you will complete the delete.php page. Your delete.php page will process the query that will remove the necessary data for the database. When this process is complete, your page will redirect the user back to the index.php page.
• Review the delete.php page template.
• You will need to modify the php code to delete the provided entry based on the address id.
• When the action is complete, your page should redirect back to index.php.

STEP 6: Test Links to Update, Delete, and Insert Pages. Submit Deliverables.
Finishing Your Web-Enabled Database
Before submitting, ensure that you have added at least 5 entries to your Addressbook database.
Ensure that all links for update, delete, and insert work properly.
When you are done, submit your web-link to your index.php (e.g. http://devry.edupe.net/~####/index.php), If your web pages do not work according to the rubric below you can submit a zip file titled Lab4_FirstInitialLastName.zip which should contain your six php code pages and your database script for possible partial credit,
Rubric
Points will be awarded according to the following rubrics.
• 10 points: Script and Table created for the MyAddressbook entity as described in the Data Dictionary.
• 10 points: index.php created that correctly displays the contents of the database.
• 10 points: insert.php and insert_data.php pages created that correctly add data to the database.
• 10 points: update.php and update_data.php pages created that correctly update the data in the database.
• 10 Points: delete.php page created that correctly removes an entry from the database.
For a more advanced optional lab experience you can add error checking and comments and better appearance to the code. Use Chapter 11 as a guide.

ITSE 2309 LAB 2 More SQL Queries and Modification

Oracle 11g SQL–Chapters- 3,6,8,9,11,12,
You will continuing using items created in Lab 1

Lab 2a -- Problems 1–4,
1. For each customer, list each stock item ordered,
1) the manufacturer,
2) the quantity ordered, and
3) the total price paid.
Include the following columns in the order given below:
- From Customer Table: Company
- From Stock Table: Description
- From the Manufact Table: Manu_Name
- From the Items Table: Quantity, Total Price
Order the output by Company and Description.
Submit/hand in Output from SQL query

2. List all orders with a shipping date between December 25, 1999 and January 5, 2000
Include
1) the Order Number,
2) Order Date,
3) Customer company name, and
4) Shipping Date.
Order by
Customer Company Name and Order Number.
Submit/hand in Output from SQL query

3. Count the number of customers who do not have any orders placed.
Submit/hand in Output from SQL query

4. List all customers –
I) Who are ordering equipment whose description begins with 'tennis' or 'volleyball'.
II )Include
1) Customer number,
2) Stock number, and
3) Description.
Submit/hand in Output from SQL query
Do not repeat any rows.

Lab 2b Problems 5, 6, 7 and 8
5. Use the following SQL CREATE commands to CREATE the following tables in your
CREATE TABLE Professor
(Prof_ID NUMBER(3) Constraint pk_Professor Primary Key,
Prof_Lname VARCHAR2(15) NOT NULL,
Prof_Hiredate DATE,
Prof_Sal NUMBER(8,2),
Prof_Dept CHAR(6),
);
CREATE TABLE Student
(Stu_ID NUMBER(4) Constraint pk_Student Primary Key,
Stu_Lname VARCHAR2(15) NOT NULL,
Stu_Major CHAR(6),
Stu_CredHrs NUMBER(4),
Stu_GradePts NUMBER(5),
Prof_ID NUMBER(3),
CONSTRAINT fk_Student_Prof_ID FOREIGN KEY(Prof_ID)
REFERENCES Professor
);
Submit/Hand in: Print out of the Create commands, the system response and a DESCRIBE of the tables created.

6. Insert the following data into the tables created above using SQL INSERT commands.
Professor Table:
Prof_ID Prof_Lname Prof_Hiredate Prof_Sal Prof_Dept
123 Hilbert 20-MAY-1992 58000.00 MATH
243 Newell 15-JUL-1997 65500.00 CMPSCI
389 Lessing 04-APR-1988 40250.00 ENG
Student Table:
Stu_ID Stu_Lname Stu_Major Stu_CredHrs Stu_GradePts Prof_ID
2001 Parker CMPSCI 52 160 243
2166 Smith ENG 30 75 389
3200 Garcia MATH 62 248 123
4520 Smith CMPSCI 45 157 NULL
BE SURE TO ISSUE A COMMIT AFTER TABLE MODIFICATION COMMANDS HAVE BEEN RUN SUCCESSFULLY.
Submit a
Listing of each INSERT command,
The systems response and the resulting tables after the INSERTS are completed
(Example: SELECT * FROM Student;).

7. Perform the following SQL DELETE statements. Be sure to do them in order.
Issue a COMMIT command after all DELETEs have run.
a. Try to delete Professor 389. What message do you get? ___________________________
b. Delete Student 2166.
c. Now Delete Professor 389. Explain why the first attempt in a. was unsuccessful, and this time the DELETE was successful.
Submit/hand in : A listing of the DELETE statements.
The answers to questions a. b. and c.
A listing of the two tables after the deletes have run.

8. Perform the following UPDATE commands.
Issue a COMMIT command after all UPDATEs have run.
a. Replace the value of the Prof_ID for Student 4520 with 243.
b. Add 10% to the salary for each professor
Submit/hand in : A listing of the UPDATE statements
A listing of the two tables after the UPDATEs have run.

ITSE 2309 LAB 1 Database and SQL Queries

Submit: Query Statements Query Output
Chapters 2,3,4,5 11g SQL book
Objectives: Be able to access ORACLE/SQL Plus Be able to do simple SQL operations

Steps:
- Log onto the sample Oracle 11g database created.
- See Chapter 7 in "Database Systems" for information on coding, saving, and executing your queries.
- Save the output of the queries by using the SPOOL option ( see chpt 14 in 11g SQL in lab resources tab)
- Save both the queries code and the output of the queries as "Lab1_LastName.txt and submit via Blackboard—using the - Attach file – Upload function

Do the following queries:
1. List all columns and rows in the stock table.
2. List the last name, first name, and company of all customers (List the columns in that order). Place the list in alphabetical order by company name.
3. List the company names for all customers from Sunnyvale, Redwood City, or San Francisco.
4. List all orders that were placed between the dates 12/31/1999 and 01/03/2000. List order number, order date, customer number, ship date, and paid date. (Hint: Specify year in single quotes 'DD-MMM-YYYY')
5. List the order number, order date, and shipping charges for all orders that are not on backlog and for which the shipping charge is over $15.00.
6. List all stock items which are baseball items which have a unit price greater than $200.00 and a manufacturer code which starts with 'H'. (Hint: use LIKE)
7. List the company name for all customers who have orders. Don not list a company more than once.
8. List the customer number and the description (from the stock table) of all items ordered by customers with customer numbers 104-108. Order the output by customer number and description. (There should be no duplicate rows in your output).
9. List the number of (distinct) customers having an order. Label the column "Total_Customers_with_Orders".
10. For each customer having an order, list the customer number, the number of orders that customer has, the total quantity of items on those orders, and the total price for the items. Order the output by customer number. (Hint: You must use a GROUP BY clause in this query).

Oracle 11g PL/SQL Joan Casteel Chapter 9 Hands-On Assignments Part 9-5 to 9-8

Assignment 9-5: Processing Discount
Brewbean's is offering a new discount for return shoppers. Every fifth completed order gets a 10% discount. The count of orders for a shopper is placed in a packaged variable named pv_disc_num during the ordering process. The count needs to be tested at checkout to determine whether a discount should be applied. Create a trigger named BB_DISCOUNT_TRG so that when an order is confirmed (the ORDERPLACED value is changed from 0 to 1), the pv_disc_num packaged variable is checked. If it's equal to 5, set a second variable named pv_disc_txt to Y. This variable is used in calculating the order summary so that a discount is applied, if necessary.
create a package specification named DISC_PKG containing the necessary packaged variables. Use an anonymous block to initialize the packaged variables to use for testing the trigger. Test the trigger with the following UPDATE statement:
UPDATE bb_basket
SET orderplaced = 1
WHERE idBasket = 13;
If you need to test the trigger multiple times, simply reset the ORDERPLACED column to 0 for basket 13 and then run the UPDATE again. Also, disable this trigger when you're finished so that it doesn't affect other assignments.


Assignment 9-6: Use Triggers to Maintain Referential Integrity
At times, Brewbean's has changed the ID numbers for existing products. In the past, developers had to add a new product row with the new id to the BB_PRODUCT table, modify all the corresponding BB_BASKETITEM and BB_PRODUCTOPTION table rows, and then delete the original product row. Can a trigger be developed to avoid all these steps and handle the update of the BB_BASKETITEM and BB_PRODUCTOPTION table rows automatically for a given change in product ID? If so, create the trigger and test by issuing an update statement, which changes the IDPRODUCT of 7 to 22. Do a rollback to return the data back to its original state. Also, disable the new trigger after you have completed the assignment.


Assignment 9-7: Updating Summary Data Tables
The Brewbean's owner uses several summary sales data tables every day to monitor business activity. The BB_SALES_SUM table holds the product ID, total sales in dollars, and total quantity sold for each product. A trigger is needed so that every time an order is confirmed or the ORDERPLACED column is updated to 1, the BB_SALES_SUM table is updated accordingly. Create a trigger named BB_SALESUM_TRG that perform this task. Before testing, reset the ORDERPLACED column to 0 for basket 3, as shown in the following code, and use this basket to test the trigger.
UPDATE bb_basket
SET orderplaced = 0
WHERE idBasket = 3;
Notice that the BB_SALES_SUM table already contains some data. Test the trigger with the following UPDATE statement, and confirm that the trigger is working correctly:
UPDATE bb_basket
SET orderplaced = 1
WHERE idBasket = 3;
Do a rollback and disable the trigger when you're finished so it doesn't affect the other assignments.


Assignment 9-8: Maintaining an Audit Trail of Product Table Changes
The accuracy of product table data is critical and the Brewbean's. owner wants to have an audit file that containing information on all DML activity on the BB_PRODUCT table. This information should indicate the ID of the user performing a DML action, the date, the original values of the changed row, and the new values. This audit table needs to track specific columns of concern, including PRODUCTNAME, PRICE, SALESTART, SALEEND, and SALEPRICE. Create a table named BB_PRODCHG_AUDIT that can hold the relevant data. Then create a trigger named BB_AUDIT_TRG that fires an update to this table whenever one of the specified columns in the BB_PRODUCT table is changed.
Be sure to issue the following command. If you created the SALES_DATE_TRG trigger in the chapter, it conflicts with this assignment.
ALTER TRIGGER SALES_DATE_TRG DISABLE;
Use the following update statement to test your trigger:
UPDATE bb_product
SET salestart = '05-MAY-03', saleend = '12-MAY-03', saleprice = 9
WHERE idproduct = 10;
When you have finished, do a rollback and disable the trigger so that it does not affect other assignments.

Oracle 11g PL/SQL Joan Casteel Chapter 9 Hands-On Assignments Part 9-1 to 9-4

Assignment 9-1: creating a Tigger to Handle Product Restocking
Brewbean's has a couple of columns in the Product table to assist in inventory tracking. The REORDER column contains the stock level at which the product should be reordered. If the stock fall to this level. Brewbean's wants the application to insert a row in the BB_PRODUCT_REQUEST table automatically to alert the ordering clerk that additional inventory is needed. Brewbean's currently uses the reorder level amount as the quantity that should be ordered. This task can be handled by using a trigger.
1. Take out some scrap paper and a pencil. Think about the tasks the triggers needs to perform. Including checking. whether the new stock level falls below the reorder point. If so, check whether the product is already on order by viewing the product request table; if not, enter a new product request. Try to write the trigger code on paper. Even though you learn a lot by reviewing code, you improve your skills faster when you create the code on your own.
2. Open the c9reorder.txt file in the Chapter09 folder. Review this trigger code, and determine how it compares with your code.
3. In SQL Developer, create the trigger with the provided code.
4. Test the trigger with product ID 4. First, run the query shown in Figure 9-36 to verify the current stock data for this product. Notice that a sale of one more item should initiate a reorder.
5. Run the UPDATE statement shown in Figure 9-37. It should cause the trigger to fire. Notice the query to check whether the trigger fired and weather a product stock request was inserted in the BB_PRODUCT_REQUEST table.
6. Issue a ROLLBACK statement to undo these DML actions to restore data to its original state for use in later assignments.
7. Run the following statement to disable this trigger so that it doesn't affect other projects:
ALTER TRIGGER bb_reorder_trg DISABLE;


Assignment 9-2: Updating Stock Information When a Product Is Filled
Brewbean's has a BB_PRODUCT_REQUEST table where requests to refill stock level was inserted automatically via a trigger. After the stock level falls below the reorder level, this trigger fires and enters a request in the table. This procedure works great; however, when store clerks record that the product request has been filled by updating the table's DTRECD and COST columns. they want the stock level in the product table to be updated. Create a trigger named BB_REQFILL_TRG to handle this task, using the following steps as a guideline:
1. In SOL Developer, run the following INSERT statement to create a product request you can use in this assignment:
INSERT INTO bb_product_requect (idRequest, idProduct, dtRequest, qty)
VALUES (3, 5, SYSDATE, 45);
COMMIT;
2. Create the trigger (BB_REQFILL_TRG) so that it fires when a received date is entered in the BB_PRODUCT_REQUEST table. This trigger needs to modify the STOCK column in the BB_PRODUCT table to reflect the increased inventory.
3. Now test the trigger. First, query the stock and reorder data for product 5. as shown in Figure 9-38.
4. Now update the product request to record it as fulfilled by using UPDATE statement shown in figure 9-39.
5. Issue queries to verify that the trigger fired and the stock level of product 5 has been modified correctly. Then issue the ROLLBACK statement to undo modifications.
6. If you aren't doing assignment 9-3, disable the trigger so that it doesn't affect other assignments.


Assignment 9-3: Updating the Stock Level If a Product Fulfillment is Cancelled
The Brewbean's developers have made progress on the inventory-handling processes; however, they hit a snag when a store clerk incorrectly recorded a product request as fulfilled. When the product request was updated to record a DTRECD value, the product stock level was updated automatically via an existing trigger, BB_REQFILL_TRG. If the clerk empties the DTRECD column to indicate that the product request has been filled, the product stock level need to be corrected or reduced, too. Modify the BB_REQFILL_TRG to solve this problem.
1. Modify the trigger code from Assignment 9-2 as needed. Add code to check whether the DTRECD column already has a data in it and is now being set to NULL.
2. Issue the following DML actions to create or update rows that you can use to test the trigger:
INSERT INTO bb_product_request (idRequest, idProduct, dtRequest, qty, dtRecd, cost)
VALUES (4, 5, SYSDATE, 45, '15-JUN-2012', 225);
UPDATE bb_product
SET stock = 86
WHERE idProduct = 5;
COMMIT;
3. Run the following UPDATE statement to test the trigger, and issue queries to verify that the data has been modified correctly.
UPDATE bb_product_request
SET dtRecd = NULL
WHERE idRequest = 4;
4. Be sure to run the following statement to disable this trigger so that it doesn't affect other assignments:
ALTER TRIGGER bb_reqfill_trg DISABLE;


Assignment 9-4: Updating Stock Levels When an Order ls Cancelled
At times, customers make mistakes in submitting orders and call to cancel an order. Brewbean's wants to create a trigger that automatically updates the stock level of all products associated with a cancelled order and updates the ORDERPLACED column of the BB_BASKET table to zero, reflecting that the order wasn't completed. Create a trigger named BB_ORDCANCEL_TRG to perform this task, taking into account the following points:
The trigger need to fire when a new status record is added to the BB_BASKETSTATUS table and when the IDSTAGE column is set to 4, which indicates an order has been cancelled.
Each basket can contain multiple items in the BB_BASKETITEM table, so a CURSOR FOR loop might be a suitable mechanism for updating each item's stock level
Keep in mind that coffee can be ordered in half or whole pounds.
Use basket 6, which contains two items, for testing.
1.Run this INSERT statement to test the trigger:
INSERT INTO bb_basketstatus (idStatus, idBasket, idStage, dtStage)
VALUES (bb_status_seq.NEXTVAL, 6, 4, SYSDATE);
2. Issue the queries to confirm that the trigger has modified the basket's order status and product stock level correctly.
3. Be sure to run the following statement to disable this trigger so that it doesn't affect other assignments:
ALTER TRIGGER BB_ORDCANCEL_TRG DISABLE;

Oracle 11g SQL Joan Casteel Chapter 13 Hands-On Assignments

To perform the following activities, refer to the tables in the JustLee Books database.
1. Create a view that lists the name and phone number of the contact person at each publisher. Don't include the publisher's ID in the view. Name the view CONTACT.
2. Change the CONTACT view so that no users can accidentally perform DML operations on the view.
3. Create a view called HOMEWORK13 that includes the columns named Col1 and Col2 from the FIRSTATTEMPT table. Make sure the view is created even if the FIRSTATTEMPT table doesn't exist.
4. Attempt to view the structure of the HOMEWORK13 view.
5. Create a view that lists the ISBN and title for each book in inventory along with the name and phone number of the person to contact if the book needs to be reordered. Name the view
REORDERINFO.
6. Try to change the name of a contact person in the REORDERINFO view to your name. Was an error message displayed when performing this step? If so, what was the cause of the error message?
7. Select one of the books in the REORDERINFO view and try to change its ISBN. Was an error message displayed when performing this step? If so, what was the cause of the error message?
8. Delete the record in the REORDERINFO view containing your name. (If you weren't able to perform #6 successfully, delete one of the contacts already listed in the table.) Was an error message displayed when performing this step? If so, what was the cause of the error message?
9. Issue a rollback command to undo any changes made with the preceding DML operations.
10. Delete the REORDERINFO view.