L06 Database Design Using the E-R Model

Date: 12 March

Tutorial and Assignment
[20200309~20200317] Tutorial No.4 - 22 MAR

Design Phases

Outline of ER Model

Entity Sets

Composite Attributes

address

• street
  • street name
  • street number
• city
• state
• postal_code

Expressing Weak Entity Sets

Advisor is a set of connected with section and course

E-R Diagram for a University Enterprise

many-to-many relationship
An instructor may advise many students, and a student may have many advisors

Redundant Attributes

Just one for more
schema for relationship set advisor

advisor = (s_id, i_id)

Loop

Prereq is many to many

Weak entity set

(phone_number) is multivalued attribute M

冗余（Redundancy）：存储两次数据，以便使系统更快速。 主键（Primary Key）：主键是唯一的。同一张表中不允许出现同样两个键值。一个键值只对应着一行。 外键（Foreign Key）：用于连接两张表。

Redundancy 冗余 of Schemas

classroom (building, room_number, capacity)
>> classroom (building, room_number, capacity)
course (course_id, title, credits)
>> course (course_id, title, dept_name, credits)
department (dept_name, building, budget)
>> department (dept_name, building, budget)
instructor (ID, name, salary)
>> instructor (ID, name, dept_name, salary) 
student (ID, name, tot_cred)
>> student (ID, name, dept_name, tot_cred)
section (course_id, sec_id, semester, year)
>> section (course_id, sec_id, semester, year, building, room, time_slot_id)
time slot (time_slot_id, day, start_time, end_time)
>> time slot (time_slot_id, day, start_time, end_time)
teaches (ID, *course_id, sec_id*, semester, year)
>> teaches (ID, course_id, sec_id, semester, year)
takes (

Specialization

Attribute inheritance
Overlapping
Disjoint

Completeness constraint

total
an entity must belong to one of the lower-level entity sets

partial
an entity need not belong to one of the lower-level entity sets

Aggregation

Same information will take overlapping

schema eval_for is

class eval_for {
    ..
  S_ID,
  project_id,
  i_ID,
  evaluation_id
}
create table eval_for {
  S_ID,
  project_id,
  i_ID,
  evaluation_id
}

Design Issues

Common Mistakes in E-R Diagrams

dept_name is emlinate

show the diagram
assignment or marks can only make one inside stud_section schema.

Correct form

Entities vs Attributes

Phone number is not a nessary component in instructor so it is enough to set inside instructor as Attributes rather than entitles sets.

To simply the structure

Alternative ER Notations