Database Management and Administration (Database 2)

Introduction to Database Systems

Learning Objectives

• Classify the components of a database system environment.
• Describe different types of database objects.
• Explain the components of a Database Application.
• Explain the types of database application architecture.
• Apply Structured Query Language (SQL) in creating database objects.

Components of Database System Environment

The database system environment consists of:

• Users
• Database
• Application
• DBMS (Database Management System)

Functions of DBMS

• Create
• Process
• Administer

Components of Database System

• Database: A systematic and organized collection of stored data, making data easily accessible and manageable using computer systems.
• Database Management System (DBMS): Software designed to create and manage a database and its objects. It allows users to store, retrieve, define, delete, and manage data in a database.
  • Examples: MySQL, Oracle, DB2
• Database application: A computer program/software that allows users to enter, update, read, and delete data from a database. It is a software for end-users to complete certain tasks.
  • Examples: Accounting systems, Airline Reservation systems (e.g., AirAsia), e-commerce applications (e.g., Shopee, Zalora).
• Database User: A person who uses a database and avails benefits from it.
  • Examples: DBA (Database Administrator), System analyst, Business analyst, naive user.

Database Objects

Database in DBMS generally consists of the following objects:

• Tables
• Schemas
• Views
• Aliases
• Indexes
• Sequences
• Triggers
• Stored procedures
• User-defined functions

Tables

• A table consists of data logically arranged in rows and columns.

  • Tables are also called files or relations.
  • Rows are also called records or tuples.
  • Columns are also called fields or attributes.

• Tables are created using SQL CREATE TABLE statement.

  • Syntax:

  CREATE TABLE <table_name> (
  column_name column_type...
  )
  

Schemas

• A schema is the skeleton structure that represents the logical view of the entire database.

• It's a collection of database objects associated with one particular database username.

• Example:

  • If you log on to the database as USER1 and create a table called EMPLOYEETBL, the default schema name for the table is USER1, and the database stores the table’s name as USER1.EMPLOYEETBL.
  • When you access a table that you own, you do not have to refer to the schema name.

• Schemas include things like Staff_Schema, Student_Schema, Degree_table, Foundation_table, NAcademicS_table, AcademicS_table.

• Schema is created using SQL CREATE SCHEMA statement.

  • Syntax:

  CREATE SCHEMA <schema_name> AUTHORIZATION <user_name>
  

  • Example:

  CREATE SCHEMA schema1 AUTHORIZATION user2
  

Views

• A view is a way of representing data from one or more base tables.

• It's a subset of a database that is based on a query that runs on one or more database tables.

• A view appears like a table and can contain all or some of the columns from the base tables.

• The columns in a view can be arranged differently compared to the original/base tables.

• Practical for reporting purposes.

• Views are created using SQL CREATE VIEW statement.

  • Syntax:

  CREATE VIEW <view_name> (<col_name>, <col_name1...) AS
  SELECT <cols>.. FROM <table_name>
  

  • Example:
    • Given an EMPLOYEE table with columns EmployeeID, LastName, HireDate, Salary
    • To create a view showing only EmployeeID and Salary:

  CREATE VIEW salaryview (Emp_ID, Emp_Salary) AS
  SELECT EmployeeID, Salary FROM employee
  

Aliases

• An alias is an alternate name for a table or view.

• You can use an alias to refer to a table or view, making its name shorter, reducing the query size, and increasing the readability of the query.

• Alias is created using SQL CREATE ALIAS statement.

  • Syntax:

  CREATE ALIAS <alias_name> FOR <table_name>
  

  • Example:

  CREATE ALIAS emp FOR employee
  

Indexes

• An index is a set of pointers that refer to rows and data in a base table.

• They are based upon one or more columns but stored as a separate entity.

• Good for performance (searching) and guarantee uniqueness.

• It allows for fast query by creating pointers to where data is stored within a database.

• Index is created using SQL CREATE UNIQUE INDEX statement.

  • Syntax:

  CREATE UNIQUE INDEX <index_name> ON <table_name>(<unique_column>)
  

  • Example:

  CREATE UNIQUE INDEX deptid ON department (deptid)
  

• Example Table and Index:

  • DEPARTMENT Table with columns DEPTID, DEPTNAME, COSTCENTER.
  • DEPTID Index points to the row number in the DEPARTMENT table.

Sequences

• A sequence is used to automatically generate integer or sequence numbers.

• It can support SMALLINT, BIGINT, INTEGER, and DECIMAL data types.

• Sequence is created using SQL CREATE SEQUENCE statement.

  • Syntax:

  CREATE SEQUENCE <seq_name>
  

• The following parameters can be used when sequences are created:

  • START WITH: Specifies the first value for the sequence.
  • INCREMENT BY: Step value by which a sequence is incremented.
  • MINVALUE or NO MINVALUE: A minimum value for a sequence to start with.
  • MAXVALUE or NO MAXVALUE: A maximum value for a sequence.
  • CYCLE or NO CYCLE: Specifies whether or not the sequence should continue to generate values after reaching either its maximum or minimum value.
  • CACHE or NO CACHE: Specifies whether or not to keep some pre-allocated values in memory for faster access (performance and tuning option).

• Sequence can be generated in three ways:

  • Ascending order, without range. E.g:

  CREATE SEQUENCE emp_id
  START WITH 1
  INCREMENT BY 1
  

  • Ascending order, within specified range. E.g:

  CREATE SEQUENCE emp_id
  START WITH 1
  INCREMENT BY 1
  NO MINVALUE
  MAXVALUE 1000
  

  • Ascending order, within specified range, and cycle back to the beginning to start again. E.g:

  CREATE SEQUENCE emp_id
  START WITH 1
  INCREMENT BY 1
  NO MINVALUE
  MAXVALUE 1000
  CYCLE
  

Stored Procedures

• A set of SQL statements and business logic with an assigned name that is stored in the database in compiled form.
• Client applications can call stored procedures using SQL CALL statement.
• Stored procedures can help improve application performance and reduce database access traffic.
  *Example: client application sends a single CALL myproc statement instead of multiple SQL statements.
• Stored procedures promote code reuse, modular programming, faster execution, and maximum security.
• Syntax:

CREATE PROCEDURE procedure_name ([IN | OUT | INOUT] parameter_name type,...)
BEGIN
< procedure_body >
END

*   Parameter:
    *   IN: Pass a value to the stored procedure
    *   OUT: Returns a value to the calling program
*   Example 1 – without parameters:

CREATE PROCEDURE proc_sample1()
BEGIN
INSERT INTO emp_table values (121, ‘Scott’);
SELECT * FROM emp_table;
END

• To call the procedure:
  mysql> call proc_sample1()
• Example 2 – with IN parameter:

CREATE PROCEDURE proc_sample2(IN countryName VARCHAR(255))
BEGIN
SELECT *
FROM offices
WHERE country = countryName;
END

• Example 3 – IN & OUT parameter:

CREATE PROCEDURE findMin (IN a NUMBER, IN b NUMBER, OUT c NUMBER)
BEGIN
IF a < b THEN
SET c = a;
ELSE
SET c = b;
END

• To call the procedure:

mysql> call findMin(23, 43, ?)

• Example 4 – INOUT parameter only:

CREATE PROCEDURE squareNo (INOUT x NUMBER)
BEGIN
SET x = x * x;
END

• To call the procedure:

mysql> call squareNo (23)

User-Defined Functions (UDF)

• A database object that allows users to extend the SQL language with their own logic.
• Different from stored procedures, UDFs must always return a value(s).
• UDF is created using SQL CREATE FUNCTION statement.
  • Syntax:

CREATE FUNCTION function_name (parameter_name type,...)
RETURNS return_datatype
BEGIN
< procedure_body >
RETURN [<value>|<sql_statement>]
END

• Types of functions:
  • Scalar functions
  • Table functions
• Scalar functions:
  • Returns a single value.
  • E.g.

CREATE FUNCTION emp_current_votes(employee_id char(6))
RETURN integer
BEGIN
DECLARE current_votes INTEGER;
SET current_votes = (SELECT count(*)FROM  emp_votes WHERE empno = employee_id);
RETURN current_votes;
END

* To call the scalar function:
  `mysql> SELECT firstname, lastname, emp_current_votes(empno) FROM employee`

• Table functions:

CREATE FUNCTION emp_vote_results()
RETURNS TABLE  (empno CHAR (6), votecount INTEGER)
BEGIN
RETURN SELECT empno, count(*) votes
FROM emp_votes
WHERE empno = employee_id
GROUP BY empnoEND
END

• To call the table function:

mysql> select * from table(emp_vote_results()) as t

Triggers

• A database object that defines operations that should occur automatically upon an INSERT, UPDATE, or DELETE operation on a table.
• Created using SQL CREATE TRIGGER statement.
• Types of triggers:
  • BEFORE
  • AFTER
• BEFORE trigger:
  • Activated before any table data is affected by any INSERT, UPDATE, or DELETE operation.
  • Syntax:

CREATE TRIGGER <trigger_name>
NO CASCADE BEFORE [INSERT|UPDATE|DELETE] ON <table_name>
REFERENCING NEW AS <table_object>
FOR EACH ROW MODE DB2SQL

*   E.g.
    *   Statements:

INSERT INTO cl_sched (class_code, day, starting) VALUES ('DB20001', 1, '10:00')
or
INSERT INTO cl_sched (class_code, day, starting, ending) VALUES ('DB20002', 2, '12:00', '15:00')

    *   Trigger:

CREATE TRIGGER default_class_end
NO CASCADE BEFORE INSERT ON cl_sched
REFERENCING NEW AS n
FOR EACH ROW MODE DB2SQL
WHEN (n.ending IS NULL)
SET n.ending = n.starting + 1 HOUR

• AFTER trigger:
  • Activated after any table data is affected by any INSERT, UPDATE, or DELETE operation.
  • Syntax:

CREATE TRIGGER <trigger_name>
NO CASCADE AFTER [INSERT|UPDATE|DELETE] ON <table_name>
REFERENCING OLD AS <table_object>
NEW AS <table_object>
FOR EACH ROW MODE DB2SQL

*   E.g.

CREATE TRIGGER audit_emp_sal
AFTER UPDATE OF salary ON employee
REFERENCING OLD AS o
NEW AS n
FOR EACH ROW MODE DB2SQL
INSERT INTO audit
VALUES (CURRENT TIMESTAMP,
' Employee ' | |
o.empno |
| ' salary changed from ' |
| |
CHAR(o.salary) |
| ' to ' |
| |
CHAR(n.salary) |
| ' by ' |
| |
USER)

Components of Database Application

A database application consists of 3 application logic components:

• PRESENTATION LOGIC
  • Input
  • Output
• PROCESSING LOGIC
  • Data processing
  • Business rules
  • Data management
• STORAGE LOGIC
  • Data storage
  • Data retrieval
• Presentation logic: Responsible for formatting the user interface, managing user input, and presenting output on the user’s screen.
• Processing logic: Handles data processing logic (validation and identification of processing errors), business rules logic, and data management logic (identifies data necessary for processing the transaction or query).
• Storage logic: Responsible for data storage and retrieval from the physical storage devices.

Database Application Architectures

Database application architectures can be distinguished by how their application logic components are distributed throughout the system.
It can be classified into:

• 1-tier Architecture
• Client/Server Architecture (n-tier)

1-Tier Architecture

• All application logic components reside on a single machine.
• E.g., Personal database applications (Microsoft Access, OpenOffice Base, LibreOffice).

Client/Server Architecture

• A networked computing model that distributes processes between clients and servers.
• A middleware is needed to connect client/server applications to a database.
• Middleware consists of an application programming interface (API) and a database driver to connect to a specific type of database.

Client-Side Development

• Application developers code their programs on a client and access the database using the application program interfaces (APIs).
• Client applications can be developed using any programming language (e.g., Java, C/C++).
• For application development with Java, Java applications access the database using Java Database Connectivity (JDBC) driver.

Application Development Overview (Example)

• Client:
  • Development Tools: IBM Data Studio, IDA, RSA, RAD, Visual Studio, ZendCore
  • Programming Language: Embedded static & dynamic SQL in C/C++, Cobol, Fortran, REXX, ODBC/CLI, JDBC/SQLJ, ADO, ADO.NET, OLE DB, PHP, RoR, etc.
  • IBM Data Server Client / Driver
  • Operating System
• Server:
  • DB2 Server
  • Database
  • Stored Procedures
  • User-defined Functions
  • Triggers
  • Operating System

Common APIs Used

• Open Database Connectivity (ODBC): VB.NET and C applications
• ADO.NET : VB.NET and C applications
• Java Database Connectivity (JDBC): Java applications
• Client/server architecture can span until n-tier architecture, but the most commonly used are:
  • 2-tier client/server architecture
  • 3-tier client/server architecture

Two-Tier Client/Server Application Development

• Server-side development (database server)
  • Stored Procedures
  • User-defined Functions
  • Triggers
• Client-side development (client application)
  • May require a DBMS client or driver to be installed
  • Database applications (in C/C++, .NET, COBOL, Java, etc.)
• Application logic components reside on two machines.
• Can use either two configurations:
  • Thin client
  • Fat client
• Thin client – presentation logic is handled by the client machine, processing logic, and storage logic is handled by the server.
  • E.g., Web-based email (Gmail, Yahoo! Mail, Outlook.com); Web-based messenger (Yahoo! IM, Facebook IM, WhatsApp Web, Skype for Web)
• Fat client – presentation and processing logic is handled by the client machine, and storage logic is handled by the server.
  • E.g., Desktop-based email (Microsoft Outlook, Thunderbird); Desktop-based messenger (Yahoo Messenger desktop, WhatsApp desktop, Skype for desktop)

Three-Tier Client/Server Architecture

• Application logic components reside on three machines.
• Use thin client configuration.
• Almost all web applications are working on a 3-tier architecture.
• API Location:
  • 2-Tier: API is located on the Client
  • 3-Tier: API is located on the Application Server

Advantages & Disadvantages

Review Questions

• What are the application logic components of a database application?
• Fill in the diagram for:
  • Database Application Architecture
    • 1-tier
    • 2-tier
      • Thin client
      • ___
• Fill in the diagram for:
  • Database Application Objects
    • Stored Procedures
    • _
    • Scalar functions
    • __
    • Triggers
    • _
    • AFTER trigger

Conclusion

• The database system environment consists of the Database, Database Management Systems, Database application, and user.
• Database objects include tables, views, sequences, indexes, triggers, user-defined functions, and stored procedures.
• Stored procedures help improve database performance and network traffic.
• User-defined functions always return a value.
• Triggers are activated/fired whenever an event such as insert, update, or delete occurs in the database server.
• A database application consists of three application logic components.
• Database application architectures can be distinguished by the way their application logic components are distributed throughout the system, either 1-tier or client-server (n-tier).