Code in SQL:

1. Create a script create2.sql to define the tables department, course and section.

1. The fields of department are:

a. id : an int to distinguish departments from each other. You now want to make this a primary key, and use the serial or bigserial type to automatically generate values

b. name : the name of the department, cannot be null

c. email : a string for the department email address this one can be null

d. building: the name of the building in which the department has its office, cannot be null

e. room : a string for the room in which the department has its office, cannot be null.

2. The fields of course are: (none of these can be null)

a. id: an automatically generated field like departments id this is the primary key of the course table

b. name: the name of the course

c. dept: a foreign key referencing the department. Note that the type here is not serial/bigserial since the database is not going to try to generate a new id, but should be int/bigint,

d. units: an int for the number of units

3. The fields of section are (shouldnt be null, but I didnt say so initially, so dont worry about it)

a. id: primary key for section so it is unique across all sections,

b. course: a foreign key referencing the course this section belongs to,

c. term: an integer code for the semester (if you check on Blackboard, they are using 5 for spring, 9 for fall, though of course a different school might use a different code. I am choosing to use integer for faster comparisons, though you are not doing math with the term

d. yr: an integer for efficient comparisons,

e. secNum: a string because lab sections at CSUEB often include letters. This field has a default value of 01

2. Create a script insert2.sql to insert acceptable data. Use INSERT statements to insert records into the department, course and section tables

a. Insert enough records so that any queries will return at least one, but not all records

b. For serial/bigserial fields and default values, see the 2^nd example on PostgreSQL: Documentation: 13: INSERT, which is for default values, but works for serial/bigserial fields, too

3. Create a script query2.sql that includes semicolon-separated queries to do the following (terminate each query with a semicolon so the grader can run the whole script at once) :

a. List names of all course in the English department

b. List id and section number of all sections of Database Architecture for term 5 (Spring) of 2021

c. List names of departments that are not offering any sections in term 5 of 2021

4. Create a script insert_bad.sql that uses INSERT statements to insert data that should not be allowed, and should now fail

a. Insert department with a null name (remember to use the null keyword, not a blank in C++/Java, you cant say ptr =; )

b. should not insert section with duplicate value of id

c. insert a course with a dept field that does not exist in the department table

------------------------------------------------------------//-------------------------------------------------------------------------------

// previous assignment:

create.sql :

-- CS 431, Spring 2021

-- create table to represent Assignments

CREATE SCHEMA solution

CREATE TABLE assignment (

id bigint,

course varchar(255),

name varchar(255),

duedate date,

maxgrade bigint)

;

-------------------------------------//-----------------------------------

insertbad.sql :

-- CS 431, Spring 2021

-- inserting assignment records that should not be allowed

-- id, course, name (of assignment), max grade, should not be null

INSERT INTO solution.assignment VALUES (null, null, null, null, null);

-- should not insert assignment with duplicate value of id

INSERT INTO solution.assignment VALUES (2, 'CS 431', 'hw1', to_date('08 Feb 2021 23:59', 'DD Mon YYYY HH24:MI'), 100);

-- max score should not be negative

INSERT INTO solution.assignment VALUES (3, 'CS 431', 'hw3', to_date('05 Feb 2021 23:59', 'DD Mon YYYY HH24:MI'), -100);

------------------------------------//--------------------------------------

insertok.sql :

-- CS 431, Spring 2021

-- insert assignments -- shows use of to_date()

INSERT INTO solution.assignment VALUES (1, 'CS 301', 'hw1', to_date('29 Jan 2021 23:59', 'DD Mon YYYY HH24:MI'), 100);

INSERT INTO solution.assignment VALUES (2, 'CS 431', 'hw1', to_date('29 Jan 2021 23:59', 'DD Mon YYYY HH24:MI'), 100);

INSERT INTO solution.assignment VALUES (3, 'CS 431', 'hw2', to_date('05 Feb 2021 23:59', 'DD Mon YYYY HH24:MI'), 100);

INSERT INTO solution.assignment VALUES (4, 'MATH 130', 'hw1', to_date('01 Feb 2021 23:59', 'DD Mon YYYY HH24:MI'), 20);

---------------------------------------------//-----------------------------------------

query.sql :

-- CS 431, Spring 2021

-- simple queries of assignment table

-- list all data in table

select * from solution.assignment;

-- list name and duedate of all assignments for CS 431

select name, duedate

from solution.assignment

where course = 'CS 431';

-- list ids and duedates of all assignments due before February 1, 2021, 12:00 am (the start of the day)

select id, duedate

from solution.assignment

where duedate < to_date('01 Feb 2021 00:00', 'DD Mon YYYY HH24:MI');