Database Exam 3

flexibility

• Allows the user to decide exactly what data to return
• Greatly improves the usability of the system
• Frequently used in web-based DSS

Using userforms and collect the parameter values as variables from the user

• 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

Inserting or "injecting" SQL code via data that is sent to a DBMS when (non-parameterized) dynamic SQL is used

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

input parameters

• Programs written in standard languages (Java, C#)
• Scripting languages (JavaScript, VBScript, PHP)
• SQL command prompt (SQL*Plus, Query Analyzer)

• Performance
• Productivity and Ease of Use
• Security

• 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

• Improved development efficiency (avoids redundant coding and has specialization)
• Common integration / development standards (can be called by practically any app)

Limit / restrict users' access to the DB by using SP as middle ware or buffer

• 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

A Procedure that is automatically executed by the RDBMS when a given data manipulation event occurs

• 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

• Set Forms and reports
• Primary interaction with user

Set of tables ("under the hood")

• Data collection
• Display (query results and computations)
• Switchboard/Gateway for DBMS

• Data entry vs detailed exploratory analysis
• Special needs
• human factors such as consistency and clarity (i.e. colors fonts, input sources)

Since MySQL is webbades and closely linked to PHP we use PHP for building a reporting / data entry interface to a MySQL database

Can be any combination of characters that allows you to differentiate between the individuals DDL statements and the DML statements

• Escaping user-supplier input values and/or data validation
• Using parameterized prepared statements (prepared queries)

• Escaping user-supplied input [mysql_real_escape_string() or addslashes()]
• Data validation [is_numeric]