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));


Comments

Post a Comment

Popular posts from this blog

how to compile multiple file in java using single command

Some times we need to compile multiple files with a single command. Though most programmer use IDe but sometimes we have command lines only. If your case is like that then you can use any of the following methods- 1. Specify all the files manually javac File1.java File2.java File3.java 2. Compile all .java file in the directory javac *.java This will compile all the Java files in the current directory.
Read more

Ternary operator and Nested Ternary operator

Ternary operator  Ternary operator which is a conditional operator and, as the name suggests, it takes three operands (parts). Ternary operator is available in most languages, like, C, C++, Java, JavaScript, etc  Syntax:-  variable = condition ? expression1_if_true : expression2_if_false Explanation: condition: This is a boolean expression that evaluates to either true or false. expression1_if_true: This is the value or statement that is executed if the condition is true. expression2_if_false: This is the value or statement that is executed if the condition is false.  Ternary operator is nothing, just a concise way of writing an if-else statement, i.e. the same operation can be achieved using an if-else block as shown below.  if(condition){     variable = value_if_true  }else{     variable = value_if_false  } Example 1 :- C program find max number among two given numbers using ternary operator #include <stdio.h> int mai...
Read more

How to pass argument in main function of java program using command line argument

Image
How to pass argument in main function of java program using command line       argument  There are only one way to pass argument in main() function of java program and that is using command  line .Here is the step by step procedure of how to pass argument using command line. As main function have a formal parameter i.e., args[] which is array of String type and accepts number of arguments passed during the run time of program through command line. This is  illustration of program to calculate the sum of number passed by user as argument in main function. public class CommandLine {   public static void main(String []args)    { int sum=o;       for(int i=0;i<args.length;i++)     {sum=sum+Integer.parseInt(args[i]); /* Here the argument accepted from command line are added one by one.But there is problem as command line arguments are of String type ,Hence we need to convert them before adding wh...
Read more