Student: Stanley

Sql, Er Diagrams,Relationship Table

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. Country Representative ACM Chapter Women Youth Students Veterans Coordinator Coordinator Coordinator Coordinator Coordinator State Chapters State Chapters State Chapters State Chapters State Chapters City Chapters 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, 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). Dr. Isaac Gang – CSCI 340 Final Project

Budget: $80.00

Due on: April 30, 2020 00:00

Posted: 5 months ago.

Answers (0)