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. 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
  2. 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
  3. IM300 Week 4 Hotel Database Part 3 DDL script

    IM300 Week 4 Integrity and Constraints Hotel Database Part 3

    Regular Price: $25.00

    Special Price: $20.00

    IM300 Week 4 Integrity and Constraints Hotel Database Part 3


    Resources: SR-bi-003 – Hotel Database, the results from SR-bi-003 – Hotel Database Parts 1 & 2, Oracle® Database Express® and Microsoft® Visio® Virtual Desktop.


    Complete Service Request SR-bi-003 - Hotel Database–Part 3.


    Normalize the entities and attributes to at least the third normal form.


    Use Microsoft® Visio® diagramming tools to create an entity-relationship diagram (ERD) that demonstrates the logical design of your normalized database.


    Create and alter tables in the city’s Oracle® Database Express® database to implement the ERD design.


    Generate a new DDL script.


    Create a fax cover sheet addressed to the project manager for the ERD and a newly generated DDL script to show your progress on the project.

    Learn More
  4.  ITS407 Module 8 Entities and Attributes

    ITS407 Module 8 Project Ace Software MySQL Database For Mom and Pop Johnson video store

    Regular Price: $25.00

    Special Price: $20.00

    ITS407 Module 8 Project Ace Software MySQL Database For Mom and Pop Johnson video store


    You are a database consultant with Ace Software, Inc., and have been assigned to develop a database for the Mom and Pop Johnson video store in town. Mom and Pop have been keeping their records of videos and DVDs purchased from distributors and rented to customers in stacks of invoices and piles of rental forms for years. They have finally decided to automate their record keeping with a relational database.


    You sit down with Mom and Pop to discuss their business, and watch their operation for about a week. You discover quickly that a video and a DVD are both copies of a movie kept in a separate plastic case that is rented out. They have several copies of each movie they rent, therefore there are several videos and DVDs for each movie title. You learn that in their inventory they have several thousand videos and DVDs, which they get wholesale from about a half dozen distributors. The video and DVD prices to them are based on the quantity of their shipment and the past business they have done with each company.


    The price of a DVD for a movie might be different than the price of a video for the same movie, even from the same distributor. Each distributor provides different types of movies (e.g., suspense, horror, mystery, comedy, etc.). A single distributor may provide several different types of movies in both video and DVD format. It is possible to obtain the same movie from multiple distributors and at different wholesale prices.


    Each video and DVD has a unique identification number that Mom and Pop assign in their inventory, in addition to the distributor's serial number for the item. Each movie also has a unique identification number Mom and Pop assign in addition to the title and any movie IDs the distributors use in their electronic catalogs. Distributors provide electronic catalogs to Mom and Pop, and the information from these catalogs must be included in the database.


    Mom and Pop need to record when a video or DVD is rented, when a video or DVD is returned, and all customer charges such as late and damaged fees, failure to rewind fees, and taxes. They need a report of which videos are returned late because there are standard and late charges. On occasion there are discount prices for certain movies or types of movies. Customers want to rent movies based on actors or actresses, running length, type of movie, rating, year released, the director, and the Academy Awards won (by the movie, the actors, the actresses and/or the directors). Customers also want to know how many videos they have rented in the last month, year, and so forth. Mom and Pop need to keep only basic information on customers in their database, such as name, address, telephone numbers, etc.


    There must be no limit to the number of video and/or DVD copies of a movie that Mom and Pop can have in their inventory. Video/DVD ID numbers, movie ID numbers, and distributor ID numbers for videos, DVDs, and movies are all different. Also, each movie must be able to have an unlimited number of actors, actresses, directors, and Academy Awards (i.e., Oscars). Other types of awards (e.g., Golden Globe, People's Choice, etc.) are not of interest for this application. The rental of equipment, sale of videos, DVDs, popcorn, etc., is not to be kept in the database.


    1) Identify and describe the entities and their attributes.
    2) Develop relationship sentence pairs.
    3) Draw an ERD with Visio.
    4) Develop metadata from the ERD and document in an Excel spreadsheet.
    5) Using your selected RDBMS (SQL Server, Oracle, or MySQL), develop and execute an SQL script file of DDL SQL to create the database tables in the metadata document.
    6) Using your selected RDBMS, develop and execute an SQL script file of DML SQL INSERT statements to populate the tables using SQL INSERT statements for at least 5 rows of data per table.
    7) Using your selected RDBMS develop and execute an SQL script file to:
     a) Show the contents of all tables
     b) Retrieve all of the customers' names, account numbers, and addresses (street and zip code only), sorted by account number
     c) Retrieve all of the DVDs rented in the last 30 days and sort in chronological rental date order
     d) Update a customer name to change their maiden names to married names. You can choose which row to update. Make sure that you use the primary key column in your WHERE clause to affect only a specific row.
     e) Delete a specific customer from the database. You can choose which row to delete. Make sure that you use the primary key column in your WHERE clause to affect only a specific row.


    The metadata should be submitted in an Excel spreadsheet. All other outputs for the database design, SQL code, and SQL results should be submitted in a single Word file in order, by step, and clearly labeled.

    Learn More
  5. Microsoft Access 2010 Chapter 7 Using SQL Camashaly Design database

    Microsoft Access 2010 Chapter 7 Using SQL Camashaly Design database AC 418 - AC 457

    Regular Price: $25.00

    Special Price: $20.00

    Microsoft Access 2010 Chapter 7 Using SQL Camashaly Design database AC 418 - AC 457


    Read and work through project 7 pp. AC 418- AC 457.


    The owners of Camashaly Design want to be able to use the extended data management capabilities available through SQL. In becoming familiar with SQL, Camashaly would like to create a wide variety of SQL queries.
    Similar to creating queries in Design view, SQL provides a way of querying relational databases. In SQL, however, instead of making entries in the design grid, you type commands into SQL view to obtain the desired results, as shown in Figure 7 – 1a. You then can click the View button to view the results just as when you are creating queries in Design view. The results for the query in Figure 7 – 1a are shown in Figure 7 – 1b.


    Overview
    As you read through this chapter, you will learn how to create SQL queries by performing these general tasks:
    • Create queries involving criteria.
    • Sort the results of a query.
    • Group records in a query and perform group calculations.
    • Join tables in queries.
    • Create a query that involves a subquery.
    • Update data using the INSERT, UPDATE, and DELETE commands.


    SQL Query Guidelines.
    When querying a database using SQL, you must design queries appropriately, identifying the required fields, tables, criteria, sorting, grouping, and operations to use. Querying in SQL also requires proper expression of queries using SQL clauses and formatting.
    1. Select the fields for the query. Examine the requirements for the query you are constructing to determine which fields are to be included.
    2. Determine which table or tables contain these fields. For each field, determine the table in which it is located.
    3. Determine criteria. Determine any criteria that data must satisfy to be included in the results. If there are more than two tables in the query, determine the criteria to be used to ensure the data matches correctly.
    4. Determine sort order. Is the data to be sorted in some way? If so, by what field or fields is it to be sorted?
    5. Determine grouping. Is the data to be grouped in some way? If so, by what field is it to be grouped? Identify any calculations to be made for the group.
    6. Determine any update operations to be performed. Determine whether rows need to be inserted, changed, or deleted. Determine the tables involved.
    When necessary, more specific details concerning the above decisions and/or actions are presented at appropriate points in the chapter. The chapter also will identify the use of these guidelines in creating SQL queries such as the one shown in Figure 7 – 1.


    To Start Access
    The following steps, which assume Windows 7 is running, start Access based on a typical installation. You may need to ask your instructor how to start Access for your computer.
    Click the Start button on the Windows 7 taskbar to display the Start menu.
    Type Microsoft Access as the search text in the ‘Search programs and files text box and watch the search results appear on the Start menu.

    Learn More
  6. Microsoft Access 2010 Chapter 10 Administering a Database System Clients and Trainers Database

    Microsoft Access 2010 Chapter 10 Administering a Database System AC 602 to AC 666

    Regular Price: $25.00

    Special Price: $20.00

    Microsoft Access 2010 Chapter 10 Administering a Database System AC 602 to AC 666


    Read and work through Project 10, starting on page AC 602 and work through AC 672
    Save the Client-Trainer Query, p. AC 616


    Project - Administering a Database System
    Camashaly Design realizes the importance of database administration, that is, the importance of administering its database system properly. Making a database available on the Web (Figure 10 – 1) is part of this activity. Another important activity in administering databases is the creation of custom templates, application parts, and data type parts. Application parts and data type parts are templates that you can add to your database to extend its functionality. Clicking an application part adds to your database a predetermined collection of objects such as tables, queries, forms, reports, and/or macros. Clicking a data type part adds a predetermined collection of felds to a table.


    Camashaly management realizes that database administration encompasses a wide variety of activities (Figure 10 – 2). Database administration can include conversion of an Access database to an earlier version of Access. Database administration usually includes such activities as analyzing tables for potential problems, analyzing performance to see if changes are warranted to make the system perform more effciently, and documenting the various objects in the database. It can include creating custom categories and groups in the Navigation Pane as well as changing table and database properties. It also can include the use of feld properties in such tasks as creating a custom input mask and allowing zero-length strings. It can include the creation of indexes to speed up retrieval. The inclusion of automatic error checking is part of the administration of a database system. Understanding the purpose of digital certifcates and the Trust Center is critical to the database administration function.
    Another important area of database administration is the protection of the database. This protection includes locking the database through the creation of an ACCDE file to prevent unauthorized changes from being made to the VBA source code or to the design of forms and reports. Splitting the database into a front-end and a back-end database is another way to protect the functionality and effciency of a database.

    Learn More
  7. ITCO231 Assignment 1 AdventureWorks Database

    ITCO231 Unit 2 AdventureWorks Database

    Regular Price: $25.00

    Special Price: $20.00

    ITCO231 Unit 2 AdventureWorks Database


    Task 1
    Click here to download and install the AdventureWorks LT2012 database into the SQL environment. Unzip the file and store the data file and the log file into the Data folder within the folder Program Files (x86)/Microsoft SQL Server/MSSQL11.SQLEXPRESS/MSSQL/DATA .
    Choose the appropriate file based on whether you are running SQL Server 2012 or 2008.


    Task 2
    Generate a database diagram for all the tables in the database.
    Include a screenshot of this diagram in your Word document.


    Task 3
    Write SELECT statements for the following questions:
    For each of these statements, include a screenshot of the SQL statements and results. Make sure to include the statement execution, including the resulting data.
    Display all columns and all rows from the Customer table.
    Display the ProductID, ProductNumber and ListPrice for all rows in the Products table.
    Modify query 2 so that the column headings are as follows:
    "ID," "Number," and "List Price"
    Display the CustomerID, full customer name, and CountryRegion for all customers.
    Display the CustomerID, full customer name, and CountryRegion for all customers.
    Name the columns "ID," "Name," and "Region" (respectively), and sort the data by the company name.


    Combine all of the screenshots into a single Word document, and submit this for grading.

    Learn More
  8. MIS582 Week 6 Course Project ABC Company Order Inventory System Database

    MIS582 Week 6 Course Project ABC Company Order Inventory System Database

    Regular Price: $25.00

    Special Price: $20.00

    MIS582 Week 6 Course Project ABC Company Order Inventory System Database


    PROJECT OVERVIEW:
    Scenario and Summary
    In this assignment, you will create and populate a MySQL database based on the ERD you created in task2. Update the ERD you submitted for task2 with the corrections required by your instructor. To help you with this task, your instructor will provide you with the data dictionary of the database. This will provide you information on every table that you must create for the ABC Company database. Every entity can be found listed in the data dictionary along with the PK/FK information as well as the data types for the attributes. These data types are MySQL data types so they can go directly in the create table statements. Using the corrected ERD and the data dictionary provided, write the necessary MySQL statements to create the ABC company database. To complete this assignment, you will need to be able to run MySQL via Omnymbus.


    Deliverables
    1. Corrected ERD based on the instructor feedback and data dictionary. The data dictionary titled COURSE PROJECT Week6Task3DataDictonary.xlsx can be found in the folder titled Course Project Documents. Save your file as Lastname_Task3_revisedERD.vsd.
    2. Script file as Lastname_task3.sql which includes all the drop, create, alter, and insert statements needed to create the ABC Company database.
    3. Word document showing the run result of the script file saved as Lastname_task3Result.docx.
    4. When you are done, upload a zip file containing the three files to the Week 6: Course Project Week 6 Task 3 Dropbox.


    STEP 1
    Revise the ERD you submitted for the previous task with the feedback provided by your instructor and the help of the given data dictionary document.


    STEP 2
    • Write create table statements for each table in the database in a file. Use the data types given in the data dictionary. Make sure every table has primary key(s), foreign keys if required, and not nulls if required. (Refer to data dictionary for this information)
    • Save the file with the name Lastname_Task3.sql.
    • Login to your MySQL account via Omnymbus.
    • Create the database following the naming convention which is databaseName_loginId. You may use MIS582Project_####, replace the #### with your four-digit username.
    • Click on the database name you just created.
    • Run the script file by choosing Run SQL from file button. Select From uploaded file and find your file by clicking on the browse button. Once you find the file, click on the Execute button.
    • If there are no errors, all tables will be successfully created. You may check them by adding SHOW TABLES at the end of your script file.
    • If you have errors, correct those errors and execute the script again until it is error free.
    • You may test each create table statement individually by clicking the Execute SQL button and pasting the create statement in the window.
    • Be sure to drop all tables before running the script file to create all tables at once.


    STEP 3
    Adding data to your database.
    • Write insert statements to successfully insert data into each table.
    • A minimum of 3 rows are required for each table.
    • Check the data in each table by running a SELECT * FROM table query.
    • You may add these statements to your script file and run everything together.


    Rubrics
    Points for will be awarded according to the following rubrics.
    • 10 Points: Visio drawing: Revised E-R model provided as a Visio diagram.
    • 15 Points: CREATE STATEMENTS: Successfully creating all tables.
    • 5 Points: PRIMARY KEYS: Correct primary keys created for all tables.
    • 5 Points: FOREIGN KEYS: All required foreign keys created.
    • 5 Points: ALTER STATEMENT: Any alter statements required.
    • 10 Points: INSERT STATEMENTS: Minimum three rows per table found.

    Learn More
  9. Chapter 3 Case Problem Level 1 Antiques

    Succeeding in Business with Access Chapter 3 Case Problem 1 and 3

    Regular Price: $25.00

    Special Price: $20.00

    Succeeding in Business with Access 2010
    Chapter 3 Analyzing Data for Effective Decision Making

    Case Problem Level 1 - managing Customer Information for NHD Development Group Inc.
    Antiques.accdb complete solution from Step 1 - Step 12.

    Case Problem Level 2 - Retrieving Employee Information for MovinOn Inc.
    MovinOn.accdb complete solution from Step 1 - Step 12.

    Learn More
  10. MSCD610 Week 7 Logical ERD

    MSCD610 Week 7 Course Project Details Hotel Database

    Regular Price: $30.00

    Special Price: $25.00

    MSCD610 Week 7 Course Project Details Hotel Database


    The course project is to develop a Data Model and Database Design for a set of business case requirements. Students should informally review their ER model with the facilitator in week 4 or 5.


    This project should follow a top-down database design process and produce the following outputs:
    1. A set of business information requirements in the form of a business case abstract.


    2. A complete ERD that models those requirements.
    This model should include the following:
    a. A definition of each entity
    b. The unique identifier for each entity
    c. The attributes associated with each entity
    d. The relationships between the entities including their cardinality, optionally, and names


    3. A physical implementation of a database from the logical design (ERD).
    The tables should be normalized to 3NF. For this design include:
    a. The name for each table
    b. The primary key for the table, and any secondary keys
    c. Any foreign keys
    d. A data dictionary
    e. Any sample data available


    4. The DDL scripts for implementing the physical database design for this database. Include the referential integrity constraints for these tables. Also include the DDL for any needed indexes and/or views. If necessary, indicate any database tuning which is anticipated on this database.


    5. Create the designed Oracle database, and load it with sample data. Then print the definition of the tables, indexes, and any views. Print sample report(s) showing the query definitions in the business requirements have been met.

    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