CS 350—Database Management Systems Fall 2024Matlab

Java Python CS 350—Database Management Systems

Fall 2024

Assignment 1: Civic Association

You have been asked to help design a small database for a civic association in your community (the Juniors Club). This club has many members. The club would like to track the name, address, email, telephone number, and the date on which all members joined the club. When a member joins the club, they may be given a mentor. The mentor is a current club member who has chosen to be a mentor. Mentors may have several new members assigned to them, but each new member may have just one mentor.

The club has many committees that the members can join. Each committee has a club member who is the chairperson. The club members can join as many committees as they would like (or none at all) and each committee has at least one but typically 20 to 25 members on it. Each committee has a name and a mission.

The focus of the club is the events that they put on. Events are put on by at least one committee but sometimes several committees work together on a single event. Each event can benefit several charities, or it may not benefit any charity at all. In the case when several charities benefit from one event the club needs to know what percentage of the money raised at the given event goes to which charity. For example, the Fall Ball benefited Habitat for Humanity and it received 50 percent of the money raised, while MADD and Metropolitan Ministries only received 25 percent of the money from that ball. Alternatively, the Spring Rodeo Roundup may split the money it raises evenly between Big Brother/Big Sister and Metropolitan Ministries. For each charity, they keep the charity’s name, address, a contact person's name, their telephone number, a fax number, and a short two to three-sentence write-up on what the charity does.

Each event is also given a name, a date and time it will occur and a location at which it will occur. The club also likes to track what mission of the club the event falls into (i.e. family unity, environmental clean-up, faith, education, or the arts). Each event can have many missions, which can be the same as the committee or committees that are sponsoring them.

Extra credit - The following two paragraphs can be modeled for extra credit. There is no partial credit on this portion of the assignment, and it is worth an additional 20 points on this assignment grade. There are two sample figures attached to the end of the assignment to help you with this part of the assignment.

Because the Junior Club is a national organization, it generally puts on events that are also done by other branches of this same national organization. The national headquarters sells a variety of items to their Junior Clubs throughout the United States. The club wants to track all the items that headquarters sells, what the items name is, the current price, and the number of that item included in one order (i.e. if they order 8-inch napkins, they would want to know that each order of napkins contains 50 napkins).

Whenever the club makes an order, it may include many different items in it. The club will track the order number and date for each order, which event that order is for and then the item number, and quantity ordered for each order line item on the order (see the attached order for a detailed example). When a shipment comes in it includes the original order number on it, the shipment number given to it and the date the shipment arrived. As each shipment may have many items on it, the club also needs to track the item number and the amount of each item received.

Requirements

· Build the conceptual model for the Juniors database represented as an Entity Relationship Diagram using SmartDraw online showing entities, relationships, minimum and maximum cardinalities, identifiers and at least two attributes for each entity (including entity’s identifier)

· Add associative entities (see definition in slides and textbook) when needed identifying their relevant attributes; use a minimum of two attributes per each associative entity.

· You may list the assumptions in the model, if necessary. Assumptions must be made only if they are not clearly defined in the above description. You can include the assumptions on the model using SmartDraw text tool. A list of unnecessary assumptions will be ignored

· Use the same model notations used in the textbook and slides and not from other courses or your own making. From within SmartDraw, choose Software Design then Entity Relationship Diagram template.

· If you decide to do the Extra Credit, then make a copy of your original diagram and add the re CS 350—Database Management Systems Fall 2024Matlab quisite entity types and relationships as described in the Extra Credit narrative.

Deliverable:

Upload this to Assignment #1 in Brightspace—from within SmartDraw, export the sdr file as a pdf file

If you do the Extra Credit, submit an additional pdf file only.

At the top of the page, write or type:

Your Name

Assignment #1

Date of the assignment

Grading

This is an individual assignment which means that your solution is entirely your own. Allow enough time to work on the assignment—for these assignments, assignments submitted late without prior notice will not be accepted.

There is no pre-grading review! You can ask clarification questions in-person during office hours or via email; I will only evaluate your solution once after the deadline. Multiple solutions can be uploaded to Brightspace, but only the last solution will be graded.

This assignment is based on Chapter 2 of the textbook and associated video.

#	Criteria	Points
1	Understanding of ERD Concepts · Diagram demonstrates understanding of entities, attributes, relationships, and cardinality and accurately defines key terms related to ERDs	15
2	Entity Identification · Diagram identifies relevant entities based on the given scenario or problem and includes primary keys for each entity.	15
3	Attribute Specification · Diagram lists attributes for each entity and includes data types and constraints (e.g., string, integer, etc.).	15
4	Relationship Definition · Diagram defines relationships between entities (one-to-one, one-to-many, many-to-many) and specifies cardinality (optional, mandatory).	20
5	Completeness and Correctness · Diagram ensures all relevant entities and relationships are included and avoids redundancy and inconsistency.	20
6	Diagram Clarity · Diagram creates a clear and organized ERD using appropriate symbols (boxes, diamonds, lines) and labels entities and relationships clearly