Create a Program to Work with Tables in SQL Assignment Solution

Instructions

Objective

Write a SQL assignment program to work with tables.

Requirements and Specifications

Source Code

Question 1

Part A: Functional Dependencies

Source Code

Question 1

Part A: Functional Dependencies

• Identified functional dependencies among attributes:
• student_advisor_id student_advisor_name
• student_id student_name, student_advisor_id
• department_id department_name
• course_id course_title, course_credit, department_id
• course_professor_id course_professor_name
• course_id, semester course_professor_id
• course_id, semester, course_professor_id student_id, course_grade
• course_id, semester, student_id semester_GPA

Additional assumptions:

• A student advisor can advise one or many students.
• For a student to be considered admitted into one or more departments, they would have been enrolled on courses coming from different departments. A course is under a department and a department has list of courses in it.
• Multiple same courses can open in the same semester which can have many different professors teaching it. However, for each course opened on the semester, only one professor handles it.
• A student can retake the same course but for a different semester which will yield them a different grade.

Part B: Primary key

Identified primary key: student_advisor_id, student_id, department_id, course_id, course_professor_id, semester

Part C: Normalization

Underlined attributes are primary keys while italicized attributes are foreign keys.

First Normal Form

Break columns that contain multiple values to make them atomic:

• student_advisor_name splits into advisor_first_name and advisor_last_name
• student_name splits into student_first_name and student_last_name
• course_professor_names split into professor_first_name and professor_last_name
• semester splits into semester_year and semester_number

Remove repeating groups thus we need to re-organize the columns to their respective tables:

• ADVISOR (student_advisor_id, advisor_first_name, advisor_last_name)
• STUDENT (student_id, student_first_name, student_last_name)
• DEPARTMENT (department_id, department_name)
• COURSE (course_id, course_title, course_credit, department_id)
• PROFESSOR (course_professor_id, professor_first_name, professor_last_name)
• COURSE_OFFERING (course_id, semester_year, semester_number, course_professor_id, student_id, course_grade, semester_GPA)

Second Normal Form

Non key attributes should be fully functional dependent on the primary key:

• SEMESTER_STUDENT (semester_year, semester_number, student_id, semester_GPA)
• COURSE_OFFERING (course_id, semester_year, semester_number, course_professor_id, student_id, course_grade)

Third Normal Form

Remove transitive functional dependency:

• COURSE_OFFERING (course_id, semester_year, semester_number, course_professor_id)
• COURSE_STUDENT (course_id, semester_year, semester_number, course_professor_id, student_id, course_grade)

Part D: ER Model

Question 2

Part A: ER Model

Part B: Constraints

With regards to cardinality:

• A team must have 1 leader and should be a student.
• Not all students can be a leader in a team.
• A team must have at least 1 student member.
• A student member can only be in a single team or none at all.
• A team can only work on project.
• A project must have at least 1 team working on it but can be more.
• A project should either be an internal or external but not both.

With regards to participation:

• Student has a partial participation to a team because they are not required to have a team right away.
• A team has full participation to a project. Without a team to work on a project it gets dissolved.
• An internal and external project has full participation to a project. They will only exists if a project exists.
• With regards to the full disjoint:
• A project must be an internal project or an external project (must be complete) but cannot be both (cannot overlap).

Part C: Create Table Statements

create table PROJECT ( pid integer not null, topic varchar(100) not null, project_type char(1) not null, primary key(pid), check (project_type = 'I' or project_type = 'E') ); create table INTERNAL_PROJECT ( pid integer not null, primary key(pid), foreign key(pid) references PROJECT (pid) ); create table EXTERNAL_PROJECT ( pid integer not null, client_name varchar(30) not null, budget decimal(10, 2) not null, primary key(pid), foreign key(pid) references PROJECT (pid) ); create table STUDENT ( sid integer not null, tid integer, name varchar(30) not null, dept varchar(30) not null ); create table TEAM ( tid integer not null, pid integer not null, team_lead integer not null, ate varchar(30) not null, primary key(tid), foreign key(pid) references PROJECT (pid), foreign key(team_lead) references STUDENT (sid) ); alter table STUDENT add foreign key (tid) references TEAM (tid);

Part D: Queries

Part i: All students that have not been assigned to a team.

select *

from STUDENT

where tid is null;

Part ii: Number of teams working in each project ranked in descending order

select TEAM.tid, count(STUDENT.sid) as num_students

from TEAM left join STUDENT on TEAM.tid = STUDENT.tid

group by TEAM.tid

order by count(STUDENT.sid) desc;

select FILTERED_TEAM.tid, STUDENT.name, STUDENT.sid, FILTERED_TEAM.num_students

from STUDENT,

(select TEAM.tid, TEAM.team_lead, count(STUDENT.sid) as num_students

from TEAM left join STUDENT on TEAM.tid = STUDENT.tid

group by TEAM.tid

having count(STUDENT.sid) < 3) as FILTERED_TEAM

where FILTERED_TEAM.team_lead = STUDENT.sid

order by FILTERED_TEAM.num_students asc;

Part E: Additional Constraints

There are a few ways to solve these additional constraints in terms of limiting the number of rows for a particular table. One of which is the use of triggers but it is not a portable solution because every SQL application have different ways and syntax on how to implement them.

A more portable and logical solution is to create a column that keeps track the count. This count however needs to be updated from time to time. If a related data is removed, the count decreases and if a related data is added, then the count increases.

Part i: Each team can have no more than 4 members

We add a new column to the TEAM table that keeps track the number of members in the team:

alter table TEAM

add num_members integer;

alter table TEAM

add check (num_members >= 0 and num_members < 4);

So before we insert a new member to the team, we first update the num_members by increasing it. If it goes beyond 4, the constraint will throw an error thus it means we’re not allowed to add a new member to the team anymore.

Part ii: No more than 3 teams can work on a project

We add a new column to the PROJECT table that keeps track the number of teams working on it:

alter table PROJECT

add num_teams integer;

alter table PROJECT

add check (num_teams >= 1 and num_teams < 3);

So before we assign a project to a team, we first update the num_teams by increasing it. If it goes beyond 3, the constraint will throw an error thus it means we’re not allowed to add a new team for the project anymore.