- Information
- AI Chat
HW2 rubric
Computer Science
Standard
Recommended for you
Preview text
🏒
HW2 - ER Diagrams
💡 If the student's solution seems correct but does not match the Grading
Schema, email Grad TA (ani@brown).
📌 For all the parts, start form 0 points and go through all the steps giving
points based on the rubric attached after each solution.
📌 The student might show only the "diff" of the diagram, and that's fine.
It's not not necessary for them to show the full diagram at each stage.
⚠ At this stage, students would not have gotten to the point where we talk
about Normal Forms, however, the book mentions attribute atomicity when deriving the relational schema: pages 42, and 285 (derivation of the time_slot entity).
Graded Problem
Assume you have been hired to design a database for a small research center. The center is comprised of a few departments, each consisting of several researchers. In each department, one of the researchers is chosen to be the director. Every time that the center takes on a new project, the director assigns researchers to the project. During summer, the center offers student internships, for which they hire undergraduate students to work on specific projects. Every student is advised by one of the researchers.
You are given an initial E/R diagram design and you are requested to modify it based on some new requirements from the center. For each of the following parts, give an updated diagram, describe and justify the necessary changes.
Part 1
Right away, you notice that the current DB design does not enforce that there could be only one director per department. Briefly describe the design flaw and update the diagram to reflect the necessary changes. 5 points)
Solution:
In addition, you notice that the Researchers and Students share many common attributes. A better, more clear design of the diagram would be such that common attributes are grouped together into a separate entity. Describe the changes needed and update the diagram. Note that, an employee can only be either a Student or Researcher, not both. 9 points)
Solution:
Rubric:
Give 1 points if a new Entity is created with name "Person", "Human", etc.
Give 0 points for each correct attribute in the entities Person, Student, and Researcher. (max 2 points) Deduct 0 points for each incorrect attribute in the entity Person. Give 1 point if the entities Student and Researcher are connected through an ISA connector. Give 1 additional point if the ISA connector is of the disjoint type. Accept answers that have a "total" label (this could be a total disjoint generalization / specialization). Give 1 point if the Student entity has only one attribute: university. If the attribute is underlined, deduct 0 points. University is not a primary key). Give 1 point if the Researcher entity has only one attribute: seniority. If the attribute is underlined, deduct 0 points. Seniority is not a primary key). Give 1 point if the description of the changes is coherent, justified, and corresponds to the changes in the ER diagram. Deduct 1 point if some other changes are done to the diagram.
Part 3
As part of some recent internal changes in the research center, the departments will be divided into labs, each having a lab coordinator. Labs will have a distinct name and a set of locations that can be used for research. The naming for lab locations is composed of two parts: a building number and a room number.
Researchers can work across many labs, however, there can only be one coordinator per lab, and a researcher may coordinate multiple labs. The students are not considered as part of the lab, and labs do not have ownership of the projects, rather the researchers do. Describe the changes that should be made to your diagram and give an updated design. 15 points)
Solution:
Alternatively, the student might have a separate entity of the kind "Lab_Locations" or "Locations", which has two atomic attributes for "bldg_no" and "room_no". If this is the case use the following grading criteria:
Give 1 point if the Lab entity has a foreign key reference to the "Lab_Locations" entity. Give 1 point if the entity "Lab_Locations" has the correct attributes as described above.
Give 2 points if the relationship "Dept_Lab" was added. (hopefully they come up with a better name for it :) )
Deduct 1 point if the Dept_Lab relationship was not marked with double borders to indicate a weak entity.
Give 1 point if the Dept_Lab relationship is connected with double lines with the Lab entity.
Give 1 point if the Dept_Lab relationship is connected with an arrow with the Department entity.
Give 1 points if the relationship "Coord" or "Coordinates" is created.
Give 1 point if the relationship Coord is connected with double lines with the Lab entity.
Give 1 point if the relationship Coord is connected with an arrow to the Researcher entity.
Give 1 points if the relationship "lab_work" or "Works" is created.
Give 1 point if the relationship Lab_Work is connected with double lines with the Lab entity.
Give 1 point if the relationship Lab_Work is connected with double lines with the Researcher entity.
Give 1 point if the description of the changes is coherent, justified, and corresponds to the changes in the ER diagram.
Deduct 1 point if some other changes are done to the diagram.
Part 4
Now that the ER diagram is finalized, derive the relational schema for the DB. Specify the primary and foreign keys explicitly. 45 points)
Solution:
📌 Primary keys are shown in bold
Student(id, name, email, university)
Researcher(id, name, email, seniority, employed_department_name)
employed_department_name is a foreign key to Department
Project(id, start_date, end_date, budget, assigned_researcher_id)
assigned_researcher_id is a foreign key to Researcher
Department(name, bldg_no, director_id)
director_id is a foreign key to Researcher
Advisor(student_id, project_id, researcher_id)
student_id is a foreign key to Student project_id is a foreign key to Project researcher_id is a foreign key to Researcher
Lab(department_name, lab_name, coord_researcher_id)
department_name is a foreign key to Department coord_researcher_id is a foreign key to Researcher
LabLocations(bldg_no, room_no, department_name, lab_name)
The tuple (department_name lab_name) is a foreign key to Lab. (department_id, lab_name)
ResearcherLabWork(researcher_id, department_name, lab_name)
researcher_id is a foreign key to Researcher
Give 1 point if there is a relation named Student. Give 1 point if the primary key of the relation Student is "id". Give 1 point if there is a relation named Researcher. Give 1 point if the primary key of the relation Researcher is "id". Give 1 point if the relation Student contains an attribute "university", but not "seniority". Give 1 point if the relation Researcher contains an attribute "seniority", but not "university". Give 1 point if both relations have a "name" and "email".
Alternative 3 Max: 6 points - mostly correct solution)
Person(id, name, email)
Student(id, university, person_id)
person_id is a a foreign key to Person
Researcher(id, seniority, person_id)
person_id is a a foreign key to Person
Have 3 relations: "Person", "Student", and "Researcher". The relation Person has all the attributes shown in the diagram, and the relations Student and Researcher have foreign keys to the relation Person in addition to the distinctive attributes. This approach is unnecessarily convoluted (requires additional joins), although correct. If the student has this solution,
Give 1 point if there is a relation named Person. Give 1 point if the primary key and attributes in the relation Person are correct. Give 1 point if there is a relation named Student. Give 1 point if the primary key and attributes in the relation Student are correct.
Give 0 points if the foreign key in Student is separate from the primary key. Note that, Student cannot be used both as a foreign key to Person and a primary key to the relation Student, because primary keys need to be unique, and that setup wouldn't guarantee it. The Student has to have two separate id attributes: one acting as a primary key, and one as a foreign key to the relation Person. Give 1 point if the primary key and attributes in the relation Researcher are correct. Give 0 points if the foreign key in Researcher is separate from the primary key. Note that, Researcher cannot be used both as a foreign key to Person and a primary key to the relation Researcher, because primary keys need to be unique, and that setup wouldn't guarantee it. The Researcher has to have two separate id attributes: one acting as a primary key, and one as a foreign key to the relation Person.
💡 If the student has a different implementation for the inheritance that
seems to be correct, but it's not mentioned here, shoot an email to the grad TA (ani@brown).
Deriving from the Employed relationship (max 3 points)
Alternative 1 Max: 1 point - partially correct solution) Employed(researcher_id, department_name) Students might choose to have a separate relation called "Employed". In this case the relation must have attributes "researcher_id" and "department_name". This solution is correct, however, there is a lot of redundancy. If the student has this solution, the use the following grading schema: Give 0 points if the attributes are correct. Give 0 points if the primary key is correct. The primary key here is the tuple (researcher_id, department_name)
Have a relation called "Assigned" that has a primary key made up from the tuple (project_id, researcher_id). However this does not guarantee that the projects are assigned only to one researcher (as the arrow shows in the diagram), so this solution is not fully correct. Give 0 points if there is a relation named "Assigned" with the correct attributes. Give 0 points if the primary key is correct. Give 0 points if the foreign keys are correct.
Alternative 2 Max: 3 points - best solution) Project(id, start_date, end_date, budget, assigned_researcher_id) assigned_researcher_id is a foreign key to Researcher Have an attribute named "assigned_researcher_id" in the relation "Project" that acts as a foreign key to Researcher. Give 1 points if the relation "Project" has an attribute named something like "assigned_researcher_id" or "assigned_researcher". Give 1 points if it is explained that this acts as a foreign key to Researcher.
Deriving from the Department entity (max 3 points)
Department(name, bldg_no)
Give 1 point if there is a relation named "Department". Give 0 points if there is an attribute "name". Give 1 points if the primary key is "name". Give 0 points if there is an attribute "bldg_no". Deduct 1 point if there is an attribute "size" or "size()". Relational schemas must only have atomic attributes, and "size" is a derived attribute.
Deriving from the Director relationship (max 3 points)
Alternative 1 Max: 1 point - partially correct solution) Students might choose to have an attribute in the relation "Researcher" that is named something like "is_director_of". This attribute must act as a foreign key to the Department. However, this solution is not fully correct since not all researchers are Department directors, and this design would call for null values for this attribute. If the student has implemented exactly this alternative solution, as described here, then give 1 point for this part. Alternative 2 Max: 3 points - best solution) Department(name, bldg_no, director_id) director_id is a foreign key to Researcher The correct solution would have a "director_id" attribute in the relation Department instead, since all Departments have Directors. Give 1 points if there is an attribute "director_id" in the entity Department. Give 1 points if this attribute is specified to be a foreign key to Researcher.
Deriving from the Advisor relationship (max 5 points)
⚠ This is a non-binary relationship, and the book does not specify a
standardized way to derive a relational schema from this type of relationships. However, the student is expected to use common sense in how to best implement this.
The Lab entity is a weak entity, since the Dept_Lab relationshp is drawn with double borders and the attribute name is underlined with a dashed line. Therefore, the relation Lab must include a reference to the attached "strong" entity, and the primary key becomes the tuple (department_name, lab_name).
Give 1 point if the student creates a relation named Lab Give 0 for each correct attribute (max 1 point) Give 1 point if the primary key is specified to be the tuple. If only one of the attributes is chosen as a primary key, give 0 points. Give 1 point if the department_name is specified to be the foreign key. Deduct 1 point if the student creates a relation named Dept_Lab, or something that indicates that they derived a relation from the relationship weak_entity.
Deriving from the Coord relationship (max 3 points)
Alternative 1 Max 3 points - correct solution)
Lab(department_name, lab_name, coord_researcher_id) department_name is a foreign key to Department coord_researcher_id is a foreign key to Researcher Give 1 points if the student has an attribute named coord_researcher_id in the relation Lab. Give 1 points if the attribute is specified to be a foreign key to Researcher.
Alternative 2 Max 1 point - partially correct solution)
The student might have a separate relation for the relationship Coord. In this case, the solution wouldn't be correct since the design cannot guarantee that only one researcher coordinates the lab. In this case, give only 1 point if the attributes and keys are correct. Give 0 points otherwise.
Alternative 3 Max 1 point - partially correct solution)
The student might have an attribute "is_coordinator_of" in the Researcher relation to specify the lab that the Researcher would coordinate. In this case, the solution wouldn't be correct since not all researchers are coordinators, and it would call for null values in that attribute. In this case, give only 1 point.
Deriving the lab locations (max 5 points)
LabLocations(bldg_no, room_no, department_name, lab_name)
The tuple (department_name, lab_name) is a foreign key to Lab. (department_name, lab_name)
This relation is necessary since the labs can have multiple relations (the locations attribute is both multivalued and composite), and the attributes cannot be arrays. It's important to make sure that the student includes references for the lab locations and the lab keys.
Give 1 point the student creates a relation named "LabLocations", "Locations", etc. Give 0 points for each correct attribute. Note that the location is a composite attribute, so only the atomic attributes should be included, i. bldg_no and room_no. If the student also includes an attribute named "location", then deduct 0 points. (max 2 points) Give 1 point if the tuple (department_name, lab_name) is used as a foreign key to Lab.(department_name, lab_name). If they are specified to be foreign keys separately, not as a tuple, then deduct 0 points. Give 1 point if the primary key is set to be the tuple (bldg_no, room_no).
Deriving from the Lab_Work relationship (max 5 points)
ResearcherLabWork(researcher_id, department_name, lab_name)
researcher_id is a foreign key to Researcher The tuple (department_name, lab_name) is a foreign key to Lab. (department_name, lab_name)
HW2 rubric
Subject: Computer Science
Standard
- Discover more from:Computer Science835 Documents
- More from:Computer Science835 Documents
