Lesson 1 Flashcards
Database application
software that helps users interact with database systems
database administrator
responsible for securing the database system against unauthorized users. Enforces procedures for user access and system availability.
Authorization
Database users should have limited access to specific tables, columns, or rows
Rules
Ensures data is consistent with structural and business rules
Query processor
interprets queries, creates a plan to modify the database or retrieve data, and returns query results to the application
Query optimization
Query processor performs query optimization to ensure the most efficient instructions are executed on the data
Store manager
Translates the query processor instruction into low-level file-system commands that modify or retrieve data
Indexes
Store manager uses indexes to quickly locate data
Transaction manager
Ensures transaction are properly executed
Metadata
Data about the database, like column names and the numbers of rows
Relational database
-Stores data in tables, columns, and rows
-All relational database systems support SQL query language
Relational databases are good for what?
Ideal for databases that require an accurate record of every transaction, such as banking, airline reservation, and student records
SQL
Stands for Structured Query Language and include statements that read and write data, create and delete tables, and administer the database system
Big data
Growth of the internet in the 1990s generated massive volumes of online data, called big data, often with poorly structured or missing information
MongoDB: big data, open-source, noSQL
NoSQL
Newer non-relational systems. “not only SQL”, and are optimized for big data
INSERT
inserts rows into table
SELECT
retrieves data from a table
UPDATE
modifies data in a table
DELETE
delete rows from a table
CREATE TABLE
creates a new table by specifying the table and column names
Database design: Analysis
-Specifies database requirements without regard to a specific database system.
-Requirements are represented as entities, relationships, and attributes
-Conceptual Design
ER diagram
Entities, relationships, and attributes are depicted in ER diagrams.
Logical diagram
Logical Design
-Implements database requirements in a specific database system
-For relational database systems, logical design converts entities, relationship, and attributes into tables, keys, and columns
-Schema
Key
Column used to identify individual rows of a table.
Specified with CREATE TABLE statements
Physical design
-Add indexes and specifies how tables are organized on storage media
-Affects query processing speed but never affects query result
Data independence
-Principle that physical design never affects query results
-When index or row order is modified, always generate the same results
API (application programming interface)
-simplify the use of SQL with general purpose language
-is a library of procedures or classes that links a host programming language to a database
MySQL Command-Line Client
-text interface included in the MySQL Server download
-allows developers to connect to the database servers, perform administrative functions, and execute SQL statements
root account
administrative account that has full control of MySQL
error code
what returns when a SQL statement is syntactically incorrect or cannot execute statement
