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.
Lesson 1
Relational Database Structure and Terminology
Before we jump into the nuts and bolts of relational database design, let us now review relational database structure and 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:
- Describe the relational database model
- Describe the structure of a relational database
- Explain how relational database tables are linked to access data
- Explain why SQL is the lingua franca of relational databases
- Describe the functions of a relational database management system
Fundamental Concept behind all Databases
There are things in a business environment, about which we need to store data, and those things 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, for example, 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, then, don't the simple database software packages (the list managers) produce true databases? Because they cannot represent relationships between data, much less use such relationships to retrieve data. The problem is that list management 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.
Ad Database Systems
Why, then, don't the simple database software packages (the list managers) produce true databases? Because they cannot represent relationships between data, much less use such relationships to retrieve data. The problem is that list management 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.
Database Tables
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:
The table would then have customer data stored in the rows of the table.
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
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.
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.