Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2011
    Posts
    11

    VERY URGENT: Help with converting conceptual design to ERD

    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
    Attached Thumbnails Attached Thumbnails conceptual DB.bmp  
    Last edited by DeenaS; 08-20-11 at 15:03. Reason: spelling mistakes

  2. #2
    Join Date
    Mar 2011
    Posts
    11

    VERY URGENT: ERD for Subsidaries

    posted above
    Attached Thumbnails Attached Thumbnails conceptual DB.bmp  
    Last edited by DeenaS; 08-20-11 at 15:03.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Mar 2011
    Posts
    11

    Assignment details

    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...
    Last edited by DeenaS; 08-15-11 at 19:03.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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

  6. #6
    Join Date
    Mar 2011
    Posts
    11
    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...
    Attached Thumbnails Attached Thumbnails cons.JPG  
    Last edited by DeenaS; 08-16-11 at 12:37.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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.

  8. #8
    Join Date
    Mar 2011
    Posts
    11

    Ownership

    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

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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.

  10. #10
    Join Date
    Mar 2011
    Posts
    11

    Foreign Keys

    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.

  11. #11
    Join Date
    Mar 2011
    Posts
    11

    Forei gn Keys

    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.

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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)
    );

  13. #13
    Join Date
    Mar 2011
    Posts
    11

    Thank you

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •