_____________________________ Individual Assignment #6 -- 50 pts For the following queries, write them in SQL and save as you have done previously....
I am struggling to understand DDL, and the ER diagram that my professor wants us to work on. I barely have an understanding of SQL, I really just need help.
Name: _____________________________Individual Assignment #6 -- 50 ptsFor the following queries, write them in SQL and save as you have done previously.Query 20 (8 pts)Points receivedUsing DDL, add the table CAMPUS (see below) to the SCHOOL database.Be sure to include the primary key constraint, use good data types andsuitable lengths._____CAMPUS CAMPUSIDAB CAMPUSNAMEBoiseTwin Falls Query 21 (8 pts)Using DDL statements add the attribute Campus_ID to the Faculty table.Include the foreign key constraint._____Query 22 (4 pts)Using one update statement, make all the values for Faculty.Campus_ID ‘A’. _____ Query 23 (4 pts)Change Student.StuName “Burns, Edward” to your name (leaving the rest of theattribute values the same)._____Query 24 (4 pts)Add 15 credits to all Students total credits. Use one query. ______ Query 25 (6 pts)Find the facids of any faculty who teach in the same room. Do NOT include anyinstances of the same faculty teaching different classes in the same room. Your resultmust have three columns: the facids for both faculty and the room on one line.Extra credit (3 pts)If the result does not repeat the same pair of facids on different lines._____ERD (16 pts)_____Using ERwin, draw the ERD for the following situation. Copy your diagram into thisdocument for submission.Mountain View Hospital (MVH) records information regarding each patient stayin their hospital. When admitted to the hospital, patients provide personal information (e.g., name,address, SSN, insurance, etc.). MVH hires doctors to care for patients in their hospital and records typicalpersonal information as required by the government (e.g., name, address, SSN,license, etc.). Medicaid dictates a list of procedures and codes which the hospital must use forbilling purposes. Insurance companies accept the list as the set of all possiblereimbursable procedures. MVH stocks certain medical supplies which are required for patient care such assyringes, bandages, braces, etc. MVH has an inventory of rooms in which patients are housed for their stay. Theserooms have different designations (e.g., in the cardiac wing, only patients withheart problems are housed, in pediatrics only children under the age of 14 arehoused, etc.)The business rules for MVH are: A patient is not assigned any particular doctor. However, a specific doctor mustorder any procedure which is performed for a patient. A patient may have manyprocedures ordered by many doctors, or may not have any procedure ordered byany doctor. A doctor may not have ordered any procedures for any patients. Aprocedure may not have been ordered by a doctor for a patient. MVH needs thedate and time recorded for any procedure ordered by a doctor for a patient. When admitted to MVH each patient is assigned to a specific room. A patient isnot admitted until there is a room for that patient. A room may have housed manypatients over time, or may not have ever housed a patient. Because a patient mayhave been admitted to the hospital many times over the course of their life, he orshe could stay in different rooms. The hospital needs to record the date the patientis admitted to a room. During the course of a patient’s stay at MVH, different supplies may be used forthat patient. A supply item may not have been used, but can be used by only onepatient. Be sure to: Convert all many-to-many relationships and ternary (three-way) relationships toassociative entities. Convert all multivalued attributes to entities. Specify the maximum and minimum cardinality for each relationship. Show the attributes associated with each associative entity (i.e., the many-to-manyentity or the ternary entity) if any. Do not show any attributes other than primary keys and foreign keys for theother entities.
Show more
