Logo courtesy Wordclouds.com

California State University Stanislaus

CS 4250: Database Management Systems

Fall 2019

3:00 - 3:50 pm, M W F, DBH 104, 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

12/15/2019	The final exam will reuse the sample schema used in the midterm. Here is the chunk of the midterm (and final).
11/25/2019	"SQL, Java Top List of Most In-Demand Tech Skills", IEEE Job Site, Nov. 2019.
10/13/2019	Here is one small chunk of the midterm you will take on Friday.
10/8/2019	To help students practice SQL syntax, you may log in (using SSH or a similar tool) to hopper.csustan.edu and use your local MySQL account to try SQL queries at the MySQL command line. After you log in to hopper, then start the MySQL command line (#2 on the account paper you are given) copy and paste the text from createHW2tables.txt into the MySQL prompt then download and edit loadHW2data.txt so that it refers to whatever location you plan to put the files of sample data (you may need to edit on your local machine, then copy the file onto hopper.csustan.edu put doctor.txt, patient.txt, department.txt at the location you specified in loadHW2data.txt then copy and paste the text from loadHW2data.txt into the MySQL prompt That will set up sample Doctor, Patient and Department 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.) If you would prefer to download SQLite to your own machine and use the command prompt interface to practice, here are the files that will set up Patient, Doctor and Department for you. departmentSQLITE.txt, doctorSQLITE.txt and patientSQLITE.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.)
10/7/2019	"Cybersecurity Needs YOU" by CSU News, Oct 2019
9/27/2019	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(10), authorage INTEGER, PRIMARY KEY (bookID, award)) Let's write some queries... Find all authors (full names) of books. Will distinct change the output of above query? How about authors whose names (first or last) start with H? How about authors (full names) who have won a Hugo or a Pulitzer? (Authors who've won both?)
9/20/2019	Mini-homework is just relational algebra. There was a typo, referring to also completing the problems in SQL. I have removed the typo.
9/8/2019	Extra hours for Part 2 ER diagrams. I will be in my office from approximately 11:15 to 12:45pm on Monday, Sept 9. Since Monday is otherwise a bunch of meetings, I am extending the Part 2 Review 2 deadline to Tuesday, 9/10. I will be in my office from about 12:30 to 5pm on Tuesday. Stop by to show me an ER diagram draft and get feedback. (If my plans change, I'll update this list.)
9/4/2019	Extra hours for Part 2 ER diagrams. I will be in my office from approximately 12:30 to 5pm on Thursday, Sept 5. Also from 9:30 am to 11am, 12:15-12:45, 2-2:45 and 3-4 or so on Friday, Sept 6. Stop by to show me an ER diagram draft before the deadline for the Review 1 deadline. (If my plans change, I'll update this list.)
8/26/2019	Career Center events for Fall 2019 - resume help, workshops on preparing for job interviews, and a Career Fair on Sept 25.
8/23/2019	"Where the Engineering Jobs Are in 2019", IEEE Job Site, 1 May 2019 and "Tech Workers Are Finding Jobs Outside of Traditional Tech", IEEE Job Site, 19 July 2019
8/23/2019	Welcome to CS 4250!

Homeworks

Project Overview

	Points
Project Part 1	20
Project Part 2	25 + 4
Homework 1	30
Project Part 3	20
Mini-Homework 1	7
Homework 2	30
Project Part 4	10
Homework 3	30
Project Part 5	25
Homework 4	35
Project Part 6	12
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.
• MySQL and mSQL by Randy Yarger.
• MySQL by Paul DuBois (electronic).
• Readings in Database Systems by M. Stonebraker. A collection of influential papers in DBMS research history.

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

Campus Resources

• Career and Professional Development Center
• 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

Fun News Items

• "How To Get Your Data Scientist Career Started", Forbes.com, Apr. 14, 2019
• "Who Hired A Lot in 2018?" by IEEE Job Site - what companies made the most tech job postings in 2018?
• "Seven of the Coolest-and Best-Paying-Jobs in Emerging Tech" (eWeek.com, Feb 2019)
• "Why data scientist is the most promising job of 2019" TechRepublic (and LinkedIn), Jan 10, 2019
• "These Bay Area companies are 'hiring like crazy' in 2019, according to Glassdoor", SFGate.com, Jan 2, 2019
• One reason why you should be doing backups: "City Pays $2K in Ransomware, Stirs 'Never Pay' Debate" (threatpost.com, Oct 23, 2018)
• LinkedIn August 2018 Workforce Report -- what skills are employers looking for? How is hiring going? Interesting quotation: "Demand for data scientists is off the charts"
• "Looking for a tech job? These 8 industries pay the most" (TechRepublic, Feb 2018) (Beware annoying auto-play video at the top of the page.)
• "https://www.fastcompany.com/40420383/i-review-thousands-of-resumes-heres-what-im-really-looking-for" (Fast Company, May 2017)
• "Software Engineering Jobs Dominate List of Best Careers for 2017" (IEEE Spectrum, 22 March 2017)
• "Data Scientist, DevOps Engineer and DBA Among Top IT Jobs for 2017" (Datamation.com, Jan 2017)
• "Data Center Love: Tech Chiefs Show Off the Gear That Keeps Everything Humming" by CIO Journal Staff, of the Wall Street Journal, on Dec. 31, 2015.
• "Top secret Visa data center banks on security, even has moat", (USA Today, 3/2012) Security guards, power back-ups, earthquake and hurricane protection... credit card companies take protecting data seriously.
• "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.