INFO607: Applied Database Technology Fall 2012 Online Course Website: http://learn.dcollege.net
Instructor: Dr. Il-Yeol Song Office: Rush 332, 215-895-2489 (Office), 895-2494 (Fax) Office Hours: W: 5:00-6:00 or by appointment Email:
[email protected]; (Best way to reach me; Put INFO607 in the email subject) Home page: http://www.ischool.drexel.edu/faculty/song/ Prerequisite: Info606 (Database Management II) and Info620 (Information Systems Analysis & Design) or permission of the instructor Text Books: • (Mandatory) Kimball, R., The Data Warehouse Toolkit : The Complete Guide to Dimensional Modeling, 2nd. Ed., Wiley, 2002. (Note that this book is still THE best data warehousing book in the market) • (Optional) Urman, S., Oracle 10g PL/SQL Programming, McGraw Hill, 2004. (Recommended; but you may use other books or Web resources.) Source code of the book: http://www.mhprofessional.com/getpage.php?c=oraclepress_downloads.php&cat=4222
• Other collection of articles. Will be posted in the right weeks. Website: Learning materials will be available online every Monday morning by 8AM: To-do List: A list of tasks for each week (under “Discussion” folder). Lecture Notes: Lecture slides are the most important materials (under “Course Material” folder). Exercises: Problems for exercises only. Not to be graded. Solution will be provided afterwards (under “Discussion” folder). Homework: Several homework problems to be graded will be assigned (Submission link will be provided under “Assignments” folder). Mandatory discussion item: I expect you participate in the discussion within a week, from Monday to Sunday midnight of your local time. Not graded. Just count participation. Optional discussion item: You may participate in the discussion any time, but you don’t have to.
INFO607, Fall 2012, Il-Yeol Song, IST, Drexel University References: Data Warehousing • Kimball, R., Ross, M., Thornthwait, W., etc., The Data Warehouse Lifecycle Toolkit: Expert Methods for Designing, Developing, and Deploying Data Warehouses, Wiley, 2nd. Ed., 2008. • Kimball, R. and Caserta, J., The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleaning Data, Wiley, 2004. • Mundy, J. and Thornthwaite, W. with Kimball, R., The Microsoft Data Warehouse Toolkit: with SQL Server 2008 R2 and the Microsoft Business Intelligence Toolset, Wiley, 2011. • Golfarelli, M. and Rizzi, S., Data Warehouse Design: Modern Principles and Methodologies, McGraw-Hill, 2009. • Adamson, C., Star Schema: The Complete Reference, McGraw-Hill, 2010. • Imhoff, C., Galemmo, N., and Geiger J., Mastering Data Warehouse Design: Relational and Dimensional Techniques. Wiley, 2003. • Inmon, W.H., et al., DW 2.0: The Architecture for the Next Generation of Data warehousing,, Morgan Kaufman, 2008. • Inmon, W.H., et al., Tapping into Unstructured Data: Integrating Unstructured Data and Textual Analysis into Business Intelligence, Prentice Hall, 2007. • Inmon, W.H., et al., Business Metadata: Capturing Enterprise Knowledge, Morgan Kauffman, 2008. • Adamson, C. and Venerable, M., Data Warehouse Design Solutions, John Wiley, 1998. • Kimball, R. and Merz, R., The Data Webhouse Toolkit: Building the Web-Enabled Data Warehouse. Wiley, 2000. • Sweiger, M., Madson, M.R., Langston, J., and Lombard, H., ClickStream Data Warehousing. Wiley, 2002. Data Mining • Han, J. and Kamber, M., Data Mining: Concepts and Techniques, 3rd ed., Morgan Kauffman, 2011. • Witten, I. and Frank, E., Data Mining: Practical Machine Learning Tools and Techniques 3rd ed.., Morgan Kauffman, 2011. • Lindoff, G. and Berry, M, Data Mining Techniques for Marketing, Sales, and Customer Relationship Management, 3rd ed.., Wiley, 2011. ORACLE SQL: Loney, K. and Koch, Oracle Database 11g: The Complete Reference. McGraw-Hill Osborne Media, 2008. • Bryla, B. and Loney, K. and Theiriault, M., Oracle11g DBA handbook, Oracle Press, 2007. Good PL/SQL Books: • Feuerstein, S. and Pribyl B, Oracle PL/SQL Programming Through Oracle Database 11g Release 2, O'Reilly Media, 2009. • Feuerstein, S., Oracle PL/SQL Best Practice, O'Reilly & Associates, Inc., 2007. • McDonald, C., Katz, C., Beck, C., Kallman, J.R. and Knox, D.C. , Mastering Oracle PL/SQL: Practical Solutions, Apress, 2004..
2
INFO607, Fall 2012, Il-Yeol Song, IST, Drexel University
Course Objectives: This course covers principles and techniques related to data warehousing & OLAP technologies as well as Oracle PL/SQL programming. We will cover PL/SQL programming including stored procedures & functions, packages, and triggers. Topics for data warehousing include dimensional modeling and design, OLAP, ROLAP/MOLAP, aggregation, ETL, physical data warehouse design, optimization techniques such as partitioning, bit map indexing, multi-table join indexing, star schema query optimization, and materialized views. Assignments, Project, and Exams: There will be four homework assignments that include reading assignment, Oracle PL/SQL programming assignments, and data warehouse schema design and implementation using PL/SQL. There will be no mid-term exam. A term project is required. An extra credit may be given for an extraordinary project. The final exam will be take-home and comprehensive. Oracle and Visio:
For SQL assignments, you may use the IST Oracle system or you may use your own copy Request an IST Oracle ID at URL: https://linux.ischool.drexel.edu/signup/index.pl When prompted for your Drexel University Email ID and PW, you should use the short version of their Email ID (such as abc22) and the PW that goes with that account. If you require any assistance, contact the iCommon by emailing
[email protected].
Downloading your own personal edition of Oracle 10g Express: http://www.oracle.com/technology/software/products/database/xe/htdocs/102xewinsoft.html http://www.oracle.com/technology/software/products/database/index.html
Visio: Get a recent copy of MS Visio; You will automatically receive an email from “DreamSpark MSDNAA” account that you can download a recent copy of Visio software. The email will be sent you on Monday afternoon of the first week. You will use Visio to develop Entity-Relationship Diagrams (ERDs) and dimensional schemas for exercises, assignments, and term project. (If you already have a Visio, the old version is sufficient for this class purpose.) From the website, you may also download other MS software such as SQL Server. If you require any assistance for downloading Visio, contact the iCommon by emailing
[email protected] by sending your name, email address, and course (INFO 605) information. If this is their first time getting an account, The email will come from “Drexel University The iSchool - DreamSpark Premium
[email protected]” with the subject of “An Account has been created for you”. Alternately if you already had an account, they will get the following message from “Drexel University - The iSchool - DreamSpark Premium
[email protected]” with the subject of “Your account has been reactivated”.
3
INFO607, Fall 2012, Il-Yeol Song, IST, Drexel University
Grading and Grade: Assignments Project
25% 25% (Proposal on time: 5%; Project contents and correctness: 80% Documentation, organization, formatting, and necessary components: 15%) Final Exam 40% Class participation 10% NOTE that your proposal submitted on time will count 5% of your project grading. Mandatory Discussion The Class participation to mandatory discussion items (but neither optional discussion nor exercises) will be graded as follows: 2 missing : full credit 3 missing: -1 4 missing: -2 5 missing: -3 6 missing: -5 7 missing: no credit If you miss more than 7, you will get zero percent for the Class Participation part. Certain assignments are graded, while others are only for practice. I will make it clear which one will be graded.
Letter Grade: A+ A
97-100 93-96
A-
90-92
Walks on water. Professional level work of the highest caliber. Outstanding achievement. Student performance demonstrates full command of the course materials and evinces a high level of originality and/or creativity that far surpasses project and course expectations. Excellent achievement. Student performance demonstrates thorough knowledge of the course materials and exceeds project and course expectations by completing all requirements in a superior manner.
A key difference between a B and an A is the degree to which the work turned in is original, creative, and shows mastery of the concepts. Doing what is expected and no more, or simply following my suggestions for improvement, however well done, earns, at best, a B+. B+
87-89
B
84-86
B-
80-83
Very good work. Student performance demonstrates above-average comprehension of the course materials and exceeds project and course expectations on all tasks as defined in the course syllabus. Student performance meets designated project and course expectations and demonstrates understanding of the course materials at an acceptable level. Marginal work. Student performance demonstrates incomplete understanding of course materials.
In graduate school, a C does not mean satisfactory, even though it is considered a “passing” grade. It’s an indication that there is something definitely wrong that needs to be addressed
4
INFO607, Fall 2012, Il-Yeol Song, IST, Drexel University C+ C CD+ D DF
77-79 74-76 70-73 68-69 64-67 60-63 0.0
Unsatisfactory work. Student performance demonstrates incomplete and inadequate understanding of course materials. Unacceptable work at varying levels of unacceptability.
Failing—or dropped the course and forgot to tell anyone
Submission Policy & Plagiarism Policy:
All the graded assignments are usually due within one week, unless otherwise stated. I expect 4 assignments throughout the term. Assignments must be turned in on the due date before the midnight. No late assignments will be accepted for grading once the assignment material is discussed and answers are posted. Late assignments will be allowed on the condition that (1) it is pre-arranged before the due date (2) there is unavoidable reason such as business trip, important family matters, sickness, or accidents. All late assignments, however, will receive 10% off regardless of the reasons for the lateness. No make-up assignments will be given. Individual completion of assignments and tests is mandatory. Note that copying sentences directly from the textbook, lecture notes, or other’s work is plagiarism and will not be accepted. Write with your own words. You can site one or a few sentences with clear citation. Do not give/show a soft/hard copy of assignment answers or send via email your assignment answer files to your classmates. Once caught, both parties will suffer. You can discuss lecture materials, but you cannot discuss assignments with your classmates. You cannot co-develop a part or the whole solution of any assignments. Both parties will suffer for doing this. Ask me in advance if you have any questions on what is allowed and what is not allowed. For this course, remember that all assignments and final exams are individual efforts and only the project is a joint effort venture. We’re All Human I am well aware that you have a life and responsibilities outside of this course. If you encounter serious personal difficulties (e.g., death in the family) that preclude you from completing an assignment or attending an examination, please contact me in advance of the due date to discuss alternatives. Plagiarism Plagiarism is the representation of another’s words, ideas or work as your own. The standards for academic honesty are described in Student Handbook and on Drexel website. All students are responsible for reading and understanding these rules. All submissions should be original work, any exchange, reuse or direct submission of other’s work (documents, code etc) as your own or as your team’s is Plagiarism. For this course, students found guilty of plagiarism can expect one or more or all of (a) Incident being reported to the University Judicial Office, where a permanent record is maintained, and (b) Fail the course. 5
INFO607, Fall 2012, Il-Yeol Song, IST, Drexel University For group project’s, all the team members are equally responsible to observe this policy. If a team member does not faithfully collaborate, I recommend you talk to me about the nature of collaboration. Different members of a group may receive a different grade, if such an instance is noticed or suspected. Code of conduct http://www.drexel.edu/provost/policy_reminders_fall_2006.asp#conduct Academic Honesty Policy http://www.drexel.edu/provost/policies/academic_dishonesty.asp
Students with Disabilities If you have any type of disability which will interfere with your participation and comprehension in this class, or with completion of assignments, please contact the Office of Disability Services as soon as possible. Student with disabilities requesting accommodations and services at Drexel University need to present a current accommodation verification letter (“AVL”) to faculty before accommodations can be made. AVL's are issued by the Office of Disability Services (“ODS”). For additional information, contact the ODS at http://www.drexel.edu/oed/disability, 3201 Arch St., Ste. 210, Philadelphia, PA 19104, Phone 215-895-1401, or TTY 215-895-2299. Submission of assignments, proposals, term projects, peer evaluation form, and the final exam (1) All the submissions (except questions) must be submitted to digital dropbox. (2) By the new college rule imposed by the dean to raise awareness about academic dishonesty and reduce cases of academic dishonesty in the College. All the submissions must show the following statements at the beginning of page 1: --------------------------------------------------------------------------------------------------I certify that: This assignment /paper/project/exam is entirely my own work. I have not quoted the words of any other person from a printed source or a website without indicating what has been quoted and providing an appropriate citation. I have not submitted this submission to satisfy the requirements of any other course. Signature__________________________( writing your name here serves as your signature for
this submission). Date
____________________
---------------------------------------------------------------------------------------------------
6
INFO607, Fall 2012, Il-Yeol Song, IST, Drexel University Tentative Course Schedule and Reading assignments: 1
DW technology, Introduction to data warehousing & OLAP
2
OLAP and DW design (Kimball Ch1.) PL/SQL: Stored Procedures & Functions (Part I; Urman Ch 1-3, 8.1)
3
Dimensional Modeling I (Kimball Ch.2) PL/SQL: Cursors & Exceptions (Part II, Urman : Ch 4, 5, 7)
4
Dimensional Modeling: II (Kimball Ch. 3) PL/SQL: Triggers (Urman Ch.10)
5.
Time, Additivity (Kimball Ch. 4-5) PL/SQL: Triggers and Packages (Urman Ch 8.2)
6
Kimball (Ch 6-7, 12-14)
7
Kimball (Ch 15)
8
ETL
9
Physical design I (Aggregates and Materialized Views, Indexing)
10
Physical design II (Partition, Star schema optimization, Kimball Ch. 16-17)
11
Other advanced topics, Summary of the course
Important Dates: Project Proposal Due: Week3 (Wednesday, October 10, 11:59PM) Final Exam: (take home) Given on Week 11 (Given on Thursday, December 6) Final Due: Wednesday, December 12, 11:59PM Project and Peer Evaluation Form Due: Friday, December 14, 11:59PM.
DB Magazines and Web Sites: Title Database Trends and Applications Information Management Oracle Magazine Application Development Trends
URL http://www.dbta.com
Free Subscription? Yes
http://www.information-management.com/ http://www.oramag.com http://www.adtmag.com
Yes Yes Yes
7
INFO607, Fall 2012, Il-Yeol Song, IST, Drexel University See http://www.ischool.drexel.edu/faculty/song/datalin.htm for links to Oracle documentation/SQL/PL SQL/Data Warehouses/OLAP/Data mining/CRM/Research Articles. Also see my Research Help page in my home page.
8