Trending October 2023 # Property And Relationship Mapping With Example # Suggested November 2023 # Top 11 Popular

You are reading the article Property And Relationship Mapping With Example updated in October 2023 on the website Nhunghuounewzealand.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested November 2023 Property And Relationship Mapping With Example

Introduction to SQL Mapping

Hadoop, Data Science, Statistics & others

Property and Relationship mapping:

There are two types of mapping that need to be followed while mapping the SQL database to or from the objects.

The first type of mapping is property mapping which involves mapping the physical attributes and virtual attributes of the objects to one or more columns. It is not always necessary to map all the attributes of objects to their respective columns in tables.

For example, when we have the item name then that attribute should be stored in the table that is why we will create a new column in the table for storing the name of the item. But if there is an attribute for daily sale then that attribute need not be saved in any of the columns of SQL database as it is calculated at the application level. Also, there are situations in which we might need more than one column to store the value of a single attribute of the object in SQL. The property mapping becomes, even more, simpler when the attributes and columns both have the same basic data types. For example, attributes are in string format then columns can be defined in char format or both column and attributes can be in date format, and so on.

The other type of mapping is called relationship mapping where we will be describing how we can persist the relationship that exists between one or more objects and maintaining the same in SQL tables. This is mostly achieved by defining the association, aggregation, and composition relations between the tables and objects. The tables are mapped from the classes.

Example of SQL Mapping

Let us take a simple mapping example where we will take the class model and will see an equivalent physical data model in which each attribute of the class model will be mapped to a single column in the physical data model. Consider an example where we will be storing the class details of the stock at a shop. The following are the attributes that are being defined in the stock class.

Stock Class

purchaseDate: Date expiryDate: Date iGST : rupee sGST : rupee cGST: rupee cost: rupee MRP: rupee getTotal(): rupee dispatch() cancel() calculateTax()

Orders class (one to many mappings of stock with orders)

getBillAmount() quantity()

The equivalent mapping of the above two classes of stock and orders into the SQL Database can involve simple mapping with two tables named stock and orders that store the data in the below datatypes that is required to generate the functionality at the application level with the help of the classes written above.

Stock Table

itemId : int (Primary Key) purchaseDate: Date expiryDate: Date iGST : Float sGST : Float cGST: Float cost: Float MRP: Float latestUpdation :Timestamp

Orders table

orderId: int (Primary Key) itemId: int (Primary Key, Foreign Key) quantity: int latestUpdation: TimeStamp

Relationship Mapping

In one relationship, a maximum of one element is related to one element of the other class or table. For example, a single student can have only one rank in the class. In one to many relations, a maximum of one element is related to many elements of the other class or table. For example, a single student can opt-out for multiple sports and extra curriculum activities. In many to many relations, any number of elements are related to many elements of the other class or table. For example, a single student can be assigned multiple projects while a single project can be assigned to multiple students.

The relationships between the objects can also be classified depending on the directionality. There are two categories of relationship in this case which are unidirectional relations and bidirectional relationships. In case of unidirectional relations, one object is not aware about the other one but the other one is aware of the original one. For example, in case of student object is aware about the rank but rank object need not be necessarily aware about the student object. In case of bidirectional relations both the objects are aware about each other. For example, student object is aware about the teacher while teacher object also knows the students she/he is related to.

In UML diagrams the unidirectional relations are depicted by using the single arrow while bidirectional relations are denoted by a single line. The multiplicity of the objects is denoted by the 1…1, 1..* and *..* for one to one, one to many and many to many respectively. While mapping all these relationships to the tables the same method is followed unless there is any foreign key reference.

Conclusion

It is very easy to map the classes of Object-Oriented Programming of applications to the tables in SQL. We need to map the attributes of the class to the columns depending on the usage. There are situations in which the attributes are derived from the values of the other attributes also called as base attributes. In this case, only base attributes should be stored as the columns of the table while the derived one can be calculated at the application side and there is no need to store then in SQL while mapping. While mapping the relations between the objects we need to be careful only when the referential integrity constraint comes into picture like in case of foreign keys.