Consider the following relational schema: violations(VID BID SNu SNa Zip InD Bor BoI NVI NVD). (Abbreviations for Violation ID, Building ID, Street Number, Street Name, Zip Code, Inspection Date, Borough, Boro ID, Notice of Violation ID, Notice of Violation Description)   Assume the following functional dependencies hold on violations. VID → BID SNu SNa Zip InD Bor BoI NVI NVD BID → SNu SNa Zip Bor BoI NVI → NVD NVD → NVI Bor → BoI BoI → Bor SNu SNa Zip → Bor 1.Show a decomposition of violations into normal form. Show each step in your decomposition and the primary key for each table. 2. Find a functional dependency in the original that does not correspond to a key in your normalized scheme. 3. Is it possible that a legal database instance d (one that satisfies all keys) on your normalized scheme violates the functional dependency in question 9 in the sense that if you join all the relations in d together, the result r violates the functional dependency? Explain why or why not.

Consider the following database schema and specify the following queries or views in SQL. EMPLOYEE(SSN, name, salary, DNo, SupervisorSSN) DEPARTMENT (DNo, DeptName, MgrSSN) PROJECT(PNo, location, ProjName) WORKON (SSN, PNo, hours) Find the SSN and name of the employee who works on more than two projects. For each project, retrieve the PNo, ProjName, and the number of employees from 'Research' department who work on the project. Create a view called 'DEPT_INFO' that has the department name, the number of employees, and the total salary of the employees of the department.

For developing our queries, we will be using the following relational schema: Student(SID, name, username, major) Course(cname, description, department, weight) Offering(OID, cname, year, term, duration) Enrolled(SID, OID, grade) A course relation is the generic description of a course which is offered by a department. The offering relation is the specific instance of a course being taught in a given term, and is the entity which will be associated with the student enrollment. Students are enrolled in these offerings and achieve a mark. Express each of following queries in Relational Algebra: | Write a relational algebra query which will list the cname and description of all the courses which are being offering in the Fall 2018 term. 2 Write a relational algebra query which will list the name of all the students who have earned 60% in both 'COSC2P12' and 'COSC2P13'.

Use the following relational schema for this homework: Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date) • Reserves ®: 100,000 tuples stored in 1,000 pages. There are 100 boats. Each boat is equally poplar (i.e., each has the same number of reservations). • Sailors (S): Stored in 500 pages. There are 10 distinct rating levels (1 to 10), each with the same number of sailors. 1. The sid column takes up 20% of the data size of the Reserves relation. The sid and sname columns take up 70% of the data size of the Sailor relation. a. If you are given 3 memory pages, describe an efficient execution plan for the execution tree in the right. b. Estimate the processing time (I/O cost) of your computation stratgegy. You do not have to include the cost of writing the query result in your analysis. 2. If you have 4 memory pages, explain a better computation strategy to take advantage of…