Search This Blog

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

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.

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

 

















Sunday, 14 January 2018

Convert ER diagram to relation schemas specialization included

Reduce ERD to relation schemas, Reduce specialization (EER component) to relation schemas, How to convert ER diagram to relation schemas, how to convert specialization to relation schemas, ER model to relational model conversion


Convert the following ERD to relational 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.
Specialization – super class is modeled as strong entity set. Sub-classes are included with the super class’s primary keys along with their own attributes as in 1-to-M relationship.

ER Component
Reduced into Relational Schemas
Strong Entity
            Driver

            Truck

            Trip

            Shipment


Driver(ID, Name, PhoneNo)

Truck(LicNo, maxVol, maxWt)

Trip(tripNo)

Shipment(ShipNo, Vol, Weight)

M-to-M Relationship
Binary           - Journey
           
Ternary         -Between

Journey(ID, LicNo, tripNo)

Stoppoint stores either FROM address or TO address. Hence, we have renamed Address attribute as follows;
SBetween(ShipNo, From_Address, Pickup_time, To_Address, Dropoff_time)
1-to-M Relationship
            Carries

No separate schema. But the many side strong entity set is added with one side’s primary key.
Shipment(ShipNo, Vol, Weight, tripNo)
Specialization
            StopPoint
            (super class entity)
            Warehouse
            (sub-class entity)
            shopNpay
            (sub-class entity)


StopPoint(Address)

Warehouse(Address, port)

shopNpay(Address, openHrs)
Final set of relations are as follows;

Driver(ID, Name, PhoneNo)

Truck(LicNo, maxVol, maxWt)

Trip(tripNo)

Shipment(ShipNo, Vol, Weight, tripNo) – tripNo is foreign key referencing Trip.

Journey(ID, LicNo, tripNo) – ID, LicNo, and tripNo all are foreign keys referencing Driver, Truck, and Trip relations respectively.

SBetween(ShipNo, From_Address, Pickup_time, To_Address, Dropoff_time) – From_Address, To_Address are foreign keys referencing StopPoint’s Address attribute. Pickup_time and Dropoff_time are descriptive attributes of the ternary relationship SBetween.

StopPoint(Address)

Warehouse(Address, port) – Address is the foreign key referencing the super class’s primary key.

shopNpay(Address, openHrs) - Address is the foreign key referencing the super class’s primary key.

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













Thursday, 7 September 2017

Descriptive attribute or Relationship attribute in Entity Relationship Model

Descriptive attribute in ER model, Relationship attribute in Entity Relationship Model, Descriptive attribute in ER diagram, Purpose of descriptive attribute, Descriptive attribute example


Descriptive attribute in ER model


The attribute(s) used for describing the relationship is called descriptive attributes, also referred as relationship attributes. They are actually used for storing information about the relationship. A relationship can have zero or more attributes.
Let us consider an example ER diagram given below;


Here, the entity sets CUSTOMER and ACCOUNT are connected using a relationship set ACCESS. A customer can have many accounts and in turn an account may belong to one or more customers. Hence, it is a many-to-many relationship.
A customer accesses his/her account to withdraw or transfer funds. And, we would like to store the information LAD (Last Accessed Date) of all accounts to keep track of recent manipulation of any account. We have included LAD as an attribute of relationship set. LAD is called as descriptive attribute/relationship attribute associated with the many-to-many relationship ACCESS.

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

Go to Some keywords and Definitions in DBMS page

Go to Entity Relationship model page

Go to Reduce/Convert ER diagram to relation page







Followers