CSC$430/530$ Database Management Systems

Hands$-$on activity $$ ER diagram

Suppose that a database is needed to keep track of student enrollments in classes and students$$ final grades. After analyzing the mini$-$world rules and the users$$ needs, the requirements for this database were determined to be as follows:

The university is organized into colleges $($COLLEGE$),$ and each college has a unique name $($CName$),$ a main office $($COffice$)$ and phone $($CPhone$),$ and a particular faculty member who is dean of the college. Each college administers a number of academic departments $($DEPARTMENT$).$ Each department has a unique name $($DName$),$ a unique code number $($DCode$),$ a main office $($DOffice$)$ and phone $($DPhone$),$ and a particular faculty member who chairs the department. We keep track of the start date $($CStartDate$)$ when that faculty member began chairing the department.

A department offers a number of courses $($COURSE$),$ each of which has a unique course name $($CoName$),$ a unique code number $($CCode$),$ a course level $($Level: this can be coded as $1$ for freshman level, $2$ for sophomore, $3$ for junior, $4$ for senior, $5$ for graduate level$),$ a course credit hours $($Credits$),$ and a course description $($CDesc$).$

The database also keeps track of instructors $($INSTRUCTOR$)$; and each instructor has a unique identifier $($Id$),$ name $($IName$),$ office $($IOffice$),$ phone $($IPhone$),$ and rank $($Rank$)$; in addition, each instructor works for one primary academic department.

The database will keep student data $($STUDENT$)$ and stores each student$$s name $($SName$,$ composed of first name $($FName$),$ middle name $($MName$),$ last name $($LName$)),$ student id $($Sid$,$ unique for every student$),$ address $($Addr$),$ phone $($Phone$),$ major code $($Major$),$ and date of birth $($DoB$).$ A student is assigned to one primary academic department. It is required to keep track of the student$$s grades in each section the student has completed.

Courses are offered as sections $($SECTION$).$ Each section is related to a single course and a single instructor, and has a unique section identifier $($SecId$).$ A section also has a section number $($SecNo$),$ semester $($Sem$),$ year $($Year$),$ classroom $($CRoom: this is coded as a combination of building code $($Bldg$)$ and room number $($RoomNo$)$ within the building$),$ and days$/$times $($DaysTime: for example, $$MWF $9$am$-9\mathrm{.}50$am$$ or $$TR $3\mathrm{.}30$pm$-5\mathrm{.}20$pm$).$ The database keeps track of the students in each section, and the grade is recorded when available $($this is a many$-$to$-$many relationship between students and sections$).$

Your task is to design an ER diagram that represents a conceptual schema for the described mini$-$world.

Make sure you use following ER design concepts:

$$ Entity types.

o Attributesofentitytypes.

o Types of attributes $($e$.$g$.$ key, composite, multivalued$).$

$$ Relationship types.

o Attributes of relationship types $($if applicable$).$

o Role names $($if applicable$).$

o Cardinality ratio and participation constraints of relationship types.

Solution: