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

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.

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)