Three Main Types of Relationship:

one-to-one (1:1)

one-to-many (1:n)

many-to-many (m:n)

Attributes of Relationship Types

• Weak Entity Types - do not have any key attributes of their own
• Identifying Relationship - the relationship type that relates a weak entity type to its owner.
• Identifying Owner
• Partial Key - set of attributes, that can uniquely identify weak entities related to the same owner entity.

Relations

1. dept - student 1:n

2. dept - faculty 1:n

3. enrol n:m

4. faculty - class 1:n

Practical Knowledge  

• Rrequirements analysis
• User orientation
• User views
• Preliminary conceptual schema 

a) Conceptual analysis - era

b) Logical design

• normalisation
• relation schemas 

c) Structural design

• storage structures
• access paths
• query....?

• transaction analysis
• updates

• de-normalisation

d) Physical design

• Data representation

• File organisation
•  index sequential etc.

For the following relation that stores information about students living in dormitories at a college, draw the ER diagram

college (stuname, stuid, homeadd, homephone, dormroom, roommate_name, dormadd, status, mealplan, roomcharge, mealcharge) 

1. Each student is assigned to one dormitory room and has at most one roommate. Names of students are not unique. 

2. The college has several dorms. Dormroom contains a code for the dorm and the number of the particular room assigned to the student. For example, a221 means adams hall, room 211. dorm names are unique. 

3. The dormadd is the address of the dorm building. Each building has its own unique address. For example, adams hall may be 123 main street, toronto, m5n 1h4. 

4. Status tells the student's status: freshman, sophomore, junior, senior of graduate student. 

5. Mealplan tells how many meals per week the student has chosen. Each meal plan has a single mealcharge associated with it. 

6. The roomcharge is different for different dorms, but all students in the same dorm pay the same amount