SpaceYaTech hosted the session, with Faith Kilonzi as the speaker and Juma Lawrence as the host.
About the Speaker
Faith Kilonzi is a software engineer, DevOps consultant, senior technical writer, and big data practitioner. She is a computer scientist and is currently pursuing big data technologies. She has over 4 years of work experience in the consulting, ed-tech, fintech, banking, and DevSecOps spaces. She also co-founded and hosted the Global Tech Sailors podcast.
How the Web Works
When a user enters a URL into their browser's address bar, the browser sends a request to the server machine hosting the website. The server machine processes the request and generates a response, which is sent back to the browser as an HTTP response. The browser then renders the response body as a webpage for the user to view, based on the HTML file and any additional resources included in the response.
Fullstack Web Architecture
"3-tier architecture is a software architecture pattern that divides an application into three logical layers, each with its own set of responsibilities. These layers are typically referred to as the presentation layer, the business logic layer, and the data access layer. It allows a clear separation of concerns, which makes the application easier to develop, test, and maintain. It allows scalability and flexibility as each layer can be developed and scaled independently.
By dividing the application into these three layers, developers can achieve better separation of concerns, making the application more modular, maintainable, and scalable.
The presentation layer is responsible for handling user input and displaying output to the user. It typically includes web pages or UI components that users interact with directly.
The business logic layer is responsible for processing and managing business rules and logic. It is where the core functionality of the application resides, and it interacts with the presentation layer and data access layer to retrieve and manipulate data as needed.
The data access layer is responsible for managing data storage and retrieval. It interacts with the business logic layer to provide access to data stored in databases, file systems, or other data sources.
Database Management Systems(DBMS)
The database management system (DBMS) is a collection of interrelated data and a collection of programs to access that data.
Structured Query Language (SQL) is a standard language used to interact with relational databases, which are managed by relational DBMS. It provides a standardized way to create, read, update, and delete data from a database. It can be used to perform a wide variety of tasks, from simple queries to complex data transformations.
Types of SQL Databases
It can be categorized into two types:
Relational Database: They are based on the relational data model, which organizes data into tables or relations. They use SQL as the query language for manipulating and querying data. e.g MySQL, Microsoft SQL Server, Oracle Database, IBM DB2, and PostgreSQL
Non-relational databases, also known as NoSQL databases, use different data models and query languages than relational databases. They are often used to manage unstructured or semi-structured data and provide flexible data models and scalable data storage. e.g MongoDB, Apache Cassandra, Redis, Couchbase, and Apache HBase
Database
A database is a collection of logically related information organized so that it can easily be accessed, managed, and updated. It is generally accessed electronically from a computer system. It is controlled by a database management system (DBMS)
Functions of Database
The database provides a reliable and efficient way to store, organize, and manage data and makes that data accessible to users when and where they need it.
Data storage: to store and organize data. Databases are designed to efficiently store large amounts of data and retrieve it quickly when needed.
Data Retrieval: It allows users to retrieve data based on various criteria, e.g., search terms or specific data fields. It enables users to find the information they need quickly and easily.
Database manipulation: It allows users to add, modify, and delete data as needed. It provides tools for managing and updating data e.g data validation and data integrity checks
Data Security: It provides a range of security features to ensure that data is kept safe and secure. It includes features such as access controls, encryption, and backups.
Data Sharing: It allows multiple users to access and work with the same data simultaneously. It enables collaboration and ensures that everyone has access to up-to-date information
Data Analysis: It can be used to analyze data and generate insights into trends, patterns, and relationships. It is often done using tools e.g SQL queries or data visualization
Importance of Database Engineering in Backend Development
Designing, implementing, and managing databases that store and organize large volumes of data
Building robust and reliable backend systems that can handle complex data operations and support the needs of modern applications
Data modeling, normalization, query optimization, backup and recovery, and ensuring data security.
Principles of Database Engineering
Database engineering involves applying principles and best practices to design, develop, and maintain efficient and reliable databases. The goal is to ensure data integrity, optimize performance, and enhance the overall functionality of your database systems.
Data modeling: Database engineering involves creating a data model that defines the structure of the data to be stored in the database. This involves identifying entities, attributes, and relationships between the data elements.
Normalization: Normalization is the process of organizing data in a database to eliminate redundancy and improve data integrity. This involves breaking down complex data structures into smaller, more manageable components.
Data integrity: Database engineering involves ensuring the accuracy and consistency of the data in the database. This includes implementing constraints, such as unique keys and referential integrity, to prevent data inconsistencies.
Performance optimization: Database engineering involves designing the database and database schema to optimize performance. This includes indexing data, partitioning data across multiple servers, and using caching strategies.
Security: Database engineering involves implementing security measures to protect sensitive data. This includes implementing access controls, encryption, and audit trails.
Data backup and recovery: Database engineering involves implementing backup and recovery strategies to ensure that data can be recovered in the event of a disaster or system failure.
Demo Session: E-Commerce Use-Case
Looking through the Jumia.co.ke website to break down SQL and how the database tables could be structured and their relationships Using TVs pages TCL as an example
tables
specific items and what to be captured: store, name, image, details, price, brand
queries well engineered
Using VS Code
install SQLTOOLs extension on VS Code
create Connections: Name
choose a database either: MariaDB or MySQL
fill in the following fields connection name, port, database, username, password mode, an authentication protocol, and then connect
Open the terminal
$ mysql -u root -p // start the MySQL client // Enter your password when prompted $ CREATE DATABASE // to create a new database $ USE // to switch to the new database $ CREATE TABLE // to create one or more tables in the new database $ SHOW DATABASES // see a list of databases $ SHOW TABLES // see a list of tablesCREATE TABLE my_table ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), email VARCHAR(255) );High-Level E-commerce Application Database
User management: The database should store user account information, such as usernames, passwords, and contact details. It should also track user activity, such as login attempts and account changes.
Product Catalog: The database should store information about the products that are available for purchase, including product names, descriptions, prices, and images. It should also track product availability and inventory levels.
Shopping cart and orders: The database should store information about users' shopping carts, including the items that have been added and their quantities. It should also track order information, including order numbers, delivery addresses, and payment details.
Reviews and ratings: The database should store user reviews and ratings for products, along with information about the reviewers, such as their usernames and the dates of their reviews.
Payment and transactions: The database should store information about transactions, including payment amounts, payment methods, and transaction dates. It should also track the status of transactions, such as whether they have been authorized or declined.
Seller management: The database should store information about sellers, including their usernames, contact details, and sales histories. It should also track seller ratings and feedback.
Search and recommendations: The database should support searching and filtering of products based on various criteria, such as price, category, and brand. It should also provide personalized product recommendations to users based on their browsing and purchasing history.
Security and logging: The database should implement security measures, such as access controls, encryption, and data backups. It should also log user activity and system events for auditing and troubleshooting purposes.
Database Modeling and Design for E-Commerce Applications
Data modeling and design is the process of creating a conceptual, logical, and physical representation of data that is to be stored in a database. The role of data modeling and design is to create a set of tables and establish relationships between them so that the data can be efficiently and accurately stored, accessed, and manipulated.
Conceptual modeling is the initial phase, where the main entities and their relationships are identified. This phase establishes the scope and requirements of the database and helps define its primary purpose.
Logical modeling is the next phase, where the conceptual model is converted into a more structured and formal representation. Here, data elements are defined, attributes are identified, and relationships between entities are established. Logical models are typically presented in the form of entity-relationship diagrams (ERDs).
Physical modeling is the final phase, where the logical model is translated into an actual database schema that can be implemented in a database management system. This involves specifying data types, constraints, indexes, and other details that are required to create and manage the database.
The role of data modeling and design is critical in ensuring that the database is well-designed, efficient, and capable of supporting the intended business processes. It helps to ensure data integrity, consistency, and accuracy and enables the development of robust and scalable database applications.
Database Relationships
In database design, there are three common types of relationships between tables: one-to-one, one-to-many, and many-to-many:
One-to-one: A one-to-one relationship in an e-commerce site could exist between a customer's account information and their payment information. Each customer can have at most one payment method associated with their account, and each payment method can be associated with at most one customer account.
One-to-many: A one-to-many relationship in an e-commerce site could exist between a product and its reviews. Each product can have many reviews associated with it, but each review can only be associated with one product.
Many-to-many: A many-to-many relationship in an e-commerce site could exist between products and categories. Each product can be associated with many categories (e.g., clothing, electronics, home goods), and each category can be associated with many products. This allows for flexible and dynamic categorization of products on the site.
SQL Queries
SQL queries are commands used to retrieve, insert, update, or delete data from a database:
SELECT statements are used to retrieve data from one or more tables.
INSERT statements are used to add new data to a table.
UPDATE statements are used to modify existing data in a table.
DELETE statements are used to remove data from a table.
SQL Data Types
Understanding the different data types in SQL is important for creating well-designed database tables and writing effective SQL queries that retrieve and manipulate data correctly.
VARCHAR: A variable-length string of alphanumeric characters.VARCHAR is a data type that represents variable-length character strings. The VARCHAR data type is used to store alphanumeric data that varies in length, up to a maximum length specified when the column is created.
For example, a column defined as
VARCHAR(50)can hold a string of up to 50 characters in length. If a shorter string is stored in that column, the remaining space is left unused.The
VARCHARdata type is commonly used to store text data, such as names, addresses, and descriptions, in database tables. Other common data types in SQL includeINTEGERfor whole numbers,DECIMALfor decimal numbers,DATEfor dates, andBOOLEANfor boolean values (true/false).INTEGER: A whole number without a decimal point.DECIMAL: A number with a specified number of digits before and after the decimal point.DATE: A date value (year, month, day).TIME: A time value (hour, minute, second).BOOLEAN: A value that can be true or false.BLOB: A binary large object used to store large amounts of binary data, such as images or documents.ENUM: A set of predefined values that a column can take on (e.g. color choices: red, green, blue).SET: Similar toENUM, but allows for multiple values to be selected (e.g. a list of hobbies).User Table
CREATE TABLE User ( id INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(50), password VARCHAR(50), created_at DATETIME, updated_at DATETIME );Product Table
CREATE TABLE Product ( id INT PRIMARY KEY, name VARCHAR(100), description TEXT, price DECIMAL(10,2), image_url VARCHAR(200), category VARCHAR(50), stock INT, created_at DATETIME, updated_at DATETIME );Thank you for reading through the article. This has been a summary of the topics covered in the Mastering Database Engineering session, and you can access the video of the session on Youtube and the Speaker notes here.
SpaceYaTech continues to host weekly workshops like this. You can register for the upcoming event on our Eventbrite page to get an update when it happens and also join the mailing list for the weekly events. If you’re interested in speaking about a given topic, you can fill out this form to be considered as one of our speakers in upcoming sessions.