Lesson 1

Before we jump into the nuts and bolts of relational database design, let us now review the terminology, which is essential for the successful comprehension of this course.

• Learning Objectives:
  After completing the lessons in this module, you should be able to:
  1. Describe the relational database model
  2. Describe the structure of a relational database
  3. Explain how relational database tables are linked to access data
  4. Explain why SQL is the "language of choice" for relational databases
  5. Describe the functions of a relational database management system

1. Describe the Relational Database Model

   The relational database model organizes data into one or more relations, commonly referred to as tables, with rows and columns. Each table represents a specific entity or concept and consists of:

   • Rows (Tuples): Each row represents a single record or instance of the entity.
   • Columns (Attributes): Each column represents a property or field of the entity, defining the type of data that can be stored.

   The relational model uses primary keys to uniquely identify rows and foreign keys to establish relationships between tables. It is grounded in mathematical principles, particularly set theory and predicate logic, ensuring data consistency and integrity.

2. Describe the Structure of a Relational Database

   A relational database is structured as follows:

   • Tables: Organized into rows (records) and columns (fields). Each table stores data about a specific entity.
   • Primary Keys: Unique identifiers for each row within a table, ensuring no duplicate records.
   • Foreign Keys: Columns that link one table to another, establishing relationships.
   • Indexes: Special data structures that improve the speed of data retrieval by enabling efficient search and query performance.
   • Constraints: Rules enforced on data, such as NOT NULL, UNIQUE, and CHECK, to maintain data integrity.
   • Schema: A blueprint that defines the structure, relationships, and constraints within the database.
3. Explain How Relational Database Tables Are Linked to Access Data

   Relational database tables are linked using keys and relationships:

   • Foreign Keys: A foreign key in one table references the primary key of another table, creating a link between the two. For example, a CustomerID in an Orders table links to the CustomerID in the Customers table.
   • Joins: SQL queries use JOIN operations (e.g., INNER JOIN, LEFT JOIN) to combine data from multiple tables based on these key relationships.
   • Normalization: Ensures that data is organized into related tables to eliminate redundancy while maintaining logical connections.
   
   This system allows users to retrieve and manipulate data across multiple tables seamlessly.
4. Explain Why SQL Is the "Language of Choice" for Relational Databases:
   SQL (Structured Query Language) is the "language of choice" for relational databases due to its following strengths:
   1. Standardization: SQL is an ANSI/ISO standardized language, making it universally accepted across major relational database management systems (RDBMS) like MySQL, PostgreSQL, Oracle, and SQL Server.
   2. Declarative Syntax: SQL allows users to specify what data they want to retrieve or manipulate without describing how to do it. This simplicity and abstraction make it accessible.
   3. Versatility: SQL supports a wide range of operations, including data insertion, retrieval, updating, and deletion, as well as schema creation and data access control.
   4. Optimization: Most RDBMS engines optimize SQL queries for performance, making it efficient for both simple and complex data operations.
   5. Scalability and Interoperability: SQL can handle large-scale databases and works well with modern tools and applications, making it a reliable and scalable solution.

SQL’s flexibility and power make it indispensable for managing relational databases effectively.

There are elements in a business environment which we need to store, and those elements are related to one another in a variety of ways. In fact, to be considered a database, the place where data are stored must contain not only the data but also information about the relationships between those data. We might need to relate our customers to the orders they place with us and our inventory items to orders for those items. The idea behind a database is that the user, either a person working interactively or an application program has no need to worry about how data are physically stored on disk. The user phrases data manipulation requests in terms of data relationships. A piece of software known as a database management system (DBMS) then translates between the user's request for data and the physical data storage.
Why don't the simple database software packages produce true databases?
Because they cannot represent relationships between data, much less use such relationships to retrieve data. The problem is that pre-packaged software has been marketed for years as database software, and many purchasers do not understand exactly what they are purchasing. Making the problem worse is that a rectangular area of a spreadsheet is also called a database. As you will see later in this book, a group of cells in a spreadsheet is even less of a database than a stand-alone list. Because this problem of terminology remains, confusion about exactly what a database happens to be remains as well. Here is a quick overview of the primary elements of a relational database. These elements will be described in more detail as you continue with the course.

A relational database is a collection of related data tables. Columns describe the specific pieces of information in the table and each row stores the corresponding data. For example a "Customer" table may contain columns:

CustID, Name, City, Item ID, Description, Qty, Total

The table would then have customer data stored in the rows of the table.

• Primary Keys: A primary key is one column or compilation of several columns that has a unique value, making each row unique in the table.
  Every table must have a primary key because it is used to link data in related tables. For example, the primary key of the Customer table would be the column called "CustomerId", whereas the Order History table may have "OrderID" as a primary key.
• Foreign Keys A foreign key is the primary key from another table and is used to relate rows of data between tables.
  For example, the Order History table has a primary key of "OrderId" to identify each record. To know which customer placed the order, the data from the customer's "CustomerID" column from the Customer table is stored in the Order History row. Queries then can be performed to join the data from both tables together in business applications.

The next lesson introduces you to the structures of the relational database model.