Question

the college wants to keep track of the semester schedules across years. i.e. Departments, their Courses as they exist in the College catalog, and the course section offerings across the years and their semesters. From a catalogue perspective:

A department has a unique name (Finance, Management and Marketing, Math, ABC-Law, etc.) and offers multiple courses.

A course has a unique name, which is a hyphenated prefix-Number like (BUAN-310, CIS-205, FIN315, MATH-153, etc.), a long name like “principles of Data management”, a long description and a number of credit (leave prerequisites out for now).

During any given semester (Fall, Spring, Summer-1, Summer-2, Winter), Zero-Or-More-sections of a given course are offered. For example, For BUAN-227 and for the Fall of 2019, 4 sections (1, 2, 3 and 61, I made it up) were offered.

Each section has a CRN, which is a unique identifier and a capacity to indicate the max number of students that can sign-up for the course (Leave time and room out for now).

We leave students who register for courses out for now

Your reverse engineered schema looks similar

Write the SQL code that creates the tables and enforces Primary and foreign key constraints.

Your SQL Code Goes Below:

#All Fields are required

#It is your call if any of the fields is Unique.

use ……..; #Your database Schema Name instead

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE Department CASCADE;

Create TABLE Department (

…….

);

#

DROP TABLE Course CASCADE;

CREATE TABLE Course(

…….

);

#

DROP TABLE CourseSection CASCADE;

CREATE TABLE CourseSection(

…….

);

#

SET FOREIGN_KEY_CHECKS=1;

Insert 5-Department Records into the Department table. Your SQL Code Goes Below:

Enter 10 Courses into the Course table; make sure you assign them to different departments. Your SQL Code Goes Below:

Enter 20 course-sections into the courseSection table across different years and semesters. No more than 3 course sections for a given year and a given semester. Your SQL Code Goes Below:

MY SQL workbench

Answer 1

NOTE - I HAVE ENTERED DATA ACCORDING TO CONSTRAINTS , BUT CHECK ON YOUR WORKBENCH AND EDIT ACCORDINGLY

BEFORE RUNNING SCRIPTS , CHECK IF DROP TABLE WORKS WELL BECAUSE ON SOME MACHINES , BEFORE CREATION OF TABLE IT THROWS ERROR.

so i will suggest run scripts step by step.

CREATE DATABSE MYCOLLEGE
USE MYCOLLEGE

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE Department CASCADE;
CREATE TABLE Department

(
dept_id INT NOT NULL PRIMARY KEY,
name VARCHAR(20)
);

DROP TABLE Course CASCADE;

CREATE TABLE Course(

prefix_name VARCHAR(20) NOT NULL,
full_name VARCHAR(100),
description VARCHAR(200),
credits INT,
dept_id INT,
PRIMARY KEY (prefix_name),
FOREIGN KEY(dept_id)
REFERENCES Department(dept_id)
);

DROP TABLE CourseSection CASCADE;

CREATE TABLE CourseSection(

CRN VARCHAR(20) NOT NULL PRIMARY KEY,
Course_offered VARCHAR(20),
capacity INT,
Semester VARCHAR(20),
Year INT ,
FOREIGN KEY(Course_offered)
REFERENCES Course(prefix_name)

);

SET FOREIGN_KEY_CHECKS=1;

----- Inserts statements starts here --------------

Insert INTO Department VALUES
(
101,
"FINANCE"
) ;

Insert INTO Department VALUES
(
102,
"MANAGEMENT"
) ;

Insert INTO Department VALUES
(
103,
"MATHS"
) ;

Insert INTO Department VALUES
(
104,
"PHYSICS"
) ;

Insert INTO Department VALUES
(
105,
"GEOGRAPHY"
) ;

------------------------------------------------------------------------------------------------

INSERT INTO Course VALUES(
'FIN-213',
'FINANCE STUDIES',
'STUDYING ECONOMIC FACTORS OF COUNTRY RELATED TO FINANCE',
100,
(SELECT DEPT_ID FROM Department where name = 'FINANCE')

);

INSERT INTO Course VALUES(
'MATHS-290',
'MATHS STUDIES',
'TRIGNOMETRIC RELATIONSHIPS OF PHYSICAL THEORY',
130,
(SELECT DEPT_ID FROM Department where name = 'MATHS')

);
INSERT INTO Course VALUES(
'GEO-245',
'GEO STUDIES',
'GEOGRAPHY OF PLANET GREEN',
145,
(SELECT DEPT_ID FROM Department where name = 'GEOGRAPHY')

);
INSERT INTO Course VALUES(
'MAN-452',
'MANAGEMENT STUDIES',
'MANAGING COMPANY RESOURCES',
126,
(SELECT DEPT_ID FROM Department where name = 'MANAGEMENT')

);

INSERT INTO Course VALUES(
'MATHS-256',
'MATHS STUDIES',
'TRIGNOMETRIC RELATIONSHIPS OF DIFFERNTIAL THEORY',
130,
(SELECT DEPT_ID FROM Department where name = 'MATHS')

);

INSERT INTO Course VALUES(
'GEO-217',
' INDIAN GEOGRAPHY',
'STUDYING INDIAN SUB CONTINENT',
100,
(SELECT DEPT_ID FROM Department where name = 'GEOGRAPHY')

);
INSERT INTO Course VALUES(
'FIN-216',
'FINANCIAL ECONOMY',
'AMERICAN ECONOMIC FACTORS',
110,
(SELECT DEPT_ID FROM Department where name = 'FINANCE')

);
INSERT INTO Course VALUES(
'MAN-98',
'ACCOUNTS MANAGEMENT',
'CORPORATE ACCOUNTING',
100,
(SELECT DEPT_ID FROM Department where name = 'MANAGEMENT')

);
INSERT INTO Course VALUES(
'MATHS-562',
'INTEGERATION',
'STUDYING BLACK HOLE EQUATIONS',
180,
(SELECT DEPT_ID FROM Department where name = 'MATHS')

);
INSERT INTO Course VALUES(
'PHY-456',
'ELECTROMAGNETIC RADIATIOON',
'STUDYING EFFECTS OF UV RADIATION',
140,
(SELECT DEPT_ID FROM Department where name = 'PHYSICS')

);

---------------------------------------------------------------------------------------------------

INSERT INTO CourseSection VALUES(
'COURSE-GEO_1',
(SELECT prefix_name FROM Course WHERE description= 'STUDYING INDIAN SUB CONTINENT' ),
40,
'SPRING',
2020
);

INSERT INTO CourseSection VALUES(
'COURSE-MATH_1',
(SELECT prefix_name FROM Course WHERE description= 'TRIGNOMETRIC RELATIONSHIPS OF PHYSICAL THEORY' ),
45,
'SPRING',
2020
);

INSERT INTO CourseSection VALUES(
'COURSE-PHY_1',
(SELECT prefix_name FROM Course WHERE description= 'STUDYING EFFECTS OF UV RADIATION' ),
50,
'SPRING',
2020
);

INSERT INTO CourseSection VALUES(
'COURSE-GEO_2',
(SELECT prefix_name FROM Course WHERE description= 'STUDYING INDIAN SUB CONTINENT' ),
40,
'SUMMER-1',
2020
);

INSERT INTO CourseSection VALUES(
'COURSE-MATH_2',
(SELECT prefix_name FROM Course WHERE description= 'STUDYING BLACK HOLE EQUATIONS' ),
45,
'SUMMER-1',
2020
);

INSERT INTO CourseSection VALUES(
'COURSE-FIN_2',
(SELECT prefix_name FROM Course WHERE description= 'AMERICAN ECONOMIC FACTORS'),
50,
'SUMMER-1',
2020
);

INSERT INTO CourseSection VALUES(
'COURSE-PHY_3',
(SELECT prefix_name FROM Course WHERE description= 'STUDYING EFFECTS OF UV RADIATION' ),
30,
'SUMMER-1',
2020
);

INSERT INTO CourseSection VALUES(
'COURSE-GEO_3',
(SELECT prefix_name FROM Course WHERE description= 'GEOGRAPHY OF PLANET GREEN' ),
45,
'SUMMER-1',
2020
);

INSERT INTO CourseSection VALUES(
'COURSE-FIN_4',
(SELECT prefix_name FROM Course WHERE description= 'AMERICAN ECONOMIC FACTORS'),
50,
'SUMMER-1',
2020
);

INSERT INTO CourseSection VALUES(
'COURSE-PHY_4',
(SELECT prefix_name FROM Course WHERE description= 'STUDYING EFFECTS OF UV RADIATION' ),
30,
'SUMMER-2',
2020
);

INSERT INTO CourseSection VALUES(
'COURSE-GEO_5',
(SELECT prefix_name FROM Course WHERE description= 'GEOGRAPHY OF PLANET GREEN' ),
45,
'SUMMER-2',
2020
);

INSERT INTO CourseSection VALUES(
'COURSE-FIN_5',
(SELECT prefix_name FROM Course WHERE description= 'AMERICAN ECONOMIC FACTORS'),
50,
'SUMMER-2',
2020
);

INSERT INTO CourseSection VALUES(
'COURSE-MATH_6',
(SELECT prefix_name FROM Course WHERE description= 'TRIGNOMETRIC RELATIONSHIPS OF PHYSICAL THEORY' ),
40,
'WINTER',
2020
);

INSERT INTO CourseSection VALUES(
'COURSE-PHY_6',
(SELECT prefix_name FROM Course WHERE description= 'STUDYING EFFECTS OF UV RADIATION' ),
80,
'WINTER',
2020
);

INSERT INTO CourseSection VALUES(
'COURSE-FIN_7',
(SELECT prefix_name FROM Course WHERE description= 'STUDYING ECONOMIC FACTORS OF COUNTRY RELATED TO FINANCE'),
60,
'WINTER',
2020
);

INSERT INTO CourseSection VALUES(
'COURSE-MATH_7',
(SELECT prefix_name FROM Course WHERE description= 'TRIGNOMETRIC RELATIONSHIPS OF PHYSICAL THEORY' ),
40,
'SPRING',
2021
);

INSERT INTO CourseSection VALUES(
'COURSE-PHY_8',
(SELECT prefix_name FROM Course WHERE description= 'STUDYING EFFECTS OF UV RADIATION' ),
80,
'SPRING',
2021
);

INSERT INTO CourseSection VALUES(
'COURSE-FIN_9',
(SELECT prefix_name FROM Course WHERE description= 'STUDYING ECONOMIC FACTORS OF COUNTRY RELATED TO FINANCE'),
60,
'SPRING',
2021
);

INSERT INTO CourseSection VALUES(
'COURSE-GEO_9',
(SELECT prefix_name FROM Course WHERE description= 'STUDYING INDIAN SUB CONTINENT' ),
40,
'SUMMER-1',
2021
);

INSERT INTO CourseSection VALUES(
'COURSE-MATH_11',
(SELECT prefix_name FROM Course WHERE description= 'STUDYING BLACK HOLE EQUATIONS' ),
45,
'SUMMER-1',
2021
);

INSERT INTO CourseSection VALUES(
'COURSE-FIN_11',
(SELECT prefix_name FROM Course WHERE description= 'AMERICAN ECONOMIC FACTORS'),
50,
'SUMMER-1',
2021
);

INSERT INTO CourseSection VALUES(
'COURSE-PHY_12',
(SELECT prefix_name FROM Course WHERE description= 'STUDYING EFFECTS OF UV RADIATION' ),
30,
'SUMMER-2',
2021
);

INSERT INTO CourseSection VALUES(
'COURSE-GEO_12',
(SELECT prefix_name FROM Course WHERE description= 'GEOGRAPHY OF PLANET GREEN' ),
45,
'SUMMER-2',
2021
);

INSERT INTO CourseSection VALUES(
'COURSE-FIN_13',
(SELECT prefix_name FROM Course WHERE description= 'AMERICAN ECONOMIC FACTORS'),
50,
'SUMMER-2',
2021
);