Database Concepts and Life Cycle

• A database is a collection of data stored in a standardized format designed to be shared by multiple users.
• A key purpose of a database is to support decision-making processes.
• Databases facilitate reporting on a company's operations for legal compliance, descriptive reporting, and predictive analysis.
• DBMS (Database Management System) is a software package used to create, organize, and manage databases, often including querying, security, and user management tools.
• The database life cycle progresses through various phases, emphasizing the importance of understanding each stage.

Database Life Cycle Phases

• Requirements Collection & Analysis involves gathering and analyzing what data is needed; can be challenging if users struggle to articulate their data needs for decision support.
• Conceptual Modeling employs notations like Chen’s notation or Crow’s feet notation.
• Logical Modeling uses the relational data model.
• Physical Modeling defines how the data will be physically stored.
• Application Design involves designing the applications that will interact with the database.

Conceptual Modeling

• Modeling plays a key role in database design.
• The objectives of data modeling are to abstract the real world, and capture necessary data for a database.
• Databases should be used by multiple users to prevent duplicate efforts and data redundancy.
• A conceptual model can be interpreted using Entity-Relationship diagrams with Chen and Crow's Feet notations.
• There are specific rules for creating a conceptual model which must be understood.

Database Concepts

• Database design focuses on how the database structure will store and manage end-user data.
• Database modeling involves creating a specific data model.
• Problem domain is a well-defined area in the real-world environment with clear scope and boundaries for database creation.
• Data serves as source of competitive advantage.
• Data consistency, integrity, and updates are important as the data supports decision making

Conceptual Modeling Concepts

• Conceptual modeling extracts abstract concepts from the real world to identify data for storage in a database, resulting in a representation of the real-world application.
• Modeling Concepts include things like Keys (primary, candidate, surrogate, component), attributes, relationships (Unary, binary, ternary, n-ary), relationship attributes, and mapping ratios.
• Mix/max cardinalities provide finer detail than mapping ratios.
• It's important to know how cardinalities appear in both Crow’s feet and Chen notation and to understand the differences between optional versus mandatory participation in relationships.
• Min/max cardinalities are essential for drawing a conceptual model.

Conceptual Model Elements

• Entities represent concepts in the real world.
• Attributes are the characteristics/properties of an entity.
• Relationships show associations between entity types.
• Entity-relationship diagrams must be drawn with correct and consistent notation.
• Crow's Feet diagrams must also be drawn with correct notation.
• Min/max cardinalities require careful placement in both Chen and Crow’s Feet notations.
• The degree of a relationship is the number of entities involved in the relationship.
• Many-to-many relationships can have relationship attributes, with representational differences between Chen’s notation and Crow’s Feet.

Terminology and Concepts

• Important terms include: entity, relationship, attribute, abstraction, instance, instantiation, occurrence, association, mapping ratios, identifiers, key (primary, composite, candidate, surrogate), min/max cardinalities, optional and mandatory participation.

Role of Business Rules

• Business rules help standardize a company’s view of data.
• They facilitate communication between users and designers.
• Business rules enable designers to understand the nature, role, and scope of data and business processes.
• Business rules allow designers to capture appropriate relationships in design.

Data and Wisdom

• It's important to know the progression from data to wisdom and the implications for designing and using information systems and databases.

Data in Decision Making

• Data's role in decision making is important, but human judgment is still required.
• Understanding queries is essential.

Relational Model

• A basic understanding of relational terminology is required.
• Concepts related to normalization are not needed.
• There is a shift from a conceptual model using Chen or Crow's Feet notation to creating a logical i.e. relational model
• Specific rules exist for translating a conceptual model into a relational model, dependent on the min/max cardinalities of the relationships, to create a "good" relational model.
• Key characteristics include: domain constraints, entity constraints, and data constraints.

ER-Relational Model Translation

• Every entity becomes a separate relation.
• Relationships have two options in ER-Relational Model Translation.
• Foreign key is used for 1:N relationships.
• ER uses a separate relation with the key of the relation being a concatenation of the two keys of the corresponding entities and relationship attributes becoming non-key attributes.

Data Warehouses and Data Mining

• Query processing, OLTP, and OLAP are different.
• Data is represented as an OLAP cube to handle multi-dimensional aspects.
• Data warehouses receive data from multiple sources.
• Data mining finds application through: customer segmentation, marketing and promotion targeting, market basket analysis, collaborative filtering, customer churn, fraud detection, financial modeling, and also hiring and promotion.

SQL

• A query language is used to build and retrieve useful information from the database
• SQL (Structured Query Language) is a nonprocedural language so it specifies what to retrieve, not how.
• SQL is used for data administration, data manipulation, and to query a database, as well as for report generation.
• There are two forms, DDL (data definition language) and DML (data manipulation language).

SQL: DDL and DML

• The Create Statement is used to create tables.
• Referential integrity must be ensured in the data when populating a database.

SQL Queries

• There are example queries from class and text for single-table queries.
• An understanding of SQL keywords and built-in functions is required.