Zybook Week 1 Flashcards
what is a database?
is a collection of data in a structured format. In principle, databases can be stored on paper or even clay tablets. In practice, however, modern databases are invariably stored on computers. The database structure ensures that similar data is stored in a standardized manner.
Database system, database management system or DBMS
is software that reads and writes data in a database. Database systems ensure data is secure, internally consistent, and available at all times. These functions are challenging for large databases with many users, so database systems are complex.
Query and Query Language
A query is a request to retrieve or change data in a database. A query language is a specialized programming language, designed specifically for database systems. Query languages read and write data efficiently, and differ significantly from general-purpose languages such as Python, Java, and C++.
Database Application
software that helps business users interact with database systems. Many databases are complex, and most users are not familiar with query languages. Consequently, direct database access is usually not feasible. Instead, programmers write applications to simplify the user experience and ensure data access is efficient and secure.
Database Adminstrator
responsible for securing the database system against unauthorized users. A database administrator enforces procedures for user access and database system availability.
Database designer
determines the format of each data element and the overall database structure. Database designers must balance several priorities, including storage, response time, and support for rules that govern the data. Since these priorities often conflict, database design is technically challenging.
Database programmer
develops computer programs that utilize a database. Database programmers write applications that combine database query languages and general-purpose programming languages. Query languages and general-purpose languages have significant differences, so database programming is a specialized challenge.
Database User
is a consumer of data in a database. Database users request, update, or use stored data to generate reports or information. Database users usually access the database via applications but can also submit queries directly to the database system.
What are the specific requirements of large, complex databases?
Performance, authorization, security, rules, recovery.
What is a transaction?
a group of queries that must be either completed or rejected as a whole. Execution of some, but not all, queries results in inconsistent or incorrect data
When processing transactions, databases must:
Ensure transactions are processed completely, or not at all.
Prevent conflicts between concurrent transactions
Ensure transaction results are never lost.
What is the architecture of a database system?
a database system describes the internal components and the relationships between components.
Most have the following components: query processor, storage manager, transaction manager, log and a catalog or data dictionary.
What is a storage manager?
translates the query processor instructions into low-level file-system commands that modify or retrieve data. Database sizes range from megabytes to many terabytes, so the storage manager uses indexes to quickly locate data.
What is a query processor?
interprets queries, creates a plan to modify the database or retrieve data, and returns query results to the application. The query processor performs query optimization to ensure the most efficient instructions are executed on the data.
What is a transaction manager?
ensures transactions are properly executed. The transaction manager prevents conflicts between concurrent transactions. The transaction manager also restores the database to a consistent state in the event of a transaction or system failure.
What is the log?
a file containing a complete record of all inserts, updates, and deletes processed by the database. The transaction manager writes log records before applying changes to the database. In the event of a failure, the transaction manager uses log records to restore the database.
What is the catalog?
also known as a data dictionary, is a directory of tables, columns, indexes, and other database objects. Other components use catalog information to process and execute queries.
What is a relational database?
stores data in tables, columns, and rows, similar to a spreadsheet. All data in a column has the same format. All data in a row represents a single object, such as a person, place, product, or activity.
What is open source?
software is software that anyone can inspect, copy, and modify with no licensing fee.
What is SQL?
stands for Structured Query Language and includes statements that read and write data, create and delete tables, and administer the database system.
What is NoSQL?
The newer non-relational systems are called NoSQL, for ‘not only SQL’, and are optimized for big data
What is database design?
a specification of database objects such as tables, columns, data types, and indexes. Database design also refers to the process used to develop the specification.
For small, simple databases, the database design process can be informal and unstructured. For large, complex databases, the process has three phases:
Analysis
Logical design
Physical design
What is the analysis phase of database design?
specifies database requirements without regard to a specific database system. Requirements are represented as entities, relationships, and attributes. An entity is a person, place, activity, or thing. A relationship is a link between entities, and an attribute is a descriptive property of an entity.
What are ER Diagrams, and how are they depicted?
Rectangles represent entities. Entity names appear at the top of rectangles.
Lines between rectangles represent relationships.
Text inside rectangles and below entity names represent attributes.
What is the logical design phase?
implements database requirements in a specific database system. For relational database systems, logical design converts entities, relationships, and attributes into tables, keys, and columns
What is a key?
a column used to identify individual rows of a table. Tables, keys, and columns are specified in SQL with CREATE TABLE statements.
What is a table diagram?
Rectangles represent tables. Table names appear at the top of rectangles.
Text within rectangles and below table names represents columns.
Solid bullets (●) indicate key columns.
Empty bullets (○) and arrows indicate columns that refer to keys.
The logical design, as specified in SQL and depicted in a table diagram, is called a database schema
What is the physical design phase?
phase adds indexes and specifies how tables are organized on storage media. Ex: Rows of a table may be sorted on the values of a column and stored in sort order. Physical design is specified with SQL statements such as CREATE INDEX and, like logical design, is specific to a database system
What is data independence?
The principle that physical design never affects query results is called
What is a database model?
A conceptual framework for database software.
What is the relational model?
A database model based on mathematical principles, with 3 parts:
- A data structure that prescribes how data is organized.
- Operations that manipulate data structures.
- Rules that govern valid relational data.
What is a set?
A collection of values with no inherent order.
What three mathematical concepts is the relational data structure based on?
A domain is a named set of possible database values, such as integers, dictionary words, or logical values TRUE and FALSE.
A tuple is a finite sequence of values, each drawn from a fixed domain. Ex: (3, apple, TRUE) is a tuple drawn from domains (Integers, DictionaryWords, LogicalValues). Each tuple position is called an attribute, which has a name that is unique within the relation.
A relation is a named set of tuples, all drawn from the same sequence of domains. Ex: The relation below is named Grocery and contains three tuples.
What is relational alegebra?
The relational model stipulates a set of operations on tables.
What are relational rules or integrity rules?
The relational model stipulates a set of operations on tables
What are structural rules
relational rules that govern data in every relational database. The relational model stipulates a number of structural rules, such as:
Unique primary key — all tables should have a column with no repeated values, called the primary key and used to identify individual rows.
Unique column names — different columns of the same table must have different names.
No duplicate rows — no two rows of the same table may have identical values in all columns.
What are business rules?
Unique column values — in a particular column, values may not be repeated.
No missing values — in a particular column, all rows must have known values.
Delete cascade — when a row is deleted, automatically delete all related rows.
