Section 1
Preview this deck
What is SQL?
Front
| 5 | 0 | ||
| 4 | 0 | ||
| 3 | 0 | ||
| 2 | 0 | ||
| 1 | 0 |
Active users
0
All-time users
0
Favorites
0
Last updated
7 years ago
Date created
Mar 1, 2020
Cards (97)
Section 1
(50 cards)
What is SQL?
A way to communicate with a relational database.
Give and example of a many to many relationship.
Students to classes.
How are Tables structured?
Into rows and columns.
Third Normal Form
The table is in it's second normal form. All columns are not transitionally dependent on the primary key.
Second Normal Form
The table is in it's first normal form. All columns are dependent on the primary key.
What does the Update command do?
Data modification. Updates or modifies a row of data.
What are columns in a SQL Table?
The types and identifiers. Make up the structure of what a row should have.
What is SQL and acronym for?
Structured Query Language
What does the Insert command do?
Data entry. Inserts a row of data.
What commands are a part of Data Control Language?
Grant and Revoke.
Surrogate Key
A value generated solely for being a primary key (a counter or random number or string)
What is the syntax of the Insert command?
INSERT into TABLE(Type1,Type2) VALUES (t1,t2);
Give and example of a one to one relationship.
Customer to ID.
Dialects
Oracle, Mysql, Microsoft SQL, Postgres.
Multiplicity
Description of the relationships between fields and tables.
What is the syntax of the Update command?
Update TABLE set FIELD = VALUE where FIELD = VALUE;
Distributed Architectures
A distributed system is a model in which components located on networked computers communicate and coordinate their actions by passing messages. The components interact with each other in order to achieve a common goal. Three significant characteristics of distributed systems are: concurrency of components, lack of a global clock, and independent failure of components Data is removed from the code itself allowing for changes to be made to the codebase without harming the data.
Lookup Tables
A way to show the relationship between two entities. Two columns and with at least one being a primary key.
Database normalization in plain english.
A table should be about a specific topic and contain columns that support the topic.
What does Normalization lead to?
Breaking a larger table into smaller tables.
Why are lookup tables useful?
They reduce the amount of information used in a table. Essentially join two tables.
What is the syntax of the Delete command?
DELETE from TABLE where FIELD = VALUE
What is Data Query Language
Commands for making queries.
What commands are a part of Data Query Language?
Select
What are rows in a SQL Table?
The entities.
Referential Integrity
The consistency of relationships between tables when changes are made.
What does Grant do?
allows specified users to perform specified tasks.
What does Revoke do?
cancels previously granted or denied permissions.
Can you have a null primary key?
Nope.
Natural Key
A unique value that already exists in a table that would function as a primary key.
What are foreign keys?
References to an another table. Must match a correct value (the row that the foreign key belongs to must exist).
Should primary keys be changed?
Nope.
First Normal Form
The information stored in columns is atomic, or the smallest amount of data possible. Columns are not repeating.
What is TCL?
Transaction Control Language commands are used to manage transactions in the database. These are used to manage the changes made to the data in a table by DML statements. It also allows statements to be grouped together into logical transactions.
Primary Key
Unique identifier for each row.
What are the sublanguages of SQL?
DDL (Data Definition Language), DML (Data Manipulation Language), DQL (Data Query Language), and DCL (Data Control Language), and TCL (Transaction Control Language).
What is Data Control Language?
Commands that grant or revoke access to data.
What does the Delete command do?
Data deletion. Deletes a row.
What commands are a part of DML?
Insert, Update, and Delete
What is the Select command?
Select returns a result set of records from one or more tables
Give an example of a one to many relationship.
Grandma to cats.
What is DDL?
Data Definition Language. Commands to manipulate and change data tables.
Cardinality
The maximum amount of relationships a field of data can be involved in.
What is the syntax for the Select command?
Select FIELD from TABLE; * is all.
3 Reasons for Normalization
1) Minimize duplicate data 2) Minimize data modification. 3) Simplify queries.
Composite Key
A key made of multiple columns. Not very good.
Database
A structured collection of data.
Relational Data
A relationship exists between A and B. Databases do not have to be relational.
Normalization
The process of organizing data in a database. Helps to keep all data related. prevents data-modification-related errors and inconsitencies (referential integrity) Removing what we don't need and keeping what we do need. Simplifiy queries. Reducing redundancy and enforcing referential integrity.
Foreign Key
A reference to another table. DOES NOT HAVE TO BE A PRIMARY KEY but should be.
Section 2
(47 cards)