¡GoConqr estrena diseño! Cuéntanos qué te parece.

Regístrate gratis

Fichas

SIT772.2 - DATA MODELLING

Amber Saxon SIT772 Inglés 123 fichas

SIT772.2 - DATA MODELLING

Fichas del conjunto (123)

  1. 1ERD modelling level ENTITYAt the ERD modeling level, an ENTITY refers to the ENTITY SET, not to a single entity.
    In the ER model, an ENTITY corresponds to a TABLE and its SCHEMA, and the ENTITY INSTANCE corresponds to a row in a table.
  2. 2ERD modelling CROW'S FOOT NOTATIONAn ENTITY is represented by a rectangle that contains the entity’s name.
    The entity name (a noun) and fields are usually written in all capital letters.
    The attributes are written below the entity name, separated by a line, with each attribute on a new line.
  3. 3ERD modelling CROW'S FOOT NOTATION entity/attributes STUDENT ENTITY exampleThe attributes are written below the entity name, separated by a line, with each attribute on a new line.ERD modelling CROW'S FOOT NOTATION entity/attributes STUDENT ENTITY example
  4. 4ERD modelling CHEN'S NOTATIONChEN'S NOTATION is an alternate way to describe ENTITIES and ATTRIBUTES diagrammatically.
    The entity rectangle is usually encompassed by a series of attribute ovals connected to the entity rectangle using a line.
  5. 5ERD modelling CHEN'S NOTATION STUDENT ENTITY exampleThe entity rectangle is encompassed by a series of attribute ovals connected to the entity rectangle using a line.ERD modelling CHEN'S NOTATION STUDENT ENTITY example
  6. 6Required attributesRequired attributes are attributes which must not be left empty.
    For example, the attributes last name and first name would probably be required attributes as all students must have a name.
  7. 7Optional attributesOptional attributes may be left empty.
    For example, the attribute email could be left empty if the student doesn’t have a personal email address.
  8. 8The Attribute DomainThe attribute domain is a set of possible values for an attribute.
    For example, the initials would probably have a single character as its attribute domain, for example A, T, J, or Z. Some attributes may share domains.
  9. 9Identifiers or Primary KeysAttributes known as IDENTIFIERS or PRIMARY KEYS are those which UNIQUELY identity each entity or instance.
    Must not be NULL.
    Must be UNDERLINED in ERDs.
  10. 10Composite IdentifiersAttributes known as COMPOSITE IDENTIFIERS are those where a PRIMARY KEY is composed of TWO or MORE different ATTRIBUTES.
    For example, the combination of last name and first name can uniquely identity each student (given that there are not two John Smith’s of course!).
  11. 11Composite AttributeA COMPOSITE ATTRIBUTE is when an attribute can be further subdivided into some SIMPLER ATTRIBUTES.
    For example, if we were to have defined an attribute full name, it would probably be able to be subdivided into initials, last name and first name. The subdivided attributes can no longer be further subdivided and are therefore no longer composite attributes.
  12. 12RelationshipA RELATIONSHIP is an association between ENTITIES.
  13. 13ConnectivityThe term CONNECTIVITY is used to describe the RELATIONSHIP classification, which is either:
    One-to-one (1:1),
    One-to-many (1:M) or
    Many-to-many (M:M).
    Designers need to consider BOTH DIRECTIONS of the relationship.
  14. 14CardinalityCARDINALITY expresses the minimum and maximum number of ENTITY OCCURRENCES associated with one occurrence of the related entity.
    Cardinality is established by very concise statements known as BUSINESS RULES, and it is important that database designers can identify the minimum and maximum number of entity occurrences.
  15. 15Cardinality an exampleOne professor can teach a maximum of four classes (1,4)
    and one class can only be taught by one professor (1,1).
    The application software should apply that cardinality to the limit the number of classes that a professor can teach.
  16. 16Cardinality An ERD exampleOne professor can teach a maximum of four classes (1,4)
    and one class can only be taught by one professor (1,1).    Cardinality An ERD example
  17. 17Cardinality Using N values examplethe university may not run a unit if there are less than 10 students enrolled. In addition, a standard classroom can only accommodate a maximum of 30 students. Therefore, the application software should use that cardinality to limit enrollment in the class to (1, N), where N can have a minimum of 10 and a maximum of 30.
  18. 18CARDINALITY Optional ParticipationSome of RELATIONSHIPS may have OPTIONAL PARTICIPATION. This means that, in a relationship between two entities, one entity may not necessarily warrant a corresponding entity in that relationship.
    For example, a teaching staff member may be allocated as a unit chair for a unit. Note the may be in the business rule. We can use the (0,N) cardinality to represent this the optional participation in the relationship; a teaching staff member may not be a unit chair, or may be chairing one, two or N number of units.
  19. 19CARDINALITY Optional ParticipationA teacher may be allocated as a unit chair for a unit. We can use the (0,N) cardinality to represent this OPTIONAL PARTICIPATION in the relationship; a teaching staff member may not be a unit chair, or may be chairing one, two or N number of units.CARDINALITY Optional Participation
  20. 20CARDINALITY Mandatory ParticipationSimilar to optional participation, we can use mandatory participation to enforce that, in a relationship between two entities, one entity must always have a corresponding entity in that relationship.
  21. 21CARDINALITY Mandatory Participation EXAMPLEAll degrees at Deakin must contain a certain number of units for a student to pass. Note the must in the business rule. We can use the (1,N) cardinality to represent the MANDATORY PARTICIPATION in the relationship; a degree should always have one or more units for the degree to be attained.
  22. 22CARDINALITY Mandatory Participation EXAMPLEThe ERD for this relationshipCARDINALITY Mandatory Participation EXAMPLE
  23. 23ER Diagrams and ParticipationCrow’s Foot notation
    PARTICIPATION effects the strokes within ERD relationship lines:
    these combinations are often referred to as CARDINALITY and also support instances where specific PARTICIPATION and CRADINALITIES are not required.
  24. 24CROW'S FOOT NOTATION ER Diagrams and ParticipationPARTICIPATION effects the strokes within ERD relationship lines:CROW'S FOOT NOTATION ER Diagrams and Participation
  25. 25ERD Entity / ObjectIn an ERD
    Because an entity represents a real-world object, the words entity and object are often used interchangeably.
  26. 26Different Types of AttributesNot only are there normal attributes, but some attributes may have differing characteristics;
    * Required Attributes
    * Optional Attributes
    * The Attribute Domain
    * Identifiers or Primary Keys
    * Composite Identifiers
    * A Composite Attribute
  27. 27Relationship DegreeThe number of entities associated by the one relationship.
  28. 28Relationship Degree TypesUnary
    Binary
    Ternary
  29. 29RELATIONSHIP DEGREE Unarythe association of that relationship is maintained within a
    SINGLE ENTITY
  30. 30RELATIONSHIP DEGREE BinaryThe relationship exists between
    TWO ENTITIES
  31. 31RELATIONSHIP DEGREE TernaryThe relationship exists between
    THREE ENTITIES
  32. 32UNARY RelationshipThe ERD relationship will show a relationship line going from that entity back into that entity.
  33. 33UNARY exampleUNARY example
  34. 34TERNARY Relationships aka Higher-Order RelationshipsTernary, or higher-order relationships, imply that the relationship is associated between
    three (or sometimes more) different entities.
  35. 35TERNARY Relationship example 1TERNARY Relationship example 1
  36. 36TERNARY RELATIONSHIP example 1 cont'dThe prescription entity is able to join all the info up from within the initial ternary relationship and make it practically accessible.TERNARY RELATIONSHIP example 1 cont'd
  37. 37Predicate LogicUsed exclusively in mathematics to provide a framework in which an assertion (statement of fact) can be verified as either true or false
  38. 38Set TheoryA part of mathematical science that deals with sets, or groups of things, and is used as the basis for data manipulation in the relational model
  39. 39TupleIn the relational mode, a table row
  40. 40DomainIn data modeling, the construct used to organize and describe an attribute's set of possible values
  41. 41Primary Key (PK)An identifier composed of one or more attributes that uniquely identifies a row. Also, a CANDIDATE KEY selected as a UNIQUE ENTITY IDENTIFIER.
  42. 42KeyOne or more attributes that determine other attributes. See also superkey, candidate key, primary key (PK), secondary key and Foreign Key (FK)
  43. 43DeterminationThe role of a key. In the context of a database table, the statement "A determines B" indicates that knowing the value of attribute A means that the value of attribute B can be looked up
  44. 44Functional DependenceWithin a relation R, an attribute B is functionally dependent on an attribute A if and only if a given value of attribute A determines exactly one value of attribute B. The relationship "B is dependent on A" is equivalent to "A determines B", and is written as A-->B
  45. 45DeterminantAny attribute in a specific row whose value directly determines other values in a row. See also Boyce-Codd Normal Form (BCNF).
  46. 46DependentAn attribute whose value is determined by another attribute.
  47. 47Full Functional DependenceA condition in which an attribute is functionally dependent on a COMPOSITE KEY but not on any subset of the key.
  48. 48COMPOSITE KEYA multiple-attribute key
  49. 49Key AttributesThe attributes that form a PRIMARY KEY. See also prime attribute
  50. 50SuperkeyAn attribute or attributes that, uniquely identify each ENTITY in a TABLE.
    The longest of keys, includes every attribute.
  51. 51Candidate KeyA minimal superkey; that is, a key that does not contain a subset of ATTRIBUTES that is itself a SUPERKEY.
  52. 52Entity IntegrityThe property of a relational table that guarantees each ENTITY has a UNIQUE value in a PRIMARY KEY and that the key has no NULL values.
  53. 53NULLThe absence of an ATTRIBUTE value. Note that NULL is not blank.
  54. 54Foreign Key (FK)An ATTRIBUTE or ATTRIBUTES in one TABLE whose values must match the PRIMARY KEY in another TABLE or whose values must be NULL.
  55. 55Referential IntegrityA condition by which a DEPENDENT TABLE'S FOREIGN KEY must have either A NULL entry or a matching entry in the related table
  56. 56Secondary KeyA key used strictly for DATA RETRIEVAL purposes. For example, customers are not likely to know their customer number (PRIMARY KEY), but the combination of last name, first name, middle initial and telephone number will probably match the appropriate TABLE ROW.
  57. 57FlagsSpecial codes implemented by designers to trigger a required response, alert end-users to specified conditions, or encode values. FLAGS may be used to prevent NULLS by bringing attention to the absence of a value in a TABLE
  58. 58Relational AlgebraA set of mathematical principles that form the basis for manipulating RELATIONAL TABLE contents; the eight main functions are SELECT, PROJECT, JOIN, INTERSECT, UNION, DIFFERENCE, PRODUCT and DIVIDE
  59. 59RelvarShort for RELATION VARIABLE, a variable that holds a relation. A RELVAR is a container (variable) for holding relation data, not the relation itself.
  60. 60ClosureA property of relational operations that permits the use of RELATIONAL ALGEBRA OPERATORS on existing TABLES (relations) to produce new relations
  61. 61SELECT (aka RESTRICT)In RELATIONAL ALGEBRA, an operator used to SELECT a subset of ROWS. aka RESTRICT
  62. 62PROJECTIn RELATIONAL ALGEBRA, an OPERATOR used to SELECT a subset of COLUMNS.
  63. 63UNIONIn RELATIONAL ALGEBRA, an OPERATOR used to MERGE (append) two TABLES into a new TABLE, DROPPING the duplicate rows. The Tables must be union-compatible
  64. 64Union-compatibleTwo or more TABLES that have the same number of columns and the corresponding columns have compatible domains
  65. 65INTERSECTIn RELATIONAL ALGEBRA, an OPERATOR used to yield only the ROWS that are commonto two union-compatible TABLES.
  66. 66DIFFERENCEIn RELATIONAL ALGEBRA, an OPERATOR used to yield all rows from one table that are not found in another union-compatible table.
  67. 67PRODUCTIn RELATIONAL ALGEBRA, an OPERATOR used to yield all possible pairs of rows from two tables. Aka, the CARTESIAN PRODUCT
  68. 68JOINIn RELATIONAL ALGEBRA, a type of OPERATOR used to yield rows from two table based on criteria. There are many types of joins, such as natural join, theta join, equijoin and outer join.
  69. 69Natural JoinA RELATIONAL OPERATION that yields a NEW TABLE composed of only the rows with common values in their common attribute/s.
  70. 70Join ColumnsColumns that are used in the criteria of join operations. The join columns generally share similar values.
  71. 71EquijoinA join operator that links tables based on an equality condition that compares specified columns of the tables.
  72. 72Theta JoinA Join Operator that links tables using an inequality comparison operator (<, >, <=, >=) in the join condition.
  73. 73Inner JoinA join Operation in which only rows that meet a given criterion are selected. The JOIN CRITERION can be an equality condition (natural join or equijoin) or an inequality join (theta join). The inner join is the most commonly used type of join. Contrast with Outer Join
  74. 74Outer JoinA Relational Algebra Join Operation that produces a Table in which all unmatched pairs are retained; unmatched values in the related table are left NULL.
    Contrast with inner join. See also left out join and right outer join
  75. 75Left Outer JoinIn a pair of tables to be joined, a join that yields all the rows in the left table, including those that have no matching values in the other table. For example, a left outer join of Customer with Agent will yield all the customer rows, including the ones that do not have a matching Agent row.
  76. 76Right Outer JoinIn a pair of tables to be joined, a join that yields all the rows in the right table, including those that have no matching values in the other table. For example, a right outer join of Customer with Agent will yield all the Agent rows, including the ones that do not have a matching customer row.
  77. 77DIVIDEIn Relational Algebra, an operator that answers queries about one set of data being associated with all values of data in another data set.
  78. 78Data DictionaryA DBMS component that stores METADATA. Thus, the data dictionary contains the data definition as well as their characteristics and relationships. It may also include data that are external to the DBMS. (aka information resource dictionary)
  79. 79HomonymThe use of the same name to label different attributes. Homonyms should be avoided. Some relational software automatically checks for homonyms and either alerts the user to their existence or automatically makes the appropriate adjustments
  80. 80SynonymThe use of different names to identify the same object, such as an entity, an attribute, or a relationship; synonyms should generally be avoided
  81. 81Composite EntityAn entity designed to transform an M:N relationship into two 1:M Relationships. The Composite Entity's Primary Key compromises at least the primary keys of the entities it connects. (aka bridge entity, associative entity)
  82. 82Linking TablesIn the relational model, a table that implements an M:M relationship.
  83. 83IndexAn ordered array of index key values and row ID values (pointers). Indexes are generally used to speed up and facilitate data retrieval (aka Index Key)
  84. 84Unique IndexAn Index in which the Index Key can have only one associated pointer value (row).
  85. 85Required AttributeMust have a value
  86. 86Optional AttributeMay be left empty
  87. 87DomainRange of possible values for an attribute
  88. 88Composite IdentifiersPrimary Key composed of more than one attribute
  89. 89Composite AttributesAn Attribute that can be divided up into smaller components
  90. 90Simple AttributesCannot be further subdivided
  91. 91Single and Multi value attributesWhether or not an attribute can have more than one value
  92. 92Derived AttributesCan be calculated from other attributes, need not be stored in the database
  93. 93ERDEntity Relationship Diagram
  94. 94Entity Relationship DiagramEntities represent by rectangles (Tables).
    Attributes represented by notes with the rectangles (columns).
  95. 95Multi-value attributesShould be avoided by;
    1) Creating new attributes for each of the multi-value attributes components.
    2) Creating a new entity composed of original multi-value attribute components
  96. 96RelationshipAssociation between entities (always moves in both directions)
  97. 97ConnectivityUsed to describe relationship classification (both directions)
    1:1, 1:M, M:N
  98. 98CardinalityThe specific number of entity occurrences associated with one occurrence of the related entity (ie 1:1, 1:4 etc) more specific than connectivity notation, provides bounds for connectivity.
    - On ER diagrams, the notation next to the relationship line containing numbers and letters eg (0,2) or (0,N) etc, where the syntax is (lower bound, upper bound) - representing a domain.
    N represents Many
    0 indicates that the lower bound is where a match cannot be found.
  99. 99Relationship Participation - OptionalOne Entity Occurrence does not require a matching entity occurrence in a particular relationship
  100. 100Relationship Participation MandatoryEach Entity occurrence requires a matching identity occurrence in a relationship
  101. 101Relationship Strength Existence DependenceEntities existence depends on the existence of one or more other entities
    (PK of related entity contains PK component of parent entity - Strong Identifying Relationship)
    Represented by Solid Line
  102. 102Relationship Strength Existence IndependenceEntity can exist apart from one or more entities - represented by dashed line
  103. 103Weak EntitiesExistence is dependent
    Has a PK that is partially or totally derived from the parent entity in the Relationship.
  104. 104Relationship DegreeUnary Relationship - association is maintained with a single entity.
    Binary Relationship - two entities are associated
    Ternary Relationship - three entities are associated.
  105. 105Recursive RelationshipExists on the same entity set
    (naturally found within unary relationships)
  106. 106Composite / Bridge Entities- Composed of the PKs of each of the entities to be connected
    - Usually created for connecting an M:N Relationship.
  107. 107Database Design Process- Create narrative of organizations operations
    - Identify business rules based on description operations
    - Identify main entities and relationships from business rules
    - Develop initial ERD
    - Identify attributes and Primary Keys to describe these entities
    - Revise and Review ERD
  108. 108Conflicts of Database DesignHigh processing speeds, quest for timely information, business needs vs design standards.
    Often forces designers to make desing compromises.
  109. 109Strong (identifying) RelationshipsWhere the PK of a related entity contains a PK component of the parent entity (represented by a solid line)
  110. 110Weak (non-identifying) RelationshipsWhere the PK of related entity does not contain a PK component of the parent entity (ie is based on the FK of one of the tables rather than PK components).
  111. 111NormalizationA mechanical process used to evaluate and correct the table structure, with an aim to eliminate data redundancies.
    3NF is the highest usually used. Above that causes maintenance and usability issues.
    Ensures that all tables;
    - represent a single subject
    - are not storing duplicate data unnecessarily.
    - have attributes that are uniquely identified by a primary key.
    - does not contain insertion, update and delete anomalies.
  112. 112First Normal Form (1NF)- A table must not contain repeating groups
    - a "key" should not identify more than one record
    - To achieve 1NF, remove the repeating groups, identify the PK and all dependencies.
  113. 113Functional DependenciesA --> B
    (A determines B, ie; B is functionally dependent on A) if for every valid instance of A, that value of A uniquely determines the value of B.
    (A is determinant, B is dependent).
  114. 114Partial DependencyA dependency based on part of a composite primary key value.
  115. 115Transitive DependencyA dependency of one non-prime attribute.
    (A non-prime attribute is an attribute that is not part of the primary key).
  116. 116Second Normal Form (2NF)Removal of partial dependencies
    (ie part of the PK should not identify a subset of attributes in the same relation
  117. 117Third Normal Form (3NF)- Removal of Transitive Dependencies (ie remove any attributes that are determined by another attribute and add these to their own table)
    - Most common and generally preferable in transactional databases.
  118. 118Higher Level Normal FormBoyce-Codd Normal Form (BCNF) - al determinants are candidate keys.
    - Only a relation that has one PK can be in 3NF and BCNF.
    -BCNF is viokated if it has multiple candidate keys.
  119. 119DenormalizationAs normalization creates more tables, means more joins and queries become inefficient.
    - Denormalization uses both indexing and the removal of joins to improve processing speed.
    - Inherently implies redundancy.
  120. 120Insert AnomalyOccurs when you add to a table, but some of the information you need to add isn't available at the time you want to insert it.
    This means you either have to enter dummy information or you need to wait until all of the information is available.
  121. 121Update AnomalyOccurs when you want to update a certain value, but you are forced to update the same information in lots of different places.
    Forces duplicate updates.
  122. 122Delete AnomalyOccurs when you delete a record and that cause you to delete more information than just the information you wanted to delete.
  123. 123CONSTRAINTSENTITY Integrity
    REFERENTIAL Integrity
    ENTITY - PK UNIQUE and NOT NULL
    REFERENTIAL - references between data are VALID and COMPLETE

Información

Descripción
SIT772 Fichas sobre SIT772.2 - DATA MODELLING, creado por Amber Saxon el 25/11/2019.
Creado por
Amber Saxon
Visitas
0
Valoración

Escríbenos

¿Sobre qué es tu mensaje?

¿Qué te parece el nuevo diseño?

¿Te gusta?