+1 (315) 557-6473 

Create a Program to Work with Tables in SQL Assignment Solution.


GET FREE QUOTE
  1. Home
  2. Program to Work with Tables in SQL Assignment Solution.

Instructions

Objective
Write a SQL assignment program to work with tables.

Requirements and Specifications

program to work with tables in SQL
program to work with tables in SQL 1

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.