Databases Flashcards
define: data
“known facts that can be recorded and have implicit meaning”
define: database
collection of related data
define: DBMS
acronym for DataBase Management System
a collection of programs that enables users to create and maintain a database with processes for creating and maintaining a database (that would otherwise be manually maintained)
These are the processes:
- defining (info type, structures, constraints)
- construction (process of storing data)
- manipulation (query, update, generate reports)
- sharing (between multiple users)
define: database system
database + DBMS
define: DBA
aka DataBase Administrator
define: persistent object
permanent
What does SQL stand for?
Structured Query Language
What is SQL?
programming language designed to manage data stored in relational databases
How does SQL operate?
through simple, declarative statements
Why does SQL operate through simple, declarative statements?
keeps data accurate and secure, and helps maintain the integrity of databases, regardless of size.
What is a relational database?
A database that organizes information into one or more tables
What is a table?
A table is a collection of data organized into rows and columns.
What’s another word for tables?
Tables are sometimes referred to as relations.
Identify the parts of this diagram in SQL terms
Refer to attached picture
How can you create a table from scratch?
Relation: celebs with properties
id (integer)
name
age (integer)
How do you insert a new row into a table? Given this data
1, Justin Beiber, 22
2, Beyonce Knowles, 33
3, Jeremy Lin, 27
4, Taylor Swift, 30
What’s the faster way to insert multiple rows into a table?
What happens if we try to create a table with an existing name?
SQLite is case insensitive for most syntax
it will throw an error, because the table name already exists
https://discuss.codecademy.com/t/what-happens-if-we-try-to-create-a-table-with-an-existing-name/376312
How do you return column ‘name’ from relation ‘celebs’?
SELECT name FROM celebs;
What does SELECT statement do?
always return a new table called the result set
How do you select all data from relation?
SELECT * FROM celebs;
How would you add a new column to celebs table
twitter_handle
data type: text
What is NULL?
A special value in SQL that represents missing or unknown data.
∅
Can we change the order a column is added to a table?
No. By default, a new column will always be added at the end of the table
however, you can always select the columns in any order
SELECT col3, col1, col2
source: https://discuss.codecademy.com/t/can-we-add-a-column-at-a-specific-position-to-a-table/376656
How would you change Taylor Swift’s twitter handle to ‘@taylorswift13’ ?
UPDATE celebs
SET twitter_handle = ‘@taylorswift13’
WHERE id = 4;
- UPDATE is a clause that edits a row in the table.
- celebs is the name of the table.
- SET is a clause that indicates the column to edit.
ALTER vs UPDATE statement
The ALTER statement is used to modify columns. With ALTER, you can add columns, remove them, or even modify them.
The UPDATE statement is used to modify rows. However, UPDATE can only update a row, and cannot remove or add rows.
https://discuss.codecademy.com/t/how-is-alter-different-from-update/376655
How to delete existing records in the celebs table with no twitter_handle
DELETE FROM celebs
WHERE twitter_handle IS NULL;
How do you delete only a certain number of rows?
DELETE FROM table
WHERE condition
LIMIT 5;
What are constraints?
rules/restrictions around what kind of data type can be input into a table. Rejects data that doesn’t stick to the rules
What does the PRIMARY KEY constraint do?
Uniquely identify the row.
Attempts to insert a row with an identical value to a row already in the table will result in a constraint violation which will not allow you to insert the new row.
What does the UNIQUE constraint do?
UNIQUE columns have a different value for every row.
PRIMARY KEY vs UNIQUE
A table can have many different UNIQUE columns, there can only be one PRIMARY KEY
What does the NOT NULL constraint do?
NOT NULL columns must have a value.
Attempts to insert a row without a value for a NOT NULL column will result in a constraint violation and the new row will not be inserted.
What does DEFAULT do?
DEFAULT columns take an additional argument that will be the assumed value for an inserted row if the new row does not specify a value for that column.
How would you add the following constraints for the celebs table?
id is primary key
name is unique
date_of_birth must have a value
date_of_death must have a default value
CREATE TABLE celebs (
id INTEGER PRIMARY KEY,
name TEXT UNIQUE,
date_of_birth TEXT NOT NULL,
date_of_death TEXT DEFAULT ‘Not Applicable’
);
What command should you use to permanently remove a table from a database?
DROP TABLE
When inserting, we need to check which constraint?
➢ that the candidate keys are not already present,
➢ that the value of each foreign key either
– is all NULL, or
– is all non-NULL and occurs in the referenced relation
When deleting, we need to check which constraint?
referential integrity – check whether the primary key
occurs in another relation.
(slide 28, lec 2.1)
define relational database schema
set of relation schema
{R1, . . . , Rm} and a set of integrity constraints.
(slide 31, lec 2.1)
relational database instance is a
set of relation instances {r1, . . . , rm} such that each ri
is an instance of Ri, and the integrity constraints are satisfied.
(slide 31, lec 2.1)
