Chapter 6: Advanced SQL and MySQL PDF

Chapter 6 Advanced to SQL and MySQL   Database design  Why we need to design database?  Ensures viability, performance and reliability of dbs.  Crucial process for successful long-term management of information  Process normalization will eliminate...

Chapter 6 Advanced to SQL and MySQL   Database design  Why we need to design database?  Ensures viability, performance and reliability of dbs.  Crucial process for successful long-term management of information  Process normalization will eliminate redundancies and others problem that will undermine the integrity of your data.  Normalization  A series of rules which help define organization of db.  You need to define the role of the application before begin normalizing db.  You need also to understand how the information will be accessed  Keys – pieces of data that help to identify a row of information in a table.  2 types of keys:  Primary key -> unique identifier  Foreign key -> representation of the primary key.  Primary key  Unique identifier and must follow 3 important rules:  Always have a value  Have a value that remains the same (never change)  Have a unique value for each record in the table.  Example: StudentID Student  To assign primary key in a table StudentID  Look for fields that.. StudentName 1. Always have value StudentEmail 2. The value remains same 3. Uniquely identify the record StudentAddress  Foreign key  Representation of the primary key from table A in table B.  Example:  Table Student -> studentID as primary key  Table Teacher -> teacherID as primary key and studentID as foreign key Student Teacher PK StudentID PK TeacherID Primary key StudentName FK StudentID Primary key StudentEmail TeacherName Foreign key StudentAddress TeacherEmail  Relationships  How the data in one table relates to the data in another table.  Relationship can be..  One to one  If one and only one item in Table A applies to one and only one item in Table B.  Eg : Each student have one studentID and each StudentID number applies to only one student.  One to many  If one item in Table A can apply to multiple item in Table B.  Eg : Male and female terms apply to many student, but each person can be only one or the other.  Many to many  If multiple items in Table A can apply to multiple items in Table B.  Eg : A student can take many courses and a course can have many students. Relationship in database modeling schemes Entity 1:1 Entity Entity 1:M Entity Entity M:M Entity Relationships and keys work together in that a key in one table will normally relate to a field in another table.  Database Normalization: First Normal Form (1NF)  Each column must contain only one value  Steps to check structure of 1NF compliance: 1. Identify any field that contains multiple pieces of information 2. Break up any fields found in step 1 into a distinct field. 3. Double check that all new fields created in step 2 pass the 1NF. Table :URL Content (unnormalised) Item Value URLID 1 URL www.php.net Date submitted 20/02/2006 Approved Yes URL title PHP: Hypertext Processor URL description The home page.. Identify any field that contains URL type General PHP, Programming, multiple pieces of Web Development info  Unnormalised 1NF URL Content Item Value URLID 1 URL www.php.net Date submitted 20/02/2006 Approved Yes Break up any URL title PHP: Hypertext fields found in Processor step 1 into a URL description The home page.. distinct field. URL type General PHP  1NF compliant 1 URL Content Item Value Value Value URL ID 1 2 3 URL www.php.net www.php.net www.php.net Date submitted 22/02/2006 20/02/2006 23/02/2006 Approved Yes No Yes URL title PHP: Hypertext PHP: Hypertext PHP: Hypertext Processor Processor Processor URL description The home page.. The home page.. The home page.. URL type General PHP Programming Web Development  Second Normal Form (2NF)  Must be INF compliant  All columns whose values are the same across multiple rows must be turned into their own table and related back.  Steps to check structure of 2NF compliant: 1. Identify any fields that could have repeating values 2. Create new tables accordingly 3. Assign or create new primary keys 4. Create the requisite foreign key that link the relationship. Incorporate foreign keys to identify how all of the data and tables are associated. A primary key in one table will most likely be a foreign key in another.  1NF2NF Identify any 1 URL Content fields that could have Item Value Value Value repeating URL ID 1 2 3 values URL www.php.net www.php.net www.php.net Date submitted 22/02/2006 20/02/2006 23/02/2006 Approved Yes No Yes URL title PHP: PHP: PHP: Hypertext Hypertext Hypertext Processor Processor Processor URL The home The home The home description page.. page.. page.. URL type General PHP Programming Web Development  1NF2NF 2 URL titles Item Value URLs URL www.php.net Item Value Title PHP: URL ID 1 Hypertext Date 22/02/2006 Processor submitted Description The home Approved Yes page.. Type General PHP Create new tables accordingly  1NF2NF 3 URL titles URLs Item Value Item Value URL title ID 1 URL ID 1 URL www.php.net Date 22/02/2006 submitted Title PHP: Hypertext Approved Yes Processor Description The home page.. Assign or Type General PHP create new primary keys  2NF Compliant 4 URL titles URLs Item Value Item Value URL title ID 2 URL ID 1 URL www.php.net URL title ID 2 Title PHP: Date 22/02/2006 Hypertext submitted Processor Approved Yes Description The home page.. Type General PHP Create the requisite foreign key that link the relationship  Third Normal Form (3NF)  Must be 2NF compliant  Every nonkey column is dependent upon the primary key.  Steps to check structure of 3NF compliant: 1. Identify any fields that do not relate directly to the primary key. 2. Create new tables accordingly 3. Assign or create new primary keys 4. Create the requisite foreign keys that link any of the relationship.  2NF3NF 1 URL titles URLs Item Value Item Value URL title ID 2 URL ID 1 URL www.php.net URL title ID 2 Title PHP: Date 22/02/2006 Hypertext submitted Processor Approved Yes Description The home page.. The URL type field is Type General PHP, not specific to each Programming, record. Web Development  2NF3NF 2 URL titles URLs Item Value Item Value URL title 2 URL ID 1 ID URL title ID 2 URL www.php.net Date 20/02/2006 submitted Title PHP: Hypertext Approved Yes Processor Description The home page.. URL Types Item Value Create new tables Type General PHP accordingly  2NF3NF 3 URL titles URLs Item Value Item Value URL title 2 URL ID 1 ID URL title ID 2 URL www.php.net Date 20/02/2006 Title PHP: submitted Hypertext Approved Yes Processor Description The home URL Types page.. Item Value Assign or create new Type ID 2 primary keys Type General PHP  3NF Compliant 4 URLs Item Value URL titles URL ID 1 Item Value URL title ID 2 URL title 2 Type ID 2 ID Date 20/02/2006 URL www.php.net submitted Title PHP: Approved Yes Hypertext Processor Description The home page.. URL Types Item Value Create the requisite foreign Type ID 2 keys that link any of the relationship. Type General PHP  Summary  Normalization – The process of creating more and more tables until potential redundancies have been eliminated  Database normalization  1st normal form  2nd normal form  3rd normal form  2 types of keys  Primary key  Foreign key  Relationship – how table relate from one and another  1 to 1  1 to many  Many to many  Exercise : Normalization The picture can't be displayed. ClientRental unnormalized table  Creating database  Identify the column types and set the column names  MySQL flexible on name of databases, tables and columns which are:  Use alphanumeric characters and the underscore to separate words  Limit to fewer than 64 characters  End primary and foreign key columns with id or pk and fk  List the primary key first in a table, followed by foreign keys.  Field names should be unique across every table, except for the keys.  Create database, table and columns definitions  CREATE DATABASE unikl_calendar;  CREATE TABLE calendar_activity ( id INT(11) NOT NULL AUTO_INCREMENT, title VARCHAR(255) NOT NULL DEFAULT, user_id INT(11) NOT NULL DEFAULT '0', date DATETIME DEFAULT NULL, allday ENUM('y','n') DEFAULT 'n', start_activity DATETIME default '0000-00-00 00:00:00', duration DATETIME DEFAULT '0000-00-00 00:00:00', text BLOB, PRIMARY KEY (id) )  Performing joins  A special query statement to retrieve the information.  Joins – SQL queries performed by cross referencing tables  Use to extract more usable data from relational database  Several types of joins  Inner join  Outer or left join  Joins query  Inner join  Will retrieve all of the information from both the table A and table B only where a match is made.  Example query:  SELECT * FROM urls, url_types WHERE urls.type_id = url_types.type_id;  Retrieve all information from both urls and url_types tables wherever a urls.type_id is the same as the url_types.type_id.  When selecting from multiple tables and columns, use the dot syntax (table.column) if they have columns with same name. Example inner join  SELECT url_id, type FROM urls AS u, url_types AS t WHERE u.type_id = t.type_id;  Joins query  Outer or left join  Could return records not matched by a conditional only where a match is made.  Example query:  SELECT * FROM url_types LEFT JOIN urls ON urls.type_id = url_types.type_id;  Retrieve all of the url_types record and urls information, if a match is made.  If both tables have same column name, simplify the query into:  SELECT * FROM url_types LEFT JOIN urls USING (type_id); Example left join  SELECT url_id,type FROM url_types LEFT JOIN urls ON urls.type_id =url_types.type_id;  Using functions  Used with SQL queries to format and alter the returned data.  To use any function, need to modify query to specify to which column or columns the function should be applied.  Example:  SELECT FUNCTION(column) FROM tablename;  SELECT *, FUNCTION(column) FROM tablename;  SELECT column1, FUNCTION(column2),column3 FROM tablename; 4 types of SQL functions in MySQL text function date and SQL numeric time functions Functions functions grouping functions  Text functions  CONCAT() – most useful of the text functions, use for concatenation.  The syntax require to place within parentheses, the various values want assembled, in order and separated by commas.  Syntax query:  SELECT CONCAT(column1,column2) FROM table;  Example:  SELECT CONCAT(last_name, ‘, ‘ first_name) FROM users;  SELECT CONCAT(last_name, ‘, ‘ first_name) as name FROM users;  Text functions  Others text function  LENGTH()-> return the length of the value stored in the column  TRIM() -> trim excess spaces from the beginning and end of stored value.  UPPER() -> capitalize the entire string stored string  LOWER()-> turns the stored string into an all-lowercase format.  Numeric Functions  To formatting and performing calculations on numeric values.  FORMAT()  Turns any number into more conventionally formatted layout.  Example:  SELECT CONCAT(‘$’, FORMAT (5639.6,2)) AS cost;  Output: $5,639.60  Numeric Functions  ROUND()  Take one value, presumably from a column and round that to a specified number of decimal places  If no decimal places are indicated, it will round the number to the nearest integer.  Example:  SELECT ROUND(5639.6) AS cost;  Output: 5640  RAND()  Used for returning random order of data.  Example:  SELECT * FROM table ORDER BY RAND();  Output : returned rows in random  Date and time functions  MySQL have flexible date and time functions  Range from returning portions of a date column to those that return the current date or time. Function Usage Purpose HOUR() HOUR(column) Returns just the hour value of a stored date MINUTE() MINUTE(column) Returns just the minute value of a stored date SECOND() SECOND(column) Returns just the second value of a stored date DAYNAME() DAYNAME(column) Returns the name of the day for a date value MONTH() MONTH(column) Return just the numerical month value of a stored date ADDDATE() ADDDATE(column, INTERVAL Return the value of x units added to x type) column  Example syntax of date and time functions  The syntax  SELECT DATE_FUNCTIONS (date_column) FROM tablename;  Example  SELECT HOUR(datetime) FROM calendar;  SELECT DAYNAME(datetime) FROM calendar;  SELECT MINUTE(datetime) FROM calendar;  SELECT MONTH(datetime) FROM calendar;  ADDATE() and SUBDATE()  Synonyms for DATE_ADD() and DATE_SUB() perform calculations upon date values  Syntax:  ADDATE(date, INTERVAL x type)  Example:  ADDATE (date, INTERVAL 2 hour)  To add two hours to a date  SUBDATE(date, INTERVAL ‘1-3’ YEAR_MONTH)  To subtract one year and three month to the value stored in the date column.  Formatting the date and time  DATE_FORMAT()  To format both date and time if a value contains both  Example : YYYY-MM-DD HH:MM:SS  TIME_FORMAT()  To format only time value and must be used if only time value is being stored  Example : HH:MM:SS  The syntax  SELECT DATE_FORMAT(date_column,’formatting’) FROM tablename;  Formatting – combinations of key codes and the percent sign to indicate what values returned.  DATE_FORMAT() and TIME_FORMAT() parameters TERM USAGE EXAMPLE %e Day of the month 1-31 %d Day of the month, two digit 01-31 %D Day with suffix 1st -31st %W Weekday name Sunday –Saturday %a Abbreviated weekday name Sun-Sat %c Month number 1-12 %m Month number, two digit 01-12 %M Month name January – December %b Month name, abbreviated Jan-Dec %Y Year 2002 %y Year 02  Grouping functions  Works in query statement to group returned data into similar blocks of information.  Example:  SELECT * FROM urls GROUP BY type_id  Returned just the specific itemized records  Can apply combination of WHERE, ORDER BY and LIMIT conditions to a GROUP BY  Example:  SELECT column FROM table WHERE clause GROUP BY column ORDER BY column LIMIT x;  Indexes  A special system that database use to improve the overall performance.  MySQL creates extra files to store and track indexes efficiently.  Indexes best used on columns:  That are frequently used in the WHERE part of a query  That are frequently used in an ORDER BY part of a query  That are frequently used as the focal point of a join  That have many different values  MySQL has three types of indexes:  INDEX  UNIQUE  PRIMARY KEY  Example indexes ALTER TABLE url_types ADD UNIQUE (type); ALTER TABLE url_types ADD INDEX (title_id), ADD INDEX (type_id), ADD INDEX (date_submitted);  Summary: Recap  Performing Joins  Inner join  Outer or Left join  MySQL functions  Text functions  Numeric functions  Date and time functions  Grouping functions  Indexes  Exercise Write SQL statement for : Use Join SQL statement to select title, date, username, firstName from table user and calendar_activity. Use date formatting syntax to show only ‘YYYY-MM-DD’ from calendar_activity column date. Table: calendar_activity Table: user  Exercise Table: user Write SQL statement for : Use text functions to select combine firstname and lastname into one single column from table user.