SQL Flashcards
an organized collection of structured information, or data, typically stored electronically in a computer system
Databases
What does DBMS stand for?
database management system
Serves as an interface between the database and its end users or programs, allowing users to retrieve, update, and manage how the information is organized and optimized. Also facilitates oversight and control of databases, enabling a variety of administrative operations such as performance monitoring, tuning, backup, and recovery.
database management system
type of database that stores and provides access to data points that are related to one another. based on the relational model, an intuitive, straightforward way of representing data in tables. Stores data into relations (tables)
Relational Databases
What does SQL stand for?
Structured Query Language
a programming language used by nearly all relational databases to query, manipulate, define data, and to provide access control
SQL
translations on high level queries into low level expressions that can be used at physical level of file system, query optimization and actual execution of query to get the result.
Query Processing
core part of a DBMS that provides access to the data in a database managed by the DBMS.
DBMS Engine
What are the sublanguages of SQL?
DDL, DML, DCL, TCL
What is DDL?
Data Definition Language
What does DDL do?
Used for creating database tables, defining the database structure
what are the commands for DDL?
Create
Alter
Drop
what is DML?
Data Manipulation Language
What does DML do?
All operations on individual rows
what are the commands for DML?
Select
Insert
Update
Delete
what is DCL?
Data Control Language
What does DCL do?
For DB admin, allow users to have different permissions
what are the commands for DCL?
Grant
Revoke
what is TCL?
Transaction Control Language
What does TCL do?
Concerned with grouping SQL commands and executing them as a batch
what are the commands for TCL?
Commit
Savepoint
Rollback
Transact
refers to the SQL variant used by a database vendor
SQL Dialect
refer to the different categories of the statements/commands in SQL
SQL Sublanguages
Used for setting guidelines in what data belongs to a column
Constraints
what are the Constraint types
Data type Not Null Unique Check Primary Key - unique and not null Foreign Key
used to uniquely identify a data set, and also to establish relationships between entities
Keys
what are the different keys?
Candidate key
Primary key
Foreign Key
Composite Key
Minimal set of columns in a table that every other column depends on
Candidate key
Unique identifier for a row in a table
Primary key
A set of columns which hold the values of some primary key to establish a relationship to another row
Foreign Key
Any key that’s more than one column
Composite Key
Ensures the accuracy and consistency of data within a relationship
Referential integrity
Relationships in SQL : This means that two sets of data are unique to each other
1:1
Relationships in SQL : This means that one set of data can have many instances of the other data set
1:m
Relationships in SQL : This means that both data sets can have many instances of each other
m:m
Relationships in SQL : Put both entities in the same table or separate the entities in two tables with a FK reference that is Unique and Not Null
1:1
Relationships in SQL : Two tables, FK that is not unique
1:m
Relationships in SQL : 3 tables one of which is a join/junction table
m:m
shows the relationships of entity sets stored in a database. Visual representation of your DB design.
ER Diagram
Designing a database in a certain way to ease data management
Normalization
What are the normal forms?
1NF
2NF
3NF
Normal Forms: Atomic Values, No repeating groups of columns, No duplicate rows
1NF
Normal Forms: Has to be 1NF, NO partial dependencies, No composite keys mean you’re 2NF by default
2NF
Normal Forms: Has to be 2NF, NO transitive dependencies
3NF
