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 11 to 20 of 35 total

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

Grid  List 

Set Ascending Direction
  1. 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
  2. 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
  3. 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
  4. 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
  5.  MIS562 Week 2 Homework Oracle Queries

    MIS562 Week 2 Homework Oracle Queries Database

    Regular Price: $25.00

    Special Price: $20.00

    MIS562 Week 2 Homework Oracle Queries

    Create the tables from demo.sql script as follows:
    Download and copy the demo.sql file from the Doc Sharing to the C:\temp directory. At the sql prompt enter the command @V:\temp\demo.sql.  This will create some tables and insert data into them. View the script in notepad to determine the table names that were created. Use the describe command to view the structure of the tables. Please use the instructions in the Submission Requirements to create and submit a document with your solutions. Submissions that do not meet the Requirements will be returned with a grade of zero and will be accepted and graded when corrected and re-submitted. Anyone having difficulties can obtain assistance in the TDAs.



    Write SQL statements to create result tables showing the solutions to the following requests.
    1. (4 pts) List all employee information in department 30.
    2. (4 pts) List employees name, job, and salary that is a manager and has a salary > $1,000
    3. (4 pts) Repeat exercise 2 for any employee that is not a manager or earns a salary > $1,000
    4. (4 pts) Show all employee names and salary that earn between $1,000 and $2,000.  Use the between operator.
    5. (4 pts) Select all employees that are in department 10 and 30. Use the IN operator.
    6. (4 pts) Select all employee names with an “A” in the first position of the employee name.  Use the substring function or a wild card.
    7. (4 pts) Select all employees with an “A” as the second character of their name.  Use a wildcard.
    8. (4 pts) List the employee names in alphabetical sequence.
    9. (4 pts) List the job, salary, and employee name in job order and then salary in descending order.
    10. (4 pts) Show a list of different jobs.  Eliminate repeating values.
    11. (4 pts) Show employee names, salary that has not earned a commission yet.
    12. (4 pts) Show the employee name, salary, commission, and the total of salary and commission. Be sure you evaluate null values correctly.
    13. (4 pts) Write an SQL query that retrieves data from the COURSE table for courses that cost 1195, and whose descriptions do not start with ‘Intro’, sorted by their prerequisites in descending order.
    14. (4 pts) Write an SQL query that retrieves data from the STUDENT table for students whose last names begin with “A” though “T” and who work for ‘Competrol Real Estate’, sorted by the last names.
    15. (4 pts) Determine which student does not have the first letter the last name capitalized.  Show the STUDENT_ID and LAST_NAME columns.
    16. (4 pts) Check if any of the phone numbers in the INSTRUCTOR table have not been entered in the (###)###-#### format.  Show the instructor last name and the phone number that is in the incorrect format.
    17. (4 pts) Write an SQL statement that uses the CAST function that converts a number datatype to a varchar datatype.
    18. (4 pts) Write a SQL statement that converts a date datatype to a char datatype.
    19. (4 pts) Write a SQL statement that convert a number value to a character.

    Before doing problems 20 - 25 Download and Execute the script demoaddrows.sql from the Doc Sharing to add rows into the two tables above. Write SQL statements and produce result tables to solve the following requests.
    20. (2 pts) Show a list of different jobs.  Eliminate repeating values.
    21. (2 pts) How many employees are working at each job in each department and what the sums and averages are for the salary of those employees?
    22. (2 pts) Show the employee name with the maximum salary
    23. (2 pts) Show the average salary for all employees that are not managers.
    24. (2 pts) What is the difference between the highest and lowest salary?
    25. (2 pts) Select employee number, name for all employees with salaries above the average salary. Use a subquery.

    Before doing problems 26 through 31 Create the text Student tables with the createStudent.sql script. The script and instructions are in the schemaset.zip file available in the Doc Sharing. Download and unzip the files from the Doc Sharing to a C: drive directory such as the \temp directory. At the sql prompt enter the command @V:\temp\createStudent.sql. This will create  tables exactely as they are portrayed in the text and insert data into them.
    26. (2 pts) Show the state and the number of zip codes by state. Order the result by number of zip codes in descending sequence. Use the ZIPCODES table.
    27. (2 pts) Which city has the most zip codes assigned to it? Use a TOP-N query.
    28. (2 pts) Show the state and the number of zip codes by state. Order the result by number of zip codes in descending sequence and exclude all states having less than 5 zip codes.
    29. (2 pts) Show the Student_ID, last name, and the number of enrollments for the student. Show only students with more than 2 enrollments. Use an Inline view.
    30. (2 pts) Show the number of students enrolled and zip code for New York and where the city begins with 'W'. Use a Scalar subquery.
    31. (2 pts) Display the course number and description of courses with no enrollment. Also include courses which have no section assigned.

    Learn More
  6. 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
  7. Access 2010 Chapter 5 Lab 1 ECO Clothesline Database

    Microsoft Access 2010 Chapter 5 Lab 1 Adding Fields and Creating Multitable Forms for the ECO Clothesline Database

    Regular Price: $25.00

    Special Price: $20.00

    Microsoft Access 2010 Chapter 5 Lab 1 Adding Fields and Creating Multitable Forms for the ECO Clothesline Database


    Problem: ECO Clothesline needs to maintain additional data on each sales rep. The company needs to add the date each rep started as well as notes concerning the sales rep and a picture of the sales rep. The company wants a form that displays sales rep information and the customers for whom they are responsible.


    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 4. Otherwise, see the inside back cover of this book for instructions on downloading the Data Files for Students, or contact your instructor for more information about accessing the required files.


    Perform the following tasks:
    1. Add the Start Date, Notes, and Picture fields to the end of the Sales Rep table. Save the changes to the structure of the table.
    2. Add the data shown in Table 5 – 2 to the Sales Rep table. Adjust the row and column spacing to best fit the data. Save the changes to the layout of the table.
    3. Create the form shown in Figure 5 – 87. Use Sales Rep Master Form as the name of the form and Customers of Sales Rep as the name of the subform. Users should not be able to tab through the Picture control. The title is centered with a font size of 24 and a font weight of bold.
    4. Query the Sales Rep table to find all sales reps who started before January 1, 2012, and who have computer skills. Include the Sales Rep Number, First Name, Last Name, and Notes fields in the query results. Save the query as Computer Skills Query.
    5. Submit the revised database in the format specified by your instructor.

    Learn More
  8. Chapter 6 Lab 1 Sales Rep Master List for the ECO Clothesline Database

    Microsoft Access 2010 Chapter 6 Lab 1 Adding Tables and Creating Reports for the ECO Clothesline Database

    Regular Price: $25.00

    Special Price: $20.00

    Microsoft Access 2010 Chapter 6 Lab 1 Adding Tables and Creating Reports for the ECO Clothesline Database


    Problem: ECO Clothesline needs to maintain data on a weekly basis on the open orders for its customers. These are orders that have not yet been delivered. To track this information requires a new table, an Open Orders table. The company also needs a report that displays sales rep information as well as information about customers and any open orders that the customer has. The company would like to show its appreciation to current customers by discounting the amount customers currently owe.


    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 5. Otherwise, see the inside back cover of this book for instructions on downloading the Data Files for Students, or contact your instructor for more information about accessing the required files.


    Perform the following tasks:
    1. Create the Open Orders table using the structure shown in Table 6 – 5.


    2. Import the Open Orders.txt file into the Open Orders table.


    3. Create a one-to-many relationship between the Customer table and the Open Orders table.


    4. Create a query that joins the Sales Rep and the Customer tables. Include the Sales Rep Number, Last Name, and First Name fields from the Sales Rep table. Include all fields except the Sales Rep Number field from the Customer table. Save the query as Sales Reps and Customers. 5. Create the report shown in Figure 6 – 86. The report uses the Sales Reps and Customers query as the basis for the main report and the Open Orders table as the basis for the subreport. Use the name Sales Rep Master List for the report. The report title has a Text Align property value of Distribute. The Border Width property is hairline and the subreport label is Open Orders. The report is similar in style to the Business Analyst Master List shown in Figure 6 – 1a on page AC 339.


    6. Create the Customer Discount Report shown in Figure 6 – 87. The report uses the Sales Reps and Customers query. Customers who have paid $500 or more will receive a 3% discount on the remaining balance, and customers who have paid less than $500 will receive a 1% discount on the remaining balance. The report includes subtotals and grand totals for the Balance and Amount Paid fields. The report is similar in style to the Discount Report shown in Figure 6 – 1b on page AC 340.


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

    Learn More
  9. Microsoft Access 2010 Chapter 9 Navigation Forms

    Microsoft Access 2010 Chapter 9 Macros Navigation Form PivotTables and Charts AC 538 to AC 590

    Regular Price: $25.00

    Special Price: $20.00

    Microsoft Access 2010 Chapter 9 Macros Navigation Form PivotTables and Charts AC 538 to AC 590


    The first part of the project you will be creating the Client View and Update Form.
    Save the PivotTable for Analyst and Course Offerings Query, p. AC 579
    Save the PivotChart for Analysts and Course Offerings Query, p. AC 589


    Project - Macros, Navigation Forms, PivotTables, and PivotCharts
    The owner of Camashaly Design have heard about navigation forms that enable users to access forms and reports by simply clicking tabs and buttons. A navigation form like the one shown in the Figure 9-1a is a form that includes tabs to display forms and reports. This navigation form contains several useful features. With the Client tab selected, you can click the client number on any row to see the data for the selected client displayed in the Client View and Update Form (Figure 9-1b). The form does not appear in a tabbed sheet, the way tables, queries, forms and reports normally do. Rather, it appears a s a pop-up form, a form that stays on top of other open objects, even when another object is active. Clicking the Business Analyst tab displays business analyst data. As with clients, clicking the business analyst number on any record displays data for that analyst in a pop-up form. Clicking the Form tab in the Camashaly Design navigation form displays button for each of the available forms (Figure 9-1c). You can open the desired form by clicking the appropriate button. Clicking the Reports tab displays an option group for displaying reports (Figure 9-1d). You can preview or export any of the reports on at a time by clicking the corresponding option button. Camashaly would like such a navigation form because they believe it will improve the user-friendliness of the database, thereby improving employee satisfaction and efficiency.


    Befor creating the navigation form, Camashaly will create macros, which are collection of actions designed to carry out specific tasks. To perform the actions in a macro, you run the macro. When a macro is run, Access will execute the various steps, called actions, in the order indicated by the macro. The navigation form macros are run by clicking certain buttons in the form.

    Learn More
  10. CMIS420 PROJECT 2 Mail-Order Database DML and DDL statements

    CMIS420 Advanced Relational Database PROJECT 2 Mail-Order Database

    Regular Price: $25.00

    Special Price: $20.00

    CMIS420 Advanced Relational Database PROJECT 2 Mail-Order Database


    Overview:
    Use SQL, PL/SQL, and Triggers to design and create a Mail-Order Database System. Please create your own data for testing purpose. Use the attached file "Project 2 Tables" as a guide to creating the tables. You should pre-populate the PARTS, CUSTOMERS, EMPLOYEE and ZIPCODES tables.


    Due Date:
    Check the due date in Syllabus for the exact date for this assignment. No project will be accepted after the due date.


    Deliverables:
    Turn in all SQL scripts in the form of a SQL script files. The script files should include,


    1. A script file containing all the DML and DDL statements. That is, the SQL used to create the tables and sequence and the SQL to pre-populate or insert records in the tables. Name this file XXX_PROJ2.sql, where XXX are you intials.
    2. A file containing the PL/SQL package (Specification and Body) that provides the functionality listed in the requirements below. Name this file XXX_PROJ2.pkg, where XXX are you initials.
    3. A file containing the database triggers. Name this file XXX_PROJ2.trg, where XXX are you initials.
    4. Finally, provide a test SQL*PLUS routine (PL/SQL anonymous block) that will test the PL/SQL functionality developed. Name this file XXX_PROJ2_tst.sql, where XXX are your initials.


    You should submit your assignment through WebTycho as you did for previous assignments.
    Use winzip or any zip software to package the four (4) files into one file called XXX_project2.zip, where XXX are your initials.


    Requirements:
    The Mail-Order Database consists of the following tables and attributes. Please ensure that all constraints are created when creating the tables. All constraints other than NOT NULL constraints must be named.
    1. EMPLOYEE(ENO, ENAME, ZIP, HDATE, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
    2. PARTS(PNO, PNAME, QOH, PRICE, REORDER_LEVEL, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
    3. CUSTOMERS(CNO, CNAME, STREET, ZIP, PHONE, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
    4. ORDERS(ONO, CNO, ENO, RECEIVED, SHIPPED, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
    5. ODETAILS(ONO, PNO, QTY, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
    6. ZIPCODES(ZIP, CITY)
    7. ORDERS_ERRORS(TRANSACTION_DATE, ONO, MESSAGE)
    8. ODETAILS_ERRORS(TRANSACTION_DATE, ONO, PNO, MESSAGE)
    9. RESTOCK(TRANSACTION_DATE, PNO)


    • The EMPLOYEE table contains information about the employees of the company. The ENO (Employee Number) attribute is the primary key. The ZIP attribute is a foreign key referring to the ZIPCODES table.
    • The PARTS table keeps a record of the inventory of the company. The record for each part includes its number (PNO) and name (PNAME) as well as the quantity on hand (QOH), the unit price (PRICE) and the reorder level (REORDER_LEVEL). PNO is the primary key for this table.
    • The CUSTOMERS table contains information about the customers of the mail-order company. Each customer is assigned a customer number (CNO), which serves as the primary key. The ZIP attribute is a foreign key referring to the ZIPCODES table.
    • The ORDERS table contains information about the orders placed by customers, the employee who took the orders, and the dates the orders were received and shipped. Order number (ONO) is the primary key. The Customer number (CNO) attribute is a foreign key referring to the CUSTOMERS table, and the ENO attribute is a foreign key referring to the EMPLOYEES table.
    • The ODETAILS table contains information about the various parts order by the customer within a particular order. The combination of ONO and PNO attributes forms the primary key. The ONO attribute is a foreign key referring to the ORDERS table, and the PNO attribute is a foreign key referring to the PARTS relation.
    • The ZIPCODES table maintains information about the zip codes for various cities. ZIP is the primary key.
    • The ORDERS_ERRORS table contains information about any errors that occurred when an order is processed. Transaction date is the date of the transaction.
    • The ODETAILS_ERRORS table contains information about all errors that occur when processing an order detail. Transaction date is the date of the transaction.
    • The RESTOCK table contains information about all parts (PNO) that are below the reorder level. Transaction date is the date of the transaction.


    1. Write a package called Process_Orders to process customer orders. This package should contain four procedures and a function, namely;
    Add_order. This procedure takes as input customer number, employee number, and received date and tries to insert a new row in the Orders table. If the received date is null, the current date is used. The shipped date is left as null. If any errors occur, an entry is made in the Orders_errors table. A sequence called Order_number_seq should be used to populate the order number (ONO) column.
    Add_order_details. This procedure receives as input an order number, part number, and quantity and attempts to add a row to the Odetails table. If the quantity on hand for the part is less than what is ordered, an error message is sent to the Odetails_errors table. Otherwise, the part is sold by subtracting the quantity ordered from the quantity on hand for this part. A check is also made for the reorder level. If the updated quantity for the part is below the reorder level, an entry is made to the Restock table.
    Ship_order. This procedure takes as input an order number and a shipped date and tries to update the shipped value for the order. If the shipped date is null, the current date is used. If any errors occur, an entry is made in the Orders_errors table.
    Delete_order. This procedure takes as input an order number and tries to delete records from both the Orders and Odetails tables that match this order number. If any errors occur or there is no record that matches this order number, an entry is made in the Orders_errors table.
    Total_emp_sales. This function takes as input an employee number. It computes and returns the total sales for that employee.


    2. Create triggers on the PARTS, ORDERS, and ODETAILS tables to populate the CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY columns when an insert or update is made. Use SYSDATE and the pseudo column USER to populate these columns.


    3. Write a trigger that fires when a row in the Orders table is updated or deleted. The trigger should record the dropped order records in another table called deleted_orders. The deleted_orders table should also contain a date attribute that keeps track of the date and time the action (update or delete) was performed. This date is quite different from the CREATED_DATE and UPDATED_DATE from the Order table. Do not copy these dates to the deleted_order table. Please include the table creation script for the deleted_orders table in the script file.


    4. Create a sequence called order_number_seq that will be used to populate the order number (ONO) column.


    5. Write a PL/SQL anonymous block to test the above.

    Learn More

Items 11 to 20 of 35 total

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

Grid  List 

Set Ascending Direction