Editing Relational model (section)

=== Customer relation ===
{| class="wikitable"
|-
! Customer ID !! Name
|-
| 123 || Alice
|-
| 456 || Bob
|-
| 789 || Carol
|}

If we attempted to ''insert'' a new customer with the ID ''123'', this would violate the design of the relvar since '''<u>Customer ID</u>''' is a ''primary key'' and we already have a customer ''123''. The [[DBMS]] must reject a [[database transaction|transaction]] such as this that would render the [[database]] inconsistent by a violation of an [[Database integrity|integrity constraint]]. However, it is possible to insert another customer named ''Alice'', as long as this new customer has a unique ID, since the Name field is not part of the primary key.

''[[Foreign key]]s'' are [[integrity constraint]]s enforcing that the [[Value (computer science)|value]] of the [[attribute set]] is drawn from a ''[[candidate key]]'' in another [[relation (database)|relation]]. For example, in the Order relation the attribute '''<u>Customer ID</u>''' is a foreign key. A ''[[Join (SQL)|join]]'' is the [[Operation (mathematics)|operation]] that draws on [[information]] from several relations at once.  By joining relvars from the example above we could ''query'' the database for all of the Customers, Orders, and Invoices. If we only wanted the tuples for a specific customer, we would specify this using a [[restriction condition]]. If we wanted to retrieve all of the Orders for Customer ''123'', we could [[Information retrieval|query]] the database to return every row in the Order table with '''<u>Customer ID</u>''' ''123'' .

There is a flaw in our [[database design]] above. The Invoice relvar contains an Order ID attribute. So, each tuple in the Invoice relvar will have one Order ID, which implies that there is precisely one Order for each Invoice. But in reality an invoice can be created against many orders, or indeed for no particular order. Additionally the Order relvar contains an Invoice ID attribute, implying that each Order has a corresponding Invoice.  But again this is not always true in the real world. An order is sometimes paid through several invoices, and sometimes paid without an invoice. In other words, there can be many Invoices per Order and many Orders per Invoice. This is a '''[[many-to-many (data model)|many-to-many]]''' relationship between Order and Invoice (also called a ''non-specific relationship''). To represent this relationship in the database a new relvar should be introduced whose [[role]] is to specify the correspondence between Orders and Invoices:

 OrderInvoice ('''<u>Order ID</u>''', '''<u>Invoice ID</u>''')

Now, the Order relvar has a ''[[One-to-many (data model)|one-to-many relationship]]'' to the OrderInvoice table, as does the Invoice relvar. If we want to retrieve every Invoice for a particular Order, we can query for all orders where '''<u>Order ID</u>''' in the Order relation equals the '''<u>Order ID</u>''' in OrderInvoice, and where '''<u>Invoice ID</u>''' in OrderInvoice equals the '''<u>Invoice ID</u>''' in Invoice.