COMP2004 - SQL

Question 0

Defining primary keys

Answer 0

create table Student
(
ID integer PRIMARY KEY,
email varchar(20) UNIQUE NOT NULL,
lastName varchar(20),
firstName varchar(20),
DOB date
);

Question 1

Create table

Answer 1

drop table if exists Student
create table Student
(
ID integer,
email varchar(20),
lastName varchar(20),
firstName varchar(20),
DOB date
);

Question 2

Create Course table

Answer 2

drop table if exists Course;
create table Course
(
Code char(8) PRIMARY KEY,
title Varchar(50)
);

Question 3

Create table Transcript

Answer 3

drop table if exists Transcript; 
create table Transcript
(
Student_Id Integer,
Course_Code char(8),
mark integer default 0,
PRIMARY KEY (Student_ID, Course_Code),
FOREIGN KEY (Student_ID) REFERENCES Student(Id),
FOREIGN KEY(Course_Code) REFERENCES Course(Code)
);

Question 4

Functional dependency

Answer 4

Constraint between two sets of attributes in a relation of a database.
It occurs when a set of attributes X uniquely determines another set of attributes Y if each X value is associated precisely one Y value.
R is said to satisfy the functional dependency X–>Y
X is called determinant and Y the dependant set.
Funct dependcy FD: X–>Y is called trivial if Y is a subset of X.

Question 5

VIEWS

Answer 5

Virtual tables created from a stored query. Represent a subset of the data in the physical tables

Question 6

Updatable view

Answer 6

Based on 1 table and contains its primary key and no grouping/combining commands. 
CREATE VIEW MaltaHols AS
SELECT HolidayNo
FROM Holiday
WHERE location="Malta"

Question 7

Not updateable view

Answer 7

Does not contain the table's primary key;
CREATE VIEW MaltaHols AS
SELECT HotelNo
FROM Holiday
WHERE location="Malta"

Question 8

Stored procedures - advantages

Answer 8

Data validation
Centralises access logic
Can be executed by triggers such as INSERT
Reduce complilation overheads
Reduce network traffic

Question 9

Stored procedures, disadvantages

Answer 9

Very vendor specific
Not equally supported by all vendors
Puts extra load on database server, slowing service for other queries

Question 10

Trigger to track changes on update

Answer 10

CREATE TRIGGER Customer_Holiday_Archive
AFTER UPDATE ON CustomerHoliday
FOR EACH ROW
BEGIN
IF Old.StartDate != NewStartDate THEN
INSERT INTO CHANGES (CustomerId, HolidayNo, newDate, oldDate)
VALUES (OLD.CustomerNo, old.HolidayNo, NEW.StartDate, OLD.StartDate)
END IF
END

Question 11

SELECT
FROM
WHERE
AND
AND

Answer 11

SELECT Table.Attribute
FROM Table or two
WHERE Table.QueriedAttribute=” “
AND Table.Attribute is so and so

Question 12

SELECT , COUNT
FROM
GROUP BY

Answer 12

SELECT attribute, COUNT(whtvr)
FROM table
GROUP BY attribute

Question 13

INSERT INTO

VALUES

Answer 13

INSERT INTO table(attribute1, att2)

VALUES (000, “thing”)

Question 14

Keys

Answer 14

Super keys
Candidate keys
Primary keys
Foreign keys

Question 15

Super keys

Answer 15

Set of attributes which uniquely identify all other attributes

Question 16

Candidate key

Answer 16

Subset of super keys with minimal number of attrubutes needed to uniquely identify a record.

Question 17

Primary key

Answer 17

One of the candidate keys which user has specified will be the unique identifier fir a record.
Irreducible
Not null

Question 18

Foreign key

Answer 18

Refers to a candidate key in another table