need sql expert who can create databases

Using the Part 1 “SmallVille Library” narrative, design and document a logical data model using Erwin. You should also make and document any reasonable assumptions where you feel the case is vague or ambiguous or missing any data necessary to meet the requirements. However you cannot ignore or overrule the requirements that are explicitly stated. Show all cardinality, prime keys and foreign keys. Use verbs to clarify your relationships. Show only the attributes that are contained explicitly in the case. Only create a new identifier for an entity if there a no existing attributes that can be reasonably used to define a primary key

Part 2 – 100 marks

Given the attached physical and logical ERD in ProjectAssign2F16ERD undertake the following:

1. Using SQL Server create a database called Project2

2. Define all primary keys, foreign keys and data types (20 marks)

3. Populate the tables with sufficient data to adequately test all aspects of your queries. (See Note below). Failure to do so will lose you marks

4. Develop the SQL queries to satisfy the questions below – 80 marks.

No views should be employed in this assignment. While no question would require it you may use dynamic temporary tables as a technique.

--1. Show for each zoo the number of different species there are for the animals in that zoo.

--2. List the zoo name, employee name and salary of each employee that is a world’s best expert in a species. Sequence the output by employee name within zoo name.

--3. List the detail of animals that are in captivity in any zoo whose mother is currently in the Boston zoo

--4. Show for each zoo in Canada a count of captive animals. Sequence the output by highest to lowest count

--5. Show for each species a count of the species qualified employees that are in total for zoos in the USA

--6. List the details for employees in any zoo in Canada with a salary of at least 75000 or that are qualified in the Tiger species. Sequence the output by employee name

--7. List the details for all animals born in captivity in 2006 that belong to an endangered species (status = E)

--8. List the details for the zoos in China that have more than 2 animals that belong to the Tiger species

--9. List the names, gender and salaries of all male employees that are the world’s best expert for a threatened species (status = T)

--10. List the details of the zoo that has the employee with the highest salary

--11. List the details for any species for which there are animals held in any zoo in China

--12. List the details for the zoos that have animals belonging to more than 3 different species. Sequence the output alphabetically by zoo within city

--13. List details for the animals that have a mother that is resident in a zoo that is different from their child's current zoo

--14. Show the name of any country that has more than 2 zoos with elephants

--15. List the species details for the species that have a world’s best expert working in a zoo in the USA which also has captive animals of that same species. Show each species only once

--16. List the details for the employee that has the lowest salary for someone qualified in the Tiger species

--17 List the details for any endangered species for which there are more than 4 animals in Canadian zoos.

--18 List the details of any zoo that has more than 3 Tigers born in 2015

--19. Show the count of how many species qualified employees are at the Toronto zoo.

--20 List the details of mothers in Canadian zoos that have more than 2 offspring in total resident in any zoo.

Database Administration Engineering MySQL Software Architecture SQL

Project ID: #12226624

About the project