IT 57 DATABASE MANAGEMENT SYSTEM 2

landyaddaInternet και Εφαρμογές Web

10 Νοε 2013 (πριν από 4 χρόνια και 11 μήνες)

594 εμφανίσεις



Republic of the Philippines

ISABELA STATE UNIVERSITY

Cauayan Campus

Cauayan City, Isabela





COLLEGE OF COMPUTING AND INFORMATION TECHNOLOGY





Course Syllabus in



IT 57


DATABASE MANAGEMENT SYSTEM 2





2nd

Semester

School Year 2010

-

20
1
1




I.

COURSE

NUMBER:


IT
213

II.

COURSE TITLE:



DATABASE MANAGEMENT SYSTEM 2

III.

COURSE DESCRIPTION:


This course deals with
the functions and features of Structured Query Language (SQL). These include SQL,

and error c
orrection and debugging.

The student

will gain proficienc
y in SQL coding through the

use of extensive hands
-
on workshop exercises utilizing

SQL in an interactive environment.

IV.

CREDIT UNITS:



3 UNITS

V.

COURSE PRE
-
REQUISITE:

DATABASE MANAGEMENT SYSTEM

VI.

COURSE OBJECTIVES:


At the end of this course, the student should

be able to:

1.

create tables, forms and queries using SQL;

2.

apply SQL Operators and SQL Functions in the created tables in SQL;

3.

design a database based from the different cycles in creating databases;

4.

connect databases into an information system; and

5.

manage a

database embedded in an information system








COURSE SYLLABUS IN

IT
57


DATABASE MANAGEMENT SYSTEM 2



SPECIFIC OBJECTIVES


COURSE CONTENT

TIME
ALLOT.

TEACHING
STRATEGIES

MATERIALS USED

EVALUATIVE
MEASURES


VALUES


INPUT


o

To orient the students of
v
ision, mission, goals and
objectives of the
department and the
university.



o

University Mission/Vision

o

CCIT

Goals/Objectives


30 mins.


Orientation


Discussion


Printed Copies


Presentation


Quiz


Graded
Recitation



Information
Update


Open
-
mindedness


o

Discuss Structured Query
Language

o

Explain the concepts of
data models

o

Create database,
database schema, data
types

o

Create data structures,
SQL constraints and SQL
indexes

o

Add table rows in the
database

o

Save table changes

o

List table rows

o

Update table rows



I. Introduction to Structured Query
Language (SQL)

o

Introduction to SQL

o

Data Definition Commands



The Database Model



Creating the Database



The Database Schema



Data Types



Creating Table Structure



SQL Constraints



SQL Indexes

o

Data Manipulation Commands



Adding
Tables Rows



Saving Table Changes



Listing Table Rows



Updating Table Rows



2

hrs


Lecture

Discussion


Oral
Interaction


Library work


Presentation


Modules



Quiz


Oral
Recitation


Seatwork


Intelligence


Independen
ce


Objectivity


Critical
Thinking



SP
ECIFIC OBJECTIVES


COURSE CONTENT

TIME
ALLOT.

TEACHING
STRATEGIES

MATERIALS USED

EVALUATIVE
MEASURES


VALUES


INPUT

o

Restore table contents

o

Delete table rows

o

Insert Table Rows with a
Select Subquery

o

Use Select queries

with
conditional restrictions

o

Explain
the rule of
precedence

o

Use logical operators
and special operators

o

Change Column’s Data
type and characteristics

o

Add and drop column

o

Copy parts of tables

o

Add primary and foreign
key designation

o

Delete a table from the
database




Restoring Table Contents



De
leting Table Rows



Inserting Table Rows with a
Select Subquery

o

Select Queries



Selecting Rows with
Conditional Restrictions



The Rule of Precedence



Logical Operators: And, Or,
and Not



Special Operators

o

Advanced Data Definition
Commands



Changing Column’s Data

Type



Changing a Column’s Data
Characteristics



Adding a Column



Dropping a Column



Advanced Data Updates



Copying Parts of Tables



Adding Primary and Foreign
Key Designation



Deleting a Table from the
Database

o

Advanced Select Queries



2

hrs


Lecture

Discussion


Library work


Presentation


Modules


Internet Topic


Seatwork


Quiz


Initiative


Attentiveness


Intelligence


Independen
ce


Objectivity




SPECIFIC OBJECTIVES


COURSE CONTENT

TIME
ALLOT.

TEACHING
STRATEGIES

MATERIALS USED

EVALUATIVE
MEASURES


VALUES


INPUT

o

Order list

o

List unique values

o

Use aggregate functions

o

Group data

o

Create a new view of
database

o

Join database tables



Ordering a Listing



Listing Unique Values



Aggregate Functions



Grouping Data

o

Virtual Tables: Creating a View

o

Joining Database Tables



Jo
ining Tables with an Alias



Arithmetic Operators:



Recursive Joins



Other Joins


2

hrs


Lecture

Discussion


Library Work


Presentation


Modules


Book
s


Internet Topic


Quiz


Oral
Recitation


Intelligence


Independen
ce


Critical
Thinking





o

Use relational
Set
operators

o

Apply

SQL JOIN
operators

in the
database design

o

Use Subqueries and
Correlated Queries

o

Develop a database
using
Multirow Subquery
Operators ANY and ALL



II. Advanced SQL

o

Relational Set Operators



UNION



UNION ALL



INTERSECT



MINUS



Syntax Alternat
ives

o

SQL JOIN Operators



Cross Join



Natural Join



Join USING Clause



JOIN ON Clause



Outer Joins

o

Subqueries and Correlated
Queries



WHERE Subqueries



IN Subqueries



HAVING Subqueries

o

Multirow Subquery

Operators
ANY and ALL



FROM Subqueries



Attribute List Subq
ueries



6

hrs


Lecture

Discussion


Library Works


Presentation


Modules


Quiz


Oral
Recitation


Preliminary
Examination


Intelligence


Independen
ce


Objectivity


Critical
Thinking



SPECIFIC OBJECTIVES


COURSE CONTENT

TIME
ALLOT.

TEACHING
STRATEGIES

MATE
RIALS USED

EVALUATIVE
MEASURES


VALUES


INPUT

o

Use SQL Functions





Correlated Subqueries

o

SQL Functions



Date and Time Functions



Numeric Functions



String Functions



Conversion Functions


2

hrs


Lecture

Discussion


Library Works


Presentation


Modules


Quiz


O
ral
Recitation


Intelligence


Analytical
Thinking



o

Explain database design

o

Discuss information
system

o

Explain and enumerate
the Systems
Development Life Cycle
(SDLC)

o

Discuss the Database
Life Cycle (DBLC)

o

Explain the Database
Design Strategies

o

Different
iate centralized
from decentralized
design


II. Database Design

o

The Information System

o

The Systems Development Life
Cycle (SDLC)



Planning



Analysis



Detailed System Design



Implementation



Maintenance

o

Database Life Cycle (DBLC)



The Database Initial Study



Datab
ase Design



Implementation and Loading



Testing and Evaluation



Operation



Maintenance and Evolution

o

Database Design Strategies

o

Centralized vs. Decentralized
Design




2 hrs


Lecture
Discussion


Brainstorming


Library work


Presentation


Modules


Books


Inter
net Topics



Oral
Recitation


Quiz


Homework




Attentiveness


Cooperation


Diligence


Critical
Thinking


o

Explain Advanced
database concept

IV. Advanced Database Concept:
Transaction Management and
Concurrency Control











SPECIFIC OBJECTIVES


COURS
E CONTENT

TIME
ALLOT.

TEACHING
STRATEGIES

MATERIALS USED

EVALUATIVE
MEASURES


VALUES


INPUT

o

Discuss what transaction
is

o

Evaluate transaction
results, transaction
properties, transaction
management with SQL
and the transaction log

o

Discuss Concurrency
contr
ol, concurrency
control with locking
methods, concurrency
control with time
stamping methods and
concurrency control
with Optimistic methods



o

What is Transaction?



Evaluating Transaction Results



Transaction Properties



Transaction Management
with SQL



The Tr
ansaction Log

o

Concurrency Control



Lost Updates



Uncommitted Data



Inconsistent Retrievals



The Scheduler

o

Concurrency Control with Locking
Methods



Lock Granularity



Lock Types



Two
-
Phase Locking to Ensure
Serializability



Deadlock

o

Concurrency Control with Time
S
tamping Methods



Wait/Die and Wound/Wait
Schemes

o

Concurrency Control with
Optimistic Methods

a.

Database Recovery
Management

-

Transaction Recovery


4

hrs


Lecture
Discussion


Oral
Interaction


Library Works


Presentation


Modules


Internet Topics


Exercises


Re
citation


Quiz


Seatwork


Homework




Attentiveness


Cooperation


Patience


Analytical
Thinking


o

Explain database
performance tuning and
Query Optimization

o

Enumerate and explain
Database Performance
-
Tuning Concepts

V. Database Performance Tuning and
Query

Optimization

o

Database Performance
-
Tuning
Concepts



Performance Tuning: Client
and Server



DBMS Architecture



2 hrs


Lecture
Discussion


Oral
Interaction


Presentation


Modules



Recitation


Quiz




Attentiveness


Cooperation




SPECIFIC OBJECTIVES


COURS
E CONTENT

TIME
ALLOT.

TEACHING
STRATEGIES

MATERIALS USED

EVALUATIVE
MEASURES


VALUES


INPUT

o

Enumerate and discuss
the different phases of
query processing

o

Discuss indexes and
query optimization

o

Use hints to affect
optimizer choices

o

Use SQL performance
tun
ing

o

Formulate Query

o

Explain DBMS
Performance Tuning





Database Statistics

o

Query Processing



SQL Parsing Phase



SQL Execution Phase



SQL Fetching Phase

o

I
ndexes and Query Optimization

o

Optimizer Choices



Using Hints to Affect
Optimizer Choices

o

SQL Performance Tun
ing



Index Selectivity

Conditional Expressions

o

Query Formulation

o

DBMS Performance Tuning

o

Query Optimization Example



4 hrs


Exercises


Lecture

Discussion


Oral
Interaction



Modules


Books


Internet Topics


Homework


Seatwork


Quiz


Midterm
Examination


Co
operation


Diligence


Initiative


Critical
Thinking



o

Explain database
management systems

o

Discuss the evolution of
Distributed DBMS

o

Enumerate and discuss
the advantages and
disadvantages of
DDBMS

o

Enumerate and discuss
the characteristics of
DDBMS

o

Discuss
the levels of data
and process distribution

VI. Distributed Database Management
Systems

o

The Evolution of Distributed
Database Management Systems

o

DDBMS Advantages and
Disadvantages

o

Distributed Processing and
Distributed Databases

o

Characteristics of Distribu
ted
Database Management Systems

o

D
DBMS Components

o

Levels of Data and Process
Distribution



Single
-
Site Processing, Single
-
Site Data (SPSD)



Multiple
-
Site Processing,
Single
-
Site Data (MPSD)



2 hrs


Lecture

Discussion


Oral
Interaction


Exercises




Presentat
ion


Modules


Books


Internet Topics


Homework


Recitation


Seatwork


Quiz



Attentiveness


Cooperation


Diligence


Initiative


Critical
Thinking



SPECIFIC OBJECTIVES


COURSE CONTENT

TIME
ALLOT.

TEACHING
STRATEGIES

MATERIALS USED

EVALUATIVE
MEASURES


VAL
UES


INPUT

o

Discuss

distributed
database transparency
features

o

Discuss

distributed
database design

o

Differentiate
Client/Server from DBMS




Multiple
-
Site Processing,
Multiple
-
Site Data (MPMD)

o

Distributed Database
Transparency Features

o

Distributed Transparenc
y



The Database Supports
Fragmentation Transparency



The Database Supports
Location Transparency



The Database Supports Local
Mapping Transparency

o

Distributed Database Design



Data Fragmentation



Data Replication



Data Allocation

o

Client/Server vs. DBMS


2 hrs


L
ecture

Discussion


Oral
Interaction


Exercises



Presentation


Modules


Books


Internet Topics


Homework


Recitation


Seatwork


Quiz



Attentiveness


Cooperation


Diligence


Initiative


Critical
Thinking


o

Discuss the need for
data analysis

o

Explain support

systems

o

Discuss and enumerate
the DSS architectural
style and rules that
define data warehouse

o

Discuss online analytical
processing

o

Discuss star schemas

VII. The Data Warehouse

o

The Need for Data Analysis

o

Decision Support Systems



Operational Architectural
Style



DSS Database Requirements

o

The Data Warehouse



DSS Architectural Styles



Twelve Rules that Define a
Data

warehouse

o

Online Analytical Processing



Multidimensional Data
Analysis Technique



Advanced Database
Support



Easy
-
to
-
Use End
-
User
Interface



Client/Serv
er Architecture

o

Star Schemas



2 hrs


Lecture

Discussion


Oral
Interaction


Exercises



Presentation


Modules


Books


Internet Topics


Homework


Recitation


Seatwork


Quiz



Attentiveness


Cooperation


Diligence


Initiative


Critical
Thinking




SPECIFIC
OBJECTIVES


COURSE CONTENT

TIME
ALLOT.

TEACHING
STRATEGIES

MATERIALS USED

EVALUATIVE
MEASURES


VALUES


INPUT

o

Implement data
warehouse

o

Discuss Extension fro LAP


o

Implementing a Data warehouse

o

Data Mining

o

SQL Extensions for OLAP








o

Discuss database
con
nectivity and Web
development

o

Discuss Extensible
Markup Language (XML)

VII. Database Connectivity and Web
Development

o

Database Connectivity



Native SQL Connectivity



ODBC, DAO and RDO



OLE
-
DB



ADO.NET

o

Internet Database



Web
-
to
-
Database
Middleware: Server
-
Side
E
xtensions



Web Server Interface



The Web Browser



Client
-
Side Extensions

o

Using a Web
-
to
-
Database
Production Tool: ColdFusion

o

Extensible Markup Language
(XML)



4

hrs


Lecture

Discussion


Oral
Interaction


Exercises



Presentation


Modules


Books


Internet Top
ics


Homework


Recitation


Seatwork


Quiz


Final
Examination



Attentiveness


Cooperation


Diligence


Initiative


Critical
Thinking




VII.
SCHEDULE OF EXAMINATION:


a. Preliminary Examination:




July 14, 15, 16 , 2010


b.
Midterm Examination:




August
25, 26, 27 , 2010


c. Final Examination:




October 6, 7, 8, 2010





VIII. COURSE REQUIREMENTS:

The students are expected to:

a.

Pass all requirements and computer programs

b.

Attend 80% of total contact hours for the course

c.

Take and pass quizzes, midterm & fin
al examination

d.

Take and pass laboratory examinations

e.

Submit required laboratory exercises and projects

f.

Participate in class discussions

IX. GRADING SYSTEM:

A.

Class Standing






Attendance




5

%



Attitude




5

%



Assignment




5

%




Recitation




5

%



Projects




10
%



Quizzes/Prelim Exam


20
%

B.

Midt
erm

Examination


25
%



C.

Final Examination



25
%



TOTAL GRADE = (RAW GRADE * 0.625) + 37.5


Submitted by:

Approved by:

Noted by:





LIEZL C. SALATAN

Instructor





DIONICIO D. GANTE

Program Chair,
BSIT





DR
. MARY JANE S. BITANGA

Dean, CCIT


1.00

-----

98
-

100

1.25

-----

95


97

1.50

-----

92
-

94

1.75

-----

89


91

2.00

-----

86


88

2.25

-----

83
-

85

2.50

-----

80


82

2.75

-----

77
-

79

3.00

-----

75


76 passed

4.00

-----

70


74 Conditional

5.00

-
----

Below 70 Failure