sql, er diagrams,relationship table

Sonythapa

Presentation71.pptx

Home >Computer Science homework help >sql, er diagrams,relationship table

INTRODUCTION TO DATABASE :CSCI340

FINAL GROUP PROJECT

LAL KUMAR

NILESH

MILAN

SUSHIL

MOHAN

Table of contents:

Backgrounnd

Analysis

ER-diagrams

Redundancy

Relationship

Functional dependencies

Relational table

SQL

Conclusion

Background:

The essence of data management system was realized long ago. Humans have used different approaches to record data throughout the history. Now, they are able to develop complex system that allows systematic and scientific ways for input, storage, security and retrieval of the data.

Now a days, different organizations have different approaches of data management system based on organization’s goals.

Here, we’re creating a systematic and effective database system for an organization named Association of Computing Machinery(ACM).

We have followed the detailed process of analysis, logical design, diagrams and relationship to come up with best possible database system to address the organizational need.

BASIC APPROACH AND PROGRAMS:

Physical meeting: we met number of times to discuss and have clear objective with the kind of database we will design.

Group Chats: It helped a lot for trying several approaches and come up with new ideas we can incoperate in our project.

Design: For designing of hiearchy tables, ER diagrams we used draw.io

Data: we used dummy data to test the accuracy of the program.

MySQL: For testing dummy datas, we used MySql.

ReadMedocument: It helped in explaining complex working module in form of simple words.

Analysis:

VERDICT

Critically analyzing the case of ACM (Association for computer Machinery), Numerous elements have come to light that have direct association with the main constituent of the data that the company required. After a thorough case study of the project we concluded that among the entities provided directly by the case are the member, chapters (state, city), country representative, chapter coordinator, and sub memberships. Establishing a working relationship among these entity into a relational database will solve all the issues related to maintaining the records for ACM database.

MEMBERS

To begin the journey as ACM member, first people must apply for membership in the city or the state where they reside. People become a full ACM membership with all of its privileges after the membership granted and membership dues are paid. Then finally by applying and paying the minimal fees, people have opportunity for sub membership, and for working in the office of representative and the chapter coordinator.

Analysis:

CITY_CHAPTERS

In case there is no city chapter present in the city that a member lives in they become member of the nearest city chapter available, or the state chapter. There may be more than one city chapter in a state, and all the city chapters need to report to the state chapter leader who will report to state coordinator.

STATE_CHAPTERS

Membership is also granted in the state chapter, and there are state chapters in seventeen different states. ACM state chapters leaders reports to state coordinator who then reports to country representative.

SUB_MEMBERSHIP

Once a member has full time membership, they have opportunity to apply for sub membership in four different categories that comes with a membership fee. The four categories are Youth, Veteran, Women, and Student. Each of the four sub-entities has its own chapters in the 17 states as well. The sub member chapters get reported by the particular sub member type, who then reports to the country representative.

Analysis:

CHAPTER_COORDINATOR

ACM members have the opportunity to be employed in the office of Chapter Coordinator by paying occasional minimal dues. They receive reports from State Chapter which they forward to Country Representative.

COUNTRY_REPRESENTATIVE

ACM members have the opportunity to be employed in the office of Country Representative by paying occasional minimal dues. The country representative acts as the head of all the department of ACM. They receive reports by Chapter Coordinator and the sub membership (Student, Veterans, Youth and Women).

Analysis:

The following diagram illustrates the REPORTING MECHANISM:

RELATIONSHIP:

MEMBERS STATE (BINARY ONE-TO-MANY)

MEMBERS CITY (BINARY ONE-TO-MANY)

CITY STATE (BINARY ONE-TO-MANY)

MEMBERS SUBMEMBERSHIP (BINARY ONE-TO-MANY)

STATE SUBMEMBERSHIP (BINARY ONE-TO-MANY)

CITY SUBMEMBERSHIP (BINARY ONE-TO-MANY)

STATE CHAPTER CHAPTER COORDINATOR. (BINARY ONE-TO-MANY)

CHAPTER COORDINATOR COUNTRY REPRESENTATIVE (BINARY ONE-TO-MANY)

SUBMEMBERSHIP COUNTRY REPRESENTATIVE. (BINARY ONE-TO-MANY)

MEMBERS COUNTRY REPRESENTATIVE. (BINARY ONE-TO-MANY)

MEMBERS CHAPTER COORDINATOR. (BINARY ONE-TO-MANY)

ER-DIAGRAM APPROACHES

REDUNDANCY:

Redundancy

If you store the same data item more than once in a database, that is data redundancy. In database for the maximum efficiency, the same item should be recorded only once. An example of data redundancy would be saving the same file five times to five different disks. Exactly, in the similar manner, the existence of redundancy can be seen in the strategy described in the pdf file and the details provided here for maintaining the membership information of ACM. ACM members are working in more than one office and in order to be working at any office you should have the membership that comes with certain fees. So, it makes sense to save all the details for members at all level of hierarchy. But it creates repetition of same information that creates redundancy. The membership details are repeated for the members who pays extra fees to participate in office of representative, office of coordinator, sub-membership programs. Therefore, considering the growing requirement of the ACM and planning to fix these redundancies, we decided to use relational table strategy for maintaining the membership information of ACM.

FINAL ER-DIAGRAM

FUNCTIONAL DEPENDECY:

Functional Dependencies For Main Entities

member_id->Full_name member_id->position member_id->address member_id->age member_id->country member_id->state member_id->county member_id->citizenship member_id->other_citizenship member_id->tribe member_id->region member_id->date_of_birth member_id->date_of_joining member_id->university_attended member_id->highest_degree_earned member_id->veteran_status member_id->marital_status member_id->if_office_of_rep member_id->if_office_of_coordinator member_id->sub_membership city_id->city_name city_id->telephone city_id->office_address city_id->member_id

state_id->telephone state_id->office_number state_id->city_id state_id->member_id coordinator_id->coordinator_name coordinator_id->year_hire coordinator_id->fees coordinator_id->state_id representative_id->rep_name representative_id->office_number representative_id->year_hire representative_id->fees representative_id->coordinator_id representative_id->sub_membership_id sub_membership_id->member_type sub_membership_id->fees sub_membership_id->member_id

RELATIONAL TABLE

MY SQL

TIME FOR QUESTIONS:

.MsftOfcThm_Accent1_Fill { fill:#4472C4; } .MsftOfcThm_Accent1_Stroke { stroke:#4472C4; }