8/17/2019 ITD 256 03
1/70
ITD 256Database Concepts
Lecture 03
8/17/2019 ITD 256 03
2/70
Agenda
•Functional Dependency
• Normalization
• Class Exercise
• Recap HW2
• Project Discussion• Database Processing
• Business Intelligence Systems
• Datawarehouses and Datamarts
• Dimensional Databases• Class Exercise
• Test Discussion
• Quiz
8/17/2019 ITD 256 03
3/70
Agenda
•Functional Dependency
• Normalization
• Class Exercise
• Recap HW2
• Project Discussion• Database Processing
• Business Intelligence Systems
• Datawarehouses and Datamarts
• Dimensional Databases• Class Exercise
• Test Discussion
• Quiz
8/17/2019 ITD 256 03
4/70
Functional Dependencies
• EMPLOYEE relation
• EMPLOYEE (EmployeeNumber, FirstName, LastName, Department, Email, Phone)
• Functional Dependency is the determination of values of one more columns if
value of another column is already known
•
Identify the Functional Dependencies
8/17/2019 ITD 256 03
5/70
Functional Dependencies
• EMPLOYEE relation
• EMPLOYEE (EmployeeNumber, FirstName, LastName, Department, Email, Phone)
• EmployeeNumber -> (FirstName, LastName, Department, Email, Phone)
8/17/2019 ITD 256 03
6/70
Functional Dependencies
• EMPLOYEE relation
• EMPLOYEE (EmployeeNumber, FirstName, LastName, Department, Email, Phone)
• EmployeeNumber -> (FirstName, LastName, Department, Email, Phone)
• Email -> (EmployeeNumber, FirstName, LastName, Department, Phone)
8/17/2019 ITD 256 03
7/70
Functional Dependencies
• EMPLOYEE relation
• EMPLOYEE (EmployeeNumber, FirstName, LastName, Department, Email, Phone)
• EmployeeNumber -> (FirstName, LastName, Department, Email, Phone)
• Email -> (EmployeeNumber, FirstName, LastName, Department, Phone)
• Department -> Phone
8/17/2019 ITD 256 03
8/70
Agenda
•Functional Dependency
• Normalization
• Class Exercise
• Recap HW2
• Project Discussion• Database Processing
• Business Intelligence Systems
• Datawarehouses and Datamarts
• Dimensional Databases• Class Exercise
• Test Discussion
• Quiz
8/17/2019 ITD 256 03
9/70
Normalization
• Normalization is Process of breaking table with more than one
theme into single themed tables based on functional dependencies
• Follow two principles to call a table well-formed
– For a well-formed relation, every Determinant must be a Candidate key
– If not well-formed, break relation into two or more well-formed relations
• Any relation that has following 8 characteristics is in 1NF
• Normalized relations
are in 2NF & 3NF
8/17/2019 ITD 256 03
10/70
Normalization Process
1. Identify all Candidate keys in the relation
2. Identify all functional dependencies in the relation
3. Examine determinants , if it is not a candidate then
a. Place columns of functional dependency in a new relation
b. Make determinant PRIMARY key of new relation
c. Leave a copy of determinant as foreign key in original relation
d. Write referential integrity constraint between original & new
relation
4. Draw the Normalized tables
8/17/2019 ITD 256 03
11/70
Normalization Process
• Sample data for PRESCRIPTION table
8/17/2019 ITD 256 03
12/70
1. Identify all Candidate keys in the relation
1.
PRESCRIPTION (PrescriptionNumber, Date, Drug, Dosage, CustomerName,
CustomerPhone, CustomerEmail)
8/17/2019 ITD 256 03
13/70
1.PRSECRIPTION (PrescriptionNumber, Date, Drug, Dosage, CustomerName,
CustomerPhone, CustomerEmail)
2. Identify all functional dependencies in the relation
2.
PrescriptionNumber -> (Date, Drug, Dosage, CustomerName, CustomerPhone,CustomerEmail)
8/17/2019 ITD 256 03
14/70
1.PRESCRIPTION (PrescriptionNumber, Date, Drug, Dosage, CustomerName,
CustomerPhone, CustomerEmail)
2. Identify all functional dependencies in the relation
2.
PrescriptionNumber -> (Date, Drug, Dosage, CustomerName, CustomerPhone,CustomerEmail)
CustomerEmail -> (CustomerName, CustomerPhone)
8/17/2019 ITD 256 03
15/70
1.PRESCRIPTION (PrescriptionNumber, Date, Drug, Dosage, CustomerName,
CustomerPhone, CustomerEmail)
2. PrescriptionNumber -> (Date, Drug, Dosage, CustomerName, CustomerPhone,
CustomerEmail)CustomerEmail -> (CustomerName, CustomerPhone)
3. Examine determinants , if it is not a candidate then
a. Place columns of functional dependency in a new relation
3.
a. CUSTOMER (CustomerName, CustomerPhone, CustomerEmail)
8/17/2019 ITD 256 03
16/70
1.PRESCRIPTION (PrescriptionNumber, Date, Drug, Dosage, CustomerName,
CustomerPhone, CustomerEmail)
2. PrescriptionNumber -> (Date, Drug, Dosage, CustomerName, CustomerPhone,
CustomerEmail)CustomerEmail -> (CustomerName, CustomerPhone)
3. Examine determinants , if it is not a candidate then
a. Place columns of functional dependency in a new relation
b. Make determinant PRIMARY key
3.
a. CUSTOMER (CustomerName, CustomerPhone, CustomerEmail)
b. CUSTOMER (CustomerName, CustomerPhone, CustomerEmail)
8/17/2019 ITD 256 03
17/70
1.PRESCRIPTION (PrescriptionNumber, Date, Drug, Dosage, CustomerName,CustomerPhone, CustomerEmail)
2. PrescriptionNumber -> (Date, Drug, Dosage, CustomerName, CustomerPhone,CustomerEmail)
CustomerEmail -> (CustomerName, CustomerPhone)
3. Examine determinants , if it is not a candidate then
a. Place columns of functional dependency in a new relation
b. Make determinant PRIMARY key
c. Leave a copy of determinant as foreign key in original relation
3.a. CUSTOMER (CustomerName, CustomerPhone, CustomerEmail)
b. CUSTOMER (CustomerName, CustomerPhone, CustomerEmail)
c. PRESCRIPTION (PrescriptionNumber, Date, Drug, Dosage, “CustomerEmail ” )
8/17/2019 ITD 256 03
18/70
1.PRESCRIPTION (PrescriptionNumber, Date, Drug, Dosage, CustomerName,CustomerPhone, CustomerEmail)
2. PrescriptionNumber -> (Date, Drug, Dosage, CustomerName, CustomerPhone,CustomerEmail)
CustomerEmail -> (CustomerName, CustomerPhone)
3. Examine determinants , if it is not a candidate then
a. Place columns of functional dependency in a new relation
b. Make determinant PRIMARY key
c. Leave a copy of determinant as foreign key in original relationd. Create referential integrity constraint between original & new relation
3.
a. CUSTOMER (CustomerName, CustomerPhone, CustomerEmail)
b. CUSTOMER (CustomerName, CustomerPhone, CustomerEmail)
c. PRESCRIPTION (PrescriptionNumber , Date, Drug, Dosage, CustomerEmail )
d. CustomerEmail in PRESCRIPTION must exist in CustomerEmail in CUSTOMER
8/17/2019 ITD 256 03
19/70
1.PRESCRIPTION (PrescriptionNumber, Date, Drug, Dosage, CustomerName,
CustomerPhone, CustomerEmail)
2. PrescriptionNumber -> (Date, Drug, Dosage, CustomerName, CustomerPhone,CustomerEmail)
CustomerEmail -> (CustomerName, CustomerPhone)
3.
a. CUSTOMER (CustomerName, CustomerPhone, CustomerEmail)
b. CUSTOMER (CustomerName, CustomerPhone, CustomerEmail)
c. PRESCRIPTION (PrescriptionNumber , Date, Drug, Dosage, “CustomerEmail ” )
d. CustomerEmail in PRESCRIPTION must exist in CustomerEmail in CUSTOMER
8/17/2019 ITD 256 03
20/70
1.PRESCRIPTION (PrescriptionNumber, Date, Drug, Dosage, CustomerName,
CustomerPhone, CustomerEmail)
2. PrescriptionNumber -> (Date, Drug, Dosage, CustomerName, CustomerPhone,
CustomerEmail)
CustomerEmail -> (CustomerName, CustomerPhone)
3. a. CUSTOMER (CustomerName, CustomerPhone, CustomerEmail)
b. CUSTOMER (CustomerName, CustomerPhone, CustomerEmail)
c. PRESCRIPTION (PrescriptionNumber , Date, Drug, Dosage, “CustomerEmail ” )
d. CustomerEmail in PRESCRIPTION must exist in CustomerEmail in CUSTOMER
8/17/2019 ITD 256 03
21/70
Normalization Example 2
• Sample data for STU_DORM
8/17/2019 ITD 256 03
22/70
1. STU_DORM (StudentNumber, LastName, FirstName, DormName, DormCost)
8/17/2019 ITD 256 03
23/70
1. STU_DORM (StudentNumber, LastName, FirstName, DormName, DormCost)
2. StudentNumber -> (LastName, FirstName, DormName, DormCost)
8/17/2019 ITD 256 03
24/70
1. STU_DORM (StudentNumber, LastName, FirstName, DormName, DormCost)
2. StudentNumber -> (LastName, FirstName, DormName, DormCost)
DormName -> DormCost
8/17/2019 ITD 256 03
25/70
1. STU_DORM (StudentNumber, LastName, FirstName, DormName, DormCost)
2. StudentNumber -> (LastName, FirstName, DormName, DormCost)
DormName -> DormCost
3.
a. DORM (DormName, DormCost)
8/17/2019 ITD 256 03
26/70
1. STU_DORM (StudentNumber, LastName, FirstName, DormName, DormCost)
2. StudentNumber -> (LastName, FirstName, DormName, DormCost)
DormName -> DormCost
3.
a. DORM (DormName, DormCost)
b. DORM (DormName, DormCost)
8/17/2019 ITD 256 03
27/70
1. STU_DORM (StudentNumber, LastName, FirstName, DormName, DormCost)
2. StudentNumber -> (LastName, FirstName, DormName, DormCost)
DormName -> DormCost
3.
a. DORM (DormName, DormCost)
b. DORM (DormName, DormCost)
c. STU_DORM (StudentNumber, LastName, FirstName, “DormName” )
8/17/2019 ITD 256 03
28/70
1. STU_DORM (StudentNumber, LastName, FirstName, DormName, DormCost)
2. StudentNumber -> (LastName, FirstName, DormName, DormCost)
DormName -> DormCost
3.
a. DORM (DormName, DormCost)
b. DORM (DormName, DormCost)
c. STU_DORM (StudentNumber, LastName, FirstName, “DormName” )
d. DormName in STU_DORM must exist in DormName in DORM
8/17/2019 ITD 256 03
29/70
1. STU_DORM (StudentNumber, LastName, FirstName, DormName, DormCost)
2. StudentNumber -> (LastName, FirstName, DormName, DormCost)
DormName -> DormCost
3.
a. DORM (DormName, DormCost)
b. DORM (DormName, DormCost)
c. STU_DORM (StudentNumber, LastName, FirstName, “DormName” )
d. DormName in STU_DORM must exist in DormName in DORM
8/17/2019 ITD 256 03
30/70
Normalization Example 3
• Sample data for EMPLOYEE
8/17/2019 ITD 256 03
31/70
1. EMPLOYEE (EmployeeNumber, LastName, Department, Email, DeptPhone)
8/17/2019 ITD 256 03
32/70
1. EMPLOYEE (EmployeeNumber, LastName, Department, Email, DeptPhone)2. EmployeeNumber -> (LastName, Department, Email, DeptPhone)
8/17/2019 ITD 256 03
33/70
1. EMPLOYEE (EmployeeNumber, LastName, Department, Email, DeptPhone)2. EmployeeNumber -> (LastName, Department, Email, DeptPhone)
Email -> (EmployeeNumber, LastName, Department, DeptPhone)
8/17/2019 ITD 256 03
34/70
1. EMPLOYEE (EmployeeNumber, LastName, Department, Email, DeptPhone)2. EmployeeNumber -> (LastName, Department, Email, DeptPhone)
Email -> (EmployeeNumber, LastName, Department, DeptPhone)
Department -> DeptPhone
8/17/2019 ITD 256 03
35/70
1. EMPLOYEE (EmployeeNumber, LastName, Department, Email, DeptPhone)2. EmployeeNumber -> (LastName, Department, Email, DeptPhone)
Email -> (EmployeeNumber, LastName, Department, DeptPhone)
Department -> DeptPhone
3.
a. DEPARTMENT (Department, DeptPhone)
8/17/2019 ITD 256 03
36/70
1. EMPLOYEE (EmployeeNumber, LastName, Department, Email, DeptPhone)2. EmployeeNumber -> (LastName, Department, Email, DeptPhone)
Email -> (EmployeeNumber, LastName, Department, DeptPhone)
Department -> DeptPhone
3.
a. DEPARTMENT (Department, DeptPhone)
b. DEPARTMENT (Department, DeptPhone)
8/17/2019 ITD 256 03
37/70
1. EMPLOYEE (EmployeeNumber, LastName, Department, Email, DeptPhone)2. EmployeeNumber -> (LastName, Department, Email, DeptPhone)
Email -> (EmployeeNumber, LastName, Department, DeptPhone)
Department -> DeptPhone
3.
a. DEPARTMENT (Department, DeptPhone)
b. DEPARTMENT (Department, DeptPhone)
c. EMPLOYEE (EmployeeNumber, LastName, “Department” , Email)
8/17/2019 ITD 256 03
38/70
1. EMPLOYEE (EmployeeNumber, LastName, Department, Email, DeptPhone)2. EmployeeNumber -> (LastName, Department, Email, DeptPhone)
Email -> (EmployeeNumber, LastName, Department, DeptPhone)
Department -> DeptPhone
3.
a. DEPARTMENT (Department, DeptPhone)
b. DEPARTMENT (Department, DeptPhone)
c. EMPLOYEE (EmployeeNumber, LastName, “Department” , Email)
d. Department in EMPLOYEE must exist in Department in DEPARTMENT
8/17/2019 ITD 256 03
39/70
1. EMPLOYEE (EmployeeNumber, LastName, Department, Email, DeptPhone)2. EmployeeNumber -> (LastName, Department, Email, DeptPhone)
Email -> (EmployeeNumber, LastName, Department, DeptPhone)
Department -> DeptPhone
3.
a. DEPARTMENT (Department, DeptPhone)
b. DEPARTMENT (Department, DeptPhone)
c. EMPLOYEE (EmployeeNumber, LastName, “Department” , Email)
d. Department in EMPLOYEE must exist in Department in DEPARTMENT
8/17/2019 ITD 256 03
40/70
Agenda
•
Functional Dependency• Normalization
• Class Exercise
• Recap HW2
•
Project Discussion• Database Processing
• Business Intelligence Systems
• Datawarehouses and Datamarts
•Dimensional Databases
• Class Exercise
• Test Discussion
• Quiz
8/17/2019 ITD 256 03
41/70
Class Exercise
•Explain the meaning of the following expression.
A (B, C)
Given this expression, tell if it is also true that:
A B and A C
• Explain the meaning of the following expression:
(D, E) F
Given this expression, tell if it is also true that:
D F and E F
• Normalization process is based on ________________________________.
8/17/2019 ITD 256 03
42/70
Agenda
•
Functional Dependency• Normalization
• Class Exercise
• Recap HW2
•
Project Discussion• Database Processing
• Business Intelligence Systems
• Datawarehouses and Datamarts
•Dimensional Databases
• Class Exercise
• Test Discussion
• Quiz
HW 2
8/17/2019 ITD 256 03
43/70
1. Explain the difference between a primary key and a candidate key. (2pts)
2. What is a surrogate key and why would you use one? (2pt)
3. Explain the term foreign key. (2pt)
4. Define the term referential integrity constraint. (2pts)
5. Define the terms functional dependency and determinant. (2pts)
6. Define the term primary key in terms of functional dependencies. (2pts)
7. Draw Relations Equivalent set of terms for Table, Row, and Column. (2pts)
8. In your own words, describe the purpose of the normalization process. (2pts)
9. Apply the normalization process to figure above to develop a set of normalized
relations (assume PetName is unique in the relation above). Show the results of
each of the steps in the normalization process 1,2,3a,3b,3c,3d. (5pts)
10. Draw and populate the two normalized tables from Q9. (4pts)
8/17/2019 ITD 256 03
44/70
Agenda
•
Functional Dependency• Normalization
• Class Exercise
• Recap HW2
•
Project Discussion• Database Processing
• Business Intelligence Systems
• Datawarehouses and Datamarts
•Dimensional Databases
• Class Exercise
• Test Discussion
• Quiz
8/17/2019 ITD 256 03
45/70
Agenda
•
Functional Dependency• Normalization
• Class Exercise
• Recap HW2
•
Project Discussion• Database Processing
• Business Intelligence Systems
• Datawarehouses and Datamarts
•Dimensional Databases
• Class Exercise
• Test Discussion
• Quiz
8/17/2019 ITD 256 03
46/70
Database Processing
8/17/2019 ITD 256 03
47/70
Database Processing
• Stored Procedures
• Stored Procedure is like a computer program stored
within the database – Any SQL code to make changes to the database can be
executed from a stored procedure
– Application programs, web applications and query users can
invoke stored procedures
– Parameters can be passed to stored procedure to perform
action or receive results
8/17/2019 ITD 256 03
48/70
Database Processing
• Triggers
• Trigger is program stored within database that is
executed when specific event occurs – The events are typically INSERTS, UPDATES, or DELETES
– The events are handled BEFORE, AFTER, or INSTEAD OF trigger
logic
– Action code can be written or Stored Procedures can be called
as a trigger response
8/17/2019 ITD 256 03
49/70
Web Database Processing
8/17/2019 ITD 256 03
50/70
Web Database Processing
• Standard Interfaces developed for accessing
databases
– OLEDB
– ADO.NET
– ODBC
8/17/2019 ITD 256 03
51/70
Web Database Processing
• Hypertext Markup Language (HTML)
– Most common web page standard
– Hypertext means other objects are included
(pictures, audio, video, maps, etc.)
8/17/2019 ITD 256 03
52/70
Web Database Processing
• Extensible Markup Language (XML)
– Common way of defining document structure and
transmitting them between computers
– Standard means of submitting & receiving data from
the database(s) in web development environment
8/17/2019 ITD 256 03
53/70
Agenda
•
Functional Dependency• Normalization
• Class Exercise
• Recap HW2
•
Project Discussion• Database Processing
• Business Intelligence Systems
• Datawarehouses and Datamarts
•
Dimensional Databases• Class Exercise
• Test Discussion
• Quiz
8/17/2019 ITD 256 03
54/70
Business Intelligence Systems
• Business Intelligence (BI) systems are also called DecisionSupport (DSS) systems
• BI assist Management in analysis of past/current activities and
prediction of future events
• BI systems support management assessment, analysis, planning,
control, and decision making.
• Note - BI systems do not support operation activities, only
analyze the data
8/17/2019 ITD 256 03
55/70
Business Intelligence Systems
• BI application need to read & process from Operational Data
• Querying Data for BI applications is resources intensive
• Operational data is limited for BI use, so they need to be separate
• Therefore large organizations extract separate BI databases from
operational databases
– BI systems use “Data Dictionary” which is reference repository containing
metadata
8/17/2019 ITD 256 03
56/70
Business Intelligence Systems
•Operational Systems: Online Transaction Processing (OLTP)
• BI System: OLAP (Online Analytical Processing)
8/17/2019 ITD 256 03
57/70
Agenda
•
Functional Dependency• Normalization
• Class Exercise
• Recap HW2
•
Project Discussion• Database Processing
• Business Intelligence Systems
• Datawarehouses and Datamarts
•
Dimensional Databases• Class Exercise
• Test Discussion
• Quiz
8/17/2019 ITD 256 03
58/70
Data Warehouses
8/17/2019 ITD 256 03
59/70
Data Marts
• Data Mart is a collection of data that is smaller thandata warehouse and addresses a particular component
of business
• Users of data mart are knowledgeable analysts of that
business function
8/17/2019 ITD 256 03
60/70
Data Marts
A d
8/17/2019 ITD 256 03
61/70
Agenda
•
Functional Dependency• Normalization
• Class Exercise
• Recap HW2
•
Project Discussion• Database Processing
• Business Intelligence Systems
• Datawarehouses and Datamarts
•
Dimensional Databases• Class Exercise
• Test Discussion
• Quiz
Di i l D t b
8/17/2019 ITD 256 03
62/70
Dimensional Databases
– Databases in data warehouse or data mart are built to different
database design (not normalized relational design) calleddimensional database.
– Dimensional Databases are designed for efficient data queries
and analysis
8/17/2019 ITD 256 03
63/70
Dimensional Databases
– Star Schema is a data warehouse modeling technique used torepresent the multi-dimensional data in fact and dimension tables
A d
8/17/2019 ITD 256 03
64/70
Agenda
• Functional Dependency
• Normalization
• Class Exercise
• Recap HW2
•
Project Discussion• Database Processing
• Business Intelligence Systems
• Datawarehouses and Datamarts
•
Dimensional Databases• Class Exercise
• Test Discussion
• Quiz
8/17/2019 ITD 256 03
65/70
Class Exercise
Business Intelligence (BI) systems do which of the following?A) Analyze current and past activities
B) Predict future events for strategic advantage
C) Record and process transactions
D) Both A and B
E) All of the above
Business Intelligence (BI) systems fall into which of the following categories?
A) Processing
B) Reporting
C) Decision Support
D) Both A and B
E) Both B and C
8/17/2019 ITD 256 03
66/70
Class Exercise
Star Schema is used to graphically represent what kind of data?A) Operational
B) Missing
C) Multi-dimensional
D) Both A and B
E) None of the above
A particular component of business data subset of the datawarehouse?
A) ERD
B) OLAP
C) ODBC
D) DataMart
E) Data Dictionary
8/17/2019 ITD 256 03
67/70
Class Exercise
Data Dictionary is a reference repository containingA) User data
B) Metadata
C) SQL code
D) Both A and B
E) None of the above
In web processing environment XML provide a way for documents to be
A) Displayed
B) Removed
C) Transmitted
D) Altered
E) None of the above
A d
8/17/2019 ITD 256 03
68/70
Agenda
• Functional Dependency
• Normalization
• Class Exercise
• Recap HW2
•
Project Discussion• Database Processing
• Business Intelligence Systems
• Datawarehouses and Datamarts
•
Dimensional Databases• Class Exercise
• Test Discussion
• Quiz
8/17/2019 ITD 256 03
69/70
Test Discussion
• Test 1 Lecture 01, Lecture 02 & Lecture 03 (150pts)
• 10 multiple choice questions from Relational Databases
– (3pts each = 30pts)
• 5 questions related to Relational Databases
– (3pts each = 15pts)
• 4 Questions regarding 3rd normal form to write schema
– (10pts each = 40pts)
• 1 Normalize a 2-themed list showing steps 1,2,3abcd & 4
– (25pts)
• 10 multiple choice questions from BI Systems
– (4pts each = 40pts)
Agenda
8/17/2019 ITD 256 03
70/70
Agenda
• Functional Dependency
• Normalization
• Class Exercise
• Recap HW2
•
Project Discussion• Database Processing
• Business Intelligence Systems
• Datawarehouses and Datamarts
•
Dimensional Databases• Class Exercise
• Test Discussion
• Quiz
Top Related