You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
116 lines
4.7 KiB
116 lines
4.7 KiB
import sqlite3 as sql
|
|
|
|
conn=sql.connect('university_database.db')
|
|
|
|
c=conn.cursor()
|
|
|
|
c.execute("""CREATE TABLE student(
|
|
ID INTEGER PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
dept_name TEXT,
|
|
tot_cred REAL CHECK(tot_cred>=0),
|
|
FOREIGN KEY(dept_name) REFERENCES department(dept_name) ON DELETE SET NULL
|
|
|
|
)""")
|
|
|
|
|
|
c.execute("""CREATE TABLE department(
|
|
dept_name TEXT PRIMARY KEY,
|
|
bulding TEXT,
|
|
buget REAL CHECK(buget>0)
|
|
)""")
|
|
|
|
c.execute("""CREATE TABLE instructor(
|
|
ID INTEGER,
|
|
name TEXT NOT NULL,
|
|
dept_name TEXT,
|
|
salary REAL CHECK(salary>29000),
|
|
FOREIGN KEY(dept_name) REFERENCES department(dept_name) ON DELETE SET NULL
|
|
|
|
)""")
|
|
|
|
|
|
c.execute("""CREATE TABLE section(
|
|
course_id INTEGER,
|
|
sec_id INTEGER ,
|
|
semester TEXT CHECK(semester IN('Fall','Winter','Summer','Spring')),
|
|
year INTEGER CHECK(year>1701 and year<2100),
|
|
building TEXT,
|
|
room_number INTEGER,
|
|
time_slot_id INTEGER,
|
|
PRIMARY KEY(course_id,sec_id,semester,year),
|
|
FOREIGN KEY(course_id) REFERENCES course(course_id) ON DELETE CASCADE,
|
|
FOREIGN KEY(building,room_number) REFERENCES classroom(building,room_number) ON DELETE SET NULL
|
|
|
|
)""")
|
|
|
|
c.execute("""CREATE TABLE course(
|
|
course_id INTEGER PRIMARY KEY,
|
|
title TEXT,
|
|
dept_name,
|
|
credits REAL CHECK(credits>0),
|
|
FOREIGN KEY(dept_name) REFERENCES department(dept_name) ON DELETE SET NULL
|
|
)""")
|
|
|
|
c.execute("""CREATE TABLE classroom(
|
|
building TEXT,
|
|
room_number INTEGER,
|
|
capacity INTEGER,
|
|
PRIMARY KEY(building,room_number)
|
|
)""")
|
|
|
|
c.execute("""CREATE TABLE advisor(
|
|
s_id INTEGER PRIMARY KEY,
|
|
i_id INTEGER,
|
|
FOREIGN KEY(s_id) REFERENCES student (ID) ON DELETE SET NULL,
|
|
FOREIGN KEY(i_id) REFERENCES instructor (ID) ON DELETE CASCADE
|
|
)""")
|
|
|
|
c.execute("""CREATE TABLE prerep(
|
|
course_id INTEGER,
|
|
prerep_id INTEGER,
|
|
PRIMARY KEY(course_id,prerep_id),
|
|
FOREIGN KEY(course_id) REFERENCES course(course_id) ON DELETE CASCADE,
|
|
FOREIGN KEY(prerep_id) REFERENCES course(course_id)
|
|
)""")
|
|
|
|
c.execute("""CREATE TABLE takes(
|
|
ID INTEGER,
|
|
course_id INTEGER,
|
|
sec_id INTEGER,
|
|
semester TEXT,
|
|
year INTEGER,
|
|
grade TEXT,
|
|
PRIMARY KEY(ID,course_id,sec_id,semester,year),
|
|
FOREIGN KEY(course_id,sec_id,semester,year) REFERENCES section(course_id,sec_id,semester,year) ON DELETE CASCADE,
|
|
FOREIGN KEY(ID) REFERENCES student(ID) ON DELETE CASCADE
|
|
)""")
|
|
|
|
c.execute("""CREATE TABLE teaches(
|
|
ID INTEGER,
|
|
course_id INTEGER,
|
|
sec_id INTEGER,
|
|
semester TEXT,
|
|
year INTEGER,
|
|
PRIMARY KEY(ID,course_id,sec_id,semester,year)
|
|
FOREIGN KEY(course_id, sec_id, semester, year) REFERENCES section (course_id, sec_id, semester, year) ON DELETE CASCADE,
|
|
FOREIGN KEY(ID) REFERENCES instructor (ID) ON DELETE CASCADE
|
|
|
|
)""")
|
|
|
|
c.execute("""CREATE TABLE time_slot(
|
|
time_slot_id INTEGER,
|
|
day INTEGER,
|
|
start_hr INTEGER CHECK(start_hr>=0 and start_hr<=24),
|
|
start_min INTEGER CHECK(start_min >= 0 and start_min < 60),
|
|
end_hr INTEGER CHECK (end_hr >= 0 and end_hr < 24),
|
|
end_min INTEGER CHECK (end_min >= 0 and end_min < 60),
|
|
PRIMARY KEY(time_slot_id,day,start_hr,start_min)
|
|
)""")
|
|
|
|
|
|
|
|
conn.commit()
|
|
conn.close()
|
|
|