Notes and Study Materials

Completeness Constraint

 

 

The completeness constraint specifies whether each entity supertype occurrence must also be a member of at least one subtype. The completeness constraint can be partial or total.

Partial completeness (symbolized by a circle over a single line) means that not every supertype occurrence is a member of a subtype; that is, there may be some supertype occurrences that are not members of any subtype.

Add a comment

Specialization and Generalization:

 

 

You can use various approaches to develop entity supertypes and subtypes. For example, you can first identify a regular entity, and then identify all entity subtypes based on their distinguishing characteristics. You can also start by identifying multiple entity types and then later extract the common characteristics of those entities to create a higher-level supertype entity.

Specialization is the top-down process of identifying lower-level, more specific entity subtypes from a higher-level entity supertype. Specialization is based on grouping unique characteristics and relationships of the subtypes. In the aviation example, you used specialization to identify multiple entity subtypes from the original employee supertype.

Add a comment

Entity Clustering

 

 

An entity cluster is a “virtual” entity type used to represent multiple entities and relationships in the ERD. An entity cluster is formed by combining multiple interrelated entities into a single abstract entity object. An entity cluster is considered “virtual” or “abstract” in the sense that it is not actually an entity in the final ERD. Instead, it is a temporary entity used to represent multiple entities and relationships, with the purpose of simplifying the ERD and thus enhancing its readability.

The following Figure illustrates the use of entity clusters which contains two clusters:

Add a comment

Primary Key and Its Key Characteristics

 

 

Arguably, the most important characteristic of an entity is its primary key (a single attribute or some combination of attributes), which uniquely identifies each entity instance. The primary key’s function is to guarantee entity integrity. Furthermore, primary keys and foreign keys work together to implement relationships in the relational model.

Natural Keys and Primary Keys:

Add a comment

 Surrogate Primary Keys

 

 

 There are some instances when a primary key doesn’t exist in the real world or when the existing natural key might not be a suitable primary key. In these cases, it is standard practice to create a surrogate key. A surrogate key is a primary key created by the database designer to simplify the identification of entity instances. The surrogate key has no meaning in the user’s environment—it exists only to distinguish one entity instance from another. One practical advantage of a surrogate key is that since it has no intrinsic meaning, values for it can be generated by the DBMS to ensure that unique values are always provided.

Add a comment

Normalization and Its Need

 

 

Normalization is a process for evaluating and correcting table structures to minimize data redundancies, thereby reducing the likelihood of data anomalies. The normalization process involves assigning attributes to tables based on the concept of determination.

Normalization works through a series of stages called normal forms. The first three stages are described as first normal form (1NF), second normal form (2NF), and third normal form (3NF). From a structural point of view, 2NF is better than 1NF, and 3NF is better than 2NF. For most purposes in business database design, 3NF is as high as you need to go in the normalization process. Properly designed 3NF structures also meet the requirements of 4th normal form (4NF).

Add a comment

Functional Dependence in DBMS

The attribute B is fully functionally dependent on the attribute A if each value of A determines one and only one value of B.


Example: PROJ_NUM -- -->PROJ_NAME
(read as “PROJ_NUM functionally determines PROJ_NAME”)

In this case, the attribute PROJ_NUM is known as the “determinant” attribute, and the attribute PROJ_NAME is known as the “dependent” attribute.   

Add a comment

First Normal Form (1NF) in DBMS

 

 

The First Normal Form (1NF) describes the tabular format in which:

 

• All of the key attributes are defined.

• There are no repeating groups in the table. In other words, each row/column intersection contains one and only one value, not a set of values.

All attributes are dependent on the primary key.

Add a comment