(36 points) Q1 A. Consider the following ER diagram. Write SQL statements to cre

Question

(36 points) Q1 A. Consider the following ER diagram. Write SQL statements to create the corresponding relations and capture as many of the constraints as possible. name ssn cost pname age Employee olicy Dependent B. Assume we have the following instances for two tables Employee and Dept-Policy: Employee Dept-Polic ssn pname age cost ssn 5381 Madayan 5381 Sara 5382 Guldu name 100 100 60 5381 Jack 12 5382 Jill What will be the new instances of the tables Employee and Dept-Policy after running the following query? DELETE FROM Employee E WHERE E.ssn = '5381.

Explanation / Answer

a)

To create table Employee with primary key

constraint ssn we use the following query

  CREATE TABLE Employee (
        ssn int NOT NULL,
        name varchar(255) NOT NULL,
        PRIMARY KEY (ssn)
);

To create table dept-policy with constraints

ssn as foreign key and ssn,pname as

composite primary key we use below query

Create table dept-policy (

ssn int NOT NULL,

pname VARCHAR(255) NOT NULL,

age int NOT NULL,

cost int NOT NULL,

FOREIGN KEY (ssn) REFERENCES Employee(ssn),

PRIMARY KEY(ssn, pname)

);

b)

      After this query execution the record with ssn 5381 will be deleted and

      the table will have only one record 5382 guldu.

      Now Dependent table is having referential integrity with employee

      hence the 2 records with ssn 5381 will be deleted and the new table

      will have only 1 record 5382 jill 5 60.

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

---

---

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