Describe cardinality ratios with example ERD

Define Cardinality ratio, Define Mapping cardinalities, What are the different types of relationships, Example ER diagrams for cardinality ratio

Cardinality ratios

In ER model, a relationship is an association among entities (records) of one or more entity sets. Cardinality ratio or mapping cardinalities is a concept that describes binary relationship set (a relationship that connects two entity sets) and its types. It is about the maximum number of entities of one entity set that are associated with the maximum number of entities of the other entity set.

For example, let us assume an entity set Department (to model information about departments) and courses entity set (to model the courses that are offered by a department). To establish a link (relationship) between these two entity sets we need to define the number of entities (records) of department that are associated with number of entities (records) of courses. As per our design requirement, if a Department can offer many courses and a course can only be offered by at most one department, then the relationship between department and courses is a one-to-many relationship from department to courses (or the cardinality ratio between department and courses is one-to-many).

See the example shown in the ERD below; [arrow head side - one side, without arrow – many side]

One-to-many relationship OFFERS from entity Department to Courses

The following are the types of relationship sets [cardinality ratios];

• One-to-one relationship

• One-to-many relationship

• Many-to-one relationship

• Many-to-many relationship

*************

Go to Important Keywords/Terms in DBMS page

Go to Advanced DBMS concepts page

Go to Some facts about relational tables page

Go to Reducing (converting) an ER diagram to schemas page

Composite attribute in ER model

What is composite attribute in ER model, Define composite attribute, Examples of composite attribute in ER diagram, Explain composite attribute with example

Composite attribute

Composite attribute is one of the types of attribute used in ER modeling. There are values that are to be stored in an attribute can be further divided into meaningful values (sub-values). For example, Name may contain first name, middle initial and last name. Not all the database applications need to store the name under 3 sub-parts. Some applications like Supermarket database, may store the name of their customers as a single value, while some other applications like University database system may prefer two or three parts of the names separately. The attribute that stores such values is called as composite attribute.

"Composite attribute is an attribute where the values of that attribute can be further subdivided into meaningful sub-parts."

Typical examples for composite attribute are;

• Name – may be stored as first name, last name, middle initial

• Address – may be stored as door_no, street_name, area_name, city, pincode etc.

• DOB – may be stored as date, month and year and so on.

The composite attributes are represented as follows in ER diagram;

EName and Address composite attributes

 Above figure shows the composite attributes EName with component attributes First_Name, Mid_initial, Last_Name and Address with component attributes Door_no, Str_name, city.

In some cases, a component attribute of a composite attribute may be composite. For example, consider the ER diagram given below; in this diagram, Address is a composite attribute with components Str_Addr, City, Pincode and Str_Addr is another composite attribute with components Door_no, Str_Name.

Entity set Employee with Composite attribute Address. Str_Addr is a composite attribute and a component attribute of Address

 

 ***********

Go to Important Keywords/Terms in DBMS page

Go to Advanced DBMS concepts page

Go to Some facts about relational tables page

what is derived attribute in dbms

What is derived attribute in DBMS? How would we create table for a derived attribute? How the values for derived attributes are inserted? Define derived attribute, Derived attribute detailed examples

Derived attributes

In ER model, derived attribute is a type of attribute where the value for that attribute will be derived from one or more of the other attributes of the same entity set.

Consider the following entity set for example;

Employee Entity set with derived attribute Experience

In this entity set Employee, Experience is a derived attribute (represented as dashed ellipse). That means the value for Experience will be derived from one or more of the other attributes, in our example, the other attribute is DOJoin (Date of join) attribute.

What does that mean? How would we include the derived attribute in a table? Do we need not enter value for Experience attribute while we insert records into the Employee table? Or, the value will be automatically calculated and stored?

Not exactly. The concept of derived attribute is not about storing values, but about calculating the values. For example, consider the following record of Employee table;

(‘E101’, ‘Ramkumar’, ’10-Feb-2001’, 0)

This record shows that the employee Ramkumar joined on 10^th of February, 2001. At the time of record insertion, it is zero years of experience. What about the value of the experience on ’10-Feb-2004’? It is about 3 years. It clearly shows that the value is not fixed and it is variable whenever the DOJoin is adjusted.

So the solution is, we are not creating our table with derived attributes in it. Instead, the values can be calculated at the time of retrieval and shown to the user. Hence, our record will look like as follows;

(‘E101’, ‘Ramkumar’, ’10-Feb-2001’)

One solution to calculate the experience value is by creating database views. See the example below;

CREATE VIEW v as SELECT Emp_No, EName, DOJoin, MONTHS_BETWEEN(sysdate, DOJoin)/12 as Exp;

The statement will create a view named v. The idea behind the concept view is, every time you access something through the view v, the query is freshly executed and what you get as the result is the correct experience.

Look at the example data below;

The result for “SELECT * FROM Employee;” is as follows;

Emp_No	EName	DOJoin
E101	Manoj	’10-Mar-2001’
E102	Steve	’20-Mar-2005’
E106	Mary	’21-Feb-2008’

Table: Employee (the records are physically stored)

The result for “SELECT * FROM v;” is as follows; (on a date ’25-Mar-2009’)

Emp_No	EName	DOJoin	Exp
E101	Manoj	’10-Mar-2001’	8
E102	Steve	’20-Mar-2005’	4
E106	Mary	’21-Feb-2008’	1

Table: Result of the above query (the records are generated and not physically stored)

The values for Exp column is derived from the DOJoin attribute against the date at which the query is executed.

******************

Go to Important Keywords/Terms in DBMS page

Go to Advanced DBMS concepts page