Logo courtesy Wordle.net

California State University Stanislaus

CS 4250: Database Management Systems

Fall 2024

10:00 - 10:50 am, M W F, B 203. Dr. Megan Thomas

[Syllabus ]        [Announcements]        [Calendar/Lecture Notes]        [Homeworks]        [Resources]        [Fun News]

Welcome to CS4250, an introduction to database management systems. Topics include:

Modeling, development, and implementation of database systems using storage structures, data definition languages, and data manipulation languages for the relational approach to database management. Database integrity and security problems. Historical development of database systems. (Academic Catalog description.)

Prerequisite: CS 3100.

Announcements and Upcoming Events

17 December 2024	"10 most in-demand enterprise IT skills" from CIO.com (10 Dec 2024). Notice that two of the "most in-demand skills" are closely tied to knowledge of DBMSs -- Oracle and Microsoft SQL Server. "4 highest-paying technology jobs of 2024" from CIOdive.com (5 Dec 2024).
15 December 2024	The final exam will, mostly, re-use the sample schema from the midterm. Minor changes were made, and this small hint includes the changes.
2 December 2024	dragon.csustan.edu was down -- the web server was down, and that's how people reach PhPMyAdmin -- for a while today. As the timing on that is truly unfortunate for Part 5, I've extended the Part 5 deadline to Wednesday. For any student group that makes the original deadline in spite of unplanned obstacles, I am likely to grant some extra credit.
14 November 2024	"Engineering the First Fitbit: The Inside Story. They had a billion-dollar idea - and a lot to learn about hardware.", IEEE Spectrum, September 2024. A good read if you want to be in a start-up or work with consumer devices someday. (May only be accessible from on campus, or on the campus VPN.)
5 November 2024	dragon.csustan.edu - tip for when you first log in: When you first log in to dragon.csustan.edu, which you will do as part of a future project part, you may be given the sh shell. To start the (easier to use) bash shell, just type 'bash' at the command line. (Just the 4 letters; don't type the quotation marks.)
23 October 2024	"Video Friday: Mobile Robot Upgrades", IEEE Spectrum, 18 Oct 2024. (You might need to be on campus to visit this link; the library pays for access to Spectrum material, for the campus.)
22 October 2024	"IT hiring roars back after monthslong slump", CIODive.com, 4 Oct 2024.
6 October 2024	Here is one small chunk of the midterm you will take on Wednesday during class time.
25 Sept 2024	For our in-class exercise work this week: Schema: Earthquake (qname, qdate, latitude, longitude, magnitude, fault) Damage(cityID, qname, cost, liveslost) Cities(cID, cname, latitude, longitude, population) [city names may not be unique] Questions (in SQL, and relational algebra where possible): Find the names and dates of earthquakes whose magnitude was greater than 7.0. Find the average population of all the cities whose names start with "San". List the lives lost, quake names and city names for all quakes that occurred before 1960. List the city identifiers and total cost, per city, of all quakes with magnitude greater than 7.0. List the average magnitude of quakes for each fault (grouped by fault). List all earthquakes and, where applicable, also list city IDs and lives lost in that city for quakes that affected cities. List the names of cities whose populations are greater than the average city population.
16 Sept 2024	Sample tables for classroom exercises: CREATE TABLE Books(title char(40), lastname CHAR(40), firstname CHAR(40), pubyear INTEGER, bookID INTEGER PRIMARY KEY) CREATE TABLE AwardWinners (bookID INTEGER, year INTEGER, award CHAR(60), authorage INTEGER, PRIMARY KEY (bookID, award, year)) CREATE TABLE Awards (awardname char(60), primary key (awardname)) (Note that, to fit on a PPT slide, foreign key key declarations were left out)
21 August 2024	Welcome to CS 4250!

Homeworks

Project Overview

	Points
Project Part 1	20
Project Part 2	25 + 6
Homework 1	30
Project Part 3	20
Homework 2	30
Project Part 4	10
Homework 3	30
Project Part 5	25
Project Part 6	12
Homework 4	35
Project Part 7	70

Books at the CSU Stanislaus Library

• Database Systems: The Complete Book by Garcia-Molina, Ullman and Widom, three extremely well-respected database researchers and professors at Stanford University.
• Database Management Systems by Raghu Ramakrishnan and Johannes Gherke, excellent, systems-oriented introduction to the field of database management.
• Database Tuning by Dennis Sasha and Philippe Bonet. A clear presentation of how database design decisions impact database performance. Also discusses IBM, Oracle and Microsoft systems and how design choices can impact them.
• Readings in Database Systems by M. Stonebraker. A collection of influential papers in DBMS research history.
• Computer Science & Information Systems from the CSU Stanislaus Library -- easiest link to reach the Safari TechBooks collection, where many books about SQL, etc, can be found. (You'll need your CSU Stanislaus student credentials to log in.)

The Ramakrishnan and Garcia-Molina/Ullman/Widom textbooks are both available at the university library.

Campus Resources

• Career and Professional Development Center --- and Career Center Event Schedule
• CSU Stanislaus Dreamers: Resources for AB540 and undocumented students
• Disability Resource Services
• Useful campus resource links, courtesy the Learning Commons



• Instructions on how to install a VPN for our campus network, which would let you access some CS resources from off-campus. See "How do I get access and start using VPN?" here:
  Information Security, CSU Stanislaus or skip straight to:
  Windows: Global Protect VPN Guide - Windows or
  Mac: Global Protect VPN Guide - Mac.

DBMS Resources

• IBM's DB2, etc
• MySQL
• Oracle
• PostgreSQL
• Microsoft SQL Server
• SQLite
  • 'cd' (Unix/Mac) or 'chdir' (Windows/ Command Prompt) your command line window into whatever folder you put the files you downloaded and unzipped, and type ./sqlite3 (Unix/Mac) or .\sqlite3 (Windows) and the SQLite command line tool should start up.
  • I've put the start-up file of SQL commands here. I usually copy-and-paste the ch5-demo.sql file's contents into the sqlite3 command prompt. (There is an import command, but it has occasionally behaved oddly for me.)
  • Basic instructions for the command line SQLite are here.
• SQLFiddle.com -- a web site that allows uers to "fiddle around" with SQL.
• JDBC Tutorial, courtesy Jeff Ullman
• JDBC Tutorial, courtesy Oracle
• MySQL Tutorial - pure tutorial web site
• Execute PHP MySQL On-line, a PHP testing facility provided by http://www.tutorialspoint.com/
• Phlonx's Third Normal Form Tutorial, with numerous illustrative diagrams
• Lynda.com's databases-related courses -- all videos. You would need to authenticate yourself as a CSU Stanislaus student to view the videos.
• XKCD on SQL
• "Hard drive teardown", EngineerGuy video
• Podcast: "Where the Internet Lives", 2020-2021. A Google-centric view into the insides of data centers, and interviews with assorted people who work at them. May give you insights into possible future careers.
• "Episode 485: Howard Chu on B+tree Data Structure in Depth", from the Software Engineering Radio podcast -- a one hour introduction to the most popular data structure in database systems
• For more information on ...
  • ... the ARIES recovery algorithm, the paper to read is "ARIES: a transaction recovery method supporting fine-granularity locking and partial rollbacks using write-ahead logging", which someone already put online on the Stanford web site.
    C. Mohan, Don Haderle, Bruce Lindsay, Hamid Pirahesh, and Peter Schwarz. 1992. ARIES: a transaction recovery method supporting fine-granularity locking and partial rollbacks using write-ahead logging. ACM Trans. Database Syst. 17, 1 (March 1992), 94-162. https://doi.org/10.1145/128765.128770
  • ... "optimistic locking techniques vs strict two-phase locking", the paper to read is "Concurrency control performance modeling: alternatives and implications", which someone put online on the UCBerkeley web site.
    Rakesh Agrawal, Michael J. Carey, and Miron Livny. 1987. Concurrency control performance modeling: alternatives and implications. ACM Trans. Database Syst. 12, 4 (Dec. 1987), 609-654. https://doi.org/10.1145/32204.32220
  • I know of a nice introduction to ARIES written by Mike Franklin but, tragically, it was published in a book that we have no access to.

Fun News Articles and Podcasts

• "What Developer Specializations Pay the Most?", Dice.com, 2024 Aug 14.
• "Where America's STEM and R&D jobs are and what pays the most", R&D World, 2024 June 5.
• "How many jobs are available in technology in the US?" (Computerworld, 10 Mar 2023)
• "Best Tech Careers for 2023" (Baselinemag.com, 15 Mar 2023)
• "IT jobs in 2023: Look before you leap" (Feb 21, 2023, InfoWorld)
• "10 Hardest IT Jobs to Fill" (Mar 1, 2023, InformationWeek)
• "The top 10 most-regretted college majors - and the degrees graduates wish they had pursued instead", CNBC.com, 12 Nov 2022
• "Developer jobs: Nearly a third of top tech roles remain empty, say recruiters: Despite hiring freezes and layoffs, companies of all sizes need software professionals - and there simply aren't enough to go around", ZDNet, 20 Oct 2022.
• Podcast episode: "Episode 454: Thomas Richter on Postgres as an OLAP database" (IEEE Software Engineering Radio, 8 Apr 2021). Listen to this towards the end of a semester, when you will know more of the DBMS vocabulary.
• "Under the Hood at Google and Facebook: A peek at the data centers, servers, and software that keep us feeling connected" IEEE Spectrum, June 2011.
• Database Normalization for the Real World: nice article walking one through the steps of database normalization, with larger- than- can- fit- on- a- PowerPoint- slide relation examples.