Cleveland State University

IST 331- Important Messages - Fall 2007

Recent

 

Lecture Notes .

SPRING - 2013 In substitution for Dr. H. Paul.
The following notes were prepared by Dr. V. Matos
they include the following subjects:

MYSQL - Stored Procedudres & Triggers -
Intro to PHP -
Scripts for creating the COMPANY database -
HOMEWORK Assignment (Due April-10-2013)

 

Previous
Lecture Notes .
1. ADO.NET - Using the Command & DataReader Classes
2. ADO.NET - Using the Command & DataReader Classes

3. ADO.NET - Using DataGridView

Lecture Notes .
Microsoft SQL Server 2005

Lecture Notes .
Linking Data from External Databases.


Homework-7.
Database Programming VBA. Part 2.
Due Wed. Oct. 31.

Homework-6 .
Database Programming VBA.
Due Wed. Oct. 24.

Lecture Notes
Using the Microsoft ActiveX Data Control ADO, DAO, and MS-Access DBMS

Lecture Notes
Developing simple applications with MS-Access & VBA

Homework-5 .
Database Maintenance Part 2.
Due Wed. Oct. 10.

Homework-4 .
Database Maintenance Part 1.
Due Wed. Oct. 3.

Homework-3 .
SQL queries III - complex questions - COMPANY database.
Due Mon. Sept. 24.

Homework-2 .
SQL queries II - Asking questions - COMPANY database.
Due Mon. Sept. 17.

Homework-1 .
SQL queries I - Updating the COMPANY database.
Due Wed. Sept 12

Exam-1 .
Chapters 1 & 2. Wed. Sept. 12

Lecture Notes.
COMPANY database - Sample of SQL queries (taken from Elmasri-Navathe Fundamentals of Database Systems Ed. Addison-Wesley 5Ed. page 159)

Lecture Notes.
SQL Learning Guide - Company Database

IST 331 / 631

Modern Database Design and Implementation (4-0-4)

Description of the Course

OBJECTIVES: This course provides an understanding of the issues in managing database systems as an essential organizational resource. Students learn the enterprise data architecture components, data storage configurations, and information retrieval methods.

SCOPE: Information systems design and implementation is covered within a database management system environment. Students will demonstrate their mastery of the software development process by constructing a physical system using database software to implement the logical design.

TOPICS: Conceptual, logical, and physical data models, and modeling tools; structured and object design approaches; models for databases: relational and object oriented; design tools; data dictionaries, repositories, database implementation including user interface, data retrieval and maintenance, and reports; use of Structured-Query-Language (SQL) for retrieval, maintenace and administration; multi-tier planning and implementation; data conversion and post implementation review.


Prerequisite:
IST 311 / 511

Skill and Learning Outcomes

The student will become proficient in the utilization of

  • Techniques and software tools to design and model a conceptual/physical relational database
  • SQL statements to define, query, and control a relational database
  • MS-Access and Microsoft .NET platform (including VB.NET, ADO.NET, ASP.NET) to implement relational database applications.

Room / Time

Calendar

Final Exam

BU 117. Tu. & Th. 2:00 - 3:50 PM.

Link to University Calendar (web page listing final exams, drop date, holidays, etc.) http://www.csuohio.edu/registrar/calendar.html

Wed. Dec 12, 2:00-4:00 PM (to be confirmed).

Instructor

Victor Matos

Email
v.matos@csuohio.edu (This is my preferred way of communication)
Web Page
http://grail.cba.csuohio.edu/~matos
Office Office: BU342. Office hours: Mo & We 10:00-12:00 & 4:00-5:00 PM. (and by appointment)
Phone: 687-3911 Fax: 687-5448. Dept. Secretary: 216.687.4760
Text

Database Processing: Fundamentals, Design, and Implementation, 10/E
by David Kroenke. ISBN: 0-13-167272-X. Publisher: Prentice Hall. Copyright: 2006

Book web-site contains:Self-Study Guide, True or False, Multiple Choice, Essay, PowerPoint Presentations.

Reference book

Mastering Access 2002 Development. Alison Balster. Ed. SAMS Publishing (or newer version)

Software Microsoft Academic Alliance Program ( Read instructions)

Method of Instruction:  

This course will use (a) traditional lectures based on recitation of the material, (b) live presentation of the software in the classroom, and (b) directed tutorials. During those supervised tutorials students will implement small pieces of code related to the topics discussed in class. Students are encouraged to actively participate in the class discussions. Students may be asked to make a class presentation of their computer projects. Your instructor will try to reduce the amount of documents handed to you on paper; important messages, lecture notes, assignments, examples of previous coursework, code samples, etc., will be posted on the course web-page.

Course Policy:  

  • Class participation and regular attendance is expected. Students are responsible for bringing themselves up-to-date on class material and assignments.
  • All students are expected to read the assigned chapters before attending classes.
  • Exams will be a combination of material presented in lectures, covered in the textbook and additional notes, homework problems, and lab experience.
  • Homeworks and lab assignments should be completed and returned in operational form.
  • If I have to cancel a class, I will try to place a message on the course web page as early as possible. I will make efforts in recuperating any lost time.
  • All grading mistakes must be corrected no later than a week after receiving your papers. It is your responsibility that your exam/assignments have been graded correctly.

Tentative Schedule:

Week Chapter / Topic / Specific Objectives / Activities
1

1. Introduction

To understand the nature and characteristics of databases
To survey some important and interesting database applications
To gain a general understanding of tables and relationships
To describe the components of a Microsoft Access database system and explain the functions they perform
To describe the components of an enterprise-class database system and explain the functions they perform
To define the term database management system (DBMS) and describe the functions of a DBMS
To define the term database and describe what is contained within the database
To define the term metadata and provide examples of metadata
To define and understand database design from existing data
To define and understand database design as new systems development
To define and understand database design in database redesign
To understand the history and development of database processing

2

2. Introduction to Structured Query Language

To be able to write queries in SQL to retrieve data from a single table.
To be able to write queries in SQL to use DISTINCT, WHERE, AND, OR, NOT, BETWEEN, LIKE, IN, IS NULL, IS NOT NULL, ORDER BY, GROUP BY, and HAVING.
To be able to use the built-in functions of SUM, COUNT, MIN, MAX, and AVG with and without the use of a GROUP BY clause.
To be able to write queries in SQL to retrieve data from a single table but restricting the data based upon data in another table (subquery).
To be able to write queries in SQL to retrieve data from multiple tables table using a JOIN.

Lecture Notes: Examples of SQL code using the Company Database

3

3. The Relational Model and Normalization

• We have received one or more tables of existing data • The data is to be stored in a new database
• QUESTION: Should the data be stored as received, or should it be transformed for storage?
• We need to understand: – The relational model – Relational model terminology

4

4. Database Design Using Normalization

• We have received one or more tables of existing data • The data is to be stored in a new database
• QUESTION: Should the data be stored as received, or should it be transformed for storage?
• We need to understand: funcional and multivalued dependencies, primary/foreing/candidate keys, referencial integrityconstraints.

5

5. Data Modeling with the Entity-Relationship Model

To understand the Elements of Database Design
To be able to represent Id-dependent, 1:1, 1:N, and N:M relationships in relations
To be able to represent Weak/Strong entities in relations
To be able to represent Supertype/Subtypes as relations
To be able to represent recursive relationships
To be to represent ternary relationships


Lecture Notes: ER Modeling examples
Lecture Notes: ER Crowfoot Notation (Key)
Lecture Notes: IDEF1X - Crowfoot Conversion
Lecture Notes: Converting ER diagrams into Physical Database Designs

6 6. Transforming Data Models into Database Designs

Creating a table for each entity
Creating relationships using primary/foreign keys
Creating logic for enforcing minimum cardinalities.
7

7. SQL for Database Construction and Application Processing

Application requirements
SQL DDL and DML
SQL commands: create table, data types, constraints, relationships, cardinalities, default values and data constraints
SQL ALTER command: adding and droping columns and constraints
Removing tables
Inserting records in a table

8

8. Database Redesign

Need for database redesign
Correlated and non-correlated subqueries
Checking functional dependencies
Exists and not-exists
Nested Exists operator
Reverse engineering, dependency graphs
Database backup and Test Databases
Database redesign changes: altering tables and columns, altering relationships, changing constraints, changing cardinalities
Forward engineering

9

9. Managing Multi-User Databases

Purpose and importance of database administration.
Concurrency control.
Atomic transactions. Errors ocurring in the absence of atomicity. Error prevention.
Concurrent transaction processing. Lost update problem. Resource locking. Lock terminology.
Concurrent processing with explicit locks. Serialization. Two-phased locking.
Deadlocks. Optimistic and pesimistic locking protocols.

10

10. Managing Databases with Oracle

To be able to install Oracle and create a database
To be able to use Oracle's SQL*Plus
To be able to use SQL DDL and to create database structures with Oracle
To understand the purpose and role of stored procedures and learn how to create simple stored procedures
To understand the purpose and role of triggers and learn how to create simple stored procedures
To understand how Oracle implements concurrency control
To understand the fundamental features of Oracle backup and recovery facilities

11

11. Managing Databases with SQL/Server 2000

To be able to install SQL Server and create a database
To be able to use SQL Server's graphical utilities
To be able to submit both SQL DDL and DML via the query analyzer
To understand the purpose and role of stored procedures and learn how to create simple stored procedures
To understand the purpose and role of triggers and learn how to create simple stored procedures
To understand how SQL Server implements concurrency control
To understand the fundamental features of SQL Server backup and recovery facilities

12

12. ODBC, OLE DB, ADO, and ASP

Standards for accessing database servers

Lecture Notes: Using the Microsoft ActiveX Data Control ADO, DAO, and MS-Access DBMS
Lecture Notes: Using ADO- DAO and Oracle DBMS


13

13. XML and ADO.NET

Using XML to display and update data from organizational databases
XML documents, Extensible Style Language, Schema, Elements, Attributes
Creating XML documents from database data
Using ADO.NET to reach documents and databases. Data provider components: Connection, Command, DataReader, Dataset, DataAdaptor
Examples of ADO.NET usage in the .NET database environment

14

14. JDBC, Java Server Pages, and MySQL

To understand the characteristics of JDBC and different types of JDBC drivers.
To understand the nature of JSP and know the differences between JSP and ASP.
To learn the features and functions of MySQL and be able to compare MySQL with commercial products such as Oracle and SQL Server.

15 15. Database Processing for Business Intelligence Systems

To learn the different system architectures that can be used to support multi-user database processing and the advantages and disadvantages of each.
To understand the benefits and problems of downloading data.
To understand the nature and problems of distributed database processing.
To learn the purpose, characteristics, and basic terminology of On Line Analytical Processing (OLAP).
To learn the purpose, nature, and concepts of data warehousing and data marts.
To understand that organizational data is an asset that needs not only to be protected, but also to be effectively used.
To learn the scope, role, and basic functions of data administration.

Appendices
  1. A Microsoft Access Database (Company Demo)
  2. Lecture Notes: ER-Diagrams (1) (Power Point)
  3. Lecture Notes: ER-Diagrams (2) (Power Point)
  4. Lecture Notes: Transforming ER diagrams into Relational DBs
  5. Lecture Notes: Extended ER-Diagrams (Power Point)
  6. Lecture notes: Relational Database Design & Relational Algebra (IST331)
  7. Using the Microsoft ActiveX Data Control ADO,ORACLE8-18-Authority.ppt">SQL facilities.
  8. ADO Notes
  9. OLD sample homeworks
  10. Sample Data<: No makeup possible.
  11. Students are allowed to bring to exams a single summary page (letter size) containing their personal notes.
  12. Late Assignments: All lab assignments are due at the beginning of class on the date specified. Laboratory Assignments handed in after the class has begun will be accepted with a 20% grade penalty for a period of two weeks and then not accepted at all. All laboratory assignments must be completed. Failure to do so will lower your course grade one additional letter grade.
  13. Any project, exam, homework which is consiredered -by the instructor- to be a non-original and/or not-individual contribution of the student will be given an "F" grade.
  14. Academic Misconduct. Academic misconduct refers to any fraudulent actions or behaviors that affect the evaluation of a student's academic performance or record of academic progress. Those forms of dishonest behavior include cheating, plagiarism, and tampering. Major infractions automatically result in an entry on the student's permanent record that the student has engaged in academic misconduct (see CSU code 3.1.2.A(2)(b)). 30% from weekly Assignments (about 10 homeworks)
  15. 70% from periodic short exams (between 6 and 7 taken every other week). Short exams have a uniform grade value.
 
  • 35% Quizzes, Homeworks & Laboratory Assignments
  • 30% Midterm Exam
  • 35% Final Exam

==> Completion of Homeworks/Labs is required for obtaining a passing grade.

This is a tentative scale and
it could be changed

Letter
Grade

Quality Points

 


Passing grade for IST courses requires a C or better letter grade.

  A

> 93%  

A: Outstanding (student's performance is genuinely excellent)

  A-

90% - 93%

 

  B+

87% - 90%   

 

  B

82% - 87%

B: Very Good (student's performance is clearly commendable but not necessarily outstanding)

 

  B-

80% - 82%

 

 

  C

75% - 80%

C: Good (student's performance meets every course requirement and is acceptable; not distinguished)
    D 65%-75% D: Below Average (student's performance fails to meet course objectives and standards)

 

  F

<65%

F: Failure (student's performance is unacceptable)

Lab Assignments: Weekly assignments - To be announced via classroom/web.

ADA Adherence. If you need course adaptations or accommodations because of a disability, if you have emergency medical information to share with me, or if you need special arrangements in case the building must be evacuated, please make an appointment with me as soon as possible. My office location and hours are listed on top of this syllabus. If you need further information, please contact the ACCESS office, phone number 687-5106.

CSU Official Academic Calendar-Important Dates:
Click here for detailed deadlines (including registration and course withdrawal)

Fall 2007

First Saturday Class Aug 25
First Weekday Class Aug 27
Last Day to Add (at 8:00 pm) Aug 31
Labor Day (University Holiday) Sep 3
Last Day to Drop (at 8:00 pm) Sep 7
Columbus Day (University Holiday) Oct 8
Midterm Grades Oct 8-14

Last Day to Withdraw (at 8:00 pm) Nov 2
Veterans Day (no classes - offices open) Nov 12
Thanksgiving Recess (no classes on Saturday) Nov 22-25
Last Day of Classes Dec 7
Final Exams Dec 10-15
Commencement Dec 16
Fall Incomplete Deadline May 2

 


Programming standards

  • Every program must include your name, CSU ID number, the words 'Homework # ...', and a short description of the assignment. For example:
     ' Name: Maria Macarena  
     ' ID: 1234567            
     ' Homework #1            
     ' Description: Computing the average life of a light bulb
  • Every variable should have a meaningful name (this includes function/procedure/subprogram names).
  • Every portion of the program should be as cohesive (single purposed) as possible. This leads to a large number of small functions.
  • Every function (including the main function) should be preceded by a comment indicating its arguments and a description of the transformation it performs.
  • Non-obvious code within a function should be explained.
  • Code should not be over commented.


 


Notes from Previous Semester (Homeworks, Lecture notes, etc.)

Final Exam (Tu. 08-May-2007).
A VB.NET solution.

Lecture Notes:
Microsoft SQL-Server 2005
NOTE: Subject includes SQL2005 architecture, TSQL programming, Stored Procedures, Cursors, Transaction Processing, .NET applications, CLR Stored Procedures and Triggers.

Lecture Notes (Video -Webcasting):
Learning Resources. Video Series: SQL Server 2005 Express Edition for Beginners
This Microsoft video series is designed specifically for SQL Server beginners—individuals who are interested in learning the basics of how to create, manage, and connect to SQL Server Express databases.

Lecture Notes (Video - Webcasting):
Architecting .NET Solutions with C#. ADO.NET: Database Programming in .NET
NOTE:
You need to register with the Microsoft MSDN Webcast Registry (simple one-time process asking for your email, name & address). This is a link to the event number 1032269036
Microsoft MSDN Webcast : Architecting .NET Solutions with C# (Part 07 of 15): ADO.NET: Database Programming in .NET (Level 200)
This is the seventh webcast in the series entitled "Modern Software Development in .NET and C#", designed for Java, VB6 and C++ developers moving to .NET.
This session introduces database programming using ADO.NET, beginning with an overview of the ...
4/26/2005 7:00 PM Pacific Time (US & Canada) - 1/5/2013 12:00 AM Pacific Time (US & Canada) | Duration: 103 Minutes
Primary Language: English - Primary Target Audience: Developer
https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032269036&culture=en-US

Lecture Notes
ADO.NET - Developing Windows Applications

Homework-7 .
Programming Database Apps using VB.NET and ADO.NET
<< Due Th. April 19

Homework-6 .
PART 2. MS-Access DB applications using VBA and ADODB

Homework-5 .
PART 1. MS-Access DB applications using VBA and ADODB

Homework-4 .
A
simple application based on MS-Access & VBA

Lecture Notes
Developing simple applications with MS-Access & VBA

Homework-3 .
SQL queries III - Advanced Retrieval & Maintenance

Lecture Notes
Using the Microsoft ActiveX Data Control ADO, DAO, and MS-Access DBMS

Exam-1.
Exam includes chapters 1,,2, 3, and SQL material discussed in class (click to get class notes)
<< Th. Feb 8

Homework-2 .
SQL queries II - Using the COMPANY database.
<< Due Th. Feb 8

Homework-1 .
SQL queries I - Using the COMPANY database.

Lecture Notes.
COMPANY database - Sample of SQL queries (taken from Elmasri-Navathe Fundamentals of Database Systems Ed. Addison-Wesley 5Ed. page 159)

Lecture Notes.
SQL Learning Guide - Company Database


OLD! - OLD! - OLD!
(The following samples are taken from previous semesters and should be used only as an example of typical course-work.)

Homework and Lecture Notes


Oracle & VB6


Some SQL Links