Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2006
    Posts
    41

    Entity-relationship diagram and logical design step??

    hi,

    I have to perform "the logical design step" on some ER diagrams. What this basically means is translate them into SQL.

    I've never done this before and need a little guidance.

    I'll try my best to "draw" the ER diagram. It looks a little something like this:

    employees<---->(works on)<---->projects

    employee<---->(works in)<---->departments

    departments<---->(located in)<--->building

    etc...

    Am I supposed to create "dummy" tables with attributes to identify primary keys, foreign keys etc in order to write the table declarations in SQL.

    For example. Employee would have the fields emp_num (primary key), first_name, surname, department(foreign key).

    or is there another way.

    Thanks

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    IMO, converting an ERD into SQL is NOT a part of the logical design - it is a part of the PHYSICAL database design.

    By the time you begin creating SQL, all the fields of your table should be listed in the ERD. So, there should be no need of creating a dummy table first.

    Now, you may need to create the tables first, then alter the tables to add foreign key relationships. Or, carefully look at the order in which tables are to be created, to ensure that all tables required for foreign key relationships are created first.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Mar 2006
    Posts
    41
    Would the SQL for the entitiy-relationship diagram in my post look something like this:

    CREATE TABLE employees(

    emp_id INT PRIMARY KEY
    first_name VARCHAR(15) NOT NULL
    last_name VARCHAR(20) NOT NULL
    department_id INT NOT NULL REFERENCES department(dep_id)
    ...
    ...
    )

    CREATE TABLE department(
    dep_id INT PRIMARY KEY
    dep_staff_size INT CHECK(dep_staff_size >= 0)
    ..
    ..
    )

    and so on.

    Or am I doing something wrong.

    cheers.

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by uraknai
    Would the SQL for the entitiy-relationship diagram in my post look something like this:

    CREATE TABLE employees(

    emp_id INT PRIMARY KEY
    first_name VARCHAR(15) NOT NULL
    last_name VARCHAR(20) NOT NULL
    department_id INT NOT NULL REFERENCES department(dep_id)
    ...
    ...
    )

    CREATE TABLE department(
    dep_id INT PRIMARY KEY
    dep_staff_size INT CHECK(dep_staff_size >= 0)
    ..
    ..
    )

    and so on.

    Or am I doing something wrong.

    cheers.
    Yes, this would work. Except that you should FIRST create the department table, then the employees table, as if you try to create the employee table first, you would get an error when you try to reference the non-existing (as of yet) department table.

    ALso, you might want to consider the possibility of having your surrogate keys (department ID and Employee ID) be automatically generated. In SWL Server, you would declare them as Identity fields, in Access as Autonumbers, in Oracle or PostgreSQL as Sequences (Or, in PostgreSQL as a Serial type, which is a shortcut notation for generating Sequences)
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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