top of page

ER Diagram Example: University Database

The university database stores details about university students, courses, the semester a student took a particular course (and his mark and grade if he completed it), and what degree program each student is enrolled in. The database is a long way from one that’d be suitable for a large tertiary institution, but it does illustrate relationships that are interesting to query, and it’s easy to relate to when you’re learning SQL. We explain the requirements next and discuss their shortcomings at the end of this section.


Consider the following requirements list:

  • The university offers one or more programs.

  • A program is made up of one or more courses.

  • A student must enroll in a program.

  • A student takes the courses that are part of her program.

  • A program has a name, a program identifier, the total credit points required to graduate, and the year it commenced.

  • A course has a name, a course identifier, a credit point value, and the year it commenced.

  • Students have one or more given names, a surname, a student identifier, a date of birth, and the year they first enrolled. We can treat all given names as a single object—for example, “John Paul.”

  • When a student takes a course, the year and semester he attempted it are recorded. When he finishes the course, a grade (such as A or B) and a mark (such as 60 percent) are recorded.

  • Each course in a program is sequenced into a year (for example, year 1) and a semester (for example, semester 1).


The ER diagram derived from our requirements is shown in Figure . Although it is compact, the diagram uses some advanced features, including relationships that have attributes and two many-to-many relationships.


In our design:

  • Student is a strong entity, with an identifier, student_id, created to be the primary key used to distinguish between students (remember, we could have several students with the same name).

  • Program is a strong entity, with the identifier program_id as the primary key used to distinguish between programs.

  • Each student must be enrolled in a program, so the Student entity participates totally in the many-to-one EnrollsIn relationship with Program. A program can exist without having any enrolled students, so it participates partially in this relationship.

  • A Course has meaning only in the context of a Program, so it’s a weak entity, with course_id as a weak key. This means that a Course is uniquely identified using its course_id and the program_id of its owning program.

  • As a weak entity, Course participates totally in the many-to-one identifying relationship with its owning Program. This relationship has Year and Semester attributes that identify its sequence position.

  • Student and Course are related through the many-to-many Attempts relationships; a course can exist without a student, and a student can be enrolled without attempting any courses, so the participation is not total.

  • When a student attempts a course, there are attributes to capture the Year and Semester, and the Mark and Grade.



Source: oreilly


The Tech Platform





5 comments

5 Comments


Guest
Nov 29, 2023

Well, I can say that the article turned out to be quite successful and useful. As for the database, yes, it is a small problem now compared to what it was thirty years ago. But I can still use scholarship essay examples, even with all the knowledge that is available now. Because it is simply easier and faster to implement, think about it, even to spend your own time when professionals can do everything for you. So now I'm very actively studying other subjects outside of college, because our database is boring to me.

Like

Guest
Nov 29, 2023

The database is quite a solution in such moments when you need to prepare for an exam. I used to rely on it a lot and was very happy that we had it. But that didn't change the fact that I didn't have time to do my written work. Therefore, I ordered services from an essay writer online who did everything flawlessly and clearly. Thanks to him, I managed to overcome several semesters of study and wrote my master's thesis with his help. Therefore, in combination with the database of our university, I was quite advanced in my studies.

Like

Guest
Nov 29, 2023

It's very cool that someone has a quality database from their university, it's pretty handy. I can say that I've seen this several times, but unfortunately I didn't have it. In order to complete my written assignments efficiently, I had to use cheap essay writing service reviews I read spoke for themselves in terms of the quality of this resource. That's why I could choose this service without any hesitation and be sure that it would do everything with high quality. But time passed and I got to the place where the knowledge base was and therefore made up for my mistakes.

Like

Guest
Nov 14, 2023

Very cool article, I always like it when people make quality articles, especially those related to education. Usually, everyone misses some point and it concerns books, in my opinion, if our education system paid more attention to this aspect, the situation would be coordinately different. I read a lot of books https://edureviewer.com/lsat-reading-comprehension-books/ in my time and thanks to that, I was ahead of my peers in my development. In general terms, this did not stand out so much, but if we take into account a clearer comparison, the difference was enormous. That's why I'm very glad that even online, quality articles have begun to appear.

Like

Guest
Jul 16, 2023

Thank you for this exercise, truly appreciate.

Like
bottom of page