I need someone to query my Oracle database

sbevensmtley1955

create_-_copy.txt

Home >Computer Science homework help >I need someone to query my Oracle database

CREATE TABLE club
	(club_Id VARCHAR2(7) PRIMARY KEY,
	club_Name CHAR(30) NOT NULL,
	club_Address VARCHAR2(30) NOT NULL,
	club_Phone VARCHAR2(11) NOT NULL,
	club_Fee NUMBER(5,2) NOT NULL)


CREATE TABLE member
	(member_Id VARCHAR2(11) PRIMARY KEY,
	member_FirstName CHAR(20) NOT NULL,
	member_LastName CHAR(20) NOT NULL,
	member_Address VARCHAR2(50) NOT NULL,
	member_Phone VARCHAR2(11) NOT NULL)


CREATE TABLE subscription
	(member_Id VARCHAR2(11) NOT NULL,
	club_Id VARCHAR2(7) NOT NULL,
	subscription_Date DATE NOT NULL,
	CONSTRAINT subscription_pk PRIMARY KEY (member_Id, club_Id),
	FOREIGN KEY (member_Id) REFERENCES member(member_Id),
	FOREIGN KEY (club_Id) REFERENCES club(club_Id));


CREATE TABLE meeting
	(meeting_Id VARCHAR2(8) PRIMARY KEY,
	meeting_Name VARCHAR2(50) NOT NULL,
	meeting_Date DATE NOT NULL,
	club_Id VARCHAR2(7) NOT NULL,
	FOREIGN KEY (club_Id) REFERENCES club(club_Id));


CREATE TABLE attendance
	(member_Id VARCHAR2(11) NOT NULL,
	meeting_Id VARCHAR2(8) NOT NULL,
	CONSTRAINT attendance_pk PRIMARY KEY (member_Id, meeting_Id),
	FOREIGN KEY (member_Id) REFERENCES member(member_Id),
	FOREIGN KEY (meeting_Id) REFERENCES meeting(meeting_Id));


CREATE TABLE event
	(event_Id VARCHAR2(8) PRIMARY KEY,
	event_Type VARCHAR2(20) NOT NULL,
	event_Name VARCHAR2(20) NOT NULL);


CREATE TABLE rank
	(member_Id VARCHAR2(11) NOT NULL,
	meeting_Id VARCHAR2(8) NOT NULL,
	event_Id VARCHAR2(8) NOT NULL,
	result NUMBER(5,2) NOT NULL,
	CONSTRAINT rank_pk PRIMARY KEY (member_Id, meeting_Id, event_Id),
	FOREIGN KEY (member_Id) REFERENCES member(member_Id),
	FOREIGN KEY (meeting_Id) REFERENCES meeting(meeting_Id),
	FOREIGN KEY (event_Id) REFERENCES event(event_Id));


CREATE TABLE ad
	(ad_Id VARCHAR2(5) PRIMARY KEY,
	ad_Name VARCHAR2(30) NOT NULL);


CREATE TABLE meeting_ad
	(meeting_Id VARCHAR(8) NOT NULL,
	ad_Id VARCHAR2(5) NOT NULL,
	CONSTRAINT meeting_ad_pk PRIMARY KEY (meeting_Id, ad_Id),
	FOREIGN KEY (meeting_Id) REFERENCES meeting(meeting_Id),
	FOREIGN KEY (ad_Id) REFERENCES ad(ad_Id));


CREATE TABLE newspaper
	(newspaper_Id VARCHAR2(7) PRIMARY KEY,
	newspaper_Name VARCHAR2(30) NOT NULL,
	contact_Phone VARCHAR2(11) NOT NULL,
	bookable VARCHAR2(30) NOT NULL);


CREATE TABLE newspaper_ad
	(newspaper_Id VARCHAR(7) NOT NULL,
	ad_Id VARCHAR2(5) NOT NULL,
	CONSTRAINT newspaper_ad_pk PRIMARY KEY (newspaper_Id, ad_Id),
	FOREIGN KEY (newspaper_Id) REFERENCES newspaper(newspaper_Id),
	FOREIGN KEY (ad_Id) REFERENCES ad(ad_Id));