PART A

1. Highlight the major differences between structured, unstructured, and semi-structured data.

2. Illustrate how referential integrity constraint is represented using a foreign key.

3. Distinguish between super key, candidate key and primary key using a real convincing example.

4.Design an ER diagram for the following scenario:.

There is a set of teams, each team has an ID (unique identifier), name, main stadium, and to which city this team belongs. Each team has many players, and each player belongs to one team.

Each player has a number (unique identifier), name, DoB, start year, and shirt number that he uses. Teams play matches, in each match there is a host team and a guest team. The match takes place in the stadium of the host team.

For each match we need to keep track of the following: The date on which the game is played The final result of the match. The players participated in the match. For each player, how many goals he scored, whether or not he took yellow card, and whether or not he took red card.

During the match, one player may substitute another player. We want to capture this substitution and the time at which it took place. Each match has exactly three referees. For each referee we have an ID (unique identifier), name, DoB, years of experience. One referee is the main referee and the other two are assistant referee.

PART B

5. EMPLOYEE(ENO, NAME, ADDRESS, DOB,AGE, SALARY, DNUM, SUPERENO)

DEPARTMENT(DNO, DNAME, DLOCATION, DPHONE, MGRENO)

PROJECT(PNO, PNAME, PLOCATION, PCOST, CDNO)

In the above relations DNUM is a foreign key that identifies the department to which an employeebelongs. MGRENO is a foreign key identifying the employee who manages the department.

CDNO is a foreign key identifying the department that controls theproject.

SUPERENO is a foreign key identifying the supervisor of each employee.

Write relational algebra expressions for the following queries:-

(a) Names of female employees whose salary is more than 20000.

(b) Salaries of employee from ‘Accounts’ department

(c) Names of employees along with his/her superviser’s name

(d) For each employee return name of the employee alog with his department name and the names of projects in which he/she works.

6. a. With the helpof an example, establish that a relationship of degree 3 need not be equivalent to three relationships with degree 2.

b. Show how the division operation can be implemented using the basic relation algebra operations. Illustrate its application through a realistic example.

7. What is meant by correlated nested query? Illustrate with a real example.

8. List any two uses of triggers. Write a trigger which raises an error if more than three students are deleted simultaneously from the STUDENT table.

9. List out and illustrate the two anomalies in relational databases design.

10. Given the FDs P→Q, P→R, QR→S, Q→T, QR→U, PR→U, write the sequence of Armstrong’s Axioms needed to arrive at a. P → T b. PR → S c. QR → SU