PrcKvRcU

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(
16  `id`   int  primary key ,
17  `name` varchar(30) not null
18
19);
20
21# Faculty table
22# primary key: faculty.id
23# foreign key: faculty.deptid references department.id (department table pk)
24create table faculty
25(
26  `id`        int primary key ,
27  `firstname` varchar(30) not null,
28  `lastname`  varchar(30) not null,
29  `deptid`    int,
30
31  foreign key (deptid) references department (id)
32);
33
34# Student table
35# primary key: student.id
36# foreign key: student.majorid references department.id
37create table student
38(
39  `id`           int primary key ,
40  `firstname`    varchar(30) not null,
41  `lastname`     varchar(30) not null,
42  `street`       varchar(50) not null,
43  `state`        varchar(2)  not null,
44  `streetDetail` varchar(30) NULL,
45  `city`         varchar(30) not null,
46  `postalCode`   char(5)     not null,
47  `majorId`      int         not null,
48
49  foreign key (majorId) references department (id)
50);
51
52# Course table
53# primary key: course.id
54# foreign key: course.deptid references department.id
55create table course
56(
57  `id`     int primary key ,
58  `name`   varchar(50) not null,
59  `deptid` int         not null,
60
61  foreign key (deptid) references department (id)
62);
63
64# StudentCourse table
65# primary keys: studentId, courseId
66# foreign key: studentCourse.courseId references student.id
67create table studentCourse
68(
69  `studentid` int,
70  `courseid`  int,
71  `progress`  int  not null,
72  `startdate` date not null,
73  primary key (studentid, courseid),
74  foreign key (courseid) references student (id),
75  foreign key (courseid) references course (id)
76);
77
78# FacultyCourse table
79# primary keys: facultyId, courseId
80# foreign key: facultyId
81create table facultyCourse
82(
83  `facultyid` int,
84  `courseid`  int,
85  primary key (facultyid, courseid),
86  foreign key (facultyid) references faculty (id),
87  foreign key (courseid) references course (id)
88);