2.1 Database.pdf

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 Co...

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 12/31/23, 4:54 PM zyBooks tuple A tuple is an ordered collection of elements enclosed in parentheses. table A table has a name, a fixed tuple of columns, and a varying set of rows. column A column has a name and a data type. row A row is an unnamed tuple of values. Each value corresponds to a column and belongs to the column's data type. data type A data type is a named set of values, from which column values are drawn. relational algebra These operations are collectively called relational algebra and are the theoretical foundation of the SQL language. Relational rules Relational rules are part of the relational model and govern data in every relational database. Business rules Business rules are based on business policy and specific to a particular database. constraints Relational rules are implemented as SQL constraints and enforced by the database system. Progression 2.1.1 Relational model. 2.2 Structured Query Language Structured Query Language / SQL Structured Query Language (SQL) is a high-level computer language for storing, manipulating, and retrieving data. statement An SQL statement is a complete command composed of one or more clauses. clause A clause groups SQL keywords like SELECT, FROM, and WHERE with table names like City, column names like Name, and conditions like Population > 100000. Data Definition Language Data Definition Language (DDL) defines the structure of the database. https://learn.zybooks.com/zybook/WGUD426v2/content-explorer/print 6/39 12/31/23, 4:54 PM zyBooks Data Query Language Data Query Language (DQL) retrieves data from the database. Data Manipulation Language Data Manipulation Language (DML) manipulates data stored in a database. Data Control Language Data Control Language (DCL) controls database user access. Data Transaction Language Data Transaction Language (DTL) manages database transactions. 2.3 Managing databases database system instance A database system instance is a single executing copy of a database system. Personal computers usually run just one instance of a database system. Shared computers, such as computers used for cloud services, usually run multiple instances of a database system. CREATE DATABASE DatabaseName CREATE DATABASE DatabaseName creates a new database. DROP DATABASE DatabaseName DROP DATABASE DatabaseName deletes a database, including all tables in the database. USE DatabaseName USE DatabaseName selects a default database for use in subsequent SQL statements. SHOW DATABASES SHOW DATABASES lists all databases in the database system instance. SHOW TABLES SHOW TABLES lists all tables in the default database. SHOW COLUMNS FROM TableName SHOW COLUMNS FROM TableName lists all columns in the TableName table of the default database. SHOW CREATE TABLE TableName SHOW CREATE TABLE TableName shows the CREATE TABLE statement for the TableName table of the default database. 2.4 Tables table A table has a name, a fixed sequence of columns, and a varying set of rows. column A column has a name and a data type. https://learn.zybooks.com/zybook/WGUD426v2/content-explorer/print 7/39 12/31/23, 4:54 PM zyBooks row A row is an unnamed sequence of values. Each value corresponds to a column and belongs to the column's data type. cell A cell is a single column of a single row. empty table A table without rows is called an empty table. data independence Rule 4 is called data independence. CREATE TABLE The CREATE TABLE statement creates a new table by specifying the table name, column names, and column data types. DROP TABLE The DROP TABLE statement deletes a table, along with all the table's rows, from a database. ALTER TABLE The ALTER TABLE statement adds, deletes, or modifies columns on an existing table. Progression 2.4.1 Creating, dropping, and altering tables. 2.5 Data types data type A data type is a named set of values from which column values are drawn. Integer Integer data types represent positive and negative integers. Decimal Decimal data types represent numbers with fractional values. Character Character data types represent textual characters. Date and time Date and time data types represent date, time, or both. Some date and time data types include a time zone or specify a time interval. Binary Binary data types store data exactly as the data appears in memory or computer files, bit for bit. Spatial Spatial data types store geometric information, such as lines, polygons, and map coordinates. Document Document data types contain textual data in a structured format such as XML or JSON. https://learn.zybooks.com/zybook/WGUD426v2/content-explorer/print 8/39 12/31/23, 4:54 PM zyBooks signed A signed number may be negative. unsigned An unsigned number cannot be negative. 2.6 Selecting rows operator / operands An operator is a symbol that computes a value from one or more other values, called operands: Arithmetic operators compute numeric values from numeric operands. Comparison operators compute logical values TRUE or FALSE. Operands may be numeric, character, and other data types. Logical operators compute logical values from logical operands. . unary A unary operator has one operand. binary A binary operator has two operands. expression An expression is a string of operators, operands, and parentheses that evaluates to a single value. Operands may be column names or fixed values. The value of an expression may be any data type. operator precedence Operators in an expression are evaluated in the order of operator precedence, shown in the table below. Operators of the same precedence are evaluated from left to right. Regardless of operator precedence, expressions enclosed in parentheses are evaluated before any operators outside the parentheses are applied. SELECT / FROM The SELECT statement selects rows from a table. The statement has a SELECT clause and a FROM clause. The FROM clause specifies the table from which rows are selected. The SELECT clause specifies one or more expressions, separated by commas, that determine what values are returned for each row. result table The SELECT statement returns a set of rows, called the result table. https://learn.zybooks.com/zybook/WGUD426v2/content-explorer/print 9/39 12/31/23, 4:54 PM zyBooks LIMIT MySQL has a LIMIT clause that limits the number of rows returned by a SELECT statement. condition A condition is an expression that evaluates to a logical value. WHERE A SELECT statement has an optional WHERE clause that specifies a condition for selecting rows. A row is selected when the condition is TRUE for the row values. A row is omitted when the condition is either FALSE or NULL. Progression 2.6.1 Selecting rows. 2.7 Null values NULL NULL is a special value that represents either unknown or inapplicable data. NOT NULL The NOT NULL constraint prevents a column from having a NULL value. Statements that insert NULL, or update a value to NULL, are automatically rejected. NOT NULL follows the column name and data type in a CREATE TABLE statement. IS NULL / IS NOT NULL Instead, the IS NULL and IS NOT NULL operators must be used to select NULL values. Value IS NULL returns TRUE when the value is NULL. Value IS NOT NULL returns TRUE when the value is not NULL. truth tables Progression The value of logical expressions containing NULL operands is defined in truth tables. 2.7.1 Null values. 2.8 Inserting, updating, and deleting rows INSERT The INSERT statement adds rows to a table. INSERT INTO The INSERT INTO clause names the table and columns where data is to be added. The keyword INTO is optional. VALUES The VALUES clause specifies the column values to be added. DEFAULT The optional DEFAULT keyword and default value follow the column name and data type in a CREATE TABLE statement. The https://learn.zybooks.com/zybook/WGUD426v2/content-explorer/print 10/39 12/31/23, 4:54 PM zyBooks UPDATE column is assigned the default value, rather than NULL, when omitted from an INSERT statement. The UPDATE statement modifies existing rows in a table. SET The UPDATE statement uses the SET clause to specify the new column values. DELETE The DELETE statement deletes existing rows in a table. FROM The FROM keyword is followed by the table name whose rows are to be deleted. TRUNCATE The TRUNCATE statement deletes all rows from a table. Progression 2.8.1 Inserting, updating, and deleting rows. 2.9 Primary keys primary key A primary key is a column, or group of columns, used to identify a row. simple primary key A simple primary key consists of a single column. composite primary key A composite primary key consists of multiple columns. Minimal Minimal. All primary key columns are necessary for uniqueness. When any column is removed, the resulting simple or composite column is no longer unique. PRIMARY KEY The PRIMARY KEY constraint in a CREATE TABLE statement names the table's primary key. The PRIMARY KEY constraint ensures that a column or group of columns is always unique and non-null. auto-increment column An auto-increment column is a numeric column that is assigned an automatically incrementing value when a new row is inserted. AUTO_INCREMENT The AUTO_INCREMENT keyword defines an auto-increment column. AUTO_INCREMENT follows the column's data type in a CREATE TABLE statement. Progression 2.9.1 Primary keys. https://learn.zybooks.com/zybook/WGUD426v2/content-explorer/print 11/39 12/31/23, 4:54 PM zyBooks 2.10 Foreign keys foreign key A foreign key is a column, or group of columns, that refer to a primary key. Referential integrity Referential integrity requires foreign key values must either be NULL or match some value of the referenced primary key. FOREIGN KEY / REFERENCES A foreign key constraint is added to a CREATE TABLE statement with the FOREIGN KEY and REFERENCES keywords. Progression 2.10.1 Foreign key constraints. 2.11 Referential integrity fully NULL A fully NULL foreign key is a simple or composite foreign key in which all columns are NULL. Referential integrity Referential integrity is a relational rule that requires foreign key values are either fully NULL or match some primary key value. RESTRICT RESTRICT rejects an insert, update, or delete that violates referential integrity. SET NULL SET NULL sets invalid foreign keys to NULL. SET DEFAULT SET DEFAULT sets invalid foreign keys to the foreign key default value. CASCADE CASCADE propagates primary key changes to foreign keys. ON UPDATE / ON DELETE Actions are specified in the optional ON UPDATE and ON DELETE clauses of the FOREIGN KEY constraint. ON UPDATE and ON DELETE are followed by either RESTRICT, SET NULL, SET DEFAULT, or CASCADE. Progression 2.11.1 Referential integrity. 2.12 Constraints constraint A constraint is a rule that governs allowable values in a database. Constraints are based on relational and business rules, and implemented with special keywords in a CREATE TABLE https://learn.zybooks.com/zybook/WGUD426v2/content-explorer/print 12/39 12/31/23, 4:54 PM zyBooks column constraint statement. The database automatically rejects insert, update, and delete statements that violate a constraint. A column constraint appears after the column name and data type in a CREATE TABLE statement. Column constraints govern values in a single column. table constraint A table constraint appears in a separate clause of a CREATE TABLE statement and governs values in one or more columns. UNIQUE The UNIQUE constraint ensures that values in a column, or group of columns, are unique. CHECK The CHECK constraint specifies an expression on one or more columns of a table. The constraint is violated when the expression is FALSE and satisfied when the expression is either TRUE or NULL. CONSTRAINT Table constraints may be named using the optional CONSTRAINT keyword, followed by the constraint name and declaration. Progression 2.12.1 Constraints. 3. Complex Queries 3.1 Special operators and clauses IN The IN operator is used in a WHERE clause to determine if a value matches one of several values. BETWEEN The BETWEEN operator provides an alternative way to determine if a value is between two other values. The operator is written value BETWEEN minValue AND maxValue and is equivalent to value >= minValue AND value <= maxValue. LIKE The LIKE operator, when used in a WHERE clause, matches text against a pattern using the two wildcard characters % and _. BINARY The LIKE operator performs case-insensitive pattern matching by default or case-sensitive pattern matching if followed by the BINARY keyword. DISTINCT The DISTINCT clause is used with a SELECT statement to return only unique or 'distinct' values. https://learn.zybooks.com/zybook/WGUD426v2/content-explorer/print 13/39