Database Concepts: Architecture, SQL, Normalization, Keys

Posted on Apr 21, 2025 in Computers

Database Architectures

Database architectures define how a database system is structured and how its components interact. Common architectures include 1-Tier, 2-Tier, and 3-Tier.

1-Tier Architecture

• In this architecture, the database is directly available to the user. This means the user can directly sit on the DBMS and use it.
• Any changes done here will directly be done on the database itself. It doesn’t provide a handy tool for end users.

The 1-Tier architecture is used for the development of local applications, where programmers can directly communicate with the database for a quick response.

2-Tier Architecture

The 2-Tier architecture is the same as a basic client-server model. In the two-tier architecture, applications on the client end can directly communicate with the database at the server side. For this interaction, APIs like ODBC and JDBC are used.

The user interfaces and application programs run on the client-side.

The server side is responsible for providing functionalities like query processing and transaction management.

To communicate with the DBMS, the client-side application establishes a connection with the server side.

3-Tier Architecture

The 3-Tier architecture contains another layer between the client and server. In this architecture, the client cannot directly communicate with the server.

The application on the client-end interacts with an application server which further communicates with the database system.

The end user has no idea about the existence of the database beyond the application server. The database also has no idea about any other user beyond the application.

The 3-Tier architecture is used in the case of large web applications.

ER Model

• ER model stands for an Entity-Relationship model. It is a high-level data model. This model is used to define the data elements and relationships for a specified system.
• It develops a conceptual design for the database. It also develops a very simple and easy-to-design view of data.
• In ER modeling, the database structure is portrayed as a diagram called an entity-relationship diagram.

For example, suppose we design a school database. In this database, the student will be an entity with attributes like address, name, ID, age, etc. The address can be another entity with attributes like city, street name, pin code, etc., and there will be a relationship between them.

SQL Fundamentals

SQL stands for Structured Query Language. It is a standardized programming language used to manage and manipulate data in relational databases.

SQL allows you to perform various tasks such as querying data, updating records, inserting new data, and deleting data in a database.

Key SQL Concepts

1. Database: A structured collection of data that is stored electronically. It can be thought of as a digital filing system.
2. Table: A table is a set of data organized in rows and columns within a database. Each table typically represents a specific entity, such as customers or products.
3. Query: A query is a request for data from a database. In SQL, you use the SELECT statement to retrieve data from one or more tables.
4. CRUD Operations: SQL supports four main operations:
   • Create: Adding new records to a table using the INSERT statement.
   • Read: Retrieving data using the SELECT statement.
   • Update: Modifying existing records using the UPDATE statement.
   • Delete: Removing records from a table using the DELETE statement.
5. Primary Key: A unique identifier for each record in a table. It ensures that each entry can be uniquely distinguished from others.
6. Foreign Key: A field in one table that links to the primary key in another table, establishing a relationship between the two tables.

SQL is widely used in various applications, from small-scale personal projects to large enterprise systems, making it an essential skill for anyone interested in data management or software development.

If you have any specific questions about SQL or need help with a particular SQL query, just let me know!

Advantages of SQL

1. Ease of Use: SQL has a simple and easy-to-understand syntax, making it accessible for users who may not have a programming background.
2. Powerful Data Manipulation: SQL allows for complex queries and data manipulation, enabling users to retrieve and analyze data efficiently.
3. Standardization: SQL is a standardized language, which means that it can be used across different database systems with minimal changes.
4. Data Integrity: SQL supports constraints like primary keys, foreign keys, and unique constraints, which help maintain data integrity and consistency.
5. Scalability: SQL databases can handle large amounts of data and can be scaled up to accommodate growing data needs.

Disadvantages of SQL

1. Complexity in Advanced Operations: While basic SQL operations are easy, more complex queries can become difficult to write and understand, especially for large datasets.
2. Performance Issues: SQL queries can become slow with very large databases if not optimized properly, leading to performance bottlenecks.
3. Limited Support for Unstructured Data: SQL is primarily designed for structured data, making it less suitable for handling unstructured data types like documents, images, or videos.
4. Vendor Lock-in: Different database vendors may implement SQL with slight variations, which can lead to compatibility issues when switching between database systems.
5. Security Risks: SQL databases can be vulnerable to attacks like SQL injection if not properly secured, which can compromise data integrity and security.

Database Normalization

1NF (First Normal Form), 2NF (Second Normal Form), and 3NF (Third Normal Form) are stages of database normalization, which is a process used to organize a database to reduce redundancy and improve data integrity.

1NF (First Normal Form)

A table is in 1NF if all its columns contain atomic (indivisible) values, and each entry in a column is of the same kind. There should be no repeating groups or arrays. For example, consider a table of students with their subjects:

This table is not in 1NF because the “Subjects” column contains multiple values. To convert it to 1NF, we can split the subjects into separate rows:

2NF (Second Normal Form)

A table is in 2NF if it is in 1NF and all non-key attributes are fully functionally dependent on the primary key. This means there should be no partial dependency of any column on the primary key. Using the previous example, if we add a column for the student’s department, we might have:

Here, “Department” depends only on “Student_ID” and not on the combination of “Student_ID” and “Subject.” To achieve 2NF, we would separate this into two tables:

Students Table

Subjects Table

3NF (Third Normal Form)

A table is in 3NF if it is in 2NF and all the attributes are not only fully functionally dependent on the primary key but also non-transitively dependent. This means that no non-key attribute should depend on another non-key attribute. If we had a scenario where the department name is stored in the students table, we might have:

In this case, “Department_Name” is dependent on “Department_ID” rather than directly on the primary key. To achieve 3NF, we would separate this into:

Students Table

Departments Table

In summary, normalization helps organize data efficiently, reducing redundancy and ensuring data integrity through these forms.

Database Keys

In the context of Database Management Systems (DBMS), keys are essential for uniquely identifying records within a table. Here’s a brief description of two important types of keys:

Candidate Key

• A candidate key is a set of one or more attributes (columns) that can uniquely identify a record in a table. Each candidate key can serve as a unique identifier for the records, meaning no two rows can have the same value for the candidate key attributes.
• A table can have multiple candidate keys, but only one of them will be selected as the primary key. For example, in a student database, both the student ID and email address could be candidate keys since each can uniquely identify a student.

Primary Key

• A primary key is a specific candidate key that has been chosen to uniquely identify records within a table. It must contain unique values and cannot contain NULL values. This ensures that every record can be identified without ambiguity.
• The primary key is crucial for maintaining the integrity of the database and is often used to establish relationships between tables. For example, in the student database, if the student ID is chosen as the primary key, each student will have a unique ID that distinguishes them from others.

In summary, while all primary keys are candidate keys, not all candidate keys are primary keys. The primary key is the selected candidate key that uniquely identifies each record in a table.

Database Management

Database management refers to the systematic approach of storing, organizing, and retrieving data in a database. It involves using a Database Management System (DBMS), which is software that interacts with the database, users, and applications to ensure that data is efficiently and securely managed.

Key Concepts of Database Management

1. Database: A structured collection of data that is stored and accessed electronically. Databases can store a wide variety of information, from simple text to complex data types.
2. DBMS: A Database Management System is software that provides the tools to create, manage, and manipulate databases. Examples include MySQL, Oracle, Microsoft SQL Server, and PostgreSQL.
3. Data Models: These are frameworks that dictate how data is organized and structured in a database. The most common data model is the relational model, which organizes data into tables (or relations) with rows and columns.
4. Schema: The schema defines the structure of the database, including the tables, fields, data types, and relationships between tables. It acts as a blueprint for how the database is constructed.
5. Data Integrity: This refers to the accuracy and consistency of data within the database. Techniques such as constraints, transactions, and normalization help maintain data integrity.
6. Query Language: SQL (Structured Query Language) is the most common language used to perform operations on a database, such as retrieving, updating, and deleting data.
7. Transactions: A transaction is a sequence of operations performed as a single logical unit of work. Database management ensures that transactions are processed reliably, maintaining data integrity even in the event of failures.
8. Backup and Recovery: Database management includes strategies for backing up data to prevent loss and recovery methods to restore data in case of corruption or failure.

In summary, database management encompasses the processes and tools used to handle data effectively, ensuring that it is organized, accessible, and secure. It plays a crucial role in various applications across industries, enabling efficient data handling and decision-making.