Experiment 2

 

Reference and source:- Database Management Systems”, by Raghu Ramakrishnan & Johannes Gehrke,   third edition, McGraw-Hill, 2003.

Please refer above book for details 

Part 1:-

A university DB contains information about professors (identified by social security number, or SSN) and courses (identified by courseid). Professors teach courses; each of the following situations concerns the Teachers relationship set. For each situation, draw an ER diagram that describes it (assuming no further constraints hold).

• •Professors can teach the same course in several semesters, and each ofering must be recorded.
• •Professors can teach the same course in several semesters, and only the most recent such offering needs to be recorded. (Assume this condition applies in all subsequent questions.)
• •Every professor must teach some course.
• •Every professor teaches exactly one course.
• •Every professor teaches exactly one course, and every course must be taught by some professor.
• •Now suppose that certain courses can be taught by a team of professors jointly, but it is possible that no one professor in a team can teach the course.

 Part 2:- 
Design and draw ER diagram capturing all the following constraints regarding an university DB:

• •Professors have an SSN, a name, an age, a rank, and a research specialty.
• •Projects have a project number, a sponsor name, a starting date, an ending date, and a budget.
• •Graduate students have an SSN, a name, an age, and a degree program (M.S. or Ph.D.)
• •Each project is managed by one professor (known as the project’s principal investigator).
• •Each project is worked on by one or more professors (known as the project’s co-investigators).
• •Professors can manage and/or work on multiple projects.
• •Each project is worked on by one or more graduate students (the project’s research assistants). 
• •When grad. students work on a project, a professor must supervise their work on the project. Grad. Students may work on many projects (in this case they may have more than one supervisor). 
• •Departments have a department number, name, and a main office. 
• •Departments have a professor, who runs the department. 
• •Professors work in one or more departments, and for each department that they work in, a time percentage is associated with their job. 
• •Grad. students have one major department in which they are working on their degree. 
• •Each grad. Student has another, more senior grad. student (a student advisor) who advises him/her on what courses to take.