INFO 340 Lecture Notes - Lecture 5: Data Definition Language, Stored Procedure, Business Rule
INFO 340 Lecture 5
Union (UNION command between two SELECT commands), intersection(R1 – (R1- R2)),
difference (EXCEPT command between two SELECT commands)
Cross product – to find pairs of similar values
Review of last week
- What is meant by normalization?
o Occurs in logical design phase
Data Definition Language – anything that is used to create, modify, or delete
- An object that can be created
- Foreign key goes toward the “many” relationship
- Rely on someone to input information into database
Business rules – Use C.R.A.B.E. commands
- CREATE FUNCTION → RETURNS INT → AS → BEGIN → END
- Data Definition Language – things that can be created, altered, dropped
o Schema, (more examples?)
- INSERT, UPDATE, DELETE – Data Modification Language
User-defined function vs stored procedure
- User-defined function – does not alter/change data (no insert, update, delete)
o Answers a question (e.g., what is the inventory value?
- Stored Procedure
- Both take parameters
Computed column – produces result calculated from other values
- Based on # reads vs data modifications
- Cannot change the computed result
- Goal – reduce number of CPU cycles (things that are read frequently)
Business Rule – restricts data from being modified if rule is violated
- Goal – support business operations to increase efficiency + profitability
- Example – delivery area, number of coupons, registration restrictions, etc.
Stored procedure pseudocode:
Data Definition Language
- Use during the coding stage of database
- Vocabulary to define / modify data structures
o Data structure: coded objects within database (table, view, login, attribute,
function)
Document Summary
Union (union command between two select commands), intersection(r1 (r1- r2)), difference (except command between two select commands) Cross product to find pairs of similar values. What is meant by normalization: occurs in logical design phase. Data definition language anything that is used to create, modify, or delete. Foreig(cid:374) key goes toward the (cid:862)(cid:373)a(cid:374)y(cid:863) relatio(cid:374)ship. Rely on someone to input information into database. Create function returns int as begin end. Data definition language things that can be created, altered, dropped: schema, (more examples?) Computed column produces result calculated from other values. Based on # reads vs data modifications. Goal reduce number of cpu cycles (things that are read frequently) Business rule restricts data from being modified if rule is violated. Goal support business operations to increase efficiency + profitability. Example delivery area, number of coupons, registration restrictions, etc. Use during the coding stage of database.
