Week 3

Question 1

What is the DDL statement for this table

Answer 1

CREATE TABLE Students
(sid TEXT,
name TEXT,
login TEXT,
age INTEGER,
gpa REAL);

Question 2

“For a given student and course, there is a single grade in Enrolled” What is the DDL statement

Answer 2

CREATE TABLE Enrolled
(sid VARCHAR(20)
cid VARCHAR(20),
grade VARHAR(2),
PRIMARY KEY (sid,cid) );

Question 3

Create Enrolled table, “Students can take only one course, and receive a single grade for that course; further, no two students in a course receive the same grade.”

Answer 3

CREATE TABLE Enrolled
(sid VARCHAR(20)
cid VARCHAR(20),
grade VARCHAR(2),
PRIMARY KEY (sid),
UNIQUE (cid, grade) );

Question 4

Answer 4

CREATE TABLE Works_In(
ssn VARCHAR(1),
did INTEGER,
since TEXT,
PRIMARY KEY (ssn, did), FOREIGN KEY (ssn)
REFERENCES Employees(ssn),
FOREIGN KEY Departments (did)
REFERENCES Departments (did) );

Question 5

Answer 5

CREATE TABLE Enrolled
(sid VARCHAR(20), cid VARCHAR(20), grade VARCHAR(2),
PRIMARY KEY (sid,cid),
FOREIGN KEY (sid) REFERENCES Students (sid) );

Answer 6

CREATE TABLE Enrolled
(sid VARCHAR(20), cid VARCHAR(20), grade VARCHAR(2),
PRIMARY KEY (sid,cid),
FOREIGN KEY (sid) REFERENCES Students (sid) );

Question 7

Answer 7

CREATE TABLE Dep_Policy (
pname VARCHAR(20),
age INTEGER,
cost REAL,
ssn VARCHAR(11) NOT NULL,
PRIMARY KEY (pname, ssn),
FOREIGN KEY (ssn) REFERENCES Employ