ER Diagramming Conventions

• A four-sided visual element with rounded corners, used to represent an entity in an ERD. Entity represented by Softbox

1. Read the given business scenario. Draw the entities HAIRSTYLIST and CLIENT. List the attributes associated with each entity and specify whether they are mandatory or optional. Identify the UIDs. Write out the relationship in English, including optionality and cardinality. Follow the diagramming conventions discussed.

“In our salon, we have a number of hairstylists. They are all salaried employees, so we keep a record of their first name, last name, address, phone number, social-security number, and salary. During the course of a day, a hairstylist may see several clients. On a slow day, a hairstylist may not work on anyone at all. We have several walk-in clients, and they each get assigned to one hairstylist. We just ask for their first name. We also have customers who call to make an appointment. When they do this, we ask for their first name, last name, and phone number. We also ask if they would like a specific hairstylist. If they have no preference, we assign one for them. Of course, they are allowed to switch to another hairstylist for their next visit to the salon. We are interested in tracking the daily appointments -- which stylist works on which client during a given day.”

a.    Hairstylist has all attributes mandatory.

b.    Client, if he is walk-in, last name and phone number is not collected. But I have marked Preference as mandatory here, because, he is given a preference in DB the stylist who is available free next for walk-in case also.

c.    An appointment cannot be made without a client and hairstylist, but they can’t uniquely identify an instance of appointment, reason being that person will come again after 15 days for haircut. So no barred relationship here.

Stylist vs client:

·         On stylist can be preference of many clients. – Crow feet at client [cardinality]

·         On client can have only one preference at a time. - Single toe at stylist side [cardinality]

·         One stylist may be very bad and no one likes him – dotted line of stylist side [optionality]

·         On client must have a preference, if he says, I don’t have any, person on counter assigns, next available – solid line on client side [optionality]

. Stylist vs Appointment:

·         On stylist can have multiple appointments – Crow feet at Appointment [cardinality]

·         On one client, only one stylist works at a time, if client needs multiple things, like haircut and eyebrow both, he makes two appointments with same or different stylists. – single toe on Stylist side [cardinality]

·         One stylist may be very bad and no one likes him, so no one appoints him – dotted line of stylist side [optionality]

·         On appointment must have a stylist to work. – solid line on apt side [optionality]

. Client vs Appointment:

·         On client can have multiple appointments – Crow feet at Appointment [cardinality]

·         One appointment cannot serve multiple clients. – single toe on client  side [cardinality]

·         One client enters system, when he makes appointment or he walks in, if he walks in even then current appointment is made. – solid line of client side [optionality]

·         On appointment must have a client  to serve. – solid line on apt side [optionality]

2. Read the given business scenario. Draw the entities BAND and MUSICIAN. List the attributes underneath each entity. Specify whether they are mandatory or optional. Identify the UIDs. Write out the relationship in English, including optionality and cardinality.

“I am an agent for several musicians and bands. A musician may be a solo performer or may belong to a band. A band will always have one or more musicians in it. Some musicians are a one-man band. However, a musician can belong to only one band. Since I schedule them for concerts and events, I need to keep track of certain information: the musician’s first name, last name, address, phone number, and hourly rate. If it’s a band, I need to know the band name in addition to the information I already keep for the member musicians. I’ve handled bands with the same name, so just to make sure I book the right band, I assign an ID to each one. The hourly rate for a band is the total of the hourly rates of its members.”

·         A Band can have multiple musician – Crow feet at Musician  [cardinality]

·         A musician can belong to only one band. – single toe on Band  side [cardinality]

·         A musician may be a solo performer. – dotted line of musician side [optionality]

It also means BandId is optional here

·         A band will always have one or more musicians in it. – solid line on Band side [optionality]

Further rate of a band is calculated by musician rate, so no need to keep it in band entity.

3. Read the given business scenario. Draw the entities TEACHER and COURSE and CLASS. List the attributes underneath each entity. Specify whether they are mandatory or optional. Identify the UIDs. Write out the relationship in English, including optionality and cardinality.

“We have several teachers at our school. A teacher can be assigned up to three classes per semester. If a teacher is on sabbatical, he doesn’t teach that semester. We keep a record of the teacher’s first name, last name, address, phone number, and email address.”

“Our school offers many courses -- such as Data Modeling, Introduction to SQL, Trigo-nometry, Physics, and Biology. Each course has a code. For example: Data Modeling would be DM001, Trigonometry would be TR004, etc. During each semester, a course may be taught in several classes -- so there could be two classes of Physics, three classes of Biology, etc. Each class can be taught by only one teacher. We assign a unique ID for each class, and we also keep track of the day it is taught, the time, and the classroom.”

Red – procedural – need coding

Teacher-Subbatical-Session(semester)

TeacherId and SessionId combination in Subbatical are unique and deem to be composite primary key.

Teacher vs Subbatical

·         Teacher may take Subbatical in a session – dotted line [optionality]

·         Sabbatical must be taken by any teacher –solid line [ optionality]

·         Teacher may take many sabbatical – crow foot on sabbatical [Cardinality]

·         A Subbatical is taken by one teacher – single toe on Teacher side [cardinality]

Subbatical vs Session

·         Subbatical must be taken in a session-solid line on suubatical side[optionality]

·         There may be a sabbatical in a session- dotted line on session side [optionality]

·         There could be many sabbatical in a session- crow foot on sabbatical side[cardinality]

·         A sabbatical can only be in one session – single toe on session side [cardinality]

Teacher-TeacherCourseMap-Course

TeacherId and CourseId (coursecode) combination in TeacherCourseMap is unique and deem to be composite primary key.

Teacher vs teachercoursemap

·         A teacher may be teaching a subject or be just a newly hired. (This has nothing to do with sabbatical, sabbatical verification is done procedurally in code) – dotted line on teacher side [optionality]

·         A teachercoursemap must have a teacher id- solid line on teachercoursemap side [ optionality]

·         A teacher is proficient in multiple subjects – crow foot on teachercoursemap side – [cardinality]

·         A teachercoursemap is mapped to single teacher – single toe on teacher side [cardinality]

Techercoursemap vs course

·         A teachercourse map must be corresponding to a course entry- solid line on teachercoursemap side [optionality]

·         A course may have a teacher in college  - dotted line on course side [optionality]

·         A course has zero or more teachers in college – crow foot on teachcoursemap side [cardinality]

·         A teachercoursemap  instance has only one course to map to- single toe on course side [cardinality]

TeacherCourseMap – Class

TeacherId + CourseId is primary key for teachercoursemap and a unique identifier. This is used as composite foreign key in class entity. E.g. [ actual db creation query example] :

alter table public.class add CONSTRAINT fk_teachercoursemap_class FOREIGN KEY(teacherid, classid) REFERENCES teachercoursemap(teacherid, classid)

Purpose of composite foreign key here is to make a teacher available for class only if he can teach that subject, you may not want to give Biology class to data modelling teacher.

·         A teacher if available in a semester (not went on sabbatical and is active) only then can teach, this is handled by coding. Means even if a teachercoursemap is available, it may not have been used. This much is sufficient to make dotted line on teachercoursemap side, but there could be other factor also, like course is no longer active or is not decided to be taught in current session due to length of course and current session is summer.- so dotted line on teachercoursemap side [optionality]

·         A class when taught by a teacher for a subject must refer to an entry in teachercoursemap – solid line on class side [ optionality]

·         A teacher proficient in a subject can teach more than one class – crow foot on class side [cardinality]

·         A class teaches one subject for one teacher – single tow on teachecoursemap side – [cardinality]

Class vs Schedule

·         A class might have a schedule; some classes are so easy that, no schedule is required, only study material is distributed – dotted line on class side [optionality]

·         If there is a schedule instance this must be for a class- solid line on schedule side [optionality]

·         A class has zero or many schedules – crow foot on schedule side [cardinality]

·         A schedule cater only  one class – single toe on class side [ cardinality]

Schedule vs Room

·         A room might be booked for a schedule – dotted line on classroom side [optionality]

·         A schedule must happen in a room( if it is an online schedule, room name is virtualLineXX) – solid line on schedule side [optionality]

·         In room many schedules may be booked – crow foot on schedule side [cardinality]

·         One schedule is booked in single room – single toe on classroom side – [cardinality]

Class vs Session (semester)

·         A class must happen in a semester – solid line on class side [optionality]

·         In a session, there might be  a class, say there is no student, session will still happen teachers will be paid, but no class will be organized – dotted line on session side [optionality]

·         In a session there could be multiple classes – crow foot on class side [cardinality]

·         A class happens in one session – single toe on session side [cardinality]