Relational Database Course Requirements

This course can be taken on Windows, Mac, or Linux platforms.

This course does not require any specific software. In order to read the case study on which the course project is based,
you will now want to obtain a word-processing application such as Microsoft Word.

1. The case study on which the course project is based. This outlines the characteristics and data-retrieval needs of Stories on CD, Inc., a fictional mail-order company selling books on CD.
2. A copy of the database design diagram that represents the culmination of the course project.
3. A table comparing database terminology and the different phases of the design and creation process in which equivalent terms are used.

Click the link to your left to download the case study.

The following book can be purchased from the book store.
Relational Database Design
The next lesson discusses the learning resources available through this website. The following tasks are required for database analysis. A data modeler can design an efficient database by creating relational constructs, normalizing tables, creating joins and views and by optimizing the database's physical design.

In addition to designing efficient databases, a data modeler should be aware of several other key areas in the field of data analysis. These areas are crucial for ensuring that data modeling aligns with broader business and analytical objectives. Some of these areas include:

1. Data Warehousing and ETL (Extract, Transform, Load) Processes
   • Data Warehousing: Knowledge of how data warehouses are structured, including star and snowflake schemas, and their use in analytical reporting.
   • ETL Processes: Understanding how data is extracted from multiple sources, transformed into a suitable format, and loaded into databases or data warehouses for analysis. Efficient ETL design can improve data quality and timeliness.
2. Big Data and NoSQL Databases
   • Big Data: Awareness of how to model and manage unstructured or semi-structured data in environments like Hadoop, Apache Spark, and similar platforms.
   • NoSQL Databases: Understanding when to use NoSQL databases like MongoDB, Cassandra, and others for scalability and handling large datasets that don't fit the relational model.
3. Data Governance and Compliance
   • Data Governance: A data modeler should understand policies around data management, including data stewardship, data ownership, and access control to ensure proper data quality, security, and privacy.
   • Compliance: Familiarity with regulations such as GDPR, CCPA, and HIPAA, especially how data modeling decisions affect compliance with data protection laws.
4. Metadata Management
   • Metadata: Understanding the role of metadata in documenting the meaning, relationships, and lineage of data. Metadata helps track where data originates, how it has been transformed, and how it should be interpreted.
5. Business Intelligence (BI) and Reporting Tools
   • BI Tools: A data modeler should know how databases and models interact with BI tools like Tableau, Power BI, or Looker to ensure that the data structure supports efficient querying and reporting.
   • Self-Service Analytics: Data modeling should consider how non-technical users will access and manipulate data through BI platforms.
6. Dimensional Modeling
   • OLAP (Online Analytical Processing): Data modelers need to understand how to structure databases for OLAP systems, which support complex analytical queries and reporting.
   • Dimensional Modeling: Skills in creating fact and dimension tables to optimize databases for analytics and reporting purposes.
7. Performance Tuning and Indexing
   • Query Optimization: Beyond physical design, understanding how query optimization works, including indexing strategies, partitioning, and caching, ensures efficient data retrieval.
   • Database Performance Tuning: This involves optimizing the database environment, such as through the use of indexes, partitioning, and table optimization to improve read/write speeds and ensure optimal database performance.
8. Master Data Management (MDM)
   • MDM Concepts: Knowledge of how to manage master data, which serves as the single source of truth for an organization, ensuring consistency and integrity of critical business data across different systems.
9. Data Visualization Techniques
   • Data Visualization: Awareness of how data should be structured and modeled to make visualization straightforward for end-users. Ensuring the model supports drill-downs, aggregations, and real-time analytics is important for efficient reporting and analysis.
10. Machine Learning and Predictive Analytics
    • Data Modeling for Machine Learning: While not necessarily building machine learning models, a data modeler should understand how to structure data for training, testing, and validating predictive models.
    • Predictive Analytics Tools: Understanding how to integrate the database design with tools that support statistical analysis and machine learning workflows.
11. Cloud Data Architecture
    • Cloud Databases: Familiarity with cloud-based databases such as AWS RDS, Google Cloud BigQuery, or Azure SQL, and how cloud infrastructure impacts data modeling, storage, and access patterns.
    • Serverless and Distributed Data Systems: Awareness of cloud-native solutions, including serverless databases and distributed systems, which offer flexibility and scalability.
12. Data Integration
    • Cross-Platform Integration: Understanding how to model data for integration across different platforms and applications, enabling data flow between systems such as CRMs, ERPs, and third-party applications.
    • APIs for Data Access: Knowledge of how data models can be accessed via APIs for real-time data integration between systems.
13. Data Quality and Cleansing
    • Data Quality Frameworks: A solid understanding of techniques for maintaining high data quality, including validation, deduplication, and handling missing or erroneous data.
    • Data Cleansing: Skills in preparing and cleansing data to ensure it is reliable and accurate for analysis.

By developing skills in these areas, a data modeler can contribute to a holistic approach to data management, ensuring that databases not only support operational needs but also empower effective data analysis, reporting, and decision-making across the organization.

Logical database schema design is concerned with organizing data into a logical form acceptable to the underlying database system. One of the logical structures is relational structure which we use to develop logical schema (Conceptual schema). It is a complicated process. Some of the important points which make it complicated are:

1. The designer is constrained by the limited data structure types supported by the data base system.
2. The database designer may have to consider the access path of the records.
3. The database designer may have to consider how to make database access and modification efficient.
4. The designer has to identify and select a set of most relevant attributes for an entity.
5. The designer has to identify the size of a relation and connect two or more relations for navigation.

A good relational database is a set with a proper relational schema. A proper relational schema contains a set of relevant attributes of the entity it represents where every attribute is clearly related (directly or indirectly) to other attributes of the relation. A good relation should require minimum storage space and have minimum data redundancy.