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