Republic of the Philippines ISABELA STATE UNIVERSITY Cauayan Campus Cauayan City, IsabelaCOLLEGE OF COMPUTING AND INFORMATION TECHNOLOGY Course Syllabus in IT 57 – DATABASE MANAGEMENT SYSTEM 2 2nd Semester School Year 2010 - 2011 CREDIT UNITS: COURSE PRE-REQUISITE: COURSE OBJECTIVES: 3 UNITS DATABASE MANAGEMENT SYSTEM At the end of this course. 3. IT 213 DATABASE MANAGEMENT SYSTEM 2 This course deals with the functions and features of Structured Query Language (SQL). design a database based from the different cycles in creating databases. III. connect databases into an information system. 4.I. These include and error correction and debugging. II. COURSE NUMBER: COURSE TITLE: COURSE DESCRIPTION: SQL. apply SQL Operators and SQL Functions in the created tables in SQL. V. create tables. 2. manage a database embedded in an information system COURSE SYLLABUS IN . and 5. VI. the student should be able to: 1. The student will gain proficiency in SQL coding through the use of extensive hands-on workshop exercises utilizing SQL in an interactive environment. forms and queries using SQL. IV. o Definition Commands The Database Model Creating the Database The Database Schema Data Types Creating Table Structure Library work Critical Thinking constraints and Data Oral Interaction Seatwork Objectivity Introd 2 hrs Lecture Discussion Modules Oral Recitation Independenc e Presentation Quiz Intelligence and the o CCIT Goals/Objectives Discussion Presentation Graded Recitation Openmindedness 30 mins. mission. Introduction to Structured Query Language (SQL) o uction to SQL database. data .IT 57 – DATABASE MANAGEMENT SYSTEM 2 TIME SPECIFIC OBJECTIVES COURSE CONTENT ALLOT. o Discuss Structured I. goals and objectives of the department university. SQL SQL indexes o Add table rows in the database o Save table changes o List table rows o Update table rows database schema. Orientation Printed Copies Quiz Information Update VALUES INPUT Query Language o Explain the concepts of data models o Create types o Create data structures. TEACHING STRATEGIES MATERIALS USED EVALUATI VE MEASURE S o To orient the students o University Mission/Vision of vision. Or. TEACHING STRATEGIES MATERIALS USED EVALUATI VE MEASURE S 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 o Use the rule of The Rule of Precedence Logical Operators: And. SQL Constraints SQL Indexes o Data Manipulation Commands Adding Tables Rows Saving Table Changes Listing Table Rows Updating Table Rows TIME SPECIFIC OBJECTIVES COURSE CONTENT ALLOT. and Not Special Operators Objectivity precedence logical operators and special operators o Change Column’s Data type characteristics o Add and drop column o Copy parts of tables and o Restrictions Independenc e Restoring Table Contents Deleting Table Rows Inserting Table Rows with a Select Subquery Select Queries Selecting Rows with Conditional Library work Internet Topic Intelligence 2 hrs Lecture Discussion Modules Quiz Attentivenes s Presentation Seatwork Initiative VALUES INPUT . o Order list o List unique values o Use functions o Group data o Create a new view of database o Join database tables aggregate Ordering a Listing Listing Unique Values Aggregate Functions Grouping Data Internet Topic Library Work Books Critical Thinking 2 hrs Lecture Discussion Modules Oral Recitation Independenc e Presentation Quiz Intelligence .o Add primary and 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 Advanced Select Queries TIME TEACHING STRATEGIES MATERIALS USED EVALUATI VE MEASURE S VALUES INPUT foreign key designation o Delete a table from the database o SPECIFIC OBJECTIVES COURSE CONTENT ALLOT. o l Tables: Creating a View o g Database Tables Virtua Joinin Joining Tables with an Alias Arithmetic Operators: Recursive Joins Other Joins o Use Set II. Advanced SQL o operators onal Set Operators o Apply SQL JOIN UNION operators in the database design UNION ALL o Use Subqueries and INTERSECT Correlated Queries o Develop a database MINUS using Multirow Syntax Alternatives Subquery Operators o ANY and ALL JOIN Operators Cross Join Natural Join Join USING Clause JOIN ON Clause relational Relati 6 hrs Lecture Discussion Modules Library Works Oral Recitation Preliminary Examinatio n SQL Critical Thinking Independenc e Objectivity Presentation Quiz Intelligence . o Outer Joins Subqueries and Correlated Queries WHERE Subqueries IN Subqueries HAVING Subqueries o Multirow Subquery Operators ANY and ALL FROM Subqueries Attribute List Subqueries TIME TEACHING STRATEGIES MATERIALS USED EVALUATI VE MEASURE S VALUES INPUT SPECIFIC OBJECTIVES COURSE CONTENT ALLOT. Database Design o The design Information System o Discuss information o The Systems Development Life Cycle system (SDLC) o Explain and enumerate the Systems Planning Development Life Cycle Analysis (SDLC) o Discuss the Database Detailed System Design 2 hrs Lecture Discussion Presentation Modules Quiz Oral Recitation Intelligence Analytical Thinking Library Works Lecture 2 hrs Discussion Presentation Modules Oral Recitation Quiz Attentivenes s Cooperation Diligence Critical Brainstorming Books Library work Internet Topics Homework . o Use SQL Functions o Correlated Subqueries SQL Functions Date and Time Functions Numeric Functions String Functions Conversion Functions o Explain database II. database concept Transaction Management Concurrency Control TIME SPECIFIC OBJECTIVES COURSE CONTENT ALLOT.Life Cycle (DBLC) o Explain the Database o from Design Strategies o Differentiate centralized decentralized design o o Implementation 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. TEACHING STRATEGIES MATERIALS USED EVALUATI VE MEASURE S o Discuss transaction is o Evaluate results. transaction management with SQL and the transaction log o Discuss Concurrency . transaction transaction what o What is Transaction? Evaluating Transaction Results Transaction Properties Transaction Management with SQL The Transaction Log 4 hrs Lecture Discussion Modules Oral Interaction Library Works Internet Topics Exercises Seatwork Patience Homework Quiz Cooperation Presentation Recitation Attentivenes s VALUES INPUT properties. Decentralized Design Advanced Database Concept: and Thinking o Explain Advanced IV. Transaction Recovery o Explain performance database V. Database Recovery Management . control methods. control concurrency with with locking time control Optimistic concurrency o stamping methods and concurrency with methods o 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 Wait/Die and Wound/Wait Analytical Thinking Schemes o Concurrency Control with Optimistic Methods a. Database Performance Tuning and 2 hrs Lecture Discussion Modules Oral Interaction Quiz Cooperation Presentation Recitation Attentivenes s tuning Query Optimization o Database Performance-Tuning and Query Optimization Concepts o Enumerate and explain Performance Tuning: Client and Database PerformanceServer Tuning Concepts DBMS Architecture .control. TIME SPECIFIC OBJECTIVES COURSE CONTENT ALLOT. Distributed Database Management 2 hrs Lecture Discussion Modules Oral Interaction Books Seatwork Recitation Cooperation Presentation Homework Attentivenes s Systems o The o Discuss the evolution of Evolution of Distributed Distributed DBMS Database Management Systems o DDBM o Enumerate and discuss S Advantages and the advantages and Disadvantages management systems . TEACHING STRATEGIES MATERIALS USED EVALUATI VE MEASURE S VALUES INPUT o Enumerate and discuss the different phases of query processing o Discuss o Use indexes to and affect o o query optimization hints optimizer choices o Use SQL performance tuning o Formulate Query o Explain DBMS Performance Tuning o o Database Statistics Query Processing SQL Parsing Phase SQL Execution Phase SQL Fetching Phase Indexes and Query Optimization Optimizer Choices Using Hints to Affect Optimizer Choices SQL Performance Tuning Index Selectivity 4 hrs Exercises Lecture Discussion Modules Books Internet Topics Homework Seatwork Quiz Midterm Examinatio n Cooperation Diligence Initiative Critical Thinking Oral Interaction Conditional Expressions o Query Formulation o DBMS Performance Tuning o Query Optimization Example o Explain database VI. SingleSite Data (SPSD) Multiple-Site Processing. Single-Site Data (MPSD) Diligence Exercises Internet Topics Quiz Initiative Critical Thinking .disadvantages DDBMS of o o o o Enumerate and discuss the characteristics of DDBMS o Discuss the levels of data and process distribution 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. TIME SPECIFIC OBJECTIVES COURSE CONTENT ALLOT. The Data Warehouse o The data analysis Need for Data Analysis o Explain support o Decisi on Support Systems systems o Discuss and enumerate Operational Architectural Style 2 hrs Lecture Discussion Modules Oral Recitation Cooperation Presentation Homework Attentivenes s . 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 Data Fragmentation Data Replication Data Allocation 2 hrs Lecture Discussion Presentation Modules Homework Recitation Attentivenes s Cooperation Oral Interaction Exercises Books Internet Topics Seatwork Diligence Quiz Initiative Critical Thinking o Client/Server vs. DBMS o Discuss the need for VII. TEACHING STRATEGIES MATERIALS USED EVALUATI VE MEASURE S VALUES INPUT o Discuss distributed database transparency features o o Discuss distributed database design Differentiate Client/Server DBMS from o o o Multiple-Site Processing. the style DSS and architectural rules that online o o define data warehouse o Discuss analytical processing o Discuss star schemas o 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 TIME Interaction Exercises Books Internet Topics Seatwork Diligence Quiz Initiative Critical Thinking TEACHING STRATEGIES MATERIALS USED EVALUATI VE MEASURE S VALUES INPUT SPECIFIC OBJECTIVES COURSE CONTENT ALLOT. Database Connectivity and Web 4 hrs Datab Lecture Discussion Modules Oral Interaction Books Seatwork Recitation Cooperation Presentation Homework Attentivenes s connectivity and Web Development o development ase Connectivity o Discuss Extensible Native SQL Connectivity Markup Language (XML) . o Implement warehouse o Discuss LAP Extension data fro o o o Imple menting a Data warehouse Data Mining SQL Extensions for OLAP o Discuss database VII. Final Examination: July 14. 16 . 7. 2010 October 6. SCHEDULE OF EXAMINATION: a. Midterm Examination: c. Take and pass quizzes. Preliminary Examination: b. 8. 15. midterm & final examination . 27 . Pass all requirements and computer programs b. DAO and RDO OLE-DB ADO. Attend 80% of total contact hours for the course c. 2010 August 25. COURSE REQUIREMENTS: The students are expected to: a.NET o Intern et Database Web-to-Database Middleware: Server-Side Extensions Web Server Interface The Web Browser Client-Side Extensions o Using a Web-to-Database Production Tool: ColdFusion o Extensible Markup Language (XML) Diligence Exercises Internet Topics Quiz Initiative Final Examinatio n Critical Thinking VII. 26. 2010 VIII.ODBC. Class Standing Attendance 5% Attitude 5% Assignment 5% Recitation 5% Projects 10% Quizzes/Prelim Exam 20% B.75 3. BSIT DR. Submit required laboratory exercises and projects f. GANTE Program Chair.00 2.94 89 – 91 86 – 88 83 . BITANGA Dean. CCIT .79 75 – 76 passed 70 – 74 Conditional Below 70 Failure Noted by: LIEZL C.50 1.00 4.d. MARY JANE S.00 5. GRADING SYSTEM: A.5 Submitted by: Approved by: 1.85 80 – 82 77 . Final Examination 25% TOTAL GRADE = (RAW GRADE * 0.75 2.00 1. Participate in class discussions IX.25 1. SALATAN Instructor DIONICIO D.100 95 – 97 92 .625) + 37.25 2.50 2.00 --------------------------------------------- 98 . Midterm Examination 25% C. Take and pass laboratory examinations e.