A person can be an employee, alumnus, or a student. For a person, P_id (identifier), date of birth, gender, and contacts are stored. A person can have more than one contact number and there are no other types of person that exist. An instance of a person can belong to more than one type. Date hired and salary is the unique attributes of the employee. For alumnus, the degree is required to be stored that comprises of year and designation. The major department is the attribute of the student. A student can be a graduate student or an undergraduate student but not both at the same time. No other type of student exists. For graduate student test score is stored and for undergraduate student class standing is stored. Employees can be faculty, staff, or others but not both at the same time. For faculty, rank is stored and for staff, the position is stored. An undergraduate student can issue books from the library. An undergraduate student can issue at most one book and a book is issued by no student or one student. Issue date, return date and fine amount are stored when undergraduate student issues a book. Book has attributes b_id, b_title, and b_category and b_id is the primary key for the book. An undergraduate student can get enrolled for more than one course and a course is enrolled by at least one undergraduate student. The course has attributes course_id (identifier), credit, description, and code. Courses can have one, none, or many prerequisites. Each course is a prerequisite of zero, one or many courses. Department offers one or more courses and a course gets offered by one or many departments. Every instance of the department has a unique dept_id and other attributes are name and location that need to be stored. Location is composed of city, postal code, and zip code.
Create an Entity-Relationship Model to represent the data requirements of the problem given below. Specify the cardinalities of relationships and all-important constraints. Also mention the assumptions if you have made any.
A person can be an employee, alumnus, or a student. For a person, P_id (identifier), date of birth, gender, and contacts are stored. A person can have more than one contact number and there are no other types of person that exist. An instance of a person can belong to more than one type. Date hired and salary is the unique attributes of the employee. For alumnus, the degree is required to be stored that comprises of year and designation. The major department is the attribute of the student. A student can be a graduate student or an undergraduate student but not both at the same time. No other type of student exists. For graduate student test score is stored and for undergraduate student class standing is stored. Employees can be faculty, staff, or others but not both at the same time. For faculty, rank is stored and for staff, the position is stored. An undergraduate student can issue books from the library. An undergraduate student can issue at most one book and a book is issued by no student or one student. Issue date, return date and fine amount are stored when undergraduate student issues a book. Book has attributes b_id, b_title, and b_category and b_id is the primary key for the book. An undergraduate student can get enrolled for more than one course and a course is enrolled by at least one undergraduate student. The course has attributes course_id (identifier), credit, description, and code. Courses can have one, none, or many prerequisites. Each course is a prerequisite of zero, one or many courses. Department offers one or more courses and a course gets offered by one or many departments. Every instance of the department has a unique dept_id and other attributes are name and location that need to be stored. Location is composed of city, postal code, and zip code.
Step by step
Solved in 2 steps with 1 images
