4.1 Database.pdf

12/31/23, 4:54 PM zyBooks equivalent and is equivalent to SELECT GroupColumn, Function(Column) FROM Table GR Relational algebra expressions are equivalent if the expressions operate on the same tables and generate the same result. query optimizer / query execution plan A query optimizer convert...

12/31/23, 4:54 PM zyBooks equivalent and is equivalent to SELECT GroupColumn, Function(Column) FROM Table GR Relational algebra expressions are equivalent if the expressions operate on the same tables and generate the same result. query optimizer / query execution plan A query optimizer converts an SQL query into a sequence of lowlevel database actions, called the query execution plan. The query execution plan specifies precisely how to process an SQL statement. cost The cost of an operation is a numeric estimate of processing time. The cost estimate usually combines both storage media access and computation time in a single measure. Progression 3.9.1 Relational algebra. 4. Database Design 4.1 Entities, relationships, and attributes entity-relationship model An entity-relationship model is a high-level representation of data requirements, ignoring implementation details. entity An entity is a person, place, product, concept, or activity. relationship A relationship is a statement about two entities. attribute An attribute is a descript­ive property of an entity. reflexive relationship A reflexive relationship relates an entity to itself. entity-relationship diagram / ER diagram An entity-relationship diagram, commonly called an ER diagram, is a schematic picture of entities, relationships, and attributes. glossary / data dictionary / repository A glossary, also known as a data dictionary or repository, documents additional detail in text format. entity type An entity type is a set of things. Ex: All employees in a company. relationship type A relationship type is a set of related things. Ex: EmployeeManages-Department is a set of (employee, department) pairs, https://learn.zybooks.com/zybook/WGUD426v2/content-explorer/print 19/39 12/31/23, 4:54 PM zyBooks attribute type where the employee manages the department. An attribute type is a set of values. Ex: All employee salaries. entity instance An entity instance is an individual thing. Ex: The employee Sam Snead. relationship instance A relationship instance is a statement about entity instances. Ex: "Maria Rodriguez manages Sales." . attribute instance An attribute instance is an individual value. Ex: The salary $35,000. Analysis Analysis develops an entity-relationship model, capturing data requirements while ignoring implementation details. Logical design Logical design converts the entity-relationship model into tables, columns, and keys for a particular database system. Physical design Physical design adds indexes and specifies how tables are organized on storage media. 4.2 Discovery Progression 4.2.1 Discovery. 4.3 Cardinality cardinality In entity-relationship modeling, cardinality refers to maxima and minima of relationships and attributes. Relationship maximum Relationship maximum is the greatest number of instances of one entity that can relate to a single instance of another entity. singular / plural A related entity is singular when the maximum is one and plural when the maximum is many. Relationship minimum Relationship minimum is the least number of instances of one entity that can relate to a single instance of another entity. optional / required A related entity is optional when the minimum is zero and required when the minimum is one. Attribute maximum Attribute maximum is the greatest number of attribute values that can describe each entity instance. Attribute maximum is usually specified as one (singular) or many (plural). https://learn.zybooks.com/zybook/WGUD426v2/content-explorer/print 20/39 12/31/23, 4:54 PM zyBooks Attribute minimum Attribute minimum is the least number of attribute values that can describe each entity instance. Attribute minimum is usually specified as zero (optional) or one (required). unique attribute Each value of a unique attribute describes at most one entity instance. Progression 4.3.1 Cardinality. 4.4 Strong and weak entities identifying attribute An identifying attribute is unique, singular, and required. identify Identifying attribute values correspond one-to-one to, or identify, entity instances. strong entity A strong entity has one or more identifying attributes. weak entity / identifying relationship / identifying entity A weak entity does not have an identifying attribute. Instead, a weak entity usually has a relationship, called an identifying relationship, to another entity, called an identifying entity. Cardinality of the identifying entity is 1(1). Progression 4.4.1 Strong and weak entities. 4.5 Supertype and subtype entities subtype entity / supertype entity A subtype entity is a subset of another entity type, called the supertype entity. IsA relationship A supertype entity identifies its subtype entities. The identifying relationship is called an IsA relationship. Similar entities Similar entities are entities that have many common attributes and relationships. partition A partition of a supertype entity is a group of mutually exclusive subtype entities. partition attribute Each partition corresponds to an optional partition attribute of the supertype entity. Progression 4.5.1 Supertype and subtype entities. https://learn.zybooks.com/zybook/WGUD426v2/content-explorer/print 21/39 12/31/23, 4:54 PM zyBooks 4.6 Alternative modeling conventions crow's foot notation Variations in cardinality conventions are common. One popular convention, called crow's foot notation, depicts cardinality as a circle (zero), a short line (one), or three short lines (many). The three short lines look like a bird's foot, hence the name "crow's foot notation". subject area Decompose a complex model into a group of related entities, called a subject area. independent / dependent Refer to strong entities as independent and weak entities as dependent. Unified Modeling Language / UML Unified Modeling Language, or UML, is commonly used for software development. Software data structures are similar to database structures, so UML includes ER conventions. IDEF1X IDEF1X stands for Information DEFinition version 1X. IDEF1X became popular, in part, due to early adoption by the United States Department of Defense. Chen notation Chen notation appeared in an early ER modeling paper by Peter Chen. Chen notation is not standardized but often appears in literature and tools. 4.7 Implementing entities strong table A strong entity becomes a strong table. The primary key must be unique and non-NULL, and should be stable, simple, and meaningless. Single-column primary keys are best, but if no such column exists, a composite primary key may have the required properties. artificial key An artificial key is a single-column primary key created by the database designer when no suitable single-column or composite primary key exists. subtype table A subtype entity becomes a subtype table . weak table A weak entity becomes a weak table. Progression 4.7.1 Implementing entities. https://learn.zybooks.com/zybook/WGUD426v2/content-explorer/print 22/39 12/31/23, 4:54 PM zyBooks 4.8 Implementing relationships Progression 4.8.1 Implementing relationships. 4.9 Implementing attributes Progression 4.9.1 Implementing attributes. 4.10 First, second, and third normal form depends on Column A depends on column B means each B value is related to at most one A value. functional dependence Dependence of one column on another is called functional dependence. Multivalued dependence / join dependence Multivalued dependence and join dependence entail dependencies between three or more columns. Redundancy Redundancy is the repetition of related values in a table. Normal forms Normal forms are rules for designing tables with less redundancy. first normal form Every cell of a table contains exactly one value. A table is in first normal form when, in addition, the table has a primary key. second normal form A table is in second normal form when all non-key columns depend on the whole primary key. third normal form Informally, a table is in third normal form when all non-key columns depend on the key, the whole key, and nothing but the key. A formal definition appears elsewhere in this material. 4.11 Boyce-Codd normal form candidate key / Minimal A candidate key is a simple or composite column that is unique and minimal. Minimal means all columns are necessary for uniqueness. non-key A non-key column is a column that is not contained in a candidate key. third normal form A table is in third normal form if, whenever a non-key column A depends on column B, then B is unique. Columns A and B may be https://learn.zybooks.com/zybook/WGUD426v2/content-explorer/print 23/39 12/31/23, 4:54 PM zyBooks Boyce-Codd normal form Progression simple or composite. A table is in Boyce-Codd normal form if, whenever column A depends on column B, then B is unique. Columns A and B may be simple or composite. 4.11.1 Normal form. 4.12 Applying normal form Normalization Normalization eliminates redundancy by decomposing a table into two or more tables in higher normal form. depends on Column A depends on column B when each B value is related to at most one A value. A and B may be simple or composite columns. Boyce-Codd normal form In a Boyce-Codd normal form table, if column A depends on column B, then B must be unique. Denormalization Denormalization means intentionally introducing redundancy by merging tables. 5. Data Storage 5.1 Storage media Access time Access time is the time required to access the first byte in a read or write operation. Transfer rate Transfer rate is the speed at which data is read or written, following initial access. Volatile memory Volatile memory is memory that is lost when disconnected from power. Non-volatile memory Non-volatile memory is retained without power. Main memory / random-access memory (RAM) Main memory, also called random-access memory (RAM), is the primary memory used when computer programs execute. Flash memory / solidstate drive (SSD) Flash memory, also called solid-state drive (SSD), is less expensive and higher capacity than main memory. https://learn.zybooks.com/zybook/WGUD426v2/content-explorer/print 24/39