COMP2004 - SQL Flashcards
Defining primary keys
create table Student ( ID integer PRIMARY KEY, email varchar(20) UNIQUE NOT NULL, lastName varchar(20), firstName varchar(20), DOB date );
Create table
drop table if exists Student create table Student ( ID integer, email varchar(20), lastName varchar(20), firstName varchar(20), DOB date );
Create Course table
drop table if exists Course; create table Course ( Code char(8) PRIMARY KEY, title Varchar(50) );
Create table Transcript
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) );
Functional dependency
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.
VIEWS
Virtual tables created from a stored query. Represent a subset of the data in the physical tables
Updatable view
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"
Not updateable view
Does not contain the table's primary key; CREATE VIEW MaltaHols AS SELECT HotelNo FROM Holiday WHERE location="Malta"
Stored procedures - advantages
Data validation Centralises access logic Can be executed by triggers such as INSERT Reduce complilation overheads Reduce network traffic
Stored procedures, disadvantages
Very vendor specific
Not equally supported by all vendors
Puts extra load on database server, slowing service for other queries
Trigger to track changes on update
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
SELECT FROM WHERE AND AND
SELECT Table.Attribute
FROM Table or two
WHERE Table.QueriedAttribute=” “
AND Table.Attribute is so and so
SELECT , COUNT
FROM
GROUP BY
SELECT attribute, COUNT(whtvr)
FROM table
GROUP BY attribute
INSERT INTO
VALUES
INSERT INTO table(attribute1, att2)
VALUES (000, “thing”)
Keys
Super keys
Candidate keys
Primary keys
Foreign keys