A relational database is a type of database. It uses a structure that allows us to identify and access data in relation to another piece of data in the database. Often, data in a relational database is organized into tables. We can connect to relational databases for analysing data using the pandas library as well as another additional library for implementing database connectivity. This package is named as sqlalchemy which provides full SQL language functionality to be used in python. Installing SQLAlchemy. The installation is very straight forward using Anaconda which we have discussed in the chapter Data. Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance. This article describes how to create foreign key relationships in SQL Server 2019 (15.x) by using SQL Server Management Studio or Transact-SQL. You create a relationship between two tables when you want to associate rows of one table with rows of another.
What is a Relational Database? According to Oracle, a relational database is “a type of database that stores and provides access to data points that are related to one another”. OK, sounds good! We can create, read, update and delete (the basic functions of any database) the information in our relational database using a Relational Database Management System.
Relational Database Rules
Building a system in Oracle or some other relational database product does not automatically make it a relational database. Similarly, you can design a perfectly good relational database and implement it in something other than a relational database product. We discuss two important areas:
- What do people mean by relational database?
- What is the Oracle relational database product?
What makes a database “relational”?
When a database is described as relational, The padre bar. it has been designed to conform (at least mostly) to a set of practices called the rules of normalization. A normalized database is one that follows the rules of normalization.
For example, in an organization, you have employees who work in specific departments. Each employee and department has a number and a name. You could organize this information as shown in Table 1. Briefly 1 5 2 – still motion video creator.
Table 1: Sample Employee Information
EmpNo | Ename | DeptNo | DeptName |
101 | Abigail | 10 | Marketing |
102 | Bob | 20 | Purchasing |
103 | Carolyn | 10 | Marketing |
104 | Doug | 20 | Purchasing |
105 | Evelyn | 10 | Marketing |
If you structure your data this way and make certain changes to it, you’ll have problems. For example, deleting all the employees in the Purchasing department will eliminate the department itself. If you change the name of the Marketing department to “Advertising,” you would need to change the record of each employee in that department.
Using the principles of relational databases, the Employee and Department data can be restructured into two separate tables (DEPT and EMP), as shown in Tables 2 and 3.
Table 2: A Sample Relational DEPT Table
DeptNo | DeptName |
10 | Marketing |
20 | Purchasing |
Table 3: A Sample Relational EMP Table
EmpNo | EName | DeptNo |
101 | Abigail | 10 |
102 | Bob | 20 |
103 | Carolyn | 10 |
104 | Doug | 20 |
105 | Evelyn | 10 |
By using this structure, you can examine the EMP table to find out that Doug works in department 20. Then you can check the DEPT table to find out that department 20 is Purchasing. You might think that Table 1 looks more efficient. However, retrieving the information you need in a number of different ways is much easier with the two-table structure. Joining the information in the two tables for more efficient retrieval is exactly the problem that relational databases were designed to solve.
When the tables are implemented in the database, the information in the two tables is linked by using special columns called foreign keys. In the example, the DeptNo column is the foreign key linking the Department and Employee tables.
Tables 4 and 5 show another common database structure, namely a purchase order (PURCH_ORDER table) for an item and the information details associated with the purchase order (PURCH_ORDER_DTL table).
Table 4: A Sample Relational PURCH_ORDER Table Movavi photo editor 6 2 0 64.
![Relation Database Relation Database](https://cdn.ttgtmedia.com/rms/onlineimages/sql_server-relational_database_mobile.png)
PO_Nbr | Date |
450 | 12/10/2006 |
451 | 2/26/2006 |
452 | 3/17/2006 |
453 | 6/5/2006 |
Table 5: A Sample Relational PURCH_ORDER_DTL Table
PO_Nbr | Line_Nbr | Item | Qty | Price |
450 | 1 | Hammer | 1 | $10.00 |
451 | 1 | Screwdriver | 1 | $8.00 |
451 | 2 | Pliers | 2 | $6.50 |
451 | 3 | Wrench | 1 | $7.00 |
452 | 1 | Wrench | 3 | $7.00 |
452 | 2 | Hammer | 1 | $10.00 |
453 | 1 | Pliers | 1 | $6.50 |
![Relational database Relational database](https://cdn.slidesharecdn.com/ss_thumbnails/chapter7relationdatabaselanguage-130729001451-phpapp01-thumbnail-4.jpg?cb=1375057014)
Relational Database Example
A purchase order can include many items. Table 5 shows that Purchase Order 451 includes three separate items. The link (foreign key) between the tables is the Purchase Order Number.
Understanding basic database terminology
A database consists of tables and columns, as described in the preceding section. There are some other terms you need to know in order to understand how databases work. A database is built in two stages. First you create a logical data model to lay out the design of the database and how the data will be organized. Then you implement the database according to the physical data model, which sets up the actual tables and columns. Different terminology applies to the elements of the logical and physical designs. In addition, relational database designers use different words from object-oriented (OO) database designers to describe the database elements. Table 6 shows the words used in each of these cases.
Table 6: Database Design Terminology
Relational Database
Logical/Relational | Logical/Object-Oriented | Physical Implementation |
Entity | Class | Table |
Attribute | Attribute | Column |
Instance | Object | Row |
The definitions of the words in Table 6 are as follows:
- Entity: An entity corresponds to something in the real world that is of interest and that you want to store information about. Examples of entities include things such as departments within an organization, employees, or sales. Each specific department or employee is considered an instance of that entity. For example, in Table 3, Doug is an instance of the entity Employee. (In the OO world, Doug would be an object in the Employee class.)
- Attribute: This word is used in both relational and OO databases to represent information about an entity instance or an object that will be tracked. An example of an attribute might be the birth date or Social Security number of an employee.
- Entities (classes), their attributes, and instances (objects): These are implemented in the database as tables, columns, and rows respectively.
Relation Database
One additional important concept to understand when dealing with relational databases is the primary key. A primary key uniquely identifies a specific instance of an entity. No two instances of an entity can have the same primary key. The values of all parts of the primary key must never be null. The most common types of primary keys in relational databases are ID numbers. For example, in Table 3, the EmpID can be the primary key. Sometimes more than one attribute (or sets of attributes) can be used as a primary key. These attributes are called candidate keys, one set of which must be designated as the primary key.