Database table of a tennis club in oracle(query)

 Database table of a tennis club

The following is query of the database of a tennis club with different tables ,their relationships and constraints.

It has following five tables:

1.  PLAYERS
2. TEAMS
3. MATCHES
4. PENALTIES
5. COMMITTEE_MEMBERS

CREATE TABLE PLAYERS(PLAYERNO NUMBER PRIMARY KEY,NAME VARCHAR(20) NOT NULL ,

INITIALS VARCHAR(30) NOT NULL CHECK(INITIALS NOT LIKE '% %' AND INITIALS NOT LIKE '%.%') ,

BIRTH_DATE DATE NOT NULL ,SEX CHAR(1) CHECK(SEX IN ('M','F')) ,JOINED DATE CHECK(JOINED>'31-DEC-1969'),

STREET VARCHAR(20),HOUSENO VARCHAR(20) NOT NULL,POSTCODE NUMBER NOT NULL CHECK(LENGTH(POSTCODE)=6),

TOWN VARCHAR(20) UNIQUE,PHONENO NUMBER NOT NULL CHECK(LENGTH(PHONENO)=10),LEAGUENO NUMBER UNIQUE NOT NULL);

CREATE TABLE TEAMS(TEAMNO NUMBER PRIMARY KEY,PLAYERNO NUMBER ,FOREIGN KEY(PLAYERNO) REFERENCES PLAYERS(PLAYERNO),DIVISION VARCHAR(7) CHECK(DIVISION IN ('FIRST','SECOND')));

CREATE TABLE MATCHES(MATCHNO NUMBER PRIMARY KEY,PLAYERNO NUMBER, FOREIGN KEY(PLAYERNO) REFERENCES PLAYERS(PLAYERNO),TEAMNO NUMBER,

FOREIGN KEY(TEAMNO) REFERENCES TEAMS(TEAMNO),WON NUMBER CHECK(WON>=0 AND WON<4),LOST NUMBER CHECK(LOST>=0 AND LOST<4));

CREATE TABLE PENALTIES(PAYMENTNO NUMBER PRIMARY KEY,PLAYERNO NUMBER,FOREIGN KEY(PLAYERNO) REFERENCES PLAYERS(PLAYERNO), PAYMENT_DATE DATE CHECK(PAYMENT_DATE>='01-JAN-1970'),AMOUNT NUMBER CHECK(AMOUNT>0));

CREATE TABLE COMMITTEE_MEMBERS(BEGIN_DATE DATE CHECK(BEGIN_DATE>='01-JAN-1990') ,PLAYERNO NUMBER, FOREIGN KEY(PLAYERNO) REFERENCES PLAYERS(PLAYERNO),END_DATE DATE CHECK(END_DATE>='01-JAN-1990'),POSITION VARCHAR(30) NOT NULL,PRIMARY KEY(PLAYERNO,BEGIN_DATE));