Assignment I

Autumn 2006, IT 403 FIP

Develop ER/EER model for the following. You can add additional information if necessary. For the problems where detailed description is not provided, you can assume a typical situation and write down the information requirements for it and accordingly develope the ER/EER model.
Convert the ER/EER models to relational models.
Develop a small application using a suitable RDBMS to insert, update and query the database (any one)

A student is described by a unique Roll Number, Name Adress, and Semester. Each student enrolls himself in an Academic programme offered by a Department. Academic programmes have programme name(unique), duration, a programme code(unique) and a list of courses (both core and elective course) while the departments have department code (unique), department name (unique), HoD who is a Teacher and list of courses offered by it. Each teacher is described by employee code (unique), name, department and designation. A student registers some courses in a semester. A course is described by a unique course number, title of the course, credit allotted for the course and offering department. Database stores the grades obtained by different a student in different courses registered by him/her in different semesters. Database also stores information about the courses offered by a department in a semester, the corresponding teacher(s) for each course.

It is desired to keep track of information about votes taken in the a state assembly during the current five year term. The database needs to keep track of each assembly constituency's name, district. Each MLA is described by their names and, includes constituency represented, the start date when they were first elected and the political party they belong to. The database keeps track of each bill (i.e. proposed law), and includes the bill name, the date of vote, whether the bill passed or failed and the sponsor i.e. MLA who proposed it. the database keeps track of how each MLA voted on each bill.

A database is being constructed to keep track of the teams and games of a football league. A team has a number of players, not all of whom participate in each game. It is desired to keep track of the player participating in each game for each team, the position they played in the game, and result of the game. Each player is described by their name, address and age. Include additional information if necessary.

It is necessary to construct a database to keep track of the passed out students of Tezpur University (Alumni). The students are described by Name, TU registration number, home address, current address, present job and organization, e-mail address. It is desired to keep track of the programme(s) the student completed at TU and the CGPA. It is also necessary to keep track of experiences of the student after leaving TU, which includes organization name, job position and duration.

A bank database to store information about its customers, accounts(savings a/c, current a/c...), loans(car loan, housing loan, ...), transaction made by customers for each loan(payments)/account(withdrawal, deposite).

Library information system : Database should store information about books, journals, megazines etc., Their issue/receipt/reservation. It should be possible to search books by author, title, subject. Similarly journals should possible to search by subject area, publisher etc.

Hospital information system : Patients - indoor/outdoor, medicines/lab tests(including results) prescribed to patients, information if a patient if referred to other expert/hospital. Doctors - specialization, patients attended etc. Different wards/beds and patients alloted to them etc.