s0VGGv23

1# Don't use single quotes for naming fields in a table
2# Run each query individually
3
4# 7 foreign keys!
5
6drop database if exists SQL_SBA;
7
8create database SQL_SBA;
9#use the 'SQL_SBA' database
10use SQL_SBA;
11
12# Departments table
13# Primary key: department.id
14create table department (
15  `id` int NOT NULL ,
16  `name` varchar(30) not null,
17  primary key (id)
18);
19
20# Faculty table
21# primary key: faculty.id
22# foreign key: faculty.deptid references department.id (department table pk)
23create table faculty (
24  `id` int NOT NULL,
25  `firstname` varchar(30) NOT NULL,
26  `lastname` varchar(30) NOT NULL,
27  `deptid` int NOT NULL,
28  primary key (id),
29  foreign key (deptid) references department(id)
30);
31
32# Student table
33# primary key: student.id
34# foreign key: student.majorid references department.id
35create table student (
36  `id` int NOT NULL,
37  `firstname` varchar(30) NOT NULL,
38  `lastname` varchar(30) NOT NULL,
39  `street` varchar(50) NOT NULL,
40  `state` varchar(2) NOT NULL,
41  `streetDetail` varchar(30) NULL,
42  `city` varchar(30) NOT NULL,
43  `postalCode` char(5) NOT NULL,
44  `majorId` int,
45  primary key (id),
46  foreign key (majorId) references department(id)
47);
48
49# Course table
50# primary key: course.id
51# foreign key: course.deptid references department.id
52create table course (
53  `id` int NOT NULL ,
54  `name` varchar(50) NOT NULL,
55  `deptid` int,
56  primary key (id),
57  foreign key (deptid) references department(id)
58);
59
60# StudentCourse table
61# primary keys: studentId, courseId
62# foreign key: studentCourse.courseId references student.id
63create table studentCourse (
64  `studentid` int ,
65  `courseid` int,
66  `progress` int NOT NULL,
67  `startdate` date  NOT NULL,
68  primary key (studentid, courseid),
69  foreign key (courseid) references student(id),
70  foreign key (courseid) references course(id)
71);
72
73# FacultyCourse table
74# primary keys: facultyId, courseId
75# foreign key: facultyId
76create table facultyCourse (
77  `facultyid` int NOT NULL,
78  `courseid` int NOT NULL,
79  primary key (facultyid, courseid),
80  foreign key (facultyid) references faculty(id),
81  foreign key (courseid) references course(id)
82);