VERY URGENT: ERD for Subsidaries

DeenaS · #1 (**permalink**) 08-15-11, 17:22

I need help with my ERD.
I have conceptual DB...

SBU(sbu_id, sbu_name, Hq_country)
Subsdary(Sub_id, sbu_id, subsiary_name, country_id, hq_country_id, ownership_type) – Need for a check constraint on ownership_type whose values are
Restricted to two: either “Full_ownrship” or “Partial_ownrship” only.
Country(country_id, Country_name)
Market(market info, mkt_name)
F_Ownrship(sub_id, corporation_name, date_acquired) – Composite PK
Part-Ownrship(sub_id, corporation_name, share_percent, date_acquired)
SBU_Part_Ownrship(Sbu_Id, Share_percent, date_acquired)

I need to build this into system, and not sure what the ERD should look like..
Do I introduce new entities..?

any ideas? help very much welcome

Thank you
DeenaS

DeenaS · #2 (**permalink**) 08-15-11, 17:32

posted above

Pat Phelan · #3 (**permalink**) 08-15-11, 17:42

Without access to the actual assignment, we would only be guessing. Without knowing exactly what the teacher expects you to demonstrate that you've learned, we have little or no chance of using the techniques that they expect to see.

-PatP

DeenaS · #4 (**permalink**) 08-15-11, 17:53

Hi Pat

there is no information, apart from entities and attributes given in my previous post.
The tutor just said: convert Conceptual model into ERD
Once converted, implement the system i.e. create tables in My sql, and insert data..
Then build forms in Oracle ...:-(

I;m stuck on step one..
how to resolve many to many relationships...

especially troubled with constraint: ownership type..
I dont get it:-(

This is the exact instructions:
Figure 1 shows a conceptual database design for Dalas company. A proposed logical model from this design is also given. You are required to implement this model (ERD /Tables/ and forms) and discuss the key aspects of your understanding of the design and your suggested implementation techniques.

As you can notice - it is very umbigous...

andrewst · #5 (**permalink**) 08-16-11, 10:16

If you mean that you need to produce a diagram like an ERD that has an entity for each table that will need to be created, then you will need to introduce new tables to "resolve" your many-to-many relationships such as Subsidiary >--< Market. For this example you might add an entity called Subsidiary Market with relationships like:

Subsiduary --< Subsidiary Market >-- Market

DeenaS · #6 (**permalink**) 08-16-11, 11:27

Thank you Tony, i did /action as advised.....
Can I ask for your help on constraint..not sure how to deal with ownership type..is it correct they way I have done it..? Please see attached..

Not sure when we have constraints- can i have the same attribute ( sub_id) as primary key in both children tables?
Or do you think ptrimary key should be the actual contraint ( ownership_type)?

SOS..help...

andrewst · #7 (**permalink**) 08-17-11, 06:01

If your model means that an Ownership is always either a Full Ownership or a Part Ownership, then you don't need a new surrogate "sub_id" column in these tables, you can just use the ownership_id as the primary key.

DeenaS · #8 (**permalink**) 08-17-11, 06:42

Thank you Tony

Shall I assume the SQL for creating the tables will be as follows:

CREATE TABLE OWNERSHIP
( (ownership_id CHAR(10) PRIMARY KEY
,ownership_type VARCHAR2(20)
);
CREATE TABLE PART_OWNERSHIP
( (ownership_id CHAR(10) PRIMARY KEY
,corporation_name VARCHAR2(40)
,date_aquired DATETIME
,share_persent VARCHAR2(30)
);

CREATE TABLE FULL_OWNERSHIP
( (ownership_id CHAR(10) PRIMARY KEY
,corporation_name VARCHAR2(40)
,date_aquired DATETIME
);

Will it work, having ownership_id as primary key to 2 child tables

Also - who do I say that ownership_type is constraint in entity OWNERSHIP,

( can I just type constraint???)
How do I restrict the values to say: CONSTRAINT: full Ownership and Part Ownership

SOS..help

Deena

andrewst · #9 (**permalink**) 08-17-11, 07:12

You should add foreign keys in the child tables as shown below. I have also added the constraint on ownership_type.

Code:

CREATE TABLE OWNERSHIP
(ownership_id CHAR(10) PRIMARY KEY
,ownership_type VARCHAR2(20)
,constraint ownership_type_chk
   check (ownership_type in ('Full Ownership','Part Ownership'))
);
CREATE TABLE PART_OWNERSHIP
(ownership_id CHAR(10) PRIMARY KEY
,corporation_name VARCHAR2(40)
,date_aquired DATETIME
,share_persent VARCHAR2(30)
,constraint part_ownership_fk (ownership_id) references ownership (ownership_id)
);

CREATE TABLE FULL_OWNERSHIP
(ownership_id CHAR(10) PRIMARY KEY
,corporation_name VARCHAR2(40)
,date_aquired DATETIME
,constraint part_ownership_fk (ownership_id) references ownership (ownership_id)
);

I hope I have the syntax right for you - this will certainly work in Oracle.

DeenaS · #10 (**permalink**) 08-17-11, 14:23

Thank you Tony. Really helpfull indeed!

This was real eye opener- I dont think I have ever heard it during the past 3 months on my lessons..

Can i ask you if we can have more than one foreign key in a table?
I have table SUB and table MARKET
to resolve many to many relationship, as advised I created SUBMARKET table..

CREATE TABLE SUBMARKET
(submarket_id CHAR(10)
,sub_id CHAR(10)
,market_id CHAR(10)
,start_date DATETIME
,PRIMARY KEY (submarket_id)
,FOREIGN KEY (market_id) REFERENCES MARKET(market_id)
,FOREIGN KEY (sub_id) REFERENCES SUB(sub_id)
);

is that correct way of showing that I have foreign keys?
Can we actually have more than one foreign key in a table?
Can you create a table wonly with primary key and not showing the foreign keys? ( is that allowed inMy sql/Oracle)?

SOS>...novice..struggling...help..

Warm regards,

Deena.

DeenaS · #11 (**permalink**) 08-17-11, 14:28

Thank you Tony. Really helpfull indeed!

This was real eye opener- I dont think I have ever heard it during the past 3 months on my lessons..

Can i ask you if we can have more than one foreign key in a table?
I have table SUB and table MARKET
to resolve many to many relationship, as advised I created SUBMARKET table..

CREATE TABLE SUBMARKET
(submarket_id CHAR(10)
,sub_id CHAR(10)
,market_id CHAR(10)
,start_date DATETIME
,PRIMARY KEY (submarket_id)
,FOREIGN KEY (market_id) REFERENCES MARKET(market_id)
,FOREIGN KEY (sub_id) REFERENCES SUB(sub_id)
);

is that correct way of showing that I have foreign keys?
Can we actually have more than one foreign key in a table?
Can you create a table wonly with primary key and not showing the foreign keys? ( is that allowed inMy sql/Oracle)?

SOS>...novice..struggling...help..

Warm regards,

Deena.

andrewst · #12 (**permalink**) 08-17-11, 15:30

Yes, you can have more than one foreign key in a table, and the syntax you used is correct.
You can have a table without foreign keys, but it is best practice to define foreign keys where they exist (as here): otherwise you could end up with nonsense in your database (e.g. SUBMARKETs that "belong" to MARKETs that don't actually exist).

This table doesn't really need its own surrogate primary key column, since presumably there can only be one row per sub_id/market_id pair? In which case you can do this:

Code:

CREATE TABLE SUBMARKET
(sub_id CHAR(10)
,market_id CHAR(10)
,start_date DATETIME
,PRIMARY KEY (sub_id, market_id)
,FOREIGN KEY (market_id) REFERENCES MARKET(market_id)
,FOREIGN KEY (sub_id) REFERENCES SUB(sub_id)
);

If for some reason you decided you did want a separate surrogate key, you should still enforce uniqueness of market_id/sub_id pairs like this:

Code:

CREATE TABLE SUBMARKET
(submarket_id CHAR(10)
,sub_id CHAR(10)
,market_id CHAR(10)
,start_date DATETIME
,PRIMARY KEY (submarket_id)
,FOREIGN KEY (market_id) REFERENCES MARKET(market_id)
,FOREIGN KEY (sub_id) REFERENCES SUB(sub_id)
,UNIQUE(sub_id,market_id)
);

DeenaS · #13 (**permalink**) 08-20-11, 14:01

HiTony

Thank you for your advice and putting me on the right direction:-)

As I'm novice, I have no doubt, that will be coming back with some more questions.
Once again , many thanks, your answers were real eye opener..
regards
Deena.

Internet Services

Web Development

Digital Marketing

Consumer Tech