1

I have two tables

Customer(cid (PK), name, phone)

Order(oid,cid (FK),date,amount)

A customer can have many orders but each order belongs to only one customer.

The question is what is the relationship between them. Is it a one to many identifying relationship or non identifying?

Since an order cannot exist without a customer that makes it an Identifying relationship?

So I should be including the cid in the primary key of the order table or not?

The database cannot have a customer without any order.

EJoshuaS - Stand with Ukraine
  • 11,977
  • 56
  • 49
  • 78
Karan Gupta
  • 529
  • 2
  • 7
  • 21

1 Answers1

2

Identifying relationships are not determined by an existence dependency between two entity sets. An existence dependency generally means an entity set must participate totally in a relationship.

Identifying relationships mean one entity set depends on another for identity. The dependent entity set is called a weak entity set, since it can't be identified by its own attributes alone.

In your example, I assume oid is an auto-incremented column. That makes it a surrogate key which identifies Order, and Order is then a regular entity set since it can be identified by its own attributes. This means the relationship between the two entity sets isn't identifying.

The relationship is represented by the pair of columns (oid, cid). oid should be PK, so that each order (oid) can only be associated with one customer (cid). However, there's no unique constraint on cid, which means each cid can be associated with multiple oid. Hence, it's a one-to-many relationship.

Also see my answer to the following question: is optionality (mandatory, optional) and participation (total, partial) are same?

Community
  • 1
  • 1
reaanb
  • 9,806
  • 2
  • 23
  • 37