For this question we are going to take a set of relational schema and produce an ER diagram which best represents it. This can be done as a first step in evaluating a preexisting system to see where improvements could be made. Please capture any constraints which are logically associated with the schema and ask the instructor for any clarification regarding the functioning of the schema.

CREATE TABLE DRIVER (

LICENSENO CHAR(15) PRIMARY KEY,

NAME VARCHAR(60),

ADDR VARCHAR(120),

PHONE CHAR(10),

DOB DATE,

EXPIRY_DATE DATE

);

CREATE TABLE LICENSE_CLASS (

CLASS CHAR(2) PRIMARY KEY,

DESC VARCHAR(60)

);

CREATE TABLE DONOR_AUTH (

LICENSENO CHAR(15),

NAME CHAR(60),

PHONE CHAR(10),

PRIMARY KEY (LICENSENO, NAME),

FOREIGN KEY (LICENSENO) REFERENCES DRIVER ON DELETE CASCADE

);

CREATE TABLE DRIVERS_CLASS (

LICENSENO CHAR(15),

CLASS CAHR(2),

PRIMARY KEY (LICENSENO, CLASS),

FOREIGN KEY (LICENSENO) REFERENCES DRIVER,

FOREIGN KEY (CLASS) REFERENCES LICENSE_CLASS

);

CREATE TABLE VEHICLE (

MAKE CHAR(20),

MODEL CHAR (30),

WEIGHT INTEGER,

CATEGORY INTEGER NOT NULL,

PRIMARY KEY (MAKE, MODEL),

FOREIGN KEY (CATEGORY) REFERENCES VEHICLE_CATEGORY

);

CREATE TABLE VEHICLE_CATEGORY (

CATEGORY INTEGER PRIMARY KEY,

DESCR CHAR(80),

MAX_WEIGHT INTEGER,

AXLES INTEGER

);

CREATE TABLE REQUIRED_CLASS (

CATEGORY INTEGER,

CLASS CHAR(2),

PRIMARY KEY (CATEGORY, CLASS),

FOREIGN KEY (CATEGORY) REFERENCES VEHICLE_CATEGORY, FOREIGN KEY (CLASS) REFERENCES LICENSE_CLASS

);