Session   

Contents

1M

Introduction to class

1W

TOPIC: Database Management Systems

Read: Ch1 (3-38)

• Concept of file, records, fields
• Limitation of file-based approach.
• Differences between file-based and DBMS approach. Advantages of DBMS
• Main components of DBMS
• Different users of DBMS and different access methods (including DDL, DML, application programs)

1F

Read: Ch2, pages 38-48 and 58-68. Ch 4, pages 112-125

• DBMS architecture and main components (not all detail from textbook needed). Difference between procedural and non-procedural DDL.
• ANSI-SPARC 3 layer architecture (and its relationship/lack of relationship to DBMS architecture). Definitions of different levels.
• Logical and physical data independence
• Concept, functions of 4GL.
• Outline of DB development lifecycle (will return to later)

2M

Demonstration of a Microsoft Access application

• file server vs. client server database architectures

2W

TOPIC: Entity Relationship Modelling

Read: Ch 5, pages 149-167

• Role of ERM in overall DB lifecycle
• Entity types (strong and weak)
• Attributes (simple, composite, etc.)
• Keys (primary, candidate, composite)

2F

Read: Ch 5, pages 167-179

• Relationship types (how to diagram, degrees)

• Structural Constraints (participation, cardinality)
• Problems in ERM (Fan and Chasm)

3M

Read Ch 5, 179-187

• Subclass and super class
• Attribute Inheritance
• Categorization

• Example of ERM

3W

Read Ch 7,  225-241

• Review of sub-class and superclass.

Conceptual Database Design Methodology (begins)

·        Purpose of Methodology

·        Identification of Entity Types

3F

Conceptual Database Design Methodology (continues)

• Identification of Relationship Types
• Identification and Association of Attributes with Entities/Relationships
• Attribute Domains
• Primary Keys (review)
• Discussion of project exercise.

Here is a handy checklist to use for this assignment

Distribution of individual assignment 1

4M

NO CLASS – WORK ON ASSIGNMENT 1, Project teams

4W

Assignment 1 is due in class

TOPIC: The Relational Model

Read: Chapter 3 – pages 71 to 86

• The idea of a relation
• Properties of a relation (not ordered, no duplicates, etc.)
• Attributes and properties of attributes
• Correspondences between set theory and the relational model
• Attribute domains

4F

• Comparison of relational model concepts to Oracle, file and ERM concepts
• Keys in the relational model (primary, candidate and super)
• Nulls in the relational model (meaning)
• Entity Integrity (nulls and primary keys)

Submission of project topic choices and team makeup

• Discussion of group exercises

5M

• Review of assignment 1

·        Discussion of model answer

·        Questions

·        Discussion of common mistakes

5W

Read: Chapter 3 – pages 86-96

(This is tough, but please do your best. Knowledge of relational concepts is required, but you do not need to learn formal algebraic notation.)

• Basic Relational Operations
• Select
• Project (including treatment of duplicates)
• Plus (union) and Minus (set different). Union compatibility.

5F

• Joins
  • Comparison of Joins to Relationships in ERM
  • Cartesian Products
  • Joins (theta, equi and natural)
  • Inner versus outer joins
• Foreign Keys (introduction)
• Views

Progress report due for group projects

6M

TOPIC: Logical Database Design for the Relational Model

Read Chapter 8 (242-260 only)

Translation of ERM into Relational Schema

• Use of foreign keys to represent relationships (1:1, 1:M)
• Relational Integrity Constraints
• Translation of participation constraints

6W

MID-TERM EXAMINATION (in-class)

(Exam will cover DBMS concepts, ERM techniques and Relational Model/ Operations. Specific SQL language features will not be included)

6F

Translation of ERM into Relation Schema (continues…)

• Break down of complex relationships (M:M)
• Treatment of relationships with attributes and n-ary relationships
• Translation of weak entities
• Translation of subclass/superclass

Progress report due for group projects

7M

Review of midterm and associated material

7W

TOPIC: SQL and Oracle

Read: Chapter 13 (pages 384-404)

Read: ORACLE book, chapter 3 (38-54)

• The Select Statement

·        The Select… From… clauses

·        The Where clause

·        The Order by clause

• Comparison with Select and Project in relational algebra L

7F

Read: ORACLE book, chapter 3 (54-68)

• Basic Joins in SQ

• Insert command
• Update command

Progress report due for group projects

8M

DDL in SQL

ORACLE book, chapter 18 (366-387)

Chapter 13 (421-436)

• Create Table command
• SQL data types (and relationship to attribute domains)

8W

Read: Chapter 14 (440-467)

Assignment 2 issued (covers SQL, conversion of ERM to relational model)

• Integrity Constraints

·        Difference between table and column constaints

·        “check” constraints

·        primary key constraints

·        foreign key constraints (include cascading of deletes)

·        uniqueness constrains

8F

Advanced DML techniques

Read: Chapter 3 (401-420)

• Views
  1. Advantages of views
  2. Disadvantages of views
  3. Use of views to provide row and column level security
• Calculated fields
• Granting rights

Progress report due for group projects

9M

Grouping Results and Aggregate functions

• Aggregate functions (count, sum, avg, min, max)
• The HAVING clause, difference between having and where
• Use of aggregate functions in views
• Limitations of group by (totals, averages only for most specific level)

9W

Assignment 2 due in class

For model answers see here (many tables) and here (few tables).

·        Review of grouping

·        “IN” and “EXISTS” with lists of values

·        Basic subqueries

·        Relationship of subqueries and joins

·        Correlated subqueries

9F

Normalization Techniques

Read: Chapter 6 (192-217)

Oracle Book – Chapter 40 (898-907)

• Purpose of Normalization
• Functional Dependency
• 1^st, 2^nd and 3^rd normal forms

Progress report due for group projects

10M

University Holiday – Memorial Day

10W

Practical Database Design & Database Application Architecture

• Real-world considerations
• Why sometimes not to normalize!
• Pros and cons of highly normalized design (using assignment 2 as an example)

10F

• Web database design
• Class demonstration of honors projects (those with interfaces)

Finished projects due in class.

Final project statement due in class

Individual project summaries due in class.

11

Final Examination

ISYS 210 002 Database Management

Wednesday June 6th, 2001

10:30am - 12:30pm  Randell 121

Hints on content available.