Database Management Extra 2 (b) Flashcards
??? : relationship between attributes in which 1 attribute (or group of attributes) determines the value of another attribute in the same table. Ex : if we know cookie price and quantity of cookies in a box, then we can find out the total box price of cookies.
??? : attribute that we use as starting point (variable on the left of equation “cookie price”) is called a determinant.
Functional Dependency / Determinants
A ??? ??? of a relation will functionally determine all other non-key attributes in a row, and a primary key of a relation will functionally determine all other non-key attributes in the row.
??? : to be a well-formed relation, every determinant must be a candidate key, any relation not well wormed should be broken into 2 or more well formed relations.
Candidate Key / Normalization Principles
Table with multi-valued attributes (remove multi-valued attributes) -> 1NF (remove partial dependencies) -> 2NF (remove transitive dependencies) -> 3NF
??? : table contains no multi-valued attributes (every attribute value is atomic), all relations are in 1NF.
??? : be in 1NF plus every non-key attribute is fully functionally dependent on the entire primary key, every non-key attribute must be defined by the entire key, not only part of the key, no partial dependencies.
??? : be in 2NF plus no transitive dependencies (functional dependencies on non-primary-key attributes); such dependencies are called transitive because primary key is determinant for another attribute, which is then a determinant for a third. Solution : non-key determinant w/transitive dependencies goes into new table, non-key determinant becomes primary key in new table and remains as foreign key in old table.
info / 1NF / 2NF / 3NF
??? : ANSI / ISO standard, based on relational algebra; comprised of DDL (used to define and manage database structures) and DML (data definition and updating, data retrieval / queries) and DCL (for creating user accounts, managing permissions, security, etc). SQL statements end with a semi-colon “;”.
??? : to create database objects.
entity class : description of structure and format of occurrences of the entity.
??? : specific occurrence of an entity class.
??? : entities have attributes that together describe the entity ex : projectName, startDate, etc; each attribute has a data type and other properties.
a ??? will identify a specific instance in the entity class ex : socialSecurityNumber, studentID, employeeID, etc …
SQL / CREATE / entity instance / attributes / key
??? ??? : relationships are named and classified by their cardinalities, which is a word that means “count” (as in number of items in a set); is the maximum number of entity instances that can participate in a relationship instance; maximum cardinalities are usually defined as one, many, or some other positive fixed number.
??? ??? : is minimum number of entity instances that must participate in a relationship instance; these values usually have a value or zero (optional) or one (mandatory).
Maximum Cardinality / Minimum Cardinality
??? : entity whose instances cant exist in database w/o existence of instance of another entity.
??? : can exist independently in database.
??? : benefits -> simplicity, improved query performance; negatives -> modification anomalies, redundant data, more storage space is required.
weak entity / strong entity / denormalization
??? : key from one relation is placed in the other as a foreign key; if both sides of relationship are optional, it doesn’t matter which table receives foreign key; if only 1 side of relationship is optional, the optional side receives foreign key.
??? : is implemented by placing primary key from 1 table into another table as a foreign key; foreign key always goes into many side of the relationship; the 1 side is called the parent and N side is called the child.
??? : a new table is created and is called intersection table or associative entity; intersection table usually has composite key comprised of keys from each table to which it is connected.
1:1 Relationship / 1:N Relationship / N:M Relationship
??? : identifiers, properties (these are data items associated w/the object), and methods (these are algorithms that allow the object to perform tasks).
??? : is a data structure that contains copy of some of the data from 1 or more existing database tables and makes querying far more efficient (more speed).
??? : hashing algorithm is used to convert an input value into a location w/in an index, which in turn contains or points to the actual data row.
Objects classes have / An Index / Hashed Index
??? support management assessment, analysis, planning, control, etc … but dont support real time operations. Reporting Systems : can sort, filter, group, and make elementary calculations on operational data (in real time). Data Mining Apps : perform sophisticated analyses on data, analyses that usually involve complex statistical and math processing (typically doesnt rely on real-time data, but archived historical data).
Extract, Transform, Load -> data needs to be cleaned before they can be added to a data warehouse, data commonly needs to be transformed before added to a data warehouse.
BI systems / ETL
??? : technique for dynamically examining database data; uses simple math functions like Sum, Average, and Count.
??? : refers to rapidly expanding amount of data being stored and used by org.’s.
OLAP / Big Data
