Fundamentals of Databases Flashcards
What is a entity?
A category of object, person, event or thing of interest about which data needs to be recorded.
What is a attribute?
An identifier of what data is stored in the entity.
What is a primary key?
A attribute that uniquely identifies a particular record.
What is a composite primary key?
When two or more attributes are used to uniquely identify a record.
What are the possible relationships between databases?
one-to-one
one-to-many
many-to-many
What is a foreign key?
When the primary key of one table is in another table.
What is a flat-file database?
Where only one table holds all the data.
What is a relational database?
Where data is stored on multiple tables.
What conditions have to be met for a data to be 1st normalised form ?
No repeating attributes
All attributes are atomic.
What conditions have to be met for a data to be 2nd normalised form?
In 1NF
No partial dependencies on composite key.
What conditions have to be met for a data to be 3rd normalised form?
In 2NF
No non-key dependencies
What is the SQL statement to return certain items?
SELECT fields
FROM tables
WHERE conditions
What is the SQL statement to organise data returned?
ORDER BY (ASC/ DESC)
What does the SQL statement LIKE do?
WHERE … LIKE …
Selects data that starts/ ends with a certain character. (St*)
What does the SQL statement BETWEEN do?
WHERE … BETWEEN … AND …
Returns data values between the values given.
How do you return data from two tables?
FROM table1, table2
WHERE table1.primarykey = table2.foreignkey
What is the SQL statement to combine data from multiple tables?
JOIN table
ON primarykey = foreignkey
WHERE conditions
What is the SQL statement to create a new table?
CREATE TABLE name
(data)
How do you specify data values when creating a new table?
userID CHAR(6) PRIMARY KEY, NOT NULL
name VARCHAR(num)
How do you specify a foreign key when creating a new table?
FOREIGN KEY name REFERENCES name(table)
What are the possible data types for a table? (8)
CHAR
VARCHAR
BOOLEAN
INTEGER
FLOAT
DATE
TIME
CURRENCY
What is the SQL statement to add a new attribute to a table?
ALTER TABLE table
ADD attribute
What is the SQL statement to delete an attribute in a table?
ALTER TABLE table
DROP attribute
What is the SQL statement to add a new record into a table?
INSERT INTO table(attributes)
VALUES (values)
What is the SQL statement to change a record in a table?
UPDATE table
SET new values
WHERE record
What is the SQL statement to remove a record from a table?
DELETE FROM table
WHERE record
