Week 5

Question 1

How do you create a database schema?

Answer 1

CREATE SCHEMA Company;

Where Company is the schema name

Question 2

What are examples of attribute domains?

Answer 2

INT
REAL or DECIMAL(n,m) where m is the number of decimal place.
CHAR(n), where n is the number of chars
VARCHAR(5): variable length from 0 to 5.
BIT(n)
BIT VARYING(n)
Boolean (TRUE/FALSE/UNKOWN)
Date: YYYY-MM-DD
TIMESTAMP: HH:MM:SS

Question 3

What are different types of value & domain constraints?

Answer 3

1. Constraint on the default value of an attribute.
   DEFAULT{value}
   or
   NOT NULL

2.Domain range constraint that checks an attribute.
CHECK
eg., DNumber INT NOT NULL CHECK (Dnumber >0 AND Dnumber<21);

Question 4

How do you declare a primary key?

Answer 4

Name; Type; NOT NULL
PRIMARYKEY (Name)

Eg.,Dnumber INT NOT NULL
PRIMARY KEY(Dnumber)

Question 5

how do you declare the candidate key?

Answer 5

CKName, Type, NOT NULL,
UNIQUE(Name)

Dname VARCHAR(15) NOT NULL
UNIQUE(Dname);

Question 6

How do you declare the foreign key in a relation?

Answer 6

FOREIGN KEY(FKName) REFERENCES (PKName)

Eg.,
FOREIGN KEY(Super_SSN) REFERENCES Employee(SSN)

PK: SSN
FK: Super_SSN
Relation: Employee

Question 7

What do you do when a tuple is deleted or updated?

Answer 7

ON UPDATE CASCADE updates all of the foreign keys that refer to the PK tuple
Similarly, ON DELETE CASCADE

Question 8

What are constraint labels and why do we use them?

Answer 8

CONSTRAINT
PRIMARYKEY(PKname)

CONSTRAINT EMPPK 
PRIMARY KEY (Ssn)

EMPPK is the label in this case. It’s used for more control and to mitigate mistakes of things being deleted accidentally)

Question 9

What happens to the foreign key if the primary key is deleted?

Answer 9

Often in the relation of the PK, a default value is defined.

We can say ON DELETE SET DEFAULT.
We must also say ON UPDATE CASCADE (so that FK are updated when PK are modified)

However, we don’t always want to CASCADE so we might:
SET DEFAULT
NOT NULL

Here, the database analyst is making sure that when something is updated/deleted it isn’t transferred to an inconsistent state.

Question 10

Retrieve the name and address of all employees who work for the ‘Research’ department.

Answer 10

SELECT Fname, Lname, Address
FROM EMPLOYEE, DEPARTMENT
WHERE Dname = ‘Research’ AND Dnumber = Dno;

• Dname = ‘Research’ is a selection condition
• Dnumber = Dno; is a join condition

Question 11

For every project located in ‘Stafford’, list the project number, the controlling department number, and the department manager’s birth date.

Answer 11

SELECT Pnumber, Dnum, Lname, Address, Bdate
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE Dnum = Dnumber AND Mgr_ssn = Ssn AND Plocation = ‘Stafford

Question 12

What is the AS operator used for?

Answer 12

The AS command is used to rename a column or table with an alias.

Eg., SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;

Question 13

For each employee, retrieve the employee’s first and last name and the first and last name of their supervisor.

Answer 13

SELECT E.Fname, E.Lname, S.Fname, S.Lname
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.Super_ssn=S.Ssn;

Question 14

When can u use an asterisk (*)?

Answer 14

To list all of the attributes in the SELECT clause

eg.,
SELECT *
FROM EMPLOYEE
WHERE Dno=5

Question 15

What is a set?

Answer 15

only unique elements, e.g., S = {a, b, c}

Question 16

What is a mutliset?

Answer 16

might have duplicates, e.g., M = {a, a, a, b, c, c}

Question 17

What are operators?

Answer 17

UNION, EXCEPT, INTERSECT

Question 18

How do you retrieve all of the distinct values of an attribute?

Answer 18

SELECT DISTINCT

eg.,,
SELECT DISTINCT Salary
FROM EMPLOYEE

Question 19

How do you use the UNION operator?

Answer 19

split this into two sub-queries and then use the set UNION operator over the partial results.