Here is the question below! We will create a database according to these criterias:

You have just been hired as a consultant for a big airplane manufacturer. They want you to completely redesign their database system. Talking with the people in the company, you get the following information.
1-The database contains information about employees, factories and parts.
2-Each employee has a social security number (SSN), name and salary. An employee is uniquely identified by his/her SSN.
3-Each factory has an id, a name and a budget. The id uniquely identifies a factory.
4-Each part has an id and a name. The id uniquely identifies a part.
5-Each employee reports to at most one other employee.
6-Each employee works in at least one factory.
7-Each part is manufactured in exactly one factory.

Here is the answer of the question:

create table report(
report_ssn int not null,
primary key (report_ssn))

create table employee (
ssn int not null,
e_name varchar(20) not null,
salary int not null,
report_ssn int not null,
fac_id int not null,
primary key (ssn),
foreign key (report_ssn)references report(report_ssn))

create table factory (
fac_id int not null,
fac_name varchar(20) not null,
budget int not null,
ssn int not null,
primary key (fac_id),
foreign key (ssn)references employee(ssn))

create table part(
part_id int not null,
part_name varchar(10) not null,
fac_id int not null,
primary key (part_id,fac_id),
foreign key (fac_id) references factory (fac_id))

alter table employee add
foreign key(fac_id) references factory(fac_id)

Try to decode the code above firstly! I will explain the code at the next article (=

Hint: Since i could not captured the fac_id foreign key into the employee table when i am creating i added the foreign key by alter table.I could not captured that foreign key first since the table factory and the table employee include each other's primary key as foreign key.So i needed to create factory table first and than add the foreign key by alter table.