I attached the slide deck referenced in the homework in question 2
ER → Relational Mapping Mark Fontenot, PhD Northeastern University Material borrowed from Dr. Derbinsky’s slides for 3200 with permission. Mandatory vs Optional: Ex 1 Profs: (1, Alford) (2, Quicksall) (3, Norris) Classes: (CS, 2500) (CS, 2510) (CS, 1800) (CS, 3200) Profs Classes teach Description: A professor can teach more than one class, but doesn’t have to be teaching any classes. Each class is taught by 1 professor and must have a professor assigned to it. Mandatory vs Optional: Ex 1 Profs: (1, Alford) (2, Quicksall) (3, Norris) Classes: (CS, 2500) (CS, 2510) (CS, 1800) (CS, 3200) Profs Classes teach Description: A professor can teach more than one class, but doesn’t have to be teaching any classes. Each class is taught by 1 professor and must have a professor assigned to it. 0…M Mandatory vs Optional: Ex 1 Profs: (1, Alford) (2, Quicksall) (3, Norris) Classes: (CS, 2500) (CS, 2510) (CS, 1800) (CS, 3200) Profs Classes teach Description: A professor can teach more than one class, but doesn’t have to be teaching any classes. Each class is taught by 1 professor and must have a professor assigned to it. 0…M 1…1 Mandatory vs Optional: Ex 2 Profs: (1, Alford) (2, Quicksall) (3, Norris) Classes: (CS, 2500) (CS, 2510) (CS, 1800) (CS, 3200) Profs Classes teach Description: Every professor must be teaching at least 1 class (but could be more). Each class is taught by 1 professor and must have a professor assigned to it. Mandatory vs Optional: Ex 2 Profs: (1, Alford) (2, Quicksall) (3, Norris) Classes: (CS, 2500) (CS, 2510) (CS, 1800) (CS, 3200) Profs Classes teach Description: Every professor must be teaching at least 1 class (but could be more). Each class is taught by 1 professor and must have a professor assigned to it. 1…M 1…1 Where are we? Requirements Analysis Conceptual Design Choose a DBMS and Map Conceptual Physical Design System Implementation and Tuning ‹#› emp_dep (depends on) locations ‹#› Department Employee Project Dependent name fName midInitial lName bDate IDNum address salary gender locations number name location number name name gender bDate relationship dept_emp (houses) dept_mgr (manages) emp_proj (works_on) dept_proj (controls) hours startDate emp_dep (depends on) emp_mgr (supervises) supervisor supervisee 1…1 0…N 1…1 0…N 1…1 1…N 1…1 0…1 0…M 0…N 1…1 0…N Dependent ‹#› How We Will Describe Relations ‹#› SSN FName LName Bdate SSN SeqNum Salary From To Employee: SalaryInfo: Step 1: Handling Strong Entity Types For each strong entity type, create a corresponding relation that includes all the simple attributes include simple attributes of composite attributes. Choose one of the candidate keys as the primary key. primary key may be a composite key. Note any other candidate keys will be marked as unique attributes when creating tables. useful in performance tuning / indexing later in physical design ‹#› emp_dep (depends on) locations ‹#› Department Employee Project Dependent name fName midInitial lName bDate IDNum address salary gender locations number name location number name name gender bDate relationship dept_emp (houses) dept_mgr (manages) emp_proj (works_on) dept_proj (controls) hours startDate emp_dep (depends on) emp_mgr (supervises) supervisor supervisee 1…1 0…N 1…1 0…N 1…1 1…N 1…1 0…1 0…M 0…N 1…1 0…N Dependent ‹#› Step 1: Result for Example ERD ‹#› Step 2: Handling Weak Entity Types For each weak entity type, create a corresponding relation that includes all simple attributes. Add as a foreign key all of the primary key attribute(s) from the corresponding strong entity type The primary key of the new relation is the combination of the PK attributes of the owner entity type and the partial key (discriminator) of the weak entity ‹#› Step 2: Result for Example ERD ‹#› Step 3 - Handling Binary 1:1 Relationships General Idea: Foreign Key used to map the relationship Choose one relation as S, the other as T Better if S has mandatory participation b/c it reduces the number of null values. Add to S all the simple attributes of the relationship Add as a foreign key in S the primary key attributes of T ‹#› Step 3: Result for Example ERD ‹#› Step 4: Handling Binary 1:N Relationships Assume S is the relation on the many side of the relationship; the other relation is T Add as a foreign key in S all of the primary key attribute(s) of T. Add any simple attributes of the relationship to S. ‹#› Step 4: Result for Example ERD ‹#› Step 5: Handling Binary M:N Relationships Create a new relation S (sometimes called the bridge table) In some ER Diagrams, you may see this indicated with a box around the diamond Add as foreign keys to S the primary keys of both relations; their combination forms the PK of S. Add any simple attributes of the M:N relationship to S. ‹#› Step 5 - Result for Example ERD ‹#› Step 6 - Handling MultiValued Attributes If number of instances is unbounded or unknown Create a new relation S Add as foreign keys to S the primary keys of the corresponding relation Add the attribute to S (if composite, the simple attributes). The combination of all attributes in S forms the primary key of S. If number of instances is bounded and known Add additional attributes to relation, one for each potential instance. ‹#› Step 6 - Result for Example ERD ‹#› Review Step 1: Handle Strong Entity Types Step 2: Handle Weak Entity Types Step 3: Handle 1:1 Relationships Step 4: Handle 1:M Relationships Step 5: Handle M:N Relationships Step 6: Handle Multivalued Attributes ‹#› HW05 - Univ ER Diagram Students Sections Departments Courses takes majors minors Name Code College Department Credits Number Description Name belongs to Course Number Number Year Semester Instructor Student ID First Year Semester SSN Last Name Perm. Address Unique Num. Grade ID 0… 1 0… M 0…N 0… N 1…1 1… 1 0…N 1…1 Email CS 3200 - Database Design - Fontenot Homework 05 - ER Modeling & Relational Mapping You will submit this assignment as a single PDF to GradeScope. It is vitally important that you complete the submission process on GradeScope wherein you indicate the location of each of your answers for the assignment. Failure to do so will result in a grade of 0 on the assignment. All ER Diagrams need to be created in a drawing app such as Google Draw, LucidChart, OmniGraffle, or even PowerPoint; no handwritten/drawn diagrams. All ER Diagrams must use the notation consistent with the examples done in class. Each relationship should have participation and cardinality for all involved entities using the 0…M style notation. Do not use a double line to indicate mandatory participation. Additionally, no crow’s foot or UML-style notation. Question 1. Below (in italics), you’re given a bulleted, narrative explanation of a system, and below that, you’re given an ER diagram. Assume that the narrative is the ground truth for the system under review. Find at least ten mistakes in the ER diagram. You will submit: 1. a brief English description of each of the 10 errors you find, and 2. an updated ER diagram correctly reflecting the requirements from the narrative. You get equal credit for each corrected mistake; even if correcting a mistake requires several changes to the diagram. For each correction, you will receive half of the points for your textual description of the mistake, as well as justification with respect to the narrative and ERD, and half if you fully correct the issue in your diagram. Please number each textual description, and label the associated change(s) in the diagram, such that your submission is clear and understandable. Each error is worth 6 points (3 for explanation and 3 for proper diagram update. Narrative: · The university keeps track of each student’s name, student ID, social security number, permanent address, major department, and minor department (if any). Some applications need to refer to the city, state, and ZIP code of the student’s permanent address, as well as the student’s last name. Both SSN and student ID have unique values for each student, but the university has chosen student ID as the unique identifier for students. Every student is allowed to have up to 4 email addresses. · Each department is described by a unique name, a unique department code, and a college name. The department code serves as the primary key of the Departments table · Each course has a course name, description, offering department, unique number among all courses in the department offering the course, number of credits. · Each section has an instructor, semester, year, course, and section number. The section number distinguishes sections of the same course that are taught during the same semester/year. · When a student takes a section of a course, they receive a letter grade and a numeric grade. Given a numeric grade from the instructor, the letter grade is assigned based upon the university’s standard grade-conversion table. Question 1 - Part 1: The 10 errors you found. 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. Question 1 - Part 2: Corrected Diagram. You can start with the Google Drive Drawing found > here < or recreate the diagram in another tool of your choice. indicate any changes or additions using a different colored block/text. please label the associated change(s) in the diagram with your description from q1 part 1 above, such that your submission is clear and understandable. no handwritten diagrams will be graded. question 2: convert the corrected er diagram from question 1 into a relational diagram as demonstrated in lecture slide deck 15 (er → relational mapping). provide the diagram as it would exist after each step of the mapping process. (in other words, you’ll have 6 diagrams to submit) (40 points total) or="" recreate="" the="" diagram="" in="" another="" tool="" of="" your="" choice.="" indicate="" any="" changes="" or="" additions="" using="" a="" different="" colored="" block/text.="" please="" label="" the="" associated="" change(s)="" in="" the="" diagram="" with="" your="" description="" from="" q1="" part="" 1="" above,="" such="" that="" your="" submission="" is="" clear="" and="" understandable.="" no="" handwritten="" diagrams="" will="" be="" graded.="" question="" 2:="" convert="" the="" corrected="" er="" diagram="" from="" question="" 1="" into="" a="" relational="" diagram="" as="" demonstrated="" in="" lecture="" slide="" deck="" 15="" (er="" →="" relational="" mapping).="" provide="" the="" diagram="" as="" it="" would="" exist="" after="" each="" step="" of="" the="" mapping="" process.="" (in="" other="" words,="" you’ll="" have="" 6="" diagrams="" to="" submit)="" (40="" points="">