Just the explanation Consider the database schema with the following SQL relatio

Question

Just the explanation

Consider the database schema with the following SQL relation declarations: CREATE TABLE R (A INT, B INT, C CHAR (30); CREATE TABLE S (D INT): Consider each of the following parts separately; that is, start fresh with the above relation definition for each part. in each case, if it is not possible, state so, clearly explaining why. Specify that attributes A and C in R together form a primary key. Specify that attributes A or C R are keys but only C is the primary key. Specify that attribute A in R may not take on the value NULL in each of the following ways: 1-As one or more attribute based constraints; 2- As one or more general assertions. Specify that there is a referential integrity constraint from attribute D (the foreign key) in S to attribute A (the primary key) in R, in each of the following ways: 1- As a referential integrity constraint; 2- As one or more attribute based constraints; 3- As one or more general assertions. Specify that for each of R the value of attribute A must be at least twice the value of attribute B, in each of the following ways: 1- As one or more attribute based constraints; 2- As one or more based constraints; 3-As one or more general assertions.

Explanation / Answer

[a]

CREATE TABLE R (
A INT,
B INT,
C CHAR(30),
Constraint PK_R PRIMARY KEY (A, C)
);

[b]
CREATE TABLE R (
A INT,
B INT,
C CHAR(30) PRIMARY KEY,
);

[c]
CREATE TABLE R (
A INT NOT NULL,
B INT,
C CHAR(30),
CHECK (A>0 AND C='welcome')
);

CREATE TABLE R (
A INT NOT NULL,
B INT,
C CHAR(30),
)
CREATE ASSERTION constraint_1
CHECK ((SELECT AVG(A) FROM R >80) NOT DEFERRABLE;

[d]
CREATE TABLE R (
A INT NOT NULL PRIMARY KEY,
B INT,
C CHAR(30)
);

CREATE TABLE S(
D INT,
FOREIGN KEY (D) REFERENCES R(A)
)
CREATE ASSERTION constraint_1
CHECK (80 < (SELECT A FROM S)) NOT DEFERRABLE;

[e]
CREATE TABLE R (
A INT NOT NULL,
B INT,
C CHAR(30),
CHECK (A>=2*(B) AND (SELECT C FROM R WHERE C = 'full'))
);

CREATE TABLE R (
A INT NOT NULL,
B INT,
C CHAR(30),
CHECK (A>=2*(B) AND C = 'ABC')
);

CREATE ASSERTION Avg CHECK(
100 < (SELECT avg(A) FROM R)AND
2000 > (SELECT avg(B) FROM R))

Related Questions

Navigate

• Browse (All)
• Browse J
• Subjects

---

---

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