database ERD help

with stuctures tables.. answers the questions in the bottom

CREATE TABLE employees (

emp_no INT NOT NULL,

birth_date DATE NOT NULL,

first_name VARCHAR(14) NOT NULL,

last_name VARCHAR(16) NOT NULL,

gender ENUM ('M','F') NOT NULL, -- Enumeration of either 'M' or 'F'

hire_date DATE NOT NULL,

PRIMARY KEY (emp_no)

);

CREATE TABLE departments (

dept_no CHAR(4) NOT NULL,

dept_name VARCHAR(40) NOT NULL,

PRIMARY KEY (dept_no),

UNIQUE KEY (dept_name)

);

CREATE TABLE dept_emp (

emp_no INT NOT NULL,

dept_no CHAR(4) NOT NULL,

from_date DATE NOT NULL,

to_date DATE NOT NULL,

KEY (emp_no),

KEY (dept_no),

FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,

-- Cascade DELETE from parent table 'employee' to this child table

FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,

-- ON UPDATE CASCADE??

PRIMARY KEY (emp_no, dept_no)

);

CREATE TABLE dept_manager (

dept_no CHAR(4) NOT NULL,

emp_no INT NOT NULL,

from_date DATE NOT NULL,

to_date DATE NOT NULL,

KEY (emp_no),

KEY (dept_no),

FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,

FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,

PRIMARY KEY (emp_no, dept_no)

);

CREATE TABLE titles (

emp_no INT NOT NULL,

title VARCHAR(50) NOT NULL,

from_date DATE NOT NULL,

to_date DATE,

KEY (emp_no),

FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,

PRIMARY KEY (emp_no, title, from_date)

);

CREATE TABLE salaries (

emp_no INT NOT NULL,

salary INT NOT NULL,

from_date DATE NOT NULL,

to_date DATE NOT NULL,

KEY (emp_no),

FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,

PRIMARY KEY (emp_no, from_date)

In this step of the project you are required to provide the conceptual design for your application database and draw the Entity-Relationship Diagram (ERD). The purpose of the ERD is to record and model the business rules that need to be supported by your application as stated in your project proposal.

Part 1: Entity-Relationship Diagram (ERD):

Draw the Entity-Relationship Diagram (ERD) for your application to show the entities and relationships. Make sure to clearly show the following:

1- All entities (i.e., objects) that are needed to implement your application

2- Each entity must be identified by a primary key and described by a set of attributes.

3- List clearly all relationships between entities (Business Rules). For example, the relationship between Customer and Invoice entities is described as Each customer can have more than one invoice, however, an invoice can belong to only one customer.

4- Relationship cardinalities for all relationships in your ERD (1-to-1, 1-to-M, or M-to-M).

Part 2: Relational Schema:

Convert the ERD that you designed in Part 1 into the corresponding relational schema. The relational schema should include the following:

Table names

Attribute names and data types

Primary keys

Foreign keys