Relationship Transferability

• ·         Description of a relationship where an instance of A is related to an instance of B, and the association can be moved to another instance of B. – Transferable
• ·         Description of a relationship where an instance of A is related to an instance of B, and the association cannot be moved to another instance of B - nontransferable

1. Draw ERDs for each of the following. Draw softboxes, relationship lines, and labels for each relationship in both directions. Indicate non-transferability when appropriate.

a. Each town may be the birthplace of many people. Each person must be born in one and only one town.

assuming world has no villages, only towns:

b. Each room may house one or more guests. Each guest may stay in one and only one room.

c. Each employee must work for one and only one department. Each department may have one or more employees.

d. Each hotel may be the host of one or more guests. Each guest may be hosted in one or more hotels. This one is tricky, generally when a guest is hosted in a hotel, he stays there, if he change hotel, previous hotel booking goes away, but here, problem says guest may be  hosted in multiple hotels

e. Each message must be addressed to one or more persons. Each person may be the addressee of one or more messages. A message is addressed here, not sent, so it is still transferrable.

f. Each garment must have one and only one price. Each price may be for one or more garments.

g. Each airline coupon must be used for one and only one destination. Each destination may be visited with one or more coupons. A coupon is generated one destination, if generated by mistake, new is reissued, so nontransferable here.

h. Each automobile must use one and only one tire size. Each tire size may be used by one or more automobiles. Tire size of an automobile is defined by design,   if the tire size need to be changed, means the automobile will be recalled. And a new unit will be given. So it is nontransferable.

i. Each child must have one and only one biological mother. Each mother must be the parent of one or more children.

j. Each person must be of one and only one blood type. Each blood type may classify one or more persons.

k. A person may be on one or more junk-mail lists. Each junk list may contain one or more persons.

l. Each student may learn from one or more teachers. Each teacher may educate one or more students.

m. Each school may be attended by one or more honor students. Each honor student must attend one and only one school.

n. Each fingerprint must belong to one and only one person. Each person must have one and only one fingerprint.

Documenting Business Rules

• ·         A type of business rule that indicates the types of information to be stored and how the information elements interrelate. – structural business rule
• ·         A formalized statement of the usual, customary, or generalized course of action or behavior for a business. – business rule
• ·         A type of business rule that is workflow or business process related. (e.g., A has to happen before B, and then C has to happen at the same time as D.) This is also called a process business rule. –procedural business rule

1. Members of your design team have been working with the local hospital to develop a data model for their need to store information about patients, the patient's room number, the patient's doctor, drug prescriptions given, and specific drug information.

However, they all went on vacation and left you to figure out the model. They also failed to give you any of their documentation other than the entities and attributes illustrated here. Instead of going back to the hospital, which could reflect poorly on your company, you’re going to have to think about everything you know about hospitals!

Your task is to generate a list of business rules you think were used to arrive at the information shown here. Use your imagination. List 10 structural rules, 5 procedural rules, and 2 programmatic rules (rules to be addressed by computer applications in the future). State each rule as a single sentence.

Based on your set of business rules, draw the ERD.

·         Each floor in all buildings has a VIP room which should be allocated to normal patient only when all other normal rooms are full. – procedural business rule, need programming

·         Patient, who has past dues, will not be admitted, if there is a balance from past admissions – procedural business rule, needs programming

·         When a Physician goes on leave, all his patients are directed to one of the junior doctor in his team, available on the day of admission. – procedural business rule, need programming

·         If a prescription is raised for a drug, while giving medicine, if generic version in stock, it will be issued to the patient, unless doctor mandated use of branded drug in follow-up call by pharmacist. – procedural business rule, need programming

·         If the drug being issues to patient is going to expire in next 6 months, drug won’t be issued and drug label won’t be generated. – procedural business rule, need programming

·         When a DrugLabel is generated, means patient is issued a batch of dose, no of available refill reduce by one – procedural business rule, need programming

o   A room is identified by room number and building number, in same building there can be no rooms with same number – structural business rule

o   A room can be VIP room or ordinary room – structural business rule

o   Each room has the capacity of one patient – structural business rule

o   Patient may be issued a room, or not. – structural business rule

o   Patient must be issued a unique patient number at the time of registration. - structural business rule

o   A patient must be assigned a unique physician while admission – structural business rule

o   A Physician has unique physician number – structural business rule

o   Physician must have a valid license number to be employee of this hospital.- structural business rule

o   A Physician may have a senior Physician under whose supervision he works – structural business rule 

o   A prescription has unique prescription number – structural business rule

o   If a doctor prescribes multiple drugs, for each drug he will raise separate prescription – structural business rule

o   A prescription is bound to a unique patient – structural business rule

o   A drug must mention, whether it is generic or not – structural business rule.

o   Drug label must mention the expiration date of pills included in the container. - structural business rule

o   A drug label must be issued with prescription – structural business rule

o   No drug label may not generated for a prescription, if patient condition improves before the drug label is generated, but doctor don’t cancel such prescription, just in case symptoms come back soon. – structural business rule.

Even though DrugLabel (if seen as a print out) generated contains a lot of information from Prescription, I won’t repeat information, since it has prescriptionId in the database.

Supertypes and Subtypes

• ·         All subtypes are listed without omission. - Exhaustive
• ·         A means of classifying an entity that has subtypes. - Supertype
• ·         Something an entity may be split into based on common attributes and/or relationships. - Subtype
• ·         Each instance of a supertype is an instance of only one possible subtype. – Mutually Exclusive

1. Identify which item off of the following list is the supertype entity and which items are the subtypes of that entity.

a.       Amputation = ______Subtype of d_______________

b.      Visual Impairment = _____Subtype of d________________

c.       Hearing Impairment = ______Subtype of d_______________

d.      Disability = ___SuperType of the rest__________________

e.       Paralysis = ____Subtype of d_________________

2. For each rule, indicate whether the rule is applicable to supertypes or subtypes.

__Subtype________ They share common attributes

__SubType________ They inherit all attributes and relationships of the entity

____SubType______ It never exists alone

____SuperType______ It contains the attributes held in common by all instances

3. Name three things you consider when modeling supertypes and subtypes.

·         Is this subtype a kind of supertype?

·         Have I covered all possible cases? (Exhaustive)

·         Does each instance fit into one and only one subtype? (mutually exclusive)

4. Find the incorrect subtypes in the illustration. Explain why you think the subtype is incorrect. Adjust the model to improve it.

·         Is this subtype a kind of supertype – a vehicle may also be non-automobile

·         Have I covered all possible cases? – in both BUILDING and AUTOMOBILE all possible cases are not covered

·         Does each instance fit into one and only one subtype – a sedan is an enclosed automobile body having two or four doors and seating four or more persons on two full-width seats. Means “4-DOOR VEHICLE” and ‘SEDAN” are not mutually exclusive.

To improve AUTOMOBILE, 4-door may be removed and a new SubType OTHER may be added.

Same in BUILDING, to accommodate anything else than HOUSE, OTHER may be added.

5. Read the following scenario and construct an ERD that contains at least two subtypes of the entity PRODUCT. Show clearly which attributes belong to the entity supertype, and which belong to the subtypes. Identify a UID for the entity.

“Our shops sell several kinds of women’s clothing, including dresses, skirts and blouses. Of course each product has a name, a description, and a price. Oh, and sizes too: all products have a waist size. Dresses and skirts have a hem length but blouses don’t. Dresses and blouses have a chest size, but skirts don’t.”

Dresses have generally two parts that is why they have hemlength and chestsize.

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]