Matrix Diagrams

• A grid-like drawing that can be used to discover and record relationships between entities in an entity- relationship model - matrix diagram

1.      Read the business scenario and review the ERD. Using the matrix diagram, make up two or more possible relationships between PHOTOGRAPH and the other entities that make sense for the business.

Scenario:

“I’m an amateur photographer. I own several cameras and am always taking pictures of different subjects. I’m trying to keep track of which camera and type of film perform best under certain conditions—indoor light, outdoor light, etc.—so when I have my films developed, I note down which camera I used. When the pictures come back, I note the subject and conditions. Each picture always features one subject. A subject could be a view, a person or group of persons, or an object or group of objects.”

Two possible relationships are:

	CAMERA	PHOTOGRAPH	SUBJECT
CAMERA		Used to take with
PHOTOGRAPH	Taken with		of
SUBJECT		Featured in

2. Complete the matrix diagram below, and construct an ERD from it.

	Runner	City For Race	Race Type	Running Event
Runner		visits	chooses	attends
City For Race	visited by			hosts
Race Type	Chosen by			Contained in
Running Event	Attended by	Happens in	contains

1.      City of race must be visited by many runners, otherwise there is no race and hence, it’s not a city of race. Runner may visit multiple city of race since he is very enthusiastic.

2.      RaceType must be chosen by many runners otherwise, racetype will be dropped from the event. Runner may wish to pick up multiple RaceTypes ,  he may be versatile.

3.      Event must be attended by multiple runners or there is no event. Runner may choose to attend multiple events.

4.      City of race must host running event/events or else it’s not a city of race. An event must happen in a city.

5.      RUNNING event must have one or more race types, runner won’t pay baseball in running event, will they run small or big distances. A racetype may be present in multiple events happening around.

Speaking ERDish and Drawing Relationships

• The language or statements used to describe relationships between entities in an entity-relationship diagram. -ERDish

1.      The goal of this practice is to read a relationship. Which text corresponds to the diagram?

a. Each EMPLOYEE may be assigned to one or more DEPARTMENTs.

Each DEPARTMENT must be responsible for one or more EMPLOYEEs.

b. Each EMPLOYEE must be assigned to one and only one DEPARTMENT.

Each DEPARTMENT must be responsible for one or more EMPLOYEEs.

c. Each EMPLOYEE must be assigned to exactly one DEPARTMENT.

Each DEPARTMENT may be responsible for exactly one EMPLOYEE.

1.      In the diagram for #1 identify the symbols for cardinality.

·         Crow foot on Employee side

·         Single toe on Department side

2.      In the diagram for #1 identify the symbols for optionality.

·         Solid line on Employee side

·         Solid line on department side

3.      Read the relationship in the diagram below. Write the ERD statement for the relationship.

·         Each CAMERA may be (optionality, dotted line) used to take one or more (cardinality, crow’s foot) PHOTOGRAPH

·         Each PHOTOGRAPH must be (optionality, solid line) taken with one and only one ( cardinality, single toe) CAMERA.

1.      Read each relationship in the model below. For each relationship, write the ERD statement and your comments. Use your knowledge of normal people and towns in your comments.

1)      Born in birth place: This is wrong, it says:

·         Each PERSON must be (optionality, solid line) born in one or more (cardinality, crow’s foot) TOWN

·         Each TOWN   may be (optionality, dotted line)   birthplace of one and only one ( cardinality, single toe) PERSON.

Right is: (assuming world has no villages, only towns)

If I assume, there are also villages in this world, solid line near PERSON becomes dotted.

1)      Living in/hometown of:  If I assume, there is no village, only towns in this world, this is OK. It says:

·         Each PERSON must be (optionality, solid line) living in one and only one ( cardinality, single toe) TOWN

·         Each TOWN   may be (optionality, dotted line)   hometown of one or more (cardinality, crow’s foot) PERSON.

If I assume, there are also villages in this world, solid line near PERSON becomes dotted. Also, this assumes that a person will not have two houses, if a person has two houses in two cities, that is not covered here.

2)      Visitor of/visited by: It says:

·         Each PERSON may be (optionality, dotted line) visitor of one or more (cardinality, crow’s foot)  TOWN

·         Each TOWN   must be (optionality, solid line) visited by one or more (cardinality, crow’s foot) PERSON.

I consider it right, because, if a there is a town in this world which is not visited by a PERSON, I won’t dare it call a town.

3)      Mayor of/governed by: it says:

·         Each PERSON may be (optionality, dotted line) mayor of one and only one ( cardinality, single toe)   TOWN

·         Each TOWN   may be (optionality, dotted line) governed by one and only one (cardinality, single toe) PERSON.

Mayor is the elected head of a city, town, or other municipality. And there is no point in selecting two Mayors for same town. Also, I don’t think a mayor can handle two towns. So single toe’s are OK

But I never heard of a city without mayor, even back in India, it exist but designation Title is different. So, solid line on town side seems more logical.

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]

Identifying Relationships

• ·         Identify the vocabulary word for each definition below.
• ·         A property of an end of a relationship between X and Y that describes how many of X is related to Y. Cardinality
• ·         A connection or association between objects. Relationship
• ·         A property of an end of a relationship between X and Y that describes whether X must be or may be related to Y. Optionality

1. Read the given business scenario. Name the relationships between EMPLOYEE and JOB. Include appropriate optionality and cardinality.

“We have a lot of employees who handle one or more different jobs. We’d like to keep track of who is working on which job. Although employees can help each other, a job is assigned to one employee and is the ultimate responsibility of that employee. All of our employees have at least one job. However, jobs exist that are not yet assigned to anyone.”

• ·         Single toe on employee side [cardinality]
• ·         Crow foot on job side [cardinality]
• ·         Dotted line on job side [optionality]
• ·         Solid line on Employee side [optionality]

Notation: 1. dotted line means may, solid line means must 2. Single toe means one and only one, crow foot means many

Edit:

Each Employee must be [optionality] assigned one or more [cardinality] Job. Each Job may be [optionality] assigned to one and only one [cardinality] employee.

2. Read the given business scenario. Name the relationships between ORDER and WAITER. Include the appropriate optionality and cardinality.

“We assign our waiters to certain areas, except for our trainees who just observe and are not responsible for taking any orders yet. A waiter takes the orders for the tables in his area. All areas have one assigned waiter. A customer places an order with a waiter. If the customer has a question or wants to make a change to the order, he needs to request this with the assigned waiter.”

• ·         One WAITER can take multiple orders. So crow foot on ORDER side [cardinality]
• ·         An order can be taken by/further taken care of by one waiter for single tow on WAITER side [cardinality]
• ·         A WAITER may also be a trainee, who need not cater order, or say there is no customer to cater, So, dotted line on waiter side [Optionality]
• ·         An ORDER cannot be catered without a waiter, so solid line on ORDER side. [OPTIONALITY]

3. Read the given business scenario. Name the relationships between PARENT and CHILD. Include the appropriate optionality and cardinality.

“At the end of each day, parents need to pick up their children at our day-care center. All children must be picked up by 6 p.m. A child may have two parents, but we need only one of them to come and pick up the child. We cannot release a child to anyone but that child’s parent.”

·         Parent picks child, but any parent may pick child. Dotted line on parent side [Optionality]

·         A Child must be picked up by Parent, so solid line on Child side. [Optionality]

·         One Child cannot be picked up by multiple parents, so single toe on Parent side [Cardinality]

·         One Parent may pick up multiple children, so Crow foot on Child side. [Cardinality]

Edit:

Each Parent may be [optionality] picking one or more [cardinality] child. Each Child must be [optionality] picked by one and only one [cardinality] parent.

4. Read the given business scenario. Name the relationships between TEACHER and STUDENT. Include the appropriate optionality and cardinality.

“Some students request remedial help in certain subjects, such as math. We can assign a tutor who can work with the student after class. Some of our teachers agree to be tutors. If several students need tutoring in one subject, then we assign them to the same teacher. If a student needs tutoring in several subjects, then he will probably be assigned to several different tutors.”

M:M

• ·         A Student can be assigned to multiple teachers. Crow foot [Cardinality]
• ·         A teacher can be assigned multiple teachers. Crow foot [Cardinality]
• ·         Since Student needs tutoring that’s why he is here, so he must be assigned to at least one teacher, so solid line on student side. [Optionality]
• ·         All teachers who agree to help are here, they are in second collection, one of them is teaching philosophy, quite possible that this teacher don’t get any student. So dotted line on teacher side. [Optionality]

5. Draw two entities that you are familiar with from your own experience (use colored pens and markers on hand). Write down the relationships between these two entities, including cardinality and optionality. Share your entities with the class.

• ·         Patient may raise multiple health claim so dotted line. [Optionality]
• ·         Healthcare claim must be raised by patient, so solid line. [Optionality]
• ·         Patient may raise multiple health claim so single toe on patient side and Crow foot on Claim side. [Cardinality]
• 
  

Entity Relationship Modeling and ERDs

• ·         Not dependent on the physical model. – Implementation-free
• ·         A drawing that is used to represent a data model. – Entity Relationship Diagram

1. True or False: To be described as implementation-free, a data model must be changed to accommodate the database system onto which it is built. False

2. True or False: To be described as implementation-free, a data model must not change to accommodate the database system onto which it is built. True

3. List four goals of entity relationship modeling.

·         Capture all required information.

·         Ensure that information appears only once.

·         Model no information that is derivable from other information already modeled.

·         Locate information in predictable, logical place.

4. An __________ is a consistent tool that can be used to represent data requirements regardless of the type of database used. Entity Relationship Diagram