There are three levels of data abstraction:

  1. Physical level: This is the lowest level of abstraction and describes how the data is actually stored. It deals with the physical storage of the information on the disk.
  2. Logical level: The next level of abstraction, the logical level describes what data is stored and what relationship exists among those data.
  3. View level: This is the highest level of abstraction and describes only part of the entire database. It is designed to hide the complexity and only show relevant information to the user.
Procedural DML Declarative DML
Requires the user to specify what data is needed and how to get it Requires the user to specify what data is needed without specifying how to get it
Examples: SQL's INSERT, UPDATE, DELETE Examples: SQL's SELECT
More control over the database Less control over the database
More complex to write and understand Easier to write and understand
More time-consuming Less time-consuming

Mapping Cardinality

Mapping cardinality, also known as cardinality ratio, defines the number of associations between two entities. There are four types of mapping cardinalities:

  1. One to One (1:1): An entity in A is associated with at most one entity in B, and an entity in B is associated with at most one entity in A. Example: Each employee in a company has one ID card, and each ID card belongs to one employee.
  2. One to Many (1:N): An entity in A is associated with any number (zero or more) of entities in B. An entity in B is associated with at most one entity in A. Example: A teacher can have multiple students, but each student has only one class teacher.
  3. Many to One (N:1): An entity in A is associated with at most one entity in B. An entity in B is associated with any number (zero or more) of entities in A. Example: Many students can enroll in the same course, but each student enrolls in one course at a time.
  4. Many to Many (N:M): An entity in A is associated with any number (zero or more) of entities in B, and an entity in B is associated with any number (zero or more) of entities in A. Example: A student can enroll in multiple courses, and a course can have multiple students.

Participation Constraints

Participation constraints dictate whether the existence of an entity depends on it being related to another entity via the relationship. There are two types of participation constraints:

  1. Total Participation (Existence dependency): This specifies that every entity in the entity set must participate in at least one relationship instance. In other words, participation of every entity in the entity set is mandatory in the relationship. Example: Every employee must work for at least one department.
  2. Partial Participation: This specifies that not all entities in the entity set need to participate in a relationship instance. In other words, participation of entities in the entity set is optional in the relationship. Example: Some employees may not be assigned to any project.

https://whimsical.com/erd-97ANpLeUZp8k38ZFwcV2ak

SQL