Database project

Dr. Isaac Gang – CSCI 340 Final Project

CSCI340 – Introduction to Database

Final Team Project (100 Pts) – ACM Database

Due Date at 11:59 PM Central

(MyLeo & in-class presentation)

Topic (s): Various

The Association of Computing Machinery (ACM), one of the largest computing

associations in the world, is having difficulty maintaining its membership information

because its information system is old and no longer reflects its growing needs. The

following is a typical national organizational hierarchy of the ACM in the US.

As can be seen above, the ACM is big organization with a complex leadership structure.

The organization currently has chapters in 17 states. These are Texas, Tennessee,

Country

Representative

ACM Chapter

Coordinator

Veterans

Coordinator

Youth

Coordinator

Students

Coordinator

Women

Coordinator

State Chapters

State Chapters

State Chapters

State Chapters

State Chapters

City Chapters

Dr. Isaac Gang – CSCI 340 Final Project

Missouri, Nebraska, Minnesota, Iowa, South Dakota, North Dakota, New York, District

of Columbia, Alaska, Maine, Washington, Colorado, Arizona, California, and Utah.

In order to be an ACM member, you apply for ACM membership in the city or state

where you reside. Once membership is granted and membership dues are paid, you have

a full ACM membership with all of its privileges. In addition, it is possible to apply for a

sub-membership. There are four sub-memberships types available. These are student,

youth, veteran, and women. Each of the four sub-entities has its own chapters in the 17

states as well. Because each sub-membership comes with its perks, there are additional

membership subscription fees to become a member, but you have to be a full ACM

member before you can qualify for a sub-membership. Also, some members are

employed in the offices of the Representative and the Chapter Coordinator and may be

required to pay occasional minimal dues.

Reporting mechanism are upward. This means the lowest entity reports to the one above

it. To be specific, ACM city chapters report to their state chapter leadership. For instance,

state leaders for students, youth, women, and veteran chapters report to their national

coordinators; ACM state chapter chairman/chairwomen reports to the ACM Chapter

Coordinator; the ACM Chapter Coordinator reports to the Country ACM Representative

– who also receives reports from the student, youth, women, and veteran Coordinators.

However, other relationships – aside from reporting, are possible both ways.

ACM maintains the following data (attributes) from each member: Full name, ID

number, position, address, age, country, state, county, citizenship, Other Citizenship,

tribe, region, date of birth, date of joining, university attended, highest degree earned,

veteran status, marital status, whether they work in the office of the Representative or

not, whether they work in the office of the Chapter Coordinator or not, and sub-

membership.

Having been given the necessary information by the ACM IT manager, who hired you to

develop a robust and efficient relational database to address the organizational need, it is

up to you to come up with the appropriate design choices for the ACM national

organization’s database. However and while design choices are left to each team’s

discretion, the following minimum must be addressed and reflected in your final

relational database deliverables:

1. Analysis (10 pts): You must analyze the requirements and the show your analysis, results and the final decision

2. ER Diagram (10 pts): You must have ER diagrams, showing the necessary relationships – including cardinality, and modality, that you have deduced from

the organizational chart and description.

3. Redundancy (10 pts): You must make sure that you minimized if not eliminate it.

Dr. Isaac Gang – CSCI 340 Final Project

4. SQL (20 pts): Conduct and document the following queries during your test.

a. List all the ACM members who also work in the office of the Representative

b. List all the ACM members who also work in the office of the Chapter Coordinator

c. List all the members who live in Texas d. List all the members who live in Texas and are Indians e. List all the members who hold PhDs f. List all the members who also hold youth membership g. List all the members between the ages of 18 and 35 h. List all the members who are Americans i. List all the members who have a dual citizenship of America and South

Sudan

j. List all the members who join ACM in 2018 k. Join several tables

5. Logical Design (10 pts). Show your ER-Diagram before and after converting them to relational Database.

6. Include in the project whatever else you think will make your database robust and useful (5 pts)

7. In a one page document, describe the various relationships that you were able to identify (5 pts)

8. Finally, write a ReadMe document explaining what you have done, your design choices, and how your database works (10 pts).

9. Make a 20 minutes PowerPoint presentation in class overviewing your project and demonstrating your database (20 pts).