dbpractice/createTables.sql at master · jasminemp/dbpractice · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
-- CALIFORNIA STATE UNIVERSITY, SAN BERNARDINO
-- DEPARTMENT OF COMPUTER SCIENCE
-- Course: CSE572
-- STUDENT: JOSEPH CARMONA, JASMINE PENA
--
-- UNIVERSITY DATABASE PROJECT
-- CREATION OF TABLES

DROP TABLE DEPARTMENT CASCADE CONSTRAINTS PURGE;
CREATE TABLE DEPARTMENT (
	DEPARTMENTNO		INT NOT NULL,
	MANAGERNO		INT UNIQUE,
	NAME			VARCHAR2(15) UNIQUE NOT NULL,
	PHONENO			CHAR(12) NOT NULL,
	FAXNO			CHAR(12) NOT NULL,
	LOCATION		VARCHAR2(30) NOT NULL,
	CONSTRAINTS DEPARTMENT_PK PRIMARY KEY (DEPARTMENTNO)
);

DROP TABLE COURSE CASCADE CONSTRAINTS PURGE;
CREATE TABLE COURSE (
	COURSENO		VARCHAR2(7) NOT NULL,
	DEPARTMENTNO		INT NOT NULL,
	MANAGERNO		INT UNIQUE NOT NULL,
	STUDENT_TOTAL		INT NOT NULL,
	TITLE			VARCHAR2(30) NOT NULL,
	CONSTRAINTS COURSE_PK PRIMARY KEY (COURSENO)
);

DROP TABLE ACADEMIC_STAFF CASCADE CONSTRAINTS PURGE;
CREATE TABLE ACADEMIC_STAFF (
	STAFFNO			INT NOT NULL,
	NETWORKID		INT UNIQUE NOT NULL,
	DEPARTMENTNO		INT NOT NULL,
	FIRST_NAME		VARCHAR2(15) NOT NULL,
	LAST_NAME		VARCHAR2(15) NOT NULL,
	PHONEEXT		CHAR(5) NOT NULL,
	OFFICENO		CHAR(5) NOT NULL,
	SEX			CHAR(1) NOT NULL,
	SALARY			INT NOT NULL,
	POST			VARCHAR2(15) NOT NULL,
	QUALIFICATIONS		VARCHAR2(50) NOT NULL,
	STREET			VARCHAR2(30) NOT NULL,
	CITY			VARCHAR2(15) NOT NULL,
	STATE			CHAR(2) NOT NULL,
	ZIP			CHAR(5) NOT NULL,
	START_DATE		DATE NOT NULL,
	END_DATE		DATE,
	CONSTRAINTS ACADEMIC_STAFF_PK PRIMARY KEY (STAFFNO),
	CONSTRAINTS ACADEMIC_STAFF_CK CHECK (SEX IN ('M','F'))
);

DROP TABLE MODULE CASCADE CONSTRAINTS PURGE;
CREATE TABLE MODULE (
	MODULENO		CHAR(6) NOT NULL,
	MODULETITLE		VARCHAR2(30) NOT NULL,
	COURSENO		VARCHAR2(7) NOT NULL,
	COORDINATORNO		INT NOT NULL,
	START_DATE		DATE NOT NULL,
	END_DATE		DATE NOT NULL,
	TEXTS			VARCHAR2(100),
	ASSESSMENT_SCHEME	VARCHAR2(200),
	CONSTRAINTS MODULE_PK PRIMARY KEY (MODULENO)
);

DROP TABLE STUDENT CASCADE CONSTRAINTS PURGE;
CREATE TABLE STUDENT (
	MATRICULATIONNO		INT NOT NULL,
	NETWORKID		INT UNIQUE NOT NULL,
	COURSENO		VARCHAR2(7) NOT NULL,
	FIRST_NAME		VARCHAR2(15) NOT NULL,
	LAST_NAME		VARCHAR2(15) NOT NULL,
	STREET			VARCHAR2(30) NOT NULL,
	CITY			VARCHAR2(15) NOT NULL,
	STATE			CHAR(2) NOT NULL,
	ZIP			CHAR(5) NOT NULL,
	DOB			DATE NOT NULL,
	SEX			CHAR(1) NOT NULL,
	LOAN			INT,
	PERFORMANCE		VARCHAR2(200) NOT NULL,
	CONSTRAINTS STUDENT_PK PRIMARY KEY (MATRICULATIONNO),
	CONSTRAINTS STUDENT_CK CHECK (SEX IN ('M','F'))
);

DROP TABLE NEXT_OF_KIN CASCADE CONSTRAINTS PURGE;
CREATE TABLE NEXT_OF_KIN (
	MATRICULATIONNO		INT UNIQUE NOT NULL,
	FIRST_NAME		VARCHAR2(15) NOT NULL,
	LAST_NAME		VARCHAR2(15) NOT NULL,
	STREET			VARCHAR2(30) NOT NULL,
	CITY			VARCHAR2(15) NOT NULL,
	STATE			CHAR(2) NOT NULL,
	ZIP			CHAR(5) NOT NULL,
	PHONENO			CHAR(12) NOT NULL,
	RELATIONSHIP		VARCHAR2(15) NOT NULL
);

DROP TABLE LECTURER_POSITION CASCADE CONSTRAINTS PURGE;
CREATE TABLE LECTURER_POSITION (
	MODULENO		CHAR(6) NOT NULL,
	STAFFNO			INT NOT NULL,
	HOURS_WORKED		INT NOT NULL,
	CONSTRAINTS LECTURER_POSITION_PK PRIMARY KEY (MODULENO, STAFFNO)
);

DROP TABLE SEAT CASCADE CONSTRAINTS PURGE;
CREATE TABLE SEAT (
	MODULENO		CHAR(6) NOT NULL,
	MATRICULATIONNO		INT NOT NULL,
	CONSTRAINTS SEAT_PK PRIMARY KEY (MODULENO, MATRICULATIONNO)
);