1. The Emory College University Database

First, we must find the entities. Which "things" are important enough for us to uniquely identify them with a number ?

From the description, we can discern the following entities:

• department
• faculty
• course
• semester
• section
• student

---

OK, let's highlight the entities in the description:

1. The Emory College consists of a number of Departments (MathCS, Physics, etc)
2. A department has a unique name, address, phone number and can have a number of faculty members.
3. A faculty member has SSN, name, office, phone number and salary
4. A department has one department chair with a starting date
5. A department can teach a number of courses
6. A course has a unique ID, a course name and a description
7. A semester has an unique ID a start date and an end date.
8. A number of sections of a course is offered in a semester and a section will be taught by one faculty member.
9. Each section is uniquely identified by a section number.
10. Not every course will be offered in a semester and some courses may have multiple offerings (multiple sections of the course).
11. A student has an ID, name and address
12. A student can enrolled in one section of some course.
13. A student can enrolled in multiple sections in one semester.
    (Clarification: multiple sections of different courses - but you don't have to represent this fact in the ER diagram because it is not easy to do)

---

---

The next thing you do is find the attributes for each entity.

Attributes are properties that belong to a single entity - if you find a property that involves 2 (or more) entities, that property is not an attribute, but a relationship.

From the descriptions, we can gather the following attributes for the entities:

• From line 2: department has a unique name, address, phone number
  • Department(dName, address, phone)

• From line 3: faculty member has SSN, name, office, phone number and salary
  • Faculty(SSN, name, office, phone, salary)

• From line 6: course has a unique ID, a course name and a description
  • Course(CourseID, cname, description)

• From line 7: semester has an ID, a start date and an end date.
  • Semester(SemID, start_data, end_date)

• From the hint that section is very important for a university (see above for reason...) We just give section a key for now (and identify the attributes later): sectionID
  • Section(SectionID)

• From line 10: Student has an ID, name and address
  • Student(StudID, name, address)

---

---

Next, find the properties that involves 2 or more entities.

These are relationships. You must also find the properties of the relationship.

From the descriptions, we can gather the following relationships and their properties:

• Line 2: department has faculty members:
  • Relationship: Member(Department, Faculty)

  • Relationship property:
    • 1 department has N faculty,
    • 1 faculty belongs to 1 department,
    • department can have 0 faculty member
    • but, a faculty member must belong to some department

• Line 4: department has a department chair (faculty):
  • Relationship: Chair(Department, Faculty)

  • Relationship property:
    • 1 department has 1 chair faculty,
    • 1 faculty chairs 1 department,
    • every department must have a chair (this may sound contradicting with the fact that a department can have 0 faculty members.... it need not, because empty departments can have some faculty from another department as chair)
    • not every faculty member is a chair

• Line 5: department offers courses:
  • Relationship: Catalog(Department, Course)

  • Relationship property:
    • 1 department offers N courses,
    • 1 course is offered by 1 department,
    • a department need not offer any course,
    • a course must be offered by some department

• Line 8: a course is offered as a section:
  • Relationship: Offer(Section, Course)

  • Relationship property:
    • 1 course can be offered in N different sections,
    • 1 section can offer exactly 1 course,
    • not every course will be offered as sections,
    • a section must offer some course

• Line 8: a section is offered in a semester:
  • Relationship: Offered_In(Section, Semester)

  • Relationship property: 1 section can be offered in 1 semester,
    • 1 semester can offer N sections,
    • 1 section is offered in 1 semester,
    • not every semester will offer section (If you have a total participation for semester, it's OK and no point will be deducted, in your model, you would require that at least 1 section be offered in every semester...)
    • a section must be offered in some semester (no choice here)

• Line 8: a section is taught by a faculty:
  • Relationship: Teaches(Section, Faculty)

  • Relationship property:
    • 1 section is taught by 1 faculty (no shared teaching),
    • 1 faculty can teach N sections,
    • a section must be taught by some faculty,
    • not all faculty has to teach some section (e.g., faculty can be on leave)

• Line 11: a student enrolls in a section, and
  Line 12: A student can enrolled in multiple sections in one semester.
  • Relationship: Takes(Student, Section)

  • Relationship property:
    • 1 student can enroll in N sections,
    • 1 section can have N students enrolled,
    • a student need not enroll in any section (you just have to register to be a students, don't need to take any course - this can happen when you just "wait" for graduation),
    • a section can have no students enrolled (e.g., before the enrollment period. BTW, sections without any students enrolled will be cancelled. But before cancellation, the section exists in the DB without participating in the Takes relationship !!!)
  Note:

  "A student can enrolled in multiple sections in one semester" does not mean that there is a relationship between student, section and semester !!! Previously, you know that: A section is offered in a semester The sentence: "A student can enrolled in multiple sections in one semester" is repeating the information that: A section is offered in a semester This sentence is actually saying that: "A student can enrolled in multiple sections that are offered in a semester"

---

When we put all the above information into an ER-diagram, we will get the following:

---



---

Note: the constraint that students may not take different sections of the same course in the same semester cannot represented (easily) with an ER diagram.

---

---

• FAQ:

  Why can't I use this ternary relationship: Answer: This ternary relationship will require the use of null values in some situation If there are no students enrolled in some section, you need to use this tuple to represent the fact that faculty1 teaching section section1234567: (faculty1, section1234567, NULL) The ER model of the answer does not exhibit this problem and is therefore more accurate Why can't I use this ternary relationship: Answer: This relationship is too flexible and will allow some illegal information to be stored in the model Example: This ternary relationship will allow us to use the same section number for 2 different courses The cardinality retio and participation constraints in the binary relationships can prevent this from being stored

2. Question 3.16

OK, I will be a little less verbose than the above solution.

First, the entities (with their attributes):

• Student(name, StudNum, SSN, curr_addr, phone, perm_addr,perm_phone, bdate, sex, class, degr_prog)

• Department(dName, dCode, office_num, office_phone, college)

• Course(cName, description, cnumber, hours, level)

• Section(section_number, semester, year, instructor)
  • Notes on the Section entity:

    although the problem description says "section has a course", you can't include "course" as an attributes because "course" is an entity ! (Similar to: an employee has a department, a student has a major, etc... these are relationships) There is no key in this entity !!!. According to the problem description: section_number only distinguishes the same course taught in same semester and year, so (section_number, semester, year) is only unique if the course is added to it. By themselves, they are not a key !!! To put it in another way: you need to add "course number" to these these attributes to make a key, i.e., (section_number, semester, year, cnumber) is a key . (But you can't put the "course number" attribute in this entity because "Course" is an entity on its own !) Since there is no key in Section, "Section" is a weak entity. It needs cnumber in Course to form a key - in other words: the relationship between "Section" and "Course" is an (identifying) weak relationship

• Grade(letterGrade, numberGrade)
  • Note:

    if you did not define the Grade entity, your ER model may include letterGrade and numberGrade as attributes inside the "GradeReport" relation; like this: GradeReport( ......., LetterGrade, NumberGrade, ..... ) This solution will store duplicate information. Example: GradeReport tuples: GradeReport tuple 1: ......... , A, 4, .... GradeReport tuple 2: ......... , B, 3, .... GradeReport tuple 3: ......... , A, 4, .... The fact that A has 4 points is repeated

---

---

---

The Relationships:

• Student "majors" in a department: major(Student, Department)
  • a student have one or 2 majors
  • a major can be taken by many students
  • a student must have one major (total)
  • a major may not have any student (partial)

• Student minors in a department: minor(Student, Department)
  • a student have one minor
  • a minor can be taken by many students
  • a student does not need to have a major (partial)
  • a minor may not have any student (partial)

• Department teaches courses: teaches(Department, Course)
  • a department teaches many courses
  • a course is taught by one department
  • a department does not need to offer any course (partial)
  • a course must be offered by some department (total)

• Section offers a course (a course is offered in a section): offering(Section, Course)
  • a course is offered in many sections
  • a section offers one course
  • a course may not be offered in any section (partial)
  • a section must offer some course (total) - in fact, since section is a weak entity, its participation must be total.

• A grade is reported for a student (who is) taking a section.

  So this relationship is between 3 entities: grade_rep(Student, Section, Grade) - a grade_report assigns a grade to a student for a certain section

  This relationship is analogous to the (supplier, part, project) relationship discussed here: click here . If you use binary relationships (relationships between 2 entities) to represent the reality, you will not be able to capture the fact that "a grade is assigned to a student for a certain section".

  Notes on the Grade_Rep relationship:

  If you designed the "grade_report" as follows: Your ER-model will have duplicate information about (letterGrade, numberGrade) Example: Since grade_rep is a ternary relationship you don't have to specify cardinality constraints for a ternary relationship...

---

When we put all the above information into an ER-diagram, we will get the following:

---



---