University course "Databases". About what?

Question

Database

From the height of the experience gained, what would you like to see in due time in the university course "Databases"?
What topics can't be skipped?

Answer 1

I teach a database course at the university, one semester of the second year, 12 lectures, a lot of lab and term paper. The sequence of submission of material is approximately the following:
0. Tales that the database is the bread of the programmer, and a description of all requests received over the past week to find an oracle programmer for the bank for NNN thousand rubles a month :)
1. Introduction, a general description of the problem, a bit stories. (0.5 lectures)
2. Tabular representation of data immediately with an example: we draw a huge denormalized table with sold tickets to the cinema and start thinking about what is wrong in it and how to fix it. Students themselves name the words "duplication", "inconsistency", etc., and they themselves figure out how to solve these problems (dividing into tables); the concepts of primary key and foreign are born (so far informally). This is the most important section, I think - here we learn how to design relational databases using the example of a non-trivial movie theater database. It also discusses many conceptual issues of the database on the example of the same cinema. (1-1.5 lectures)
3. Relational data model: formal definitions, designations, and definitions of primary and foreign keys and functional dependencies; NULL and three-valued logic (1 lecture) (1 lecture)
4. Normal forms 1-3 and BCNF, formal definitions and normalization procedure. (1 lecture)
5. Transactions and concurrency (description of problems, possible approaches to solution). (0.5 lectures)
6. SQL language: DDL, DML (insert-update-delete), transactions. (1 lecture)
7. SQL language: select: from, where, select. (0.5 lectures)
8. SQL language: join, grouping, little things (union-minus, exist, outer join). (1 lecture)
9. SQL language: subqueries (in from, in where, in select). (1 lecture)
10. SQL language: complex subqueries, solving problems of sampling relational data (1-1.5 lectures)
11. Procedural extensions: procedures, functions, triggers (0.5 lectures)
12. Indexing: what is an index, how and why it is works (0.5 lectures)
13. Other Oracle schema objects: sequences (and the uniqueness problem in general), overviews, snapshots, temporary tables, etc. (0.5 lectures)
14. Infological modeling (0.5 lectures)
15. Non-relational databases (1 lecture)

Answer 2

I have only 1 thought, Lord, not OpenOfficeBayz.
We need modern bases, base construction theory, normal forms, that's all. I would like to hear about the real applications of bases, for example, when they deliberately go for denormalization. In short, about ORM and non-relational databases. Best practice in designing database schemas.

Answer 3

See what is in the course "Database" by Ilya Teterin . They are very good in my opinion.

Answer 4

We were given the construction of queries in mySQL, quite satisfied, because there was 1 pair per week. A little, but quite capacious.
Perhaps I would like to expand my horizons a little more—i.e. be aware of the canonical differences between MySQL, MS SQL, Oracle…
I would also like to stick triggers, transactions, procedures into the course…

Answer 5

A very dumb and stupid question.
There is a state standard on this subject. There is a detailed list of topics that are necessary for studying. The list is quite detailed and thoughtful, tk. it was approved by more competent people. Whether you like it or not, you must follow it.
Another thing is the practical part of this course - you have the opportunity to choose a DBMS.

Answer 6

I recommend using only OpenSource DBMS for the course.
The basis, of course, should be Relational Databases. Relational algebra, SQL, trees and index building, organization of data storage on disks.
Focus more on how theory helps solve practical problems. Decomposition can be performed with an eye to improving the performance of data sampling, or maybe to optimize the amount of disk space occupied.
Next… Clustering technologies: replication, sharding, etc. Apache Cassandra is a good tool, it’s really NoSQL… It’s
necessary to give material on assessing the complexity and resource intensity of queries, taking into account indexes and without them.
After that, switch to NoSQL and key-value storages, map/reduce queries, and so on... This should be mandatory these days. It is better to give it at the end. By this time, all the necessary theory will already be known to them ...
It also makes sense to give Document-oriented databases and repositories of semi-structured data and selections for them (sometimes search engines use).
Don't get fooled by Oracle. I understand that Oracle promises free servers and training materials. But:
1) you have to splurge on iron. The system requirements are very, very big ...
2) When delivering Oracle, the university will have to sign a very enslaving agreement that limits your options. Well, as long as they don't make you wear Oracle briefs. T-shirts and other marketing tinsel in a good household will find use. But they will almost certainly prohibit the use of databases from other manufacturers and open-source DBMS in the educational process ... Free cheese, as they say ...
3) You will deprive your students of the opportunity to practice at home. The Express Edition and Personal Edition are severely curtailed. For example, you will not assemble clusters on them ... Open-Source is preferable in many respects.
4) The real needs of the market for Oracle programmers are not high. Especially on the periphery. If you do not have a metropolitan university, then this is pointless ...

Answer 7

we were given half a year of SQL itself in the MS sql server environment. And another whole year passed Oracle

Answer 8

Oracle, of course, is good, but for some reason I have not seen a single small office where it is used, unlike banks and large Internet / telecom providers with millions of users.
By the way, regarding hardware and OS - they are now moving away from Solaris towards certified Linux - Rkhel, Centosi, Suzie, Oracle Linux.
And for small student bases, this should be enough, although there are still Oracle distribution requirements for installation by media / RAM and processor (s) - this should be clarified already according to your edition.
I would also give database administration separately - database server configuration, backups, checks, restores, creating / changing databases, schemas, tables, user and rights management, optimization for high load, replication, clustering, monitoring, access protection - firewall, for example. It is worth mentioning how these databases lie on media - where are the indexes, where are the schemas, where are the logs / data, etc., the options for accessing the server - streams / threads / sockets / tcpip / what else he can do there. By the way, the analysis of the database server logs is also worth giving.

Answer 9

We had two semesters. First: relational databases in general, relationships, structure, keys, indexes, triggers, data integrity, transactions, normal forms, etc. There were many examples and tasks aimed at creating the correct conceptual database model for various subject areas. Second semester: SQL language, from creating a database to terrible nested queries that calculate the average temperature in a hospital and take data from several tables. It all ended with term paper - the creation of an automated workplace for a given subject area. The course is very good, but we did the labs on Microsoft Visual FoxPro - a slightly outdated development environment. Although the coursework could be done on anything.