O GoConqr está de visual novo! Conte para nós o que você achou.

Registre-se grátis

Flashcards

ITECH2004 - Data Modeling

Ash Z Inglês 111 flashcards

Chen Notation

Flashcards do conjunto (111)

  1. 1Why Databases?- Data is everywhere
    - Persistent Data
    - Shareable
    - Secure
    - Store
    - Manage
    - Retrieve
  2. 2Sources for Generating Data- Facebook
    - Pharmacy
    - Grocery Store
    - E-Commerce
  3. 3Data- Raw Facts
    - Building Block Of Information
    - No meaning to the end-user
    - No context
    - Birthdate - Telephone Number
  4. 4Information- Context
    - Processed Data
    - Bedrock of knowledge
    - Organising data for patterns
    - Accurate
    - Timely
    - Relevant
  5. 5What is a database?- Shared
    - Integrated
    - Stores Data
  6. 6Meta-DataData About Data which the end-user data is integrated and managed by
  7. 7DBMSDatabase Management System
    - Collection of programs
    - Manages the database structure
    - Controls access to the data
  8. 8DBMS Features- Sharing
    - Security
    - Integration
    - Minimized Inconsistency
    - Improved Decision Making
    - Data Dictionary
    - Multiuser Access
    - Integrity Management
  9. 9DBMS Disadvantages- Cost
    - Complexity
    - Dependency on vendor
    - Frequent Upgrade
  10. 10Types Of Databases- Single-User - One User at a time
    - Multiuser - Multiple users at the same time
    - Analytical Database - Historical data and business metrics used for decision making
    -
  11. 11File System Problems- Development Time
    - Complex System
    - Extensive Programming
    - Lack of security
  12. 12Data AnomaliesDevelop when not all the required changes in the redundant data are made:
    - Update Anomalies
    - Insert Anomalies
    - Delete Anomalies
  13. 13Data Model Relationships- One to Many (1:M)
    - Many to Many (M:N)
    - One to One (1:1)
  14. 14Chen Notation- Diamond in middle explaining the relationship e.g. Paints
    - Uses relationships 1 on left entity and M on right    Chen Notation
  15. 15Crows FootCrows Foot
  16. 16UML Class NotationUses (1:1, M:N) Both sidesUML Class Notation
  17. 17Chatersistics of Big DataVolume
    Velocity
    Variety
  18. 18Date Model Types:Hierarchical
    Network
    Relational
    Object-Oriented
    Extended Relational
  19. 19Primary KeyAttribute or combination of attributes that uniquely identifies a row
  20. 20DependenciesDetermination - Knowing a value can determine another
    Functional - Value of one or more attributes determines the value of one or more attributes
    Full Functional - Entire collection of attributes in the determinant is necessary for the relationship
  21. 21Composite KeyKey composed of more than one attribute
  22. 22Key AttributeThe attribute that is part of a key
  23. 23SuperkeyCan uniquely identify any row in a table
  24. 24Candidate KeyMinimal Superkey
  25. 25Foreign Keythe primary key of one table that has been placed into another table to create a common attribute
  26. 26Ways to handle nullsFlags
    NOT NULL
    UNIQUE
  27. 27SELECTUnary Operator that yields a horizontal subset of a table
  28. 28PROJECTYields a vertical subset of a table
  29. 29UNIONCombines all rows from two tables, excluding duplicate rows
    The same set of attributes needed
  30. 30INTERSECTYields only the rows that appear in both tables
  31. 31DIFFERENCEYields all the rows not found in the other table
  32. 32PRODUCTYields all possible pairs of rows from two tables
  33. 33NATURAL JOINLinks tables by selecting only the rows with common values in their common attribute
  34. 34Equijoinlinks tables on the basis of an equality condition that compares specified columns of each table
  35. 35Theta joinlinks tables using an inequality comparison operator
  36. 36Inner joinonly returns matched records from the tables that are being joined
  37. 37Outer joinmatched pairs are retained and unmatched values in the other table are left null
    Left outer join: yields all of the rows in the first table, including those that do not have a matching value in the second table
    Right outer join: yields all of the rows in the second table, including those that do not have matching values in the first table
  38. 38Data DictionaryDescription of all tables in the database created by the user and designer
  39. 39System CatalogSystem Data dictionary that describes all objects within the database
  40. 40IndexesOrderly arrangement to logically access rows in a table
    Index key: index’s reference point that leads to data location identified by the key
    Unique index: index key can have only one pointer value associated with it
  41. 41Composite AttributeComprised of two attributes
  42. 42ConnectivityInclude 1:1, 1:M, and M:N
  43. 43Cardinalityexpresses the minimum and maximum number of entity occurrences associated with one occurrence of related entity
  44. 44Weak (non-identifying) relationshipPrimary key of the related entity does not contain a primary key component of the parent entity
  45. 45Strong (identifying) relationshipsPrimary key of the related entity contains a primary key component of the parent entity
  46. 46Weak EntitiesExistence-dependent
    Has a primary key that is partially or totally derived from parent entity in the relationship
  47. 47Unary relationshipassociation is maintained within a single entity
  48. 48Binary relationshiptwo entities are associated
  49. 49Ternary relationshipthree entities are associated
  50. 50Recursive relationshiprelationship exists within a single entity type
  51. 51EERMEnhanced entity-relationship model
    Result of adding more semantic constructs to the original entity-relationship (ER) model
  52. 52Entity SupertypeGeneric entity type related to one or more entity subtypes
    Contains common characteristics
  53. 53Entity subtypeContains unique characteristics of each entity subtype
    There must be different, identifiable kinds of the entity in the user’s environment
    The different kinds of instances should each have one or more attributes that are unique to that kind of instance
  54. 54Specialization HierarchyEntity supertypes and subtypes organized in a specialization hierarchy
    Depicts arrangement of higher-level entity supertypes and lower-level entity subtypes
    Relationships are described in terms of “is-a” relationships
    Subtype exists within the context of a supertype
    Every subtype has one supertype to which it is directly related
    Supertype can have many subtypes
  55. 55InheritanceEnables an entity subtype to inherit attributes and relationships of the supertype
    All entity subtypes inherit their primary key attribute from their supertype
  56. 56Subtype DiscriminatorAttribute in the supertype entity that determines to which entity subtype the supertype occurrence is related
  57. 57Entity Clustering“Virtual” entity type used to represent multiple entities and relationships in ERD
    Formed by combining multiple interrelated entities into a single, abstract entity object
  58. 58Specialization HierarchyA specialization hierarchy depicts the arrangement and relationships between entity supertypes and entity subtypes
  59. 59Natural KeyNatural keys are identifiers that exist in the real world
  60. 60Normalizationevaluating and correcting table structures to minimize data redundancies
    Design Process
  61. 61Normal FormsFirst normal form (1NF)
    Second normal form (2NF)
    Third normal form (3NF)
  62. 621NFTable Format
    No Repeating Groups
    PK Identified
    All Key Attributes are Dependent on the PK
  63. 632NF1NF
    No Partial Dependencies
    If the 1NF has a single-attribute primary key, then the table is automatically in 2NF
  64. 643NF2NF
    No Transitive Dependencies
  65. 65Partial DependencyFunctional dependence in which the determinant is only part of the primary key
  66. 66Transitive Dependencythe attribute is dependent on another attribute that is not part of the primary key
  67. 67Surrogate KeySystem Defined
    When the PK is considered unusable
    AUTOINCREMENT VALUE
  68. 68Boyce Codd Normal FormLike 3NF
  69. 694NF3NF
    No Multivalued Dependencies
  70. 70DenormalizationJoining a larger number of tables
    Defects in normalized table
  71. 71SELECTspecifies attributes to be returned by the query
    ATTRIBUTES
    SELECT columnlist
    FROM tablelist;
  72. 72FROMThe TABLE
    SELECT columnlist
    FROM tablelist;
  73. 73WHEREFilters Rows of data on criteria
    SELECT columnlist
    FROM tablelist
    [WHERE conditionlist ]
  74. 74GROUP BYgroups the rows of data into collections based on sharing the same values in one or more attributes
  75. 75HAVINGfilters the groups formed in the GROUP BY clause based on provided criteria
  76. 76ORDER BYsorts the final query result rows in ascending or descending order based on the values of one or more attributes
    SELECT columnlist
    FROM tablelist
    [ORDER BY columnlist [ASC|DESC] ];
  77. 77Arithmetic Operators+ (Add)
    - (Subtract)
    * (Multiply)
    / (Divide)
    ^ (Raise to the Power of)
  78. 78JOIN ONJoins on a condition
    SELECT column-list
    FROM table1
    JOIN table2
    ON join-condition
  79. 79Comparison OperatorsComparison Operators
  80. 80Data TypesNUMBER - NUMBER (7,2)
    NUMERIC
    INTEGER - Whole Number
    CHAR - Fixed character up to 255
    VARCHAR - 23 Characters
    DATE - Julian Date format
  81. 81Aggregate ProcessingTakes a collection of rows and reduces it to a single row
  82. 82Aggregate functionsCount
    MIN and MAX
    SUM and AVG
  83. 83COUNTThe Count function is used to tally the number of non-null values of an attribute.
  84. 84MINA SQL aggregate function that yields the minimum attribute value in a given column.
  85. 85MAXA SQL aggregate function that yields the maximum attribute value in a given column.
  86. 86SUMA SQL aggregate function that yields the sum of all values for a given column or expression.
  87. 87AVGA SQL aggregate function that outputs the mean average for a specified column or expression.
  88. 88SubqueryA query inside another query
    SELECT FROM WHERE
    P_CODE, P_PRICE PRODUCT P_PRICE >= (SELECT AVG(P_PRICE) FROM PRODUCT);
  89. 89CREATE TABLECREATE TABLE tablename (
    column1
    column2
    PRIMARY KEY
    FOREIGN KEY
    REFERENCES tablename] [,
    CONSTRAINT constraint ] );
  90. 90ALTERAlter A table
    Put Example here
  91. 91INSERT INTOAdd data into a column
    INSERT INTO tablename VALUES (value1, value2, …, valuen)
    INSERT INTO target_tablename[(target_columnlist)]
    SELECT source_columnlist
    FROM source_tablename;
  92. 92UPDATEUPDATE tablename
    SET columnname = expression [, columnname = expression]
    [WHERE conditionlist ];
  93. 93Virtual TablesCreating a view
    CREATE VIEW viewname AS SELECT query
  94. 94System Performance GuidelinesCPU
    RAM
    Storage
    Network
    OS
    Application
  95. 95Operation ModesAutomatic Query Optimization - Finds the most cost-effective path done by DBMS
    Manual Query Optimization - Optimization done by End-user or programmer manually
  96. 96Data SparsityLow - Limited amount of values will be entered
    High - A lot of values can be entered making indexes useless
  97. 97Optimizer ChoicesRule-Based Optimizer - Preset Rules
    Cost-Based Optimizer - Algorithms
  98. 98Forming a query StepsPinpoint what columns are required (SELECT)
    Identify Source tables
    (FROM)
    Decide how to join tables
    Establish action criteria
    (WHERE)
    Determine order you want the output
    (ORDER BY)
  99. 99Query Processing- Parsing
    o DBMS parses the SQL query and chooses the most efficient access/execution plan
    - Execution
    o DMBS executes the SQL query using the chosen execution plan
    - Fetching
    o DBMS fetches the data and sends the result set back to the client
  100. 100Dirty DataInaccurate or inconsistent
  101. 101Data Qualityo Ensures accuracy
    o Validity
    o Timeliness
  102. 102Data Profiling Softwareo Determines data patterns
    o Compares data against standards
  103. 103Master Data Management (MDM)o Helps prevent dirty data by coordinating across multiple systems
  104. 104Top management roles for data- Strategic Decision making
    - Growth Opportunities
    - Define and enforce organizational policies
    - Reduce costs
    - Boost productivity
    - Provide Feedback
  105. 105Middle management roles for data- Tactical Planning
    - Monitor resources
    - Evaluate performance
    - Enforce security
  106. 106Operational management roles for dataRepresent and support company operations
    Produce query results within specified performance levels
    Enhance the company’s short-term operations
    Create query results within specified performance levels
    Enhance the company’s short-term operations
  107. 107Data AdminStrategic Planning
    Sets long term goals
    Sets policies and standards
    Job is broad in scope
    Focuses on long term
    Managerial Orientation
  108. 108Database AdminControls and supervises
    Executes plans to reach goals
    Enforces policies and procedures
    Job is narrow in scope
    Short Term
    Technical Orientation
    Backup and Recovery
  109. 109Big Data Characteristics (More)Variability - Changes data-based
    Veracity - Trustworthiness of data
    Value - Meaningful insight
    Visualization - Ability to graphically resent data to make it understandable
  110. 110Polyglot Persistencecoexistence of a variety of data storage and management technologies within an organization’s infrastructure
  111. 111—

Informações

Descrição
FlashCards sobre ITECH2004 - Data Modeling , criado por Ash Z em 28-10-2019.
Criado por
Ash Z
Visualizações
1
Avaliação

Mais sobre Database Design and Entity-Relationship Diagrams

Fale Conosco

Sobre o que é sua mensagem?

O que você acha do novo visual?

Você gostou?