Raw facts that have little meaning unless organized and
Raw facts that have little meaning unless organized and
Enterprise Database DesignKey TermsWeek 1 Key Terms:Data – Raw facts that have little meaning unless organized and context provided.Information – Data that have been processed to provide context to increase theknowledge of the person who uses it.Database – An organized collection of logically related data.Database management system – A software system that is used to create, maintain,and provide controlled access to user databases.Data governance – The management of the availability, usability, integrity, and securityof the data created and used in an enterprise. A robust data governance programincludes a governing, a defined set of procedures, and a plan to execute thoseprocedures.IT governance – The management of IT projects, IT systems and IT strategy with theorganization’s business strategy, ensuring that companies stay on track to achieve theirstrategies and goals.Week 2 Key Terms:Conceptual data model – A detailed, technology-independent specification of theoverall structure of organizational data. The conceptual data model is typically an EntityRelationship Diagram showing all entities and relationships between entities.Logical data model – The representation of data for a data management system. Inthe case of a relational data model, elements include tables, columns, rows, primaryand foreign keys, as well as constraints.Physical data model – A set of specifications that detail how data from a logical datamodels are stored in a computer’s secondary memory for a specific databasemanagement system. There is one physical data model for each logical data model.Entity – A person, place, object, event, or concept in the user environment about whichthe organization wishes to maintain data.Associative entity – An entity that associates the instances of one or more entity andcontains attributes that are peculiar to the relationship between those entity instances.Weak entity – An entity whose existence depends on some other entity.1 of 4Enterprise Database DesignKey TermsWeek 2 Key Terms Continued:Entity-relationship model – A logical representation of the data for an organization orfor the business requirements.Relationship type – A meaningful association between entities.Attribute – A property or characteristic of an entity type that is of interest to theorganization.Identifier – An attribute (or combination of attributes) that uniquely identifies individualinstances of an entity type.Constraint – A rule that cannot be violated by database users.Cardinality constraint – Specifies the number of instances of one entity that can (ormust) be associated with each instance of another entity.Candidate key – A “candidate to be the primary identifier” of an entity.Primary key – A primary key is a candidate key that has been selected as the identifierfor an entity type. The primary key uniquely identifies a row; cannot contain null values.A primary key can be composed of multiple columns.Foreign key – Column(s) whose values match the values in a candidate key of anothertable.Week 3 Key Terms:Normalization – The process of decomposing relations with anomalies to producesmaller, well-structured relations.Determinant – The attribute on the left-hand side of the arrow in a functionaldependency.Functional dependency – A constraint between two attributes or two sets of attributes.Transitive dependency – A functional dependency between two (or more) non-keyattributes.Normal form – A state of a relation that results from applying simple rules regardingfunctional dependencies (or relationships between attributes) to that relation.Partial functional dependency – A functional dependency in which one or more nonkeyattributes (such as Name) are functionally dependent on part (but not all) of theprimary key.2 of 4Enterprise Database DesignKey TermsWeek 3 Key Terms Continued:1NF – No multi-valued attributes; no repeating groups that have assumed values ratherthan real values; every attribute value is atomic; all relations are in 1NF.2NF – Every non-key column depends on a entire primary key, not part of a key; nopartial functional dependencies; every relationship/table is in both 2NF and 1NF.3NF – Every non-key column depends only on a key not on non-key columns; notransitive dependencies; every relationship/table is in 3NF, 2NF and 1NF.Week 4 Key Terms:Conceptual data model – A detailed, technology-independent specification of theoverall structure of organizational data.Logical data model – The representation of data for a particular data managementtechnology. In the case of a relational data model, elements include tables, columns,rows, primary and foreign keys, as well as constraints.Physical data model – A set of specifications that detail how data from a logical datamodels is stored in a computer’s secondary memory for a specific databasemanagement system. There is one physical data model for each logical data model.Database – An organized collection of logically related data.Database management system – A software system that is used to create, maintain,and provide controlled access to user databases.Structured Query Language (SQL) – This is the language used to retrieve andmanage data within relational database management systems. The language includesgroupings of the language such as DDL, DCL, and DML.Data Definition Language (DDL) – These commands are used to create, alter, anddrop tables and other database structures. The commands include the CREATE,ALTER, DROP, and TRUNCATE commands.Data Manipulation Language (DML) – These commands are used to manipulate thedata within the tables. The commands include INSERT, UPDATE, DELETE, andMERGE.Data Control Language (DCL) – These commands are used to authorize users toaccess various data and data structures. These commands use keywords GRANT,DENY, and REVOKE.3 of 4Enterprise Database DesignKey TermsWeek 5 Key Terms:Database management system – A software system that is used to create, maintain,and provide controlled access to user databases.Structured Query Language (SQL) – This is the language used to retrieve andmanage data within relational database management systems. The language includesgroupings of the language such as DDL, DCL, and DML.Data Definition Language (DDL) – These commands are used to create, alter, anddrop tables and other database structures. The commands include the CREATE,ALTER, DROP, and TRUNCATE commands.Data Manipulation Language (DML) – These commands are used to manipulate thedata within the tables. The commands include INSERT, UPDATE, DELETE, andMERGE.Data Control Language (DCL) – These commands are used to authorize users toaccess various data and data structures. These commands use keywords GRANT,DENY, and REVOKE.Join – Return rows when there is at least one match in both tablesLeft Outer Join – Returns all rows from the left table, even if there are no matches inthe right tableRight Outer Join – Returns all rows from the right table, even if there are no matches inthe left tableFull Outer Join – Returns rows from both tables, even when the row is not matched.In – Operates on any records in the containing command where the specifiedexpression appears in the subquery results.Exists – Operates on any records in the containing command where the subquery resulthas at least one row.4 of 4