Database Management Systems.docx

Database: An organized collection of data, generally stored and accessed electronically from a\ computer system Database Management System: A DBMS is software that controls the storage, organization, retrieval, security and durability of data. ذخیره سازی، سازماندهی، بازیابی، امنیت و دوام داده ها D...

Database: An organized collection of data, generally stored and accessed electronically from a\ computer system Database Management System: A DBMS is software that controls the storage, organization, retrieval, security and durability of data. ذخیره سازی، سازماندهی، بازیابی، امنیت و دوام داده ها Database Language: The language used to interact with a database management system. In most relational cases is SQL but can be something else. Many NoSQL databases use something proprietary. DML :The Language used to query and update a database. Part of SQL DDL: The language to define the database structure. Part of SQL Transaction: A set of one or more database operations that are totally completed or not completed\ at all (atomic) ACID: Characteristics of translations in a DBMS. Stands for Atomic, Consistent, Independent,\ Durable. Transaction Isolation: When concurrent transactions execute and produce the same result as when executed serially. **Data Warehouse :**A subset of a database, typically read-only, often for the purpose of analysis and\ reporting. Often stores summarized information. Often organized by date. Target of OLAP operations. Physical: How something is put together under the covers. In the case of a database, it's the files and folders. In the case of a computer, it's the wires and chips **Logical:** How you talk about something. In the case of the database, it's the tables, indexes, rows,\ columns, schema objects, segments, \... **Data Integrity:** The level of trust you have in a database that it represents what you say it does. I.e. is that really how much is in my bank account. DBMSs help support data integrity. **Data Security:** Protecting a database, from destructive forces and from the unwanted actions of\ unauthorized users. Data Durability: How likely is it that my data will be lost. That is, can I read it today, tomorrow and anytime into the future? Once committed, can assume permanent. **\ ** **Database Management Systems** Software system that sits between users and their important data, Provides:\ Transactions\ Security\ Data Integrity\ Concurrent access\ Development environment\ Can be Relational or NoSQL\ Cost ranges from free to many **Why Database Management Systems?** Databases are a valuable corporate resource\ People rely on databases\ Enterprises can't function without it\ Helps to think enterprise level **DBA:** Database Administrator. Usually responsible for the physical aspects of a database such as\ the database software, storage management, user and security management, performance\ tuning, client configuration, backups and disaster recovery and prevention **The job of Database Systems Manager aka DBA** Manage security\ Manage availability\ Manage configuration\ Manage capacity\ Manage performance\ Manage problems **4 important roles**\ DBA\ Designer\ Developer\ User **Same person often fulfils many of these roles =\> application developer** **Relational Database:** A database where items in the database are tabular and relationships between items are through foreign keys. (E.F. Codd relational model) SQL Server, Oracle, MySQL, MS- Access, \... are examples. The data is perceived by the user as tables (and nothing but tables)\ the operators available to the user are operators that derive "new" tables from "old" ones. **Developers need not know how databases are physically organized\ Efficiency managed by DBMS** A diagram of a database Description automatically generated **Transactions** Can be committed and rolled back\ Can be protected by constraints\ Fields within range\ Uniqueness\ Referential (i.e. required relationships) **ACIDity:** Atomic -- all or nothing\ Consistent -- same outcome regardless of order transactions are executed in\ Isolated -- each user has single, consistent view of data\ Durable -- database changes are forever **the Oracle Security Model:** Users\ Roles\ Privileges\ Schemas SQL: Structured Query Language. The programming language that you use to interact with ORACLE (or SQL Server or PostgreSQL or \...) Consists of DML and DDL (see below) DML: Data Manipulation Language. A subset of SQL that handles manipulation of records in a\ database. DML statements start with Insert, Update or Delete. DDL: Data Definition Language. A subset of SQL that handles the manipulation of database objects such as tables, columns, indexes, stored procedures. DDL statements start with Create, Drop, Alter **Embedded SQL:** A facility that allows you to add SQL statements to your own programs such as a\ JAVA or C\# application. Embedded SQL typically is implemented as a language extension or a source code preprocessor. **SQL\*Plus:** The command-line utility that enables you to execute SQL statements and perform administrative functions against an Oracle database. Always installed, Every Oracle user should learn SQL\*Plus \*and\* SQL Developer, Very primitive navigation, editing, Can run individual SQL statements or whole script files using @. Many features for the motivated DBA **SQL Developer:** An important developer IDE for Oracle. Learn SQL Developer well! Null value: The "value" that a database table field has before it is explicitly set. Null values\ complicate things **PL/SQL:** Procedural Language for SQL. Language that runs on an Oracle server that adds conditionals, looping, variables and functions. Is not part of the ANSI SQL standard and to my knowledge, runs only on Oracle and IBM DB2 **program block structure** Like a procedure in Oracle that you can call from other PL/SQL blocks. DECLARE (or header signature)\ ∕ create variables, cursors, and types. (optional)\ BEGIN\ ∕ logic, loops, SQL statements\ EXCEPTION\ ∕ error handling (optional)\ END\ ∕ closes the block **PL/SQL Conditionals**  **PL/SQL Statement** A screenshot of a computer program Description automatically generated **PL/SQL Loops**  **Reusable PL/SQL procedures or functions** stored and executed within the database!! Callable from other PL/SQL programs, queries and triggers Typically, are part of the data tier of a 3-tier application Most DBMSs support stored procedures\  **Cursor** Like a finger pointing to a current record in a list of records. Through it, you can read the\ current record and advance the "finger" to the next record Used to fetch rows returned by a query in PL/SQL Cursors can be used to update databases, but it Locks all rows if not careful Usage follows well defined pattern\ Declare cursor\ Open cursor\ Fetch rows in cursor one at a time in a loop\ Close cursor(optional) **Trigger:** PL/SQL code that is executed when a particular event occurs. Events can be **data events** (on insert\...) or **system events** (on start-up\...) Its best to understand the best practices for Triggers because poor usage can create a big mess **Use cases for triggers:** Logging changes to databases for auditing\ Filling in computed fields such as IDs, default values\ Data validation\ Auto increment fields\ Time stamps\ Preventing certain operations **Issue:** Can lead to confusing code **DML Trigger:** A trigger that fires when a DML event (insert, update ,delete\...) occurs. Can be used to log updates to records or maintain a history of\ records. **System Trigger:** A trigger that fires when a system event (start-up, shutdown , \...) occurs. Can be used to log user log-ins. **Constraint** An invariant that must be true before an update to a record is committed. Applies\ globally, unlike triggers used to enforce integrity constraints. (they are two different things) **Trigger Level** :What the trigger applies to -- either a statement or a row. **Trigger Timing:** When the trigger fires -- before or after the event **Trigger Restriction**: A Boolean expression associated with a trigger that must be true before a trigger is executed. **Compound Trigger:** A trigger that can apply to statements, rows and more than one timing. Is often used to process large numbers of rows efficiently and share intermediate values between triggering\ events.