I would use (title,prod_date) as the primary key, since movies are not uniquely identified by their title alone (remakes, for example). In my opinion, the first diagram is preferable, the sub-attribute approach in the second diagram seems a bit convoluted to me.
In Postgres, there are two ways to circumvent the chicken-and-egg problem of inserting into tables with circular references.
a) Defer one of the constraints, either declaring it as DEFERRABLE INITIALLY DEFERRED when created or as DEFERRABLE as created and then defer it when needed.
For an example of using deferrable constraints, see my answer in this question:
Implementation of a many-to-many relationship with total participation constraints in SQL.
b) Use a single statement to insert into the two (or more) tables, using modifying CTEs. Example for your case:
with
insert_emp as
( insert into employee
(Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
values
('James', 'E', 'Borg', '888665555', '1937-11-10', 'asda', 'M', 55000, NULL, 1)
returning
*
),
insert_dep
( insert into sp0090.department
(Dname, Dnumber, Mgr_ssn, Mgr_start_date)
values
('Headquarter', 1, '888665555', '1981-06-19')
returning
*
)
select *
from insert_emp as e
full join insert_dep as d
on e.dno = d.dnumber
and e.ssn = d.mgr_ssn ;
Best Answer
It's not an either / or situation. The answer for your particular system may exists as a combination of these two options. Let me explain.
If the only attribute in common between the Programmer, Engineer and Admin tables are the Emp# then I'd likely implement them as separate tables. Each would have Emp# as the primary key. I would not implement the Employee table at all since it added no additional value to the run-time system.
If the three sub-types had many columns in common, say Name, HireDate, Department .. HolidayBalance, ContactNumber etc., and only a few sub-type specific values (as you've annotated on your diagram) you could put all columns in Employee and make the inapplicable ones (e.g. a Programmer's EType) NULL. A view can be defined for each sub-type if excluding the NULLs becomes tedious.
Likely the truth lies somewhere between these two - there are enough specific attributes in each sub-type to warrant its own table, but enough common ones to justify an Employee table, too. In this case I'd make Emp# the primary key of Employee and also each sub-type would have the primary key of Emp#, too. Further I'd define Programmer.Emp# to be a foreign key referencing Employee.Emp#, and the same for the other sub-types. In this way uniqueness is maintained within each sub-type and so is integrity between the sub-type and super-type. In other words, I'd make a one-to-one relationship between Employee and each sub-type.
I would not implement a surrogate key in each of the sub-types (for example ProgrammerId) as it adds nothing that Emp# can't already achieve.
Note that it is difficult to declaratively ensure mutual exclusion between the sub-types. This will be enforced in the application or through triggers.