Database Architecture

Deployment Topologies - Local

Local / Desktop

• Resides on the user's system
• Single user environment
• Single-tier architecture

Usage Scenarios:

• Development / Test
• Database embedded in a local application

Deployment Topologies - Client / Server

• Client / Server
• Database resides on a database server
• Users access database from client systems
• Sometimes there is a middle-tier (Application server layer)

Deployment Topologies - Cloud

• Database resides in a cloud environment
• No need to download or install software
• Users can access the cloud easily
• Clients access the database through an application server layer or interface in the cloud

2-Tier Database Architecture

• Client application and database server run in separate tiers
• Application connects to the database through an API or framework
• Database interface communicates through a database client

3-Tier Database Architecture

• Application presentation layer and business logic layer reside in different tiers
• Users interact with a client presentation layer such as a mobile application
• Client application communicates with application server
• Application server communicates with database server

https://s3.us-west-2.amazonaws.com/secure.notion-static.com/6bb82e3c-55c0-4eed-8732-957265b6e368/123122331.png?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAT73L2G45O3KS52Y5%2F20210710%2Fus-west-2%2Fs3%2Faws4_request&X-Amz-Date=20210710T205041Z&X-Amz-Expires=86400&X-Amz-Signature=6cb0e31ff877426002b48aebaa5f6369424b82a35bba8fc0e6b955f26bd5fa97&X-Amz-SignedHeaders=host&response-content-disposition=filename%20%3D%22123122331.png%22

Summary

• Databases are deployed in different topologies
• A single-tier topology is one where the database is installed on a user’s local desktop. It is useful for small databases that only require single user access.
• In 2-tier database topologies the database resides on a remote server and users access it from client systems.
• In 3-tier database topologies the database resides on a remote server and users access it through an application server or a middle-tier.
• In Cloud deployments the database resides in the cloud, and users access it through an application server layer or another interface that also resides in the cloud.

Distributed Architecture and Clustered Databases

Distributed Architecture

• Mission Critical / Large scale workloads
• High Availability / High Scalability requirements
• Databases distributed on a cluster of servers
• Shared disk architecture
  • Share common storage
• Shared nothing architecture
  • Replication
  • Partitioning

Summary

• In shared disk database architectures multiple database servers process the workload in parallel, allowing the workload to be processed faster.
• In database replication changes taking place on a database server are replicated to one or more database replicas.
• In a single location database replication provides high availability. When a database replica is stored in a separate location, it provides a copy of the data for disaster recovery.
• In partitioning, very large tables are split across multiple logical partitions.
• In sharding, each partition has its own compute resources.

Database Usage Patterns

Database Access - Key user roles

Data Engineers & Database Administrators

Data Scientists & Business Analysts

Application Developers & Programmers

Summary

Three main classes of users are:

• Data Engineers
• Data Scientists and Business Analysts
• Application Developers Databases

Databases can be accessed through:

• Graphical and Web Interfaces which make it easy to interact with the database visually.
• Command line tools and scripts can be cumbersome to use but are powerful in the hands of an experienced Data Engineer and help with automating repetitive tasks.
• APIs and ORMs which help application developers create applications that access a database on behalf of a user or client application.

Major categories of database applications include:

• Database Management tools
• Data Science and BI tools
• Business and Industry Applications

Introduction to Relational Database Offerings

Relational database timeline

Summary & Highlights

At this point in the course, you know:

There are four types of database topology:

• Single tier. The database is installed on a user’s local desktop.
• 2-tier. The database resides on a remote server and users access it from client systems.
• 3-tier. The database resides on a remote server and users access it through an application server or a middle tier.
• Cloud deployments. The database resides in the cloud, and users access it through an application server layer or another interface that also resides in the cloud.

In shared disk distributed database architectures, multiple database servers process the workload in parallel, allowing the workload to be processed faster. There are three shared nothing distributed database architectures:

• Replication. Changes taking place on a database server are replicated to one or more database replicas. In a single location, database replication provides high availability. When database replica is stored in a separate location, it provides a copy of the data for disaster recovery.
• Partitioning. Very large tables are split across multiple logical partitions.
• Sharding. Each partition has its own compute resources.

There are different classes of database users, who use databases in different ways:

• Three main classes of users are Data Engineers, Data Scientists and Business Analysts, and Application Developers.
• Database users can access databases through Graphical and Web interfaces, command line tools and scripts, and APIs and ORMs.
• Major categories of database applications include Database Management tools, Data Science and BI tools, and purpose built or off the shelf business applications.
• Relational databases are available with commercial licenses or open source.
• MySQL is an object-relational database that supports many operating systems, a range of languages for client application development, relational and JSON data, multiple storage engines, and high availability and scalability options.
• PostgreSQL is an open source, object-relational database that supports a range of languages for client application development, relational, structured, and non-structured data, and replication and partitioning for high availability and scalability

Practice Quiz: Introducing Relational Database Products

TOTAL POINTS 5

Question 1

In which deployment topology is the database stored on a remote server and accessed by users on their client systems?

• Local / Desktop
• Application Server layer
• Cloud
• Client / Server

Correct. In a client / server topology, the database is on a remote server, and accessed from the client system.

Question 2

Shared disk architectures have multiple database servers processing the workload in parallel. What benefit does this provide when one of the servers fail?

• Faster processing
• Scalability
• High availability
• Simple administration

Correct. When one server fails, the clients are rerouted to other servers in the cluster.

Question 3

Which of the three main classes of database users typically requires only Read access?

• Application developers
• Data Scientists and Business Analysts
• Data engineers
• None

Correct. Data Scientists and Business Analysists do most of their work by consuming the data.

Question 4

Which of the open source relational databases offers a General Public License (GPL)?

• MySQL
• PostgreSQL
• High availability
• IBM Db2

Correct. MySQL provides a GPL for version 2.

Question 5

What stack can PostgreSQL be used with for web applications and websites?

• MEAN stack
• LAPP stack
• LAMP stack
• Stack register

Correct. You can use PostgreSQL as part of the LAPP (Linux, Apache, PostgreSQL, PHP) stack for web applications and websites.

Graded Quiz: Introducing Relational Database Products

LATEST SUBMISSION GRADE 100%

Question 1

What role does database replication play in a distributed database architecture?

• High availability
• Improved performance
• Disaster recovery
• All of the above

Correct

Question 2

Which of the three main classes of database users are more likely to use Object Relational Mapping (ORM) in their workloads?

• Application Developers
• Data Engineers
• Data Scientists
• Database Administrators

Correct

Question 3

MySQL supports multiple storage engines. Which of the following supported storage engines use table-level locking?

• InnoDB
• NDB
• MyISAM
• None of the above

Correct

Question 4

PostgreSQL is an object-relational database management system. What does the object part mean for PostgreSQL?

• Supports high availability and scalability
• Database management
• Uses Postgres source code
• Supports inheritance and overloading

Correct

Question 5

A Relation is a table made up of columns and rows. Columns are attributes or fields. What are rows?

• Data values
• Struct
• Tuples
• None of the above

Correct