Question

Consider a university database for the scheduling of classrooms for final exams. This database could be modeled as the single entity set exam, with attributes course-name, section-number, room-number, and time. Alternatively, one or more additional entity sets could be defined, along with relationship sets to replace some of the attributes of the entity set, as

• course with attributes name, department, and c-number

• section with attributes s-number and enrollment, and dependent as a weak entity set on course

• room with attributes r-number, capacity, and building
Design a relational database schema for this database application. First show all
the functional dependencies that should hold among the attributes. Then, design relation schemas for the database that are each in 3NF or BCNF. Specify the key attributes of each relation. Note any unspecified requirements, and make
appropriate assumptions to make the specification complete.
>>>MUST upload a word document or an image. Handwritten answers are acceptable.

Answer 1

According to the given business rules the entities which are possible are as follows:-

Test, Course, Section, Room

The attributes of these entities are as follows:-

1. Course (name, department, c-number)
2. Section (s-number, enrollment)
3. Room (r-number, capacity, building)
4. Test (test-ID, time)

The ER diagram is as follows:-

The functional dependency possible as as follows:-

1. c-number -> name, dependent
2. c-number, s-number, test-ID -> enrollment
3. r-number -> capacity, building
4. test-ID, r-number -> time

The relation schema based on the above functional dependencies are as follows:-

1. Course (name, department, c-number)
2. Section (s-number, enrollment, test-ID, c-number)
3. Room (r-number, capacity, building)
4. Test (test-ID, time, r-number)

In above relation schema primary key are represented by underline and foreign key by bold, and the above relation schema is normalized up to BCNF.