Database

Need Help in Database Assignment?
We can help you if you are having difficulty with your Database Assignment. Just email your Database Assignment at admin@assignmentcache.com.
We provide help for students all over the world in Database Assignment.

Items 1 to 10 of 34 total

per page
Page:
  1. 1
  2. 2
  3. 3
  4. 4

Grid  List 

Set Ascending Direction
  1. MS Access 2010 Chapter 9 Lab 1 Navigation Form for the ECO Clothesline Database

    Microsoft Access 2010 Chapter 9 Lab 1 Creating Macros a Navigation Form and a PivotTable for the ECO Clothesline Database

    Regular Price: $25.00

    Special Price: $20.00

    Microsoft Access 2010 Chapter 9 Lab 1 Creating Macros a Navigation Form and a PivotTable for the ECO Clothesline Database


    Lab 1: Creating Macros, a Navigation Form, and a PivotTable for the ECO Clothesline Database
    Problem: ECO Clothesline would like an easy way to access the various tables, forms, and reports included in the database. This would make the database much easier to maintain and update.


    Instructions: If you are using Microsoft Access 2010 Comprehensive text, open the ECO Clothesline database that you used in Chapter 8. Otherwise, contact your instructor for more information about accessing the required files.


    Perform the following tasks:
    1. Create a macro named Forms and Reports that will include submacros to perform the following tasks:
    a. Open the Sales Rep Order Data form in read-only mode.
    b. Open the Sales Rep Master Form.
    c. Open Customer Master Form.
    d. Preview the Sales Rep Master List.
    e. Preview the Customer Balance Report.
    f. Preview the Customer Discount Report.
    g. Export the Sales Rep Master List in the PDF format.
    h. Export the Customer Balance Report in the PDF format.
    i. Export the Customer Discount Report in the PDF format.


    2. Create the menu form shown in Figure 9-80. The command buttons should use the macros you created in Step 1 to open the Customer Master Form, the Sales Rep Master Form, and the Sales Rep Order Data form.


    3. Create the menu form shown in Figure 9-81. The option group should use the macros you created in Step 1 to preview and export the three reports.


    4. Create a datasheet form for the Customer table and name the form Customer. Create a UI macro for the Customer form. When a user clicks a customer number on a row in the datasheet, the Customer Master Form should appear in a pop-up form.


    5. Create a datasheet form for the Sales Rep table and name the form Sales Rep. Create a UI macro for the Sales Rep form. When a user clicks a sales rep number on a row in the datasheet, the Sales Rep Master Form should appear in a pop-up form.


    6. Create the navigation form shown in Figure 9-82 for the ECO Clothesline database. Use the same design for your navigation form as the one illustrated in this chapter. For example, the Customer tab should display the Customer form you created in Step 4, and the Sales Rep tab should display the Sales Rep form you create in Step 5. The Forms tab should display the Form List form you created in Step 3, and the Reports tab should display the Reports List form you created in Step 3.


    7. Submit the revised database in the format specified by your instructor.

    Learn More
  2. CS371 Database Design Executive Summary

    CS371 Database Design Executive Summary

    Regular Price: $25.00

    Special Price: $20.00

    CS371 Database Design Executive Summary


    Write a proposal for the new database design to include:
    An executive summary for the database project


    Develop main project details
    Explain the issues that exist with maintaining the data in its current form.
    Explain the process of migrating the data in its current form to a well formed data model by highlighting the following in detail:
    Identify required attributes that need to be tracked
    Identify functional dependencies
    Show the redesign in 1NF, 2NF, and 3NF (similar to figures 7.29, 7.31, 7.33)


    Explain the project benefits of the data in its new form.


    Create the E-R diagram of your relational tables using MySQL Workbench and include in proposal.


    Propose some additional data items, which you believe may be beneficial to the University if tracked in your database (i.e. Descriptions, more info about the instructors and students, departments, etc). Describe the steps required to implement these additions.


    Identify project risks


    In addition to the proposal, in a separate file submit:
    Provide the SQL statement required to create at least one of your tables.
    Provide the SQL statement required to delete at least one of your tables.
    Provide the SQL statement required to create a view which includes Course #, Course Name, Time, and Days only.
    Identify at least one index, which you believe would improve performance. Explain your choice.
    If you add a course description attribute to your course table, there may be some performance impacts imposed by the addition of this "text" field.

    Learn More
  3. MIS582 Week 4 Course Project ABC Company Order Inventory System Database

    MIS582 Week 4 Course Project ABC Company Order Inventory System Database

    Regular Price: $25.00

    Special Price: $20.00

    MIS582 Week 4 Course Project ABC Company Order Inventory System Database


    PROJECT OVERVIEW:
    Scenario and Summary
    In this assignment, you will learn to create a physical database model in Visio from business requirements. To complete this assignment, you will need to be able to run Visio 2010, either through Citrix or installed on your workstation or laptop.


    Deliverables
    Name your Visio file as Lastname_Task2.vsd. Create and save your database model in your Visio file. When you are done, submit your document to the Week 4: Course Project Task 2 Dropbox.


    STEP 1
    Read the following business requirements closely to determine the entities and relationships needed to fulfill the requirements. The nouns in the paragraph will tell you the entities that will be needed. The adjectives that describe those nouns are the attributes of that entity. The verbs in the paragraph will help you determine the relationships between the entities.


    ABC Company Order Inventory System
    Database Requirements
    The ABC company has many stores in many locations that are divided in regions. The company supplies products to these stores. For the purposes of this database you may think that it is an American company in terms of regions such as Midwest.
    Each store is their customer/client. The company recognizes these stores by their store ID. They also keep track of the store name, address, and contact name.
    Stores may place orders online, or by phone/fax. Each order must be uniquely identified by an order number. Each order has a date on which the order was made, a date when the order was shipped, an employee who took the order (or it could be an online order), and the mode of payment.
    To make sure that the stores get their order on time, the stores are assigned to different warehouses in different regions. Currently, there is one warehouse for every region. Each warehouse is identified by its warehouse number. The company also tracks the address of the warehouse as well as a contact phone number.
    There are five regions: West, Midwest, Southwest, Southeast, and Northwest. Each region is identified by its unique name and unique number.
    Each warehouse holds many products for this company. A product may be found in many warehouses. The company keeps track of this inventory. At any point in time, the company must be able to find which warehouses carry this item, the quantity in stock, reorder point and whether this item is out of stock.
    Each product has a unique id, a description, a price. Each product could be on many orders, and an order may have multiple products on it. The company needs to track what quantity was ordered for each product and the total price for those products.
    Phone/Fax orders are processed by the employees of the company. The company keeps track of the employee’s id, first and last names, position, hire date, department they work for, their supervisor, and their salary.


    STEP 2
    • Run Visio 2010 either via Citrix or on your workstation.
    • Click on the Software and Database template group in the main window.
    • Double-click on the Database Model Diagram template to open a new file.
    • Save the file with the name Lastname_Task2.vsd. You will need to click the computer icon in the Save As window to see the different drives. Be sure to save the file to a local drive so it will be on your workstation.
    STEP 3
    Add an entity for each entity you identified in the requirements.
    • Drag the entity icon onto the drawing area in Visio.
    • In the Database Properties window, add a physical name to identify it.
    STEP 4
    For each entity, create a list of attributes you think would be useful to describe the entity.
    • Select an entity in the drawing area of Visio.
    • In the Database Properties window, select the Columns category.
    • Use the table to add your attributes to the selected entities.
    • Select the primary key (PK).
    STEP 5
    Set the diagram to use crow's feet notation.
    • On the Database tab, in the Manage group, click Display Options.
    • In the Database Document Option dialog, select the Relationship tab.
    • Select the Crow's Feet check box, and then click OK.
    STEP 6
    Draw relationships between your entities.
    • Drag the relationship icon onto a blank part of the drawing area.
    • Connect the two ends to each of the two entities in the relationship. The parent entity must have a PK defined. The entity will be outlined in bold red lines when it connects to one end of the relationship.
    STEP 7
    Set the cardinality of your relationships.
    • Select a relationship line in the drawing area that is connecting two entities.
    • In the Database Properties window, select the Miscellaneous category.
    • Select the cardinality for the selected relationship.
    STEP 8
    When you are done, save the file on your local hard drive and upload it to the Week 4: Course Project Week 4 Task 2 Dropbox. Your file should have the following filename format: Lastname_Task2.vsd.
    For instructions on how to copy files between the Citrix server and your local machine, watch the iLab tutorial, Copying Files From Citrix, located in the iLab menu tab under Course Home.


    Rubrics
    Points for will be awarded according to the following rubrics.
    • 10 Points: Visio drawing: E-R model is provided as a Visio diagram.
    • 10 Points: Entities: A minimum of six entities are present.
    • 10 Points: Attributes: There are more than two attributes per entity; in most cases there should be several.
    • 10 Points: Relationships: A minimum of six correct relationships are present.
    • 10 Points: Correct cardinality is specified on each relationship with crow's foot notation.


    Tutorials to Consult With
    DBP-e13-Appendix-F.pdf in the folder titled Visio Instructions Documents in Doc Sharing for how to use Visio 2013.
    kroenke_dbp12e_appendix_f.pdf in the folder titled Visio Instructions Documents in Doc Sharing for how to use Visio 2010.

    Learn More
  4. MSCD610 Exam SQL

    MSCD610 Oracle Database Exam Oracle 11g SQL 2nd Casteel

    Regular Price: $30.00

    Special Price: $25.00

    MSCD610 Oracle Database Exam Oracle 11g SQL 2nd Casteel


    True/False (2 points each)
    Indicate whether the sentence or statement is true or false.
    1. A one-to-many relationship means that an occurrence of a specific entity can only exist once in each table.
    2. A table name can consist of numbers, letters, and blank spaces.
    3. A constraint can only be created as part of the CREATE TABLE command.
    4. The MODIFY clause is used with the ALTER TABLE command to add a PRIMARY KEY constraint to an existing table.
    5. If a FOREIGN KEY constraint exists, then a record cannot be deleted from the parent table if that row is referenced by an entry in the child table.
    6. By default, the lowest value that can be generated by a sequence is 0.
    7. Search conditions for data contained in non-numeric columns must be enclosed in double quotation marks.
    8. Data stored in multiple tables can be reconstructed through the use of an ORDER BY clause.
    9. Rows can be updated through a simple view as long as the operation does not violate existing constraints and the view was created with the WITH READ ONLY option.
    10. By default, the column headings displayed in a report are in upper-case characters.


    Multiple Choice (3 points each)
    Identify the letter of the choice that best completes the statement or answers the question.
    11. Suppose that a patient in a hospital can only be assigned to one room. However, the room may be assigned to more than one patient at a time. This is an example of what type of relationship?
    a. one-to-many c. one-to-all
    b. many-to-many d. one-to-one


    Contents of the BOOKS table
    12. Which of the following will display the new retail price of each book as 20 percent more than it originally cost?
    a. SELECT title, cost+.20 "New Retail Price" FROM books;
    b. SELECT title, cost*.20 "New Retail Price" FROM books;
    c. SELECT title, cost*1.20 "New Retail Price" FROM books;
    d. none of the above


    Structure of the CUSTOMERS table
    13. Which of the following commands will increase the size of the CITY column in the CUSTOMERS table from 12 to 20 and increase size of the LASTNAME column from 10 to 14?
    a. ALTER TABLE customers
    MODIFY (city VARCHAR2(+8), lastname VARCHAR2(+4));
    b. ALTER TABLE customers
    MODIFY (city VARCHAR2(20), lastname VARCHAR2(14));
    c. ALTER TABLE customers
    MODIFY (city (+8), lastname (+4));
    d. ALTER TABLE customers
    MODIFY (city (20), lastname (14));


    14. Which of the following statements about the FOREIGN KEY constraint is incorrect?
    a. The constraint exists between two tables, called the parent table and the child table.
    b. When the constraint exists, by default a record cannot be deleted from the parent table if matching entries exist in the child table.
    c. The constraint can reference any column in another table, even a column that has not been designated as the primary key for the referenced table.
    d. When the keywords ON DELETE CASCADE are included in the constraint definition, a corresponding child record will automatically be deleted when the parent record is deleted.


    15. Which of the following SQL commands will require the user RTHOMAS to change the account password the next time the database is accessed?
    a. ALTER USER rthomas PASSWORD EXPIRE ;
    b. ALTER USER rthomas CHANGE PASSWORD;
    c. ALTER USER rthomas UPDATE PASSWORD;
    d. ALTER USER rthomas EXPIRE PASSWORD;


    16. To instruct Oracle to sort data in ascending order, enter ____ after the column name in the ORDER BY clause.
    a. Asc c. ascending
    b. A d. either a or c


    17. Which of the following is an accurate statement?
    a. When the LOWER function is used in a SELECT clause, it will automatically store the data in lower-case letters in the database table.
    b. When the LOWER function is used in a SELECT clause, the function stays in affect for the remainder of that user's session.
    c. When the LOWER function is used in a SELECT clause, the function only stays in affect for the duration of that SQL statement.
    d. none of the above


    18. Which of the following functions allows for different options, depending upon whether a NULL value exists?
    a. NVL c. IFNVL
    b. IFNL d. NVL2


    Contents of the ORDERS table
    19. Based on the contents of the ORDERS table, which of the following SQL statements will display the number of orders that have not been shipped?
    a. SELECT order#, COUNT(shipdate)
    FROM orders
    WHERE shipdate IS NULL;
    b. SELECT order#, COUNT(shipdate)
    FROM orders
    WHERE shipdate IS NULL
    GROUP BY order#;
    c. SELECT COUNT(shipdate)
    FROM orders
    WHERE shipdate IS NULL;
    d. SELECT COUNT(*)
    FROM orders
    WHERE shipdate IS NULL;


    20. Which of the following is not an example of formatting code available with the FORMAT option of the COLUMN command?
    a. Z
    b. 9
    c. ,
    d. .



    Completion (4 points each)
    Complete each sentence or statement.
    21. A(n) ____________________ is a group of interrelated files.
    22. In an arithmetic expression, multiplication and ____________________ are always solved first in Oracle.
    23. If a constraint applies to more than one column, the constraint must be created at the ______Table______________ level.
    24. After a value is generated, it is stored in the ____________________ pseudocolumn so it can be referenced again by a user.
    25. The ____________________ function is used to round numeric fields to a stated position.



    SQL
    26. (5 points) Consider an employee database with relations where the primary keys are underlined defined as:
    EMPLOYEE (employee name, street, city)
    WORKS (employee name, company_name, salary)
    A – Using sql functions as appropriate, write a query to find companies whose employees earn a higher salary, on average, than the average salary at ABC Corporation


    27. (7 points) Write a SQL script to create this relational schema. Execute the script against the ORACLE database to implement physical database tables. Integrity constraints are listed below.
    EMPLOYEE (name, SSN, BDate, Sex, Salary, SuperSSN, DNO)
    DEPARTMENT (DName, DNumber, MGRSSN, MGRStartDate)
    DEPTLOCATION (DNumber, DLocation)
    PROJECT (PName, PNumber, PLocation, DNum)
    WORKSON (ESSN, PNO, Hours)
    DEPENDENT (ESSN, DEPENDENT_NAME, Sex, BDate, Relationship)


    Integrity Constraints:
    Primary key = Foreign Key
    EMPLOYEE.SSN = DEPENDENT.ESSN
    EMPLOYEE.SSN = WORKSON.ESSN
    EMPLOYEE.SSN = DEPARTMENT.MGRSSN
    EMPLOYEE.SSN = EMPLOYEE.SuperSSN
    DEPARTMENT.DNumber = EMPLOYEE.DNO
    DEPARTMENT.DNumber = DEPTLOCATION.DNumber
    DEPARTMENT.DNumber = PROJECT.DNum
    PROJECT.PNumber = WORKSON.PNO


    28. (18 points) Write SQL syntax to resolve the following queries.
    - Find the names of all employees who are directly supervised by the employee named “John Doe”
    - List the name of employees whose salary is greater than the average salary of his or her corresponding department
    - For each department, retrieve the department name and the average salary of all employees working in that department.

    Learn More
  5. Access 2010 Chapter 5 Babbage CPA Firm database

    Microsoft Access 2010 Chapter 5 Apply Your Knowledge

    Regular Price: $25.00

    Special Price: $20.00

    Microsoft Access 2010 Chapter 5 Apply Your Knowledge
    Reinforce the skills and apply the concepts you learned in this chapter.


    Adding Date/Time and OLE Fields, Using an Input Mask Wizard, and Querying Date/Time Fields


    Instructions: Start Access. If you are using the Microsoft Access 2010 Complete or the Microsoft Access 2010 Comprehensive text, open the Babbage CPA Firm database that you used in Chapter 4. Otherwise, see your instructor for information on accessing the files required in this book.


    Perform the following tasks:
    1. Add the Start Date and Picture fields to the Bookkeeper table structure, as shown in Figure 5 – 84. Create an input mask for the Start Date field. Use the Short Date input mask type.
    2. Add the data shown in Table 5–1 to the Bookkeeper table.
    3. Query the Bookkeeper table to find all bookkeepers who started after January 1, 2012. Include the Bookkeeper Number, First Name, Last Name, Hourly Rate, and Earnings YTD in the query results. Save the query as Start Date Query.
    4. Submit the revised database in the format specified by your instructor.

    Learn More
  6. Chapter 7 Lab 1  ECO Clothesline Database

    Microsoft Access 2010 Chapter 7 Lab 1 Querying the ECO Clothesline Database Using SQL

    Regular Price: $25.00

    Special Price: $20.00

    Microsoft Access 2010 Chapter 7 Lab 1 Querying the ECO Clothesline Database Using SQL

    Problem: The management of ECO Clothesline wants to learn more about SQL and has determined a number of questions it wants SQL to answer. You must obtain answers to the questions posed by management.

    Instructions: If you are using the Microsoft Access 2010 Complete or the Microsoft Access 2010 Comprehensive text, open the ECO Clothesline database that you used in Chapter 6. Otherwise, see your instructor for information on accessing the files required in this book.

    Perform the following tasks:
    1. Find all customers where the customer type is SAL. Include the Customer Number, Customer Name, and Sales Rep Number fields in the result. Save the query as Lab 7-1 Step 1 Query.
    2. Find all customers located in Tennessee (TN) with a paid amount greater than $1,500.00. Include the Customer Number, Customer Name, and Amount Paid fields in the result. Save the query as Lab 7-1 Step 2 Query.
    3. Find all customers whose names begin with the letter, C. Include the Customer Number, Customer Name, and City fields in the result. Save the query as Lab 7-1 Step 3 Query.
    4. List all cities in descending order. Each city should appear only once. Save the query as Lab 7-1 Step 4 Query.
    5. Display the customer number, name, sales rep number, first name, and last name for all customers. Sort the results in ascending order by sales rep number and customer number. Save the query as Lab 7-1 Step 5 Query.
    6. List the average balance amount grouped by sales rep number. Name the average balance as Average Billed. Save the query as Lab 7-1 Step 6 Query.
    7. Find the customer number and name for every pair of customers who are located in the same city. Save the query as Lab 7-1 Step 7 Query.
    8. Find the customer numbers, names, and sales rep numbers for all customers that have open orders. Use the alias O for the Open Orders table and C for the Customer table. Each customer should appear only once. Save the query as Lab 7-1 Step 8 Query.
    9. Use a subquery to find all sales reps whose customers are located in Pineville. Save the query as Lab 7-1 Step 9 Query.
    10. Find the average balance amount for sales rep 44. Save the query as Lab 7-1 Step 10 Query.
    11. Submit the revised database in the format specified by your instructor.

    Learn More
  7. Microsoft Access 2010 Chapter 8 Lab 1 Customer View and Update Form

    Microsoft Access 2010 Chapter 8 Lab 1 Applying Advanced Form Techniques to the ECO Clothesline Database

    Regular Price: $25.00

    Special Price: $20.00

    Microsoft Access 2010 Chapter 8 Lab 1 Applying Advanced Form Techniques to the ECO Clothesline Database


    Lab 1: Applying Advanced Form Techniques to the ECO Clothesline Database
    Problem: ECO Clothesline needs a form for the Customer table that will allow users to update data in the table ECO also needs a form to display open orders data for sales reps.


    Instructions: If you are using Microsoft Access 2010 Comprehensive text, open the ECO Clothesline database that you used in Chapter 7. Otherwise, see your instructor for information on accessing the files required in this book.


    Perform the following tasks:
    1. Create a query that includes the Sale Rep Number field from the Sales Rep tan;e, the Customer Number field from the Customer table and the Order Number and Amount fields from the Open Orders table. Sort the query in ascending order by Sales Rep Number, Customer Number, and Order NUmber Save the query as Sales Rep and Orders.


    2. Create the form shown in Figure 8-95. total Amount is a calculated control and is the sum of the Balance and Amount Paid amounts. Format the Total Amount control as Currency with two decimal places. (Hint: Use the property sheet for the control.) the form includes command buttons, a combo box for the Sales Rep Number field, and a combo box to search for customers by name. Be sure to sort the customer by names in ascending order, place a rectangle around the combo box, and update the combo box. The user should not be able to tab to the combo box. When the Add Record button is clicked, the insertion point should be in the customerNumber field. The form is similar in style to the form shown in Figure 8-1 on page AC 466.


    3. Create the Sales Rep Order Data form shown in Figure 8-96. The subform that appears in the Datasheet tab uses the Sales Reps and Orders query (Figure 8-96a). The chart ghat appears in the Chart tab uses the same query (Figure 8-96b).


    4. Submit the revised database in the format specified by your instructor.

    Learn More
  8. Microsoft Access 2010 Chapter 11 Lab 1 Designing a Database for Phils Limousine

    Microsoft Access 2010 Chapter 11 Lab 1 Designing a Database for Phils Limousine

    Regular Price: $25.00

    Special Price: $20.00

    Microsoft Access 2010 Chapter 11 Lab 1 Designing a Database for Phils Limousine


    Lab 1: Designing a Database for Phil’s Limousine
    Instructions: Consider the following set of requirements for Phil’s Limousine Service:
    • For each driver, the company keeps track of the driver’s name (first name and last name), driver’s address (street, city, state, postal code), driver’s license number, home telephone number, and cell telephone number.
    • For each limousine, the company keeps track of a unique limousine ID number, limousine license number, limousine color, and number of passengers the limousine can hold.
    • A driver can be assigned to more than one limousine. A limousine will have multiple drivers but only one driver at a time.
    Based on these requirements, do the following:
    1. Identify and list the entities and the attributes of those entities.
    2. Identify and list the functional dependencies.
    3. Create a set of 3NF relations using the shorthand notation given in the chapter. Be sure to identify all primary keys and foreign keys appropriately.
    Submit your database design in the format specified by your instructor.

    Learn More
  9. Assignment 9-4 Updating Stock Levels When an Order ls Cancelled

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

    Regular Price: $25.00

    Special Price: $20.00

    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;

    Learn More
  10. Microsoft Access 2010 Chapter 7 Lab 2 Querying the Walburg Energy Alternatives Database Using SQL

    Microsoft Access 2010 Chapter 7 Lab 2 Querying the Walburg Energy Alternatives Database Using SQL

    Regular Price: $30.00

    Special Price: $25.00

    Microsoft Access 2010 Chapter 7 Lab 2 Querying the Walburg Energy Alternatives Database Using SQL


    Problem: The manager of the Walburg Energy Alternatives store would like to learn more about SQL and has determined a number of questions he wants SQL to answer. You must obtain answers to the questions posed by the manager.
    Instructions: If you are using the Microsoft Access 2010 Complete or the Microsoft Access 2010 Comprehensive text, open the Walburg Energy Alternatives database that you used in Chapter 6. Otherwise, see your instructor for information on accessing the files required in this book.


    Perform the following tasks:
    1. Find all records in the Item table where the difference between the cost of the item and the selling price of the item is less than $0.25 (25 cents). Display the item number, description, cost, and selling price in the query result. Save the query as Lab 7-2 Step 1 Query.
    2. Display the item number, description, and profit (selling price – cost) for all items. Name the computed field Profit. Save the query as Lab 7-2 Step 2 Query.
    3. Find all items where the description begins with the letter, W. Include the item number and description in the result. Save the query as Lab 7-2 Step 3 Query.
    4. Display the vendor name, item number, description, and cost for all items where the number on hand is less than 10. Sort the results in ascending order by vendor name and description. Save the query as Lab 7-2 Step 4 Query.
    5. Find the average cost by vendor. Name the computed field Average Cost. Save the query as Lab 7-2 Step 5 Query.
    6. Find the total number of reordered items for each item in the Reorder table. Name the computed field Total Ordered. Include the item number in the result. Save the query as Lab 7-2 Step 6 Query.
    7. Add the following record to the Reorder table.
    Item Number Date Ordered Number Ordered
    8590 4/12/2012 3
    Save the steps to add the record as Lab 7-2 Step 7 Query.
    8. Update the Number Ordered field to 5 for those records where the Item Number is 8590 and the date ordered is 4/12/2012. Save the steps to update the record as Lab 7-2 Step 8 Query.
    9. Delete all records where the Item Number is 8590 and the date ordered is 4/12/2012. Save the steps to delete the record as Lab 7-2 Step 9 Query.
    10. Submit the revised database in the format specified by your instructor.

    Learn More

Items 1 to 10 of 34 total

per page
Page:
  1. 1
  2. 2
  3. 3
  4. 4

Grid  List 

Set Ascending Direction