Process for Putting a Relation into BGNF 1. The Board of Directors of Northwind have appointed Doug anus at their new CEO and Jeff Hogan as their operational manager. They are planning to devote their first two weeks in office to gain a bette understanding of Northwind's supply chain and marketing processes. As senior database analyst for Northwind, it is your responslbility to code appropriately structured SQL statements for retrieving the following information requested by Doug and Jeff. (6 points) H thacs a functional dopendancy hat hoss A. Mowa tha B. Waka the dobonm nant of hat funcsonal C. Leave a copy of the dotominant as a foregn a) The average, sum, maximum, and minimum per unit cost across all products in their produet line. They would also like to know the names of Northwind's products having per unit cost between 30 and 5o (both inclusive). b) A count and a listing of the cities and corresponding countries in North America (USA, Canada, key in the origial rlstion D. Create a reflerential imegrny constraint Mexico) where they have a customer base. relation is a candidate ky 2. You have been entrusted with developing a database for a library. With the intent of developing a blueprint for the database (the ER dlagram). your first task is to visit the library and intervlew library personnel to identify their data and informatlon requirements. Identify two questions that you would want to ask of the head librarian and cireulation desk personnel (note that your questions must be relevant to their respective job roles please avoid general questions like What information do you need to do your job?"). Explain your rationale in asking these questions. (2 points aductariRD of the sole auo B. PRESCRIPTION REEasl 2 PrgssDa uls Prgs D ts a Pa ano Pa AddrPhyl Ehwa aast rlD Partent ID of porent foe whom prescripcion ts tscued EhrdD Physidan ID of the ptysicam isutng the prestriprion Patiwt Nar 3. Identify six possible entities for a llbrary database. For each of these entitles, identify at least six attributes (l.e. columns]. Shortlist a potentlal primary key for each of these entities. [Use the notatlons depicted in your slides and textbook; e.g. PATIENT (PatlD, PatName, PatAddress, PatDOB, PatSSN)]. (6 points) 4. Apply the normalization principle to convert the two tables shown below to the BCNF. Your answer must clearly show the 4 steps (as applicable) involved in converslon to the BCNF (shown below). Use the notation for functional dependencles and tables [for example: StulD StuName StuMajor, StuDO). Briefly explain each of the 4 steps. (16 points) Process for Putting a Relation into BGNF 1. The Board of Directors of Northwind have appointed Doug anus at their new CEO and Jeff Hogan as their operational manager. They are planning to devote their first two weeks in office to gain a bette understanding of Northwind's supply chain and marketing processes. As senior database analyst for Northwind, it is your responslbility to code appropriately structured SQL statements for retrieving the following information requested by Doug and Jeff. (6 points) H thacs a functional dopendancy hat hoss A. Mowa tha B. Waka the dobonm nant of hat funcsonal C. Leave a copy of the dotominant as a foregn a) The average, sum, maximum, and minimum per unit cost across all products in their produet line. They would also like to know the names of Northwind's products having per unit cost between 30 and 5o (both inclusive). b) A count and a listing of the cities and corresponding countries in North America (USA, Canada, key in the origial rlstion D. Create a reflerential imegrny constraint Mexico) where they have a customer base. relation is a candidate ky 2. You have been entrusted with developing a database for a library. With the intent of developing a blueprint for the database (the ER dlagram). your first task is to visit the library and intervlew library personnel to identify their data and informatlon requirements. Identify two questions that you would want to ask of the head librarian and cireulation desk personnel (note that your questions must be relevant to their respective job roles please avoid general questions like What information do you need to do your job?"). Explain your rationale in asking these questions. (2 points aductariRD of the sole auo B. PRESCRIPTION REEasl 2 PrgssDa uls Prgs D ts a Pa ano Pa AddrPhyl Ehwa aast rlD Partent ID of porent foe whom prescripcion ts tscued EhrdD Physidan ID of the ptysicam isutng the prestriprion Patiwt Nar 3. Identify six possible entities for a llbrary database. For each of these entitles, identify at least six attributes (l.e. columns]. Shortlist a potentlal primary key for each of these entities. [Use the notatlons depicted in your slides and textbook; e.g. PATIENT (PatlD, PatName, PatAddress, PatDOB, PatSSN)]. (6 points) 4. Apply the normalization principle to convert the two tables shown below to the BCNF. Your answer must clearly show the 4 steps (as applicable) involved in converslon to the BCNF (shown below). Use the notation for functional dependencles and tables [for example: StulD StuName StuMajor, StuDO). Briefly explain each of the 4 steps. (16 points)