Database Exam 3

The flashcards below were created by user Kimmiey on FreezingBlue Flashcards.

  1. Effective decision support systems require ______ in interacting with the user
  2. Using parameters within a query...
    • Allows the user to decide exactly what data to return
    • Greatly improves the usability of the system
    • Frequently used in web-based DSS
  3. How can you call parameterized queries externally
    Using userforms and collect the parameter values as variables from the user
  4. What are some advantages of parameterized queries?
    • Query can be compiled once at beginning of process
    • Data types can be enforced without focus on syntax
    • Security - can help to protect against SQL injection
  5. What is SQL injection
    Inserting or "injecting" SQL code via data that is sent to a DBMS when (non-parameterized) dynamic SQL is used
  6. What is a stored procedure?
    A subroutine available to applications accessing a relational database system. A stored procedure (sometimes called a sproc or an SP) is actually stored in the database
  7. Stored procudures can receive ______ ______ and may return results
    input parameters
  8. Stored procedures can be called from:
    • Programs written in standard languages (Java, C#)
    • Scripting languages (JavaScript, VBScript, PHP)
    • SQL command prompt (SQL*Plus, Query Analyzer)
  9. Advantages of stored procedures
    • Performance
    • Productivity and Ease of Use
    • Security
  10. Describe the performance of stored procudures
    • Compiled once - procedure calls are quick and efficient
    • Serverside computation - decreased network traffic
    • Executable code is cached and shared - lowers memory requirements
    • Grouping SQL statements allows for a single call execution
  11. Describe the productivity and ease of use of stored procedures
    • Improved development efficiency (avoids redundant coding and has specialization)
    • Common integration / development standards (can be called by practically any app)
  12. Describe the security of stored procedures
    Limit / restrict users' access to the DB by using SP as middle ware or buffer
  13. Persistent stored modules
    • Blocks of code (SQL and procedures) that are stored and executed at the DBMS
    • SQL doesn't support control statements like looping statements so the SQL-99 uses this to support this
  14. What is a triger
    A Procedure that is automatically executed by the RDBMS when a given data manipulation event occurs
  15. Besides the definition what are some important things about triggers
    • It is invoked either before or after a data row is inserted, updated or deleted
    • it is associated with a database table
    • It is used to automate critical actions / provide warnings
    • it can be used to enforce constraints
  16. Front end of DBMS Application development
    • Set Forms and reports
    • Primary interaction with user
  17. Back end of DBMS Application Development
    Set of tables ("under the hood")
  18. What are the primary uses of form
    • Data collection
    • Display (query results and computations)
    • Switchboard/Gateway for DBMS
  19. What is important to take into account when creating an effective design for forms
    • Data entry vs detailed exploratory analysis
    • Special needs
    • human factors such as consistency and clarity (i.e. colors fonts, input sources)
  20. What do we use for forms in mySQL
    Since MySQL is webbades and closely linked to PHP we use PHP for building a reporting / data entry interface to a MySQL database
  21. What's the point of a delimeter
    Can be any combination of characters that allows you to differentiate between the individuals DDL statements and the DML statements
  22. What are the 2 most common defenses against SQL injection attacks
    • Escaping user-supplier input values and/or data validation
    • Using parameterized prepared statements (prepared queries)
  23. Escaping user-supplied input values and/or data validation examples
    • Escaping user-supplied input [mysql_real_escape_string() or addslashes()]
    • Data validation [is_numeric]
Card Set:
Database Exam 3
2016-11-29 04:26:46

Show Answers: