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 database design?

Database is the heart of any software systems, especially enterprise systems. A bad database design will eventually cause your system fail. Designing enterprise databases requires a lot of specific enterprise knowledge and in-depth understanding of the design of database system. That’s why you need to learn about advanced database design.

What can I do after finishing learning about database design?

You will know WHEN you should use a relational database management system or WHEN you should use a NoSQL database management system and HOW a database system works INTERNALLY.

You will be able to design databases for scalable and high availability software systems also.

That sounds useful! What should I do now?

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

After that please read
– this Martin Kleppmann (2017). Designing Data-Intensive Applications: The Big Ideas Behind Reliable, Scalable, and Maintainable Systems. O’Reilly Media book, and
– this Alex Petrov (2019). Database Internals. O’Reilly Media book to learn how to design distributed databases and how distributed databases 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 the books please click 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.

What can I do after finishing learning database management systems?

You can 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 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.

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.

Please read
– this Ignatius Fernandez (2015). Beginning Oracle Database 12c Administration: From Novice to Professional. Apress book, or
– this Adam Jorgensen et al. (2012). Microsoft SQL Server 2012 Bible. Wiley book, or
– this Vinicius M. Grippa and Sergey Kuzmichev (2021). Learning MySQL. O’Reilly Media book, or
– this Regina O. Obe and Leo S. Hsu (2017). PostgreSQL Up and Running. O’Reilly Media book.

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

After that please read
– this C. J. Date (2019). Database Design and Relational Theory: Normal Forms And All That Jazz. Apress book and
– this C. J. Date (2015). SQL and Relational Theory: How to Write Accurate SQL Code. O’Reilly Media book to learn about normal forms and relational theory behind relational databases for creating more efficient logical designs for your databases.

After that please read
– this W.H. Inmon (2003). Building The Data Warehouse. Wiley book and
– this Ralph Kimball and Margy Ross (2002). The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling. Wiley book and
– this Vincent Rainardi (2008). Building a Data Warehouse: With Examples in SQL Server. Apress book to learn how to build a data warehouse.

After that please read
– this Pramod J. Sadalage and Martin Fowler (2012). NoSQL Distilled: A Brief Guide to the Emerging World of Polyglot Persistence. Addison-Wesley Professional book, and
– this Jeff Carpenter and Eben Hewitt (2022). Cassandra: The Definitive Guide: Distributed Data at Web Scale. O’Reilly Media book, and
– this Bradshaw Shannon, Eoin Brazil and Kristina Chodorow (2019). MongoDB: The Definitive Guide: Powerful and Scalable Data Storage. 3rd Edition. O’Reilly Media book.

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 learning about database management systems please click Topic 8 – Introduction to Web Application Development to continue.