Advanced database concepts

Question

I have agreed to become a teacher's assistant in a database course and have also been asked to look into changes that can be made to the course.

The course is called "Advanced Databases", but its name is a bit deceptive. The students are not computer science majors but some kind of "IT management"-students and thus lack much of the underlying concepts that computer science major needs (higher level math, etc).

They have already had an introductory course in databases and should already know the following:

• ER-modeling
• Normalization
• SQL

So, my question to you all is: What additional concepts could this course teach?

The course is primarily aimed for the "advanced use" of databases, so I gladly accept answers with concepts that are not exactly advanced databse concepts, but are related to some kind of "advanced use of databases". Simple data mining or using external web services like Amazon have been discussed as possible subjects.

The course will contain a project that requires the student to make some kind of frontend (in PHP or whatever) to their database (MySQL), so they will have to do some programming.

Edit: Keep in mind that the students are not computer science students, so stuff that are very high tech or low level like query plans, query optimizations, etc. are not in the scope of this course.

Answer 1

Reading/understanding query plans

If they already know modeling, normalization and SQL, then understanding at least the basics of optimization is the best next step. Without that, all their other skills are useless. After those first three topics, that's where the tires meet the road.

Answer 2

Security. thats what we really need. We need new folk to not make infection friendly applications

Performance, you need to explain to them about the costs of operations, and when some things that seem useful might bring your app to a crawl such as certain joining operations (you should also explain why it's hard to make good algorithms for those operations too, but no need to bring in the math, just use stuff like bees and flowers)

Answer 3

OLTP vs OLAP

Answer 4

How indexes work and when they do + don't help

Answer 5

XML Databases were a feature of the advanced databases course I took. May be out of flavour now though?

Answer 6

teach them set based thinking. This is the most cruical thing to learn when doing any kind of work with databases.

Answer 7

Stored procedures.

Answer 8

Optimization of queries. That's pretty much all the management cares about in terms of our DBMS around here. Either the DB is working well, or it's working slowly due to a query that's joining on an unindexed column, or our non-standard database settings are causing the execution plan to ignore a perfectly fine index. THOSE are the things which really cause managers to lose sleep (due to their managers calling them at 3am), and then call the DBA's causing them to lose sleep.

Edit: Also, how to avoid risk. Need to re-build an index on a table with a lot of I/O? Is it a production server? Do it during a maintenance window. Changing an OS level TCP stack setting? Don't do it on a production server. Test it on a worthless machine, and perform it on production during a maintenance window. The other part of avoiding risk is having a running backup. Backing up your data files is good, but having redundancy is better. We have Dataguard (Oracle) running to a local machine and a machine offsite so we can instantly switch over to one when this one goes down. And it does go down, and it will go down again in the future. When it goes down the first thing that happens is that my manager, his manager, and his manager's manager all crowd around the DBA's cube asking when the site will be back up. With our dataguard setup, he asks for the manager's ok to switchover and voila, done. Site's back up.

Answer 9

Indexing strategies for optimizing queries.

Answer 10

How about information about building a data warehouse such as denormalization (intentional), fact tables, and common dimensions?

Answer 11

• Data-Mining

• Replication

• Will transactions have been covered already? v. important

Answer 12

I like your concept of teaching them to build a front-end. That is obviously key. How about some coverage of object-relational mapping: what it is, why it's used, and one or more popular frameworks.

Answer 13

• Main differences of known RDBMS (Oracle, Sybase, DB2, SQL Server, open-source, ...)
• Costs involved in RDBMS management

Answer 14

Defining Primary/Foreign keys in order to maintain referential integrity. Cascading deletes, etc.

Answer 15

Possible areas to cover might be:

1. Different types of schemas (star schema, etc.) if they weren't all covered in the basics course
2. Data warehousing, cubes, etc.
3. ETL, merging data, replication
4. Enterprise architecture from a database perspective

Answer 16

• Backup
• Clustering
• Co location
• Load balancing
• Refactoring

Answer 17

Many of the DBAs that I encounter have little knowledge of application development. Therefore they overlook such issues as schema version control, test data, and data versioning. You might also consider discussing data modeling (for example, http://www.agiledata.org/essays/dataModeling101.html). How about hierarchical models (ie, parent-child relationships), business intelligence data mining, or data warehousing? How about creating virtual machines to enable more testing?

Answer 18

Concurrency, locking, and read consistency

Answer 19

If there are people I would work with in the future I would like them to have respect for the relational data model and how it can model business rules and business constraints correctly.

Try to have them leaving the course with a firm belief that a good data model and rigorous constraints are crucial for reaping the benefits of a dbms.

Too many people view the database as just another flatfile api for data persistance.