Lesson 1

Before you can create databases and other database objects, it's important that you understand basic relational concepts. Without such understanding, you will not be able to implement your databases effectively.
This module outlines some basic principals of relational databases.

Before interacting with a database, which basic relational concepts should a developer understand?
Before interacting with a relational database, a developer should grasp several foundational concepts of the relational database model. Understanding these concepts will not only allow a developer to better interact with a database but will also assist in the design and normalization of efficient and effective databases. Here are the key concepts:

1. Tables (Relations) A relational database is essentially a collection of tables, also known as relations. Each table represents a specific entity type (e.g., customers, orders, products). A table is composed of rows and columns, where each column represents an attribute (property or characteristic) of the entity, and each row represents a single instance or record of the entity.
2. Keys: Keys are a vital part of the relational model, serving as identifiers for record instances. The two main types of keys are:
   • Primary Key: This is a unique identifier for a row within a table. No two rows in a table can have the same primary key value.
   • Foreign Key: This is a column or set of columns in one table that refers to the primary key of another table. Foreign keys are used to establish relationships between tables.
3. Relationships: The relational model supports relationships between tables, which are a key component in maintaining data integrity. There are three types of relationships:
   
   1. One-to-One (1:1): A single record in Table A is related to a single record in Table B, and vice versa.
   2. One-to-Many (1:M): A single record in Table A can be related to one or more records in Table B, but a record in Table B is related to only one record in Table A.
   3. Many-to-Many (M:N): A single record in Table A can be related to one or more records in Table B, and vice versa. This relationship type is usually implemented via an intermediary table (also known as a junction table or bridge table) which breaks down the M:N relationship into two 1:M relationships.
4. Normalization: Normalization is a process used to eliminate redundant data and ensure data is stored logically. It involves decomposing tables into smaller tables and defining relationships between them to achieve data integrity and minimize data anomalies. There are several normal forms, each with a certain level of normalization: First Normal Form (1NF), Second Normal Form (2NF), Third Normal Form (3NF), and so on.
5. SQL (Structured Query Language): SQL is the standard language for interacting with a relational database. It allows you to perform tasks such as creating tables, inserting, updating, and deleting data, retrieving data from tables, and managing database schemas.
6. ACID Properties: ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure reliable processing of database transactions. Understanding these properties and how they are enforced in a database system is crucial for a developer working with transactional systems.

Understanding these basic relational concepts will provide a developer with a solid foundation for designing, implementing, querying, and maintaining a relational database. It's important to learn these concepts before diving into database development to ensure the development of efficient and effective databases.

After completing this module, you will be able to:

1. Define a SQL Server table
2. Describe the elements of a basic table
3. Define data integrity and explain how to enforce it.
4. Define data normalization and denormalization
5. Recognize candidates for data normalization and denormalization
6. Explain when to use primary and foreign keys
7. Define null values

The ability to make changes to the logical layer without disrupting existing users and processes is called logical data independence. It is the transformation between the logical layer and the external layer that provides logical data independence. As with physical data independence, there are degrees of logical data independence. It is important that you understand that most logical changes also involve a physical change. For example, you cannot add a new database object (such as a table in a relational DBMS) without physically storing the data somewhere; hence, a corresponding change is made in the physical layer. Moreover, deletion of objects in the logical layer will cause anything that uses those objects to fail but should not affect anything else. Here are some examples of changes in the logical layer that can be safely made thanks to logical data independence:

1. Adding a new database object
2. Adding data items to an existing object
3. Making any change in which a view can be placed in the external model that replaces (and processes the same as) the original object in the logical layer, such as combining or splitting existing objects

In the next lesson, you will learn all about the concepts underlying relational databases.