Database programming evaluation practical
Data modelling & DB design Dr Heinz Herrmann Week 5 SQL
1
MySQL
MySQL Server is a popular relational DBMS. MySQL is used in many high-profile, large-scale websites and companies
Wikipedia, Google, Facebook, Twitter, Flickr, and YouTube
It is open source, and free for you to use
MySQL Server ships with no GUI tools to administer MySQL databases or manage data contained within the databases
The official set of MySQL front-end tools, MySQL Workbench is actively developed by Oracle, and is also freely available for use
The language we use to interact with a MySQL database is Structured Query Language (SQL)
MySQL installation: Go to https://dev.mysql.com/downloads/installer/
SECTION I
SQL and DDL
SQL
Database
Student table
Unit table
School table
DBMS
(e.g., SQLite)
We may need to Create a database/table, Insert data to the database, and See the data stored in the database.
Hey, create “student” table!
Hey, show me the data stored in student table!
What?
CREATE TABLE Student …..
SELECT * FROM Student
Okay!
Tom Hacks, 101, CS, IFN554
…
DBMSs don’t understand human languages.
SQL is the language DBMSs understand.
Structured Query Language (SQL)
SQL is the formal and de-facto standard language for relational databases. It allows people or systems to communicate with relational databases (like human languages).
There are four categories of SQL commands:
Data Definition Language (DDL): Used for creating, transforming, controlling databases.
Data Manipulation Language (DML): Used for accessing and transforming data.
Data Control Language (DCL): Used for setting up privileges and roles.
Transaction Control Language (TCL): Used for dealing with the transaction within the database
SQL Commands
(https://hackr.io/blog/sql-commands)
SQL Elements and Rules
A simple example of SQL commands:
SELECT person_id, person_name from Person where person_name = ‘Lee’;
The meaning: “retrieve and show the records from the table name ‘Person’ which the column name is ‘person_name’ and the value of the column is ‘Lee’”.
Reserved word: A fixed part of SQL and must be spelt exactly as required and cannot be split across lines: e.g., CREATE, TABLE, SELECT, INDEX, VIEW etc.
User-defined word: Made up by user and represent names of various database objects such as relations, columns, views: e.g., person, person_id, person_name.
Value: The value of a table: e.g., “Lee” (value of person_name).
SQL Data Definition Language (DDL)
Data Definition Language (DDL) commands are for creating, transforming, controlling databases.
There are three main DDL commands:
CREATE: A command for creating a database object (table, index, view, etc.).
ALTER: A command for altering (modifying) an existing database object.
DROP: A command for deleting (removing) a database object.
SQL DDL: Create a Table
Create a table is to store your data to the database. The data tables should be created by CREATE TABLE command.
There are several steps of table creation:
Identify tables and table names (relations)
Identify columns and their data types (attributes and domains)
Identify constraints (NOT NULL, Primary key, Foreign Keys etc.)
Create tables one by one, paying attention to referential constraints (Foreign keys)
SQL DDL: Create a Table
Let’s use the following relational schema to create tables.
SQL DDL: Create a Table
1. Identify tables and table names (relations).
There are two relations, and the populated values. From the schema, we can identify two table names: Movie and Staff.
Table: Movie, Staff
2. Identify columns and their data types (attributes and domains)
Movie and Staff both have three columns as shown in the figure. The columns of the tables are identified as follows
Columns (Movie): Movie_Nr, Movie_Title, Watch
Columns (Staff): Staff_Id, Staff_Name, Movie_Nr
SQL DDL: Create a Table
There are four major classes of predefined datatypes in standard SQL:
the boolean type — used to store true or false values;
the string types — used to store strings of characters such as names (also used to store string of bits, such as bitmaps);
the numeric types — used to store numbers;
the datetime & interval types — used to store dates, times-of-day and periods of time.
Which String Data Type to store strings?
CHAR(n)
To store fixed number of characters
For example, CHAR(10) will store the value “Tom” as “Tom “
Hint: Use CHAR to store short fixed length characters such as Sex (‘M’ or ‘F’), Unit code (‘IFB130’, ‘IFB299’ etc.), StateCode (‘QLD’, ‘NSW’, ‘VIC’ etc.)
VARCHAR(n)
To store varying number of characters
For example, VARCHAR(10) will store the value “Tom” as “Tom”
Hint: Use VARCHAR to store long and varying strings such as Streetname, Firstname, Lastname etc.
Which Numeric Type?
To encode prices in a grocery store?
Use Decimal
To encode students GPA?
Use Decimal
To encode number of children or grade?
Use Integer, Smallint
SQL DDL: Create a Table
Columns (Movie): ): Movie_Nr (String), Movie_Title (String), Watch (Numeric)
Columns (Staff): Staff_Id (String), Staff_Name (String), Movie_Nr (String)
2. Identify columns and their data types (attributes and domains)
Identified columns:
Columns (Movie): Movie_Nr, Movie_Title, Watch
Columns (Staff): Staff_Id, Staff_Name, Movie_Nr
Added data types (SQLite):
SQL DDL: Create a Table
3. Identify constraints (NOT NULL, Primary key, Foreign Keys etc.)
Consider the integrity rules of database.
The term “integrity” in database context means data are accurate, correct and valid good database design.
Create integrity rules (called database constraints) using SQL.
Required data (NOT NULL)
Domain constraints (e.g., business rules)
Entity integrity/primary key rule
Referential integrity/foreign key rule
General constraints
e.g., age > 0
e.g., student number
SQL DDL: Create a Table
Entity integrity is enforced by creating a primary key of the table.
Primary key of a table must contain a unique, non-null value for each row. A table can have only one primary key.
Referential integrity rule is enforced by creating a foreign key in a table.
Foreign key is a column or set of columns that links each row in a (child- Staff) table containing foreign key to a row of (parent- Movie) table containing matching primary key.
Movie(Movie_Nr, Movie_Title, Watch)
Staff(Staff_Id, Staff_Name, Movie_Nr)
Primary key
Foreign key
Primary key
SQL DDL: Create a Table
General constraints include:
CHECK constraint: To limit the value range that can be placed in a column
UNIQUE constraint: For single column or combination of columns that uniquely defines a row. Some of the columns can contain null values as long as the combination of values is unique.
DEFAULT constraint: To insert a default value into a column. The default value will be added to all new records, if no other value is specified.
SQL DDL: Create a Table
3. Identify constraints (NOT NULL, Primary key, Foreign Keys etc.)
Primary key: Movie_Nr (Movie), Staff_Id (Staff)
Foreign key: - (Movie), Moive_Nr (Staff)
In the relational schema:
Columns (Movie): Movie_Nr (NOT NULL), Movie_Title (NOT NULL), Watch (NULL allowed)
Columns (Staff): Staff_Id (NOT NULL), Staff_Name (NOT NULL), Movie_Nr (NOT NULL)
Identified NOT NULL and NULL allowed columns:
SQL DDL: Create a Table
General syntax of CREATE TABLE command:
SQL DDL: Create a Table
Foreign key with options (ON DELETE and UPDATE)
NO ACTION: No action is performed with the child data when the parent data is deleted or updated.
SET NULL: The child data is set to NULL when the parent data is deleted.
CASCADE: The child data is either deleted or updated when the parent data is deleted or updated.
SET DEFAULT: The child data is is set to their default values when the parent data is deleted or updated.
SQL DDL: Create a Table
When ‘M001’ in Movie table is deleted/updated,
NO ACTION: Noting changed. S001 and S002 (in Staff table) still have ‘M001’.
SET NULL: S001 and S002 will have null value.
CASCADE: S001 and S002 will be deleted if ‘M001’ is deleted in Movie.
S001 and S002 will have changed value if ‘M001’ is changed in Movie.
SET DEFAULT: S001 and S002 will have default value (e.g., M000)
SQL DDL: Create a Table
Create Movie and Staff tables
With basic constraints identified previously:
SQL DDL: Create a Table
Create Movie and Staff tables
With additional constraints (FOREIGN KEY, DEFAULT, CHECK) applied to Staff table:
SQL DDL: Change a Table
Change a table is to make some changes to an existing table by ALTER TABLE command. The command is used:
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
SQL DDL: Change a Table
To add a column, use the following command:
ALTER TABLE table_name ADD column_name datatype
Example:
ALTER TABLE Branch ADD branch_phone VARCHAR(20);
To drop an existing column, use the following command:
ALTER TABLE table_name DROP column_name;
Example:
ALTER TABLE Branch DROP branch_phone;
SQL DDL: Change a Table
To modify an existing column, use
ALTER TABLE table_name MODIFY column_name datatype
Example: ALTER TABLE Staff MODIFY Branch_phone VARCHAR(30);
SQL DDL: Delete a Table
Delete a table is to delete an existing table by DROP TABLE command.
DROP TABLE table_name [RESTRICT | CASCADE] (e.g., DROP TABLE Movie RESTRICT)
Restrict option: To prevent the parent table from being deleted if any other tables refer to it (foreign key). For example, Movie table is not deleted because Staff table (movie_nr, foreign key) refers to the table.
Cascade option: To always delete the parent table together with all the tables where they are referred (foreign key).
SQL DDL: Create/Delete Index
Index is a structure that is created for fast data retrievals in a query.
By default, an index will always be created on primary key columns. Additional indexes can be created on other table columns using CREATE INDEX command (however, indexes slow down database operations, so care is needed in creating indexes).
* If tables are frequently hit by INSERT , UPDATE , or DELETE statements, then overall performance could drop because indexes must be modified after those operations.
To create/delete index:
CREATE INDEX index_name ON table_name (index_column1, 2,…)
DROP INDEX index_name
SQL DDL: Create/Delete View
View is defined as a dynamic result of one or more relational operations operating on base tables to produce another table.
A view table is a virtual table that does not necessarily actually exist in the database but is produced upon request, at time of request.
View tables are used to show the relevant database content to the users and hide the rest of the data for the needs of a specific class of user and access control.
To create view:
CREATE VIEW view_name AS SELECT column_name1, 2, 3,… FROM table_name WHERE condition
SQL DDL: Create/Delete View
For example, the command CREATE VIEW Staff_view AS SELECT staff_id, staff_name FROM Staff WHERE staff_name = ‘Stephen Chow’ shows Staff_view table which has two columns (staff_id and name).
To delete view:
DROP VIEW view_name
SECTION II
DML
Data Manipulation Language (DML)
There are three commands to modify (update) database namely:
INSERT: to insert data (as rows) into a table
UPDATE: to update data in a table
DELETE: to remove data from a table
INSERT Command
First form of INSERT
INSERT INTO TableName [ (columnList) ]
VALUES (dataValueList)
columnList is optional; if omitted, SQL assumes a list of all columns in their original CREATE TABLE order
Any columns omitted must have been declared as NULL when table was created, unless DEFAULT was specified when creating column
INSERT Command
dataValueList must match columnList as follows:
number of items in each list must be same
must be direct correspondence in position of items in two lists
data type of each item in dataValueList must be compatible with data type of corresponding column
Example: INSERT Command
Insert a new row into Staff table supplying data for all columns:
INSERT INTO Staff
VALUES (‘SG16’, ‘Alan’, ‘Brown’, ‘Assistant’, ‘M’, Date‘1957-05-25’, 8300, ‘B003’);
Note: List of columns and their order is defined as Staff(staffNo, fName, lName, position, sex, DOB, salary, branchNo)
Example: INSERT Command
Insert a new row into Staff table supplying data for few and/or mandatory columns
INSERT INTO Staff (staffNo, fName, lName,
position, salary, branchNo)
VALUES (‘SG44’, ‘Anne’, ‘Jones’, ‘Assistant’, 8100, ‘B003’);
Or
INSERT INTO STAFF
VALUES ('SG44','Anne', 'Jones', 'Assistant', 'F', NULL, NULL, NULL);
Insert nulls in columns
Note: List of columns and their order is defined as Staff(staffNo, fName, lName, position, sex, DOB, salary, branchNo)
Example: INSERT Command
Second Form
INSERT… SELECT
Will be discussed after study of SELECT command
Auto-Increment
The value of the primary key column inserts automatically every time a new record is inserted with the help of Auto-Increment
Helps to speed up data insertion provided the primary key is of Integer/numeric type
This mechanism is DBMS specific
Auto-Increment (MySQL Example)
CREATE TABLE Customers ( ID int NOT NULL AUTO_INCREMENT, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), PRIMARY KEY (ID));
Use the following command to insert new record into the “Customers" table with next value of Id
INSERT INTO Customers (FirstName,LastName) VALUES (‘Michael',’Jackson');
In MySQL, Starting value for AUTO_INCREMENT is 1, and will increment by 1 for each new record
To let the AUTO_INCREMENT sequence start with another value, e.g. 1000, use the following SQL statement:
ALTER TABLE Customers AUTO_INCREMENT=1000;
UPDATE Command
Use update command to update values of columns of rows of a table
UPDATE TableName
SET columnName1 = dataValue1
[, columnName2 = dataValue2...]
[WHERE searchCondition];
TableName can be name of a base table or an updatable view
SET clause specifies names of one or more columns that are to be updated
UPDATE Command
WHERE clause is optional:
if omitted, named columns are updated for all rows in table;
if specified, only those rows that satisfy searchCondition are updated.
New dataValue(s) must be compatible with data type for corresponding column.
Examples UPDATE Command
Example: Give all staff a 3% pay increase
UPDATE Staff
SET salary = salary*1.03;
Example: Give all Managers a 5% pay increase.
UPDATE Staff
SET salary = salary*1.05
WHERE position = ‘Manager’;
Update Many Columns
Example: Promote David Ford (staffNo=‘SG14’) to Manager and change his salary to £18,000:
UPDATE Staff
SET position = ‘Manager’, salary = 18000
WHERE staffNo = ‘SG14’;
DELETE Command
Use Delete command to delete rows from a table
DELETE FROM TableName
[WHERE searchCondition];
TableName can be name of a base table or an updatable view
searchCondition is optional; if omitted, all rows are deleted from table. This does not delete table.
If search_condition is specified, only those rows that satisfy condition are deleted
Difference between Delete and Drop table command?
DELETE Command
Examples
(1) DELETE FROM Viewing;
DELETE FROM Registration
WHERE ClientNo = ‘CR56’;
SECTION III
Exercises
Problem
The address from our last example is missing a street name.
What command could we use to add street name to the address table after street number?
ALTER
?
What are 2 ways that we could add street name to the address table after street number?
SQL:
ALTER TABLE Address
ADD streetName VARCHAR(60) NOT NULL
AFTER streetNumber;
?
Menu:
Problem
Problem
We need to add information for 2 new patients in a medical database.
What command could we use?
INSERT
?
A patient has moved overseas.
What command(s) could we use?
DELETE: to remove
UPDATE: to change address
?
Problem
Errors in SQL
Key Terms
You are going to see errors from time to time!
Sometimes you might be missing one semicolon.
Others may be integrity related.
Read them carefully; Google; ask others in Slack.
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near…
Error Code: 1046. No database selected Select the default DB to be used by double-clicking its name in the SCHEMAS list in the sidebar.
Error Code: 1050. Table 'address' already exists
54
Resources
W3schools
StackOverflow
MySQL Reference Manual
https://dev.mysql.com/doc/refman/5.7/en/
Google the question
Key Terms
Exercise 1
Key Terms
Step 1: Create a BNE Libraries Database;
Step 2: Based on the relational schema below, create tables in the BNE libraries database;
book (barcode, bootTitle, author)
customer (customerNo, customerName)
library (libraryNo, libraryName)
loan (customerNo, barcode, libraryNo, dateLoaned, dateReturned)
NOTE: Some books start table names with lowercase and column names with uppercase.
56
Exercise 1
Key Terms
Step 3: alter the library table and add a column to store each library’s phone number after libraryName
Step 4: alter the datatypes of the dateLoaned and dateReturned columns to be DATETIME.
Step 5: Create a table called category that has two attributes: categoryID and categoryName, view it in the list of tables (use the command “SHOW TABLES”), then drop the table.
category (categoryID, categoryName)
Step 6: create a database called music, view it in the list of databases (use the command “SHOW DATABASES”) and then drop it.
NOTE: Some books start table names with lowercase and column names with uppercase.
57
Exercise 2
Key Terms
Create a database for the Final Exams relational model below.
Relational Model
Student (studentID, firstName, lastName, grade, exam)
Exam (examID, name, location, dateTime, adjudicatorID, adjudicatorName)
Foreign Keys
Student (exam) references Exam (examID)
Other Constraints
Each student only has one exam.
Each exam only has one adjudicator and adjudicator only observes one exam.
NOTE: Some books start table names with lowercase and column names with uppercase.
58
Exercise 2
Key Terms
Import “exam.csv” file (you can download this file from BB) to the previous database that you just created;
NOTE: Some books start table names with lowercase and column names with uppercase.
59
Exercise 2
Key Terms
NOTE: Some books start table names with lowercase and column names with uppercase.
60
Exercise 2
Key Terms
Step 1: insert the following data into the exam table
('History', ‘P Block’, ‘2016-10-30 10:00:00’, 6, ‘Wesley Crusher’)
(‘Legal Studies’, ‘S Block’, ‘2016-10-30 10:00:00’, 7, ‘Jean Picard’);
Step 2: The Legal Studies exam (examID 7) has moved to T Block. Update the table to reflect this change.
Step 3: The History exam has been cancelled. Delete it from the database.
NOTE: Some books start table names with lowercase and column names with uppercase.
61
62