Please convert the following ER Diagram to Sql satements Use this template to cr

Question

Please convert the following ER Diagram to Sql satements

Use this template to create the sql statements

CREATE TABLE TableName

( AttributeName DataType NOT NULL DEFAULT  ,

CONSTRAINT PrimaryKeyConstraintName PRIMARY KEY (AttributeName which is primary key),

CONSTRAINT ForeignKeyConstraintName

    FOREIGN KEY ( AttributeName which is foreign key )

);

Please use whatever datatype you feel thats appropriate but if possible stick with number, int, char, varchar2 and date

Here is the Member table that ive already created, please follow closely to this format.

CREATE TABLE Member

(
Member_ID INT ,
FirstName VARCHAR2(50) NOT NULL ,  
LastName VARCHAR2(50) NOT NULL ,
DOB DATE NOT NULL ,
Gender CHAR(10) NOT NULL ,
Address VARCHAR2(100) NOT NULL ,
Mobile VARCHAR2(10),
HomePhone VARCHAR2(10),
Email VARCHAR2(50) NOT NULL ,
HealthCondition VARCHAR2(50) NOT NULL ,
EContactName VARCHAR2(50) NOT NULL ,
EContactMobile VARCHAR2(50) NOT NULL ,
EContactRelationship VARCHAR2(50) NOT NULL ,
AttendanceStatus CHAR(1),
ParticipationStatus CHAR(1),

CONSTRAINT PK_Member_Member_ID PRIMARY KEY (Member_ID)

);

Membership ID Voting Right Ewents Attendance Event Attendance ID Member Membership Class Approved by Exercise Location Member ID Member ID Event D Membership Duration Event ID Event Name Event Date Family Acknowledgement Name Family Acknowledgement Stalus Family Acknowledgement Signature Event Photo Location Health Condition Emergency Contact Event Organiser History Family Acknowedgement Date Event Organiser ID K Member ID FM Event ID Emergency Contact Family Acknowledgement Relationship Emergency Contact Relationship Membership History Membership_History ParticipationStatus FK Member ID Mambership iD Course Couple Discoun Committee Member History Course ItD Committee Member Excercise Type Course Location Payment Payment ID Membarship History FK Member ID ype ID Training Assignment Member JD AttendanceDate Tim FK Trainer ID Committee Member Type Payment Date Payment Terms Payment Status Account S BarCode Committee Member Type ID tabus

Explanation / Answer

Answer:

The following are the tables for the given ER-Diagram.

MEMBER table was already given. Therefore the remaining tables are as follows:

CREATE TABLE MEMBERSHIP(

MEMBERSHIP_ID VARCHAR2(50) PRIMARY KEY,

VOTING_RIGHTS VARCHAR2(50) NOT NULL,

START_DATE DATE NOT NULL,

MEMBERSHIP_CLASS VARCHAR2(50) NOT NULL,

APPROVED_BY VARCHAR2(50) NOT NULL,

EXERCISE_LOCATION VARCHAR2(50) NOT NULL,

NOMINEE VARCHAR2(50) NOT NULL,

MEMBERSHIP_DURATION DATE NOT NULL,

FEE_TYPE CHAR(1) NOT NULL,

FEE_SCHEDULE DATE NOT NULL,

AMOUNT NUMBER(50) NOT NULL,

FAMILY_ACKNOWLEDGEMENT_NAME VARCHAR2(50) NOT NULL,

FAMILY_ACKNOWLEDGEMENT_STATUS CHAR(1) NOT NULL,

FAMILY_ACKNOWLEDGEMENT_SIGNATURE_STATUS CHAR(1) NOT NULL,

FAMILY_ACKNOWLEDGEMENT_DATE DATE NOT NULL,

FAMILY_ACKNOWLEDGEMENT_RELATIONSHIP VARCHAR2(50) NOT NULL,

CONSTRAINT PK_MEMBERSHIP PRIMARY KEY(MEMBERSHIP_ID)

);

CREATE TABLE MEMBERSHIP_HISTORY(

MEMBERSHIP_HISTORY_ID VARCHAR2(50) NOT NULL,

MEMBER_ID VARCHAR2(50) NOT NULL,

MEMBERSHIP_ID VARCHAR2(50) NOT NULL,

START_DATE DATE NOT NULL,

END_DATE DATE NOT NULL,

COUPLE_DISCOUNT_STATUS CHAR(1),

CONSTRAINT PK_MEMBERSHIP_HISTORY_ID PRIMARY KEY(MEMBERSHIP_HISTORY_ID),

CONSTRAINT FK_MEMBERSHIP_HISTORY_ID FOREIGN KEY(MEMBER_ID,MEMBERSHIP_ID)

);

CREATE TABLE PAYMENT(

PAYMENT_ID VARCHAR2(50) NOT NULL,

MEMBERSHIP_HISTORY_ID VARCHAR2(50) NOT NULL,

COURSE_ATTENDENCE_ID VARCHAR2(50) NOT NULL,

PAYMENT_METHOD VARCHAR2(50) NOT NULL,

PAID_BY VARCHAR2(50) NOT NULL,

PAYMENT_DATE DATE NOT NULL,

PAYMENT_TERMS VARCHAR2(50) NOT NULL,

PAYMENT_STATUS CHAR(1) NOT NULL,

ACCOUNT_STATUS CHAR(1) NOT NULL,

AMOUNT NUMBER(50) NOT NULL,

CONSTRAINT PK_PAYMENT_ID PRIMARY KEY(PAYMENT_ID),

CONSTRAINT FK_PAYMENT_ID FOREIGN KEY(MEMBERSHIP_HISTORY_ID,COURSE_ATTENDENCE_ID)

);

CREATE TABLE COMMITEE_MEMBER_HISTORY(

COMMITEE_MEMBER_HISTORY_ID VARCHAR2(50) NOT NULL,

MEMBER_ID VARCHAR2(50) NOT NULL,

COMMITEE_MEMBER_TYPE_ID VARCHAR2(50) NOT NULL,

START_DATE DATE NOT NULL,

END_DATE DATE NOT NULL,

CONSTRAINT PK_COMMITEE_MEMBER_HISTORY_ID PRIMARY KEY(COMMITEE_MEMBER_HISTORY_ID),

CONSTRAINT FK_COMMITEE_MEMBER_HISTORY_ID FOREIGN KEY(MEMBER_ID,COMMITEE_MEMBER_TYPE_ID)

);

CREATE TABLE COMMITEE_MEMBER_TYPE(

COMMITTEE_MEMBER_TYPE_ID  VARCHAR2(50) NOT NULL,

ASSOCIATION_COMMITTE_PERSON VARCHAR2(50) NOT NULL,

CONSTRAINT PK_ COMMITEE_MEMBER_TYPE_ID PRIMARY KEY(COMMITTEE_MEMBER_TYPE_ID)

);

CREATE TABLE EVENTS_ATTENDENCE(

EVENTS_ATTENDENCE_ID VARCHAR2(50) NOT NULL,

MEMBER_ID VARCHAR2(50) NOT NULL,

EVENT_ID VARCHAR2(50) NOT NULL,

CONSTRAINT PK_EVENTS_ATTENDENCE_ID PRIMARY KEY(EVENTS_ATTENDENCE_ID),

CONSTRAINT FK_EVENTS_ATTENDENCE_ID FOREIGN KEY(MEMBER_ID,EVENT_ID)

);

CREATE TABLE EVENT(

EVENT_ID VARCHAR2(50) NOT NULL,

EVENT_NAME VARCHAR2(50) NOT NULL,

EVENT_DATE DATE NOT NULL,

EVENT_TIME TIME NOT NULL,

EVENT_PHOTO_DRIVE_LOCATION VARCHAR2(50) NOT NULL,

CONSTRAINT PK_EVENT_ID PRIMARY KEY(EVENT_ID)

);

CREATE TABLE EVENT_ORGANISER_HISTORY(

EVENT_ORGANISER_ID VARCHAR2(50) NOT NULL,

MEMBER_ID VARCHAR2(50) NOT NULL,

EVENT_ID VARCHAR2(50) NOT NULL,

NOTE VARCHAR2(50) NOT NULL,

CONSTRAINT PK_EVENT_ORGANISER_HISTORY_ID PRIMARY KEY(EVENT_ORGANISER_ID),

CONSTRAINT FK_EVENT_ORGANISER_HISTORY_ID FOREIGN KEY(MEMBER_ID,EVENT_ID)

);

CREATE TABLE COURSE(

COURSE_ID VARCHAR2(50) NOT NULL,

EXERCISE_TYPE VARCHAR2(50) NOT NULL,

COURSE_LOCATION VARCHAR2(50) NOT NULL,

CONSTRAINT PK_COURSE_ID PRIMARY KEY(COURSE_ID)

);

CREATE TABLE TRAINING_ASSIGNMANT(

TRAINING_ASSIGNMANT_ID  VARCHAR2(50) NOT NULL,

TRAINER_ID VARCHAR2(50) NOT NULL,

COURSE_ID VARCHAR2(50) NOT NULL,

START_DATE DATE NOT NULL,

END_DATE DATE NOT NULL,

CONSTRAINT PK_TRAINING_ASSIGNMANT_ID PRIMARY KEY(TRAINING_ASSIGNMANT_ID),

CONSTRAINT FK_TRAINING_ASSIGNMANT_ID FOREIGN KEY(TRAINER_ID,COURSE_ID)

);

CREATE TABLE COURSE_ATTENDENCE(

COURSE_ATTENDENCE_ID VARCHAR2(50) NOT NULL,

MEMBERSHIP_ID VARCHAR2(50) NOT NULL,

ATTENDENCEDATE_TIME DATETIME NOT NULL,

BARCODE VARCHAR2(100) NOT NULL,

COURSE_ID VARCHAR2(50) NOT NULL,

CONSTRAINT PK_COURSE_ATTENDENCE_ID PRIMARY KEY(COURSE_ATTENDENCE_ID),

CONSTRAINT FK_COURSE_ATTENDENCE_ID FOREIGN KEY(MEMBERSHIP_ID,COURSE_ID)

);

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.