Database Diagrams – Crow’s Foot Notation Identifying vs Non-Identifying Relationships

I'm not a DBA but I've looked at many "new to me" databases.

I don't know if there is a trick to understanding a database. What has worked for me is just to explore the db and query it to see what it returns.

Many times as a new developer to a project I'm given a task to fix or update something. I usually look at the table like you did with alert and look at any other id columns that might be in the table (i.e user_id & incident_id). That starts the bread crumb trail. Then I look at the foriegnkeys to see what they are doing (i.e. cascade delete). The FK usually point to another table that could also lead to other tables.

If the bread crumb trail of id columns gets bigger than 2 or 3, then I usually try to diagram the db if possible. Sometimes that helps me to see the tables in a different perspective.

Other than that, it just takes time. As you get more tasks under your belt with the db you will understand more of it.

The Student - Applicant relationship is 1:1 and your implementation is correct. An alternative is to remove the app_id (or the stud_id) from the Applicant table and to use the (one and only) column both as a Primary Key and for the Foreign Key constraint (see the code below).

I assumed from your code that a Student can be selected as an Applicant. If it's the other way around (some of the Applicants are selected as Students), the foreign key constraint has to be reversed.

You also have several columns (that are used in the foreign key constraints) defined as NULL. Not sure how the diagram is to be interpreted. I guess that these should be NOT NULL. Otherwise, the course.branch_id for example being null means that you may have a course that is not related to any branch at all.

I would also name the (FK) constraints, and since this is homework, I'd name them according to the provided diagram.

It's also not bad to add the NOT NULL to primary key columns as well. It changes nothing in the created tables - until you decide to change the primary key and you forget to add the not null constraint:

create table department
 (dept_id number not null primary key, 
  dept_name varchar2(15) not null
 );

create table branch
 (branch_id varchar2(5) not null primary key, 
  electives varchar2(10),
  dept_id number not null,
  constraint Department_Has_Branches
    foreign key (dept_id)
    references department(dept_id)
 );

create table course
 (course_id number not null primary key,
  course_name varchar2(10) not null,
  branch_id varchar2(5) not null,
  constraint Branch_Offers_Courses
    foreign key (branch_id)
    references branch(branch_id)
 );

create table student
 (stud_id number not null primary key, 
  stud_name varchar2(30) not null,
  branch_id varchar2(5) not null,
  constraint Student_BelongsTo_Branch
    foreign key (branch_id)
    references branch(branch_id)
 );

create table applicant
 (app_id number not null primary key,      -- stud_id removed           
  constraint Student_SelectedAs_Applicant
    foreign key (app_id)                   -- app_id used in the FK to Student table
    references student(stud_id)
);

create table applicant_AppliesFor_branch
 (app_id number not null, 
  branch_id varchar2(5) not null,
  primary key (app_id, branch_id),
  constraint Student_AppliesFor_Branch
    foreign key (app_id)
    references applicant(app_id),
  constraint Branch_AppliedBy_Student
    foreign key (branch_id)
    references branch(branch_id)
);

For Oracle, see:

Display all names of constraints for a table in Oracle SQL