Questions

A$.$ Use the methods presented in class to translate the ER diagram into relations.

$$ Translate the ER design, including entities and relationships, into a set of relations.

$$ Describe the primary key, not NULL and inclusion dependency constraints needed for the relational schema to capture and enforce the semantics of the ER design.

$$ Note: You can ignore the derived attributes in the assignment.

Example:

Relation:

employee$($id$,$ depId, name, level, award$)$

Not Null Attributes:

All non$-$key attributes are not NULL.

Inclusion constraints:

employee$($depId$)$department$($id$)$

B$.$ Write SQL DDL statements for creating the tables corresponding to the relations you developed. Pick suitable data types for each attribute. Also include the appropriate referential integrity constraints and $$NOT NULL$$ constraint while creating the tables. Execute your DDL statements on MySQL and make sure that all the statements execute without any error $($we will be executing the DDL statements in the same order as in your submission$).$

Example:

CREATE TABLE Employee$($

id INTEGER NOT NULL,

name VARCHAR$(40)$ NOT NULL,

depId VARCHAR$(10)$ NOT NULL,

award VARCHAR$(40),$

PRIMARY KEY $($id$),$

FOREIGN KEY $($depId$)$ REFERENCES Department$($depID$)$

$)$;