Question

For these two E-R diagrams below transform the diagram to a relational schema that shows referential integrity constraints (see Figure 4-5 for an example of such a schema).

Figure 4-5:

A.

B.

Answer 1

Looking at the first problem, I imagine that you will need a table to represent the relationship attribute of completion date.  Given the examples, here's what I've come up with for number A:

Here's the MySQL table creation code:

create table `EMPLOYEE`
(
        `Employee ID` int(10) unsigned not null auto_increment,
        `Employee Name` varchar(128) not null,
        #Other optional data goes here.
        `Birth Date` datetime not null,
        primary key (`Employee ID`),
        unique key `Employee ID` (`Employee ID`)
) engine=InnoDB default charset=utf8;

create table `COURSE`
(
        `Course ID` int(10) unsigned not null auto_increment,
        `Course Title` varchar(128) not null,
        `Topic` text default null,
        primary key (`Course ID`),
        unique key `Course ID` (`Course ID`)
) engine=InnoDB default charset=utf8;

create table `COMPLETION` # This is your relationship attribute
(
        `Course ID` int(10) unsigned not null,
        `Employee ID` int(10) unsigned not null,
        `Date Completed` datetime not null,
        primary key (`Course ID`, `Employee ID`),
        unique key `Course ID and Employee ID` (`Course ID`, `Employee ID`),
        constraint `Course ID Foreign Key`
                foreign key (`Course ID`)
                references `COURSE` (`Course ID`),
        constraint `Employee ID Foreign Key`
                foreign key (`Employee ID`)
                references `EMPLOYEE` (`Employee ID`)
) engine=InnoDB default charset=utf8;

Note that even though we have two one-to-many relationships, this has the net effect of creating a many-to-many relationship between the COURSE and EMPLOYEE tables.  Note also that the relationships formed by the foreign keys in COMPLETION are identifying relationships.

Next, for problem B, you will see that the situation is quite similar:

And again, here's the MySQL code:

create table `EMPLOYEE`
(
        `Employee ID` int(10) unsigned not null auto_increment,
        `Employee Name` varchar(128) not null,
        #Other optional data goes here.
        `Birth Date` datetime not null,
        primary key (`Employee ID`),
        unique key `Employee ID` (`Employee ID`)
) engine=InnoDB default charset=utf8;

create table `COURSE`
(
        `Course ID` int(10) unsigned not null auto_increment,
        `Course Title` varchar(128) not null,
        `Topic` text default null,
        primary key (`Course ID`),
        unique key `Course ID` (`Course ID`)
) engine=InnoDB default charset=utf8;

create table `CERTIFICATE`
(
        `Certificate Number` int(10) unsigned not null auto_increment,
        `Course ID` int(10) unsigned not null,
        `Employee ID` int(10) unsigned not null,
        `Date Completed` datetime not null,
        primary key (`Certificate Number`),
        unique key `Certificate Number` (`Certificate Number`),
        constraint `Course ID Foreign Key`
                foreign key (`Course ID`)
                references `COURSE` (`Course ID`),
        constraint `Employee ID Foreign Key`
                foreign key (`Employee ID`)
                references `EMPLOYEE` (`Employee ID`)
) engine=InnoDB default charset=utf8; 

Note that, unlike the relationships formed by the foreign keys in answer A, the relationships formed by the foreign keys in CERTIFICATE are non-identifying relationships.

Please also note that the answer I am providing for you is an example of how I would implement the exercises in question. Your instructor may be looking for something fundamentally different. Also, I am making an educated guess as to what the various and sundry underlinings, boldfacings, and parenthetical bits mean.  Hopefully, this will be of help to you.  I encourage that you spend some time studying the concepts of data normalization.