Detailed schedule

Final presentations

Random order of final presentations:

1. Son
2. Alyssa
3. Katie
4. Evan
5. William
6. Billy+Leo
7. Pamela
8. Han
9. Sophia
10. Khanh
11. John+Leah
12. Amir
13. Zeki

Class 28

1. Review of course learning objectives
2. Complete course evaluations if you haven’t already done so.
3. free time to consult on final projects

Class 27

1. Informal discussion of NoSQL databases (Ch 11 of PDBM), based on abbreviated version of the textbook authors’ Chapter 11 slides: pdbm-ch11-abbrev.pptx
2. free time to consult on final projects

Class 26

Meet at the HUB Social Hall for the Civic Engagement poster session, 1:30pm. Browse posters (especially computer science senior seminar projects) until 2 PM, then return to Tome for regular class (database security demo).

Database security demo (demonstrates a security flaw known as SQL injection):

• visit http://localhost/bank.php, put any amount into the ‘Amount to deposit’ box, and this exact string into the ‘Account number’ box: 111' or 'xyz'='xyz
  • what happened and why?
• install the files bank-insecure.php and depositMoney-insecure.php into xampp/htdocs
• visit http://localhost/bank-insecure.php, put any amount into the ‘Amount to deposit’ box, and this exact string into the ‘Account number’ box: 111; drop table accounts;
  • what happened and why?

Class 25

1. Course evaluations.
2. exam discussion
3. free time to consult on final projects

Class 24

Midterm exam 2.

Class 23

1. PHP demo (optional info for final project)
2. exam review: exam review whiteboard

Class 22

Discussion of Sweeney (1997). Possible relevant link:

• Netflix Cancels Contest After Concerns Are Raised About Privacy (NYtimes, March 2010)
• Cynthia Dwork: How to Force Our Machines to Play Fair on YouTube. Professor Dwork is one of the inventors of differential privacy.
• Differential privacy on Wikipedia

Class 21

Topics for today:

1. Quickly cover write ahead logging, two-phase commit, and two-phase locking
   • see recovery-example.docx and 2-phase-locking.pdf
2. Finish understanding concurrency problems, continuing from the previous class meeting
   • see whiteboard notes from last time: concurrency-whiteboard.png, concurrency-whiteboard.pptx
   • see concurrency-examples.pdf - we did not have time to go over this in class, but these examples are similar to the homework questions and exam questions, so I do recommend studying them.

Notes are now also available from the online COMP378 OneNote notebook.

Class 20

Main topic for today: database concurrency and locking

• whiteboard notes: concurrency-whiteboard.png, concurrency-whiteboard.pptx
• Java files:
  • QueryExecutor.java
  • SlowTransfer.java
  • TransactionDemo.java
  • WithdrawMoney.java
  • Wait.java
• Bank database: bank.sql

There is a fun minilab that goes with this topic, but we do not have time to work on it in class. The instructor will demonstrate most of the programs, but you are encouraged to try them on your own also. Try the version that does not have accompanying explanations first, then look at the version that includes explanations:

• Transaction and locking minilab:
  • without explanations
  • with explanations

Classes 18 and 19

Research paper presentations

Class 17

Classic paper discussion of Brin \& Page (1998)

Class 16

Main topic for today: query optimization and join algorithms

• whiteboard notes: join-algs-whiteboard.svg
• handout on join algorithms:
  • as Excel workbook (solutions are on the second sheet of this workbook)
  • as PDF

Class 15

Results of the midsemester feedback are available.

Main topic for today: B-trees.

• Visualization by David Galles at UCSF. Set maximum degree to 5. This corresponds to what we call an order 2/4 tree.
• Handout: btree-handout.docx
  • handout solution: btree-handout-solution.pptx
• Whiteboard notes:
  • Part 1
  • Part 2

Class 14

1. Please take the midsemester survey.
2. Exam discussion
3. Overview of how to read a research paper:
   1. Read abstract, introduction, and conclusion.
   2. Examine figures and captions of figures. Try to understand the main ideas and results presented.
   3. Read the main body of the paper, skipping any technical material that is too difficult to understand.
4. Meetings with student teams for RP assignents as needed

Class 13

Main topic for today: JDBC. See the Using JDBC web page (also available from a direct link on the main course webpage).

In-class activities:

1. Quick overview of relevant slides from textbook materials: slides 30-40 from Chapter 15 Database APIs.ppt
2. Interactive minilab: implement a Java program that can access the wine database. Follow the instructions on the Using JDBC web page.
3. Remainder of time is available to work on assignments RP1 and/or CC4.

Class 12

The take-home exam begins at the start of class today. Please contact the instructor via email or Teams with any questions.

Class 11

Exam review notes: exam-review.pptx

Class 10

Main topic for today: defining indexes and views.

Most of the class session will be devoted to a lab on these topics: views-and-indexes-lab.docx

The lab requires use of the Southwind database.

Class 9

Main topic for today: SQL statements exists, union, intersect, except, insert into, delete from, update, alter table

Notes:

1. intersect and except are not available in MySQL. You should be aware of these but we will not use them.
2. insert, delete, and update are good examples of transactions. By default, in MySQL, any single statement will be treated as a transaction.
3. This is a good time to start using the Format button in phpMyAdmin. It will make your SQL much easier to read.
4. It’s also a good time to start commenting your SQL code. Lines beginning with two dashes (--) are treated as comments.

The class will consist of some demos of the above SQL statements followed by free lab time for working on homework assignments and asking questions.

Class 8

Main topic for today: database joins

• Whiteboard notes: 08-joins-whiteboard.svg
• Example databases:
  • CarlisleMenuItems.sql
  • CarlisleMenuItemB.sql

Class 7

Most of this class will be devoted to an ungraded but required lab activity to learn about creating and altering database tables within phpMyAdmin:

• table-creation-lab.docx

If you don’t have access to XAMPP, a great way to practice with phpMyAdmin is to use the online demo server at phpmyadmin.net. Note that this is a public server so your work can be viewed, altered, or deleted by others.

Class 6

Warmup exercise for functional dependencies and normalization:

• review-func-dep.xlsx
• corresponding database: carlislefood.sql

Main topic for today: converting ER model to relational model.

• textbook slides for chapter 6 again; we go over slides 49-62 and 74-77 in class.

Class 5

Please take the quiz on relations and keys. (Does not affect your grade, but demonstrates your understanding.)

Announcement: important departmental event on Thursday evening – fireside chat with Reshma Saujani, founder and CEO of Girls Who Code. 7pm in Stern Great Room, or via Zoom. To celebrate the importance and relevance of this event to our discipline, I am offering some extra credit for attending: if you attend the fireside chat, you can top up any Core Content homework assignment of your choice to 100% (provided you made a reasonable attempt at the assignment).

Main topic for today: database normalization

• Whiteboard notes: 05-normalization.svg
• textbook slides for chapter 6 again

Class 4

Main topic for today: relations and keys.

• Whiteboard notes: relations-and-keys.svg
• textbook slides for chapter 6

Class 3

Preliminary discussion of the mathematical motion of a relation. Whiteboard notes provided: relation-overview.svg

Main topic for today: the EER model (enhanced ER model).

• We continue with textbook slides for chapter 3.
  • Note the methodology on slide 45. This will be useful for homework.

Class 2

Key concepts from chapters 1 and 2:

• transaction: see textbook section 1.5.6
• ACID properties of transactions: see textbook section 1.5.6
• Different types of database management systems (DBMS): hierarchical, network, relational, object-oriented, XML, no-SQL. See textbook section 2.2.1. In this course, we focus almost exclusively on the relational approach, which is dominant in practice.

Main topic for today: the ER model.

• See textbook slides for chapter 3.

SQL topics: select, from, distinct, as, where, and, between, in, like, is null. See 7.3.1.1.

Class 1

• Overview of the course.
• Discussion of: why do we need databases?
• Instructions on XAMPP installation:
  • The recommended database software for this course is the XAMPP package. This can be downloaded and installed on Windows, Linux, and MacOS from the XAMPP web page at apachefriends.org.
  • If desired, you can use the iMacs in Tome with XAMPP. You will need to create a virtual machine on one of the iMacs then use XAMPP within that virtual machine. A virtual machine image is provided to help you with this. Details:
    • Download the VM image file, comp378VM.ova
    • Open the VirtualBox app, then choose File | Import Appliance and pick comp378VM.ova
    • The username and password for this virtual machine are available on Moodle.
• accessing phpMyAdmin:
  • on Windows
    • Run XAMPP control panel as administrator
    • Start Apache and MySQL
    • Click on Admin for MySQL
  • on Ubuntu
    • open Terminal, enter sudo /opt/lampp/manager-linux-x64.run
    • on Manage Servers tab, choose Start All
    • open FireFox, visit http://localhost, click on phpMyAdmin (top right)
• importing the wine database:
  • download wine.sql
  • in phpMyAdmin:
    • click the Home icon, then New
    • enter wine as database name, click Create
    • click Import | Choose File, then select wine.sql
    • click Go (bottom right)
• querying the wine database (Do this in your own phpMyAdmin or at the textbook online playground):
  • click on SQL
  • enter SELECT * FROM supplier, click Go (bottom right)
  • for a more interesting example, try SELECT * FROM supplier WHERE supcity='San Francisco'
• chapter 1 textbook slides: especially slides 4-13, 17-18, 21

---

Last modified: Tue May 10 17:43:54 UTC 2022 by jmac.