CSCI 39500 DATABASE DESIGN HUNTER COLLEGE CUNY SYLLABUS GRADESCOPE
LECTURE AND LAB Genady Maryash gmaryash@hunter.cuny.edu Monday, Wednesday 8:30am-9:45am Zoom
TEXTBOOKS Clare Churcher “Beginning Database Design” 2nd (2012) Edition, and
Clare Churcher “Beginning SQL Queries” 2nd (2016) — either books or PDFs.
Additional requireded readings and tutorials will be provided on this course webpage.
PYTHON REFRESHER (if needed) huntercsci127.github.io/f25.html
REQUIRED SUPPLIES You will write and draw all homeworks, tests, and exams with pen on paper in the exact maner that I specify. You will photo-scan and submit all to gradescope for semi-automated grading. Maximum consistency being essential, you MUST buy this PARTICULAR GRID PAPER and these 0.7mm BLACK Pilot FriXion pens - both very inexpensive! I will not allow any other paper or pens/pencils. You MUST also use this particular free scanner app: 🍏 / 🤖
OUTLINE OF WEEKLY TOPICS 🎦 1 2
| LECTURE — DATABASE DESIGN | LAB — DATABASE USE | |
|---|---|---|
| WEEK 1 What Can Go Wrong LAB 1 SQLite DB Overview |
Mishandling Keywords and Categories 1 Repeated Information 4 Designing for a Single Report 6 |
Introducing Database Tables 1 Attributes 2 Introducing Data Models 6The Primary Key 3 Inserting, Updating Rows in Table 3 Designing Appropriate Tables 5 Retrieving Info from Database 9 Process Approach 10 Summary 13Outcome Approach 11 Why Consider Two Approaches 12 |
| WEEK2 Development Process LAB 2 SQLite with DBeaver Editor Simple Querries on One Table |
Initial Problem Statement 10 Simple Data Model 12 Classes and Objects 12 Further Analysis: Revisiting the Use Cases 16Relationships 13 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 Conditions for Selecting Rows 19 Comparison Operators 19 Dealing with Nulls 23Logical Operators 21 Finding Nulls 24 Managing Duplicates 25Comparisons Involving Null Values 24 Ordering Output 28 Performing Simple Counts 29 Avoiding Common Mistakes 29 Wrong Use of WHERE with “both” 30 Wrong Use of WHERE with “not” 32 |
| WEEK 3 Initial Requirements and Use Cases LAB 3 Python Host- Language Interface A First Look at Joins |
Real and Abstract Views of a Problem 26 Data Minding 27 What Does the User Do? 30Task Automation 28 What Data Are Involved? 30 What Is the Objective of the System? 32 What Data Satisfies the Objective? 33 What are the Input Use Cases? 34 What Is the First Data Model? 36 What Are the Output Use Cases? 37 More About Use Cases 38 Actors 38 Finding Out More About the Problem 40Exceptions and Extensions 39 Use Cases for Maintaining Data 39 Use Cases for Reporting Information 40 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 Outcome Approach to Joins 36Inner Join 35 Extending Join Queries 38 A Process Approach 39 Other Types of Joins 44Order of Operations 41 An Outcome Approach 42 Joins by Diagrammatic Interface 43 Outer Joins 45 |
| WEEK 4 Learning from Data Model LAB 4 External Data Sources Subqueries |
Review of Data Models 43 Optionality: Should It Be 0 or 1? 46 Student Course Example 46 A Cardinality of 1, or Occasionally 2? 48Customer Order Example 47 Insect Example 47 Insect Example 48 A Cardinality of 1: With Historical Data? 50Sports Club Example 49 Sports Club Example 50 Many–Many: Are We Missing Anything? 52Departments Example 51 Insect Example 51 Sports Club Example 53 Student Course Example 55 Meal Delivery Example 55 A Many‑Many sans Intermediate Class 56 |
IN Keyword 51 Using IN with Subqueries 52 Being Careful with NOT and <> 54 EXISTS Keyword 57 Different Types of Subqueries 60 Inner Query Returns Single Value 60 Using Subqueries for Updating 63Inner Query Returns Set of Values 62 Inner Query Checks for Existence 62 Summary 64 Examples of Many Subqueriy Types 65 Examples of Many Subquery Uses 66 |
| WEEK 5 Making a Data Model LAB 5 Python Flask Front End Self Joins |
Attribute, Class, or Relationship? 59 Two, More Relationships Between Classes 61 Different Routes Between Classes 63 Redundant Information 64 Relationships with Objects of Same Class 68Routes Providing Different Information 65 False Information from Route (Fan Trap) 65 Gaps Between Classes (Chasm Trap) 67 Relationships with More Than Two Classes 69 |
Self Relationships 67 Creating a Self Join 70 Questions Involving “Both” 79Queries Involving a Self Join 71 Outcome Approach to Self Joins 76 An Outcome Approach 80 Summary 82A Process Approach 81 Self Relationships 82 Questions Involving Word “Both” 82 |
| WEEK6Specialization and Generalization LAB 6 More Python Flask Front End MultipleRelationships Between Tables |
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 When Is Inheritance Worth Considering? 81Confusing an Association with a Subclass 81 Should the Superclass Have Objects? 83 Objects Belonging to Many Subclasses 84 Composites and Aggregates 87 It Isn’t Easy 89 |
Two Relationships Between Tables 85 Info from Multiple Relationships 89 Process Approach 90 Business Rules 94Outcome Approach 93 Summary 97 |
| WEEK 7 From Data Model ⇾ RDB Design LAB 7 Even More Flask Front End Set Operations |
Representing the Model 94 Representing Classes and Attributes 94 Creating a Table 95 Primary Key 100Choosing Data Types 97 Domains and Constraints 98 Checking Character Fields 99 Determining a Primary Key 100 Representing Relationships 102Concatenated Keys 101 Foreign Keys 103 Referential Integrity 104 Representing 1–Many Relationships 105 Representing Many–Many Relationships 106 Representing 1–1 Relationships 108 Representing Inheritance 109 |
Overview of Basic Set Operations 99 Union-Compatible Tables 101 Ensuring Union Compatibility 104 Union 105 Selecting Appropriate Columns 106 Intersection 111Uses of Union 108 Union and Full Outer Joins 109 Uses of Intersection 112 Difference 118Must Project Appropriate Columns 115 Managing without INTERSECT 116 Uses of Difference 118 Division 122Managing without Keyword EXCEPT 121 Projecting Appropriate Columns 124 Summary 126SQL for Division 125 Union 127 Intersection 127 Difference 127 Division 128 |
| WEEK 8 Normalization LAB 8 Forms with Flask-WTF AggregateOperations |
Update Anomalies 113 Insertion Problems 114 Functional Dependencies 115Deletion Problems 114 Dealing With Update Anomalies 115 Definition of a Functional Dependency 115 Normal Forms 118Functional Dependencies, Primary Keys 116 First Normal Form 118 Additional Considerations 123Second Normal Form 119 Third Normal Form 120 Boyce–Codd Normal Form 122 Data Models, Functional Dependencies? 122 |
Simple Aggregate Functions 129 The COUNT() Function 129 Grouping 135The AVG() Function 132 The ROUND() Function 134 Other Aggregate Functions 135 Filter Result of Aggregate Query 140 Nested Queries and Aggregates 144Aggregates for Division Operation142 Summary 146 |
| WEEK 9 Keys and Constraints LAB 9 Host on PythonAnywhere Window Functions |
Choosing a Primary Key 129 More About ID Numbers 129 Unique Constraints 133Candidate Keys 130 An ID Number or a Concatenated Key? 131 Constraints Instead of Category Classes 135 Deleting Referenced Records 137 |
Simple Aggregates 147 Partitions 149 Order By Clause 150 Cumulative Aggregates 150 Framing 156Ranking 152 Combining Ordering with Partitions 153 Summary 159 OVER() 159 OVER(PARTITION BY <…>) 159 OVER(ORDER BY <…>) 159 (PARTITION BY<…>ORDER BY<…>)159 (ROWS BETWEEN<…>AND<…>) 159 |
| WEEK 10 Queries LAB 10 Project Efficiency |
Simple Queries on One Table 141 The Project Operation 142 Queries with Two or More Tables 145The Select Operation 142 Aggregates 143 Ordering 145 The Join Operation 145 How Indexes Can Help 149Set Operations 147 Indexes and Simple Queries 149 Views 152Disadvantages of Indexes 151 Types of Indexes 152 Creating Views 153 Uses for Views 153 |
What Happens to a Query 161 Finding a Record 163 Storing Records in Order 163 Join Techniques 168Clustered Index 164 Non-Clustered Indexes 165 Clustered Index Compound Key 166 Updating Indexes 167 Covering Indexes 168 Selectivity of Indexes 168 Nested Loops 169 Summary 173Merge Join 170 Other SQL Expressions for Joins 171 Primary Key 173 Foreign Keys 173 WHERE Conditions 173 ORDER BY GROUP BY DISTINCT173 Use the Tools 173 |
| WEEK 11 Other Implementations LAB 11 Project Data Model Tackle a Query |
Object–Oriented Implementation 169 Classes and Objects 169 Implementing Data Model in Spreadsheet 176Complex Types and Methods 171 Collections of Objects 173 Representing Relationships 173 OO Environments 175 1–Many Relationships 177 Implementing in XML 181Many–Many Relationships 180 Representing Relationships 183 Querying XML 186Defining XML types 185 NoSQL 186 |
Understanding the Data 175 Relationships Between Tables 175 Real World vs Implementation 178 Big Picture Method 181What Tables Are Involved? 180 Look at Some Data Values 180 Combine the Tables 181 Spotting Keywords in Questions 184Find the Subset of Rows 183 Retain Appropriate Columns 183 Consider an Intermediate View 184 And, Both, Also 185 No Idea Where to Start? 189Not, Never 187 All, Every 188 Find Some Helpful Tables 189 Checking Queries 191Answer the Question by Hand 189 Describe the Retrieved Result 190 Are There Alternatives? 190 Rows That Should Be Returned 192 Summary 193Rows That Shouldn’t Be Returned 192 Check Boundary Conditions 192 Check Null Values 193 |
| WEEK 12 UI LAB 12 Python Integration Again Common Problems |
Input Forms 157 Data Entry Forms Based on One Table 158 Reports 163Data Entry Forms Based on Many Tables159 Constraints on a Form 161 Restricting Access to a Form 163 Basing Reports on Views 163 Main Parts of a Report 164 Grouping and Summarizing 164 |
Poor Database Design 195 Data That Is Not Normalized 195 Problems with Data Values 201Tables with No Primary Key 198 Tables without Foreign Keys 199 Similar Data in Two Tables 199 Inappropriate Types 200 Unexpected Nulls 201 Diagnosing Problems 203Incorrect or Inconsistent Spelling 202 Wrong Characters in Text Fields 202 Inconsistent Case in Text Fields 203 Check Nested Parts Independently 204 Common Symptoms 205Know How Tables Are Combined 204 Remove Extra WHERE Clauses 204 Retain All the Columns 204 Check Queries in Aggregates 205 No Rows Are Returned 205 Common Typos and Syntax Problems 209Rows Are Missing 205 More Rows Than There Should Be 207 Incorrect Statistics or Aggregates 208 The Order Is Wrong 209 Summary 210 |
| WEEK 13 Final Project Presentations | ||
| WEEK 14 Semester Review | ||
| WEEK 15 Final Exam |
Computer: www.hunter.cuny.edu/te
Smartphone: www.hunter.cuny.edu/mobilete