Question

1. Design an entity-relationship diagram for the problem statement below - justify any need for null values

For this project, you must help design and implement a database for an organization

whose goal is to promote and support STEM education in your state. Middle/high

school teachers and parents of students in such schools (a parent may be an educator too)

can become members by paying a yearly fee of $25. Each member is reminded about

the need for membership renewal one month prior to the expiration date. Renewal after

membership expiration involves a late penalty of $5. In practice, all members get a sixmonth grace period, though this fact is not advertised.

Each member has a first name, a last name, an address (which includes street, city,

and zipcode), a phone number, and an email address. The date he/she first joined is also

important. A unique ID number is assigned to members sequentially in the order they

join the organization. The organization is guided by a small Board of Directors consisting

of members who have designations such as President, Secretary, Treasurer.

Each school has a unique name; most schools can be categorized into either middle

or high school; but some chartered schools span categories: they can be both middle and

high schools. Some members volunteer to act as liaisons in one or more schools but there

is never more than one liaison for any school. For members who are educators, it is

necessary to know the school they teach in (no one teaches in more than one school) and

whether or not they actually teach (or had taught) math/science in that school.

It is also important to keep track of each school-going child of parent members: the

child’s name, the school the child attends, and the child’s grade (Kindergarten, 1, 2, ...,

12).

Frequently, the organization arranges public meetings to publicize their activities

and spread awareness of STEM among citizens. The theme, location, and date of each

meeting must be remembered. Each meeting is the responsibility of exactly one Board

member.

With a bit of encouragement, non-members who show up at such meetings often

become members eventually. For this reason, it is desirable to note the name, address,

and email of such persons. Also, if applicable, it is useful to record the name of a schoolgoing child or the school he/she teaches in, or both.

In a previous database, it was never clear when its content was obsolete, i.e., old

enough to be unusable. Hence, it is crucial that dates be associated with information. To

help, the organization is changing some of its procedures: e.g., it now verifies member

information during renewal.

News Flash: The Board has just decided to include Computer Science in STEM. They

want to keep track of schools that offer computing classes, plus whether those classes are

during-school or after-school, an AP (Advanced Placement) course or non-AP, and use a

dedicated computer lab or not. Also, the member teachers who teach such classes need

to be identified.

Answer 1

Below is the EER diagram of the given scenario. It consist of 5 entities and 2 sub entity of parent entity Member. These are connected via 5 relationship. Each entity has certain set of attributes and primary key is underlined.

Cardinality is shown using crow's foot notation.

City Location MeetinglD PeoplelD FirstName Theme State Name MemberID Date LastName Meeting Address) lp AttendsBy People Address JoiningDate Phone Member Conducts ame Phone Liaison School ISA Type Email Subject Teaches Educators Age Parent Children ave Name School Grade