SQL Help a) Using the code in Advising.sql below, identify the tables and relati

Question

SQL Help

a) Using the code in Advising.sql below, identify the tables and relationships in the database.

(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)

Explanation / Answer

Solution:

Database Name : Majors

There are five tables that are created in this database :

1.Advisors : advisorid is the primary key. for this table.

2.Majors : majorid is the primary key for this table.

3.Major Advisors :This table uses majorid and advisorid as the foreign keys.Major Advisors table references majorid from Majors table and advisorid from Advisors table.So this table data is dependent on the data of two tables :Majors and Advisors.This table has one to many relationship as it contains the data from two tables.

4.Students :studentID is the primary key for this table..

5.Student Majors:This table uses studentid ,majorid and advisorid as the foreign keys.Student Advisors table references studentid from Students table ,majorid from Majors table and advisorid from Advisors table.So this table data is dependent on the data of three tables :Students,Majors and Advisors.This table has one to many relationship as it contains the data from three tables.

Let me know if any more explanation/help is required regarding this question.

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.