Question

Using MySQL 8.xx create a database with the following characteristics. .

A. Your design MUST meet the requirements for Third Normal Form

B. Create a database (you will need multiple tables) to store employee information. This information will include:

Employee Number, First Name, Last Name, Date of birth, Address, city, state, zip, department, job title, supervisor, health insurance number, health insurance provider, dental insurance number, dental insurance provider, spouse/partner, children, children's ages.

C. Populate the table with multiple records which will cover all possible relations.

Note: Determine which fields are required and which fields are optional. Determine the correct data types and sizes for each field. Include the 'relationships' between the tables. Hint: You will need some secondary key fields to link tables together.

What to turn in:

Screen shots (only)

1. Database table metadata showing table names, column names, data types and sizes.

2. Map of relations between tables (tables with arrows between them).

3. Contents of each table with related data.

Answer 1

1.

First we create employee table with number, first name, last name and date of birth. here the primary key is employee number.

SQL> create table emp(emp_no int NOT NULL,
F_name varchar(20) NOT NULL,
L_name varchar(20) NOT NULL,
Date_birth DATE NOT NULL,
primary key(emp_no) );

Then we create our 2nd table that is address which contains address, state, city, zip code of employee.

it also contains foreign key of emp_no to connect this table with employee table.

SQL> create table Address
( addr varchar(250) NOT NULL,
City varchar(50) NOT NULL,
State varchar(50) NOT NULL,
Zip CHAR(5) NOT NULL,
FOREIGN KEY(emp_no) REFERENCES emp(emp_no));

3rd table is department table which includes the supervisor of employee, department and job title.

SQL> create table department

( dept varchar(100) NOT NULL,

job_title varchar(100) NOT NULL,

supervisor varchar(100) NOT NULL,

FOREIGN KEY(emp_no) REFERENCES emp(emp_no));

Our 4th table is insurance table, where we include both insurance- dental and health for employee.

SQL> create table insurance

( health_insu_no char(10) NOT NULL,

health_insu_provider varchar(50) NOT NULL,

dental_insu_no char(10) NOT NULL,

dental_insu_provider varchar(50) NOT NULL,

FOREIGN KEY(emp_no) REFERENCES emp(emp_no));

5th table is Family table where we include details about family such as spouse/partner, children, children's age.

we connect this table with employee using foreign key.

SQL> create table family

( spouse_partner varchar(50) NOT NULL,

children int NOT NULL,

child_age int NOT NULL,

FOREIGN KEY(emp_no) REFERENCES emp(emp_no));

2.