Sql

Question

How can apply inhettanse in SQL
When we have an EER model how we can convert it to relational modal???

Answer 1

It is vary useful to me
Thanks Mister Josh Greig ,🙏🙏🙏
But I am a little confused about
How we can insert data in table I mean data jast insert in child table or insert single single in tow table ?

Answer 2

Thanks again Mister Josh Greig I think I can catch something in it 🙏

Answer 3

There are a few different ways to represent inheritance in an SQL database schema.

Say you have the following classes:
- class User
- class AdminUser: extends User.

class User has properties:
- id
- username

class AdminUser has properties:
- id - inherited from User
- username - inherited from User
- job_title

I used an object relational mapping(ORM) called SQLAlchemy that would represent the above class model like this(Wish I could share SQL create table statements but Sololearn blocks it for some strange reason):

table user (
id int primary key auto_increment,
username varchar,
type varchar
)

table admin_user (
id int primary key auto_increment,
job_title varchar
)

The data for an AdminUser could be represented like this:
insert into user(id, username) values(1, 'admin1', 'admin_user');
insert into admin_user(id, job_title) values(1, 'Principal');

Your application could then work with that.
Any references to User as type User in your class model could be represented in SQL by a foreign key to the user table. Properties like job_title from the subclass adminUser could then be queried and updated too since the parent table(user) identifies the name of the lowest level table.

There is more detailed discussion on modeling inheritance here:
https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database

For converting an ER diagram into a relational model, check:
https://www.learndb.com/databases/how-to-convert-er-diagram-to-relational-database
Listing properties out is fairly simple.
Converting the 1:N or N:1 relationships to foreign key references is fairly straightforward too.
The N:M relationships get more tricky because you need to add extra tables called bridge tables.

Answer 4

Ghezal wrote, "How we can insert data in table I mean data jast insert in child table or insert single single in tow table ?"

Response:
I gave an insert example for the above inheritance representation. It requires inserting 1 record in user and another record in admin_user. Both are needed to correctly represent a single instance of the AdminUser class. I'm not sure what you're confused about exactly so I'll just say more about it.

If you insert in one without the other, no database constraint would fail but the application's assumptions about the database structure will be incorrect which can lead to buggy behaviour in an application that uses your database. If you inserted to admin_user but not user, how would the application know what username the AdminUser has? Not knowing that would make it impossible for the application to convert the database state into a completely initialized AdminUser(which includes username).

Here are the insert statements again:
insert into user(id, username) values(1, 'admin1', 'admin_user');
insert into admin_user(id, job_title) values(1, 'Principal');

You can't insert into 2 tables with 1 statement. You could wrap the 2 statements in a transaction if you want it to be atomic, though.

There are other ways to model OOP style inheritance in an SQL database but the method I'm describing is most familiar to me because I used SQLAlchemy( https://www.sqlalchemy.org/ ) a lot.