Week 11

Information Systems

Nell Dale • John Lewis

 Database Management
Systems
• A database can simply be defined as a
structured set of data
• A database management system (DBMS) is a
combination of software and data made up of:
– Physical database—a collection of files that contain
the data
– Database engine—software that supports access to
and modification of the database contents
– Database schema—a specification of the logical
structure of the data stored in the database

12-2
 Database Management
Systems

Figure 12.6 The elements of a database management system 12-3

 Database Management
Systems
• Specialized database languages allow the
user to specify the structure of data; add,
modify, and delete data; and query the
database to retrieve specific stored data
• The database schema provides the logical
view of the data in the database

12-4
 The Relational Model

• In a relational DBMS, the data items

and the relationships among them are
organized into tables
– A table is a collection of records
– A record is a collection of related fields
– Each field of a database table contains a single
data value
– Each record in a table contains the same fields
12-5
 A Database Table

Figure 12.7 A database table, made up of records and fields 12-6

 A Database Table

• We can express the schema for this part

of the database as follows:
Movie (MovieId:key, Title, Genre, Rating)

12-7
 How RDMS Works

• An RDBMS will store data in the form of a table.

Each system will have varying numbers of tables
with each table possessing its own unique
primary key. The PRIMARY KEY is then used to
identify each table.
• Within the table are rows and columns. The rows
are known as records or horizontal entities; they
contain the information for the individual entry.
The columns are known as vertical entities and
possess information about the specific field.
12-8
 How RDMS Works

Before creating these tables, the RDBMS must check the following constraints:

• Primary keys -- this identifies each row in the table. One table can only
contain one primary key. The key must be unique and without null values.
• Foreign keys -- this is used to link two tables. The foreign key is kept in one
table and refers to the primary key associated with another table.
• Not null -- this ensures that every column does not have a null value, such
as an empty cell.
• Check -- this confirms that each entry in a column or row satisfies a precise
condition and that every column holds unique data.
• Data integrity -- the integrity of the data must be confirmed before the data
is created.

12-9
 Relationships

Figure 12.8 A database table containing

customer data

12-10
 Relationships

• We can use a table to represent a

collection of relationships between objects

Figure 12.9 A database table storing current

movie rentals

12-11
 Structured Query Language

• The Structured Query Language (SQL)

is a comprehensive database language for
managing relational databases

12-12
 Structured Query Language

• Structured query language (SQL) is a popular query

language that is frequently used in all types of
applications. Data analysts and developers learn and
use SQL because it integrates well with different
programming languages.
• For example, they can embed SQL queries with the Java
programming language to build high-performing data
processing applications with major SQL database
systems such as Oracle or MS SQL Server. SQL is also
fairly easy to learn as it uses common English keywords
in its statements

12-13
 Components of SQL

• Relational database management systems use

structured query language (SQL) to store and manage
data. The system stores multiple database tables that
relate to each other. MS SQL Server, MySQL, or MS
Access are examples of relational database
management systems. The following are the
components of such a system.

12-14
 What Can SQL do?

• SQL can execute queries against a database

• SQL can retrieve data from a database
• SQL can insert records in a database
• SQL can update records in a database
• SQL can delete records from a database
• SQL can create new databases
• SQL can create new tables in a database
• SQL can create stored procedures in a database
• SQL can create views in a database
• SQL can set permissions on tables, procedures, and views

12-15
 What Can SQL do?

• A database most often contains one or more tables. Each table is

identified by a name (e.g. "Customers" or "Orders") and contain
records (rows) with data.
• Below is a selection from the Customers table used in the examples:

12-16
 What Can SQL do?

• SQL keywords are NOT case sensitive: select is the same as

SELECT
• Some database systems require a semicolon at the end of each
SQL statement.
• Semicolon is the standard way to separate each SQL statement in
database systems that allow more than one SQL statement to be
executed in the same call to the server.

12-17
 SQL Commands

• SELECT - extracts data from a database

• UPDATE - updates data in a database
• DELETE - deletes data from a database
• INSERT INTO - inserts new data into a database
• CREATE DATABASE - creates a new database
• ALTER DATABASE - modifies a database
• CREATE TABLE - creates a new table
• ALTER TABLE - modifies a table
• DROP TABLE - deletes a table
• CREATE INDEX - creates an index (search key)
• DROP INDEX - deletes an index

12-18
 The SQL SELECT Statement

• The SELECT statement is used to select

data from a database.
• Syntax:
SELECT column1, column2, ...
FROM table_name;

12-19
 The SQL SELECT Statement

SELECT CustomerName,City FROM Customers;

12-20
 The SQL SELECT Statement

Select ALL columns

If you want to return all columns, without specifying every column name, you can use
the SELECT * syntax:

12-21
 The SQL SELECT Statement

The SELECT DISTINCT statement is used to return only distinct

(different) values.
SELECT DISTINCT column1,
column2, ...
FROM table_name;
12-22
 The SQL WHERE Statement

The WHERE clause is used to filter records.

It is used to extract only those records that fulfill a
specified condition. SELECT column1, column2,
...
FROM table_name
WHERE condition; 12-23
 SQL

• SQL requires single quotes around

text values (most database systems
will also allow double quotes).
• However, numeric fields should not
be enclosed in quotes:
• Example
SELECT * FROM Customers
WHERE CustomerID=1;
12-24
 SQL
• Operators in The WHERE Clause
• You can use other operators than the = operator to filter
the search.

12-25
 SQL ‘=‘

Table: Products

ProductI ProductName Category Price Stock Manufacture

D Date

1 Laptop Electronics 1000 50 2023-01-15

2 Smartphone Electronics 800 200 2023-03-20
3 Chair Furniture 120 150 2022-12-10
4 Table Furniture 300 20 2022-11-05
5 Headphones Electronics 150 80 2023-02-25

SELECT ProductName FROM Products

WHERE Category = 'Electronics';
12-26
 SQL <> or !=

Table: Products

ProductI ProductName Category Price Stock Manufacture

D Date

1 Laptop Electronics 1000 50 2023-01-15

2 Smartphone Electronics 800 200 2023-03-20
3 Chair Furniture 120 150 2022-12-10
4 Table Furniture 300 20 2022-11-05
5 Headphones Electronics 150 80 2023-02-25

SELECT ProductName FROM Products

WHERE Category <> 'Furniture';
12-27
 SQL >

Table: Products

ProductI ProductName Category Price Stock Manufacture

D Date

1 Laptop Electronics 1000 50 2023-01-15

2 Smartphone Electronics 800 200 2023-03-20
3 Chair Furniture 120 150 2022-12-10
4 Table Furniture 300 20 2022-11-05
5 Headphones Electronics 150 80 2023-02-25

SELECT ProductName, Price

FROM Products WHERE Price > 500;
12-28
 SQL <

Table: Products

ProductI ProductName Category Price Stock Manufacture

D Date

1 Laptop Electronics 1000 50 2023-01-15

2 Smartphone Electronics 800 200 2023-03-20
3 Chair Furniture 120 150 2022-12-10
4 Table Furniture 300 20 2022-11-05
5 Headphones Electronics 150 80 2023-02-25

SELECT ProductName, Stock FROM

Products WHERE Stock < 100;
12-29
 SQL >=

Table: Products

ProductI ProductName Category Price Stock Manufacture

D Date

1 Laptop Electronics 1000 50 2023-01-15

2 Smartphone Electronics 800 200 2023-03-20
3 Chair Furniture 120 150 2022-12-10
4 Table Furniture 300 20 2022-11-05
5 Headphones Electronics 150 80 2023-02-25

SELECT ProductName, Price FROM

Products WHERE Price >= 150;
12-30
 SQL <=

Table: Products

ProductI ProductName Category Price Stock Manufacture

D Date

1 Laptop Electronics 1000 50 2023-01-15

2 Smartphone Electronics 800 200 2023-03-20
3 Chair Furniture 120 150 2022-12-10
4 Table Furniture 300 20 2022-11-05
5 Headphones Electronics 150 80 2023-02-25

SELECT ProductName, Price

FROM Products
WHERE Price <= 300; 12-31
 SQL (LIKE)

Table: Products

ProductI ProductName Category Price Stock Manufacture

D Date

1 Laptop Electronics 1000 50 2023-01-15

2 Smartphone Electronics 800 200 2023-03-20
3 Chair Furniture 120 150 2022-12-10
4 Table Furniture 300 20 2022-11-05
5 Headphones Electronics 150 80 2023-02-25

SELECT ProductName FROM Products

WHERE ProductName LIKE 'S%’; or
‘%phone% 12-32
 SQL (IN)

Table: Products

ProductI ProductName Category Price Stock Manufacture

D Date

1 Laptop Electronics 1000 50 2023-01-15

2 Smartphone Electronics 800 200 2023-03-20
3 Chair Furniture 120 150 2022-12-10
4 Table Furniture 300 20 2022-11-05
5 Headphones Electronics 150 80 2023-02-25

SELECT ProductName, Category FROM

Products WHERE Category IN
('Electronics', 'Furniture'); 12-33
 SQL (BETWEEN)

Table: Products

ProductI ProductName Category Price Stock Manufacture

D Date

1 Laptop Electronics 1000 50 2023-01-15

2 Smartphone Electronics 800 200 2023-03-20
3 Chair Furniture 120 150 2022-12-10
4 Table Furniture 300 20 2022-11-05
5 Headphones Electronics 150 80 2023-02-25

SELECT ProductName, Price

FROM Products
WHERE Price BETWEEN 100 AND 500; 12-34
 SQL (AND)

Table: Products

ProductI ProductName Category Price Stock Manufacture

D Date

1 Laptop Electronics 1000 50 2023-01-15

2 Smartphone Electronics 800 200 2023-03-20
3 Chair Furniture 120 150 2022-12-10
4 Table Furniture 300 20 2022-11-05
5 Headphones Electronics 150 80 2023-02-25

SELECT ProductName, Price FROM

Products WHERE Category = 'Electronics'
AND Price > 500; 12-35
 SQL (OR)

Table: Products

ProductI ProductName Category Price Stock Manufacture

D Date

1 Laptop Electronics 1000 50 2023-01-15

2 Smartphone Electronics 800 200 2023-03-20
3 Chair Furniture 120 150 2022-12-10
4 Table Furniture 300 20 2022-11-05
5 Headphones Electronics 150 80 2023-02-25

SELECT ProductName, Price, Stock

FROM Products
WHERE Price > 500 OR Stock < 50; 12-36
 SQL (NOT)

Table: Products

ProductI ProductName Category Price Stock Manufacture

D Date

1 Laptop Electronics 1000 50 2023-01-15

2 Smartphone Electronics 800 200 2023-03-20
3 Chair Furniture 120 150 2022-12-10
4 Table Furniture 300 20 2022-11-05
5 Headphones Electronics 150 80 2023-02-25

SELECT ProductName, Category

FROM Products
WHERE NOT Category = 'Electronics'; 12-37
 Queries in SQL

select attribute-list from table-list where condition

select Title from Movie where Rating = 'PG'

select Name, Address from Customer

select * from Movie where Genre like '%action%'

select * from Movie where Rating = 'R' order by

Title
12-38
 Modifying Database Content

insert into Customer values (9876, 'John

Smith', '602 Greenbriar Court', '2938 3212
3402 0299')
update Movie set Genre = 'thriller drama'
where title = 'Unbreakable'
delete from Movie where Rating = 'R'

12-39
 Modifying Database Content

UPDATE Employees ALTER DATABASE LibraryDB

SET Salary = 50000 COLLATE
WHERE EmployeeID = 123; Latin1_General_CI_AS;

DELETE FROM Orders

WHERE OrderDate < '2024-01-01';

INSERT INTO Products

(ProductName, Price, Stock)
VALUES ('Laptop', 1000, 50);

CREATE DATABASE LibraryDB;

12-40
 Table Creation

CREATE TABLE Books (

BookID INT PRIMARY KEY,
Title VARCHAR(100),
Author VARCHAR(100),
Price DECIMAL(10, 2)
);

ALTER TABLE Books

ADD Publisher VARCHAR(100);

DROP TABLE Books;

12-41
 Database Design

• One popular technique for designing

relational databases is called entity-
relationship (ER) modeling
• Chief among the tools used for ER
modeling is the ER diagram
– An ER diagram captures the important record
types, attributes, and relationships in a
graphical form

12-42
 Database Design

• These designations show the cardinality

constraint of the relationship

Figure 12.10 An ER diagram for the movie rental database

12-43