Foundations of Business
Intelligence:
Databases and Information
Management
© 2010 by Prentice Hall
LEARNING OBJECTIVES
• Basic concepts of DBMS
• Concepts of Entity Relationship Model
• Basic Operation of Relational DBMS
• Evaluate tools and technologies for accessing
information from databases to improve business
performance and decision making
• Assess the role of information policyfor managing
firm’s data resources
© 2010 by Prentice Hall
Concepts of DBMS
• Database – a collection of data that is logically
coherent.
• DBMS – Database Management System
• Collection of interrelated data and set of programs to
store and access data both conveniently and efficiently
about a particular enterprise
– A combination of 5 components:
• Hardware
• Software
• Data
• Users
• Procedures © 2010 by Prentice Hall
Database Terminology
• Entity: An entity is a “thing” or “object” in the real world that
is distinguishable from all other objects.
• Attribute: Attributes are descriptive properties possessed by
each member of the entity set.
• Tables within a relational database hold sets of data using rows
and columns
• Rows/tuple (records) appear horizontally in a report, and
contain one or more columns
• Columns (fields) are named data elements and appear vertically
in a report
• Primary Keys identify uniqueness in a row
• Foreign Keys A primary key referenced from another table is
called a foreign key. © 2010 by Prentice Hall
Table/Entity name
Attribute names
Tables in SQL
Product
PName Price Category Manufacturer
Tuples or rows
Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi
© 2010 by Prentice Hall
An Example of a Table
Faculty
(Entity)
Fields
Records Name Designation Phone DOJ
ASM Asst. Prof 11111 12-1-13
MAH Asst. Prof 22222 12-1-12
MAX Sr. Lecturer 33333 11-2-14
© 2010 by Prentice Hall
The Database Approach to Data Management
Relational Database Tables
A relational database organizes data in the form of two-dimensional tables. Illustrated here are tables for the entities
SUPPLIER and PART showing how they represent each entity and its attributes. Supplier_Number is a primary key for the
SUPPLIER table and a foreign key for the PART table.
© 2010 by Prentice Hall
The Database Approach to Data
Management
Relational Database Tables (cont.)
Relational Database Tables (cont.)
© 2010 by Prentice Hall
The Database Approach to Data Management
The Three Basic Operations of a Relational DBMS
The select, project, and join operations enable data from two different tables to be combined and only selected attributes
to be displayed. © 2010 by Prentice Hall
The Database Approach to Data
Management
• Entity-relationship diagram
• Used by database designers to document
the data model
• Illustrates relationships between entities
© 2010 by Prentice Hall
Relationship Types
• One-to-One : relationship is single valued in both directions
– A manager manages one department; a department has only one manager.
• One-to-Many : relationship is multi-valued in one direction - one row in the
parent table is associated with many rows in the dependent table.
– One department has many employees.
• Many-to-Many : relationships are multi-valued in both directions. This type
of relationship can be expressed in a table with a column for each entity.
(crosswalk table)
– An employee can work on more than one project, and a project can have
more than one employee assigned. Employee, Project, and
Employee/Project tables.
© 2010 by Prentice Hall
An Entity-Relationship Diagram
This diagram shows the relationships between the entities ORDER, LINE_ITEM,
PART, and SUPPLIER
Boxes represent entities, lines are relationship. A line connecting two entities that ends
in two short marks designates a One- to- One relationship. A line connecting two
entities that ends with a crow’s foot topped by a short mark indicated a 0ne-to-many
relationship. © 2010 by Prentice Hall
Operations on relations
• In a relational database, we can define several
operations to create new relations out of the existing
ones.
• Basic operations:
– Insert
– Delete
– Update
– Select
– Project
– Join
– Union
– Intersection
© 2010 by Prentice Hall
Insert operation
An unary operation.
Insert a new tuple/row into the relation.
© 2010 by Prentice Hall
Delete operation
An unary operation.
Delete a tuple defined by a criterion
from the relation.
© 2010 by Prentice Hall
Update operation
An unary operation.
Changes the value of some attributes of a
tuple.
© 2010 by Prentice Hall
Select operation
An unary operation.
It is applied to one single relation and creates another
relation.
The tuples in the resulting relation are a subset of the
tuples in the original relation.
Use some criteria to select
© 2010 by Prentice Hall
Project operation
An unary operation.
It is applied to one single relation and creates
another relation.
The attributes in the resulting relation are a subset
of the attributes in the original relation.
© 2010 by Prentice Hall
Join operation
A binary operation.
Combines two relations based on common
attributes.
© 2010 by Prentice Hall
Union operation
A binary operation.
Creates a new relation in which each tuple is either
in the first relation, in the second, or in both.
The two relations must have the same attributes.
© 2010 by Prentice Hall
Intersection operation
A binary operation.
Creates a new relation in which each tuple is a member
in both relations.
The two relations must have the same attributes.
© 2010 by Prentice Hall
Create Table
Create table <table Name> CREATE TABLE Persons
( column_name1 <datatype> (
<size>, P_Id int,
column_name2 <datatype> LastName varchar(255),
<size>, FirstName varchar(255),
……………………………… Address varchar(255),
…….., City varchar(255)
column_name_n <datatype> )
<size>,
)
create table orders
(
Order_code varchar (25) not null,
Order_date date,
OrderPrice int,
primary key (order_code)
); © 2010 by Prentice Hall
Insert Data
Insert into < table_name> INSERT INTO orders
values( ‘Expressin1’, ‘Expression2’, VALUES ("Soap-104", " 1/11/2017", 500);
….’Expressionn’)
If we want to see all the records in the Table
Select * from <Table Name> SELECT * FROM orders;
If we want to see the specific column
<Select> Table_name.column_name1, SELECT orders.Order_code,
Table_name.column-name2 from < orders. OrderPrice
Table Name> FROM orders;
Select record with condition
<Select> * from < Table Name> SELECT *
where < column Name= FROM orders
WHERE Order_code='Soap-104';
‘Expression’>
© 2010 by Prentice Hall
Particular column which are distinct
Select Disctinct < column Name> from SELECT DISTINCT City
< table Name> FROM Persons;
Arrange by ascending
Select * from <table Name> order by < SELECT *
column_name> FROM Persons
ORDER BY LastName;
Arrange by Descending
Select * from <table Name> order by < SELECT *
column_name> Desc; FROM Persons
ORDER BY LastName DESC;
Select from and Option
Select * from <Table Name> where < SELECT *
column Name= ‘Expression’ and < column FROM Persons
name= ‘ Expression’; WHERE LastName='Hasan'
AND City='';
Select from and/or Option
Select * from < table Name> where < SELECT *
column_name= ‘Expression’ and FROM Persons
( column name= ‘ Expression’ or WHERE LastName='Hasan'
Column name=’ Expression’); AND (FirstName= 'Md' or City='Rajbari');
© 2010 by Prentice Hall
Particular column which are distinct
Select Disctinct < column SELECT DISTINCT City
Name> from < table Name> FROM Persons;
Insert value
INSERT INTO table_name INSERT INTO Persons
VALUES (value1, value2, value3,...) VALUES (4,'Nilsen', 'Johan', 'Bakken 2', '')
Insert Data Only in Specified Columns
INSERT INTO Persons (P_Id, LastName, FirstName)
VALUES (4, 'Sayem', 'Mr')
UPDATE
UPDATE table_name UPDATE Persons
SET column1=value, SET Address='Aftabnagar', City=''
column2=value2,... WHERE LastName='Sayem' FirstName='Mr'
WHERE some_column=some_value
UPDATE Warning(Be careful when updating records. If we had omitted
the WHERE clause in the example above, like this)
UPDATE Persons
SET Address='Rajbari', City='Gazipur'
© 2010 by Prentice Hall
DELETE
DELETE FROM DELETE FROM Persons
table_name WHERE LastName='Sayem'
WHERE AND FirstName='Mr'
some_column=some_value
Delete All Rows
DELETE * FROM DELETE * FROM persons
table_name
Select TOP
SELECT TOP 3 * FROM
Persons
SELECT TOP 30 PERCENT *
FROM Persons
© 2010 by Prentice Hall
Functions
Total
SELECT SUM(column_name) SELECT SUM(OrderPrice) AS
FROM table_name OrderTotal FROM Orders
Average
SELECT AVG(column_name) SELECT AVG(OrderPrice) AS
FROM table_name OrderAverage FROM Orders
MAX
SELECT MAX(column_name) SELECT MAX(OrderPrice) AS
FROM table_name LargestOrderPrice FROM Orders
MIN
SELECT MIN(OrderPrice) AS
MinimumOrderPrice FROM Orders
SELECT AVG(CGPA), DEPARTMENT
FROM STUDENT
GROUP BY DEPARTMENT;© 2010 by Prentice Hall
Managing Data Resources
Setting up a database is only a start. In order to make
sure that the data for your business remain accurate,
reliable, and readily available to those who need it,
need policies and procedures.
Establishing an information policy : Firm’s need to have
rules, procedures, roles for sharing, managing, standardizing,
classifying and inventorying information data
• E.g., What employees are responsible for updating
sensitive employee information
© 2010 by Prentice Hall
Managing Data Resources
a. Data administration: Firm function responsible for specific
policies and procedures to manage data. Includes developing
information policy, planning of data, overseeing logical database
design, data dictionary development, and monitoring how end-
user use data.
b. Data governance: Policies and processes for managing
availability, usability, integrity, and security of enterprise data,
especially as it relates to government regulations for privacy,
security, marinating data quality, and compliance with
regulation.
c. Database administration : Defining, organizing,
implementing, maintaining database; performed by database
design and management group © 2010 by Prentice Hall
