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

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







Tuesday, 18 July 2017

Reduce the ER diagram to relation table solved exercise

How to reduce an ER diagram to tables? Reduction of ERD to relation schema, Convert Entity Relationship diagram to set of tables, ERD to relation schema examples, mapping ER diagram to relational tables


Reduce (convert) the following ER diagram to relational schema




ER components
Given component
Result
Strong Entity Set
Rule: Strong entity set can be directly converted into table.

(a) STUDENT
(b) SUBJECT
(c) CLASS
(a) STUDENT (Student_ID, Student_Name, DOB, Address)
 (b) SUBJECT (Subject_ID, Subject_Name, Teacher)
(C) CLASS (Class_ID, Class_Name)
Derived attribute
Rule: No need to create a column in the table for derived attribute.
Age in STUDENT table
No changes
Composite attribute
Rule: Replace the composite attribute with its component attributes.
Address in STUDENT table
STUDENT (Student_ID, Student_Name, DOB, Door, Street, City, Pin)
1-1, 1-n, and n-1 Relationships
Rule: Include the primary key of one side entity set as the foreign key of other side entity set.

Attends (1-1 from STUDENT to CLASS)
Studies (1-n from STUDENT to SUBJECT)
CLASS (Class_ID, Class_Name, Student_ID)
SUBJECT (Subject_ID, Subject_Name, Teacher, Student_ID)
Descriptive attribute
Rule: An attribute that is part of a relationship is descriptive. Include the descriptive attributes to 1 side as shown above.
DateOfJoin, Hours# of Attends relationship.
CLASS (Class_ID, Class_Name, Student_ID, DateOfJoin, Hours#)
Weak entity set
Rule: Weak entity set is totally participated (existence dependent) on the strong entity set. Include the primary key of strong entity set into the weak entity set as foreign key.
(d) SECTION
SECTION (Section_ID, Section_Name, Class_ID)
Weak relationship
Rule: No need to create as a table. If created, then the table is redundant.
Has
No changes

Final set of relation schemas: (Primary keys are underlined)

STUDENT (Student_ID, Student_Name, DOB, Door, Street, City, Pin)

CLASS (Class_ID, Class_Name, Student_ID, DateOfJoin, Hours#)
Student_ID is the foreign key refers STUDENT table

SUBJECT (Subject_ID, Subject_Name, Teacher, Student_ID)
Student_ID is the foreign key refers STUDENT table

SECTION (Section_ID, Class_ID, Section_Name)
Class_ID is the foreign key refers CLASS table

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











SQL exercises for beginners one

SQL Exercises for Beginners / Simple SQL Exercises with Answers / Solved SQL exercises / SQL solved exercises with simple conditions / So...