Logo courtesy Wordle.net

California State University Stanislaus

CS 4250: Database Management Systems

Spring 2022

2:00 - 3:15 pm, Tu Th, DBH 101 starting Feb 14. (via Zoom until Feb 14.) 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.

Prerequisite: CS 3100.

Announcements and Upcoming Events

28 Apr 2022	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 For a nice "optimistic locking techniques vs strict two-phase locking" comparison, 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.
2 Apr 2022	Here is one small chunk of the midterm you will take on Tuesday during class time.
10 Mar 2022	From 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.
2 Mar 2022	To help students practice SQL syntax, you can download SQLite to your own machine and use the command prompt interface to practice. Here are the files that will set up Engineer, Project and ProjectEngineers for you. EngineerSQLITE.txt, ProjectSQLITE.txt and ProjectEngineersSQLITE.txt createHW2tablesSQLITE.txt Place all four of the above files in a known location. Edit the commands in startSQLite.txt to replace my fake locations with your files' actual locations. Start the SQLite command line. Copy and paste your own commands from startSQLite.txt onto the command line. Start practicing! (To tidy up the "look" of your query output, you might want to type: .mode column .headers on .nullvalue NULL at the SQLite command line.) That will set up sample Engineer, Project, ProjectEngineers tables, with schemas like in Hwk 2. (Note: for simplification, no foreign keys are set up. And real data is likely to have many more duplicates in human name fields, which a good DBA would think about carefully when writing queries.) Basic sample data was generated at Mockaroo.com.
20 Jan 2022	Recommended listening: "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.
2 Feb 2022	Welcome to CS 4250! Daily Screening Forms, to be completed before coming to campus each (class) day.

Homeworks

Project Overview

	Points
Project Part 1	20
Project Part 2	25 + 6
Homework 1	30
Project Part 3	20
Homework 2	30
Mini-Homework	5
Project Part 4	10
Project Part 5	25
Homework 3	30
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

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.
• SQL for Web Nerds by Philip Greenspun, a free, on-line only database textbook with a focus on Oracle DBMSs
• SQLFiddle.com appears to be 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/
• XML Tutorial, courtesy w3schools.com
• XML Tutorial, courtesy zvon.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
• "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 introduce careers you hadn't thought of.
• "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

Fun News Items

• "Top 10 Data Science Jobs that will Rise to Prominence in 2022", (AnalyticsInsight.net, 18 Nov 2021)
• "The US government just launched a big push to fill cybersecurity jobs, with salaries to match", (ZDNet.com, 16 Nov 2021)
• "4 strategies for managing your job search anxiety" (Fast Company, 4 May 2021)
• "Systems Engineer, Web App Developer: Best Entry-Level Jobs in Tech?" (Dice.com, 27 April 2021)
• "The 10 Best States For Tech Job Openings", CRN.com, 15 Apr 2021.
• "LinkedIn: Top 15 In-Demand Jobs in 2021", 14 Jan 2021. (Scroll down. Keep scrolling.)
• A podcast episode to listen to: "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.
• "50 Technology Skills That Remote Employers Want", Dice.com, December 2020
• "The 5 top tech skills companies want in new hires right now", Fortune magazine, 6 Feb 2020.
• "Tech Professions Dominate Rankings of Best Jobs in the U.S.", IEEE Spectrum 'View from the Valley', 17 Jan 2020.
• "Cybersecurity Needs YOU" by CSU News, Oct 2019
• "Data Modeling for Analytical Data Warehouses," an interview with Michael Blaha (3/3/2012) ODBMS Industry Watch.
• "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.