5. Referential Integrity Constraint: This constraint is specified between two relations.
Before defining this constraint let us study the concept of foreign keys. Foreign key in a
relation R1 is the set of attributes in R1 that refer to primary key in another relation R2 if
the domain of foreign key attributes is same as that of primary key attributes and the
value of foreign key either occurs as a value of primary key in some tuple of R2 or is NULL.
R1 is called the referencing relation and R2 is called referenced relation, and a referential
integrity constraint holds from R1 to R2.
The main purpose of this constraint is to check that data entered in one relation is
consistent with the data entered in another relation. For example, consider two relation
schemas:
Department (Dept_Name, Dept_ID, No_of_Teachers)
Teacher (Teacher_Name, Teacher_ID, Dept_ID, Subject)
Following are the primary keys (Underlined above):
F
Dept_ID is the primary key of Department relation.
F
Teacher_ID is the primary key of Teacher relation.
Now you may notice that Dept_ID- the primary key of relation in Department, is also present
in relation Teacher. The reason is that every teacher belongs to a particular department. Now
that means Dept_ID of Teacher relation must have a value that exists in Dept_ID attribute
of Department relation or it can be NULL in case a teacher has not yet been assigned to a
department. We say that Dept_ID of Teacher relation is a foreign key that references primary
key of Department relation (Dept_ID).
It is important to emphasize it is not necessary to have same name for foreign key as of the
corresponding referenced primary key. The above two schemas can also be written as
follows:
Department (Dept_Name, Dept_ID, No_of_Teachers)
Teacher (Teacher_Name, Teacher_ID, Dept_No, Subject)
Where Dept_No is the foreign key that references Dept_ID of Department relation.
A foreign key may also refer to the same relation. For example suppose we have to create a
database of all residents in a colony along with their best neighbors. Consider the following
relation:
Residents (Name, RID, Block_no, House_no, Floor, Neighbor_RID)
The Primary key of this relation is RID (Resident ID). In order to store information about
neighbor we have created a foreign key Neighbor_RID that references RID of Residents.
Note that the referencing and referenced relation are same in this case.
1.6 Structured Query Language (SQL)
SQL is a language that is used to manage data stored in a RDBMS. It comprises of a Data
Definition Language (DDL) and a Data Manipulation Language (DML) where DDL is a
9
Database Management Applications
language which is used to define structure and constraints of data and DML is used to
insert, modify and delete data in a database.
SQL commands are used to perform all the operations. In order to study SQL commands,
a database system needs to be installed on the Computer. There are various softwares
available. We will study the MySQL server.
SQL uses the terms table, row and column for the relational model terms relation, tuple
and attribute.
For studying SQL we will use MySQL Community Server 5.6.20 which is freely
downloadable. The most recent versions can be found on the website:
http://dev.mysql.com/downloads/ Following are the steps to install and configure MySQL
Community Server 5.6.20 for studying SQL commands.
1. Open the URL: http://dev.mysql.com/downloads/mysql/#downloads
2. Download the MySQL Community Server 5.6.20 available on the above webpage.
You can select the platform (Windows/Linux) as shown below.
MySQL Community Server 5.6.20
Select Platform:
Microsoft Windows
Recommended Download:
All MySQL Products. For All Windows Platforms.
In One Package
Starting with MySQL 5.6 the MySQL Installer package replaces the server-only MSI packages.
Windows (x86, 64-bit), MySQL Installer MSI
Other Downloads:
Windows (x86, 32-bit), MSI Installer 5.6.20 44.8M
(mysql-5.6.20-win32.msi) MD5: 59abb64af27634abd0f65a60204b18ab Signature
Windows (x86, 64-bit), MSI Installer 5.6.20 47.8M
(mysql-5.6.20-win64.msi) MD5: 503dc2840c6732ae3e5dc80a3022f1a7 Signature
Windows (x86, 32-bit), ZIP Archieve 5.6.20 337.6M
(mysql-5.6.20-win32.zip) MD5: 28528dd2cecdd79d33deca2e1b7058e Signature
Windows (x86, 64-bit), ZIP Archieve 5.6.20 342.9M
(mysql-5.6.20-win64.zip) MD5: 08028e89f892534114550c75f57f3453 Signature
3. Once you have downloaded the file mysql-installer-community-5.6.20.0.msi, double
click on the downloaded file and then click on the “Run” button.
10
4. MySQL Installer will start installing. Click on the “Install MySQL Products” option.
MySQL Installer
Welcome
The MySQL installer guides you through the installation and configuration of your
MySQL products. Run it from the Start Menu to perform maintenance tasks later.
Select one of the actions below:
Install MySQL Products
Guide you through the installation and configuration of your
MySQL products.
About MySQL
Lean more about MySQL products and better understand how
you can benefit the most.
Resources
Get more information on how to install MySQL and configure it
to run efficiently on you machine.
Copyright © 2014, Oracle and/or its affiliates. All rights reserved, Oracle is a registered trademark of
Oracle Corporation and/or its affiliated. Other names may be trademarks of their respective owners.
11
Database Management Applications
5. Check the option “I accept the license terms” and then Click on “Next” button.
MySQL Installer
License Agreement
MySQL. Installer
To install MySQL, you must accept the Oracle Software License Terms.
GNU GENERAL PUBLIC LICENSE
Vesrion2, June 1991
License Information Copyright (c) 1989, 1991 Free Software Foundation, Inc.,
51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
Find latest products Everyone is permitted to copy and distribute verbatim copies
of this license document, but changing it is not allowed.
Setup Type Preamble
=========
Check Requirements
The licenses for most software are designed to take away your freedom
to share and change it. But contrast, the GNU General Public License is
Installation
intended to guarantee your freedom to share and change free]
software--to make sure the software is free for all its users. This
Configuration General Public License applied to most of the Free Software
Foundation’s software and to any other program whose authors commit to
Complete using it. (Some other Free Software Foundation software is covered by
the GNU Library General Public Licence instead.) You can apply it to
your programs, too.
When we speak of free software, we are referring to freedom, not price.
Our General Public Licenses are designed to make sure that you have
the freedom to distribute copies of free software (and charge for this
service if you wish), that you receive source code or can get it if you
want it, that you can change software or use pieces of it in new\
free programs; and that you know you can do these things.
I accept the license terms
< Back Next > Cancel
6. Then next click on the “Execute” button.
MySQL Installer
Find latest products
MySQL. Installer
Before continuing, the Installer will determine if updates are available for the
products you are about to install or upgrade.
License Information
Connect to the Internet
Find latest products
Fetch product update information
Setup Type
Check Requirements
Installation
Configuration
Complete
Skip the check for updated (not recommended)
< Back Execute Cancel
12
7. On successful execution, click on the “Next” button.
MySQL. Installer
Find latest products
MySQL. Installer
Before continuing, the Installer will determine if updates are available for the
products you are about to install or upgrade.
License Information
Find latest products Connect to the Internet
Fetch product update information
Setup Type
The operation is complete. Please click 'Next >' to continue.
Check Requirements
Installation
Configuration
Complete
Skip the check for updates (not recommended)
< Back Next > Cancel
8. Select the “Server only” option. Then click on “Next” button.
MySQL Installer
Choosing a Setup Type
MySQL. Installer
Please select the Setup Type that suits your use case.
Developer Default Setup Type Description
Installs all products needed for Installs only the MySQL Server. This type should
License Information MySQL development purposes. be used where you want to deploy a MySQL
Server, but will not be developing MySQL
Find latest products applications.
Server only
Installs only the MySQL Server
Setup Type
product.
Check Requirements
Client only
Installation Installs only the MySQL Client
products, without a server.
Configuration
Full Installation Path:
Complete Installs all included MySQL products
and features. C:\Program Files\MySQL\
Custom Data Path:
Manually select the products that
C:\ProgramData\MySQL\MySQL Server 5.6\
should be installed on the system.
< Back Next > Cancel
13
Database Management Applications
9. Installer will check for the requirements. If any requirements are required, you have to
download them first before installing MySQL server. If all the requirements are met, then
the following message will be displayed. Click “Next” to continue.
MySQL Installer
Check Requirements
MySQL. Installer
There are no additional requirements to be installed. Please click Next to continue
with the product installation.
License Information
Find latest products
Setup Type
Check Requirements
Installation
Configuration
Complete
< Back Next > Cancel
10. Click on “Execute” to install MySQL Server 5.6.20.
MySQL Installer
Installation Progress
MySQL. Installer
The following products will be installed or updated.
Product Status Progress Notes
License Information MySQL Server 5.6.20 To be installed
Find latest products
Setup Type
Check Requirements
Installation
Configuration
Complete
Click [Execute] to install or update the following packages
< Back Execute > Cancel
14
11. On successful installation, Click on “Next”
MySQL Installer
Installation Progress
MySQL. Installer
The following products will be installed or updated.
Product Status Progress Notes
License Information MySQL Server 5.6.20 Install success
Find latest products
Setup Type
Check Requirements
Installation
Configuration
Complete
Show Details >
< Back Execute > Cancel
12. Click on “Next” to start initial configuration.
MySQL Installer
Configuration Overview
MySQL. Installer
The following products will now be configured.
Product Action to be performed Progress
License Information MySQL Server 5.6.20 Initial Configuration.
Find latest products
Setup Type
Check Requirements
Installation
Configuration
Complete
Show Details >
< Back Execute > Cancel
15
Database Management Applications
13. Select the following configurations and then Click “Next”.
MySQL Installer
MySQL Server Configuration 1/3
MySQL. Installer
Server Configuration Type
Choose the correct server configuration type for this MySQL Server
installation. This setting will define how much system resources are assigned
License Information to the MySQL Server instance.
Find latest products Config Type: Development Machine
Setup Type
Enable TCP/IP Networking
Check Requirements
Enable this to allow TCP/IP networking. Only localhost connections
through named pipes are allowed when this option is skipped.
Installation
Port Number: 3306
Configuration
Open Firewall port for network access
Complete
Advanced Configuration
Select the checkbox below to get additional configuration page where
you can set advanced options for this server instance.
Show Advanced Options
< Back Next > Cancel
14. Type the MySQL root password (minimum 4 characters long) and then click “Next”
MySQL Installer
MySQL Server Configuration 2/3
MySQL. Installer
Root Account Password
Enter the password for the root account. Please remember to store
this password in a secure place.
License Information
MySQL Root Password:
Find latest products
Repeat Password:
Setup Type Password Strength: Weak
Check Requirements
Installation
MySQL User Accounts
Configuration
Crate MySQL user accounts for your users and applications.
Complete Assign a role to the user that consists of a set of privileges.
MySQL Username Host User Role Add User
Edit User
Delete User
< Back Next > Cancel
16
15. Click on “Next” on the following window.
MySQL Installer
MySQL Server Configuration 3/3
MySQL. Installer
Windows Service Details
Please specify a Windows Service name to be used for this MySQL
License Information Server instance. A unique name is required for each instance.
Find latest products Windows Service Name: MySQL56
Setup Type Start the MySQL Server at System Startup
Check Requirements
Run Windows Service as ...
Installation The MySQL Server needs to run under a given user account.
Based on the security requirements of your system you need to
pick one of the options below.
Configuration
Standard System Account
Complete Recommended for most scenarios.
Custom User
An existing user account can be selected for advanced scenarios.
< Back Next > Cancel
16. Installer will configure the server. On successful configuration, click on “Next”.
MySQL Installer
Configuration Overview
MySQL. Installer
The following products will now be configured.
Product Action to be performed Progress
License Information MySQL Server 5.6.20 Configuration Complete.
Find latest products
Setup Type
Check Requirements
Installation
Configuration
Complete
Show Details >
< Back Execute > Cancel
17
Database Management Applications
17. Then Click on “Finish”. The installation and configuration of MySQL Server 5.6.20 is now
complete. You can now start using the server for creating and modifying databases.
MySQL Installer
Installation Complete
MySQL. Installer
The installation procedure has been completed.
License Information
Find latest products
Setup Type
Check Requirements
Installation
Configuration
Complete
Back Finish Cancel
We give below SQL commands used to define and modify a database:
1. Create Table Command: This command is used to create a new table or relation. The
syntax for this command is :
CREATE TABLE<table name>
(
<column 1><data type> [constraint] ,
<column 2><data type>[constraint],
<column 3><data type>[constraint]
);
where []=optional
The keyword CREATE TABLE is followed by the name of the table that you want to create.
Then within parenthesis, you write the column definition that consists of column name
followed by its data types and optional constraints. There can be as many columns as
you require. Each column definition is separated with a comma (,). All SQL statements
should end with a semicolon (;).
Table 1 shows the data types commonly used.
18
