BA 3672 Assignment 4 ER And Database Assignment Due Refer To
Ba 3672assignment 4 E R And Database Assignmentdue Refer To The Sch
Draw an E-R diagram based on the description below (Use the notation learned in the lecture). A database is being constructed to keep track of data about UNB students’ status of job applications. A student can apply to more than one company and a company has many students who applied to the company. The UNB also keeps track of an advisor who helps each student to practice for job interviews. Each student is assigned to only one advisor and each advisor helps at least one student. For each student, the database is to hold the students’ ID (primary key), name, and an email address. For each company, the database is to hold the company ID (primary key), name, and address. For each advisor, the database is to hold the tutor ID (primary key), and a telephone number.
Convert the E-R diagram into the corresponding relations (tables). Show the primary keys and other fields in the relation. Underline the primary keys, dotted underline the foreign key, and use arrows to indicate the foreign keys and the corresponding primary keys.
Paper For Above instruction
The assignment requires constructing an Entity-Relationship (E-R) diagram based on a scenario involving students, companies, and advisors at the University of New Brunswick (UNB). This diagram visually represents the relationships among these entities to facilitate a clear understanding of how data entities interact within the database. Following the creation of the diagram, the next step involves transforming the diagram into relational tables (relations). Each table must be properly designed with primary keys underlined, foreign keys indicated with dotted underlines, and arrows used to denote the relationships between entities, ensuring referential integrity.
Constructing the E-R diagram
In developing the E-R diagram, the primary entities are students, companies, and advisors. Students are characterized by student ID (primary key), name, and email address. Each student can submit multiple applications to different companies, indicating a many-to-many relationship between students and companies. This relationship can be realized through an associative entity, perhaps named "Applications," which would include foreign keys referencing both students and companies, together with additional attributes such as application date or status if needed.
The company entity consists of company ID (primary key), name, and address. Since each student applies to multiple companies, and each company has many students applying, the relationship is many-to-many, managed via the "Applications" associative table. Meanwhile, each student is associated with a single advisor, establishing a many-to-one relationship from students to advisors. The advisor entity includes tutor ID (primary key) and telephone number, with each advisor assisting at least one student, forming a one-to-many relationship.
Relations and their design
Turning the E-R diagram into relation schemas involves creating separate tables for each entity and the associative relationship. The Student table will have attributes: StudentID (underline), Name, Email, and AdvisorID (dotted underline) referencing the Advisor. The Advisor table contains TutorID (underline) and Telephone. The Company table includes CompanyID (underline), Name, and Address. The Applications table functions as a junction table for the many-to-many relationship, with StudentID and CompanyID as foreign keys (both dotted underline). To clearly illustrate the relationships, arrows indicate foreign key dependencies pointing to the primary keys of related tables.
Sample relation schemas
- Student: StudentID (PK), Name, Email, AdvisorID (FK)
- Advisor: TutorID (PK), Telephone
- Company: CompanyID (PK), Name, Address
- Applications: StudentID (PK, FK), CompanyID (PK, FK)
This structure ensures each entity’s primary key is underlined, and foreign keys are clearly marked with dotted underlines, maintaining relational integrity aligns with the ER diagram's relationships. Proper use of arrows signifies foreign key dependencies, guiding the relational implementation process effectively.
References
- Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.