ER Table assignment Solution using SQL

SQL and ER Table

Question 1

Consider a relational table that stores the following information:

(student_id, student_name, department_id, department_name, course_id, course_title, semester, course_credit, course_professor_name, course_professor_id, student_advisor_name, student_advisor_id, course_grade, semester_GPA)

Suppose the data has the following properties:

Students can be admitted into one or more departments Students can enroll in multiple courses in a semester Students can enroll in between 36 and 50 credits in a semester Each student has one advisor

The final grade for each course is a letter grade: A, B, C or F.

a) Identify the functional dependencies among the attributes in the relation. State any additional assumptions you make about the data.

b) Identify the primary key in the table.

c) Using the functional dependencies and the primary key identified above, illustrate the process of converting the table from 1NF to 3NF. Identify the primary keys and foreign keys in all your tables.

d) Illustrate your database schema using an ER model. Ensure that the multiplicity constraints are properly illustrated.

Question 2

Consider the following database schema for projects in a class:

Student(sid, name, dept, tid) Project(pid, topic)

Team(tid, team_lead, pid, date)

The student contains a row for each student with unique id sid, name name, department dept, and the team they are assigned to tid. Relation Project contains the unique id for each project pid and the topic area the students will be working on. The team contains the unique id of each team

tid, the side of the leader of the team team_lead, the project the team is working on pid, and the date the team will present their findings to the class date. The primary keys are underlined.

• Assume the following hold true:
• Students can only be assigned to one team
• Not all students have been assigned to a team
• Teams can only work on one project
• Multiple teams can work on the same project
• Internal projects are defined by the students in the team
• External projects are defined by the client; storing the client’s name and budget (if any is allotted)

a) Design an ER model to illustrate the relationship between the relations and their attributes. Clearly illustrate the multiplicity constraints in the model.

b) Explain the cardinality, participation, and disjoint constraints that exist in the model. In answering this question, define the constraints and then show examples of where the

constraint exists in the model. If it does not exist in the model, indicate that it does not exist.

c) Write the CREATE TABLE statements to represent this ER model using SQL relations. Enforce all the entity and referential constraints.

d) Write the SQL assignment query to display the following:

• All students that have not been assigned to a team.
• The number of teams working on each project is ranked in descending order.
• The team_id, name, and student_id of the team leader, and the total number of members of all teams with less than 3 members. Sort the results in ascending order.

e) After implementing the database, the course professor realized that some projects were in high demand and others were not being considered. Also, large groups of friends wanted to work together, while some students had no one to partner with. To address this, he has asked you to add the following constraints to the existing database:

• Each team can have no more than 4 members.
• No more than 3 teams can work on a project.

Solution:

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 the project.
• A project must have at least 1 team working on it but can be more.
• A project should either be internal or external but not both.

With regards to participation:

• The student has partial participation in a team because they are not required to have a team right away.
• A team has full participation in a project. Without a team to work on a project it gets dissolved.
• An internal and external project has full participation in a project. They will only exist 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, date 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; Part iii: 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 of 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.