1.1 Database.pdf

12/31/23, 4:54 PM zyBooks 1. Introduction to Databases 1.1 Database basics Data Data is numeric, textual, visual, or audio information that describes real-world systems. analog Historically, data was mostly analog, encoded as continuous variations on various physical media. digital Today, dat...

12/31/23, 4:54 PM zyBooks 1. Introduction to Databases 1.1 Database basics Data Data is numeric, textual, visual, or audio information that describes real-world systems. analog Historically, data was mostly analog, encoded as continuous variations on various physical media. digital Today, data is mostly digital, encoded as zeros and ones on electronic and magnetic media. database 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. database system / database management system / DBMS A database system, also known as a 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 language A query language is a specialized programming language, designed specifically for database systems. database application A database application is software that helps business users interact with database systems. database administrator A database administrator is responsible for securing the database system against unauthorized users. A database administrator enforces procedures for user access and database system availability. database designer A 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. Alt+A https://learn.zybooks.com/zybook/WGUD426v2/content-explorer/print 1/39 12/31/23, 4:54 PM zyBooks database programmer A database programmer develops computer programs that utilize a database. database user A 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. 1.2 Database systems transaction A transaction is 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. architecture The architecture of a database system describes the internal components and the relationships between components. query processor The query processor interprets queries, creates a plan to modify the database or retrieve data, and returns query results to the application. query optimization The query processor performs query optimization to ensure the most efficient instructions are executed on the data. storage manager The storage manager translates the query processor instructions into low-level file-system commands that modify or retrieve data. indexes The storage manager uses indexes to quickly locate data. transaction manager The transaction manager ensures transactions are properly executed. log The log is a file containing a complete record of all inserts, updates, and deletes processed by the database. catalog / data dictionary The catalog, also known as a data dictionary, is a directory of tables, columns, indexes, and other database objects. relational database A relational database stores data in tables, columns, and rows, similar to a spreadsheet. https://learn.zybooks.com/zybook/WGUD426v2/content-explorer/print 2/39 12/31/23, 4:54 PM zyBooks SQL SQL stands for Structured Query Language and includes statements that read and write data, create and delete tables, and administer the database system. big data The growth of the internet in the 1990s generated massive volumes of online data, called big data, often with poorly structured or missing information. NoSQL The newer non-relational systems are called NoSQL, for 'not only SQL', and are optimized for big data. Open source Open source software is software that anyone can inspect, copy, and modify with no licensing fee. 1.3 Query languages query A query is a command for a database that typically inserts new data, retrieves data, updates data, or deletes data from a database. query language A query language is a computer programming language for writing database queries. CRUD The four common queries are sometimes referred to as CRUD operations, an acronym for Create, Read, Update, and Delete data. Structured Query Language / SQL Structured Query Language, or SQL, is the standard query language of relational database systems. statement An SQL statement is a database command, such as a query that inserts, selects, updates, or deletes data: . INSERT INSERT inserts rows into a table. SELECT SELECT retrieves data from a table. UPDATE UPDATE modifies data in a table. DELETE DELETE deletes rows from a table. CREATE TABLE The SQL CREATE TABLE statement creates a new table by specifying the table and column names. https://learn.zybooks.com/zybook/WGUD426v2/content-explorer/print 3/39 12/31/23, 4:54 PM zyBooks data type Progression Each column is assigned a data type that indicates the format of column values. Data types can be numeric, textual, or complex. 1.3.1 Query languages. 1.4 Database design and programming database design A database design is 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. analysis The analysis phase specifies database requirements without regard to a specific database system. ER diagrams Entities, relationships, and attributes are depicted in ER diagrams: . logical design The logical design phase implements database requirements in a specific database system. key A key is a column used to identify individual rows of a table. table diagram The logical design is depicted in a table diagram. schema The logical design, as specified in SQL and depicted in a table diagram, is called a database schema. physical design The physical design phase adds indexes and specifies how tables are organized on storage media. data independence The principle that physical design never affects query results is called data independence. application programming interface / API An application programming interface, or API, is a library of procedures or classes that links a host programming language to a database. 1.5 MySQL MySQL MySQL is a leading relational database system sponsored by Oracle. MySQL Community / MySQL Server MySQL Community, commonly called MySQL Server, is a free edition. https://learn.zybooks.com/zybook/WGUD426v2/content-explorer/print 4/39 12/31/23, 4:54 PM zyBooks MySQL Enterprise MySQL Enterprise is a paid edition for managing commercial databases. MySQL Enterprise includes MySQL Server and additional administrative applications. root account The root account, the administrative account that has full control of MySQL. MySQL CommandLine Client The MySQL Command-Line Client is a text interface included in the MySQL Server download. The Command-Line Client allows developers to connect to the database server, perform administrative functions, and execute SQL statements. error code MySQL Server returns an error code and description when an SQL statement is syntactically incorrect or the database cannot execute the statement. MySQL Workbench MySQL Workbench is installed with MySQL Server and allows developers to execute SQL commands using an editor. 2. Relational Databases 2.1 Relational model database model A database model is a conceptual framework for database systems, with three parts: Data structures that prescribe how data is organized. Operations that manipulate data structures. Rules that govern valid data. . relational model The relational model is a database model based on a tabular data structure. The model was published in 1970 by E. F. Codd of IBM and released in commercial products around 1980. The data structure, operations, and rules are standardized in SQL, the universal query language of relational databases. big data The rise of the internet in the 1990s generated big data, characterized by unprecedented data volumes and rapidly changing data structures. set A set is an unordered collection of elements enclosed in braces. https://learn.zybooks.com/zybook/WGUD426v2/content-explorer/print 5/39