Advanced Database Administration

Suppose you are given the following requirements for a simple database for the National Hockey League (NHL): 

The NHL has many teams.

o Each teams has a name, a city, a coach, a captain, and a set of players.

o Each player belongs to only one team.

o Each player has a name, a position (such as left wing or goalie), a skill level, and a set of     injury records.

o A team captain is also a player.

o  A game is played between two teams (referred to as host_team and guest_team) and has a date (such as May 11th, 2016) and a score (such as 4 to 2).

(a) Construct a clean and concise ER diagram for the NHL database. 

Write short note on:

Log based Recovery

Suppose you are given the following requirements for a simple database for the National Hockey League (NHL): 

The NHL has many teams.

o Each teams has a name, a city, a coach, a captain, and a set of players.

o Each player belongs to only one team.

o Each player has a name, a position (such as left wing or goalie), a skill level, and a set of     injury records.

o A team captain is also a player.

o  A game is played between two teams (referred to as host_team and guest_team) and has a date (such as May 11th, 2016) and a score (such as 4 to 2).

(b) Map this E-R model to relational. 

Consider the employee database, where the primary key are underlined.Give an expression in SQL for each of the following queries. 

employee(employee name, street, city)

works(employee name, company name, salary)

company(company name, city)

manages(employee name, manager name)

(f) Find the name of highest paying employee.

employees of the company Consider the employee database, where the primary key are underlined.Give an expression in SQL for each of the following queries. 

employee(employee name, street, city)

works(employee name, company name, salary)

company(company name, city)

manages(employee name, manager name)

(e) Find the company that the smallest payroll. 

Consider the employee database, where the primary key are underlined.Give an expression in SQL for each of the following queries. 

employee(employee name, street, city)

works(employee name, company name, salary)

company(company name, city)

manages(employee name, manager name)

(d) Find all employees who earn more than the average salary of all employees of their company.

Consider the employee database, where the primary key are underlined.Give an expression in SQL for each of the following queries. 

employee(employee name, street, city)

works(employee name, company name, salary)

company(company name, city)

manages(employee name, manager name)

(c) Find all employees in the database who live in the same cities and on the same streets as do their managers. 

Consider the employee database, where the primary key are underlined.Give an expression in SQL for each of the following queries. 

employee(employee name, street, city)

works(employee name, company name, salary)

company(company name, city)

manages(employee name, manager name)

(b) Find all employees in the database who live in the same cities as the companies for which they work.

Consider the employee database, where the primary key are underlined.Give an expression in SQL for each of the following queries. 

employee(employee name, street, city)

works(employee name, company name, salary)

company(company name, city)

manages(employee name, manager name)

(a) Find the names of all employees who work for "First Bank Corporation.

Discuss significance of check-points in transaction and Shadow-paging.Explain Dead-lock recovery scheme in transaction management. 

Write short note on:

 Constraints in SQL

Write short note on:

Oracle 8 architecture

Differentiate between:

(a) E-R model and Relational model.

What are functional dependencies in relation. Find the set of candidate key in the following relation R = {A, B, C, D, E) with set of FD'S {A->BC, CD->E, B->D, E->A}.

Discuss normalization and its significance. What are different types of normalization level, explain with illustration. 

Discuss Serializibility, Recoverability in terms of transaction management.

Discuss ACID properties of Database and Transaction management.

Differentiate super-key, primary key and foreign key with proper explanation with illustration. 

Discuss Database architecture and data independence.

Differentiate between:

(e) 3NF and BCNF normal forms.

Differentiate between:

(d) Lock based and Timestamp protocols. 

Differentiate between:

(c) SQL and QUEL.

Differentiate between:

(b) Distributed Database and object oriented database.