CSCI 39500 DATABASE DESIGN HUNTER COLLEGE CITY UNIVERSITY OF NEW YORK
SYLLABUS
PROGRAMMING GUIDELINES
GRADESCOPE
LECTURE AND LAB Genady Maryash gmaryash@hunter.cuny.edu
Mondays and Wednesdays 8:30 AM - 9:45 AM on line
TEXTBOOK
Clare Churcher “Beginning Database Design” 2nd (2012) Edition — either book or PDF.
Additional requireded readings and tutorials will be provided on this course webpage.
REQUIRED SUPPLIES
You will write/draw all homeworks, tests, and exams with pen on paper in the exact maner that I demand; quality of presentation of your work is a large part of each grade. You MUST buy this PARTICULAR GRID PAPER and these 0.7mm BLACK Pilot FriXion PENS. I do not allow any other paper or pens/pencils. Scanner app: 🍏 / 🤖
COURSE OUTLINE
| DATE | LECTURE | LAB |
|---|---|---|
| WEEK 1 What Can Go Wrong LAB 1 SQLite DB and Studio |
Mishandling Keywords and Categories 1 Repeated Information 4 Designing for a Single Report 6 |
Introducing Database Tables 1 Attributes 2 The Primary Key 3 Inserting and Updating Rows in a Table 3 Designing Appropriate Tables 5 Introducing Data Models 6 Retrieving Information from a Database 9 Process and Outcome Approaches 10, 11 Why We Consider Two Approaches 12 |
| WEEK 2 Guided Tour of the Development Process LAB 2 Python Host-Language Interface |
Initial Problem Statement 10 Simple Data Model 12 Classes and Objects 12 Relationships 13 Further Analysis: Revisiting the Use Cases 16 Design 19 Implementation 20 Interfaces for Input Use Cases 21 Reports for Output Use Cases 22 |
Subsets of Rows and Columns 15 Using Aliases 18 Saving Queries 19 Specifying Conditions for Selecting Rows 19 Comparison and Logical Operators 19, 21 Dealing with Nulls 23 Finding Nulls 24 Comparisons Involving Null Values 24 Managing Duplicates 25 Ordering Output 28 Performing Simple Counts 29 Avoiding Common Mistakes 29 Incorrect Use of WHERE with “both”, “not” 30, 32 |
| WEEK 3 Initial Requirements and Use Cases LAB 3 Python Flask Frontend |
Real and Abstract Views of a Problem 26 Data Minding 27 Task Automation 28 What Does the User Do? 30 What Data Are Involved? 30 What Is the Objective of the System? 32 What Data are Required to Satisfy the Objective? 33 What are the Input Use Cases? 34 What Is the First Data Model? 36 What Are the Output Use Cases? 37 <summary>More About Use Cases 38</summary> Actors 38 Exceptions and Extensions 39 Use Cases for Maintaining Data 39 Use Cases for Reporting Information 40 <summary>Finding Out More About the Problem 40</summary> What Have We Postponed? 41 Changing Prices 41 Meals That Are Discontinued 41 Quantities of Particular Meals 41 |
The Process Approach to Joins 33 Cartesian Product 33 Inner Join 35 Outcome Approach to Joins 36 Extending Join Queries 38 A Process Approach 39 Order of Operations 41 An Outcome Approach 42 Joins Through Diagrammatic Interface 43 Other Types of Joins 44 Outer Joins 45 |
| WEEK 4 Learning from the Data Model LAB 4 Populating DB from External Data Sources |
Review of Data Models 43 Optionality: Should It Be 0 or 1? 46 Student Course Example 46 Customer Order Example 47 Insect Example 47 A Cardinality of 1: Might It Occasionally Be Two? 48 Insect Example 48 Sports Club Example 49 A Cardinality of 1: What About Historical Data? 50 Sports Club Example 50 Departments Example 51 Insect Example 51 A Many–Many: Are We Missing Anything? 52 Sports Club Example 53 Student Course Example 55 Meal Delivery Example 55 A Many‑Many without an Intermediate Class 56 |
SUBQUERIES: IN Keyword 51 Using IN with Subqueries 52 Being Careful with NOT and <> 54 EXISTS Keyword 57 Different Types of Subqueries 60 Inner Queries Returning a Single Value 60 Inner Queries Returning a Set of Values 62 Inner Queries Checking for Existence 62 Using Subqueries for Updating 63 |
| WEEK 5 Developing a Data Model LAB 5 Python: Web Forms with Flask-WTF |
Attribute, Class, or Relationship? 59 Two or More Relationships Between Classes 61 Different Routes Between Classes 63 Redundant Information 64 Routes Providing Different Information 65 False Information from a Route (Fan Trap) 65 Gaps in a Route Between Classes (Chasm Trap) 67 Relationships Between Objects of the Same Class 68 Relationships Involving More Than Two Classes 69 |
Self Relationships 67 Creating a Self Join 70 Queries Involving a Self Join 71 An Outcome Approach to Self Joins 76 Questions Involving “Both” 79 An Outcome Approach 80 A Process Approach 81 |
| WEEK 6 Generalization, Specialization LAB 6 SQLite DB Hosted on PythonAnywhere |
Classes or Objects with Much in Common 75 Specialization 76 Generalization 77 Inheritance in Summary 79 When Inheritance Is Not a Good Idea 80 Confusing Objects with Subclasses 80 Confusing an Association with a Subclass 81 When Is Inheritance Worth Considering? 81 Should the Superclass Have Objects? 83 Objects That Belong to More Than One Subclass 84 Composites and Aggregates 87 It Isn’t Easy 89 |
Two Relationships Between the Same Tables 85 Extracting Information from Multiple Relationships 89 Process Approach 90 Outcome Approach 93 Business Rules 94 |
| WEEK 8 From Data Model to Relational Database Design LAB 7 Examples of Real World DB Requirements |
Representing the Model 94 Representing Classes and Attributes 94 Creating a Table 95 Choosing Data Types 97 Domains and Constraints 98 Checking Character Fields 99 Primary Key 100 Determining a Primary Key 100 Concatenated Keys 101 Representing Relationships 102 Foreign Keys 103 Referential Integrity 104 Representing 1–Many Relationships 105 Representing Many–Many Relationships 106 Representing 1–1 Relationships 108 Representing Inheritance 109 |
|
| WEEK 7 Normalization LAB 8 Python: substantial host-language program integration |
Update Anomalies 113 Insertion Problems 114 Deletion Problems 114 Dealing With Update Anomalies 115 Functional Dependencies 115 Definition of a Functional Dependency 115 Functional Dependencies and Primary Keys 116 Normal Forms 118 First Normal Form 118 Second Normal Form 119 Third Normal Form 120 Boyce–Codd Normal Form 122 Data Models or Functional Dependencies? 122 Additional Considerations 123 |
|
| WEEK 9 More on Keys and Constraints LAB 9 Work with database Cursors |
Choosing a Primary Key 129 More About ID Numbers 129 Candidate Keys 130 An ID Number or a Concatenated Key? 131 Unique Constraints 133 Using Constraints Instead of Category Classes 135 Deleting Referenced Records 137 |
|
| WEEK 10 Query Basics | Simple Queries on One Table 141 The Project Operation 142 The Select Operation 142 Aggregates 143 Ordering 145 Queries with Two or More Tables 145 The Join Operation 145 Set Operations 147 How Indexes Can Help 149 Indexes and Simple Queries 149 Disadvantages of Indexes 151 Types of Indexes 152 Views 152 Creating Views 153 Uses for Views 153 |
|
| WEEK 11 Other Implementations LAB 11 Normalizing your project database into 4NF |
Object–Oriented Implementation 169 Classes and Objects 169 Complex Types and Methods 171 Collections of Objects 173 Representing Relationships 173 OO Environments 175 Implementing a Data Model in a Spreadsheet 176 1–Many Relationships 177 Many–Many Relationships 180 Implementing in XML 181 Representing Relationships 183 Defining XML types 185 Querying XML 186 NoSQL 186 |
|
| WEEK 12 User Interface LAB 12 Judicious Use of Denormalization |
Input Forms 157 Data Entry Forms Based on a Single Table 158 Data Entry Forms Based on Several Tables 159 Constraints on a Form 161 Restricting Access to a Form 163 Reports 163 Basing Reports on Views 163 Main Parts of a Report 164 Grouping and Summarizing 164 |
|
| WEEK 13 Final Project Presentations | ||
| WEEK 14 Semester Review | ||
| WEEK 15 Final Exam |
| The E/R Model Weak Entity Sets The DB Environment |
4.1 The Entity/Relationship Model 4.2 Design Principles 4.4 Weak Entity Sets |
| E/R Diagrams, Subclasses Relational Design Host Language Interface |
4.5 From E/R to Relational Designs 4.6 From Subclass Structures to Relations 4.7 Unified Modeling Language |
| UML Diagrams, ODL Designs to Relations |
4.8 From UML Diagrams to Relations 4.9 Object Definition Language 4.10 From ODL to Relational Designs 9.1 The Three-Tier Architecture |
| The Relational Model of Data Defining Schema |
2.1 An Overview of Data Models 2.2 Basics of the Relational Model 2.3 Defining Relation Schema in SQL 7.1 Keys and Foreign Keys |
| Simple Queries, 3-Tier Architecture Web Tier Flask Tutorial |
6.1 Simple Queries in SQL 6.2 Queries with More Than One Relation 9.1 The Three-Tier Architecture |
| Algebraic Query Language | 2.4 An Algebraic Query Language 5.1 Relational Operations on Bags |
| Functional Dependencies, Normalization | 3.1 Functional Dependencies 3.2 Rules on Functional Dependencies 3.3 Design of Relational Database Schemas |
| Constraints, (Transactions) | 7.2 Constraints on Attributes and Tuples 7.3 Modification of Constraints 6.6 Transactions in SQL |
| Subquerries, Database Modifications | 6.3 Subqueries 6.4 Full-Relation Operations 6.5 Database Modifications |
| Decomposition, BCNF and 3NF | 3.4 Decomposition: Good, Bad, and Ugly 3.5 Third Normal Form |
| Multivalued Dependencies, 4NF | 3.6 Multivalued Dependencies 3.7 An Algorithm for Discovering MVD’s |
Computer: www.hunter.cuny.edu/te
Smartphone: www.hunter.cuny.edu/mobilete