Database Design Exam #3
STORED PROCEDURES
*Definition:* A collection of SQL statements stored as a file that can be invoked by a single command. Usually, DBMS products provide a language for creating stored procedures that augments SQL with programming language constructs. Oracle Database provides PL/SQL for this purpose; Microsoft SQL Server provides T-SQL; MySQL also adds procedural capabilities but does not use a separate name for these additions. With some products, stored procedures can be written in a standard language such as Java. Usually, stored procedures are stored within the database itself. *Simple Definition:* A program that is stored within the database and compiled when used. Stored procedures can receive input parameters and return results. Unlike triggers, which are attached to a given table or view, stored procedures are attached to the database. They can be executed by any process using the database that has permission to use the procedure. Stored Procedures are a module of code that is called by a user or database administrator. They are assigned to a database, but not to a table or a view. They can issue INSERT, UPDATE, DELETE, and MERGE commands. They can be used for repetitive administration tasks or as part of an application. *Advantages of Stored Procedures:* Greater security, decreased network traffic, SQL can be optimized, code sharing, less work, standardized processing, specialization among developers.
SQL Views (WHY and HOW)
*Definition:* A named relation that is constructed from a single SQL SELECT statement. The term view in most DBMS products, including MySQL, Oracle Database, and Microsoft SQL Server, means SQL view. A virtual table that is constructed from other tables or views. A view has no data of its own but obtains data from tables or other views. Views are constructed from SQL SELECT statements using the CREATE VIEW statement, and view names are then used just as table names would be in the FROM clause of other SQL SELECT statements. Views are a very important part of application development for both Web client-based applications and smartphone apps. Views act as a medium between Smartphone/web applications and Database tables. The basic principle is that all summing, averaging, grouping, comparing, and similar operations should be done in SQL views and that it is the final result as it appears in the SQL view that is passed to the application program for use. SQL views can also be used to hide columns to simplify results or to prevent the display of sensitive data, display results of computed columns, and hide complicated SQL syntax. You can also layer built-in functions into views. Last 3 other important uses are: *1.* They can isolate source data tables from application code. *2.* They can give different sets of processing permissions to the same table. *3.* They can enable the definition of multiple sets of triggers on the same data source. Some views can be updated; others cannot. The rules by which this is determined are both complicated and dependent on the DBMS in use. *Updatable Views:* Views based on a single table with no computed columns and all non-null columns present in the view. Views based on any number of tables, with or without computed columns, and INSTEAD OF trigger defined for the view. *Possibly Updatable Views:* Based on a single table, primary key in view, some required columns missing from view, update and delete may be allowed. Insert is not allowed. Based on multiple tables, updates may be allowed on the most subordinate table in the view if rows of that table can be uniquely identified. *CREATE VIEW command:* CREATE VIEW CustomerNameView AS SELECT LastName AS CustomerLastName, FirstName AS CustomerFirstName, FROM CUSTOMER;
TRIGGERS
*Definition:* A special type of stored procedure that is invoked by the DBMS when a specified condition occurs. *BEFORE* triggers are executed before a specified database action. *AFTER* triggers are executed after a specified database action. *INSTEAD OF* triggers are executed in place of a specified database action. INSTEAD OF triggers are normally used to update data in SQL views. A trigger is attached to a table or a view. A table or a view may have many triggers, but a trigger is associated with just one table or view. A Trigger is a module of code that is called by the DBMS when INSERT, UPDATE, or DELETE commands are issued. They are assigned to a table or view. Depending on the DBMS, may have more than one trigger per table or view. Triggers may issue INSERT, UPDATE, and DELETE commands and thereby may cause the invocation of other triggers. *Trigger uses:* *Provide default values:* If the computation of a default value requires complicated logic, then an INSERT trigger must be used. *Enforce data constraints:* ASSERTIONs in the SQL standard are implemented in triggers. *Update views:* Applications can sometimes update the views that the DBMS cannot update by applying logic that is particular to a given business setting. In this case, the application-specific logic for updating the view is placed in an INSTEAD OF trigger. *Perform referential integrity actions:* *3x3 Matrix:* Trigger Type/ | BEFORE | INSTEAD OF | AFTER DML Action | | | -------------------------------------------------------- | Oracle | Oracle | Oracle INSERT | MySQL | SQLSer. | SQLSer. | | | MySQL -------------------------------------------------------- | Oracle | Oracle | Oracle UPDATE | MySQL | SQLSer. | SQLSer. | | | MySQL -------------------------------------------------------- | Oracle | Oracle | Oracle DELETE | MySQL | SQLSer. | SQLSer | | | MySQL
Correlated Subquery
*Definition:* A type of subquery in which an element in the subquery refers to an element in the containing query. A subquery that requires nested processing. Main difference between correlated and non-correlated subqueries: Non-correlated subqueries can be processed from the bottom-up as well. Unlike a normal subquery, a correlated subquery cannot run the bottom SELECT statement by itself, obtain the information, and use it to execute the upper query. The reason for this is in the last two lines of code below: W1.Title is being compared to W2.Title. For a correlated subquery, the processing is nested, a row from an upper query statement is used in comparison with rows in a lower-level query. Use correlated subqueries when searching for multiple rows with the same title. -------------------------------------------------------- *A correlated subquery that finds rows with the same title:* SELECT W1.Title, W1.Copy FROM WORK AS W1 WHERE W1.Title IN (SELECT W2.Title FROM WORK AS W2 WHERE W1.Title = W2.Title AND W1.WorkID <> W2.WorkID);
Create Table Statements
*Definition:* The SQL command used to create a database table. Used to construct tables, define columns and column constraints, and create relationships. 3 reasons why to learn SQL for creating tables: *1.* Creating tables and relationships with SQL is quicker than with graphical tools. *2.* Some applications, particularly those for reporting, querying, and data mining, require you to create the same table repeatedly. *3.* Some applications require you to create temporary tables during application work. CREATE TABLE ( ColumnName DataType OptionalConstraint, ColumnName DataType OptionalConstraint, ... Optional table constraint ... );
Implementation of 1:1, 1:N Table Relationships
*For 1:N Table Relationships:* Step 1: Define the referential integrity constraint. Step 2: Set the foreign key to NOT NULL in the child table. *For 1:1 Table Relationships:* Step 1: Define the referential integrity constraint. Step 2: Set the foreign key to NOT NULL in the child table. Step 3: Declare the foreign key as unique. *EX:* CREATE TABLE ARTIST ( ArtistID Int NOT NULL IDENTITY(1,1), LastName Char(25) NOT NULL, FirstName Char(25) NOT NULL, Nationality Char(30) NULL, DateOfBirth Numeric(4) NULL, DateDeceased Numeric(4) NULL, CONSTRAINT ArtistPK PRIMARY KEY(ArtistID), CONSTRAINT ArtistAK1 UNIQUE(LastName, FirstName) ); CREATE TABLE WORK ( WorkID Int NOT NULL IDENTITY(500,1), Title Char(35) NOT NULL, Copy Char(12) NOT NULL, Medium Char(35) NULL, Description Varchar(1000) NULL DEFAULT 'Unknown provenance', ArtistID Int NOT NULL, CONSTRAINT WorkPK PRIMARY KEY(WorkID), CONSTRAINT WorkAK1 UNIQUE(Title, Copy), CONSTRAINT ArtistFK FOREIGN KEY(ArtistID) REFERENCES ARTIST(ArtistID) ON UPDATE NO ACTION ON DELETE NO ACTION *CONSTRAINT UniqueWork UNIQUE (ArtistID)* ); Difference in bold between relationship types. *1:N Relationship, Parent Optional:* Specify foreign key constraint. Set foreign key to NULL. *1:N Relationship, Parent Required:* Specify foreign key constraint. Set foreign key to NOT NULL. *1:1 Relationship, Parent Optional:* Specify foreign key constraint. Specify foreign key unique constraint. Set foreign key NULL. *1:1 Relationship, Parent Required:* Specify foreign key constraint. Specify foreign key unique constraint. Set foreign key NOT NULL. *Casual Relationship:* Create a foreign key column, but do not specify foreign key constraint. If relationship is 1:1, specify foreign key unique.
Most Common SQL Server Data Types
*Int (Integer):* SQL Server: 4-byte integer. Value range from -2,000,000,000 through 2,000,000,000. Oracle: Whole number length 38. MySQL: -2,000,000,000 through 2,000,000,000. *Char:* SQL Server: Character, length 0-8,000 bytes. Oracle: Fixed length character field of length n. Maximum of 2,000 characters. MySQL: M = 0-255. *Varchar:* SQL Server: Variable-length character, length 0-8,000 bytes. Oracle: Variable length character field up to n characters long. Maximum of n = 4,000. MySQL: M = 1-255. *Nvarchar:* SQL Server: Oracle: MySQL: *Date:* SQL Server: DateTime = 8-byte datetime. Range from January 1, 1753 through December 31, 9999, with an accuracy of of three-hundredths of a second. Oracle: 7-byte field containing both date and time. MySQL: YYYY-MM-DD : 1000-01-01 to 9999-12-31 *Numeric:* SQL Server: Decimal - can set precision and scale. Range -10^38 +1 through 10^38 -1. Oracle: Number: Numeric field of length n, d places to the right of the decimal. MySQL:
LEFT and RIGHT Joins
*Left Outer Join:* SELECT C.LastName, C.FirstName, A.LastName AS ArtistName FROM CUSTOMER C LEFT JOIN CUSTOMER_ARTIST_INT CI ON C.CustomerID = CI.CustomerID LEFT JOIN ARTIST A ON CI.ArtistID = A.ArtistID; *JOIN ON syntax:* SELECT CUSTOMER.Name, ARTIST.Name FROM CUSTOMER JOIN CUSTOMER_ARTIST_INT ON CUSTOMER.CustomerID = CUSTOMER_ARTIST_INT.CustomerID JOIN ARTIST ON CUSTOMER_ARTIST_INT.ArtistID = ARTIST.ArtistID;
Table Constraints
*PRIMARY KEY:* In SQL, a constraint statement used to create a primary key for a table. *FOREIGN KEY:* In SQL, the constraint used to create relationships and referential integrity between tables. *NULL:* A NULL is stored in a table when no data is provided for that attribute. *NOT NULL:* In SQL, a constraint that specifies that a column must contain a value in every row. *UNIQUE:* In SQL, a constraint that specifies that the values in a column must be unique. *CHECK:* In SQL, a constraint that specifies what data values are allowed in a particular column. Defines data constraints. *EX:* CONSTRAINT NationalityValues CHECK (Nationality IN ('Canadian', 'English', 'French', CONSTRAINT BirthValuesCheck CHECK (DateOfBirth < DateDeceased), CONSTRAINT ValidBirthYear CHECK (DateOfBirth LIKE '[1-2][0-9][0-9][0-9]'),
*Chapters 7 & 8*
*SQL for Database Construction and Application Processing* *Chapter 7 materials will cover both MC and Written Responses*
Full vs. Differential Backups
A complete backup of the operational database should be made prior to making any changes. Before making any changes to an operational database: - A complete backup of the operational database should be made. - Any proposed changes should be thoroughly tested. 3 different copies of the database schema used in the redesign process: *1.* A small test database for initial testing. *2.* A large test database for secondary testing. *3.* The operational database.
ALTER Statements (all caveats)
DDL (Data Definition Language) The SQL command used to change the structure of a database table. It can be used to add, remove, or change columns. It also can be used to add or remove constraints. -------------------------------------------------------- *Add a column:* ALTER TABLE CUSTOMER ADD MyColumn Char(5) NULL; -------------------------------------------------------- *Drop a column:* ALTER TABLE CUSTOMER DROP COLUMN MyColumn; -------------------------------------------------------- *Add a constraint:* ALTER TABLE CUSTOMER ADD CONSTRAINT MyConstraint CHECK (LastName NOT IN ('RobertsNoPay')); -------------------------------------------------------- *Drop a constraint:* ALTER TABLE CUSTOMER DROP CONSTRAINT MyConstraint;
DROP Statements (all caveats)
DDL (Data Definition Language) The SQL command used to remove a table from a database. -------------------------------------------------------- DROP TABLE TRANS; -------------------------------------------------------- Will not drop a parent with a foreign key constraint. Even if there are no children or if you have coded delete cascade. Instead, you will need to either drop the child table or the constraint first. -------------------------------------------------------- DROP TABLE CUSTOMER_ARTIST_INT; DROP TABLE TRANS; DROP TABLE CUSTOMER;
UPDATE Statements (all caveats)
DML (Data Manipulation Language) The SQL command used to change values of existing data. To change values of existing rows. -------------------------------------------------------- *Change value of City:* UPDATE CUSTOMER SET City = 'New York City' WHERE CustomerID = 1000; -------------------------------------------------------- *Change value of City and State:* UPDATE CUSTOMER SET City = 'New York City', State = 'NY' WHERE CustomerID = 1000; -------------------------------------------------------- *Bulk Updates:* UPDATE CUSTOMER SET City = 'New York City'; -------------------------------------------------------- *Updating using values from other tables:* UPDATE PURCHASE_ORDER SET TaxRate = (SELECT Tax FROM TAX_TABLE WHERE TAX_TABLE.City = 'Bodega Bay') WHERE PURCHASE_ORDER.City = 'Bodega Bay'; -------------------------------------------------------- The UPDATE statement can set a column equal to the value of a column in a different table.
INSERT Statements (all caveats)
DML (Data Manipulation Language) The SQL command used to initially populate a table with rows of data. To add rows of data to a table. -------------------------------------------------------- *Standard Method:* INSERT INTO ARTIST (LastName, FirstName, Nationality, DateOfBirth, DateDeceased) VALUES ('Miro', 'Joan', 'Spanish', 1893, 1983); -------------------------------------------------------- *Inserting values only if data is in same order as columns and no surrogate keys:* INSERT INTO ARTIST VALUES ('Miro', 'Joan', 'Spanish', 1893, 1983); -------------------------------------------------------- *Bulk Insert:* INSERT INTO ARTIST (LastName, FirstName, Nationality, DateOfBirth, DateDeceased) SELECT LastName, FirstName, Nationality, DateOfBirth, DateDeceased FROM IMPORTED_ARTIST;
DELETE Statements (all caveats)
DML (Data Manipulation Language) The SQL command used to remove data from a table. It can be associated with a trigger, and fire that trigger when the DELETE statement is executed. Unlike the SQL TRUNCATE statement, it does not reset surrogate key values. -------------------------------------------------------- Delete the row for a customer with a CustomerID of 1000: DELETE FROM CUSTOMER WHERE CustomerID = 1000;
Alternate Join Type
Inner, Let, Right, Full
EXISTS
Operator: EXISTS Meaning: Is a non-empty set of values. *Definition:* Used in an SQL query with a subquery, the EXISTS operator returns a non-empty set of values if any row in the subquery meets a specified condition. When we use the EXIST or NOT EXISTS operator in a query, we are creating another form of correlated subquery.
NOT EXISTS
Operator: EXISTS Meaning: Is an empty set of values. *Definition:* Used in an SQL query with a subquery, the NOT EXISTS operator returns an empty set of values if all row in the subquery fail to meet a specified condition.
Double NOT EXISTS
The double use of NOT EXISTS can be used to find rows that do not not match a condition. Because of the logic of a double negative, if a row does not not match any row, then it matches every row! First, produce the set of all customers who are interested in a particular artist. Then take the complement of that set, which will be the customers who are not interested in that artist. If that complement is an empty set, then all customers are interested in the given artist.