Tag Archives: PostgreSQL

Database Management Systems, Software Engineering Curriculum

Topic 15 – Advanced Database Management Systems

Leave a comment

Why do I need to learn about advanced database management systems?

Learning about advanced database management systems is important because modern applications deal with huge, complex, and diverse data. Learning to build a database management system equips you with the skills to design scalable, distributed, and high-performance data systems, making you a better problem solver and innovator in data management.

What can I do after finishing learning about advanced database management systems?

You will understand how data is stored, indexed, and retrieved efficiently, providing deep insight into the foundations of all database systems. You will also learn how queries are processed, transactions are managed, and consistency is maintained, which is essential for reliable, multi-user applications.

With this knowledge, you will be able to design and optimize high-performance databases tailored to specific applications. You can also develop custom or specialized data systems, such as vector, time-series, graph, or embedded databases.

Additionally, you gain the skills to build distributed, scalable, and fault-tolerant systems or improve existing database platforms for reliability and efficiency.

You can also contribute to database research, develop new query languages, design novel storage engines, or create data-intensive products in AI, analytics, or fintech.

That sounds useful! What should I do now?

First, please read this book to learn about database system concepts: Abraham Silberschatz et al. (2019). Database System Concepts. McGraw-Hill Education.

Alternatively, if you want to follow the concepts with interactive explanations, you can audit this course: CMU 15-445 – Introduction to Database Systems (UC Berkeley).

After that, please read the books below to learn how to design distributed databases and understand how they work:

Terminology Review:

  • Database’s Files.
  • Storage Manager.
  • Database’s Pages vs. Hardware Pages vs. OS Pages.
  • Page Storage Architecture: Heap Files, Tree Files, Sequential / Sorted File Organization (ISAM), Hashing Files.
  • Page Directory.
  • Page Header.
  • Slotted Pages.
  • Tuple Layout.
  • Tuple Header.
  • Tuple Data.
  • Record Identifiers.
  • Log-Structured Storage: MemTable, SSTables, Compaction.
  • Index-Organized Storage.
  • Tuple Storage.
  • Word-Aligned Tuples.
  • Data Representation: Variable-Precision Numeric Type, Null Data Type, Large Values, Overflow Pages.
  • System Catalogs.
  • N-ary Storage Model (NSM).
  • Decomposition Storage Model (DSM).
  • Partition Attributes Across (PAX) Storage Model.
  • Columnar Compression: Run-Length Encoding (RLE), Bit-Packing Encoding, Bitmap Encoding, Delta Encoding, Dictionary Encoding.
  • Data-Intensive Applications.
  • Graph Databases.
  • Distributed Databases.
  • Distributed Relational Databases.

After finishing advanced database management systems, please click on Topic 16 – Advanced Software Design to continue.

 

Database Management Systems, Software Engineering Curriculum

Topic 7 – Introduction to Database Management Systems

Leave a comment

Why do I need to learn about database management systems?

Your software must help users to do their work. The most important part of the users’ work is the information. You need to learn about database management systems to store, manipulate, retrieve and secure this information.

The database is the heart of any software system, especially enterprise systems. Poor database design will eventually cause a system to fail. Designing enterprise databases requires both specific domain knowledge and a deep understanding of database design principles. This is another reason why learning about database management systems is essential.

What can I do after finishing learning database management systems?

You will be able to design and implement database part for software systems. These systems may include airlines and railways, banking, education, telecommunications, digital libraries and digital publishing, finance, sales, health care information systems, e-commerce system, content management system.

You will be able to decide when to use a relational database management system and when to use a NoSQL database management system, as well as how a database system works internally.

You will also know how to build a data warehouse. You will know how to design a relational database and write SQL code PROPERLY (i.e. for specific purposes) too.

You will also be able to design databases for scalable and highly available software systems.

It sounds interesting! What should I do now?

Learning about database management systems requires a lot of effort. First you need to learn how to use database management systems.

First, please read one of the following books to learn the core concepts of relational database management systems and gain hands-on experience working with them:

After that, please read this book to learn how to write SQL code efficiently: Stephane Faroult and Peter Robson (2006). The Art of SQL. O’Reilly Media.

After that, please read these books to learn about normal forms and the relational theory behind relational databases, which will help you create more efficient logical designs for your databases:

After that, please read these books to learn how to build a data warehouse:

After that, please read these books to learn the core concepts of NoSQL database management systems and gain hands-on experience working with them:

Terminology Review:

  • Databases.
  • Flat Files.
  • Database Management Systems.
  • Relational Databases.
  • (Oracle) Tablespaces, Datafiles, and Objects.
  • (Oracle) Databases, Instances, Schemas.
  • Tables, Rows, Columns, Primary Keys, Foreign Keys.
  • Normal Forms.
  • Relational Model: Relations, Tuples, Primary Keys, Foreign Keys, Constraints.
  • Data Manipulation Languages (DMLs): Relational
    Algebra, Relational Calculus.
  • Relational Algebra: σ Select, π Projection, ∪ Union, ∩ Intersection, – Difference, × Product, ⋈ Join.
  • Data Models: Entity-Relationship (ER) Model, Relational Model, Object-Oriented Model, NoSQL Model.
  • Schemas.
  • Entity-Relationship Model.
  • SQL, TSQL.
  • Stored Procedures.
  • Functions.
  • Queries.
  • Aggregates (AVG, MIN, MAX, SUM, COUNT), ANY_VALUE, GROUP BY, HAVING, LIKE (Strings).
  • Output Redirection.
  • ORDER BY, ROW_NUMBER, RANK.
  • Subqueries.
  • Views.
  • Common Table Expressions.
  • Indexes.
  • Recovery.
  • Replication.
  • Data Warehouse.
  • Dimensional Modeling.
  • NoSQL.
  • Key-Value Databases.
  • Document Databases.
  • Column-Family Databases.
  • Graph Databases.
  • Array (Vector, Matrix, Tensor) Databases.

After finishing database management systems, please click on Topic 8 – Introduction to Web Application Development to continue.