Showing posts with label ER Model. Show all posts
Showing posts with label ER Model. Show all posts

Thursday, 12 April 2018

Define partial participation in ER model

Define partial participation in ER model





Participation constraints

Participation constraints deal with the participation of entities from an entity set in a relationship set. Participation constraint is sometimes referred as minimum cardinality constraint.

Partial participation

If only some entities of an entity set participate in a relationship, then this situation is called as partial participation. And total participation in ERD is shown with double line (link) between the entity set and the relationship set.

Example 1:

Let us consider a database to store data about entity sets Owner, Car, and Bike. Also, let us assume the following conditions;
  • An owner can have one or more cars or bikes or both
  • A car can have one or more owners
  • A bike can have one or more owners
The following ER diagram shows the database with three entity sets Owner, Car, Bike and two relationship sets Car_Owner and Bike_Owner for the given requirement.


 
In this ERD, it is not compulsory for an owner to have a car or bike. He may have a car or bike or both. So, there may a owner who has a car but not a bike. That means that owner entity is participated in Car_Owner relationship but not in Bike_Owner relationship. Hence, the participation of Owner entity set on either Car_Owner or Bike_Owner relationships are partial.

On the other hand, for a car entity or a bike entity to exist then there should be an owner related to them. That is, each bike to be stored in Bike relation must have at least one owner. The same is applicable for Car also. Hence, the participation of Car on Car_Owner and Bike on Bike_Owner relationships are Total.

Example 2:

Consider a database for storing student details, their extra-curricular activities and the course details. To model this database, we need three entity sets. If we have to link these entity sets, then we can link STUDENT with both EXTRACURRICULAR and COURSE. In this case, all students must have course details but not extra-curricular details as it is not mandatory to participate in any extra-curricular activities. Hence, the participation of EXTRACURRICULAR entity set in the relationship between STUDENT and itself is partial.  

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




 



What is partial participation in database?
Define partial participation in ER modelling
Example ERD for partial participation
Purpose of partial participation in ER model
Advantage of partial participation
participation constraints in ER model
difference between total and partial participations

Wednesday, 11 April 2018

Define total participation in ER modelling

Define total participation in ER modeling




Participation Constraint

Total participation is one type of participation constraints. Participation constraints deal with the participation of entities from an entity set in a relationship set. Participation constraint is sometimes referred as minimum cardinality constraint.

Total participation

If each entity of an entity set must participate in a relationship, then this situation is called as total participation. And total participation in ERD is shown with single line (link) between the entity set and the relationship set.

Example 1:

For example, let us consider a database to manipulate details about cars and its owners. Also, let us assume some conditions as follows;

  • An owner must have at least one car (or more cars)

  • A car must belong to at least one owner (or more owners)

For this database we have two entity sets namely Owner and Car. These two entity sets are linked through a relationship set Car_Owner which should be a many-to-many relationship (according to the conditions given). The ER diagram for this database looks like follows;



 


In this ERD, Car_Owner is many-to-many due to the fact that an owner can have one or more cars and a car is owned by one or more owners. In simpler terms, an owner must own at least one car and a car must have at least one owner. It leads to have one record for each owner-car combination in Car_Owner relationship.
In terms of participation constraints we can say that each Owner entity must participate in the relationship Car_Owner. Hence, the participation of Owner in Car_Owner is Total. Also, each Car entity must participate in the relationship Car_Owner. Hence, the participation of Car in Car_Owner is Total.

Example 2:

Let us consider another database that stores data about entity sets Owner, Car, and Bike. Also, let us assume the following conditions;

  • An owner can have one or more cars or bikes or both

  • A car can have one or more owners

  • A bike can have one or more owners

The following ER diagram shows the database with three entity sets Owner, Car, Bike and two relationship sets Car_Owner and Bike_Owner for the given requirement.



In this ERD, it is not compulsory for an owner to have a car or bike. He may have a car or bike or both. So, there may a owner who has a car but not a bike. That means that owner entity is participated in Car_Owner relationship but not in Bike_Owner relationship. Hence, the participation of Owner entity set on either Car_Owner or Bike_Owner relationships are not total. [It is partial participation]
But, for a car entity or a bike entity to exist then there should be an owner related to them. That is, each bike to be stored in Bike relation must have at least one owner. The same is applicable for Car also. Hence, the participation of Car on Car_Owner and Bike on Bike_Owner relationships are Total.
 

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




What is total participation in database?
Define total participation in ER modelling
Example ERD for total participation
Purpose of total participation in ER model
Advantage of total participation
participation constraints in ER model



Tuesday, 16 January 2018

Reduce the given ERD with many relationships to relationschema

Reduce ERD to relation schemas, Reduce specialization (EER component) to relation schemas, How to convert ER diagram to relation schemas, how to convert multiple relationship between two entity sets to relation schemas, ER model to relational model conversion

The ER diagram given below is for sports database. Use the given ER diagram to determine the schema.

 
Reduction rules:
Strong entity – all attributes of strong entity will be attributes of relation schema.
M-to-M relationship – separate table need to be created with the primary keys of all participating strong entity sets.
1-to-M relationship – primary key of one side entity is included as foreign key in many side entity set.

ER Component
Reduced into Relational Schemas
Strong Entity
            Students

            Teams

            Games

Students(SID, Name, Address)

Teams(TID, Name, Ranking)

Games(GID, Score, Date)
M-to-M Relationship
Binary                        Membership


Membership(SID, TID)
1-to-M Relationship
            isCaptain





            Host





            Guest

No separate schema. But the many side’s strong entity set is added with one side’s primary key. Hence teams becomes as follows. SID in teams schema is given a special name.
Teams(TID, Name, Ranking, Captain)

The relationship is to represent the host team for a game. Hence, we add tid (one side pk) in games (as foreign key) with a special name host or host_team as follows;
Games(GID, Score, Date, Host_Team)

The relationship is for mentioning the guest team for a game. Hence, we add tid (one side pk) in games (as foreign key) with a special name guest or guest_team as follows;
Games(GID, Score, Date, Host_Team, Guest_Team)

Final set of relations as follows;

Students (SID, Name, Address)

Teams (TID, Name, Ranking, Captain) – here, Captain is foreign key refers SID of Students relation.

Membership (SID, TID) – here, SID and TID are foreign keys refer SID of Students and TID of Teams relations respectively.

Games (GID, Score, Date, Host_Team, Guest_Team) – here, Host_Team is a foreign key refers TID of Teams and Guest_Team is another foreign key refers TID of Teams relations. Both refers same attribute but possibly with two different values through two different relationships.

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

 

















Important properties of two phase locking protocol and its variants

Important properties of two phase locking protocol and its variants Properties of     2PL Serializability  2PL ensures co...