Introduction to Relational Databases

Fundamentals of a Relational Database

Introduction

A relational database is an application that contains various tables where values in a table are made available to records in another table so that data entered in one table is not entered again in another table. To have a relational database, there must be relationships created among that tables that need to exchange data.

Practical Learning: Introducing Data Relationships

1. Start Microsoft Access
2. Click Blank Desktop Database
3. Set the file name to Brokerage Company2
4. Click Create
5. On the Ribbon, click Create and click Query Design
6. In the Show Table dialog box, click Close
7. Right-click the body of the window and click SQL View
8. Replace the default code with the following:

   CREATE TABLE Agents
   (
       AgentCode	 char(12),
       FirstName	 text(20),
       LastName	 text(20),
       Title	 string(50),
       PayFrequency varchar(25)
   );

9. To create the table, on the Ribbon, click Design if necessary and click the Run button
10. Change the code as follows:

    CREATE TABLE Companies
    (
        CompanyCode char(10),
        Companyname text(60)
    );

11. To create the table, on the Ribbon, click the Run button
12. Replace the code with the following:

    CREATE TABLE Transactions
    (
        TransactionID   Long,
        TransactionDate date,
        AgentCode	    char(12),
        CompanyCode	    char(10),
        NumberOfShares  Long,
        PricePerShare   double,
        PaymentStatus   string(25)
    );

13. To modify the table, on the Ribbon, click the Run button
14. On the Ribbon, click File and click Options
15. In the left list of the Access Options dialog box, click Current Database
16. In the main list, click Overlapping Windows

    

17. Click OK on the dialog box
18. Click OK on the message box
19. On the Ribbon, click File and click New
20. Click Blank Desktop Database
21. Set the name to National Bank1
22. Click Create
23. When asked whether you want to save Query1, click No
24. On the Ribbon, click File and click Options
25. Click Current Database
26. In the right list, click Overlapping Windows
27. Click OK on the dialog box
28. Click OK on the message box

Introduction to Relation Keys

When it comes to relational databases, a key in a table is a field or a combination of columns used to manage the flow of records from one list or table, referred to as the source, to another list or table, referred to as the target. Relational databases support the primary and the foreign keys.

A primary key in a table is a field or a combination of columns that makes sure that each record is unique in that table. This means that a primary key can use one or more fields in a table.

Primary Keys

Visually Creating a Primary

To visually create a primary key, start a table in the Design View, specify the name and data type of the new field that will be used as the primary key, or display an existing table in Design View. In the top side of the Design View window, click the field that will be used as the primary key. Although a primary key can use almost any type of data, the most common types are:

• String: If you use a (short) string, make sure you specify its length or Field Size. By tradition, it should be less than 50 but this is suggestion, not a rule
• Integers: You can use a Long data type or an Integer

Although the other data types (floating-point numbers, Boolean values, dates, etc) are not excluded, they are not good candidates for primary keys. Once you have specified the column name and its type:

• Right-click the field and click Primary Key
• In the Tools section of the Design tab of the Ribbon, click the Primary Key button

The field that is made the primary key would then appear with a key icon to its left. When you click a field that is a primary key, the Primary Key button becomes highlighted .

As mentioned above, a primary key can use more than one column. To visually create such a primary key, display the table in Design View. Select, as a group, the fields that would constitute the primary key. Then:

• Right-click one of the selected fields of the group and click Primary Key

  

• In the Tools section of the Design tab of the Ribbon, click the Primary Key button

Each one of the fields would appear with a key icon on its left:

Practical Learning: Introducing Primary Keys

1. On the default table, double-click ID to put it into edit mode
2. Press F2 and press Home
3. Type Transaction to get TransactionID and press Enter
4. In the list that appears under Click to Add, click Short Text
5. Type Location and press Enter
6. Press Esc to dismiss the list
7. Close the table
8. When asked whether you want to save, click Yes
9. Set the name to Transactions and click OK
10. On the Ribbon, click File and click Close
11. In the list of files, click National Bank1
12. On the Ribbon, click Create and click Query Design
13. In the Show Table dialog box, click Close
14. Right-click the body of the window and click SQL View

Creating a Primary Key in SQL

To programmatically create a primary key in SQL, you have many options, all of them use the PRIMARY KEY expression on the list of flags. The simplest technique is to add that expression at the end of the column creation. Here is an example:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Departments(Gender varchar(20) PRIMARY KEY);"
End Sub

A Primary Key Constraint

A primary key is referred to as a constraint because it makes sure that each record is unique among the other records of its table. To re-enforce, a formula to create a primary key is:

CONSTRAINT PrimaryKeyName PRIMARY KEY(ColumnName)

In this formula, the CONSTRAINT (case-insensitive) keyword and the PRIMARY KEY (case-insensitive) expression are required. In the PrimaryKeyName placeholder, enter the name you want to give to the primary key. In the parentheses of the PRIMARY KEY expression, enter the name of the column that will be used as the primary key. Here is an example:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Persons" & _
                 "(" & _
                 "    PersonalIdentification char(10)," & _
                 "    FirstName varchar(20)," & _
                 "    LastName varchar(20) NOT NULL," & _
                 "    CONSTRAINT PrimKeyPeople PRIMARY KEY(PersonalIdentification)" & _
                 ");"
End Sub

By convention or tradition, the name of the primary starts with PK_ followed by the name of the table. Here is an example:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Persons" & _
                 "(" & _
                 "    PersonalIdentification char(10)," & _
                 "    FirstName varchar(20)," & _
                 "    LastName varchar(20) NOT NULL," & _
                 "    CONSTRAINT PK_People PRIMARY KEY(PersonalIdentification)" & _
                 ");"
    MsgBox "A table named Persons has been created"
End Sub

Practical Learning: Creating Primary Key Constraint

1. Type the following code:

   CREATE TABLE AccountsTypes
   (
       AccountType char(20),
       Description  LongText,
       CONSTRAINT PK_AccountsTypes PRIMARY KEY(AccountType)
   );

2. To create the table, on the Ribbon, click the Run button
3. Change the code as follows:

   CREATE TABLE TransactionsTypes
   (	
       TransactionType char(20),
       Description  LongText,
       CONSTRAINT PK_TransactionsTypes PRIMARY KEY(TransactionType)
   );

4. To create the table, on the Ribbon, click the Run button
5. Change the code as follows:

   CREATE TABLE AccountsStatus
   (
       AccountStatus char(20),
       Description  LongText,
       CONSTRAINT PK_AccountsStatus PRIMARY KEY(AccountStatus)
   );

6. To create the table, on the Ribbon, click the Run button
7. Change the code as follows:

   CREATE TABLE ChargesReasons
   (	
       ChargeReason char(20),
       Description  LongText,
       CONSTRAINT PK_ChargesReasons PRIMARY KEY(ChargeReason)
   );

8. To create the table, on the Ribbon, click the Run button
9. Change the code as follows:

   CREATE TABLE CurrenciesTypes
   (	
       CurrencyType char(20),
       Description  LongText,
       CONSTRAINT PK_CurrenciesTypes PRIMARY KEY(CurrencyType)
   );

10. To create the table, on the Ribbon, click the Run button
11. Change the code as follows:

    CREATE TABLE Locations
    (	
        LocationCode string(10),
        Location     string(10),
        Address      text(50),
        City         text(40),
        State        char(2),
        ZIPCode      varchar(20),
        Description  LongText,
        CONSTRAINT PK_Locations PRIMARY KEY(LocationCode)
    );

12. To create the table, on the Ribbon, click the Run button

Adding a Primary Key to an Existing Table

Consider two tables named Departments and Employees and created using the following code:

Private Sub cmdCreateTables_Click()
    DoCmd.RunSQL "CREATE TABLE Departments" & _
                 "(" & _
                 "      DepartmentCode varchar(5)," & _
                 "      DepartmentName Text(50)," & _
                 ");"
    DoCmd.RunSQL "INSERT INTO Departments VALUES('HMNRS', 'Human Resources');"
DoCmd.RunSQL "INSERT INTO Departments VALUES('ITSUP', 'Information Technology and Support');"
    DoCmd.RunSQL "INSERT INTO Departments VALUES('RSDEV', 'Research & Development');"
    
    DoCmd.RunSQL "CREATE TABLE Employees" & _
                 "(" & _
                 "      FirstName Text(24)," & _
                 "      LastName Text(24)," & _
                 "      DepartmentCode varchar(5)" & _
                 ");"
End Sub

You may already know how to modify an existing table to add a new column. If you have a table that has a column that can be used as the primary key, create an ALTER TABLE expression. To create a primary key, add an ADD CONSTRAINT clause that uses the same formula as if you were creating a primary key. The formula to follow is:

ALTER TABLE TableName ADD CONSTRAINT Statement

Here is an example:

Private Sub cmdMakePrimaryKey_Click()
    DoCmd.RunSQL "ALTER TABLE Departments " & _
                 "ADD CONSTRAINT PK_Departments PRIMARY KEY(DepartmentCode);"
End Sub

To add a new column that would be the primary key, create an ALTER TABLE ... ADD COLUMN statement. To set the new column as a primary key, add a PRIMARY KEY constraint. Here is an example:

Private Sub cmdCreatePrimaryKey_Click()
    DoCmd.RunSQL "ALTER TABLE Employees " & _
                 "ADD COLUMN EmployeeNumber varchar(10)," & _
                 "CONSTRAINT PK_Employees PRIMARY KEY(EmployeeNumber);"
End Sub

Foreign Keys

Introduction

A foreign key is a column on a table whose data is coming from another table. That data is coming from the primary key of another table. This means that you must first create a table that has a primary key. The table that contains a primary key and that holds the information that another table would use is called the primary table or the parent table. The table that will receive the information from the other table is called the foreign table or the child table.

Visually Creating a Foreign Key

To create a foreign key in a table, display it in either the Datasheet View or the Design View. If you are working in the Design View and you are creating a new foreign key, specify the name of the new column. Whether you are working in the Datasheet View or the Design View, the only real rule you must observe is that the column must have the same data type as that of the primary key with which it will communicate. If the primary key is using a (short) string with a length or a Field Size set, the foreign key should (must) use the same type and the same maximum length.

Creating a Foreign Key in SQL

The formula to create a foreign key in the SQL is:

ColumnName DataType REFERENCES ParentTableName(ForeignKeyCcolumn) 

The REFERENCES keyword is required. In the ParentTableName placeholder, enter the name of the table that holds the primary key. In the parentheses of , enter the name of the primary column of the parent table. Here is an example:

CREATE TABLE Departments(Gender varchar(20) PRIMARY KEY);

CREATE TABLE Persons
(
    PersonalIdentification char(10),
    FirstName varchar(20),
    LastName varchar(20) NOT NULL,
    Gender varchar(20) REFERENCES Departments(Gender),
    CONSTRAINT PK_Persons PRIMARY KEY(PersonalIdentification)
);

A Foreign Key Constraint

The above technique allows you to ask the database engine to give a name to the foreign key you are creating. An alternative is to specify a name of your choice. To do this, after the list of columns of the table, create a foreign key constraint with an explicit name. Here is an example:

Private Sub cmdCreateForeignKey_Click()
    DoCmd.RunSQL "CREATE TABLE CoursesLevels" & _
                 "(" & _
                 "    CourseLevel varchar(25), " & _
                 "    Comments note, " & _
                 "    CONSTRAINT PK_CoursesLevels PRIMARY KEY(CourseLevel) " & _
                         ");"

    DoCmd.RunSQL "CREATE TABLE Courses" & _
                 "( " & _
                 "    CourseCode varchar(10) not null, " & _
                 "    CourseName varchar(60), " & _
                 "    CourseLevel varchar(20), " & _
                 "    Notes note, " & _
                 "    CONSTRAINT FK_CourseLevels Foreign Key(CourseLevel)  " & _
                 "      REFERENCES CoursesLevels(CourseLevel), " & _
                 "    CONSTRAINT PK_Courses PRIMARY KEY(CourseCode) " & _
                 ");"
End Sub

Practical Learning: Creating Foreign Key Constraints

1. Access the Query window and change its contents with the following code:

   CREATE TABLE Employees
   (
       EmployeeNumber text(10),
       FirstName varchar(20),
       MiddleName varchar(20),
       LastName varchar(20),
       LocationCode string(10),
       Title varchar(50),
       CanCreateNewAccount bit,
       CONSTRAINT FK_EmployeesLocations FOREIGN KEY(LocationCode) REFERENCES Locations(LocationCode),
       CONSTRAINT PK_Employees PRIMARY KEY(EmployeeNumber)
   );

2. To create the table, on the Ribbon, click the Run button
3. Change the code as follows:

   CREATE TABLE Customers
   (
       AccountNumber text(10),
       EmployeeNumber text(10),
       DateCreated date,
       AccountType char(20),
       FirstName varchar(20),
       MiddleName varchar(20),
       LastName varchar(20),
       AccountStatus char(20),
       CONSTRAINT FK_AccountsCreators FOREIGN KEY(EmployeeNumber) REFERENCES Employees(EmployeeNumber),
       CONSTRAINT FK_AccountsTypes FOREIGN KEY(AccountType) REFERENCES AccountsTypes(AccountType),
       CONSTRAINT FK_AccountStatus FOREIGN KEY(AccountStatus) REFERENCES AccountsStatus(AccountStatus),
       CONSTRAINT PK_Customers PRIMARY KEY(AccountNumber)
   );

4. To create the table, on the Ribbon, click the Run button
5. On the Ribbon, click Create and click Table
6. On the table, double-click ID to put it into edit mode
7. Press F2, press Home, type AccountHistory to get HistoryID and press Enter
8. Close the table
9. When asked whether you want to save, click Yes
10. Set the name to AccountsHistories and click OK

Adding a Foreign Key to an Existing Table

Consider the following table named Courses:

Private Sub cmdCreateTable_Click()
    DoCmd.RunSQL "CREATE TABLE CoursesLevels" & _
                 "(" & _
                 "    CourseLevel varchar(25), " & _
                 "    Comments note, " & _
                 "    CONSTRAINT PK_CoursesLevels PRIMARY KEY(CourseLevel) " & _
                         ");"

    DoCmd.RunSQL "CREATE TABLE Courses" & _
                 "( " & _
                 "    CourseCode varchar(10) not null, " & _
                 "    CourseName varchar(60), " & _
                 "    Notes note, " & _
                 "    CONSTRAINT PK_Courses PRIMARY KEY(CourseCode) " & _
                 ");"
End Sub

If you have an existing table, you can add a new column that would act as a foreign key. This is done by creating an ALTER TABLE...ADD COLUMN expression. Here is an example:

Private Sub cmdCreateForeignKey_Click()
    DoCmd.RunSQL "ALTER TABLE Courses " & _
                 "ADD COLUMN CourseLevel varchar(25) " & _
                 "      REFERENCES CoursesLevels(CourseLevel);"
End Sub

As mentioned already, this type of code lets the database engine specify the name of the foreign key. If you want to create a name of your choice, explicitly create a constraint and give it a name. Here is an example:

Private Sub cmdCreateForeignKey_Click()
    DoCmd.RunSQL "ALTER TABLE Courses " & _
                 "ADD COLUMN CourseLevel varchar(25), " & _
                 "CONSTRAINT FK_CoursesLevels FOREIGN KEY(CourseLevel) " & _
                 "  REFERENCES CoursesLevels(CourseLevel);"
End Sub

Imagine you have a table that has a column that is in fact the foreign key of a parent table but the column was not specified as such.  To formally change such as a column into a foreign key, start an ALTER TABLE expression and add a foreign key that indicates the parent table and the corresponding column. Here is an example:

Private Sub cmdAddForeignKey_Click()
    DoCmd.RunSQL "ALTER TABLE Employees " & _
                 "ADD CONSTRAINT FK_Departments FOREIGN KEY(DepartmentCode) " & _
                 "  REFERENCES Departments(DepartmentCode);"
End Sub

Practical Learning: Adding Foreign Keys to a Table

1. Access the Query window and, to add foreign keys to an existing table, change the code as follows:

   ALTER TABLE AccountsHistories
   ADD COLUMN AccountNumber text(10),
   CONSTRAINT FK_CustomersHistories FOREIGN KEY(AccountNumber)
              REFERENCES Customers(AccountNumber);

2. To create the table, on the Ribbon, click the Run button
3. Change the code as follows:

   ALTER TABLE AccountsHistories
   ADD COLUMN AccountStatus text(20),
   CONSTRAINT FK_AccountsStatus FOREIGN KEY(AccountStatus)
              REFERENCES AccountsStatus(AccountStatus);

4. To create the table, on the Ribbon, click the Run button
5. Close the Query window
6. When asked whether you want to save, click No
7. In the Navigation Pane, right-click the AccountsHistories table and click Design View
8. Click the empty cell below AccountStatus
9. Type DateChanged and press Tab
10. In the Data Type combo box, select Date/Time and press F6
11. In the bottom side of the window, click Caption and type Date Changed
12. Create a new field with the following characteristics:
    Field Name: ShortNote
    Field Size: 50
    Caption: Short Note
13. Create a new field with the following characteristics:
    Field Name: LongNote
    Data Type: Long Text
    Caption: Long Note
14. Close the Query window
15. When asked whether you want to save, click Yes

A Relationship Diagram

Introduction

A relationship diagragram is a graphic used to visualize the relationships among the tables of a database. To assist with this visualization, Microsoft Access provides the Relationships window. To get it:

• If no table is currently opened, on the Ribbon, click Database Tools:

  

  In the Relationships section, click the Relationships button
• If a table is currently displaying, on the Ribbon, click either Database Tools or Table

  

  In the Relationships section, click the Relationships button

After clicking one of those, to add a table, click it, click Add, and click Close

When you are working on the relationships of your tables, the Ribbon is equipped with a tab labeled design and that includes two sections:

If the Show Table dialog box has been closed and if you want to show it:

• Click the Show Table button on the Ribbon
• Right-click the body of the Relationships window and click Show Table...

After working with the Relationships window, you can close it by clicking its Close button . You would be asked to save it in order to keep the relationship(s) created.

Establishing a Relationship

To establish a relationship between two tables, you can drag the primary key from the parent table to the foreign key of the desired table.

Practical Learning: Establishing a Relationship

1. On the Ribbon, click File and click Open
2. In the list of files, click Brokerage Company2 created earlier
3. On the Ribbon, click Database Tools and click the Relationships button

   

4. In the Show Table dialog box, double-click Agents
5. In the Show Table dialog box, click Transactions and click Add
6. Double-click Companies and click Close
7. From the Agents list, drag AgentCode and drop it on top of AgentCode in the Transactions list:

   

8. In the Edit Relationships dialog box, make sure AgentCode is selected in both combo boxes and click Create

   

9. From the Companies list, drag CompanyCode and drop it on top of CompanyCode in the Transactions list
10. In the Edit Relationships dialog box, make sure CompanyCode is selected in both combo boxes and click Create

    

11. To close the Relationships window, click its Close button
12. When asked whether you want to save, click Yes

Managing Relationships

If you create primary keys and foreign keys in a database, when you open the Relationships window, the database engine will be aware of the relationships already for the tables that have the primary keys and foreign keys.

Practical Learning: Using a Relationship Diagram

1. On the Ribbon, click File and click Open
2. In the list of files, click Kolo Bank1 created earlier
3. On the Ribbon, click Database Tools and click the Relationships button
4. Move the table around as you see fit. Here is an example:

   

5. In the Relationships section of the Ribbon, click the Close button
6. When asked whether you want to save, click Yes