Southern Polytechnic State University



ALG #119 Final Report Supporting Material Prepared by Dr. Lei LiDr. Rebecca H. Rutherfoord Dr. Svetlana PeltsvergerDr. Jack ZhengDr. Zhigang Li Ms. Nancy N. Colyar SummaryThis document contains detailed information about the four courses we developed and implemented. For each course, we included catalog description, learning objectives, developed learning modules, analysis of effectiveness of the developed material, and summary of the survey results. IT 4153 Advanced Database – Supporting Material. Instructor: Svetlana Peltsverger Email: speltsve@kennesaw.eduCatalog DescriptionPrerequisite: CSE 3153 This course will study how databases are used with programming applications. Topics include advanced PL/SQL (or similar database programming language), database transaction, database security, database maintenance, and distributed and web databases.Course OutcomesStudents who complete this course successfully will be able toUnderstand and describe current and emerging database models and technologies;Develop functions and procedures for data manipulation and database access auditing;Describe database monitoring and performance tuning;Describe database security and administration issues, including backup and recovery;Explain the concepts of data warehousing and data miningTextbookThere is no textbook assigned. All readings are assigned in weekly modules. I require that you read the relevant papers and tutorials each week as a way to prepare you for assigned lab/quiz. Knowledge of the readings will reduce the time it takes you to finish lab assignments.Course ModulesTopic Assigned ReadingModule 1 SQL ReviewSQL Overview of the?Structured Query Language (SQL)?and how Oracle Database processes SQL statements.This chapter includes the following topics:Introduction to SQLOverview of SQL StatementsOverview of the OptimizerOverview of SQL Processing of basic structures of rows and tables , such as representation of data in rows. It also gives definition of tables , constraint and different keys such as Primary, super key.Associations Information about the various UML associations, relational schema, foreign keys, the concept of parent and child table and referential integrity.Keys Information about keys such as Candidate keys, size of Primary keys. How to design a normalized database , First Normal Form, Second Normal Form, third normal form and then practice of normalizing the database with examples. 2 Introduction to SQL Procedures & FunctionsPL/SQL Language Fundamentals Explains these following aspects of the PL/SQL language:Character SetsLexical UnitsDeclarationsReferences to IdentifiersScope and Visibility of IdentifiersAssigning Values to VariablesExpressionsError-Reporting FunctionsSQL Functions in PL/SQL ExpressionsPragmasConditional CompilationSQL Subprograms Reasons to Use Subprograms, Nested, Package, and Standalone Subprograms, Subprogram Invocations, Subprogram Parts, Forward Declaration, Subprogram Parameters, Subprogram Invocation Resolution, Overloaded Subprograms, Recursive Subprograms, Subprogram Side Effects, PL/SQL Function Result Cache, PL/SQL Functions that SQL Statements Can Invoke, Invoker's Rights and Definer's Rights (AUTHID Property), External Subprograms.Module 3 Advanced SQL Procedures & FunctionsUsing PL/SQL Control Structures How to use testing conditions such as: IF-THEN statement, IF-THEN-ELSE statement, IF-THEN-ELSIF statement, Simple case statement, Searched case statement, Guidelines for IF and CASE statements with examples.Module 4 Overview of Database AdministrationOracle Triggers Creation of triggers with syntax and explanations of basic components in PL/SQL triggers, examples of creating triggers, Invoking Row-level triggers.Enforcing business rules with triggers (v=sql.80).aspxThis is about enforcing business rules with triggers, comparison of triggers with constraints in enforcing business rules.Module 5 Database Environment Modules 1 - 4Module 6 Database and Application DesignGuidelines for the Database Specifications Specification Checklist.doc Database Specification checklist, this checklist is provided as part of the evaluation process for the Database Specifications. The checklist assists designated reviewers in determining whether specifications meet criteria established in HUD’s System Development Methodology (SDM). The objective of the evaluation is to determine whether the document complies with HUD development methodology requirements. ACID Properties Explanations about transactions covers: definition of transaction, Benefits of using transaction, example of global transaction, what is Oracle tuxedo Transaction manager, how system tracks distributes transaction processing, how system uses a two-phase commit to commit transaction.Naming Convention Oracle naming conventions about tables names, field names, primary key fields, view names, index names, naming constraints, naming about other fields, SQL keywords, Pl/SQL naming, instances, naming data files, tablespaces, REDO logs and at last summary of Naming conventions.Data Dictionary This explains storage of the data dictionary, how oracle databases use the Data Dictionary, public synonyms for data dictionary views, data dictionary cache.Module 7 Data Availability and Change ManagementCreating Indexes in Oracle Oracle documentation of managing indexes. Chapter covers: Indexes, guidelines for managing indexes, creating indexes, altering indexes, monitoring space use of indexes, dropping indexes, indexes data dictionary views.Oracle Data structure denormalization Detailed description of Oracle Data Structure denormalization. Also explains when to add redundancy and violate third normal form, planned data denormalization, Snapshots, Varrays, Oracle Materialized view with examples and at last the conclusion.Isolation Wikipedia article of Isolation of Database systems covers: concurrency control, different Isolation levels, and default isolation level.Module 8 Performance Management Oracle Database High Availability (pp 3-23)Oracle documentation of Maximize Availability with Oracle Database 12c. It contains Oracle database high availability, addressing unplanned downtime, addressing planned downtime and managing Oracle Database High availability Solutions.Database Performance Analyzer (watch now link)Talk about Database Performance Analyzer.Module 9 Database SecurityManaging Security for Oracle Database Users Oracle documentation of Creating a New User Account and Granting the User the Minimum Database Privileges, contains: example of create user statement and grant statement. Guidelines for choosing account names documentation of creating a new user account and granting the user the minimum database privileges, with examples of creating users and also contains guidelines or restrictions on creating user name for a account.Oracle security check lists vulnerability database which contains security checklist that provides detailed low level guidance on setting the security configuration of operating systems and application, it has security checklist for Oracle 10g. 11g and 12c also.Multitenant Architecture Oracle documentation of Introduction to the Multitenant Architecture, it contains: about the multitenant architecture, benefits of multitenant architecture, path to database consolidation and multitenant environment documentation roadmap.Module 10 Database Backup and RecoveryOracle Backup and Recovery (48 pages)Oracle documentation of Database Backup and Recovery User's Guide, it contains links for: Overview of Backup and Recovery, Starting and Configuring RMAN and Flashback Database, Backing Up and Archiving Data, Managing RMAN Backups, Diagnosing and Responding to Failures, Tuning and Troubleshooting, Transferring Data with RMAN and Performing User-Managed Backup and Recovery. Module 11 Data WarehouseOracle Database/ Data Warehousing Guide/ 12c (chapters 1, 2 and 3 will be included in the test 2)Oracle documentation of Database Data Warehousing Guide. It contains links for: Data Warehouse - Fundamentals, Optimizing Data Warehouses, Data Movement/ETL and Relational Analytics.The Top 10 Trends In Data Warehousing A new white paper from Oracle explores the top 10 trends and opportunities in data warehousing. Here’s a recap of that Top 10 list along with my own take on each trend.Additional readingText mining examples blog on the promise on connected science. article on Data mining, contains definitions of: Data, Information, and Knowledge and Data Warehouses. Also it explains: What can data mining do?, How does data mining work?, and What technological infrastructure is required. article on An Introduction to Data Mining, topics include: Overview, The Foundations of Data Mining, The Scope of Data Mining, How Data Mining Works, An Architecture for Data Mining, Profitable Applications and conclusion. article of Top 10 Big Data Use Cases to Watch, the top 10 key enterprise use cases for big data, what these use cases are, why companies are investing in them, and their common reference architectures. Module 12 NoSQL Databases NoSQL An article about NoSQL. Topics includes: Introduction, Classical relation database follow the ACID Rules, Distributed Systems, Advantages of Distributed Computing, Disadvantages of Distributed Computing, Scalability, What is NoSQL , Why NoSQL with examples, RDBMS vs NoSQL, RDBMS vs NoSQL, CAP Theorem (Brewer’s Theorem), NoSQL pros/cons, The BASE, NoSQL Categories, Key-value stores, Column-oriented databases, Graph databases, Document Oriented databases, Production deployment and aat last summary in a video.MongoDB MongoDB documentation of Introduction to MongoDB. This document contains topics: Document database and Key features of MongoDB. MongoDB Installation documentation of Install MongoDB Community Edition on Windows. Topics includes: Overview, Requirements, Get MongoDB Community Edition, Install MongoDB Community Edition, Run MongoDB Community Edition, Configure a Windows Service for MongoDB Community Edition, Manually Create a Windows Service for MongoDB Community Edition and some Additional Resources.2. IT 5433 – Database Design and Applications Supporting Material Instructor: Dr. Lei LiEmail: lli13@kennesaw.eduCatalog Description: This course will provide a practical foundation of database systems with emphasis on relational database design, implementation, and management. Topics include normalization, ERD, logical and physical design, SQL query, database applications, usage of XML in database, and data warehouse.Learning Objectives:Students who complete this course successfully will be able to:Compare and contrast the basic database models;Analyze, design, develop and implement a relational database system based on business requirements;Create, modify and query databases using the SQL language;Analyze the major aspects of database administration and compare and contrast issues of database security; Describe XML and its use in database systems;Conduct independent research on a subject related to the course material.Learning ModulesLM1. Database EnvironmentLearning objectives:Understand key terms in databaseExplain file processing systemsList parts of a database environmentExplain types of database develop approachesLinks:1. . . 2 – ER & EER ModelAfter this?module, student will be able to:Explain importance of data modelingDefine and use the entity-relationship modelDefine E/R termsDescribe the enhanced E/R modelDescribe how super types and sub-types are modeledExplain when you would use an enhanced E/R modelLinks to potential free course material:Link 1: link provides information on data modeling.Link 2: link provides the design, entities and attributes, entity types, keys, graphical representation, relationships, and attributes and roles of an entity relationship. Link 3: link is about the enhanced entity-relationship modelLink 4: Design – ER Notation. Module 3 – Relational Data Model After this?module, student will be able to:List the 5 properties of relationsList the properties of a candidate key, primary key and foreign key.Be proficient in converting ERD into relational data model Explain and use normalization up to 3rd normal formLinks to potential free course material:Link 1: to relational data modelLink 2: 3: link is about ERD to relational model mapping.Link 4: Design – ER to tables and Normalization. Module 4 – Physical Design After this?module, student will be able to:Describe the physical database design processExplain how attributes transpose from the logical to physical modelDescribe the different types of file organizationsDescribe the different types of indexingDescribe the pros and cons denormalizationLinks to potential free course material:Link 1: of Physical DesignLink 2: Database DesignLink 3: and Cons of DenormalizationLink 4: Design in Data WarehousesLink 5: Organization Module 5 – SQL After this?module, student will be able to:Explain the basic commands and functions of SQLUse SQL for data administrationUse SQL for data manipulationUse SQL to query a database to extract useful informationUse SQL to create a trigger and procedure for a databaseLinks to potential free course material:Link 1: 2: 3: 4: 6 – Database Application After this?module, student will be able to:Explain three components of a client-server systemDescribe differences between a 2-tiered and 3-tiered systemDescribe key components of a Web application using a databaseExplain the purpose of XML, XQuery, JSP, PHP and Links to potential free course material:Link 1: 2: –server_model Link 3: Module 6 – Data Warehouse After this?module, student will be able to:Describe the needs for data warehouseDescribe the three levels of a data warehouseExplain the independent and dependent data martExplain the basic concept of big data, NoSQL, OLAP, data visualization, and data mining.Links to potential free course material:Link 1: Introduction to data warehouse: Link 2: Three levels of database warehouse: Link 3: Data mart: Link 4: Big data: Link 5: NoSQL: Module 8 – Data Administration and Database AdministrationAfter this?module, student will be able to:Explain major functions of a data administration and database administrationDescribe data dictionaries and repositoriesDescribe the basics of database securityUnderstand the role of databases in Sarbanes-Oxley compliance.Links to potential free course material:Link 1: Data administration: Link 2: Data administration and database administration: Link 3: Data dictionaries: Link 4: Databases in Sarbanes-Oxley compliance: Link 5: 10 common database security vulnerabilities: Module 9 – Distributed DatabaseAfter this?module, student will be able to:Explain distributed database modelsList reasons why an enterprise would choose a distributed database model over a centralized modelExplain data replication and partitioningLinks to potential free course material:Link 1: Distributed Database Book Chapter: 3. IT 6713 Business IntelligenceIT 6713 Business Intelligence Course Outline and Learning MaterialsFor complete collection of learning materials, including instructor developed materials, please visit 1: IntroductionRequired reading: use the review questions (Task 1) to guide your readings. Please complete this first before the class.A practical introduction of BI by Jared Hillam (Intricity): BI intro video by LearnItFirst (there are some good points which I do agree): Overview of Business Intelligence Technology (CACM article provided in D2L). Also accessible at notes: use the lecture notes as a reading and learning guide; follow the resources presented in the slides for further information and additional learning.Downloadable from resources and readings: some more good readings. More can be found in the lecture notes. Find your own resources and conduct your own research if necessary.The problem of DIKW: Brief History of Decision Support Systems by D.J. Power: Forrester Wave?: Enterprise Business Intelligence Platforms, Q1 2015 Analytics and Business Intelligence: of BI (casual video with wacky visuals): 2: Dimensional data modelRequired reading - please complete this first before the class or start any other tasks.“What are Dimensions and Measures” a very quick, simple, and high level introduction video: more technical introduction of dimensional modeling Dimensional modeling introduction: Facts and dimensions: Star schema and snowflake schema are two common relational database schema types that implements the dimension model. notes (dimensional modeling.pdf): use the lecture notes as a learning and reviewing guide; follow the resources presented in the slides for further information and additional learning.Additional learning resources: some more good readings; these are provided to you for further exploration of the dimensional modeling techniques beyond this course. Also can be found in the lecture notes. Find your own resources and conduct your own research if necessary.A Dimensional Modeling Manifesto by Ralph Kimball: Dimensional Modeling training serials playlist: dimensional model tutorials: Kimball Dimensional Modeling Techniques collection: redbooks, Dimensional Modeling: In a Business Intelligence Environment 3: Data StorageRequired reading - please complete this first before the class or start any other tasks.Overview by Intricity: Warehousing by Andy Wicks: Data Warehousing – Overview: notes: data-storage.pdf. Use the lecture notes as a learning and reviewing guide; follow the resources presented in the slides for further information and additional learning.Additional resources and readings:Oracle Introduction to Data Warehousing Concepts: Inmon's Data Warehouse Definition Still Accurate? Overview of Data Warehousing and OLAP Technology 4: ETLRequired readingsWhat is an ETL Tool? A quick overview from Intricity: Importance of the ETL (describes some typical problems): overview: (chapter from Encyclopedia of Database Systems, pp 1095-1101)Overview of Power BI and Power Query: Lecture notes: ETL.pdf - use the lecture notes as a learning and reviewing guide; follow the resources presented in the slides for further information and additional learning.Labs and tutorials: see lab4.pdfAdditional resources and readings: 5: SSISCore learning materialsDownload and install SSIS data tools (note it is installed on our virtual lab computers already): First Integration Services Solution (download the video to view): SSIS Tutorial: Creating a Simple ETL Package (6 lessons): (v=sql.120).aspx Instructor’s three tutorials posted in D2L.Integration Services (SSIS) Features and Tasks (v=sql.120).aspx – particularly the following sectionsIntegration Services (SSIS) and Studio EnvironmentsIntegration Services (SSIS) PackagesIntegration Services (SSIS) ConnectionsIntegration Services (SSIS) ProjectsTop 10 Common Transformations in SSIS: Additional resources and readings: 6: OLAPCore required readingsVideo introductionWhat is OLAP from Intricity? is Business Intelligence and an OLAP Cube from : tutorial from TutorialsPoint: and also some good visuals from Hamel, Database Queries, Data Mining and OLAP, The Encyclopedia of Data Warehousing and Mining, 2nd Edition, Idea Group Publishers, 2008. notes with references: olap.pdfSSAS labs and tutorials – see lab 6 guide.Additional readings and resources:The origins of today’s OLAP products: AND OLAP Server Definitions: 7: Multidimensional Data AnalysisRequired readingsDemo: Explore Adventure Works in Excel by using an OLAP PivotTable report: Using Excel to interact with a SSAS cube (it is for earlier version but very similar in 2013): Excel Cube functionsQuick overview: OLAP pivot table extension: notes with references: olap query.pdfResources listed in the lab 7, including two tutorials by the instructor on Excel pivot table and cube functions.Additional readings and resources:Layout and format of Excel pivot table: Select data in a pivot table: server action: 8: Data VisualizationCore readingsThe Value of Data Visualization: MS data visualization tool choices: is a business dashboard? dashboard tutorial: notes: data visualization and dashboard.pdf and lab 8.Additional readings and resources:Data visualization for human perception: confusion: , B. (1996). The eyes have it: A task by data type taxonomy for information visualizations. tool choices: Started With Dashboards: 9: Personal BICore readingsPersonal Self Service BI: Power BI (Power Pivot, Power Query, Power View) (follow the article to the next three sections) BI - Overview and Learning: started with PowerPivot (complete the Get started section; the rest is for your reference for the term project): Lecture notes and tutorials: personal BI.pdf and lab 9.Additional resources and readings: ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download