Model (Database)
Model, Domain Model, Relational Model, Keys (Super, Candidate, Primary)
What is a Model ?
Model is an Object. Anything that exists in the word can be called or represented in RDBMS as a Model.
For example, Car, Person, Employee, Telephone, Tree, Country, etc.
You may compare Model with Noun in human language.
It can represent a People, Place, Thing and Idea.
What is a Domain Model ?
Domain means a context or a specific business boundary. A closed area which is connected with other areas.
You may call your entire business as a single domain OR each department as separate domains.
For example:
HR domain, Product domain, Sales domain, Customer relationship domain, Tax domain, Electrical domain, Electronics domain, Civil Engineering domain, etc.
Domain model represents business data and some behavior.
Domain can have sub-mains as well. (Core domain & Sub-domains)
Domain Model = Domain Data + Behavior
https://upload.wikimedia.org/wikipedia/commons/2/2d/Domain_model.png
What is a Relational Model?
Two or more models which are related to each other.
For example: Employee works in a Department. An Organization has many Departments.
Understanding a Model = Understanding SET Theory
Have a look at the relationship between the two models Product & Customer.
Relation is represented by keys and relation-name.
What are Keys?
Like in real word, each lock has its own key.
In RDBMS, key is an attribute used to uniquely identify a record in a table.
3 types of keys [Super Key, Candidate Key, Primary Key]
Super Key
One column or combination of two or more columns uniquely identifies a record in a table, then these sets of keys are called Super keys.
Example: consider the above Customer table
Super keys are [Id, (Id + Name), (Id + Type) + (Id + Name + Type)]
With the combination of (Id + Name) we can uniquely identify a record.
With the combination of (Id + Type) we can uniquely identify a record and so on.
Problem in super key is that it contains possible combination of all the columns in a table. Here in the Customer table we’ll be having 4 super keys.
Candidate Key
Minimal set of columns which can uniquely identify a record in the table.
For example:
- Id is a Candidate key in customer table
- Id is a candidate key in Product table
- Name could also be a candidate key in Product table.
A candidate key can never be NULL or empty and should be unique.
There can be more than one candidate keys for a table.
A candidate key can be a combination of two or more columns of a table.
The problem with candidate key is that there could be multiple candidate keys in a table.
Primary Key
Primary key the most optimal candidate key. It is a simple key on a single column.
For example:
Id is the Primary key (Product, Customer, etc.) tables.
There can be only one Primary key in a table.
Reference: