Notes on Information Modelling and Relational Databases

Introduction to Information Modelling

This week's focus is on information modelling, which relates to the previously discussed topics surrounding data management within structured formats, particularly in tabular forms such as spreadsheets. The objective is to understand how to effectively manage large datasets using databases, and we will begin exploring designing these structures.

Transition to Database Management

The transition from using spreadsheets to databases is significant because it allows for better organization and management of extensive datasets. Over the next week or two, the course will delve into the basics of database design, particularly focusing on relational data modeling, which is a widely recognized framework for organizing data.

Lab Sessions and Assignments

Due to time constraints within class sessions (approximately two hours per week), the lab is not due this week, although students can start working on it. It is essential that all students attend the lab next week, which will be held in the Tech Hub lab rather than the usual OGGB lab. The session is geared to provide hands-on experience with cutting-edge technologies, including AI, IoT, and VR, and will aid students in their upcoming assignments.

Assignment Breakdown

The first assignment will consist of three parts:

  1. Spreadsheet Assessment: Students will work with a medium-sized spreadsheet to perform exercises that include creating pivot tables.

  2. Information Modelling: This part involves interpreting a case study and determining how to best represent that information structurally.

  3. Technology Application: Students will choose a technology (including mixed reality) and explore its application within a chosen industry, focusing on how it can enhance competitiveness based on previously discussed strategies.

Understanding Data Models

Moving into the technical aspects, the core concept introduced is the relational data model. This model fundamentally organizes data into tables, termed as “relations,” where each table includes rows (records) and columns (attributes). Each row represents a singular instance of the data the table is designed to describe, while columns hold the necessary attributes that define the data.

Example with Books and Authors

For instance, in managing data about books, a 'Book' table would contain an ISBN, title, genre, and the publisher's ID, while a separate 'Author' table would hold details regarding authors. Data regarding publishers would also be stored separately in its own unique table, reflecting the necessity to distinguish between different entities (books, authors, publishers) without redundancy.

Relationships Between Tables

Understanding the relationships between these tables is pivotal. Using the relational data model, connections between entities (like books written by authors and published by publishers) can be characterized. The concept of modelling relationships allows the structure to not only define data types but also encompass how these distinct entities interact.

Reducing Data Redundancy

One of the relational model’s advantages is its ability to reduce data redundancy—an issue often inherent in simpler structures like spreadsheets. This refers to eliminating repeated data entries for the same objects, which can be accomplished by structuring data within distinct tables and utilizing primary and foreign keys to maintain connections.

Primary and Foreign Keys

Every relational database consists of a primary key, which serves as a unique identifier for records within a table, ensuring no duplicates exist. Conversely, foreign keys allow information to be accurately linked across distinct tables, providing a means to connect related data residing in separate places within the database. This connection is essential in maintaining the integrity and accessibility of the database.

Practical Application: ERDs

Finally, the session highlights the importance of visual representations of these structures through Entity Relationship Diagrams (ERDs). ERDs serve the function of illustrating both the tables and the relationships between them. They simplify the understanding of how data is organized and accessed and are integral to the design process in constructing a functional database framework.

Conclusion

Through this course, students will gain a comprehensive understanding of how to design and manipulate databases effectively, preparing them not only for academic assessments but also for real-world applications of database management and information modelling. Over the next sessions, practical tools such as the SQLite DB Browser will be introduced to solidify these concepts with hands-on experience.