Part 3 — ER Modeling & ER Diagram

CLO3 · 11 marks. Parts 3 & 4 share the same university schema — learn it once. Rectangle = entity, double rectangle = weak entity, diamond = relationship, underline = key.

The schema

student (student-id, name, program)
course (courseno, title, syllabus, credits)
course-offering (courseno, secno, year, semester, time, room)
instructor (instructor-id, name, dept, designation, salary)
enrolls (student-id, courseno, secno, semester, year, grade)
teaches (courseno, secno, semester, year, instructor-id)
requires (maincourse, prerequisite)

Q5(a)For each relation, identify: keys (primary/foreign), attribute types, entity vs relationship, and weak vs strong. (5)

Relation	Kind	Primary key	Foreign key(s)	Attribute notes
student	Strong entity	student-id	—	name, program — simple
course	Strong entity	courseno	—	title, syllabus, credits — simple
instructor	Strong entity	instructor-id	dept → department	name (composite), salary, designation — simple
course-offering	Weak entity (section)	(courseno, secno, year, semester)	courseno → course	existence-dependent on course; secno/semester/year = discriminator; time, room — simple
enrolls	Relationship (M:N)	(student-id, courseno, secno, semester, year)	student-id → student; (courseno,secno,semester,year) → course-offering	grade = descriptive attribute
teaches	Relationship (1:M)	(courseno, secno, semester, year)	instructor-id → instructor; (…) → course-offering	links instructor ↔ section
requires	Recursive relationship	(maincourse, prerequisite)	both → course.courseno	course-to-course; roles: maincourse & prerequisite

Attribute types you must be able to name

Type	ER symbol	Example
Simple (atomic)	plain oval	salary, gender
Composite	oval with sub-ovals	name → (first, middle, last)
Single-valued	plain oval	date_of_birth
Multi-valued	double oval	phone_numbers
Derived	dashed oval	age (from DOB)
Key	underlined	student-id

Keys — the nesting hierarchy

Q5(b)bigDraw the detailed ER diagram (attributes, keys, relationships, cardinality, correct notation). (6)

Cardinality — the four shapes

Weak entity essentials

Double rectangle for the entity.
Double diamond for its identifying relationship.
Dashed underline for the discriminator (partial key).
PK = owner's PK + discriminator.

Participation

Total (double line): every entity must participate — every student must have an advisor.

Partial (single line): may not participate — some instructors advise nobody.

Next: Part 4 — SQL →