SQL Help

Compare the code in Advising.sql to the description below. Identify three ways the code fails to implement the description.

a) A student can have one or more majors, and a single advisor.

b) The date a major is selected must be tracked and must be on or before the current date.

c) Student information includes their name and assigned school id number (nine digits); all fields are required.

d) Information about majors includes the name of the subject, the department, and advisor(s); multiple students can have the same major.

e) Department refers to the 2 to 5 letters identifying each department on campus.

f) An advisor can support multiple majors; a major can have one or more advisors.

g) Advisor information includes name, office (two digit building and three digit room numbers), and 4 digit phone extension. Each phone extension must begin with the numbers 5, 6, or 7.

(Here is the Advising.sql)

CREATE DATABASE studentMajors

GO

USE studentMajors

GO

CREATE TABLE Advisors

(advisorid int identity primary key,

advisorFirstName varchar(25) not null,

advisorLastName varchar(35) not null,

building char(2) not null CHECK (building LIKE '[0-9][0-9]'),

room char(3) not null CHECK (room LIKE '[0-9][0-9][0-9]'),

extension char(4) not null check (extension LIKE '[0-9][0-9][0-9][0-

9]'))

GO

CREATE TABLE Majors

(majorid int identity primary key,

major varchar(50) not null,

department varchar(5) not null check (department LIKE '[A-Z][A-Z]'

OR

department LIKE '[A-Z][A-Z][A-Z]' OR department LIKE '[A-

Z][A-Z][A-Z][A-Z]' OR

department LIKE '[A-Z][A-Z][A-Z][A-Z][A-Z]'))

GO

CREATE TABLE MajorAdvisors

(majorid int NOT NULL references majors,

advisorid int NOT NULL references advisors)

CREATE TABLE Students

(studentFirst varchar(25) NOT NULL,

studentLast varchar(35) NOT NULL,

studentid char(9) NOT NULL PRIMARY KEY

CHECK (studentID like '[0-9][0-9][0-9][0-9][0-9][0-9][0-

9][0-9][0-9]'))

GO

CREATE TABLE StudentMajors

(studentid char(9) NOT NULL references students,

majorid int NOT NULL references majors,

chooseDate date check (chooseDate <= getdate()),

advisorid int NOT NULL references advisors)