Relational Database Management System Questions and Answer
Question 1
i) Convert the following ER diagram to table structure in a relational database. The attribute capacity may have values such as captain, vice captain, member etc.
Based on the table Structures obtained above answer the below questions.
ii) Write an SQL statement to get a list of sports that do not have captains.
iii) Write an SQL statement to obtain a list of students (StudentID and Name) who participate in any sport as a captain.
Answer
i)
Student(StudentID, Name)
Sport(SportID, Name)
StudentSport(StudentID, SportID, Year, Capacity)
ii)
Select distinct SportID from StudentSport where capacity <> "captain"
iii) Select Student.ID, Student.Name from Student, StudentSport where Student.StudentID=StudentSport.StudentID and StudentSport.Capacity="captain"
Question 2
The candidates who have been selected for university entrance should register for the given academic year with the university given to them. Candidate who do not register before the Last date announced by each university will lose their university entrance. Once a candidate registers with given university , the candidate becomes a registered student of that university. Registered students can apply for financial support. A student can get many financial support. These financial supports could be either full or partial. All registered students will receive a laptop. However, its ownership cannot be transferred to another student. The user requirements of the above system are listed below. A user shall be able to obtain.
i) a list of students registered for a given academic year with a given university.
ii) information (such as model, serial number, warranty period) about the laptops given to each student.
iii) list of students who applied for financial support.
Draw an entity Relationship(ER) diagram required to design a database to represent the above system description and to satisfy the user requirements. State all your assumptions clearly.
Answer
No comments:
Post a Comment