COMP2004 - SQL Flashcards Preview

Undeleted > COMP2004 - SQL > Flashcards

Flashcards in COMP2004 - SQL Deck (19)
Loading flashcards...
0
Q

Defining primary keys

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

Create table

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

Create Course table

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

Create table Transcript

A
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)
);
4
Q

Functional dependency

A

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.

5
Q

VIEWS

A

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

6
Q

Updatable view

A
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"
7
Q

Not updateable view

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

Stored procedures - advantages

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

Stored procedures, disadvantages

A

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

10
Q

Trigger to track changes on update

A

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

11
Q
SELECT
FROM
WHERE
AND
AND
A

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

12
Q

SELECT , COUNT
FROM
GROUP BY

A

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

13
Q

INSERT INTO

VALUES

A

INSERT INTO table(attribute1, att2)

VALUES (000, “thing”)

14
Q

Keys

A

Super keys
Candidate keys
Primary keys
Foreign keys

15
Q

Super keys

A

Set of attributes which uniquely identify all other attributes

16
Q

Candidate key

A

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

17
Q

Primary key

A

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

18
Q

Foreign key

A

Refers to a candidate key in another table