1 / 49

Data Definition Using MySQL

Data Definition Using MySQL. Many features are covered using ISO SQL standard. All the examples are done using MySQL. SQL identifiers are used to identify objects in the database, such as table names and columns.

chico
Download Presentation

Data Definition Using MySQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Data Definition Using MySQL • Many features are covered using ISO SQL standard. • All the examples are done using MySQL. • SQL identifiers are used to identify objects in the database, such as table names and columns. • The characters that can be used in a user-defined SQL identifier must appear in a character set. • The ISO standard provides a default character set, which consists of uppercase letters A…Z, the lowercase letters a…z, the digits 0…9, and the underscore (_) character. tMyn

  2. The following restrictions are imposed on an identifier: • An identifier must start with a letter • An identifier cannot contain spaces • Character data consists of a sequence of characters from an implementation-defined character set. • The format for specifying a character data type is: CHARACTER [VARYING][length] CHARACTER can be abbreviated to CHAR and CHARACTER VARYING to VARCHAR tMyn

  3. A character string may be defined as having a fixed or varying length. • If the string is defined to be a fixed length and we enter a string with fewer characters than this length, the string is padded with blanks on the right to make up the required size. • If the string is defined to be of varying length and we enter a string with fewer characters than this length, only those characters entered are stored, thereby using less space. tMyn

  4. The exact numeric data type is used to define numbers with an exact representation. • The number consists of digits, an optional decimal point, and an optional sign. • An exact numeric data type consists of a precision and scale. • The precision gives the total number of significant decimal digits, that is, the total number of digits, including decimal places but excluding the point itself. • The scale gives the total number of decimal places. tMyn

  5. There are several ways of specifying an exact numeric data type, for example: NUMERIC [precision[, scale]] DECIMAL [precision[, scale]] INTEGER • INTEGER can be abbreviated to INT. tMyn

  6. The approximate numeric data type is used for defining numbers that do not have an exact representation, such as real numbers. • Approximate numeric, or floating point, notation is similar to scientific notation, in which a number is written as a mantissa times some power of ten (the exponent). • There are several ways of specifying an approximate numeric data type, for example: FLOAT [precision] REAL DOUBLE PRECISION • The precision controls the precision of the mantissa. tMyn

  7. The datetime data type is used to define points in time to a certain degree of accuracy. Examples are dates, times and times of day. • There are several types of datetime data type: DATE TIME [timePrecision] [WITH TIME ZONE] TIMESTAMP [timePrecision] [WITH TIME ZONE] • DATE is used to store calendar dates using the YEAR, MONTH, and DAY fields. tMyn

  8. TIME is used to store time using the HOUR, MINUTE, and SECOND fields. • TIMESTAMP is used to store date and times. • The timePrecision is the number of decimal places of accuracy to which the SECOND field is kept. tMyn

  9. Integrity control consists of constraints that we wish to impose in order to protect the database from becoming inconsistent. • We consider five types of integrity constraints: • Required data • Domain constraints • Entity integrity • Referential integrity • General constraints • These constraints can be defined in the CREATE and ALTER TABLE statements. tMyn

  10. Required data • Some columns must contain a valid value, they are not allowed to contain nulls. • A NULL is distinct from blank or zero, and is used to represent data that is either not available, missing, or not applicable. • The standard provides the NOT NULL column specifier in the CREATE and ALTER TABLE statements to provide this type of constraint. • When NOT NULL is specified, the system rejects any attempt to insert a NULL in the column. • The default is NULL. tMyn

  11. Domain constraints • Every column has a domain, in other words, a set of legal values. • For example, the sex of a member of staff is either ‘M’ or ‘F’, so the domain of the column sex of the Staff table is a single character string consisting of either ‘M’ or ‘F’. • The standard provides two mechanisms for specifying domains, the first being the CHECK clause: CHECK (searchCondition) tMyn

  12. To ensure that the column sex can be specified only as ‘M’ or ‘F’, we could define the column as: sex CHAR(1) NOT NULL CHECK (sex IN (‘M’, ‘F’)) tMyn

  13. Entity integrity • The primary key of a table must contain a unique, nonnull value for each row. • The standard supports entity integrity with the PRIMARY KEY clause in the CREATE and ALTER TABLE statements. • To define a composite primary key, we specify multiple column names in the PRIMARY KEY clause, separating each by a comma. tMyn

  14. Referential integrity • A foreign key is a column, or set of columns, that link each row in the child table containing the foreign key to the row of the parent table containing the matching candidate key value. • Referential integrity means that, if the foreign key contains a value, that value must refer to an existing, valid row in the parent table. • The standard supports the definition of foreign keys with the FOREIGN KEY clause in the CREATE and ALTER TABLE statements. tMyn

  15. SQL rejects any INSERT or UPDATE operation that attempts to create a foreign key value in a child table without a matching candidate key value in the parent table. • The action SQL takes for any UPDATE or DELETE operation that attempts to update or delete a candidate key value in the parent table that has some matching rows in the child table is dependent on the referential action specified using the ON UPDATE and ON DELETE subclauses of the FOREIGN KEY clause. • When the user attempts to delete a row from a parent table, and there are one or more matching rows in the child table, SQL supports four options regarding the action to be taken: tMyn

  16. CASCADE: Delete the row from the parent table and automatically delete the matching rows in the child table. Because these deleted rows may themselves have a candidate key that is used as a foreign key in another table, the foreign key rules for these tables are triggered, and so on in a cascading manner. • SET NULL: Delete the row from the parent table and set the foreign key value(s) in the child table to NULL. This option is valid only if the foreign key columns do not have the NOT NULL qualifier specified. tMyn

  17. SET DEFAULT: Delete the row from the parent table and set each component of the foreign key in the child table to the specified default value. This option is valid only if the foreign key columns have a DEFAULT value specified. • NO ACTION: Reject the delete operation from the parent table. This is the default setting if the ON DELETE rule is omitted. tMyn

  18. SQL supports the same options when the candidate key in the parent table is updated. • With CASCADE, the foreign key value(s) in the child table are set to the new value(s) of the candidate key in the parent table. In the same way, the updates cascade if the updated column(s) in the child table reference foreign keys in another table. tMyn

  19. General constraints • Updates to tables may be constrained by enterprise rules governing the real-world transactions that are represented by the updates. tMyn

  20. The process of creating a database differs significantly from product to product. In multi-user systems, the authority to create a database is usually received for the DBA. In a single-user system, a default database may be established when the system is installed and configured and others can be created by the user as and when required. • According to the standard, relations and other database objects exist in an environment. Among other things, each environment consists of one or more catalogs, and each catalog consists of a set of schemas. A schema is a named collection of database objects that are in some way related to one another. tMyn

  21. All the objects in a schema have the same owner and share a number of defaults. The schema definition statement has the following form: • CREATE SCHEMA [Name|AUTHORIZATION CreatorIdentifier] tMyn

  22. tMyn

  23. In a generic format the CREATE TABLE syntax looks like follows: tMyn

  24. CREATE TABLE tbl_name (create definition, …) [table_option …] create_definition: col_name column_definition | [CONSTRAINT [symbol]] PRIMARY KEY (index_col_name,...) | [CONSTRAINT [symbol]] FOREIGN KEY (index_col_name,...) reference_definition column_definition: data_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] data_type: | INT[(length)] [UNSIGNED] [ZEROFILL] | INTEGER[(length)] [UNSIGNED] [ZEROFILL] tMyn

  25. | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL] | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL] | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL] | DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL] | NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL] | DATE | TIME | TIMESTAMP | DATETIME | YEAR | CHAR(length) [CHARACTER SET charset_name] | VARCHAR(length) [CHARACTER SET charset_name] index_col_name: col_name [(length)] tMyn

  26. reference_definition: REFERENCES tbl_name [(index_col_name,...)] [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION table_option: [TABLESPACE tablespace_name STORAGE DISK] ENGINE [=] engine_name | [DEFAULT] CHARACTER SET charset_name tMyn

  27. For practical purposes to create the table we use the following basic syntax: CREATE TABLE TableName {(columnName dataType [NOT NULL] [DEFAULT defaultOption]} [PRIMARY KEY (listOfColumns),] {[FOREIGN KEY (listOfForeignKeyColumns) REFERENCES ParentTableName [(listOfCandidateKeyColumns)] [ON UPDATE referentialAction] [ON DELETE referentialAction]} tMyn

  28. Next step: Create two tables and do some tests in order to learn the basic ideas of referential integrity: CREATE TABLE Department (depNu INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(40) NOT NULL, address VARCHAR(30) NULL, CONSTRAINT depPK PRIMARY KEY(depNu)); tMyn

  29. CREATE TABLE Employees (empNu INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, fName VARCHAR(15) NOT NULL, sName VARCHAR(20) NOT NULL, depNu INTEGER UNSIGNED NOT NULL, CONSTRAINT empPK PRIMARY KEY(empNu), CONSTRAINT empFK FOREIGN KEY(depNu) REFERENCES Department(depNu) ON UPDATE CASCADE ON DELETE RESTRICT); tMyn

  30. The ISO standard provides an ALTER TABLE statement for changing the structure of a table once it has been created. • The statement consists of six options to: • Add a new column to a table • Drop a column from a table • Add a new table constraint • Drop a table constraint • Set a default for a column • Drop a default for a column. • The generic format of the statement is: tMyn

  31. ALTER TABLE tbl_name alter_specification [, alter_specification] ... alter_specification: table_option ... | ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ] | ADD [COLUMN] (col_name column_definition,...) | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} | CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name] | MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name] | DROP [COLUMN] col_name | DROP PRIMARY KEY tMyn

  32. | DROP { INDEX | KEY} index_name | DROP FOREIGN KEY fk_symbol | RENAME [TO] new_tbl_name | ORDER BY col_name [, col_name] ... | CONVERT TO CHARACTER SET charset_name | [DEFAULT] CHARACTER SET charset_name column_definition: data_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] data_type: | INT[(length)] [UNSIGNED] [ZEROFILL] | INTEGER[(length)] [UNSIGNED] [ZEROFILL] | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL] | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL] | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL] | DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL] | NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL] | DATE | TIME | TIMESTAMP | DATETIME | YEAR tMyn

  33. | CHAR(length) [CHARACTER SET charset_name] | VARCHAR(length) [CHARACTER SET charset_name] index_col_name: col_name [(length)] reference_definition: REFERENCES tbl_name [(index_col_name,...)] [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION table_option: [TABLESPACE tablespace_name STORAGE DISK] ENGINE [=] engine_name | [DEFAULT] CHARACTER SET charset_name tMyn

  34. And for practical purposes the syntax is: ALTER TABLE Tablename [ADD [COLUMN] columnName dataType [NOT NULL][UNIQUE] [DEFAULT defaultOption][CHECK (searchCondition)]] [DROP [COLUMN] columnName [RESTRICT | CASCADE]] [ADD [CONSTRAINT [ConstraintName]] tableConstraintDefinition] [DROP CONSTRAINT ConstraintName [RESTRICT | CASCADE]] [ALTER [COLUMN] SET DEFAULT defaultOption] [ALTER [COLUMN] DROP DEFAULT] tMyn

  35. To demonstrate the options, let us first create a simple table: CREATE TABLE Aims (aiNu INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, home VARCHAR(40) NOT NULL, work VARCHAR(40) NULL, CONSTRAINT aiPK PRIMARY KEY(aiNu)); tMyn

  36. Let’s add a new column to this table: ALTER TABLE Aims ADD COLUMN freeTime VARCHAR(40) NULL; tMyn

  37. tMyn

  38. Next column will be added just before the column freeTime: ALTER TABLE Aims ADD COLUMN weightTarget INTEGER NOT NULL AFTER work; tMyn

  39. tMyn

  40. Drop the column freeTime: ALTER TABLE Aims DROP COLUMN freeTime; tMyn

  41. tMyn

  42. Add constraint UNIQUE to the column weightTarget with naming (again when the table has already been created): ALTER TABLE Aims ADD CONSTRAINT goodLevel UNIQUE KEY (weightTarget); tMyn

  43. tMyn

  44. To drop the previous constraint: ALTER TABLE Aims DROP KEY goodLevel; tMyn

  45. tMyn

  46. To add default constraint on the home column: ALTER TABLE Aims ALTER COLUMN home SET DEFAULT 'more quality time with the family'; tMyn

  47. tMyn

  48. To drop the previous constraint: ALTER TABLE Aims ALTER COLUMN home DROP DEFAULT; tMyn

  49. tMyn

More Related