DBMS-数据库管理系统概念

DBMS完整概念，包含EER，relation model，physical design,DBMS是一个综合性的软件系统，它负责数据库的定义、建立、操作、管理和维护。通过DBMS，用户可以方便地存储、查询、更新和删除数据库中的数据，而无需关心数据的底层存储结构和访问方式。

DBMS

• E-R Model Constructs
  •  
  • Entities
    • Entity type
      • eg: person
      • Although we use the word “entity” when describing ER diagrams, what we are really talking about is “entity types”.
    • Entity instance
      • eg: iris
    • •  
      • SHOULD BE:
        • An object that will have many instances in the database
        • An object that will be composed of multiple attributes
        • An object that we are trying to model
      • SHOULD NOT BE:
        • A user of the database system
        • An output of the database system (e.g., a report)
    • • Also note the double lines that distinguish the weak entity and the identifying relationship.
      • Strong entity
        • exists independently of other types of entities
        • has its own unique identifier
      • Weak entity
        • dependent on a strong entity (identifying owner)
        • does not have a unique identifier
        • entity box and partial identifier have double lines
      • Identifying relationship
        • links strong entities to weak entities
    • Associative Entities
      • An entity–has attributes A relationship–links entities together When should a relationship with attributes instead be an associative entity? All relationships for the associative entity should be many The associative entity could have meaning independent of the other entities The associative entity preferably has a unique identifier, and should also have other attributes The associative entity may participate in other relationships other than the entities of the associated relationship Ternary relationships should be converted to associative entities
  • Relationships
    • Relationship Types vs. Relationship Instances
      • 
      • The relationship type is modeled as lines between entity types…the instance is between specific entity instances
    • Relationships can have attributes
      • These describe features pertaining to the association between the entities in the relationship
    • Two entities can have more than one type of relationship between them (multiple relationships)
    • Associative Entity–combination of relationship and entity
    • Degree of Relationships
      • 
      • Degree of a relationship is the number of entity types that participate in it
      • Unary Relationship
      • Binary Relationship
      • Ternary Relationship
        • 
    • Cardinality of Relationships
      • One-to-One
        • Each entity in the relationship will have exactly one related entity
      • One-to-Many
        • An entity on one side of the relationship can have many related entities, but an entity on the other side will have a maximum of one related entity
      • Many-to-Many
        • Entities on both sides of the relationship can have many related entities on the other side
    • Cardinality Constraints
      • Cardinality Constraints—the number of instances of one entity that can or must be associated with each instance of another entity
      • Maximum Cardinality
        • The maximum number
      • Minimum Cardinality
        • If one or more, then mandatory
        • If zero, then optional
  • Attributes
    • In naming attributes, we use an initial capital letter followed by lowercase letters. If an attribute name consists of more than one word, we use a space between the words and we start each word with a capital letter, for example, Employee Name or Student Home Address.
    • Classifications of attributes:
      • Required versus Optional Attributes
        • 
      • Simple versus Composite Attribute
        • 
        • different from the multivalued attribute
      • Single-Valued versus Multivalued Attribute
      • Stored versus Derived Attributes
      • • 
      • Identifier Attributes
        • Identifier (Key)–an attribute (or combination of attributes) that uniquely identifies individual instances of an entity type
        • Simple versus Composite Identifier
          • 
        • Candidate Identifier–an attribute that could be an identifier…satisfies the requirements for being an identifier
        • Naming Attributes
          • Name should be a singular noun or noun phrase
          • Name should be unique
          • Name should follow a standard format
            • e.g. [Entity type name { [ Qualifier ] } ] Class
          • Similar attributes of different entity types should use the same qualifiers and classes
  • Here we see the distinction between an enterprise data model and a project data model. The enterprise-level model is more of a bird’s eye view, and less detailed than a project-level model.
    • 
  • •  
• The Enhanced E-R Model
  • Subtype
    • A subgrouping of the entities in an entity type that has attributes distinct from those in other subgroupings
  • Supertype
    • A generic entity type that has a relationship with one or more subtypes
  • The difference between object-oriented programming and EER supertypes/subtypes is that in object oriented programming there is also the concept of behavior. Objects not only have attributes (which can be inherited), but they also have “methods” which implement active behaviors.
  • Attribute Inheritance
    • 
    • Subtype entities inherit values of all attributes of the supertype
    • An instance of a subtype is also an instance of the supertype
  • • 
  • Relationships and Subtypes
    • 
  • Generalization and Specialization
    • Generalization: The process of defining a more general entity type from a set of more specialized entity types. BOTTOM-UP
    • Specialization: The process of defining one or more subtypes of the supertype and forming supertype/subtype relationships. TOP-DOWN
  • Constraints in Supertype/ subtype relationships
    • Completeness Constraints
      • Whether an instance of a supertype must also be a member of at least one subtype
      • • Total Specialization Rule: Yes (double line)
          • 
        • Partial Specialization Rule: No (single line)
          • 
    • Disjointness Constraints
      • Whether an instance of a supertype may simultaneously be a member of two (or more) subtypes
      • • Disjoint Rule
          • 
          • An instance of the supertype can be only ONE of the subtypes
        • Overlap Rule
          • 
          • An instance of the supertype could be more than one of the subtypes
        • • 
    • Subtype Discriminator
      • An attribute of the supertype whose values determine the target subtype(s)
      • • Disjoint
          •  The d in the circle
          • a simple attribute with alternative values to indicate the possible subtypes
        • Overlapping
          •  The O in the circle
          • a composite attribute whose subparts pertain to different subtypes. Each subpart contains a Boolean value to indicate whether or not the instance belongs to the associated subtype
  • Packaged Data Models
    • Predefined data models
    • Could be universal or industry-specific
    • Universal data model = a generic or template data model that can be reused as a starting point for a data modeling project (also called a “pattern”)
    • Advantages
      • Use proven model components
      • Save time and cost
      • Less likelihood of data model errors
      • Easier to evolve and modify over time
      • Aid in requirements determination
      • Easier to read
      • Supertype/subtype hierarchies promote reuse
      • Many-to-many relationships enhance model flexibility
      • Universal models support inter-organizational systems
• relational model / logical design
  • Components
    • Data structure
      • Tables (relations), rows, columns
    • Data manipulation
      • Powerful SQL operations for retrieving and modifying data
    • Data integrity
      • Mechanisms for implementing business rules that maintain integrity of manipulated data
  • Relation
    • A relation is a named, two-dimensional table of data
      • All relations are tables, but not all tables are relations.
    • A table consists of rows (records) and columns (attribute or field)
    • Requirements for a table to qualify as a relation:
      • To qualify as a relation, each row would need to be unique, which implies that each row needs to have a primary key.
      • It must have a unique name
      • Every attribute value must be atomic (not multivalued, not composite)
      • Every row must be unique (can’t have two rows with exactly the same values for all their fields)
      • Attributes (columns) in tables must have unique names
      • The order of the columns must be irrelevant
      • The order of the rows must be irrelevant
      • NOTE: All relations are in 1st Normal form.
    • Relation vs Relationship
      • Relations (tables) correspond with entity types and with many-to-many relationship types
      • Rows correspond with entity instances and with many-to-many relationship instances
      • Columns correspond with attributes
      • The word relation (in relational database) is NOT the same as the word relationship (in E-R model)
        • People often confuse the term “relation” with the term “relationship”. A relation is a table that follows certain rules, and refers to actual relational database architecture. A relationship is a conceptual term that refers to how entities relate to each other. The ER concept of relationship will be implemented by primary and foreign keys connecting the database’s relations.
  • Mapping from Conceptual to Logical DB Design
    • 
    • Transforming EER Diagrams into Relations
      • the database development life cycle often involves first constructing a conceptual model (E-R or EER) and then using this to create the logical model (relational database design).
      • Mapping Regular Entities to Relations
        • Simple attributes: E-R attributes map directly onto the relation
          • 
        • Composite attributes: Use only their simple, component attributes
          •  A bit more complex. Although conceptually you can have a composite attribute, there is no such thing as a “composite column” or “composite field” in relational databases. So, the component attributes become individual columns. As you can see, there is no field called “Customer Address” in the relation, just its components.
        • Multivalued Attribute: Becomes a separate relation with a foreign key taken from the superior entity
          • 
      • Mapping Weak Entities
        •   If a foreign key is null, this implies an “optional one” in the 1:N relationship. But for weak entities this will never be the case. The weak entity cannot exist without its corresponding strong entity. Note the assumption above. We assume that each dependent of an employee must have a unique name (first, middle, and last). Otherwise the fields comprising the composite primary key would not be unique, violating the rules of a relation.
        • Becomes a separate relation with a foreign key taken from the superior entity
        • Primary key composed of:
          • Partial identifier of weak entity
          • Primary key of identifying relation (strong entity)
      • Mapping Binary Relationships
        • One-to-Many–Primary key on the one side becomes a foreign key on the many side
          •  Because of the mandatory one in the relationship, we could have modelled ORDER as a weak entity and Submits as an identifying relationship.
        • Many-to-Many–Create a new relation with the primary keys of the two entities as its primary key
          •  Note that any time you have a many-to-many relationship, whether or not it contains its own attributes, it must be implemented as a separate relation (table). Often this is called an intersection table or junction table.
        • One-to-One–Primary key on mandatory side becomes a foreign key on optional side
          • 1:1关系中外键设计的原则 在两个表 A 和 B 之间存在一对一关系时，有两种外键设计方式： A 表的外键指向 B 表的主键。 B 表的外键指向 A 表的主键。 两者都可以正确表示 1:1 关系，但是： 实际设计中只选一个，不可以同时存在两个外键，否则会增加数据维护的麻烦，还可能引入不一致性。
      • Mapping Associative Entities
        • Identifier Not Assigned
          • 
          • Default primary key for the association relation is composed of the primary keys of the two entities (as in M:N relationship)
        • Identifier Assigned
          • 
          • It is natural and familiar to end-users
          • Default identifier may not be unique
      • Mapping Unary Relationships
        • One-to-Many–Recursive foreign key in the same relation
          • 
        • Many-to-Many–Two relations:
          • 
          • One for the entity type
          • One for an associative relation in which the primary key has two attributes, both taken from the primary key of the entity
      • Mapping Ternary (and n-ary) Relationships
        • 
        • One relation for each entity and one for the associative entity
        • Associative entity has foreign keys to each entity in the relationship
      • Mapping Supertype/Subtype Relationships
        •  The primary key of the subtype relation (table) is also a foreign key to the supertype relation. Therefore, if you ever see a database table where the entire primary key is also a foreign key, this should imply to you that there is a supertype-subtype relationship involved
        • One relation for supertype and for each subtype
        • Supertype attributes (including identifier and subtype discriminator) go into supertype relation
        • Subtype attributes go into each subtype; primary key of supertype relation also becomes primary key of subtype relation
        • 1:1 relationship established between supertype and each subtype, with supertype as primary table
  • Key Fields
    • Keys are to databases as identifiers are to E-R models.
    • Primary keys
      • are unique identifiers of the relation. Examples include employee numbers, social security numbers, etc. This guarantees that all rows are unique
    • Foreign keys
      • are identifiers that enable a dependent relation (on the many side of a relationship) to refer to its parent relation (on the one side of the relationship)
    • Keys can be simple (a single field) or composite (more than one field)
    • Keys usually are used as indexes to speed up the response to user queries
  • graph
    •  The relation on the many side of a 1:N relationship will have a foreign key that corresponds with the primary key of the relation on the 1 side. Any time you have a M:N relationship between entities, this must be implemented via a separate relation, often called an “intersection table” or a “junction table”. The Order Line table is an example of a relation that implements a many-to-many relationship between orders and products. Each order may involve several products, and likewise each product could be involved in many orders.
  • Integrity Constraints
    • Domain Constraints
      •  A domain definition usually consists of the following components: domain name, meaning, data type, size (or length), and allowable values or allowable range (if applicable). There are many ways to enforce domain constraints. One way is in the SQL for creating tables, as we will see in later chapters. Another way is for applications to enforce this prior to inserting data into the database. For example, in Table 4-1, you can verify that an entered date is legitimate (e.g. not Feb. 29, 2015). Or, you can verify that the unit price is a positive number.
      • Allowable values for an attribute (See Table 4-1)
    • Entity Integrity
      • No primary key attribute may be null. All primary key fields MUST contain data values
    • Referential Integrity
      •  In the relation diagrams of this chapter, arrows between foreign and primary keys depict referential integrity constraints.
      • Rules that maintain consistency between the rows of two related tables
        • Referential Integrity–rule states that any foreign key value (on the relation of the many side) MUST match a primary key value in the relation of the one side. (Or the foreign key can be null)
      • For example: Delete Rules
        • Restrict–don't allow delete of 'parent' side if related rows exist in 'dependent' side
        • Cascade–automatically delete “dependent” side rows that correspond with the “parent” side row to be deleted
        • Set-to-Null–set the foreign key in the dependent side to null if deleting from the parent side → not allowed for weak entities
  • Well-Structured Relations
    • A relation that contains minimal data redundancy and allows users to insert, delete, and update rows without causing data inconsistencies
    • Goal is to avoid anomalies
      • Insertion Anomaly
        • adding new rows forces user to create duplicate data
      • Deletion Anomaly
        • deleting rows may cause a loss of data that would be needed for other future rows
      • Modification Anomaly
        • changing data in a row forces changes to other rows because of duplication
    • Functional Dependency
      • The value of one attribute (the determinant) determines the value of another attribute
    • Candidate Key
      • A unique identifier. One of the candidate keys will become the primary key
      • Each non-key field is functionally dependent on every candidate key.
    • normalization
      • 
      • First Normal Form
        •  
        • No multivalued attributes
        • Every attribute value is atomic
        • All relations are in 1st Normal Form.
      • Second Normal Form
        • 
        • 1NF PLUS every non-key attribute is fully functionally dependent on the ENTIRE primary key
          • Every non-key attribute must be defined by the entire key, not by only part of the key
          • No partial functional dependencies
        • Partial Dependencies
          •  
      • Third Normal Form
        • 2NF PLUS no transitive dependencies (functional dependencies on non-primary-key attributes)
          • Note: This is called transitive, because the primary key is a determinant for another attribute, which in turn is a determinant for a third
        • Solution: Non-key determinant with transitive dependencies go into a new table; non-key determinant becomes primary key in the new table and stays as foreign key in the old table
        • Example of Transitive Dependencies
          • 
• Physical Database Design
  • Purpose
    • translate the logical description of data into the technical specifications for storing and retrieving data
  • Goal
    • create a design for storing data that will provide adequate performance and ensure database integrity, security, and recoverability
  • Physical Design for Regulatory Compliance
    • These listed standards, guidelines, and rules focus on corporate governance, risk assessment, and security and controls of data. For full compliance, all data integrity controls must be thoroughly documented and undergo well-documented change control procedures.
    • Sarbanes- Oxley Act (SOX) – protect investors by improving accuracy and reliability
      •   
    • Committee of Sponsoring Organizations (COSO) of the Treadway Commission
    • IT Infrastructure Library (ITIL)
    • Control Objectives for Information and Related Technology (COBIT)
  • Physical Design Process
    •  Much of the input to the physical design comes from outputs from requirements analysis and logical design. Others stem from business security concerns and regulatory compliance, while still others stem from predictions of demand and performance analysis. The primary goal of physical database design is data processing efficiency.
    • Usage analysis
      • 
  • Designing Fields
    • Field
      • In relational database terms, the word field is synonymous with column. Fields correspond roughly with attributes in ER and EER models. During logical design, you converted the entities and attributes of the ER model to relations (tables) and columns (fields). Now, during physical design you give details of the fields. In actuality, some of these things could be part of logical design. In some respects, logical design and physical design blend together. In other respects physical design blends with implementation. There are not always hard-and-fast boundaries between these phases of the SDLC.
      • smallest unit of application data recognized by system software
    • Field design
      • Choosing data type
        • 
      • Coding, compression, encryption
        •  Some attributes have a sparse set of values or are so large that, given data volumes, considerable storage space will be consumed. A field with a limited number of possible values can be translated into a code that requires less space. A code table would not appear in the conceptual or logical model. The code table is a physical construct to achieve data processing performance improvements, not a set of data with business value. So, in this case you probably would not see the PRODUCT FINISH table in either the conceptual model or the logical design.
      • Controlling data integrity
        • Default value
        • Range control
        • Null value control
        • Referential integrity
          • range control (and null value allowances) for foreign-key to primary-key match-ups
    • Handling Missing Data
      • Substitute an estimate of the missing value (e.g., using a formula)
      • Construct a report listing missing values
      • In programs, ignore missing data unless the value is significant (sensitivity testing)
  • Denormalization
    • Transforming normalized relations into non-normalized physical record specifications When you normalize, you split data into separate tables in order to eliminate or at least reduce data duplication. This helps to ensure data integrity. But, it comes at a cost. It is much more computationally efficient to query a single table than to query multiple tables. Note that denormalization is most beneficial and least harmful in relations where there is a minimal amount of updates that would occur to the duplicated attributes.
    • Benefits:
      • Can improve performance (speed) by reducing number of table lookups (i.e. reduce number of necessary join queries)
    • Costs (due to data duplication)
      • Wasted storage space
      • Data integrity/consistency threats
    • Common denormalization opportunities
      • One-to-one relationship
        • 
      • Many-to-many relationship with non-key attributes (associative entity)
        •  This means that any time the description changes, the change must be done to all item quotes unless you’re willing to tolerate inconsistencies in the item descriptions. So, in this case, there is some compromising of data integrity guarantees. Again, we do this for the sake of performance. A query involving two tables is more efficient than a query involving three.
      • Reference data (1:N relationship where 1-side has data not used in any other relationship)
        •  Here again we see denormalization, this time combing two relations with 1:N relationship into a single one. Again, we improve query performance at the cost of data duplication.
    • Denormalize with caution
      • Increase chance of errors and inconsistencies
      • Reintroduce anomalies
      • Force reprogramming when business rules change
    • Partitioning
      • Another form of denormalization involves the creation of more tables by partitioning a relation into multiple physical tables.
      • Horizontal Partitioning
        • Distributing the rows of a logical relation into several separate tables
        • Useful for situations where different users need access to different rows
        • Three types: Key Range Partitioning, Hash Partitioning, or Composite Partitioning
          • Oracle’s Horizontal Partitioning
            • Range partitioning
              • Partitions defined by range of field values
              • Could result in unbalanced distribution of rows
              • Like-valued fields share partitions
            • Hash partitioning
              • Partitions defined via hash functions
              • Will guarantee balanced distribution of rows
              • Partition could contain widely varying valued fields
            • List partitioning
              • Based on predefined lists of values for the partitioning key
            • Composite partitioning
              • Combination of the other approaches
      • Vertical Partitioning
        • Distributing the columns of a logical relation into several separate physical tables
        • Useful for situations where different users need access to different columns
        • The primary key must be repeated in each file
      • Combinations of Horizontal and Vertical
      • • Advantages
          • Efficiency: Records used together are grouped together
          • Local optimization: Each partition can be optimized for performance
          • Security: data not relevant to users are segregated
          • Recovery and uptime: smaller files take less time to back up
          • Load balancing: Partitions stored on different disks, reduces contention
        • Disadvantages
          • Inconsistent access speed: Slow retrievals across partitions
          • Complexity: Non-transparent partitioning
          • Extra space or update time: Duplicate data; access from multiple partitions
  • Designing Physical Database Files
    • Physical File
      • 定义：一个命名的二级存储（比如硬盘）上的区域，用来存储数据记录。 理解： 数据库中的数据需要保存在磁盘上，物理文件就是实际存放这些数据的文件。 例子：在数据库系统中，这个文件可能叫 datafile.dbf
      • A named portion of secondary memory allocated for the purpose of storing physical records
      • Tablespace–named logical storage unit in which data from multiple tables/views/objects can be stored
      • File Organizations
        • Technique for physically arranging records of a file on secondary storage
        • Factors for selecting file organization:
          • Fast data retrieval and throughput
          • Efficient storage space utilization
          • Protection from failure and data loss
          • Minimizing need for reorganization
          • Accommodating growth
          • Security from unauthorized use
        • Types of file organizations
          • 
          • Heap – no particular order
          • Sequential
            • 
          • Indexed
            • 
          • Hashed
            • 
    • Tablespace components
      • 定义：逻辑存储单元，用于存储来自多个表/视图/对象的数据。 理解： 数据库会把数据分配到多个物理文件上，为了管理方便，这些文件被组合到一个逻辑单位里，就是“表空间”。 你可以理解为逻辑容器，而物理文件是它的组成部分。 例子： 表空间 USER_DATA 里面可能存放了用户的数据表和索引。 表空间（Tablespace） 包含多个 段（Segment）。 每个段由多个 区（Extent） 组成。 每个区由多个 数据块（Data Block） 组成。 最后，所有数据都保存在**物理文件（Physical File）**中。 
      • Segment – a table, index, or partition
      • Extent–contiguous section of disk space
      • Data block – smallest unit of storage
    • Clustering Files
      •  数据不适合聚集的原因： 频繁变动：插入、删除或更新数据会导致数据物理位置变化，增加维护成本和碎片，影响性能。 动态数据：数据变化大，查询模式不固定，聚集难以有效优化查询。 空间浪费：频繁的更新可能导致空闲空间和碎片，浪费存储。 高开销：每次数据变动需要重新排序或安排，增加操作复杂度。
  • Unique and Nonunique Indexes
    • Unique (primary) Index
      • Typically done for primary keys, but could also apply to other unique fields 
    • Nonunique (secondary) index
      • Done for fields that are often used to group individual entities (e.g. zip code, product category) 
    • Rules for Using Indexes
      • 1. Use on larger tables
      • 2. Index the primary key of each table
      • 3. Index search fields (fields frequently in WHERE clause)
      • 4. Fields in SQL ORDER BY and GROUP BY commands
      • 5. When there are >100 values but not when there are <30 values
      • 6. Avoid use of indexes for fields with long values; perhaps compress values first
      • 7. If key to index is used to determine location of record, use surrogate (like sequence number) to allow even spread in storage area
      • 8. DBMS may have limit on number of indexes per table and number of bytes per indexed field(s)
      • 9. Be careful of indexing attributes with null values; many DBMSs will not recognize null values in an index search
  • Hard Disk Structure
    • 
    • 磁道 (Track)
    • 扇区 (Sector)
    • 块 (Block)
• SQL
  • Structured Query Language – often pronounced “Sequel”
  • Purpose of SQL Standard
    • Specify syntax/semantics for data definition and manipulation
    • Define data structures and basic operations
    • Enable portability of database definition and application modules
    • Specify minimal (level 1) and complete (level 2) standards
    • Allow for later growth/enhancement to standard (referential integrity, transaction management, user-defined functions, extended join operations, national character sets)
  • Benefits of a Standardized Relational Language
    • These are all benefits of standardization. Keep in mind, though, that different database vendors may use different constructs in their SQL versions, so the standards are not perfectly applied. Standards are good, but they also have disadvantages. A standard can stifle creativity and innovation. One standard is never enough to meet all needs, and an industry standard can be far from ideal because it may be the offspring of compromises among many parties. Standard may be difficult to change (because so many vendors have a vested interest in it), so fixing deficiencies may take considerable effort. Since vendors typically extend standards with proprietary features, application portability isn’t complete; you may need to change SQL statements when migrating from one vendor to another (for example, switching from Oracle to Microsoft SSL Server or vice versa).
    • Reduced training costs
    • Productivity
    • Application portability
    • Application longevity
    • Reduced dependence on a single vendor
    • Cross-system communication
  • SQL Environment
    • 
    • Catalog
      • A set of schemas that constitute the description of a database
    • Schema
      • The structure that contains descriptions of objects created by a user (base tables, views, constraints)
    • Data Definition Language (DDL)
      • DDL is Data Definition Language. This is used to actually create the metadata of the database, including all tables, attributes and their data types, indexes, primary and foreign keys, etc. The DDL implements the logical design into actual physical databases. This is the language that database designers will use to create the database.
      • Commands that define a database, including creating, altering, and dropping tables and establishing constraints
    • Data Manipulation Language (DML)
      • DML is Data Manipulation Language. This includes all query, update, insert, and delete statements. This is the language that allows users and applications interact with and manipulate the data in the database.
      • Commands that maintain and query a database
    • Data Control Language (DCL)
      • DCL is Data Control Language. This is used by database administrators to specify who can access the data and the types of data and operations these users are authorized to do.
      • Commands that control a database, including administering privileges and committing data
    • • 
  • SQL
    • Major CREATE statements:
      • CREATE SCHEMA–defines a portion of the database owned by a particular user
      • CREATE TABLE–defines a new table and its columns
      • CREATE VIEW–defines a logical table from one or more tables or views
      • Other CREATE statements:
        • CHARACTER SET
        • COLLATION
        • TRANSLATION
        • ASSERTION
        • DOMAIN
      • SQL Data Types
        • 
      • Steps in Table Creation
        • Identify data types for attributes
        • Identify columns that can and cannot be null
        • Identify columns that must be unique (candidate keys)
        • Identify primary key–foreign key mates
        • Determine default values
        • Identify constraints on columns (domain specifications)
        • Create the table and associated indexes
      • command
        •  Primary keys can never have NULL values  Identity columns are columns whose value automatically increment with each new INSERT. So, an INSERT statement does not explicitly give a value for an identity column; this is handled automatically. Often primary keys are identity columns, but not always.
      • Data Integrity Controls
        • Referential integrity–constraint that ensures that foreign key values of a table must match primary key values of a related table in 1:M relationships
        • Restricting:
          • 
          • Deletes of primary records
          • Updates of primary records
          • Inserts of dependent records
    • Changing Tables
      • 
      • The ALTER command will be done after tables have already been created. For example, if you have an existing database, even one with actual data in it, you can modify tables by adding or changing columns, removing columns adding constraints, etc. If data in the tables violate the constraints, you will be prevented from setting these constraints until after changing the data. So, whereas CREATE TABLE is mostly a process that takes place during implementation, ALTER TABLE often takes place during maintenance.
    • Removing Tables
      • DROP TABLE CUSTOMER_T
    • Insert Statement
      •  
    • Delete Statement
      • DELETE FROM CUSTOMER_T WHERE CUSTOMERSTATE = 'HI';
        • Delete certain rows
      • DELETE FROM CUSTOMER_T;
        • Delete all rows
    • Update Statement
      • UPDATE Product_T SET ProductStandardPrice = 775 WHERE ProductID = 7;
    • Merge Statement
      • 
    • SELECT Statement
      • Used for queries on single or multiple tables
      • Clauses of the SELECT statement:
        •  
        • SELECT
          • List the columns (and expressions) to be returned from the query
            • SELECT Example Using a Function
              • SELECT COUNT(*) FROM ORDERLINE_T WHERE ORDERID = 1004;
        • FROM
          • 
          • Indicate the table(s) or view(s) from which data will be obtained
        • WHERE
          • Indicate the conditions under which a row will be included in the result
        • GROUP BY
          • 
          • Indicate categorization of results
        • HAVING
          • 
          • Indicate the conditions under which a category (group) will be included
        • ORDER BY
          • 
          • Sorts the result according to specified criteria
      • Aggregation Functions in SQL
        • AVG – calculates the average of a set of values
        • COUNT – counts rows in a specified table or view
        • MIN – gets the minimum value in a set of values
        • MAX – gets the maximum value in a set of values
        • SUM – calculates the sum of values
    • Boolean Operators
      •  
      • Operator Precedence
        • 
    • Using and Defining Views
      • Views provide users controlled access to tables
      • Base Table–table containing the raw data
      • Dynamic View
        • A “virtual table” created dynamically upon request by a user
        • No data actually stored; instead data from base table made available to user
        • Based on SQL SELECT statement on base tables or other views
      • Materialized View
        • Copy or replication of data
        • Data actually stored
        • Must be refreshed periodically to match corresponding base tables
      • Sample CREATE VIEW
        • 
      • Advantages of Views
        • Simplify query commands
        • Assist with data security (but don't rely on views for security, there are more important security measures)
        • Enhance programming productivity
        • Contain most current base table data
        • Use little storage space
        • Provide customized view for user
        • Establish physical data independence
      • Disadvantages of Views
        • Use processing time each time view is referenced
        • May or may not be directly updateable