Transcript
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 - 2011
IT 213
I.
COURSE NUMBER:
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 correction and debugging. The student will gain proficiency 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
TEACHING
MATERIALS
EVALUATI
ALLOT.
STRATEGIES
USED
VE
VALUES
MEASURE S
o
To orient the students
o
University Mission/Vision
of vision, mission, goals
o
CCIT Goals/Objectives
30 mins.
and
Printed Copies
Quiz
Discussion
Presentation
the
Graded Recitation
university.
o
Discuss
Structured I. Introduction to Structured Query Language (SQL)
Explain the concepts of Create
database,
types constraints
and
SQL indexes o
Data
o
Definition Commands
Add table rows in the database
The Database Model
Creating the Database
The Database Schema
o
Save table changes
o
List table rows
Data Types
o
Update table rows
Creating Table Structure
Presentation
Quiz
Intelligence
Modules
Oral
Independenc
Recitation
e
Seatwork
Objectivity
Discussion Oral Interaction
Create data structures, SQL
Lecture
uction to SQL
database schema, data
o
2 hrs Introd
o
data models o
Openmindedness
Query Language o
Information Update
and objectives of the department
Orientation
Library work Critical Thinking
INPUT
SQL Constraints
SQL Indexes o
Data Manipulation Commands
Adding Tables Rows
Saving Table Changes
Listing Table Rows
Updating Table Rows
SPECIFIC OBJECTIVES
COURSE CONTENT
TIME
TEACHING
MATERIALS
EVALUATI
ALLOT.
STRATEGIES
USED
VE
VALUES
MEASURE S o
Restore table contents
Restoring Table Contents
o
Delete table rows
Deleting Table Rows
o
Insert Table Rows with
Inserting Table Rows with
a Select Subquery o
Explain
the
rule
Select Queries
o
Use logical
of
Rows with Conditional
Presentation
Seatwork
Initiative
Modules
Quiz
Attentivenes
Library work
s Internet Topic Intelligence
Restrictions operators
and special operators o
Selecting
precedence o
Lecture Discussion
a Select Subquery
Use Select queries with conditional restrictions
o
2 hrs
Independenc
The Rule of Precedence
Change Column’s Data
type
Logical Operators: And, Or, and
and
characteristics
Not
o
Add and drop column
o
Copy parts of tables
Special Operators
e Objectivity
INPUT
o
Add
primary
and
o
foreign key designation o
Advanced Data Definition Commands
Delete a table from the
Changing Column’s Data
database
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 Advanced Select
o
Queries
SPECIFIC OBJECTIVES
COURSE CONTENT
TIME
TEACHING
MATERIALS
EVALUATI
ALLOT.
STRATEGIES
USED
VE
VALUES
MEASURE S o
Order list
o
List unique values
Ordering a Listing
o
Use
aggregate
functions
Listing Unique Values
o
Group data
o
Create a new view of
Aggregate Functions
database
o
Join database tables
Grouping Data
2 hrs
Lecture
Presentation
Quiz
Intelligence
Modules
Oral
Independenc
Recitation
e
Discussion Library Work Books Critical Internet Topic
Thinking
INPUT
Virtua
o
l Tables: Creating a View Joinin
o
g Database Tables
Joining Tables with an Alias
Arithmetic Operators:
Recursive Joins
Other Joins o
Use
relational
Set II. Advanced SQL
o
Apply
SQL
operators
onal Set Operators
JOIN
in
Use
Subqueries
Develop
a
using Subquery
the
Library Works
UNION ALL
and
Presentation
Quiz
Intelligence
Modules
Oral
Independenc
Recitation
e
Preliminary
Objectivity
INTERSECT
database
Examinatio
MINUS
Multirow
n
Syntax Alternatives
Operators
SQL
o
ANY and ALL
Lecture
UNION
Correlated Queries o
6 hrs
Discussion
database design o
Relati
o
operators
JOIN Operators
Cross Join
Natural Join
Join USING Clause
JOIN ON Clause
Critical Thinking
o
o
Outer Joins Subqueries and Correlated Queries WHERE Subqueries IN Subqueries HAVING Subqueries Multirow Subquery Operators ANY and ALL FROM Subqueries Attribute List Subqueries
SPECIFIC OBJECTIVES
COURSE CONTENT
TIME
TEACHING
MATERIALS
ALLOT.
STRATEGIES
USED
EVALUATI VE
VALUES
MEASURE S o
Use SQL Functions
o
Correlated Subqueries SQL Functions
2 hrs
Lecture
Presentation
Quiz
Intelligence
Modules
Oral
Analytical
Discussion
Date and Time Functions
Numeric Functions
Library Works
Recitation
Thinking
String Functions
Conversion Functions o
Explain
database
o
Discuss
information
system o
Explain and enumerate the
Systems
Development Life Cycle (SDLC) o
II. Database Design The
o
design
Discuss the Database
Information System o
The Systems Development Life Cycle (SDLC)
Lecture 2 hrs
Discussion
Analysis
Detailed System Design
Oral
Attentivenes
Recitation
s
Quiz
Cooperation
Homework
Diligence
Modules Brainstorming Books
Planning
Presentation
Library work Internet Topics
Critical
INPUT
Implementation
Life Cycle (DBLC) o
Explain the Database Design Strategies
o
o
Differentiate centralized
from
decentralized design
o
o
o
Explain
Advanced IV.
database concept
Thinking
Maintenance Database Life Cycle (DBLC) The Database Initial Study Database Design Implementation and Loading Testing and Evaluation Operation Maintenance and Evolution Database Design Strategies Centralized vs. Decentralized Design Advanced
Transaction
Database
Concept:
Management
and
Concurrency Control
SPECIFIC OBJECTIVES
COURSE CONTENT
TIME
TEACHING
MATERIALS
ALLOT.
STRATEGIES
USED
EVALUATI VE
VALUES
MEASURE S o
Discuss
what
transaction is o
Evaluate
transaction
results,
transaction
properties, transaction management with SQL
o
What
o
is Transaction?
Evaluating Transaction Results
Lecture
Transaction Properties
Transaction Management with SQL
Discuss
The Transaction Log
Presentation
Recitation
Modules
Quiz
Discussion
and the transaction log Concurrency
4 hrs
s
Oral Interaction
Attentivenes
Cooperation Internet Topics
Seatwork Patience
Library Works
Exercises
Homework
INPUT
control, control methods, control
concurrency with
o
locking
concurrency with
time
stamping methods and concurrency with
o
control Optimistic
methods
o
Concurrency Control Lost Updates Uncommitted Data Inconsistent Retrievals The Scheduler Concurrency Control with Locking Methods Lock Granularity Lock Types Two-Phase Locking to Ensure Serializability Deadlock Concu rrency Control with Time Stamping Methods
Analytical Thinking
Wait/Die
and
Wound/Wait
Schemes o
Concurrency Control with Optimistic Methods a. Database Recovery Management - Transaction Recovery o
Explain
database
V. Database Performance Tuning and
performance
o
tuning Query Optimization Database Performance-Tuning o and Query Optimization Concepts Enumerate and explain Database Performance Tuning Concepts
Performance Tuning: Client and Server
DBMS Architecture
2 hrs
Lecture
Presentation
Recitation
Modules
Quiz
Discussion Oral Interaction
Attentivenes s Cooperation
SPECIFIC OBJECTIVES
COURSE CONTENT
TIME
TEACHING
MATERIALS
EVALUATI
ALLOT.
STRATEGIES
USED
VE
VALUES
MEASURE S o
Enumerate and discuss
the different phases of o
query processing o
Discuss
indexes
and
query optimization o
Use
hints
to
affect
optimizer choices o
o
o
Use SQL performance Formulate Query
o
Explain
Using
tuning o
Database Statistics Query Processing SQL Parsing Phase SQL Execution Phase SQL Fetching Phase Indexes and Query Optimization Optimizer Choices
o
DBMS
4 hrs
Exercises
Modules
Homework
Cooperation
Lecture
Books
Seatwork
Diligence
Internet Topics
Quiz
Initiative
Midterm
Critical
Discussion Oral Interaction
Hints
Examinatio
to Affect Optimizer Choices SQL Performance Tuning
Thinking
n
Performance Tuning
Index Selectivity Conditional Expressions o
Query Formulation o
DBMS Performance Tuning o
Query Optimization Example
o
Explain
database
management systems o
Discuss the evolution of
VI. Distributed Database Management Systems o
Distributed DBMS o
Enumerate and discuss the
advantages
and
o
2 hrs The
Evolution of Distributed Database Management Systems DDBM S Advantages and Disadvantages
Lecture
Presentation
Homework
Discussion
s Modules
Recitation
Books
Seatwork
Oral Interaction
Attentivenes
Cooperation
INPUT
disadvantages
of
o
DDBMS o
Enumerate and discuss the characteristics
of
DDBMS o
o
o
Discuss the levels of data
and
distribution
process
o
Distri buted Processing and Distributed Databases Chara cteristics of Distributed Database Management Systems DDBM S Components Level s of Data and Process Distribution
Single-Site Processing, SingleSite Data (SPSD)
Multiple-Site Processing, Single-Site Data (MPSD)
Diligence Exercises
Internet Topics
Quiz Initiative Critical Thinking
SPECIFIC OBJECTIVES
COURSE CONTENT
TIME
TEACHING
MATERIALS
EVALUATI
ALLOT.
STRATEGIES
USED
VE
VALUES
MEASURE S o
Discuss distributed
database transparency features o
o
Discuss distributed o
database design o
Differentiate Client/Server
from
DBMS
o
Multiple-Site Processing, Multiple-Site Data (MPMD) Distributed Database Transparency Features Distributed Transparency The Database Supports Fragmentation Transparency The Database Supports Location Transparency The Database Supports Local Mapping Transparency Distri buted Database Design
2 hrs
Lecture
Presentation
Homework
Discussion
Attentivenes s
Modules
Recitation
Oral
Cooperation
Interaction
Books
Seatwork
Exercises
Internet Topics
Quiz
Diligence Initiative Critical Thinking
Data Fragmentation
Data Replication
Data Allocation o
Client/Server vs. DBMS o
Discuss the need for
o
Explain
support
systems o
VII. The Data Warehouse The
o
data analysis
Discuss and enumerate
Need for Data Analysis Decisi
o
2 hrs
Lecture
Operational Architectural Style
Homework
Modules
Recitation
Discussion
on Support Systems
Presentation
Oral
Attentivenes s Cooperation
INPUT
the DSS architectural style
and
rules
that
o
define data warehouse o
Discuss
online
analytical processing o
o
Discuss star schemas
o
Interaction
DSS Database Requirements The Data Warehouse DSS Architectural Styles Twelve Rules that Define a Data warehouse Online Analytical Processing Multidimensi onal Data Analysis Technique Advanced Database Support Easy-to-Use End-User Interface Client/Serve r Architecture Star Schemas
SPECIFIC OBJECTIVES
COURSE CONTENT
Books
Seatwork Diligence
Exercises
Internet Topics
Quiz Initiative Critical Thinking
TIME
TEACHING
MATERIALS
ALLOT.
STRATEGIES
USED
EVALUATI VE
VALUES
MEASURE S o
Implement
data
o
warehouse o
Discuss Extension fro LAP
o
Discuss
o
o
database
Imple menting a Data warehouse Data Mining SQL Extensions for OLAP
VII. Database Connectivity and Web
connectivity and Web Development development o
Discuss
Extensible
Markup
Language
(XML)
4 hrs Datab
o
ase Connectivity
Lecture
Homework
Modules
Recitation
Books
Seatwork
Discussion
Native SQL Connectivity
Presentation
s
Oral Interaction
Attentivenes
Cooperation
INPUT
ODBC, DAO and RDO
Diligence
Exercises
OLE-DB
Internet Topics
Quiz
Initiative
ADO.NET Intern
o
et Database
Examinatio
n
Web-to-Database Middleware: Server-Side Extensions
Web Server Interface
The Web Browser o
o
Final
Client-Side Extensions Using a Web-to-Database Production Tool: ColdFusion Extensible Markup Language (XML)
VII. SCHEDULE OF EXAMINATION: a. Preliminary Examination: b. Midterm Examination: c. Final Examination:
July 14, 15, 16 , 2010 August 25, 26, 27 , 2010 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 & final examination
Critical Thinking
d. Take and pass laboratory examinations e. Submit required laboratory exercises and projects f. Participate in class discussions
IX. GRADING SYSTEM: A.
Class Standing
1.00 -----
98 - 100
1.25 -----
95 – 97
1.50 -----
92 - 94
Attendance
5%
1.75 -----
89 – 91
Attitude
5%
2.00 -----
86 – 88
Assignment
5%
2.25 -----
83 - 85
Recitation
5%
2.50 -----
80 – 82
Projects
10%
2.75 -----
77 - 79
Quizzes/Prelim Exam
20%
3.00 -----
75 – 76 pas sed
B.
Midterm Examination
25%
4.00 -----
70 – 74 Conditional
C.
Final Examination
25%
5.00 -----
Below 70 Failure
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