Question

A company database needs to store information about employees (identified by ssn, with salary and phone as attributes), departments (identified by dno, with dname and budget as attributes), and children of employees (with name, age, and relationship to the employee as attributes). Employees work in departments; each department is managed by an employee; a child must be identified uniquely by name when the parent (who is an employee; assume that only one parent works for the company) is known. We are not interested in information about a child once the parent leaves the company.

Please provide me SQL statements to create the corresponding relations and capture as many of the constraints as possible.

Answer 1

According to the problem statement the ER diagram of the database will be as follows:-

From the question it is clear that there will be 3 entities which are represented by rectangular box and each entities will have 3 attributes which are represented by the oval shape, the relationship are represented by diamond box in the ER diagram and followings are the relationship between entities:-

• Employee works for department (N employees works for the 1 department)
• Employee manages the department (only 1 employee manages the department)
• Employee has the children (1 employee has one or more children)

Now we convert the above ER diagram into the database using the sql syntax as follows:-

(1.) Creating database Company :-

create database Company;

use Company;

(2.) Creating the tables :-

All the entities become the tables and the attributes become the columns of each table of the database therefore the DDL command to create table is as follows:-

create table Employee (SSN int(3) not null, Salary decimal(8,2), Phone int(10), Dnumber int(3));

create table Department (Dno int(3) not null, Dname varchar(20), Budget decimal(8,2));

create table Children (Name char(30) not null, Age int(2), Relationship char(20), ESSN int(3));

(3.) Adding primary key constraints to the table:-

Primary keys can be added to the table by using the alter command, therefore the DDL command to alter table is as follows:-

Alter table Employee add primary key (SSN);

Alter table Department add primary key (Dno);

Alter table Children add primary key (Name);

(4.) Adding foreign key constraints to the table:-

Since the relationship between tables shows different cardinalities therefore their is need to add foreign keys into the table.Foreign keys can be added to the table by using the alter command, therefore the DDL command to alter table is as follows:-

Alter table Employee add foreign key (Dnumber) references Department (Dno);

Alter table Children add foreign key (ESSN) references Employee (SSN);

(5.) Adding records into the tables:-

Recordes can be inserted using DML command as follows:-

Insert into tablename values (value1, value2, value3,....);

In this manner we can add records in any of the table.