Database

RACI Matrix:

R – Responsible

A – Accountable

C – Consult

I – Inform

To make any form of information more useful, easy to access and manage the data efficiently many organizations store information or data in their respective database system. At the present context, almost anything that we use are technology based and require a some form of database management system to manage large amount of data in organized way so that it can be accessible whenever required.

As a part of database project, our team was assigned to manage/maintain chapters and membership information and their relations by one of the largest computing associations in the world, The Association of Computing Machinery (ACM). Our main task of the project was to update the database system and store its members and complex leadership components of the organization and connect all the entities with each other by their relations to each other and store the data so that it could be retrieved in the future.

To complete the project with productive outcome our team members were each assigned with respective tasks as shown in the Responsibility Assignment Matrix and Google Docs was great platform to communicate, assemble our inputs and organize all the information. In addition, Microsoft Office was used to create documents and slides for the presentation which elaborates on the analysis, design and relationships of members and chapters. Different online platforms were part of the project and served as great tools necessary for the project. Draw.io was used to create ER diagrams . Our team created 000webhost account to further built and manage the database system. phpMyAdmin and MySQL was used to build our database and store all the data related to ACM’s members and chapters and its primary key, foreign keys and attributes.

ANALYSIS

As our project team was assigned to manage/maintain chapters and membership information and their relations by one of the largest computing associations in the world, The Association of Computing Machinery (ACM), the initial analysis on the project is to determine what are the main components and how the relational database among stored items of information. According to our project team analysis, The Association of Computing Machinery (ACM) the main components of the assigned project can be classified into two main entities which are members and chapters.

To become a member of ACM, a client must apply for membership and once the payment is received by the organization, a client becomes ACM member. Once a client is a member of the association, there is also sub-membership provided by the association. Sub-membership consists of Student, Youth, Veteran and Women.

The Association of Computing Machinery (ACM) mainly has two types of chapter which are state chapters and city chapters and as our entity relationship diagram below suggests, according to the entities ranking each entity is bound to report to its higher ranking entities. Our entity relationship diagram shows that City Chapter reports to State Chapter, State Chapter reports to National Coordinator and Country Representative respectively.

RESULT AND FINAL DECISION

After reviewing the analysis, our team created a final ER – diagram. Within membership entity of the ACM, our entity relationship diagram is comprised of Members, Memberships, Sub Memberships and Sub Member types. In addition, within chapters’ entity of the ACM, our entity relationship diagram is comprised of City, State, State Chairman, National Coordinator and Country Representative. Both membership and chapters entities are connected with each other and satisfies the specifications needed for successful database management.

According to the requirements, Membership details are saved with Members details. So, if Members has multiple memberships, Member details are repeated more than one time with Membership details. This creates redundancy. Members are working in one or more offices so according to the requirements these details are saved in the member’s tables so that also creates redundancy. For each sub-membership we need to store the membership details against which sub-memberships are purchased.

After a membership is provided, a member can apply for a sub-member. To qualify for a sub-member, a member has to be a full member of ACM and has to pay all the fees. An individual can apply for membership in the city. There is a dependent entity called Membership in between Members and City, which records the member’s relation to the City chapters, which contains the member primary key and city primary key. One or many members can be member of a city chapter. Therefore, the relationship between member and city is one-to-many binary relationship.

Likewise, there are four different sub-memberships available. They are youth, women, veteran, and student. A member can be a sub-member of either of the sub-membership type. The relation between members and sub-membership is one-to-many binary relationship.

Since member has to apply through the city they reside, and a state has a control over its cities chapters. The relationship between city and state is a binary one-to-many because a state can have many cities.

The relationship between the chairman and the national coordinator. Each of the chairman report to one national coordinator and a national coordinator supervises all the chairman of a specific sub membership from every state. Therefore, the relationship between the State Chairman and the National Coordinator is one-to-many binary relationship.

Similarly, all the state chairman for students, youth, women, and veteran chapters’ report to their national coordinator. Then all of the national coordinators report to the country representative. Therefore, the relationship between them is one-to-many.

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

SELECT * FROM `Members` WHERE `Works In The Office of Representative`='Yes';

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

SELECT * FROM `Members` WHERE `Works In The Office of Chapter Coordinator`='Yes';

3. List all the members who live in Texas.

SELECT * FROM `Members` WHERE `State`='TX';

4. List all the members who live in Texas and are Indians.

SELECT * FROM `Members` WHERE `State`='TX' and `Tribe`='Indians';

5. List all the members who hold PhDs.

SELECT * FROM MEMBERS WHERE Highest_Degree='PhD';

6. List all the members who also hold youth membership.

SELECT * FROM `Members` WHERE `Sub Membership`='Youth';

7. List all the members between the ages of 18 and 35.

SELECT * FROM MEMBERS WHERE Age BETWEEN 18 AND 35;

8. List all the members who are Americans.

SELECT * FROM MEMBERS WHERE Citizenship= 'USA';

9. List all the members who have a dual citizenship of America and South Sudan.

SELECT * FROM MEMBERS WHERE Citizenship= 'American' AND Other Citizenship= 'South Sudan';

10. List all the members who join ACM in 2018.

SELECT * FROM `Members` WHERE Year(`Date_joined`)=2018;

11. Join several tables.

SELECT National_Coordinator.Telephone, State_Chairman.Name FROM State_Chairman INNER JOIN National_Coordinator ON State_Chairman.Coordinator_ID=National_Coordinator.Coordinator_ID