CIND110 – Data Organization for Data Analysts Lab 03 – Basic SQL Operations
1 Running the MySQL Service
2 Creating a Database
3 Designing Tables
4 Inserting/Removing Records/Rows
5 Altering Attributes/Columns
6 Updating Data
7 Cloning Tables
8 Integrity Constraints
11 13 16 17
CIND110 – Lab 03
• Check the status of the MySQL service using the following Linux command from the Ter- minal application.
sudo service mysql status
• After ensuring that the MySQL service is active, run the following Linux command to start the MySQL service
mysql –u root -p
CIND110 – Lab 03
Running the MySQL Service
CIND110 – Lab 03
To add and manipulate a structured/rectangular dataset, we need first to model/define a rela- tional database/schema. An SQL database/schema is identified by a schema name and an au- thorization identifier. Each schema is described by a set of tables, relations, types, constraints and views. You must also decide on the structure of each table: the number of columns/at- tributes, the type of data each column may hold, whether the tables/entities will be indexed, and several other factors. There are a few basic things to decide when creating a structure for your relational database:
– The number of tables to include in your database, as well as the table names.
– Foreachtable/entity,thenumberofcolumns/attributesitshouldcontainandtheirnames.
– For each attribute, what kind of data is to be stored.
– Forbinaryrelationships,whatarethecardinalityratios((1:1,1:N,M:N)thatdetailsthe meaning of the entities involved in the database.
Creating a Database
To create an SQL database, use the SQL statement CREATE DATABASE before typing the name of the database. In this example, we will create a sample database of Ontario Birds that includes some of the bird species recorded in the Canadian province of Ontario. We will name this database RookeryDB. Keywords such as SCHEMA and DATABASE are often used interchangeably.
CREATE DATABASE RookeryDB;
The CREATE DATABASE SQL statement creates an empty database and sets up a place to add
tables that in turn hold data.
To display the list of all the databases that exist on a particular server, you can use the fol- lowing SQL statement
SHOW DATABASES;
To delete the RookeryDB database, you can use the DROP DATABASE SQL statement.
DROP DATABASE RookeryDB;
The results below show the RookeryDB database and other databases that were previously created or already existed on the working machine/server (Results might differ from one machine to another.)
The next step for structuring a database is to create the involved entities/tables. To create a table called Bird, run the following SQL statement under the MySQL shell:
CIND110 – Lab 03
Designing Tables
USE RookeryDB;
CREATE TABLE Bird(bird_id INT AUTO_INCREMENT PRIMARY KEY,
scientific_name VARCHAR(255) UNIQUE, common_name VARCHAR(50) , family_sci_name VARCHAR(255), brief_description TEXT,
bird_image BLOB);
TheaboveSQLstatementcreatesthetableBirdwithsixattributes/fields/columns,withcom- mas separating the information about each column. The AUTO_INCREMENT option automat- ically increments the value of these fields starting with the integer one, unless we specify a different number (e.g. AUTO_INCREMENT = 12).
To see how the table is structured, use the DESCRIBE keyword, as it displays the information about the columns of a table but not the stored data.
DESCRIBE RookeryDB.Bird;
CIND110 – Lab 03
• Here is a detailed description of the obtained results:
– TheFieldcolumncontainstheattributesofthetableBirdwhichbelongstotheRookeryDB database.
– TheTypecolumnliststherespectivedatatypeforeachattributeintheBirdTable.The Character-string Types are either fixed length, CHAR(n), or varying length, VARCHAR(n), where n is the number of characters, and the attributes with a data type of TEXT has a fixed max size of 216 − 1 characters. On the other hand, the Numeric data types include integer numbers of various sizes: INTEGER or INT (4 Bytes), and SMALLINT (2 Bytes),andfloating-pointnumbersofvariousprecision:FLOAT or REAL, and DOUBLE PRECISION.
– The third column in the results, Null, specifies whether each attribute can contain un- known/unavailable/inapplicable values. The NULL logical value is used to represent missing values, and it is different from blank or empty content in a field.
– The fourth column, Key, specifies the nature of the field as a key attribute in the Bird table. As you can see, the bird_id field has been set to be a primary key, shortened to PRI, to distinguish the rows in the Bird table. Notably, the default NULL value of the bird_id attribute will be overridden as the bird_id attribute is a primary key. The scientific_name field has been set to be a unique key, which is abbreviated UNI.
CIND110 – Lab 03
– Thenext-to-lastcolumnintheresultsdisplay,Default,wouldcontainthedefaultvalue which has been set for each attribute.
– Thelastcolumn,Extra,providesanyextrainformationthetablehasforeachattribute. You can see that the values for bird_id will be incremented automatically.
• Besides the DESCRIBE statement, there is another way to look at how MySQL structures a particular table. You can use the SHOW CREATE TABLE SQL statement to display the design of the attributes and other settings assumed by the server: ones that you might not have specified while creating that table.
• Tables can be renamed or entirely removed from a particular database using the RENAME TABLE … To … or the DROP TABLE statements, respectively.
USE RookeryDB;
SHOW CREATE TABLE Bird \G
Inserting/Removing Records/Rows
• Tocreatetheinvolvedentities/tablesanddeclarethedatatypeoftherespectiveattributes,we can add records/rows using the INSERT INTO SQL statement.
• The following MySQL commands will change the default database, add seven records to the Bird table, then fetch all the records of the Birds and display them.
CIND110 – Lab 03
USE RookeryDB;
INSERT INTO Bird(common_name, scientific_name) VALUES(‘Purple finch’, ‘Haemorhous purpureus’),
(‘Dark-eyed junco’ , ‘Junco hyemalis’),
(‘Killdeer’, ‘Charadrius vociferus’),
(‘Northern saw-whet owl’, ‘Aegolius acadicus’),
(‘Tree swallow’, ‘Tachycineta bicolor’),
(‘Eastern bluebird’, ‘Sialia sialis’),
(‘Mute swan’, ‘Cygnus olor’); SELECT * FROM Bird;
• On the other hand, the DELETE FROM clause can be used to remove a record or a set of records that shares a common criterion. The following command will delete all the records where the common_name value include the hyphen punctuation mark.
CIND110 – Lab 03
• As you can see, the values have been inserted in the same order in which the corresponding attributes were specified in the INSERT INTO command. The inserted values must include all attributes with NOT NULL specification or no default value. Attributes with NULL value allowed or DEFAULT values are the ones that can be left out. In this example, the bird_id, family_sci_name, and brief_description and bird_image attributes have not explicitly been specified in the INSERT INTO statement, and hence the system generates their records’ values based on what has been set in the CREATE TABLE statement.
• TheothertwotablesthatwestillneedtocreateintheRookeryDBdatabaseareBird_Family and Bird_Order. According to the taxonomic classification structure, birds are organized into groups based on their physiological and genetic similarities. The class of birds is split into orders with similar characteristics, and the orders are further divided into families.
USE RookeryDB;
CREATE TABLE Bird_family(family_id INT AUTO_INCREMENT PRIMARY KEY,
scientific_name VARCHAR(255) UNIQUE, brief_description TEXT, order_sci_name VARCHAR(255));
DESCRIBE Bird_family;
CREATE TABLE Bird_Order(order_id INT AUTO_INCREMENT PRIMARY KEY,
DESCRIBE Bird_Order;
scientific_name VARCHAR(255) UNIQUE , brief_description TEXT)
DEFAULT CHARSET=UTF8MB4;
CIND110 – Lab 03
• We have already included the family_sci_name attribute into the Bird table so it can be tied later to the Bird_Family table. Similarly, the order_sci_name attribute has been con- sidered while creating the Bird_Family table so that it can be tied to the Bird_Order table.
• For the attributes with images, the data type that can be used is BLOB. It stands for Binary Large OBject, and we can store an image file, such as a JPEG or a PNG file, as a value after converting it to a series of 0s and 1s. However, this process might enlarge the table size and can be a problem when backing up or migrating the database. It would be best to store the image files on a separate machine/server and instead store the file path or the URL address pointing to where the image file is located.
• We have also included the default character set to be used when creating the columns for the Bird_Order table. We will be applying the UTF8MB4 as some of the names might include characters that are not part of the default latin1 character set.
CIND110 – Lab 03
• The following EER diagram would be the output of the reverse engineering step of the RookeryDB database.
Altering Attributes/Columns
USE RookeryDB;
ALTER TABLE Bird ADD COLUMN last_seen_dt DATETIME
DESCRIBE Bird;
AFTER brief_description;
Thefollowingcommandsaddanotherattributenamedlast_seen_dtofdatetypeDATETIME to the Bird table right after the brief_description attribute, and then display the final design of the Bird table.
You can also add more than one column or changing the characteristics of an attribute using the CHANGE COLUMN keyword. The second command of the following commands will add three new attributes to the Bird table and adjust the data type of the common_name attribute to VARCHAR(255) rather than VARCHAR(50). In the CHANGE COLUMN keyword, notice that we listed the name of the attribute twice. The first time is to name the column that is to be changed, and the second time is to provide the new name if we want to change or rename it.
CIND110 – Lab 03
CIND110 – Lab 03
USE RookeryDB;
ALTER TABLE Bird ADD COLUMN body_id CHAR(2) AFTER last_seen_dt,
ADD COLUMN nail_beak BOOLEAN DEFAULT 0 AFTER body_id, ADD COLUMN bird_status ENUM(‘Accidental’,
‘Breeding’,
‘Extinct’),
CHANGE COLUMN common_name common_name VARCHAR(255);
DESCRIBE Bird; SELECT * FROM Bird;
6 Updating Data
• The data inside the Bird table can be modified using the UPDATE statement. In this example, we will modify the bird_status values for a particular subset of birds.
• Note that updating an attribute that is neither part of a primary key nor part of a foreign key usually causes no problems, as modifying a primary key value is similar to deleting one record and inserting another in its place and that might introduce redundant values which violates the characteristics of a primary key. For example updating the values of the bird_id might raise a Duplicate entry error.
CIND110 – Lab 03
USE RookeryDB;
SELECT * FROM Bird;
UPDATE Bird SET bird_status = ‘Breeding’ WHERE bird_id IN(1,2,4,5); SELECT * FROM Bird;
USE RookeryDB;
UPDATE Bird SET bird_id = 4 WHERE bird_status = ‘Breeding’;
CIND110 – Lab 03
• The MODIFY keyword can be used to change the design of an attribute. In the following example we will be adding more categories to the bird_status attribute that exist in the Bird table. The LIKE operator with the % wildcard at the beginning of the string status will display any attribute ends with the string status.
USE RookeryDB;
ALTER TABLE Bird MODIFY COLUMN bird_status ENUM(‘Accidental’,
SHOW COLUMNS FROM Bird LIKE ‘%status’ ;
‘Breeding’,
‘Extinct’,
‘Extirpated’,
‘Introduced’);
CIND110 – Lab 03
• The default value can be removed with the DROP operator. In this example, we will remove the default value of the nail_beak attribute which is the integer 0 or the False logical value. Note that the default value will be NULL if any record is inserted in future. If you would like to reset the default value to another, you can use the SET DEFAULT keyword.
USE RookeryDB;
DESCRIBE Bird;
ALTER TABLE Bird ALTER nail_beak DROP DEFAULT; DESCRIBE Bird;
INSERT INTO Bird(common_name, bird_status)
VALUES(‘Canada goose’, ‘Breeding’); SELECT * FROM Bird;
• The following command creates a duplicate of the Bird table structure without copying the included data.
• Tocopytheinvolveddataorrecordsthefollowingcommandcanbeused.Notably,thedesign of the table might not be copied with this command.
CIND110 – Lab 03
Cloning Tables
USE RookeryDB;
CREATE TABLE Bird_Copy LIKE Bird; SHOW TABLES;
SELECT * FROM Bird_Copy;
DESCRIBE Bird_Copy;
USE RookeryDB;
CREATE TABLE Bird_Deep_Copy SELECT * FROM Bird; SHOW TABLES;
SELECT * FROM Bird_Deep_Copy;
DESCRIBE Bird_Copy;
CIND110 – Lab 03
Integrity Constraints
While the entity integrity constraint states that no primary key value can be NULL and only specified on individual tables, the referential integrity constraint states that a record in one table that refers to another table must refer to an existing record in that table. For example, the attribute family_sci_name of Bird gives the family scientific name for which each bird belongs; hence, its value in every Bird record must match the scientific_name value of some record in the Bird_family table.
USE RookeryDB;
ALTER TABLE Bird
ADD CONSTRAINT fk_family_sci_name FOREIGN KEY(family_sci_name) REFERENCES Bird_family(scientific_name);
SHOW CREATE TABLE Bird\G
USE RookeryDB;
ALTER TABLE Bird_family
ADD CONSTRAINT fk_order_sci_name FOREIGN KEY (order_sci_name) REFERENCES Bird_Order(scientific_name);
SHOW CREATE TABLE Bird_family\G
CIND110 – Lab 03
CIND110 – Lab 03
• The following logical model shows that the Bird table can be tied to the Bird_Family ta- ble through the family_sci_name as a foreign key and and the scientific_name of the referenced table Bird_Family as the referenced column. Similarly, the Bird_Family table can be tied to the Bird_Order table through the order_sci_name as a foreign key and the scientific_name of the referenced table Bird_Order as the referenced column.
This is the end of lab3 , PhD