database project (computer)

Contribution

Isak Angerstig:

· Analysis

· Redundancy

· Design

· Database

· Proofing

Sarah Kizer:

· Analysis

· E-R Diagram

· Redundancy

· ReadMe Document

· Proofing

Ruth Leach:

· Analysis

· E-R Diagram

· Redundancy

· SQL

· PowerPoint

· Proofing

Jennifer Melendez:

· Analysis

· E-R Diagram

· Redundancy

· SQL

· PowerPoint

· Proofing

Austin Young:

· Analysis

· E-R Diagram

· Redundancy

· Design

· Database

· Proofing

Analysis

Project Description

Data is an extremely important asset and corporate resource in any aspect of the world. Without data, our society as a whole would be completely damaged. So it goes without saying that if there is not a sufficient way to keep records and properly manage of all this crucial data as a whole we would be in trouble. This is where databases come into play. This project is a cumulative representation of what we have learned this semester. It is where we take the bits and pieces and put them all together. Our main goal of the project was to take the Association of Computing Machinery (ACM), one of the largest computing associations in the world, and create a fully functioning, well-structured database based on the properties of this organization. The organization currently has seventeen chapters across the United States. Our goal for this project is to create a database to track all of the members, their attributes, and the relationships between the flow of leadership. The idea is that this will allow users of the database to insert, store, retrieve, and delete data efficiently.

Software Used

Google Drive was the most important software used in our group. It was the main source for completing this project. It was so helpful for us because it allowed us to all work on the project at the same time when we were unavailable to physically meet. It was also useful in the fact that a group member could make changes and then other members of the group could go back and specifically see changes made in order to review and contribute to the project. We utilized Google Docs, Google Slides, and Draw.io. Google Docs was used to write this document as well as to make our functional dependencies. Google Slides was used to make our PowerPoint Presentation. Finally, Draw.io was used to build our Entity-Relationship Diagram.

Database Design

· MySQL

· dbfiddle.com

· generatedata.com

Becoming a Member and Reporting Process

After going through the application process and being accepted as a member, the applicant’s information is inputted into the “Member” table. This is when they will be assigned a member ID and assigned to the appropriate city and state chapter based on the address they used on their application. At this point, members have the option to join sub-memberships. Their options of sub-memberships are women, youth, student, and veteran. Members have the ability to obtain more than one sub-membership. Sub-memberships can come with additional fees. These fees must be paid off before they are inserted into the “SubMembership” table. Each state has a chairman or chairwoman assigned to it. The person in that position receives all information from members of the organizations, including their sub-memberships. The state chair from each state chapter is responsible for reporting to the national coordinator. There are eight national coordinators that are separated into four groups. There are two coordinators per North, South, East, and West region. Finally, national coordinators report to the country representative. There are four country representatives. The country representatives work as one to ensure that decisions are being made in the best interest of the ACM organization.

Research Findings

Before applying for Association of Computing Machinery (ACM) Memberships, potentially members must apply in the city or state they are in. Once they paid membership fees and become a member of ACM, they have full ACM membership with all its’ privileges. Members have the option to apply for sub-memberships, which could come with additional fees. The four types of sub-memberships that are available are: Student, Youth, Veteran, and Women. Some members are employed in the offices of the Representative and the Chapter Coordinator and they may be required to pay occasional minimal dues.

The way ACM reporting mechanism works is upward. The lowest entity reports to the one above it. ACM maintains data attributes from each member: Full name, ID number, position, address, age, country, state, country 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-member.

Results and Final Decisions

1

Functional Dependencies

MEMID ----> CITYCHAPID

MEMID ----> memName

MEMID ----> Position

MEMID ----> Address

MEMID ----> County

MEMID ----> Country

MEMID ----> Citizenship

MEMID ----> Other Citizenship

MEMID ----> Tribe

MEMID ----> Region

MEMID ----> Age

MEMID ----> Date of Birth

MEMID ----> Date Joined

MEMID ----> University Attended

MEMID ----> Highest Degree Earned

MEMID ----> State

MEMID ----> City

MEMID ----> Veteran Status

MEMID ----> Marital Status

MEMID ----> Office Rep. Status

MEMID ----> Office Coord. Status

MEMID ----> Membership Fees

MEMID ----> Additional Fees

MEMID ----> Membership Status

CITYCHAPID ----> StCHAPID

CITYCHAPID ----> City

CITYCHAPID ----> State

StCHAPID ----> CHAIRID

StCHAPID ----> State

CHAIRID ----> CoordID

CHAIRID ----> Chair Name

CHAIRID ----> Year of Hire

COORID ----> CountryRepID

COORID ----> Coordinator Name

COORID ----> Year of Hire

CountryRepID ----> Representative Name

CountryRepID ----> Year of Hire

CountryRepID ----> Office Number

MEMID, CITYCHAPID ----> StCHAPID

MEMID,CITYCHAPID,StCHAPID ----> CHAIRID

MEMID,CITYCHAPID,StCHAPID,CHAIRID ----> COORDID

MEMID, CITYCHAPID,StCHAPID,CHAIRID,COORDID ----> CountryRepID

Relationships

One of the first relationships to establish was the one between the “Member” table and the “SubMembership” table. A member of the organization can hold a general membership along with sub-memberships. A member can have multiple sub-memberships seeing that the options are student, youth, women, and veteran it is very possible for a person to be more than one of those and therefore have the ability to obtain more than one sub-membership. We have included an ACM general membership in our sub-membership categories because that is how they are represented at the national level. Therefore, the cardinality of the relationship on both ends is many and the modality on both ends is one.

The next relationship we focused on was the relationship between the “Member” table and the “City Chapter” table. Due to the way the reporting process goes, our group decided it was important to focus on building the E-R Diagram and the relationships between entities from the lower level up.Therefore, in order to be represented by a state chapter they have to be apart of a city chapter. Each general membership and sub-membership has its own city chapter, so there are five chapters per city. As a result, the cardinality of this relationship is many on the “Membership” side and one on the “City Chapter” side. The modality is one on both ends.

According to the flow of reporting, our next focus was on the relationship between the table “City Chapter” and the “State Chapter” table. A member is not directly related to a state chapter, so the only way a member communicates to their state chapter is through their city chapter. A city can only belong to one state while a state can hold many cities. Thus, the relationship between these two entities is a binary one to many.

Furthermore, the relationship between the “State Chapter” table and the “State Chair” table is binary one-to-one. The reason for this is because there is one state chairman or chairwoman that the state chapter reports to.

The next relationship is the one between the “State Chair” table and the “National Coordinator” table. The cardinality of the relationship is many on the “State Chair” side and one on the “National Coordinator.” The modality is one for both sides.

Finally, the relationship between the “National Coordinator” table and the “Country Representative” table is binary one-to-one.

Entity-Relationship Diagram

The Association of Computing Machinery (ACM) city chapters report to their state chapter leadership. State leaders for sub-members, 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 student, youth, women, and veteran Coordinators. Other relationships aside from reporting are possible both ways.

SQL Statements

a. a.) ACM members in office of representatives

SELECT MEMID, memName

FROM Member

WHERE OfficeRepStatus = 1;

b. b.)ACM members in office of Chapter coordinator

SELECT MEMID, memName

FROM Member

WHERE OfficeCoordStatus = 1;

c.) List all the members who live in Texas

SELECT Member.MEMID, memName

FROM Member, SubMembership, CityChapter, StateChapter

WHERE Member.MEMID=SubMembership.MEMID

AND SubMembership.CITYCHAPID=CityChapter.CITYCHAPID

AND CityChapter.StCHAPID=StateChapter.StCHAPID

AND State=’Texas’;

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

SELECT Member.MEMID, memName

FROM Member, SubMembership, CityChapter, StateChapter

WHERE Member.MEMID=SubMembership.MEMID

AND SubMembership.CITYCHAPID=CityChapter.CITYCHAPID

AND CityChapter.StCHAPID=StateChapter.StCHAPID

AND State=’Texas’

AND OtherCitizenship=’Indian’;

e.) List all the members who hold PhDs

SELECT MEMID, memName

FROM Member

WHERE HighestDegreeEarned=’PHD’;

f.) List all the members who also hold youth membership

SELECT Member.MEMID, memName

FROM Member, SubMembership

WHERE Member.MEMID=SubMembership.MEMID

AND SubMem=’Youth’;

g.) List all the members between the ages of 18 and 35

SELECT Member.MEMID, memName

FROM Member

WHERE Age BETWEEN 18 AND 35;

h.) List all the members who are Americans

SELECT Member.MEMID, memName

FROM Member

WHERE Citizenship = 1;

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

SELECT Member.MEMID, memName

FROM Member

WHERE Citizenship = 1

AND OtherCitizenship = ’South Sudan’;

j.) List all the members who joined ACM in 2018

SELECT Member.MEMID, memName

FROM Member

WHERE DateJoined BETWEEN ‘2018-01-01’ AND ‘2018-12-31’;

k.) Join several tables

SELECT statechair.chairid, statechair.chairname, statechair.officeNumber,

NationalCoordinator.CoordID, NationalCoordinator.YearOfHire, NationalCoordinator.OfficeNumber,

CountryRepresentative.CountryRepID, CountryRepresentative.RepresentativeName,

CountryRepresentative.YearOfHire, CountryRepresentative.OfficeNumber

FROM StateChair, NationalCoordinator, CountryRepresentative

WHERE StateChair.COORDID = NationalCoordinator.COORDID

AND NationalCoordinator.CountryRepID = CountryRepresentative.CountryRepID;

ACM Database and Tables

Member Table image 1 and 2:

State Chair table:

State Chapter table:

National Coordinator table:

City Chapter table:

SubMembership table:

Country Representative table: