IM 544
Rollforward
"REDO" -restore database using saved data and _________ valid transactions since the last save. Starts with earlier copy of the database and applies after images (results of good transactions) to get a later state. Aka forward recovery. Use images, not transactions to recover
Having
**Can't use Aggregate in a Where function, so use having instead --Filter function --Inside subquery- use where --But can use having outside of the subquery
Purpose of Views
To focus, simplify, and customize the perception each user has of the database. -As a security mechanism by allowing users to access data through the view, without granting the users permissions to directly access the underlying base tables. -To meet the needs of reports that are run frequently by the organization -To meet the needs of queries that interactive users frequently make -To handle derived data -To assist in maintaining integrity -Sometimes simpler to construct -To provide a backward compatible interface to emulate a table whose schema has changed. -Use as a security mechanism -For things that you run over and over again you can save time by doing them in a proccesssing view
Unapplied cash
We're going to take cash for an order were not sure what sales order is applies to
Balance Forward
not trying to match each pmt with sales invoicing, you match with AR. Just put it on customer balance, not trying to map it
Isolated (ACID)
transaction data must be isolated from other transactions, meaning each transaction is executed as those it was run alone. Isolated to keep consistency. -When im doing that particular transaction, playing with 2 accounts and so dont want anything to touch those accounts while youre doing it, making sure doesnt come into the story and get the wrong story --Concurrency Control
SQL Server--Database Roles
-Db_owner: allows any database tasks -Db_accessadmin: add/remove user logins -Db_securityadmin: manage all permissions -Db_ddladmin: run DDL commands -Db_backupoperator: backup and recovery -Db_datareader: select data -Db_datawriter: update data -Db_denydatareader: no select -Db_denydatawriter: no update
Checkpoint facilities
-point of synchronization between the database and transaction log, checkpoints speed up database recovery process, most DBMS products automatically checkpoint themselves --Database can be recovered using after-images since the last checkpoint --Checkpoint can be done several times per hour --System is in a quiet state --Database and transaction logs are synchronized --This allows recovery manager to resume processing from a short period, instead of repeating entire day
Data Discovery
-process of identifying data and where it resides
Check Constraints (SQL Server Controls)
-Use to limit the value range that can be placed in a column
Avail. To purhcase
-What can we promise to the customer
Buffer
-Where we hold data temporarily when 2 components of a computer don't have the same processing speed -due to the time lag in retrieving data and sending it back -part of checkpoint (synchronization bw database and transaction log)
Report Generators
-Which are used to define the format and look of desired output
Differential Backup
-makes a copy of database changes since the last full back up (takes longer than incremental but easier to restore). The process is faster than a full backup and only backs up the data with changes, so it saves storage space. -However, the data changes that are backed up in a differential backup become progressively larger with each full backup cycle. If the cycle is too long, the size of the archive will be larger and make the process longer. --Other cons are that a full backup is required before this backup process can begin. And if either the full or ______ backup fails, the data recovery process cannot be completed.
Derived Attribute
-one that you can compute based on other attributes in the database -Ex) Total price of an invoice
Rollback
-"UNDO" the erroneous changes made to the database and reprocess valid transactions. So returns database to an earlier state aka backward recovery. Uses images not transactions to recover
Master File
-"Who" files -Don't put any TX data in them -tells us about an entity -ex) Customers, employees, shippers, products
DML (Data Manipulation Language)
--Includes commands used to populate the records and fields --add, delete, update, modify and insert data into the table structures - and to query contents
Data Query Commands
--Subset of DML --Used to ask questions about the data and see the results --Popular data query tools include QBE(Query by example) and SQL (structures query language)
Data Classification
--process of tagging and organizing data into categories to determine how it's handled plays a role in --Risk management / Security, (for example, determining, based on category of the data, whether it needs to be encrypted or made anonymous, needs to be stored internally, or is safe to store in the cloud.) --Compliance and legal discovery (meeting legal and regulatory requirements for retrieving specific information within a set timeframe), --Reducing duplicate information, which helps to cut storage and backup costs, while speeding up data searches.
RAID (Redundant array of independent disks, devices)
--storage uses multiple disks in order to provide fault tolerance, to improve overall performance, and to increase storage capacity in a system. --allows you to store the same data redundantly (in multiple paces) in a balanced way to improve overall performance. --disk drives are used frequently on servers but aren't generally necessary for personal computers. sequential data is broken into segments which are sent to the various disks in the array, speeding up throughput. --A typical array uses multiple disks that appear to be a single device so it can provide more storage capacity than a single disk. RAID level 0-10
Forms Generators
--used to design the format and look of desired screens and forms
Restore/Rerun
--you could reprocess transactions (up to the point of the failure against the backup copy of the database or the portion of the database being recovered) --2 disadvantages: --The time is takes can be prohibitive and it can affect normal operations --The sequencing of transactions will likely be different than original processing which can lead to different results
Open Sales Order
-Approved Customer Order -so converted to sales order but has not yet been shipped and billed
Transaction Processing Properties (ACID)
-Atomic -Consistent -Isolated -Durable
Access to the Objects
-Authorization Matrix -Access Control List
Binary relationship
-How are the 2 entities related -can't talk about anything other than these entities
Primary Keys
-Identifiers -Can't be null -Must be unique values -can't reuse or else can't tell one from the other -can be single or multiple attributes
Transaction File
-No accounting Data -Have to have data as a field bc of matching principle
Intellectual property
-Part of Toxic Data -something that makes you unique 3P+IP = TD
Relational Database
-Predefined tables (rows and columns) -We introduce duplication in relational database for linking, we link based on cardinalities -Handle many to many by breaking it down further and linking
Open Sales Invoice
-Sale that has been billed but has not been paid by customer
Object-Relational Database (Object Database)
-a database management system (DBMS) that's composed of both a relational database (RDBMS) and an object-oriented database (OODBMS). -ORD supports the basic components of any object-oriented database model in its schemas and the query language used, such as objects, classes and inheritance. -An object-relational database may also be known as an object relational database management systems (ORDBMS). -ORD is said to be the middleman between relational and object-oriented databases because it contains aspects and characteristics from both models the basic approach is based on RDB, since the data is stored in a traditional database and manipulated and accessed using queries written in a query language like SQL. -One of ORD's aims is to bridge the gap between conceptual data modeling techniques for relational and object-oriented databases like the entity-relationship diagram (ERD) and object-relational mapping (ORM). -has a feature that allows developers to build and innovate their own data types and methods, which can be applied to the DBMS.
Object-Oriented Database (Object Database)
-a database that subscribes to a model with information represented by objects. Object-oriented databases are a niche offering in the relational database management system (RDBMS) field and are not as successful or well-known as mainstream database engines. the main feature of object-oriented databases is allowing the definition of objects, -Objects, in an object-oriented database, reference the ability to develop a product, then define and name it. The object can then be referenced, or called later, as a unit without having to go into its complexities. This is very similar to objects used in object-oriented programming. -Before object, the code and the data were in two different places, if you went into a table you wouldnt see code you would just see the data -Object combines both data values and the code that manipulates data -Object oriented/object relation database—has code logic (computer instructions) within it. The code embedded is called the method -Ex: A real-life parallel to objects is a car engine. It is composed of several parts: the main cylinder block, the exhaust system, intake manifold and so on. Each of these is a standalone component; but when machined and bolted into one object, they are now collectively referred to as an engine. Similarly, when programming one can define several components, such as a vertical line intersecting a perpendicular horizontal line while both lines have a graded measurement. This object can then be collectively labeled a graph. When utilizing the ability to plot components, there is no need to first define a graph; but rather the instance of the created graph can be called.
Foreign Key
-a field in one table that already exists as a primary key in another table in the same database · The primary key field must exist before the foreign key field can be defined Values of the foreign key constrained by values of the related primary key · when you go to build the databases you have to go and link them up. We take entity a's primary key and let it go rest in another entity, the only reason it is there is to allow linking. Not a primary key in the other entity but a foreign key. Aka we link by duplicating keys and allowing them to be in different places Youll see each of these keys in their own tables but you can see that key in a bunch of other tables so you can link them up, so you can just link back to the first table · constrains foreign key values to match primary key values in related table
Instances
-an entity is a custoemer and each individual customer is a ______ -Records
network database
-database model that is designed as a flexible approach to representing objects and their relationships. -A unique feature of the network model is its schema, which is viewed as a graph where relationship types are arcs and object types are nodes. -Unlike other database models, the network model's schema is not confined to be a lattice or hierarchy; the hierarchical tree is replaced by a graph, which allows for more basic connections with the nodes -First time i can put together a database and don't have the constraint of one to many Benefits include: --Simple Concept: Similar to the hierarchical model, this model is simple and the implementation is effortless. --Ability to Manage More Relationship Types: The network model has the ability to manage one-to-one (1:1) as well as many-to-many (N: N) relationships. --Easy Access to Data: Accessing the data is simpler when compared to the hierarchical model. --Data Integrity: In a network model, there's always a connection between the parent and the child segments because it depends on the parent-child relationship. Data Independence: --Data independence is better in network models as opposed to the hierarchical models. -Drawbacks: --System Complexity: Each and every record has to be maintained with the help of pointers, which makes the database structure more complex. --Functional Flaws: Because a great number of pointers is essential, insertion, updates, and deletion become more complex. --Lack of Structural Independence: A change in structure demands a change in the application as well, which leads to lack of structural independence.
QBE (Query by example)
-grid or replica of an empty record is displayed and the user types search criteria in the applicable columns -Then the computer takes the info entered into the QBE grid and generates SQL commands
Deadlock
-locking can lead to this, two or more resources lock a resources and wach wait for the other to unlock the resource. -A deadlock is a situation in which two computer programs sharing the same resource are effectively preventing each other from accessing the resource, resulting in both programs ceasing to function. -The earliest computer operating systems ran only one program at a time. All of the resources of the system were available to this one program. Later, operating systems ran multiple programs at once, interleaving them. Programs were required to specify in advance what resources they needed so that they could avoid conflicts with other programs running at the same time. Eventually some operating systems offered dynamic allocation of resources. Programs could request further allocations of resources after they had begun running. This led to the problem of the deadlock solutions: --Deadlock prevention: require user programs to lock all records they will require at the beginning of the transaction rather than one at a time --Deadlock resolution: allow deadlocks but build in mechanisms to detect and break deadlocks
Edit Checks
-logic that can reside in the database or in lines of code -constrain/verify input data
Database Administration
-responsible for physical database design and for dealing with technical issues such as security enforcement, database performance, and backup and recovery -have to keep databases up and running, disaster recovery plan, custodian or physical places that houses the data --Functions: -Selection of hardware and software -Installing/upgrading DBMS -Tuning database and query processing performance -Managing data integrity and security -Data backup and recovery -Automation tools: DBeaver, Adminer, DBComparer, EMS SQL Manager for SQL Server, SQuirrel SQL, Firebird, SQLite Database Browser, ExecuteQuery, DBVisualizer Free, SQL Workbench, DBNinja for MYSQL
Default value (edit check)
-set a field contents to a pre-specified (default) value. In some cases the default values may be overridden, while in other cases they may not.
Unary relationship
-single entity and instances within that entity might be related -single entity and lines with cardinality -ex) employee (single entity) and you find out that instances can be related -one employee married to another employee
Forms Generators
-used to design the format and look of desired screens and forms
Views
-what you see on a screen may not be able what physically exists, can create a view of the database where you can only see 2 of the 18 columns, view is a virtual table -we want to audit the physical database. A view is a virtual table that is constructed dynamically from operations on a base table -A view does not contain any data It does not physically exist in the database -A view is created whenever the view is named in an SQL statement. -Views are merged with the queries to generate queries on base tables -I can create for you a view just for you only...should never audit views, bc you audit the physical database (know whether youre looking at the physical database or a view)
Toxic Data
3P+IP -3P's: PCI, PHI, PII -IP: Intellectual property
Field/Mode Check (edit check)
A field check (sometimes called a mode check) verifies that the entered data type is the appropriate mode for a field. For example, if a field is declared as a text or an alphanumeric field, the data input should be alphanumeric (letters and numbers). Other field modes include numeric, date, logical, counters, memo, and embedded objects (such as video, audio, or graphics).
Validity Check
A validity check compares entered data against pre-specified data stored within the computer (typically in a database or a file) to determine its validity. For example, to determine the validity of a user identification number, the computer would compare the entered primary key of the user to a stored list of valid user numbers.
Security Elements
Account: limit to a certain user privilege/permission: who has the rights to perform a task -Profile: set of privileges that are assigned to an account (user profile) -Role: predefined set of privileges and other roles
Incremental Forever Backup
Also known as progressive incremental backups, this variation is designed to work on disk-based backup systems. After an initial copy of the data is made, the software only backs up new and modified data. Because the backup is disk-based, there is no need to create periodic full backups.
Relation
Associated with logical design, blueprint for physical table I need to build
Change Management
Change management is the process of monitoring change requests, approving changes, documenting changes, testing changes, scheduling changes, implementing and following up on changes. Risks include unauthorized changes, circumventing change controls, inadequate testing of changes, inadequate document of changes, and lack of communication about changes.
Limit Check (edit Check)
Compares entered data to a predetermined upper OR lower limit
Domain Constraint
Has to be this data type
RTO (recovery time objective)
How long is it ok for you to be down? [database] -discuss this with your client and see what they're tolerance of risk is
RPO (recovery Point Objective)
How much data are you willing to lose? [database]
IT Elements of SOX
IT Change Management - Understanding and assessing the process that governs changes to databases, including separation of duties between people with access to databases in three key areas: development, testing and production -Logical Access to Data - Paying attention to who has access to what and who has access to too much so that you can prevent unauthorized access to data. This is why you focus on personnel controls and physical access controls. -IT operations - Understanding and assessing the policies and procedures used in day to day management of database, applications and infrastructure
DDL
Includes Commands to set up the data structures; can be used to define user views of the data (logical views/schemas). Create, alter, and delete table structures; This includes specifying all needed data items (fields), the primary key field (the unique identifier), the data type of each data item (such as date, alphanumeric, numeric, graphic, video, animation, picture, or audio), the width of each data item field, any data item default values, and any data item constraints or validation rules. The developer also specifies how data items and structures are related. --set up the structure, nothing to do with putting data in there --set up constraints here (bc that's when we do the tables)
Authentication
Is the user who he/she says they are? -Can be performed by operation system, network, database Ex: password mgmt, password encryption/hashing, biometrics, trust relationships Risks : Default accounts with default password are often left unprotected by admin DBMS often have weak password mgmt features in that they may not have intruder lockouts, periodic password change requirements etc Database admin often use batch scripts to perform admin tasks, often these scripts contain user IDs and passwords and are left unprotected Biggest risk is people not turning off defaults --Controls: -Default accounts should have their password changed or disabled -Harden = turn off default features -Companies should upgrade to newer versions of DBMIS that have more robust password mgmt features -Batch files, setup files or log files should either be deleted or protected via stronger ACLs -Admin should be educated to not pass credentials via command line to prevent them showing up in process listings -ACLs on tables, views, registries that contain password hashes should be tightened and periodically reviewed
Full Backup (part of Disaster Recovery)
Makes a copy of the entire database -all data and structures
Entities
Nouns that we want to keep data about -tables (?)
Reasonableness check
Reasonableness checks verify whether the amount of a new transaction record (or update to a master file record) appears reasonable when compared to other elements associated with each item being processed. For example, if an employee is coded as a clerk, it is probably unreasonable that her pay per week is $5,000. Note that a reasonableness check is not the same as a limit check. It might be reasonable for the president to have a weekly check of $5,000. The reasonableness of the pay is based on the relationship between position (clerk versus president) and the amount of the pay, not a fixed dollar amount (i.e., upper limit).
Referential Integrity
Referential integrity is a safeguard to ensure that every posted foreign key attribute relates to a primary key attribute. For example, suppose you have two tables: a Salesperson table and a Sales table. Since the two tables have a relationship (a salesperson participates in each sale), you must include the primary key attribute of the Salesperson table (e.g., salesperson number) in the Sale table; salesperson number is a foreign key attribute in the Sales table. You want to invoke referential integrity to ensure a link between the two tables. Referential integrity prevents writing a sale in the Sale table without a valid salesperson number from the Salesperson table. It also prevents deleting a sales person from the Salesperson table as long as the salesperson has sales in the Sale table.
Ternary
Simultaneous relationship between instances of more than 2 entities
SQL Server Roles:
Sysadmin: allows any database server act --Serveradmin: server configuration, shutdown --Setupadmin: linked servers, startup procedures --Securityadmin: logins, passwords, db permissions --Processadmin: server processes --Dbcreator: create, alter, drop databases --Diskadmin: work with disk files, size, location → go access to server then access to databases then access to objects
Valid Sign Check
The valid sign check is used to highlight illogical balances in a master file record. For example, a negative balance for the quantity on hand for a particular item in inventory is a likely error.
Infrastructure
The term infrastructure in an information technology (IT) context refers to an enterprise's entire collection of hardware, software, networks, data centers, facilities and related equipment used to develop, test, operate, monitor, manage and/or support information technology services. -A company's IT infrastructure includes the physical IT devices and products, but does not include the employees, documentation or processes used in operating and managing IT services. -How does data flow through the system. Who gets data, how do they get access to it? theoretical
Report Generators
Used to define the format and look of desired output
Disk Mirroring
You could keep two copies of the database and update simultaneously. When a database encounters an error, you could just hot swap or switch an existing copy of the database. --Does not protect you from loss of both copies -also if you store a corrupted file in one, you don't have a clean backup to revert to -RAID is apart of this
Authorization Matrix
a conceptual model of security. A rectangular array of cells with one row per subject and one column per object. The entry in a cell indicates the access mode that the subject is permitted to exercise on the object. Each column is equivalent to an access control list for the object and each row is equivalent to an access profile for the subject
Access Control List
a list of permissions attached to an object. Specifies which users or system processes are granted access to objects, as well as what operations are allowed on given objects. Each entry in a typ ACL specifies a subject and an operation
Calculated Fields (SQL Server Controls)
a numeric or date field that derives its data from the calculation of other fields, the data not entered into a calculated field by the user
Durable (ACID)
all changes from transaction are permanent, meaning results are not lost by a system failure. Not lost if there is a system failure -Recovery subsystem
Atomic (ACID)
all or nothing, meaning every step in transaction successfully completes before save. How do you handle the fact that transaction has multiple parts and they have steps and the steps are done in the right order. Start and finish transaction --Recovery subsystem
Multivalued Attributes
an attribute can lead to more value. For example, consider the skill attribute of the relation Employee. Each employee may not be limited to a single skill value. An employee could have one skill, two skills, or many skills that you want to list in your database. To avoid empty fields, you should use a separate relation to model multivalued attributes. The new relation would include the primary key of the relation (i.e., Employee number) and the multivalued attribute (a 1 to many relationship, one employee with many skills).
Open Purchase Order
approved purchase order sent to vendor that has not yet resulted in a purchase (not yet received and recognized liability)
Architecture
architecture is a term applied to both the process and the outcome of thinking out and specifying the overall structure, logical components, and the logical interrelationships of a computer, its operating system, a network, or other conception. Computer architecture can be divided into five fundamental components: input/output, storage, communication, control, and processing -the term design connotes thinking that has less scope than architecture. An architecture is a design, but most designs are not architectures. A single component or a new function has a design that has to fit within the overall architecture. -A similar term, framework, can be thought of as the structural part of an architecture.
Derivable attributes
are attributes that you can create or compute using other relation attributes. There is no need to store derivable attributes in the physical tables. You can use queries or computer code to compute them. For example, there is no need to store sales total in a table, if you have already stored the quantities of each item sold, the price per item, and the shipping and handling costs. You can derive the sales total by taking the quantities times price for each item sold to get the extended price per item, sum all the extended prices, and then add the shipping and handling costs.
Optimistic Concurrency Controls
assumes that although conflicts are possible they will be rare. Instead of locking every record every time that it is used, the system merely looks for indication that two users actually did try to update the same record at the same time. If that evidence is found, then one user's updates are discarded and the user is informed.
Composite Attributes
attributes can (and likely should) be decomposed into separate attributes. An example of a composite attribute is address. An address is actually composed of several separate attributes: street address, city, state, and zip code. You should represent the four separate attributes rather than combining all the detail into one aggregated attribute.
Database
collection of related-data in an organized fashion that allows for easier access, management, and updates. -Data itself: tables and within the tables they have values -Database mgmt. system—use some sort of software, SQL the software -Server is where the data tables are physically sitting. Where you have things that someone wants to access. -Audit trail is when the order entry, GL and invoicing hits the system -Applications computer programs that do some sort of tasks -Ex: invoicing—bills customers -I can put business rules in either the DBMS, or in the applications, not everything is in one spot
Block-level incremental backup
common form of incremental backup in which the backup software backs up storage blocks that have been written rather than backing up files and folders. Block-level backups are more efficient than file-level backups because only the changed blocks are backed up as opposed to the software having to back up the entire file
Range check (edit Check)
compares entered data to a predetermined acceptable upper AND lower limit. Data is not accepted without special authorization if the data fall outside the specified limits. Whereas a range check requires AND logic, a limit check used OR logic.
Data Confidentiality
data identification, data classification/prioritization, encryption, hashes. Not one size fits all. Dont assume that all data has to be protected at the same level, some data is mroe critical than others. You secure the data that is more valid/valuable to you
Default constraints (SQL Server Controls)
default is some value or function that the column ll take when an insert statement doesn't explicitly assign a particular value *if you create a default constraint have to make decision if going to let someone override your control*
Hierarchical Database
design that uses a one-to-many relationship for data elements. Hierarchical database models use a tree structure that links a number of disparate elements to one "owner," or "parent," primary record useful for a certain type of data storage, but it is not extremely versatile Its limitations mean that it is confined to some very specific uses. -Ex: where each individual person in a company may report to a given department, the department can be used as a parent record and the individual employees will represent secondary records, each of which links back to that one parent record in a hierarchical structure -these models make the most sense where the primary focus of information gathering is on a concrete hierarchy such as a list of business departments, assets or people that will all be associated with specific higher-level primary data elements. -Parent child constraint, cant handle many to many, Tree system, everything was one to many
User Interface
designed to aid programmers and users with the processes of data definition, data queries, and application design.
Check Constraints
determines if allowable values are present. Used to limit the value range that can be placed in a column
Closed-loop verification
enhances the validation check by adding a user confirmation to the process. This type of verification gives the users a role in verifying that the correct record is being processed and updated. This is accomplished by using an input data item to locate the record to be updated (the validation check), then displaying other data about the record so the data entry person can verify the correctness of the record. For example, if a sales order clerk enters a customer number for a customer buying merchandise on account, the computer uses the number to locate the customer record, then display additional customer data (such as name and address) on the computer screen. The user is then asked to verify that that correct customer record is being updated.
Synthetic Full Backup
full backup that is made by reading the previous full backup and subsequent incremental backups rather than reading the data from the primary storage
Patch management
helps ensure that software is updated consistently to reduce vulnerabilities within a system. Auditors should check for policies in place that address the release of new patches and the security issues that arise from their release.
DCL (Data Control Language)
includes commands used to manage and control the database, including commands to grant or revoke privileges, or access, to the database. These commands are useful to data administrators who need to control and secure the database environment. Provides basic security regarding commands and objects --Grant -- allows privileges --Deny -- disallows privileges --Revoke -- removes privileges
Suspense Files
indicates status, awaiting action ex: open SO, you shouldn't have an open SO that is there indef
History Files
indicates status, inactive/past files, when we close out files for the year ex: archive files
Data integrity
is this data accurate, complete, and consistent
Multi-level incremental backups
levels are defined as a way to decrease the amount of time it takes to restore a backup. Suppose you create a full backup, defined as Level 0, on Monday. You then create a Level 1 backup on Tuesday and a Level 2 backup on Wednesday. Under normal circumstances, if you created and then restored an incremental backup on Thursday, it would require the backups from Monday, Tuesday and Wednesday to also be restored. As an alternative, a backup administrator might create a Level 1 incremental backup on Thursday, which would include all the data created or modified since the last Level 1 backup was made (in this case on Tuesday). Consequently, a full restoration would require restoring the backups from Monday, Tuesday and Thursday rather than restoring the backups from Monday, Tuesday, Wednesday and Thursday.
Database Change Log
log to help determine the DML changes (insert, update, and delete operations) that were made to user tables in a database. Before and after images of records that have been modified by transactions. -before and after images of records that have been modified by transactions Before-image - copy of record or page of memory before modification After-image - copy of record or age of memory after modification
Incremental backup (part of Disaster Recovery)
makes a copy of the database changes since the last partial (incremental) backup
Concurrency Control
managing simultaneous transactions so data integrity is maintained and the operations do not interfere with each other in a multi-user environment. How you manage simultaneous requests, trying to update and change things at the same time. Ex: Ticketmaster giving you a minute and a half to get tickets --2 philosophies: --Serializability: finish one transaction before starting another --Locking mechanisms: the most common way of achieving serialization, data that is retrieved for the purpose of updating is locked for the updater, no other user can perform update until unlocked. (can do it for the database, table, block or page, record, field) --Issues --Lost Updates: two or more transactions attempt to update a record at same time, last update overwrites prior update --Dirty reads: transaction reads a record that has been changed, but the change has not been committed to the database, reads old data --Inconsistent/non-repeatable reads: transaction re-reads a record and retrieves different data each time --Phantom reads: transaction reads a record and upon re-read, the record has disappeared
Specific invoicing
map each cash receipt to specific sales order
Backup and Disaster Recovery
mechanism for restoring a database quickly and accurately after loss or damage Recovery facilities: backup facilities, journalizing facilities, checkpoint facility, recovery management -Backups: --Periodic, Cold, Hot, Full, Incremental
Byte Level Incremental Backup
monitor the file system for individual bytes that have changed and then back up those bytes on an incremental basis. This approach yields the smallest possible backups.
Defense in Layers
once I give you access to the server (layer 1), did I give you access to DB (layer 2), do I have access to all the object in the db (they can limit what you can do to the objects in the db aka read only and modify)
Data/Database Security
protection of data against accidental or intentional loss, destruction or misuse. Database security includes physical and logical control. It encompasses the entire client server environment - servers, client devices, network and its related components, users, etc Determine users' processing rights and responsibilities limit certain actions on certain objects to certain users or certain groups Views Authorization rules Recovery - Provide mechanism to restore database after loss or destruction
Open Purchase Requisition
purchase request that has not yet been approved and turned into a purchase order or has not yet been denied
Relationship
putting a line between two entities to show that they are related in some way. - Show on ERD because you need to link up data on database to be able to answer questions, it will help us figure out the structure of the database - When get to logical design and start creating relations, this is when we start adding additional fields that allow for linking - Key input from relation is youre going to see some additional field that are only there so we can link things up
Open Payable/liability/vendor's invoice
recorded liability that has not yet been paid
Database Transaction log
records all transactions and the databse modifications made by each transaction -record of essential data for each transaction processed against the database -not just debits and credits -will have unique Identifier -Datetime (when activity to the database occurred) -type of operation (insert, delete etc) -what activity occurred and what it's affecting
Data Administration
responsible for overall management of data resources in an organization, including maintaining corporate-wide definitions and standards -big picture, what data needs to flow who needs ownership of it, who owns it, who authorize certain things Functions: -Concerned with data needs and data flows throughout the organization -Data policies (data usage, security and authorization, data flows into and out of the organization), procedures, standards -Planning -Data conflict (ownership) resolution -Managing the data repository -Internal marketing of DA concepts -May be asked to assist with application development process by identifying data resources in the organization -May arbitrate the sharing of data across departments -May be charged with increasing the return on an organization's data investment
Default Constraints
some value or function that the column will take when and "insert" statement doesnt explicitly assign a particular value. Set value that field takes automatically
2 factor authentication
something the user has and something the user knows
3 factor Authentication
something the user has, something the user knows, biometric attribute (voice is the worst one)
PCIDSS
the database control for credit cards. if youre going to take credit cards in your business you must secure credit card information so that if someone hacked your server they cant get to it directly
Pessimistic Concurrency Control
the system assumes the worst. It assumes that two or more users will want to update the same record at the same time, and then prevents that possibility by locking the record, no matter how unlikely conflicts actually are.
Consistent (ACID)
to preserve database integrity, no other transactions on data permitted until save. Trying to hit an account and add to it and subtract from another, no other --Application program
Completeness Check (edit check)
verifies that all critical field data are entered. It checks for missing data or blanks.
Authorization
what is the user allowed to access once inside the database? Should the user be able to read, write, change, and delete data from the database? You dont have free reign Ex: views, encryption, ACL
Candidate Key
when you have more than one attribute from which to choose your primary identifier
