Introduction to Database

ER Diagrams Problems

1. Students in a college are identified by student id. They are described by name, date of joining, date of birth, contact number, address and subject. A student can have many contact numbers and can study more than one course. Address consists of house no, suburb and city. The college has many departments. Attributes of DEPARTMENT are department code (key attribute), department name and location. A student is admitted to only one department. A department must have at least one student admitted.

2. A college has staff from different countries. STAFF is identified by staff code and described by staff name, date of joining, degree and specialization. A staff can possess many degrees. COUNTRY is identified by country code, country name and continent. Staff belongs to at most one country.

3. There are many books supplies in the Sultanate. A supplier is identified by supplier code and described by supplier name, contact numbers and address (street No, suburb, city). College library orders Books from these publishers. Book has ISBN (unique), title, publisher, authors and release date. A supplier may supply one or more books or may not supply any book. A Book can be supplied by many suppliers. Number of copies supplied by the supplier is also recorded along with date of purchase.

4. In an organization, employees report to their managers who are also employees. Each employee is managed by at most one manager only. The employee who are managers manages at least one employee. Employee is identified by employee code, employee name, gender, doj and address that consists of house number, street number and city. Employee are from different countries. Country is identified by co_id and other attributes are name and population.

5. College offers many courses. Course has course code (identifier), course description and degree as its attributes. A course may have zero or one or more prerequisite courses. A course may be prerequisite for zero or one or more courses.

6. Lecturer has lecturer code (key), name, gender and salary. Each Lecturer can possess at the most one CIVIL CARD having attributes civilNo (key), date of expiry and profession. Course has course code (unique), title, credit hours and passing marks as its attributes. A lecturer can teach more than one course or may not teach a course. A course may be taught by more than one lecturer. When a lecturer teaches a course, semester and academic year should also be stored.

7. A lecturer belongs to a country and a country belongs to a Continent. Continent is identified by code and described by name. Attributes of country are country code (unique), country name and population. Lecturer has lecturer code (key), name, gender and salary. A lecturer can teach more than one course or may not teach a course. A course may be taught by more than one lecturer. When a lecturer teaches a course, semester and year should also be stored. Course has course code (unique), title, credit hours and passing marks as its attributes.

8. A library has books for all courses. Student has Id, name, cgpa, and gender as its attributes. A Book has book Id (unique), title, publisher, authors and release date. A student can borrow zero, one or more books. A book can be borrowed by more than one student. A book may not be borrowed at all. Course is identified by code, name and level. A course may have many books, but a book belongs to only one course.

9. Students in a college belongs a Wilayat and Wilayat belongs to a Region. Wilayat code (unique) and name must be stored. Similarly, region Id (unique) and name are also to be stored. Students are identified by student id. They are described by name, date of joining, date of birth, contact

numbers and address that consists of house no, suburb and city. The college has many departments. Attributes of DEPARTMENT are department code (key attribute), department name and location. A student is admitted to only one department. A department must have at least one student admitted. A student can enroll for at least one course. A course can be enrolled by many students or may not be enrolled at all. When a student enrolls a course, semester and year should also be stored.