October 22, 2020 at 1:08 am
I am in a project where information is being pulled from a database then put into a web page in a manner similar to a report. In this database I have two tables in particular that I want to have update automatically on January 1:
Table 1: Semesters - Add 3 columns where Sem_Year is the new year and Sem_Term is Spring, Summer, and Fall respectively. Semester_PK is currently set to autopopulate (IDENTITY (100,1)).
Table 2: Courses - Add a number of columns equal to the number of semesters * the number of courses. Right now, that's 9, but it could change. I'm awaiting further information. There are other fields that can auto-update, as well (e.g., INSTRUCTOR_ID), but I can build those out once I have that table populated. If you look at the code, you'll see that these fields are not set to be NOT NULL so they can be populated later either via an update script or manual entry.
INSERT TABLE Code for the tables in question follows below:
--Create Table Semesters
CREATE TABLE Semesters (
Semester_ID int IDENTITY (100,1),
Sem_Year int NOT NULL,
Sem_Term varchar(10) NOT NULL,
CONSTRAINT PK_Semester_ID
PRIMARY KEY NONCLUSTERED (Semester_ID)
);
--Create Table Courses
CREATE TABLE Courses (
Course_ID int IDENTITY (100,1),
Course_Name varchar(50) NOT NULL,
Instructor_ID int,
Semester_ID int,
CONSTRAINT PK_Course_ID
PRIMARY KEY NONCLUSTERED (Course_ID),
CONSTRAINT FK_Courses_Instructor_ID FOREIGN KEY (Instructor_ID)
REFERENCES Instructors (Instructor_ID),
CONSTRAINT FK_Courses_Semester_ID FOREIGN KEY (Semester_ID)
REFERENCES Semesters (Semester_ID)
);
Here's the problem: Everything I am reading tells me this needs to be a SQL Server Agent job instead of a Trigger, but I'm running SQL Server Express, which does not support Jobs.
I do not know if the Development Team is working in SQL Server or SQL Server Express; again, this is something I hope to find out soon.
What would be the best approach to get these tables to auto-populate as desired, provided the Development Team is also working in Express?
October 22, 2020 at 1:36 am
For SQL Express, you'd need to use the task scheduler in Windows directly, instead of using SQL Server jobs. It's more of a pain, but it should work.
You should explicitly specify NULL on columns that can be NULL. The default is very complex and is not always NULL.
Also, btw, the data model you have is not correct. You should have a separate "master" Course table, with nothing about semester in it, that contains core info about the Course itself.
Then an intersection table that indicates a course was offered in a given semester.
For example:
--Create Table Courses
CREATE TABLE Courses (
Course_ID int IDENTITY (100,1) NOT NULL,
Course_Name varchar(50) NOT NULL,
--credit hours
--semester first offered
--semester last offered
--...
CONSTRAINT PK_Course_ID
PRIMARY KEY CLUSTERED (Course_ID)
);
--Create Table Semester_Courses
CREATE TABLE Semester_Courses (
Semester_ID int NOT NULL,
Course_ID int NOT NULL,
Instructor_ID int NULL,
CONSTRAINT PK_Course_ID
PRIMARY KEY CLUSTERED (Semester_ID, Course_ID),
CONSTRAINT FK_Semester_Courses_Semester_ID FOREIGN KEY (Semester_ID)
REFERENCES Semesters (Semester_ID)
CONSTRAINT FK_Semester_Courses_Course_ID FOREIGN KEY (Course_ID)
REFERENCES Courses (Course_ID)
CONSTRAINT FK_Semester_Courses_Instructor_ID FOREIGN KEY (Instructor_ID)
REFERENCES Instructors (Instructor_ID)
);
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 22, 2020 at 8:04 pm
For SQL Express, you'd need to use the task scheduler in Windows directly, instead of using SQL Server jobs. It's more of a pain, but it should work.
The only thing is that I'm not familiar with this setup. I have read up on it a bit, but I'm not having much luck with understanding it.
You should explicitly specify NULL on columns that can be NULL. The default is very complex and is not always NULL.
OK, that isn't a problem. Right now, we are developing the code to generate the tables; fortunately, nothing has been officially created as of yet.
Also, btw, the data model you have is not correct. You should have a separate "master" Course table, with nothing about semester in it, that contains core info about the Course itself.
I don't have any info about the course except the name of the course and the Instructor. I do, however, have an Enrollment table setup as follows:
--Create Table Enrollment
CREATE TABLE Enrollment (
Enroll_ID int IDENTITY (100,1),
Student_ID int NOT NULL,
Course_ID int NOT NULL,
Duties_ID int,
CONSTRAINT PK_Enroll_ID
PRIMARY KEY NONCLUSTERED (Enroll_ID),
CONSTRAINT FK_Enroll_Student_ID FOREIGN KEY (Student_ID)
REFERENCES Students (Student_ID),
CONSTRAINT FK_Enroll_Course_ID FOREIGN KEY (Course_ID)
REFERENCES Courses (Course_ID),
CONSTRAINT FK_Enroll_Duties_ID FOREIGN KEY (Duties_ID)
REFERENCES Duties (Duties_ID)
);
Should I add the Semester_ID Reference here?
October 23, 2020 at 3:40 am
Also, btw, the data model you have is not correct. You should have a separate "master" Course table, with nothing about semester in it, that contains core info about the Course itself.
I don't have any info about the course except the name of the course and the Instructor.
The course name is related only to the course id. Therefore, it needs a table separate from Instructor.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 23, 2020 at 3:43 am
Should I add the Semester_ID Reference here?
I guess. What you really need to do is step back and do a true logical data model first including normalization. Your current table structures are going to cause you huge problems in the future because of a complete lack of normalization.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 23, 2020 at 7:51 am
Don't implement is as a table.
make it into a view. May be dynamically created view, to handle changing requirements for the column set.
Then you're not gonna need to do horribly expensive UPDATEs.
_____________
Code for TallyGenerator
October 27, 2020 at 12:38 am
Don't implement is as a table.
make it into a view. May be dynamically created view, to handle changing requirements for the column set.
Then you're not gonna need to do horribly expensive UPDATEs.
Forgive me, but I don't completely follow you.
I have attached my CREATE TABLE SQL Statements as well as a PDF of our ERD. I'm welcome to receive any input on how to improve on things, as I'm really trying to work towards a career in working with data (e.g., Data Analyst).
I also have other queries developed for creating at least one view, as well as any indexes and potential stored procedures I may need. This is to serve as the back end for a web page where the information in the DB will be pulled up based on the "semester" chosen. I'm just trying to "guild the lily" a bit with my team to help show we know how to do more than the standard statements.
October 27, 2020 at 12:41 am
OK, for some reason the files did not upload. I'll just insert the CREATE TABLE code here. I also tried attaching the ERD as a .png file to see if that will upload instead.
/*
Database Creation Statements for
Project ACES
Version 1.1.16
Written by: Russell Wright
Created: 10/12/2020
Updated: 10/14/2020
*/
--Create Table Users
CREATE TABLE Users (
User_ID int IDENTITY (100,1), --Tracking number
U_Email varchar(255),
U_FName varchar(50),
U_LName varchar(50),
U_Company varchar(100),
U_CompAdd1 varchar(100),
U_CompAdd2 varchar(100),
U_CompCity varchar(50) DEFAULT 'Charlotte',
U_CompState varchar(2) DEFAULT 'NC',
U_CompZIP varchar(10),
U_Password varchar(25)
CONSTRAINT PK_Email_Login
PRIMARY KEY NONCLUSTERED (U_Email)
);
--Create Table Students
CREATE TABLE Students (
Student_ID int IDENTITY (100,1),
S_FName varchar(50) NOT NULL,
S_LName varchar(50) NOT NULL,
S_Address1 varchar(100) NOT NULL,
S_Address2 varchar(50),
S_City varchar(50) DEFAULT 'Charlotte',
S_State varchar(2) DEFAULT 'NC',
S_ZIP varchar(10), --Should this be int?
S_Email varchar(255) NOT NULL,
S_Phone1 varchar(10) NOT NULL,
S_Phone2 varchar(10),
CONSTRAINT PK_Student_ID
PRIMARY KEY NONCLUSTERED (Student_ID)
);
--Create Table Contacts
CREATE TABLE Contacts (
Contact_ID int IDENTITY (100,1),
C_FName varchar(50) NOT NULL,
C_LName varchar(50) NOT NULL,
C_Address1 varchar(100) NOT NULL,
C_Address2 varchar(50),
C_City varchar(50) DEFAULT 'Charlotte',
C_State varchar(2) DEFAULT 'NC',
C_ZIP varchar(10), --Should this be int?
C_Email varchar(255) NOT NULL,
C_Phone1 varchar(10) NOT NULL,
C_Phone2 varchar(10),
CONSTRAINT PK_Contact_ID
PRIMARY KEY NONCLUSTERED (Contact_ID)
);
--Create Table Instructors
CREATE TABLE Instructors (
Instructor_ID int IDENTITY (100,1),
I_FName varchar(50) NOT NULL,
I_LName varchar(50) NOT NULL,
I_Address1 varchar(100) NOT NULL,
I_Address2 varchar(50),
I_City varchar(50) DEFAULT 'Charlotte',
I_State varchar(2) DEFAULT 'NC',
I_ZIP varchar(10), --Should this be int?
I_Email varchar(255) NOT NULL,
I_Phone1 varchar(10) NOT NULL,
I_Phone2 varchar(10),
CONSTRAINT PK_Instructor_ID
PRIMARY KEY NONCLUSTERED (Instructor_ID)
);
--Create Table Semesters
CREATE TABLE Semesters (
Semester_ID int IDENTITY (100,1),
Sem_Year int NOT NULL,
Sem_Term varchar(10) NOT NULL,
CONSTRAINT PK_Semester_ID
PRIMARY KEY NONCLUSTERED (Semester_ID)
);
--Create Table Courses
CREATE TABLE Courses (
Course_ID int IDENTITY (100,1),
Course_Name varchar(50) NOT NULL,
Instructor_ID int,
CONSTRAINT PK_Course_ID
PRIMARY KEY NONCLUSTERED (Course_ID),
CONSTRAINT FK_Courses_Instructor_ID FOREIGN KEY (Instructor_ID)
REFERENCES Instructors (Instructor_ID),
);
--Create Table Teams
CREATE TABLE Teams (
Team_ID int IDENTITY (100,1),
Team_Num int NOT NULL,
Team_Name varchar(100),
Course_ID int NOT NULL,
CONSTRAINT PK_Team_ID
PRIMARY KEY NONCLUSTERED (Team_ID),
CONSTRAINT FK_Teams_Course_ID FOREIGN KEY (Course_ID)
REFERENCES Courses (Course_ID)
);
--Create Table Duties
CREATE TABLE Duties (
Duties_ID int IDENTITY (100,1),
Team_ID int NOT NULL,
D_Name varchar(MAX) NOT NULL,
CONSTRAINT PK_Duties_ID
PRIMARY KEY NONCLUSTERED (Duties_ID),
CONSTRAINT FK_Duties_Team_ID FOREIGN KEY (Team_ID)
REFERENCES Teams (Team_ID)
);
--Create Table Enrollment
CREATE TABLE Enrollment (
Enroll_ID int IDENTITY (100,1),
Student_ID int NOT NULL,
Semester_ID int NOT NULL,
Course_ID int NOT NULL,
Duties_ID int,
CONSTRAINT PK_Enroll_ID
PRIMARY KEY NONCLUSTERED (Enroll_ID),
CONSTRAINT FK_Enroll_Student_ID FOREIGN KEY (Student_ID)
REFERENCES Students (Student_ID),
CONSTRAINT FK_Courses_Semester_ID FOREIGN KEY (Semester_ID)
REFERENCES Semesters (Semester_ID)
CONSTRAINT FK_Enroll_Course_ID FOREIGN KEY (Course_ID)
REFERENCES Courses (Course_ID),
CONSTRAINT FK_Enroll_Duties_ID FOREIGN KEY (Duties_ID)
REFERENCES Duties (Duties_ID)
);
--Create Table Projects
CREATE TABLE Projects (
Project_ID int IDENTITY (100,1),
P_Name varchar(100),
Contact_ID int NOT NULL,
Course_ID int NOT NULL,
CONSTRAINT PK_Project_ID
PRIMARY KEY NONCLUSTERED (Project_ID),
CONSTRAINT FK_Projects_Contact_ID FOREIGN KEY (Contact_ID)
REFERENCES Contacts (Contact_ID),
CONSTRAINT FK_Projects_Course_ID FOREIGN KEY (Course_ID)
REFERENCES Courses (Course_ID)
);
--Create Table Roles
CREATE TABLE Roles (
Role_ID int IDENTITY (100,1),
R_Name varchar(100) NOT NULL, --Role Name
Student_ID int NOT NULL,
Team_ID int NOT NULL,
CONSTRAINT PK_Role_ID
PRIMARY KEY NONCLUSTERED (Role_ID),
CONSTRAINT FK_Role_Student_ID FOREIGN KEY (Student_ID)
REFERENCES Students (Student_ID),
CONSTRAINT FK_Role_Team_ID FOREIGN KEY (Team_ID)
REFERENCES Teams (Team_ID)
);
October 27, 2020 at 1:10 am
What is the definition of the table you intend to update.
You said it's gonna be changing, so - for the current moment?
And please post some draft code you intend to use to update that table.
_____________
Code for TallyGenerator
October 27, 2020 at 3:51 am
I am in a project where information is being pulled from a database then put into a web page in a manner similar to a report. In this database I have two tables in particular that I want to have update automatically on January 1:
Table 1: Semesters - Add 3 columns where Sem_Year is the new year and Sem_Term is Spring, Summer, and Fall respectively. Semester_PK is currently set to autopopulate (IDENTITY (100,1)).
Table 2: Courses - Add a number of columns equal to the number of semesters * the number of courses. Right now, that's 9, but it could change. I'm awaiting further information. There are other fields that can auto-update, as well (e.g., INSTRUCTOR_ID), but I can build those out once I have that table populated. If you look at the code, you'll see that these fields are not set to be NOT NULL so they can be populated later either via an update script or manual entry.
INSERT TABLE Code for the tables in question follows below:
--Create Table Semesters
CREATE TABLE Semesters (
Semester_ID int IDENTITY (100,1),
Sem_Year int NOT NULL,
Sem_Term varchar(10) NOT NULL,
CONSTRAINT PK_Semester_ID
PRIMARY KEY NONCLUSTERED (Semester_ID)
);
--Create Table Courses
CREATE TABLE Courses (
Course_ID int IDENTITY (100,1),
Course_Name varchar(50) NOT NULL,
Instructor_ID int,
Semester_ID int,
CONSTRAINT PK_Course_ID
PRIMARY KEY NONCLUSTERED (Course_ID),
CONSTRAINT FK_Courses_Instructor_ID FOREIGN KEY (Instructor_ID)
REFERENCES Instructors (Instructor_ID),
CONSTRAINT FK_Courses_Semester_ID FOREIGN KEY (Semester_ID)
REFERENCES Semesters (Semester_ID)
);
I'm thinking that everywhere you said you wanted to "add columns", you really mean you wanted to "add rows", yes?
Also, I'm "loving" the password column in the users table. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 27, 2020 at 4:37 am
Also, I'm "loving" the password column in the users table. 😉
Is it the only thing you found there which deserve unconditional love?
You know, at some stage I stopped commenting on database designs and suggesting improvements, it's no use or joy to any party. Be honest with me - am I a bad person?
_____________
Code for TallyGenerator
October 27, 2020 at 10:14 am
The current (pseudo)design is horrifically bad. You have non-dependent columns in tables, repeating columns (address1, address2 / phone1, phone2), update/delete anomalies all over the place, and so on.
Stop rushing to get to tables. Back up and do logical data modeling first, including normalization. That should always be the first step anyway, before creating tables.
And please post some draft code you intend to use to update that table.
Geez no, worst thing you could do. Again, forget code for now and get the data model right first! The data model is the foundation. That's what is really critical. The app code is transient anyway. What you write now will be replaced in 2 years, 4 years, pick some number. But the data structure will be around a lot longer. And will cause far more and far worse problems if it's bad.
Sorry for the bluntness, but I tried more nicely before. And all for naught anyway. You seem determined to stick with what you already have.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 27, 2020 at 12:20 pm
Scott, you obviously don't get the concept of "no premature optimisation".
so out of touch with contemporary trends!
🙂
_____________
Code for TallyGenerator
October 27, 2020 at 1:18 pm
Nothing to do with data modeling, only development. Data modeling must precede coding.
Just don't offer terrible modeling advice and I won't have to point it out.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 27, 2020 at 1:27 pm
Jeff Moden wrote:Also, I'm "loving" the password column in the users table. 😉
Is it the only thing you found there which deserve unconditional love?
You know, at some stage I stopped commenting on database designs and suggesting improvements, it's no use or joy to any party. Be honest with me - am I a bad person?
No and no, to answer your two questions. I saw how the OP was basically ignoring design suggestions and came to the same conclusion as Scott's last statement and your statement above long before they were said but I couldn't let the password column go without at least a fleeting comment.
It also seemed that the OP confused rows and columns in the first post and I was curious to see if that was true or they were actually going to make the mistake of adding columns to table every year.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply