Logo courtesy Wordle.net
California State University Stanislaus
CS 4250: Database Management Systems
Welcome to CS4250, an introduction to database management systems.
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
Final exam from Spring 2008.
The review questions and exercises at the back of the textbook chapters are
also a good source for questions similar to what might show up on the final.
Tuple formatting and what gets stored in indexes exercise notes - exercise is based on 8.4 in our textbook
The Engineer Guy opens up a hard drive.
Michael Stonebraker wins the 2014 Turing Award, "for fundamental contributions to the concepts and practices underlying modern database systems." Congratulations, Dr. Stonebraker!
||SQLFiddle.com appears to be a web site
that allows uers to "fiddle around" with SQL. Thanks to Mr. Pena for finding it.
SQL samples generated in midterm preparation today:
- select * from Sailors LEFT OUTER JOIN Reserves ON Sailors.sid = Reserves.sid order by age;
- select max(S.age), B.color from Reserves R, Sailors S, Boats B where S.sid = R.sid AND R.bid = B.bid Group By B.color;
(oldest age for a sailor who has a reserved boats of each possible color)
- select * from Sailors order by age;
- insert into Sailors values (35, 'PinkBeard', 1, 75);
insert into Sailors values (34, 'Gulliver', 5, 75);
- select age, sname from Sailors where age = (select max(age) from Sailors);
(should return 2-3 tuples, after we insert old Gulliver and PinkBeard)
- select * from Sailors where sname IN (select sname from Sailors where sname LIKE '%r');
- Sample many-to-many creation, Sailors having diseases and diseases infecting many sailors:
create table Diseases (name text, primary key (name));
create table Sick (sid smallint, name text, primary key(sid, name), foreign key(sid) references Sailors, foreign key (name) references Diseases);
insert into Diseases values ('scurvy');
insert into Diseases values ('flu');
insert into Diseases values ('Ebola');
insert into Diseases values ('bubonic plague');
insert into Diseases values ('smallpox');
insert into Diseases values ('chicken pox');
- Sample many-to-one creation, Sailors having peg legs but each peg leg belonging to at most one sailor:
create table Pegleg (id smallint, primary key (id));
create table Wears (sid smallint, id smallint, primary key(id), foreign key (id) references Pegleg, foreign key (sid) references Sailors);
Midterm exam from Spring 2006 The order
of topics was a little different in Spring 2006 than it is this year, but the old
exam may help you get a feel for the types of questions I might ask.
The rest of Homework 1 has been put on the Homework 1 page. Hit "reload" in
your web browser if you do not see a third question.
Added to the class syllabus today:
Strong evidence from multiple sources that a student did not participate
significantly in multiple parts of the group project is grounds to assign
a zero to up to 15% of that student's grade.
Welcome to CS 4250! Please sign up for the class mailing list.
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 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 class textbook and the Garcia-Molina/Ullman/Widom textbook are both
on reserve at the university library.
- IBM's DB2, etc
- Microsoft SQL Server
- SQL for Web Nerds by Philip
Greenspun, a free, on-line only database textbook with a focus on Oracle DBMSs
- JDBC Tutorial,
courtesy Jeff Ullman
- JDBC Tutorial,
- XML Tutorial, courtesy w3schools.com, and XML Tutorial, courtesy zvon.com
- XML DTDs Tutorial,
courtesy w3schools.com, and XML Tutorial, courtesy zvon.com
- XML Schema Tutorial,
- Phlonx's Third Normal Form Tutorial,
with numerous illustrative diagrams
- Lynda.com's "Foundations of Programming: Databases" course --
all videos. You would need to authenticate yourself as a CSU Stanislaus student to
view the videos.
Fun News Items
Topic Ideas for End of Semester
- Database security
- SQL vs NoSQL DBMS debate
- Troubleshooting DBMSs that are not performing well
- XML, XQuery, etc
- Data mining, wireless sensor databases, distributed databases...