SQL Statements

1.) You have been entrusted with developing a database for a healthcare facility. Your first task is to visit the healthcare facility and interview hospital personnel to identify their data and information requirements. Identify two questions that you would want to ask of the hospital CEO and physicians (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.) Identify five possible entities for a hospital/healthcare facility database. For each of these entities, identify at least five attributes. Shortlist a potential primary key for each of these entities. [Use the notations depicted in your slides and textbook; e.g. EQUIPMENT_REPAIR (ItemNumber, Type, AcquisitionCost, RepairNumber, RepairDate, RepairAmount)].

3.) Identify five possible entities for a university database. For each of these entities, identify at least five attributes. Shortlist a primary key for each of these entities. [Use the notations depicted in your slides and textbook; e.g. EQUIPMENT_REPAIR (ItemNumber, Type, AcquisitionCost, RepairNumber, RepairDate, RepairAmount)].

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 conversion to the BCNF (shown below). Use the notation for functional dependencies and tables as shown in your slides and textbook [for example, ItemNumber -> (Type, AcquisitionCost)]. Briefly explain each of the 4 steps.

A.) STUDENT (StuID, StuName, StuMajor, ProfID, ProfName, ProfOffice)

StuID: Student ID StuName: Student Name StuMajor: Student Major

ProfID: ID of the Professor who acts as Advisor to student (only one advisor per student) ProfName: Name of Professor ProfOffice: Office address of Professor

B.) PATIENT (PatID, PatName, PatAddress, InsComp, InsAddr, InsPlan, PhyID, PhyName, PhySpec, PhyAddr)

PatID: Patient ID PatName: Patient Name PatAddress: Patient Address InsComp: Name of the primary insurance company of the patient (only one primary insurance company per patient) InsAddr: Address of the primary insurance company

InsPlan: Name of the insurance plan PhyID: Physician ID of the patients primary physician (only one per patient) PhyName: Name of the patients primary physician PhySpec: Specialization of the patients primary physician PhyAddr: Address of the patients primary physician

Please type your answers into this document and submit via Canvas on or before 02/24/2018.

Process for Putting a Relation into BGNF 1. Identify every functional dependency. 2. Identify every candidate key 3. If there is a functional dependency that has a determinant that is not a candidate key: A. Move the columns of that functional B. Make the determinant of that functional C. Leave a copy of the determinant as a foreign D. Create a referential integrity constraint between dependency into a new relation. dependency the primary key of the new relation. key in the original relation. the original relation and the new relation. 4. Repeat step 3 until every determinant of every relation is a candidate key. Note: In step 3, if there is more than one such functional dependency, start with the one with the most columns