Transcript
IONA School of Arts & Science
Advanced Topics in Database CS 720 - EA Wednesday 6:30PM - 9:30PM Murphy Center 209 Professor: Dr. S. Petrovic Office: Murphy Center, 1st Floor, 113-I Office hours: Monday 2pm–3pm Wednesday 11am –12 noon Wednesday 5:30pm–6:30pm By appointment
Computer Science Spring 2013 Phone: 914-633-2561 Email:
[email protected]
Course Description: A study of problems and techniques in the design and implementation of database systems. Topics include logical and physical design considerations, query optimization, post-relational systems, security, integrity, concurrency control, backup and recovery techniques, reorganization problems, data warehousing, data mining, issues in the design of distributed systems, and ethical and social issues in database systems. Course prerequisite: CS 620 Required Texts: Catherine Ricardo, Databases Illuminated, Jones and Bartlett Publishers (2004), ISBN-13: 9780763733148 Elmasri & Navathe, Fundamentals of Database Systems, 5/E, Addison-Wesley (2007), ISBN-13: 9780321369574 *Please check with me if you used different book in your introductory DB course Software: Oracle Database Express Edition 11g: http://www.oracle.com/technetwork/products/express-edition/downloads/index.html Oracle SQL Developer 3.2.2: http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html Oracle JDeveloper: http://www.oracle.com/technetwork/developer-tools/jdev/overview/index.html Learning Goals/Objectives: 1
2
Course Objectives Design and create a database and application program that uses it
CS Program Goals and Outcomes SLO1-work independently to analyze the requirements of problems of appropriate complexity and then design and implement solutions. SLO2-communicate clearly and effectively on technical issues in both oral and written form. SLO3-work collaboratively on problems of appropriate complexity
Understand physical data organization and indexing models
SLO1-work independently to analyze the requirements of problems of appropriate complexity and then design and implement solutions.
3 4 5 6 7
Understand query optimization techniques Understand transaction processing, concurrency control and recovery Understand concepts of database security Understand concepts of data mining Evaluate emerging database technologies
SLO1-work independently to analyze the requirements of problems of appropriate complexity and then design and implement solutions. SLO1-work independently to analyze the requirements of problems of appropriate complexity and then design and implement solutions. SLO1-work independently to analyze the requirements of problems of appropriate complexity and then design and implement solutions. SLO1-work independently to analyze the requirements of problems of appropriate complexity and then design and implement solutions. SLO2-communicate clearly and effectively on technical issues in both oral and written form. SLO4-adapt readily to new technologies and/or disciplines
Assessment Criteria/Tools: Assessment for this course is accomplished through direct and indirect measures. The direct measures are as follows. Assessment Tool Percentage of Final Grade Course Objectives Measured Project 25% 1 Midterm Exam 15% 2, 3, 4 Assignments 25% 2, 3, 4, 5,6 Presentation 5% 7 Final Exam 30% 2, 3, 4, 5, 6 Project: Students will design and implement a database application that uses backend database to store data. Project requires use of advanced database tools and programming mechanisms. Students will present their project orally. Projects will be developed in teams of 2-3 students. A peer evaluation will be performed at the end of the project, and it will impact a student’s final project grade. Midterm Exam: Scheduled tests assess students’ comprehension of lecture material, computer applications and class discussions. Assignments: In several homework assignments, students will apply the concepts and techniques presented in the lectures. Students will also write an essay on a topic related to social and ethical issues associated with databases. Presentation: Students will present to classmates an emerging database technology of their choice. Student can get a topic approval and schedule a presentation at any time during the semester.
Final Exam: The final exam is a cumulative assessment of the entire course experience. It is designed to provide a holistic view of the course for the students. The indirect measures consist of student and faculty evaluations done at the end of each semester. Students complete an online evaluation form that asks them to evaluate the extent to which the course has met each of its objectives. Each faculty member submits a spreadsheet indicating the extent to which each of the assessment tools met the desired course objectives. The results of these assessments are analyzed to identify problems. The department discusses the problems and suggests solutions to address the problems.
Course Outline: Date 1/16
1/23 1/30
2/6
2/13
2/20
2/27
3/6
3/13 3/20
Lecture Topic Review of Relational Database Concepts Data Modeling Using the ER and EER Models Mapping an ER and EER to a Relational Model Normalization Oracle DBMS Oracle SQL Developer 3.0 PL/SQL Programming PL/SQL Control Structures Collections and Records Procedures and Functions PL/SQL Programming Triggers Cursors Records JDBC Oracle JDeveloper Using Java to Connect to Oracle Database Creating JDBC Applications Executing SQL Queries Physical Data Organization Disk Storage, Basic File Structures, Hashing Indexing Structures for Files Types of Single-Level Ordered Indexes Multilevel Indexes Dynamic Multilevel Indexes Using B-Trees and B+Trees Indexes on Multiple Keys Query Optimization Review of Relational Algebra Interpretation of Queries Algebraic Techniques Processing Techniques Cost Estimation Applications Concurrency Control Need for a Concurrency Control Mechanism Locking Techniques Timestamping Techniques Validation (Optimistic) Concurrency Control Techniques Granularity of Data Items and Multiple Granularity Locking Recovery Transaction Control and Recovery Types of Failures Recovery Logs Spring Break
Reference* Ricardo Ch. 1-5
XML: Extensible Markup Language XML Hierarchical Data Model XML Documents, DTD, and XML Schema XML Documents and Databases XPath XQuery
Ricardo Ch. 13
Ricardo Ch. 6 Oracle Application Developer's Guide Oracle Application Developer's Guides PL/SQL User's Guide and Reference
Oracle PL/SQL and Application Developer's Guides
Oracle Application Developer's Guide
Ricardo Appendix A
Elmasri & Navathe Ch 13, 14
Ricardo Ch. 11
Elmasri & Navathe Ch 15
Ricardo Ch. 9
3/27
Ricardo Ch. 10
4/10
Class exam Security Security and Privacy Identification and Authentication Authorization Security Subsystems Data Encryption Distributed and Client/Server Databases Types of Distributed Systems Query Processing Updating Concurrency Control System Directory Management Project presentations
4/17
Data Warehouses and Data Mining
Ricardo Ch. 15
4/24
Data Mining Techniques Oracle Cube and rollup Problems and Open Issues Social and Ethical issues Students presentations: Emerging Database Technologies and Applications Final exam
Ricardo Ch.14
4/3
5/1
Elmasri & Navathe Ch 23
Ricardo Ch. 12
Elmasri & Navathe Ch 24, 29
* or relevant chapters in other books
College Policy for all courses and students: (full explanations of policy may be found in the College Catalog) Plagiarism: Is the unauthorized use or close imitation of the language and thoughts of another author/person and the representation of them as one's own original work. Iona College policy stipulates that students may be failed for the assignment or course, with no option for resubmission or re-grading of said assignment. A second instance of plagiarism may result in dismissal from the College. Attendance: All students are required to attend all classes. Iona has an attendance policy for which all students are accountable. While class absence may be explained it is never excused. Professors may weigh class absence in the class grade as they see fit. Failure to attend class may result in a failure of the class for attendance(FA), when the student has missed 20% or more of the total class meetings. The FA grade weighs as an F would in the final official transcript. Course and Teacher Evaluation (CTE): Iona College now uses an on-line Course and Teacher Evaluation (CTE) system. All students are required to complete the CTE in every course in which they are enrolled. This system is administered by an outside company and all of the data is collected confidentially. No student name or information will be linked to any of your responses. The information collected will be compiled by the agency and distributed back to the Iona administration and faculty. Select information about courses and professors will also be made available to students. Your feedback in this process is an essential part of improving our course offerings and instructional effectiveness. We want and value your point of view.* NOTE* You will receive several emails at your Iona email account about how and when the CTE will be administered with instructions how to proceed.
