Please enable JavaScript.

Coggle requires JavaScript to display documents.

HOW TO DESIGN A DATABASE (How To Design A Data Structure (The 6 basic…

- - - - Normalization is a formal process you can use to separate the data in a data structure into related tables. Normalization reduces data redundancy, which can cause storage and maintenance problems.
      - In a normalized data structure, each table contains information about single entity, and each piece of information is stored in exactly on place.
      - In an unnormalized data structure, a table can contain information about two or more entities. It can also contain repeating columns, columns that contain repeating values, and data that's repeated in two or more rows.
      - To normalize a data structure, you apply the normal forms in sequence. Although there are a total of seven normal forms, a data structure is typically considered normalized if the first three normal forms are applied.
    - - data redundancy: redundancy that can cause storage and maintenance problems.
      - normalized data structure: where each table contains info. about a single entity, and each piece of info. is stored in exactly on place.
      - unnormalized data structure: a table that contains information about two or more entities, repeating columns, columns that contain repeating values, and data that's repeated in two or more rows.
      - normal forms: are used to eliminate problems caused by data redundancy. There are 7 types.
      - normalization: a formal process used to separate the data in a data structure into related tables.
  - - - An invoice document can be used to help you identify the data elements in a real-world system.
      - As you identify the data elements of a system, you should begin thinking about the entities that those elements are associated with. That will help you identify the tables of the DB later on.
      - You can identify data elements in a variety of ways, including interviewing users, analyzing existing systems, and evaluating comparable systems.
  - - - This Operation...
        
        Insert a row in the foreign key table
        
        The foreign key value doesn't have a matching primary key value in the related table
        
        Update the value of a primary key
        
        The foreign key table contains one or more rows related to the row that's changed
        
        Delete a row form the primary key table.
        
        The foreign key table contains one or more rows related to the deleted row
        
        Update the value of a foreign key
        
        The new foreign key value doesn't have a matching primary key value in the related table
    - - referential integrity: the relationship between tables are maintained correctly.
      - orphaned: rows in the foreign key table that no longer have a related row in the primary key table.
      - foreign key constraint: indicates how the referential integrity between the tables is enforced
      - declarative referential integrity (DRI): is used to enforce relationships between tables and maintain their referential integrity.
    - - To use declarative referential integrity (DRI), you define foreign key constraints.
      - 1. Referential integrity means that the relationships between tables are maintained correctly. --That means that a table with a foreign key doesn't have rows with foreign key values that don't have matching primary key values in the related table
      - When you define foreign key constraints, you can specify how referential integrity is enforced when a row is deleted from the primary key table. The options are a). to return an error, b). to delete the related rows in the foreign key table, or c), to set the foreign key values in the related rows to null.
      - In Oracle, you can enforce referential integrity by using declarative referential integrity or by defining triggers.
      - If referential integrity isn't enforced and a row is deleted from the primary key table that has related rows in the foreign key table, the rows in the foreign key table are said to be orphaned.
  - - - If a suitable column doesn't exist for a primary key, you can create an ID column that's incremented by one for each new row as the primary key.
      - If two tables have one-to-one relationship, they should be related by their primary keys. This type of relationship is typically used to improve performance. Then, columns with large amounts of data can be stored in a separate table.
      - If two tables have a many-to-many relationship, you'll need to define a linking table to relate them. Then, each of the tables in the many-to-many relationship will have a one-to-many relationship with the linking table. The linking table doesn't usually have a primary key.
      - The values of the primary key should seldom, if ever, change. The values also be short and easy to enter correctly.
      - If two tables have a one-to-many relationship, you may need to add a foreign key column to the table on the "many" side. The foreign key column must have the same data types as the primary key column it's related to.
      - Each table should have a primary key that uniquely identifies each row. If possible, you should set an existing column for the primary key.
    - - linking table: a table that links two many-to-many tables to relate them.
  - - - When you subdivide a data element, you can easily rebuild it when necessary by concatenating the individual components.
      - The extent to which you subdivide a data element depends on how it will be used. Because it's difficult to predict all future uses for the data, most designers subdivide data elements as much as possible.
      - If a data element contains two or more components, you should consider subdividing the element each time you use it.
  - - - composite index: an index that consists of two or more columns.
      - table scan: when a DBMS searches through the entire table, because no index exists.
      - index: a structure that provides for locating one or more rows dierctly.
    - - An index provides a way for a database management system to locate information more quickly. When it uses an index, the database management system can go directly to a specific row rather than having to search through all the rows until it find it.
      - Indexes speed performance when searching and joining tables.
      - Because indexes must be updated each time you add, update, or delete a row, you shouldn't create more indexes that you need.
      - Oracle automatically creates an index for a primary key.
      - You can create composite indexes that include two or more columns. You should use this type of index when the columns in the index are updated infrequently or when the index will cover almost every search condition on the table.
    - - When the column is a foreign key
        
        When the column is used frequently in search conditions or joins
        
        When the column contains a large number of distinct values
        
        When the column is updated infrequently
  - - - As you assign the elements to entities, you should omit elements that aren't needed, and you should add any additional elements that ARE needed.
      - After you subdivide all of the data elements for a database, you should group them by the entities with which they're associated. These entities will later become the tables of the DB, and the elements will become the columns.
      - If a data element relates to more than one entity, you can include it under all of the entities it relates to. Then, when you normalize the database, you may be able to remove the duplicate elements.
- - - - c. Each table has fewer indexes, which makes insert, update, and delete operations more efficient.
      - b. Since each table contains information about a single entity, each index has fewer columns (usually one) and fewer rows. And that makes data retrieval and insert, update, and delete operations more efficient.
      - d. Data redundancy is minimized, which simplifies maintenance and reduces storage.
      - a. Since a normalized database has more tables than an unnormalized database, and since each table has an index on its primary key, the database has more indexes. And that make data retrieval more efficient.
    - - A database is typically considered to be normalized if it's in third normal form (3NF). The other four forms are not commonly used.
      - Each normal form assumes that the design is already in the previous normal form.
  - - - c. Include columns with derived values when those values are used frequently in search conditions. If you do that, you need to be sure that the column value is always synchronized with the value of the columns it's derived from.
      - b. If a table is updated infrequently, you should consider denormalizing it to improve efficiency. Because that data remains relatively constant, you don't have to worry about data redundancy errors once the initial data is entered and verified.
      - a. When a column from a joined table is used repeatedly in search criteria, you should consider moving that column to the primary key table if it will eliminate the need for a join.
    - - Most designers denormalize data structures to some extent, usually to the third normal form (3NF).
      - Data structures that are normalized to the fourth normal form and beyond typically require more joins than tables normalized to the third normal form and can therefore be less efficient.
      - SQL statements that work with tables that are normalized to the fourth normal form (4NF) and beyond are typically more difficult to code and debug.
      - Only denormalization when necessary. It is better to adhere to the normal forms unless it is clear that performance will be improved by denormalizing.
      - Denormalization can result in larger tables, redundant data, and reduced performance.
  - - - A table in first normal form often has repeating values in its rows. This can be resolved by applying the second normal form.
      - For a table to be in first normal form (1NF), its columns must not contain repeating values. Instead, each column must contain a single, scalar value. In addition, the table must no contain repeating columns that represent a set of values.
  - - - For a table to be in third normal form, every non-key column must depend only on the primary key.
      - If a column doesn't depend only on the primary key, it implies that the column is assigned to the wrong table or that it can be computed from other columns in the table. A column that can be computed from other columns contains derived data.
  - - - Second normal form helps remove redundant row data, which can save storage space, make maintenance easier, and reduce the chance of storing inconsistent data.
      - To apply second normal form, you move columns that don't depend on the entire primary key to another table and then establish a relationship between the two tables.
      - For a table to be in second normal form, every non-key column must depend on the entire primary key. If a column doesn't depend on the entire key, it indicates that the table contains information for more than one entity. This is reflected by the table's composite key.