tell me price
Section –A Task 2: ER Diagram 1. Carefully read following requirements and draw an ER diagram · Professor is with the following attributes: SSN (unique), age and a name, rank and research specialty. · Projects have a project number (unique), a sponsor (e.g. NSF), a starting date, ending date and a budget. · Graduate students need to store information about the degree program (e.g. M.S. or Ph.D.) that they are enrolled. · Each project is managed by one professor (known as the project’s principal investigator). · A project must have a principal investigator. · Each project is worked on by one or more professors (known as co-investigators). · Professors can manage and/or work on multiple projects. · Each project is worked on by one or more graduate students (known as project’s research assistants). 2. Carefully read following requirements and draw an ER diagram. · Students contain an id (unique), name and an address. · There are academic semesters containing a semester id (unique), semester and year. · There are courses offered during academic semesters. A course has a number (unique), name and credits. · Students make payments. A payment has receipt number (unique), amount, currency and date. · Payments can be classified into Tuition, Examination and other. · A Tuition payment is made for an academic semester · Students register for courses offered during a particular semester. The registered date must be stored in the database. Task 3: ER Diagram to Relational Model 1. Map following ED diagrams into Relational model. Clearly indicate foreign keys primary keys. NOTE : In the following diagrams arrow is pointing to the ONE side. a. 1 M Employee( SSN, Name, lot) Department ( did ,dname, budget, SSN) Works_in( did, SSN ,since) Please highlight here 1: N and N:M mapping. Discuss 1: 1 mapping as well. b. IT DA 1001 Tutorial 2 IT DA 1001 Tutorial 2 Copyright©2015-2018VIT,AllRightsReserved.2 Copyright©2015-2018VIT,AllRightsReserved.6 Employee(SSN,name, lot) Please highlight how to map the weak entity. Discuss why dependant become a weak entity. Policy(PolicyID,cost,SSN) Dependents( Policyid,pname,age) Exercise 2.1 A company database needs to store information about employees (identified by ssn, with salary and phone as attributes), departments (identified by dno, with dname and budget as attributes), and children of employees (with name and age as attributes). Employees work in departments; each department is managed by an employee; a child must be identified uniquely by name when the parent (who is an employee; assume that only one parent works for the company) is known. Section-B June 2018 Task 1: Review Questions 1. What is a relation? 2. What is an Insert Anomaly? Explain using an example. IT DA 1001 Tutorial 3 3. What is an Update Anomaly? Explain using an example. 4. What is a Delete Anomaly? Explain using an example 5. What is a dependency? 6. Explain the FOUR ways you can map EERD to Relational Model? 7. What is a referential integrity? Explain using an example. Task 2: Redesign Problems 1. What is wrong with the following set of tables, designed for a small ZOO? How would you redesign them? i. Zoo Database ANIMAL KEEPER CAGE Animal No Species Name Cage No Cage Location Keeper Name Keeper Name Address Postcode Phone Age Cage No Animal 1 Animal 2 Animal 3 Animal 4 Keeper Name Animal Keeper Cage Secies Animal No Secies ID Cage No Keeper Id Keeper ID Keeper Name Postcode Phone Age Address Cage No Species ID Cage Location Species Name ii. Cabin and Tourists Copyright © 2015-2018 VIT, All Rights Reserved. 2 CABINS TOURIST Cabin No PeopleCatered Ensuite (Y/N) Tourist_No Kitchen (Y/N) Date Arriving Date Leaving Type Of Shelter Tourist No Tourist Name Tourist Address Tourist Phone Date Booked Cabin_No1 Cabin_No2 Cabin_No3 Total Cost Duration of Stay Cabins Tourist Booking Invoice Cabin No Peoplecatered Ensuite (Y/N) Kitchen (Y/N) PricePerNight Type of Shelter TouristNo BookingNo Invoice ID Invoice Date Total Cost Booking No TouristName BookingDate TouristAddress Date Arriving TouristPhone Date Leaving Duration of Stay Cabin No Tourist No Section-c June 2018 Task 1: Normalization 1. Convert each of the following schemas to 3NF, showing all intermediate stages, that is, 1NF and 2NF. a. BRANCH (Branch#, Branch_Addr, (ISBN, Title, Author, Publisher, Num_copies)) IT DA 1001 Tutorial 4 1NF: BRANCH (Branch#, Branch_Addr) STOCKS (Branch#, ISBN, Title, Author, Publisher, Num_copies) 2NF: BRANCH (Branch#, Branch_Addr) STOCKS (Branch#, ISBN, Num_copies) BOOK (ISBN, Title, Author, Publisher) 3NF: BRANCH (Branch#, Branch_Addr) STOCK (Branch#, ISBN, Num_copies) BOOK (ISBN, Title, Author, Publisher) b. CLIENT (Client#, Name, Location, Manager#, Manager_name, Manager_location, (Contract#, Estimated_cost, Completion_date, (Staff#, Staff_name, Staff_location))) 1NF: CLIENT (Client#, Name, Location, Manager#, Manager_name, Manager_location) (intermediate step) CONTRACT (Contract#, Client#, Estimated_cost, Completion_date, (Staff#, Staff_name, Staff_location)) CONTRACT (Contract#, Client#, Estimated_cost, Completion_date) STAFF(Staff#, Contract#, Staff_name, Staff_location) 2NF: CLIENT (Client#, Name, Location, Manager#, Manager_name, Manager_location) CONTRACT (Contract#, Client#, Estimated_cost, Completion_date) STAFF (Staff#, Staff_name) ASSIGNMENT (Staff#, Contract#, Staff_location) 3NF: CLIENT (Client#, Name, Location, Manager#) MANAGER(Manager#, Manager_name, Manager_location) IT DA 1001 Tutorial 4 CONTRACT (Contract#, Client#, Estimated_cost, Completion_date) STAFF (Staff#, Staff_name) ASSIGNMENT (Staff#, Contract#, Staff_location) Exercise 4.1 Convert following schema to 3NF, showing all intermediate stages, that is, 1NF and 2NF. PATIENT (Patient#, Name, DOB, Address, (Prescription#, Drug, Date, Dosage, Doctor#, Doctor, Secretary)). Section-D June 2018 Task 1: Using Northwind Database, write SQL statements for the following data retrieval operations. Note: You may need to refer to the schema for the exact field names while framing the queries to the following questions. While framing the question, the words ‘code’, ‘number’ or ‘ID’ may have been synonymously used. For instance, ‘Customer Number’ when used may refer to a field called CustomerID. Similarly, wherever the word ‘name’ is used, appropriate interpretation may be needed based on the schema – for instance if ‘customer name’ is required to be