ITD 256 03

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


• Normalization

• Class Exercise

• Recap HW2





• 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




• EmployeeNumber -> (FirstName, LastName, Department, Email, Phone)

8/17/2019 ITD 256 03

6/70





• Email -> (EmployeeNumber, FirstName, LastName, Department, Phone)

8/17/2019 ITD 256 03

7/70





• Email -> (EmployeeNumber, FirstName, LastName, Department, Phone)

• Department -> Phone

8/17/2019 ITD 256 03

8/70

Agenda


• Normalization

• Class Exercise

• Recap HW2





• 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,



2.

PrescriptionNumber -> (Date, Drug, Dosage, CustomerName, CustomerPhone,CustomerEmail)

8/17/2019 ITD 256 03

14/70

1.PRESCRIPTION (PrescriptionNumber, Date, Drug, Dosage, CustomerName,



2.

PrescriptionNumber -> (Date, Drug, Dosage, CustomerName, CustomerPhone,CustomerEmail)

CustomerEmail -> (CustomerName, CustomerPhone)

8/17/2019 ITD 256 03

15/70



2. PrescriptionNumber -> (Date, Drug, Dosage, CustomerName, CustomerPhone,

CustomerEmail)CustomerEmail -> (CustomerName, CustomerPhone)



3.

a. CUSTOMER (CustomerName, CustomerPhone, CustomerEmail)

8/17/2019 ITD 256 03

16/70




CustomerEmail)CustomerEmail -> (CustomerName, CustomerPhone)



b. Make determinant PRIMARY key

3.


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)





c. Leave a copy of determinant as foreign key in original relation

3.a. 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)






c. Leave a copy of determinant as foreign key in original relationd. Create referential integrity constraint between original & new relation

3.



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





3.



c. PRESCRIPTION (PrescriptionNumber , Date, Drug, Dosage, “CustomerEmail ” )


8/17/2019 ITD 256 03

20/70




CustomerEmail)


3. a. CUSTOMER (CustomerName, CustomerPhone, CustomerEmail)


c. PRESCRIPTION (PrescriptionNumber , Date, Drug, Dosage, “CustomerEmail ” )


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


2. StudentNumber -> (LastName, FirstName, DormName, DormCost)

8/17/2019 ITD 256 03

24/70



DormName -> DormCost

8/17/2019 ITD 256 03

25/70




3.

a. DORM (DormName, DormCost)

8/17/2019 ITD 256 03

26/70




3.


b. DORM (DormName, DormCost)

8/17/2019 ITD 256 03

27/70




3.



c. STU_DORM (StudentNumber, LastName, FirstName, “DormName” )

8/17/2019 ITD 256 03

28/70




3.




d. DormName in STU_DORM must exist in DormName in DORM

8/17/2019 ITD 256 03

29/70




3.




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


Email -> (EmployeeNumber, LastName, Department, DeptPhone)

8/17/2019 ITD 256 03

34/70



Department -> DeptPhone

8/17/2019 ITD 256 03

35/70




3.

a. DEPARTMENT (Department, DeptPhone)

8/17/2019 ITD 256 03

36/70




3.


b. DEPARTMENT (Department, DeptPhone)

8/17/2019 ITD 256 03

37/70




3.



c. EMPLOYEE (EmployeeNumber, LastName, “Department” , Email)

8/17/2019 ITD 256 03

38/70




3.




d. Department in EMPLOYEE must exist in Department in DEPARTMENT

8/17/2019 ITD 256 03

39/70




3.




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



•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

•


• Class Exercise

• Recap HW2

•





• 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

•


• Class Exercise

• Recap HW2

•





• Class Exercise

• Test Discussion

• Quiz

8/17/2019 ITD 256 03

45/70

Agenda

•


• Class Exercise

• Recap HW2

•





• 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


• Standard Interfaces developed for accessing

databases

– OLEDB

– ADO.NET

– ODBC

8/17/2019 ITD 256 03

51/70


• 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


• 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

•


• Class Exercise

• Recap HW2

•




•

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


• 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


•Operational Systems: Online Transaction Processing (OLTP)

• BI System: OLAP (Online Analytical Processing)

8/17/2019 ITD 256 03

57/70

Agenda

•


• Class Exercise

• Recap HW2

•




•


• 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

•


• Class Exercise

• Recap HW2

•




•


• 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

•




•


• 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


In web processing environment XML provide a way for documents to be

A) Displayed

B) Removed

C) Transmitted

D) Altered


A d

8/17/2019 ITD 256 03

68/70

Agenda


• Normalization

• Class Exercise

• Recap HW2

•




•


• 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


• 4 Questions regarding 3rd normal form to write schema


• 1 Normalize a 2-themed list showing steps 1,2,3abcd & 4

– (25pts)

• 10 multiple choice questions from BI Systems


Agenda

8/17/2019 ITD 256 03

70/70

Agenda


• Normalization

• Class Exercise

• Recap HW2

•




•


• Test Discussion

• Quiz

ITD 256 03

Documents

Transcript of ITD 256 03