Students DB Exercise

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)

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. Team contains the unique id of each team tid, the sid 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 exists 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 query to display the following:

• All students that have not been assigned to a team.
• The number of teams working in each project 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.