1
NELCO EXPLAINS
C
S
0795 CSC
C
95
07
S
IN
Structured Query Language
LA
Programming Release #2
P
X
E
Prerequisite: An open mind and willingness to learn.
O
Prerequisite: A DBMS, specifically an RDBMS, installed on a computer.
LC
Note: Due to the multiplicity of the syntaxes of SQL databases, the DBMS of choice
for this whole series of release notes will be MS Access. However, generic syntaxes
will be provided where applicable.
E
N
SQL Programming Release #2
NELCO EXPLAINS CSC – Release#2 2
SQL RDBMS CONCEPTS I
RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL and for any
C
modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.
S
A Relational Database Management System is a database management system(DBMS) that is based on the
relational model as introduced by E.F. Codd.
C
95
1.0 Understanding Tables or Relations
A table is a collection of related data entries, and it consists of columns and rows. The table is the most
common and simplest form of data storage in a relational database. When working with tables in an
07
RDBMS, it is crucial to understand the meaning the table conveys.
The figure below displays the information about some employees working at a supermarket downtown.
S
IN
LA
P
X
E
On a formal account, the EMPLOYEE table is:
O
EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_AG EMPLOYEE_AD EMPLOYEE_SAL
E DRESS ARY
LC
1 Lawrence 32 NewYork 896236.23
2 Rutherford 56 Tennessee 50000.58
E
3 Guy Dermo 23 Berlin 850000
N
4 Pickerbay 25 Massachaussets 56000
Every table can be broken up into smaller entities called fields. The fields in the EMPLOYEE table are:
EMPLOYEE_ID, EMPLOYEE_NAME, EMPLOYEE_AGE, EMPLOYEE_ADDRESS,
EMPLOYEE_SALARY.
All rights reserved NELCO EXPLAINS CSC
3
A field is a column in a database table that is designed to maintain specific information about every record
in the table.
A record, also called a row of data, is each entry that exists in a table. In more technical terms, a record is
an instance of the relational schema or table structure in the database. For example, the table
EMPLOYEE contains 4 records.
One of them is:
1 Lawrence 32 NewYork 896236.23
C
A record is a horizontal entity in a table. On the other hand, a column expresses a vertical property of the
S
table.
C
A column is a vertical entity in a table that contains all information associated with a specific field in a table.
For example, a column in the EMPLOYEE table is EMPLOYEE_NAME.
95
Important point: A NULL Value in a table is a value in a field that appears to be blank, which means a
NULL value is a field with no value. It is very important to discern the fact that a NULL value is different
07
from a zero(0) value. A field with a NULL value is one that has been left blank during record creation.
2.0 SQL CONSTRAINTS S
Constraints are the rules enforced on data columns in tables. They are used to limit the type of data that can
IN
be entered into a table. This ensures the accuracy and reliability of the data in the database.
Constraints could be column-level(applicable to a column only) or table-level(applicable to the entire
LA
table). Examples of commonly used constraints are:
1. NOT NULL constraint: Ensures that a column cannot have a NULL value.
P
2. DEFAULT constraint: Provides a default value for a column when none is specified.
3. UNIQUE constraint: Ensures that all values in a column are different.
X
4. PRIMARY KEY: Uniquely identifies each row/record in a database table.
5. FOREIGN KEY: Uniquely identifies each row/record in another database table.
E
6. CHECK Constraint: Ensures that all values in a column satisfy certain conditions.
7. INDEX Constraint: Used to create and retrieve data from the database very quickly.
O
2.0.1 NOT NULL CONSTRAINT
LC
By default, a column can hold NULL values(absence of data in a cell/column). During table definition,
using the SQL CREATE command, if you do not want a column to have a NULL value, then you need to
E
define such a constraint on this column. By so doing, you tell the DBMS engines that a NULL is not allowed
for that particular column.
N
This constraint is always specifically applied to PRIMARY/KEY attributes.
The generic SQL syntax for enforcing a NOT NULL constraint on a column in a database table is:
CREATE TABLE table_name (
column_name1 data_type NOT NULL,
column_name2 data_type,
...);
SQL Programming Release #2
NELCO EXPLAINS CSC – Release#2 4
Example 1:
Given the EMPLOYEE table above. The SQL CREATE command on MS Access is:
C
S
C
95
When a NOT NULL constraint is applied to the columns, data entry becomes mandatory as illustrated in the
image below.
07
S
IN
LA
P
X
E
O
LC
Error message: “Vous devez entrer une valeur dans le champ << EMPLOYEE.EMPLOYEE_ID>>”. This
E
means that we must provide a value for the field EMPLOYEE_ID in the table EMPLOYEE because we
applied a NOT NULL constraint on EMPLOYEE_ID.
N
In a situation where we forgot to enforce a NOT NULL constraint on the EMPLOYEE_SALARY, whereas
the table is already created. We can use an ALTER Command to modify the table definition.
Remember: the ALTER command is used to modify a table structure, definition, or any other database
object. The image below displays the query used in MS Access 2016 to enforce the NOT NULL constraint
on the attribute EMPLOYEE_SALARY.
All rights reserved NELCO EXPLAINS CSC
5
C
S
C
95
07
S
In MS Access 2016 and many other SQL servers, the generic syntax used for enforcing a NOT NULL
IN
constraint on an already existing table is the following:
ALTER TABLE table_name
ALTER COLUMN column_name datatype NOT NULL;
LA
After a NOT NULL constraint has been applied to the EMPLOYEE_SALARY field, it is now mandatory to
enter a value for this field.
P
X
E
O
LC
E
N
Another way to enforce a NOT NULL constraint on columns in MS Access 2000 and later is through the
table design view. Note that the image above is also known as a data sheet view.
SQL Programming Release #2
NELCO EXPLAINS CSC – Release#2 6
2.0.2 DEFAULT CONSTRAINT
The DEFAULT constraint provides a default value to a column when the INSERT INTO statement does
not provide a specific value.
Example 2:
For example, the following SQL query creates a new table called CUSTOMER and adds five columns.
Here, the RECEIPT column is set to 5000.00 by default, so in case the INSERT INTO statement does not
provide a value for this column. Then by default, this column would be set to 5000.
C
Unfortunately, MS Access does not support the syntactic SQL DEFAULT constraint. This means that
S
executing this:
C
CREATE TABLE CUSTOMER (
95
CUSTOMER_ID INT NOT NULL,
CUSTOMER_NAME TEXT(10) NOT NULL,
AGE INT NOT NULL,
07
ADDRESS TEXT(15),
AmountDue NUMERIC DEFAULT 5000.00,
PRIMARY KEY (CUSTOMER_ID) );
S
It will have no effect in the database and will result in a syntax error pointed out by the DBMS engine.
IN
Note: SQL engines use commas(,) to signify the continuation of the query. And the absence of a comma(,)
signifies the end of the query.
LA
After removing the bolded constraint, the remaining SQL query is a valid one in MS Access. This query
helped us to create a new table, CUSTOMER, as shown below.
P
X
E
O
LC
To insert a DEFAULT value in a table’s column, in MS Access, one should:
1. Right-click on the table’s name and choose “design view”.
2. In the design grid, click the field you want to set the default for (e.g., AmountDue). At the bottom of
E
the window, you’ll see the Field Properties pane.
N
3. Locate the property called Default Value and enter the value you want as the default.
All rights reserved NELCO EXPLAINS CSC
7
After completing step 1 and selecting the desired column, the default value is to be inserted one should
encounter the design view of the table:
C
S
C
95
The design view of a table in MS Access is a view used to create and organize a table’s structure before
entering data, allowing you to define how the table will look and function. Its purpose is to set field names,
choose data types, apply validation rules, and assign a primary key, ensuring accurate, well-organized
07
storage of data. The design view is a handy method to bypass SQL queries in critical times.
S
IN
LA
P
X
E
O
LC
E
After inserting the desired default value using the design view, the non-populated CUSTOMER table looks
N
like this:
SQL Programming Release #2
NELCO EXPLAINS CSC – Release#2 8
If, for any reason, the table CUSTOMER needs to be updated to remove the default value. One can do so
through the same design grid by simply removing the default value and saving the new table design.
2.0.3 UNIQUE CONSTRAINT
The UNIQUE Constraint prevents two records from having identical values in a particular column. In the
CUSTOMER table, for example, you might want to prevent two or more people from having the same age.
The appropriate SQL syntax for this is:
CREATE TABLE CUSTOMER (
C
CUSTOMER_ID INT NOT NULL,
S
CUSTOMER_NAME TEXT(10) NOT NULL,
AGE INT NOT NULL UNIQUE,
C
ADDRESS TEXT(15),
95
AmountDue NUMERIC,
PRIMARY KEY (CUSTOMER_ID) );
However, MS Access doesn’t support all the different types of SQL constraints and syntaxes, and this is
07
another example of this case. To enforce a unique constraint on any table in MS Access, the design view will
be our way about. Keep in mind that MS Access already supports the enforcement of a unique constraint
through the PRIMARY KEY constraint, since the primary key ensures every record is unique in the table.
S
To enforce a unique constraint on a database table, follow the steps below:
IN
1. In your database, right-click the table name and choose design view.
2. Select the field(column-name) you want to make unique.
3. In the field properties pane(design grid) below, find Indexed.
LA
4. Set it to Yes(No Duplicates). This will enforce the uniqueness for that column.
After populating our CUSTOMER table with a few rows of data and enforcing a UNIQUE constraint on
P
the field Age, we get this error message when two customers have the same Age.
X
E
O
LC
E
N
Error Message: << Modifications non effectuées : risque de doublons dans champs index, clé principale ou
relation interdisant les doublons. Modifiez les données des champs contenant les doublons, enlevez ou
redéfinissez l’index pour permettre les doublons et recommencez>>
English Version: << Changes not made: risk of duplicates in an indexed field, primary key, or relationship
that disallows duplicates. Modify the data in the fields containing duplicates, remove or redefine the index to
allow duplicates, and try again.>>
All rights reserved NELCO EXPLAINS CSC
9
These error messages tell us that we enforced a UNIQUE constraint on the field AGE, but the column AGE
contains repeating values, which is not allowed by the UNIQUE constraint, hence the error message.
Additionally, you can clear a unique constraint on a column in a database table, using the general SQL
syntax:
ALTER TABLE table_name
DROP CONSTRAINT myUniqueConstraint;
2.0.4 PRIMARY KEY CONSTRAINT
C
A primary key is a field in a table that uniquely identifies each row/record in a database table. Primary keys
S
must contain unique values. A primary key column cannot have NULL values. A table can have only one
primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key,
C
they are called a composite key. If a table has a primary key defined on any field(s), then you can not have
two records having the same value of that field(s).
95
The general syntax for creating a table in SQL:
07
CREATE TABLE table_name( But this table definition does not have a
column_name1 datatype, primary key. This is a violation of 1NF, and this will also
column_name2 datatype, render the process of querying difficult.
S
column_name3 datatype,
……. );
IN
to enforce a PRIMARY KEY constraint on any suitable column_name. The following SQL syntax will work
across all SQL RDBMS(s).
LA
CREATE TABLE table_name(
column_name1 datatype NOT NULL, The NOT NULL constraint has been used on
column_name2 datatype, column_name1 because it is our PK.
P
column_name3 datatype,
X
column_name4 datatype,
PRIMARY KEY(column_name1) );
E
Note: if the table already exists, and no primary key has been added to it. The general SQL syntax for
O
adding a primary key to an existing table is:
LC
ALTER TABLE table_name ADD PRIMARY KEY(column_name).
Note: when using the ALTER command to add a primary key to an existing table, make sure the primary
key column already exists and must have been defined not to contain null values(when the table was first
E
created). However, remember that MS Access contains a design view where the table’s structure can be
modified, and yes, Primary Keys can be assigned or cleared.
N
You can clear a primary key constraint from a table in a database, using the generic SQL syntax:
ALTER TABLE table_name DROP PRIMARY KEY;
SQL Programming Release #2
NELCO EXPLAINS CSC – Release#2 10
2.0.5 FOREIGN KEY CONSTRAINT
A foreign key is a key used to link two tables together. This is sometimes referred to as a referencing key.
A foreign Key is a column or a combination of columns whose values match a Primary Key in a different
table. The relationship between two linked tables matches the Primary Key in one of the tables with a
Foreign Key in the second table.
As a reminder, if a table has a primary key defined on any field(s), then you can not have two records having
the same value of that field(s).
C
Example 3:
S
Consider the structure of the two tables as follows:
C
CUSTOMER TABLE: ORDERS TABLE:
95
CREATE TABLE CUSTOMER ( CREATE TABLE ORDERS(
ID INT NOT NULL, ID INT NOT NULL,
07
CUSTOMER_NAME TEXT(10) NOT NULL, ORDER_DATE DATETIME,
AGE INT NOT NULL, CUSTOMER_ID INT references CUSTOMER(ID),
ADDRESS TEXT(15), PRICE DOUBLE,
AmountDue NUMERIC, PRIMARY KEY(ID) );
S
PRIMARY KEY (ID) );
IN
In the SQL view of MS Access, we have this:
LA
P
X
E
O
LC
E
N
All rights reserved NELCO EXPLAINS CSC
11
The generic syntaxes for the ORDERS and the CUSTOMER tables permitted us to create the following
unpopulated tables:
C
S
C
95
07
S
IN
If the table ORDERS has already been created, and the foreign key has not yet been set, use the generic
LA
SQL syntax below, in the SQL view, to specify the foreign key and alter the ORDERS table.
ALTER TABLE ORDERS
P
ADD FOREIGN KEY (Customer_ID) REFERENCES CUSTOMER (ID) ;
X
The FOREIGN KEY constraint helps us set a relationship between two tables. Generally, to create a
relationship between two tables(table_name1 and table_name2), we use the SQL syntax below.
E
CREATE TABLE table_name1( CREATE TABLE table_name2(
O
column_name1 datatype NOT NULL, column_name1 datatype NOT NULL,
column_name2 datatype, column_name2 datatype,
LC
column_name3 datatype, column_name datatype references table_name1(column_name1),
column_name4 datatype, PRIMARY KEY(column_name1) );
PRIMARY KEY(column_name1) );
E
To clear the foreign key constraint on the table ORDERS, you can use the following SQL general syntax:
N
ALTER TABLE ORDERS
DROP FOREIGN KEY;
SQL Programming Release #2
NELCO EXPLAINS CSC – Release#2 12
2.0.6 CHECK CONSTRAINT
The CHECK Constraint enables a condition to check the value being entered into a record. If the condition
evaluates to false, the record violates the constraint and isn’t entered into the table.
Example 4:
For example, using the CUSTOMER table we created in the foreign key constraint above(example 3). The
store recently changed its product to alcoholic drinks, and would like only customers who are 18 and above
to be able to place orders. Hence, the storekeeper wants the Age of its customers in the CUSTOMER table
C
to be above 18.
S
Naturally, the generic SQL syntax below will help us create a new table CUSTOMER with that constraint:
C
CREATE TABLE CUSTOMER (
95
ID INT NOT NULL,
CUSTOMER_NAME TEXT(10) NOT NULL,
AGE INT NOT NULL CHECK (AGE >= 18),
07
ADDRESS TEXT(15),
AmountDue NUMERIC,
PRIMARY KEY (ID) );
S
Even if the CUSTOMER table already exists in the database, as is the case in Example 3, the SQL syntax
IN
below will allow us to modify the structure of the CUSTOMER table and add a CHECK constraint.
ALTER TABLE CUSTOMER
LA
MODIFY AGE INT NOT NULL CHECK (AGE >= 18);
The generic SQL syntax of a CHECK constraint on a database table is:
P
ALTER TABLE table_name
X
MODIFY column_name data_type [constraint] CHECK (condition);
E
To remove the CHECK constraint on the CUSTOMER table, the following SQL syntax will help us
modify its design.
O
ALTER TABLE CUSTOMERS
LC
DROP CONSTRAINT myCheckConstraint;
Generally,
E
ALTER TABLE table_name
DROP CONSTRAINT myCheckConstraint;
N
However, MS Access doesn’t support the syntactic approach of enforcing a CHECK constraint on a column
in a database table. As a result, we use the design view when in need of enforcing such a constraint on the
database tables.
All rights reserved NELCO EXPLAINS CSC
13
To enforce a CHECK constraint in the Age column of the CUSTOMER table. Do the following:
1. Right-click on the table’s name and choose “design view”.
2. In the design grid, click the field you want to set the check for (e.g., Age). At the bottom of the
window, you’ll see the Field Properties pane.
3. Locate the property called Validation rule and enter the value you want as the correct value.
C
S
C
95
07
S
IN
LA
P
After inserting a validating value and setting an error message that will be displayed, specifically, if we enter
a customer’s age less than 18. We get this:
X
E
O
LC
E
N
SQL Programming Release #2
NELCO EXPLAINS CSC – Release#2 14
Programming Exercise
Using your knowledge of databases and a suitable DBMS that supports SQL syntax or can perform SQL
syntax. Complete the following tasks.
1. The community clinic in Yaoundé is transitioning from paper-based records to a computerized database
system to improve efficiency, accuracy, and patient care. The new system is expected to manage patient
personal information, doctor profiles, medical services offered, and appointment scheduling. Patients
will visit the clinic to consult doctors for various services such as general consultation, laboratory tests,
C
maternity care, and minor surgeries.
S
Each appointment must be linked to both a patient and a doctor, and may involve one or more services. To
C
ensure proper record keeping and avoid inconsistencies, appointments cannot be booked for past dates,
and relationships between patients, doctors, and appointments must enforce data integrity. This database
95
will support daily clinic operations such as booking visits, tracking services rendered, and assigning
doctors to patients.
07
i. Define the terms: Entity, Referential Integrity, and Relationships as used in database design.(3mks)
ii. Cite and explain 3 reasons why the Clinic made a good choice of improving their database system.
(3mks)
S
iii. Write an SQL query to create the database CLINIC (1mks)
IN
Given the entities below, a database designer could identify 5 meaningful attributes for each of them.
Patients
LA
PatientID, FullName, DateOfBirth, Gender, ContactNumber
Doctors
DoctorID, FullName, Specialty, PhoneNumber, EmploymentDate
P
Appointments
X
AppointmentID, AppointmentDate, AppointmentTime, PatientID, DoctorID
E
Services
ServiceID, ServiceName, Description, ServiceCost, ServiceCategory
O
iv. Identify the primary key of each entity in the database CLINIC. (5mks)
LC
v. Draw the relational schema diagram of each of the entities in the database CLINIC (3mks)
vi. Write down SQL statements to create tables for the entities above. (4mks)
v. In your DBMS, using the SQL view, create the tables above in ([Link]). (1mks)
E
vi. Using Chen’s notation, draw an entity relationship diagram for the tables in the database
N
CLINIC (3mks)
vii. Populate the tables in your database CLINIC with 10 rows of suitable and consistent data.
viii. Screenshot your tables and queries.
All rights reserved NELCO EXPLAINS CSC