Provide SQL create/alter statements that fully implements each E-R diagram. Impl

Question

Provide SQL create/alter statements that fully implements each E-R diagram. Implement the table name, column names, primary key, foreign key, and not null constraints based on each E-R diagram. An example for department is shown below (Use either varchar/numeric for each field - use your own judgment when deciding the fields). Insert your answers beneath each drawing.

create table department (

dno numeric,

dname varchar(30),

budget numeric,

ssn numeric not null);

alter table department

   add constraint department_dno_pk primary key(dno);

alter table department

    add constraint department_manager_fk foreign key(ssn)

    references employee(ssn);

Explanation / Answer

1.

create table employee(

ssn numeric,

salary numeric,

phone varchar(30)

);

alter table department

add constraint employee_ssn_pk primary key(ssn);

create table department (

dno numeric,

dname varchar(30),

budget numeric,

ssn numeric not null);

alter table department

add constraint department_dno_pk primary key(dno);

alter table department

add constraint department_manager_fk foreign key(ssn)

references employee(ssn);

2.

create table car(

vehicle_id numeric,

make varchar(30) not null,

year varchar(10) not null

);

alter table car

add constraint car_vehicle_id_pk primary key(vehicle_id);

create table accident(

claim_no numeric,

date date not null

);

alter table accident

add constraint accident_claimno_pk primary key(claim_no);

create table customer(

policy_num numeric,

f_name varchar(50) not null,

l_name varchar(50),

addr varchar(100),

vehicle_id numeric,

claim_no numeric

);

alter table customer

add constraint customer_policynum_pk primary key(policy_num);

alter table customer

add constraint customer_vehicleid_fk foreign key(vehicle_id)

references car(vehicle_id);

alter table customer

add constraint customer_claim_no_fk foreign key(claim_no)

references car(claim_no);

3.

create table position(

position_id numeric,

pos_descr varchar(40)

);

alter table position

add constraint position_position_id_pk primary key(position_id);

create table qualification(

qual_id numeric,

qual_descr varchar(50)

);

alter table qualification

add constraint qualification_qual_id_pk primary key(qual_id);

create table employee(

emp_id numeric,

fname varchar(30) not null,

lname varchar(30),

hire_date date,

salary numberic not null,

commision numeric,

position_id numeric,

qual_id numeric

);

alter table employee

add constraint employee_emp_id_pk primary key(emp_id);

alter table employee

add constraint employee_position_id_fk foreign key(position_id)

references position(position_id);

alter table employee

add constraint employee_qual_id_fk foreign key(qual_id)

references qualification(qual_id);

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.