CSCI345: Database Management

Updated 2020-05-04

Syllabus     Catalog Description     Finals Schedule     Academic Calendar
PowerPoint: CH01, CH02, CH03, CH04, CH05, CH06 CH07, CH08, CH09, CH11

Final Test: Monday, 5/04/2020 from 10:00 to 11:50

Online office hours will be held during the regular class time (9:30-10:45 Tue and Thu). We will use Zoom for this. To join, here is the link: https://zoom.us/j/8345726921. Cell phone number (between 6 a.m. and 10 p.m.): 712-301-8391.

Monday, 5/04/2020

·         Northwind database

 

 

 

 

 

 

 

 

 

 

 

Day #27. Thursday, 4/30/2020: Chapter 12

·         Chapter 12: XML. Notes.

·         Videos over the notes: Part 1, part 2, part 3.

·         Review for final

·         Test will be Monday from 8:00 a.m. to 8:00 p.m.

·         Part 4 of project is due today at 11:59 p.m.

Day #26. Tuesday, 4/28/2020: Chapter 11

·         Chapter 11: Database standards. Notes.

·         Chapter 11: ODBC, OLE DB, ADO.NET. PPT. ADO, JDBC notes.

·         OLE DB coding example: Video

·         OLE DB example with Access MDB file: Notes.

·         OLE DB example with Access ACCDB file: Notes.

Chapter 10 videos

·         All of these video examples use SQL Server and the SQL Server Management Studio

·         Part 1

·         Part 2

·         Part 3

·         Part 4, creating a view

·         Part 5, creating a stored procedure: Insert customer and interest

·         Part 6, creating a stored procedure: Insert customer with transaction

·         Part 7, trigger example

Homework due next class:

·         Read chapter 12: XML

Project, PART 4: Due next Thursday, 4/30/2020

·         Project overview, Airline, Basketball, Parish. Hand in a hard copy. HAND IN: Your TABLES Access file. Your CODE Access file.

Day #25. Thursday, 4/23/2020. Using SQL Server  (Chapter 10)

·         Using SQL Server Management Studio: Notes.

·         Stored Procedures and Triggers: Notes. Notes.

·         SQL Files. These are the files from the textbook that are used in the videos.

Homework due next class:

·         Read chapter 11: The Web Server Environment

Project, PART 4: Due next Thursday, 4/30/2020

·         Project overview, Airline, Basketball, Parish. Hand in a hard copy. HAND IN: Your TABLES Access file. Your CODE Access file.

Day #24. Tuesday, 4/21/2020 Chapter 9

·         Chapter 9. Multi-user databases. Notes. There are no videos over chapter 9.

Homework due next class:

·         Chapter 9 questions. Answer the questions and submit to the drop box by 9:30 a.m. Thursday (4/23/2020).

Project, PART 4: Due next Thursday, 4/30/2020

·         Project overview, Airline, Basketball, Parish. Hand in a hard copy. HAND IN: Your TABLES Access file. Your CODE Access file.

Day #23. Thursday, 4/16/2020. Chapter 8

·         Chapter 8. Notes.

·         Chapter 8 data files:

o    VRG Access tables database

o    Chapter 8 Employee

o    Chapter 8 Parking Permit 1

o    Chapter 8 Parking Permit 2

·         Chapter 8 videos:

o    Part 1

o    Part 2

o    Part 3

o    Part 4

o    Part 5

o    Part 6

o    Part 7

o    Part 8

o    Part 9

Homework due next class:

·         Read chapter 9

Day #22. Tuesday, 4/14/2020. Finish Chapter 7

·         Chapter 7: SQL for inner joins, outer joins, views. Notes.

·         Chapter 7 videos and data files:

o    Video: Inner Joins. Database file (Access).

o    Video: Outer Joins. Salesperson Orders file (Access).

o    Video: Outer Joins: View Ridge. View Ridge Tables (Access).

o    Video: Views: View Ridge

Homework due Thursday:

·         Read chapter 8

Project, PART 3: Due next Tuesday, 4/21/2020

·         Project overview, Airline, Basketball, Parish. Hand in a hard copy. HAND IN: ER diagram from part 2. Your TABLES Access file. Your CODE Access file.

Day #21. Thursday, 4/09/2020. Test over chapter 5-6

Day #20. Tuesday, 4/07/2020. Chapter 7

·         Chapter 7: SQL for creating and modifying databases. Notes.

·         Chapter 7 videos:

o    Downloading MySQL

o    Part 1

o    Part 2: Access DDL—the CREATE TABLE command

o    Part 3: MySQL DDL—the CREATE TABLE command

o    Part 4: MySQL DML—the INSERT command

·         TEST Thursday over chapter 5-6. You will create an ER diagram in MySQL Workbench. You will have from 8:00 a.m. until 8:00 p.m. to complete it.

Project, PART 2: Due next Thursday, 4/09/2020

·         Project overview, Airline, Basketball, Parish. Hand in a hard copy. NOTE: Hand in part 1 of your project (again) when you hand in part 2. Put all documents in the drop box.

Homework due Thursday

·         Finish reading chapter 7.

 

 

 

Day #19. Thursday, 4/02/2020

·         Medical Clinic solution: Narrative, ER Diagram

·         Please read: Chapter 6 notes

·         Test #3 over chapters 5 & 6 will be Thursday, April 9.

Homework due next class:

·         Read chapter 7, pages 246-272

Project, PART 2: Due next Thursday, 4/09/2020

·         Project overview, Airline, Basketball, Parish. Hand in a hard copy. NOTE: Hand in part 1 of your project (again) when you hand in part 2. Put all documents in the drop box.

Day #18. Tuesday, 3/31/2020

·         Video store rentals solution: part 1, part 2.

·         This is the example we would have gone through in class today: Classic DB example: Invoices

·         Invoice solution videos: part 1, part 2

Homework due Thursday

·         Medical clinic ER diagram. Use MySQL Workbench to create the ER diagram. Use Word for all else. Put files in the drop box.

·         Read chapter 6.

Day #17. Thursday, 3/26/2020

·         Due: Students, teachers, and classes. Solution.

·         ER Diagrams: Fine Woodworking Magazine Subscriptions (5.58 Kroenke12). Solution.

·         ER Diagrams: Traffic Citations (5.59 Kroenke12). Solution video.

·         ER Diagrams: Temporary Employment Company. Solution.

Homework due next class:

·         ER diagram: Video Store Rentals. Use MySQL Workbench to create the ER diagram. Use Word for everything else. Put both documents in the drop box.

Project, PART 2: Due 4/09/2020

·         Project overview, Airline, Basketball, Parish. Hand in a hard copy. NOTE: Hand in part 1 of your project (again) when you hand in part 2. Put all documents in the drop box.

Day #16. Tuesday, 3/24/2020: Chapter 5

·         Note: I had originally intended to spend another day on using Access, but we covered the basics the last time we met, and given the fact that we lost two classes last week, I think we can omit that.

·         Interesting tech: small computer

·         Interesting article: The importance of computer science fundamentals

·         More ER diagrams: Athletic Department. Solution video, part 1. Solution video, part 2.

Homework due next class:

·         ER diagram: Students, teachers, and classes. Use MySQL Workbench to create the ER diagram. Use Word for all else. Put both documents in the drop box.

Day #14. Thursday, 3/12/2020: Using Access, part 1 

·         Interesting link: Names that break web sites. SQL injection.

·         Using Access. Sports Files. Investment spreadsheet. National Bank Tables. Notes.

Project PART 1, due 3/19/2020:

·         Project overview, Airline, Basketball, Parish. Hand in a hard copy.

Day #13. Tuesday, 3/10/2020

·         25 most recommended programming books of all time.

·         Return tests.

·         Chapter 5: Intro to ER Diagrams. Notes.

·         MySQL Workbench: An ER diagram tool. Appendix E. Brief user guide.

·         Assign projects:

-         Airline: Erin, Christian, Tamas

-         Basketball: Feddy, Jake

-         Parish: Ben, Casey

Homework due next class:

·         Read Appendix A: Getting Started with Access.

Project PART 1, due 3/19/2020:

·         Project overview, Airline, Basketball, Parish. Hand in a hard copy.

Spring Break

Day #12. Thursday, 2/27/2020

·         Test #2: Chapters 3, 4. Dependencies and normalization.

Homework due Tuesday, March 10:

·         Read chapter 5, pages 155-168. Expect a quiz.

Day #11. Tuesday, 2/25/2020

·         Notes from Day 10.

·         Homework due today: functional dependencies #6 (Jobs, applicants) solution.

·         Database Design Using Normalization (chapter 4). Notes

·         Chapter 4 database files: Cape Codd, Equipment Repair, Student Activity Payment

·         Discuss auto license/title functional dependencies (#7)

·         NEXT CLASS: Test #2, over chapters 3 and 4. List of review terms.

Not an assignment. For review only:

·         Functional Dependencies #7. Solution.

Day #10. Thursday, 2/20/2020

·         Notes from Day #9

·         Review of normal forms, algorithm for normalizing a database

·         Go over Functional dependencies #3.

·         Functional dependencies #4. Do in class.

·         Functional dependencies #5. Do in class.

Homework due next class:

·         Functional dependencies #6. Hand in a hard copy.

Day #9. Tuesday, 2/18/2020

·         Notes from Day 8.

·         Go over functional dependencies #2 homework.

·         Today's notes.

·         Boyce-Codd Normal Form, 4th Normal Form. Normalization Examples

Homework due Thursday

·         Functional dependencies #3. Hand in a hard copy. Hint: 5 tables.

·         Read chapter 4. Expect a quiz.

Day #8. Thursday, 2/13/2020: Chapter 3, part 2

·         Notes from day 7.

·         History: first hard disk drive

·         Interesting tech: Apollo 11

·         More on normalizing a database.

·         Project file.

·         Normalization Examples

Homework due Thursday

·         Functional dependencies #2. Hand in a hard copy. Solution.

Day #7. Tuesday, 2/11/2020: Chapter 3, part 1

·         Go over tests

·         Normalizing a database, part 1

·         Project file

·         Fig 3-2, Relation definition, Fig 3-5, Fig 3-6, Fig 3-7, Fig 3-8, Fig 3-1-a, Page 107, Fig 3-1-b, Fig 3-1-c

Homework due next class

·         Finish reading chapter 3. Expect a quiz.

·         Functional dependencies #1. Turn in a hard copy. NO DROP BOX.

Day #6. Thursday, 2/06/2020: SQL Test

·         Interesting articles: C# more popular, Data analytics and football

·         Test #1, over chapter 2.

·         You will be expected to write SQL statements (open book).

·         Use this file for the test.

Homework due Thursday

·         Read chapter 3, pages 100-112 (up to Normal Forms). Expect a quiz.

Day #5. Tuesday, 2/04/2020: Access functions, SQL Review

·         Notes from Day 4.

·         List of Access functions

·         Notes/Review for today.

·         SQL workday:

·         Do question 2.63 (A-H) using the CH02-NDX-SQL-92.accdb database.

·         Do the questions over the Marcia's Dry Cleaning questions at the end of chapter 2 (A-Q) using the CH02-MarciasDryCleaning-SQL-92.accdb. NOTE: Marcia's Dry Cleaning question K should say "Sort" instead of "Group". List all fields. Compare your answers with the solution. Here is a document with all of the questions.

Homework

·         Study for a test over chapter 2. Be prepared to read SQL queries (and produce their output) and to write SQL queries. You will be expected to know how to read/write each type of query in chapter 2. You will be allowed to use your book. Writing queries will be hands-on with an Access database that will be provided. You will NOT be allowed to use the computer when reading queries, but you will be allowed to use the book.

Day #4. Thursday, 1/30/2020: Chapter 2: Joins

·         Chapter 2: Introduction to SQL, part 3: Joins

·         Notes from Day 3.

·         Today's files: CH02-Cape-Codd-SQL-92-Tables.accdb, Chapter 2 tables (in Word), Join sample database, Salesperson orders database

Homework due next class

·         Review Questions #2.40 through 2.50. NOTE: Use the linked document rather than the textbook. There are some minor changes and corrections. Continue to use the Chapter 2 Cape Codd database. When you save your queries, name them Q2-40 through Q2-50. What to hand in: a copy of the database in the drop box.

Day #3. Tuesday, 1/28/2020: Chapter 2

·         Chapter 2: Introduction to SQL, part 2.

·         Notes from Day 2.

·         Files: CH02-Cape-Codd-SQL-92-Tables.accdb, Chapter 2 tables (in Word)

Homework due next class

·         Review Questions #2.29 through 2.38, except 2.35. NOTE: Use the linked document rather than the textbook. There are some minor changes and corrections. Continue to use the Chapter 2 Cape Codd database. When you save your queries, name them Q2-29 through Q2-38. What to hand in: a copy of the database file in the drop box.

Day #2. Thursday, 1/23/2020: Chapter 2

·         Quiz!

·         Chapter 2: Introduction to SQL, part 1.

·         Files: CH02-Cape-Codd-SQL-92-Tables.accdb, Chapter 2 tables (in Word)

Homework due Next Class

·         Finish reading chapter 2 (pages 63-82). Expect a quiz.

·         Review Questions #2.17 through 2.28. Make a copy of the Chapter 2 Cape Codd database. When you save your queries, name them Q2-17 through Q2-28. Put a copy of the database file in the drop box. Note: If a field is included in a WHERE clause, include it as a selected field (after all of the other fields), too (even though he doesn't say that—it'll make it easier to check your work).

Day #1. Tuesday, 1/21/2020: Chapter 1

·         Notes

·         Interesting stuff: Jobs, jobs

·         Files: LakeView Equipment (XL), LakeView Equipment (DB), Movie Rentals (XL), Movie Rentals (DB), Student-Class-Grade (DB), COBOL

Homework due Next Class

·         Print, READ, and sign class policies statement.

·         Read chapter 1 and chapter 2 pages 31-63. Be prepared for a quiz.

·         Incompatible date formats. Hand in a hard copy at class time.

 

 

 

 

 

 

Day #15. Tuesday, 3/24/2020: Using Access, part 2

·         Using Access. Sports Files. Investment spreadsheet. National Bank Tables. Notes.

Project, PART 3: Due Tuesday, 4/21/2020

Day #26. Tuesday, 4/28/2020. Stored Procedures & Triggers in SQL Server (Chapter 10)

Day #27. Thursday, 4/30/2020: Chapter 12

·         Happy birthday WWW!

·         SQL quiz over joins. File. Airline.

·         Part 4 of project is due today

·         A VB XML reader

*****************************

Monday, 5/07/2020

 

 

Files below have lots of bad links that have not been fixed.

Homework due Thursday, 4/21/2016

 

12. Tuesday, 3/04/2014

Homework due Day #18

20. Tuesday, 4/08/2014

21. Thursday, 4/10/2014

25. Thursday, 4/24/2014

Assignment due Day #26

27. Friday, 2/12/2010

Project, part 3 of 4, due Wednesday, 2/10/2010 at 9:05

Homework due Monday, 2/08/2010

25. Monday, 2/08/2010

·         Chapter 9: Multi-user Databases. Notes

24. Friday, 2/05/2010

·         Chapter 8: more SQL for database redesign

Homework due Wednesday, 2/03/2010

23. Wednesday

·         Views, triggers, and stored procedures.

·         Chapter 8: SQL for database redesign

·         ViewRidgeGalleryTables.accdb, Employee.accdb

22. Monday

·         Return tests

·         Review outer joins. Sample file: SalesPersonOrdersTables.accdb

·         Views, triggers, and stored procedures. Sample file: ViewRidgeGalleryTables.accdb

21. Friday

20. Wednesday, 1/27/2010

Homework due Monday, 1/25/2010

 

 

 

Homework due Wednesday, 1/20/2010

17. Wednesday, 1/20/2010

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

·         SQL Server: Tables, queries, stored procedures, triggers

·         Create Table SQL

·         Stored procedure: Customer_Insert

·         Stored procedure: NewCustomerWithTransaction

·         Trigger: CheckForProblemAccount

 

 

 

Homework due Friday, 2/05/2010. DID NOT do in 2010. We had already done these in class using Access. Only reason to assign them would be to try them out with another database.