SW 402 Database Concepts
Syllabus
Name of Course:
SW 402 Database Concepts/Oracle SQL
Instructor:
Joseph J. Corcoran
Database Consultant and Assistant Professor of Software Engineering
E-Mail/Phone:
Tandem_26@hotmail.com
(203) 877-4324
Lecture Hours:
6:30 p.m.-9:30 p.m. (includes LAB time)
Prerequisites:
none
Class Requirements:
All homework and lab work is to be submitted on time otherwise penalties will be applied. Two formal exams (Midterm & Final; 1 project and various LAB Assignments in Oracle 9i)
Course Description:
Focus is on the steps required to build and maintain the database infrastructure for client/server applications. The course covers the physical design and implementation of the database, the use of the database to meet the informational needs of a client/server system, and the installation, operation, and maintenance of RDBMS software. Specific topics include SQL, SQL utilities, and the use of an RDBMS, hardware and software tuning for maximum performance, backup and recovery of data, security, and control systems. Students perform a number of hands-on exercises using a RDBMS running on Windows NT. Microsoft SQL Server and ORACLE are used as the software vehicles for Classes and lab exercises. The course is intended for application programmers and database designers in a client/server environment. Lab included.
Course Objectives:
Classes 1 & 2 (Chap's 1 & 2 in Watson; Chap's 1 & 14 in Oracle 9i)
The student will be able to
(a) understand the key concepts of data management
(b) recognize that there are many components of an organization's memory
(c) understand the problems with existing data management systems
(d) realize that successful data management requires an integrated understanding of organizational behavior and information technology.
(e) understand the importance of information to society/organization
(f) be able to describe the characteristics of common information delivery systems
Topics Include: Individual Data Management; Organizational Data Management; Components of Organizational Memory; Problems with DBMS; Information and Organizational Change; Change Information; Database Design; SQL Overview; Report Formatting Options
Classes 3 & 4 (Chap's 3 in Watson; Chap's 2 & 3 in Oracle 9i)
The student will be able to
(a) model a single entity
(b) define a single database
(c) write queries for a single table database
(d) write simple queries using SELECT/WHERE/ORDER BY clauses
(e) format data retrieved by a query
(f) differentiate between SQL*Plus commands and SQL statements
Topics Include: The Relational Model; Modeling a single entity database; Creating a single table database; Oracle Datatypes; Operators and Expressions; SQL*Plus Basics
Classes 5 & 6 (Chap's 4 & 10 in Watson; Chapter 6 in Oracle 9i)
The student will be able to
(a) model a one-to-many relationship between two entities
(b) define a database with a one-to-many relationship write queries for a database with a one-to-many relationship
(d) write queries using single-row functions
(e) write queries using GROUP aggregate functions
Topics Include: Single-Row Functions in SQL (NULLS/Character/Numeric Date/Conversion Functions); GROUP (Multi-row) Functions; Limiting Grouped Data with HAVING; Nesting Functions
Classes 7 & 8 (Chapter 5 in Watson; Chap's 4,5 & 7 in Oracle 9i)
The student will be able to
(a) model a many-to-many relationship between two entities
(b) define a database with a many-to-many relationship write queries for a database with a many-to-many relationship
(d) write SELECT statements to access data from more than one table using equality and non-equality joins
(e) view data using outer joins
(f) write single-row and multiple-row subqueries
Topics Include: Creating a Relational Database with an m:m relationship; Equality and Non-Equality Joins; Cartesian Joins; Outer Joins; Self-Join; Set Operators; Subqueries
Classes 9 & 10 (Chapter 6 in Watson; Chap's 10 & 13 in Oracle 9i)
The student will be able to
(a) model a one-to-one and recursive relationships
(b) define a database with one-to-one and recursive relationships
(c) write queries against one-to-one and recursive relationships
(d) be able to describe each DML statement
(e) Insert/Update/Delete rows in a table
(f) Control transactions with the SET TRANSACTION statement
(g) Create/modify users and to create roles for security
Topics Include: Modeling/Mapping/Querying 1-to-1 and Recursive Relationships; LOCKING a Table; Transaction Control; Privileges and Roles; Externally Authenticated User Accounts
Classes 11 & 12 (Chap's 7 & 8 in Watson; Chap's 8,9 & 12 in Oracle 9i)
The student will be able to
(a) be able to create a data model of a relational database
(b) be able to create/describe/alter/drop database tables and views
(c) be able to drop/rename/truncate tables
(d) be able to retrieve/insert/update/delete data through a view
(e) be able to understand the process of normalization
(f) be able to distinguish between different normal forms
(g) recognize different data modeling approaches
(h) describe constraints
(i) create and maintain primary key constraints/referential integrity constraints/check constraints/sequences
(j) describe the different types of indexes
Topics Include: Data Modeling; Relationship Descriptors as Identifiers; Managing Tables and Views; Creating from Another Table;
Modifying Table Definitions; Normalization; Common Data Dictionary Views; Procedures and Packages; Triggers and the Data Dictionary
Classes 13 & 14 (Chap's 9 & 11 in Watson; Chap 15 & 16 in Oracle 9i)
The student will be able to
(a) know the structures of the relational model
(b) understand relational algebra commands
(c) be able to determine whether a DBMS is completely relational
(d) be able to declare PL/SQL variables
(e) be able to describe the rules of the nested blocks
(f) be able to identify the uses and types of control structures
(g) be able to recommend a data storage structure
(h) be able to recommend a storage device
(i) be able to write DML statements in PL/SQL
(j) be able to control transactions
(k) be able to determine the outcome of SQL DML statements
Topics Include: Data Structures; Integrity Rules; Manipulation Languages; PL/SQL Block; IF Statement; Nested LOOPS and LABELS
Data Coding Standards; Data Storage Devices; Comparative Analysis; Data Compression; Writing Explicit Cursors; Exception Handling
Class 15 (Chapter 19 in Watson; Chapter 11 in Oracle 9i)
The student will be able to
(a) be able to recommend a data storage structure
(b) be able to recommend a storage device
(c) be able to write DML statements in PL/SQL
(d) be able to control transactions
(e) be able to determine the outcome of SQL DML statements
Topics Include: Data Coding Standards; Data Storage Devices; Comparative Analysis; Data Compression; Writing Explicit Cursors;
Complex and Inline Views
Textbooks: (both texts are required)
"Oracle 9i: SQL with an Introduction to PL/SQL"
by Lannes L. Morris-Murphy; Copyright 2003 Course Technology,
a division of Thomson Learning, Inc. ISBN 0-619-06475-7
"Data Management: Databases and Organizations"
(fourth edition) by Richard T. Watson
John Wiley & Sons, Inc.
ISBN 0-471-34711-6 Copyright 2004
Attendance Policy:
Students are expected to attend all classes. If an emergency arises and a student cannot attend a class he/she is still responsible for material covered and due dates on all assigned tasks.
Grading Policy:
Mid-Term: approx. 33%
Project and Lab Assignments: approx. 33%
Final: approx. 33%
Extra Credit Projects Acceptable
|
