Shopping Cart
Your shopping cart is empty!
Categories
CIS 336 iLab 2 The Expanded Entity Relationship Diagram
$9.00
 

CHECK OUT

CIS 336 iLab 2 The Expanded Entity Relationship Diagram

This Tutorial was purchased 2 times & rated No rating by student like you.

 

This Tutorial contains following Attachments

  • CIS 336 N iLab 2 The Expanded Entity Relationship Diagram.docx

CIS336 Lab 2: The Expanded Entity Relationship Diagram LAB OVERVIEW Scenario and Summary This lab introduces the next step in creating a data model, the Entity Relationship Diagram (ERD). You will be given a business scenario for a University Medical Center, which is a small community hospital. The business specifications will outline a number of things about the business, some of which will apply directly to the database you are being asked to model. There is a table that lists the entities (tables) that will be needed for the database and related attributes (columns) for each entity. There is also a column that lists specific information about the entity that will be helpful in determining its relationship to other entities within the model. Be sure to include the minimum and maximum occurrences of each relationship (cardinality) and to supply a name to the relationship that will work in both directions. Make sure to use Crow’s Feet notation in your ERD. Narrative/Case Study The University Medical Center is a small, community hospital. A new hospital administrator has recently been hired by the Board of Directors, and directed to right-size patient care and pharmacy services and improve profitability. The hospital operates three clinical facilities: the main hospital, a mid-town clinic, and an Urgent Care location. The hospital also offers selected in-home care services. Many of the patients are repeat or regular patients who receive regular treatment for various conditions, and many utilize the hospital’s pharmacy services for prescription medications. The pharmacy dispenses about 3,000 different prescription medications of various kinds. Every prescription is associated with one patient, and is logged by the dispensing clinic. The new administrator wants to know which drugs are most prescribed, and also which are the most profitable. The following is some general information about the organization and its current processes. • The hospital operates three clinical facilities. • A healthcare worker logs in at a facility at the start of a shift and logs out at the end. • The name, address, Social Security number and other information is recorded for every healthcare worker. • All healthcare workers have one billing rate, which is determined by their job description. • Each medication dispensed by prescription is linked to both the prescription number and the medication ID number, recording the item price and the quantity dispensed. As a convenient and affordable means of providing hospice care, palliative care, and convalescent care to patients that need occasional/intermittent skilled nursing, but do not require hospitalization, the hospital offers limited in-home care. Recording of in-home care includes the healthcare worker ID, their departure time and return time, and also the prescription ID for any prescriptions administered by the healthcare worker in the home setting. The hospital administrator would like to know what home-care visits have been made to whom, by whom, when, and how long they took. There is concern at this point that the cost of providing limited home healthcare is not providing adequate return on investment, and the program should be revised or discontinued. Requirements You have been asked to develop a logical data model for University Medical Center based on the information given to you by the new hospital administrator and their staff. Through analysis of the nouns and verbs in the case study above, you have accumulated the following entity, attribute, and relationship information shown in the table below. The attribute list may not be complete. If you determine that additional attributes are needed to better define an entity, then you should add them. Entities Attributes and Relationships for University Medical Center (Parallel Lab Exercise): Entity Attributes Relationships HealthcareWorkerHealthcareWorkerID, LastName, FirstName, SSN, Address, City, State, Zip, Phone Number, HealthcareWorkerTypeID A healthcare worker can belong to any one of the three job categories, but can belong to one and only one of the three. healthcare worker has names and other contact information. HealthcareWorkerTypeHealthcareWorkerTypeID, HealthcareWorkerTypeDesc, HourlyBillingRate A healthcare worker can be either a physician (diagnoses, prescribes medication), nurse (provides physician-ordered treatments, administers medications), or a pharmacist (dispenses, delivers medication). ClinicLogClinicLogID, ClinicID, Login, Logout, HealthcareWorkerID Patients may be treated by a healthcare worker at a clinic and can be tracked by the clinic log number. A healthcare worker must sign into the clinic before he or she can serve patients, and must sign out when finished treating patients at that clinic. A healthcare worker may serve portions of a shift at more than one clinic. Clinic ClinicID, ClinicLocationDesc, AMAAccredNum The hospital operates three clinical facilities: General Hospital, Midtown Clinic, and Urgent Care. InHomeCareInHomeCareID, HealthcareWorkerID, PrescriptionID, DepartTime, ReturnTime. Relates to both the healthcare worker and the prescription entities. This entity will help track provision of home healthcare. A healthcare worker can provide many home visits but a prescription is administered on a home visit by one and only one healthcare worker. Method MethodID, Method Description Relates to medication and identifies the method of administration, for example, oral, injection, and so on. Medication MedicationID, MedicationName, Dosage, Cost, QuantityOnHand, LastPurchasedDate, ReorderMinimum Identifies the medication the hospital pharmacy dispenses. One or more medications can be dispensed per prescription. The quantity on hand allows Pharmacists to determine the inventory levels. The reorder minimum can be used to determine when the inventory level has reached a reorder point. Prescription PrescriptionID, MedicationID, BillingAmount, TransactionDateTime, ClinicLogID, PatientID A prescription is identified by a single prescription order. Prescriptions are • made to one or more patients but only one patient at a time; • made by one or more pharmacists but only one pharmacist per prescription; • recorded on one or more clinics but only one clinic per prescription; and • administered by one or more nurses but no one prescription can be administered by more than one nurse. PrescribedMedicationPrescriptionID, MedicationID, ItemPrice, QuantityDispensed Prescribed medication is part of a prescription and records medication dispensed per prescription. Prescribed medication must be able to associate multiple medications sold on a single prescription number. Patient Patient Number, First Name, Last Name, Address, City, State, Zip, Phone Number A patient can be associated with multiple prescriptions, but any one prescription is to one and only one patient. A prescription can occur without a patient registering in the system (e.g., an unconscious patent arrives by ambulance in the emergency room and receives life-saving emergency treatment). Using an appropriate drawing/data modelling tool, develop an ERD that meets the following guidelines. • Draw the entities with their attributes. • Indicate the relationships between the entities using Crow’s Foot notation. You will need to determine the cardinality and optionality for each direction of the relationships. Some of the Foreign Key relationships are identified in the graph above but not all. Be sure you identify and account for all Foreign Key relationships. • Add a name (in both directions) to the relationships. Remember, if you can verbalize the relationship in both directions, then you probably have a valid relationship. Deliverables The deliverable for this lab will be your completed ERD as a single MS Word document using copy/paste or imported as an image from your drawing/modelling application, cropped and sized appropriately (it should fit on a single page), and named lab2_solutions_yourname. LAB STEPS STEP 1: Drawing Entities and Attributes Back to Top Be sure to include all of the entities that have been defined. You need to include at least the primary and foreign key attributes where applicable in your diagram. STEP 2: Add Relationships Back to Top Be sure that you link all entities based on PK to FK relationships. There may be a case where you need to identify a combination PK and if so make sure that all of the relationships involved are defined. Be sure that you have set your drawing/modelling tool set to show Crow’s Foot notation. Also, be sure that you are defining the correct cardinality for the relationships. STEP 3: Naming of relationships Back to Top If your drawing/modelling tool creates a default name for relationships, do not simply accept this default without due consideration. There may be some relationships where the default is applicable, but in most cases you will want to explicitly name the relationship. Be sure that you have provided a verb phrase for both directions in the relationship. This is the end of lab #2

Write a review

Order Id


Order Id will be kept Confidential
Your Name:


Your Review:
Rating:   A   B   C   D   F  

Enter the code in the box below:



Related Tutorials
$9.00

This Tutorial was purchased 4 times & rated B+ by student like you.

(TCO 6) Which of the following keywords is used to remove a row in a table? (TCO 6) When using the ALTER TABLE...DROP COLUMN command, which of the following is NOT correct? (TCO 6) Which of the following commands will add a new column named FIRSTORDERDATE to the CUSTOMERS table to store t..
$9.00

This Tutorial was purchased 3 times & rated No rating by student like you.

(TCO 8) If a Cartesian join is used to link table A which contains five rows to table B which contains eight rows, there will be _______ rows in the results. (TCO 8) Which of the following statements will produce the value 21.1? (TCO 8) Which of the following format elements will display ..
$8.99

This Tutorial was purchased 3 times & rated B+ by student like you.

(TCO 7) Which of the following types of columns will automatically right-align the data when it is displayed? (TCO 7) Which of the following SQL statements will display the profit generated by each book currently stored in the BOOKS table? (You do not need to see the table structure for this ..
$9.00

This Tutorial was purchased 2 times & rated No rating by student like you.

(TCO 2) Explain what a composite attribute is (TCO 1) Data constitutes the building blocks of (TCO 1) Successful database design is first and foremost based on _____________ requirements. (TCO 1) The lowest level in a database hierarchy is the ____________ or value as it is often refe..
$7.49

This Tutorial was purchased 3 times & rated B+ by student like you.

(TCO 3) Normalization works through a series of stages called normal forms. Typically _________ stages must be completed before a table can be considered normalized. (TCO 3) The conflicts between design efficiency, information requirements, and processing speed are often resolved through ..
$32.00

This Tutorial was purchased 13 times & rated A+ by student like you.

CIS 336 Final Exam 4 Sets of Answers ..
$11.00

This Tutorial was purchased 9 times & rated A+ by student like you.

1. (TCO 7) For every order, list the order number, order date, part number, part description, and item class for each part that makes up the order. 2. (TCO 8) Write a query that will display the customer number, last name, and first name for every customer I whose first name begins with the l..
$11.00

This Tutorial was purchased 6 times & rated B+ by student like you.

1. (TCO 2) Explain what physical independence means. (Points : 15) 2. (TCO 8) Sally wants to query the EMP table and determine how many of the employees live in each of the cities the company has factories in. She writes the following query but it does not work. What is wrong with the way the..
$11.00

This Tutorial was purchased 5 times & rated A+ by student like you.

1. (TCO 1) Most DBMS are referred to as _____________database management systems. (Points : 4) elemental linked hierarchical relational 2. (TCO 1) A relational DBMS provides protection of the _______________________ through security, control, and recovery facilities. (Points : 4) ..
$11.00

This Tutorial was purchased 4 times & rated B+ by student like you.

1. (TCO 1) Most DBMS are referred to as _____________database management systems. (Points : 4) elemental linked hierarchical relational 2. (TCO 1) Data constitutes the building blocks of _____________. (Points : 4) information processing applications programming 3. (TC..
$9.00

This Tutorial was purchased 2 times & rated A+ by student like you.

CIS336 Lab 1: Normal Forms and Entity Relationship Diagrams Introduction In this exercise, you will analyze a de-normalized data set presented in the form of a spreadsheet. You will next construct a series of dependency diagrams, transforming the evolving data model from First Normal Form (1NF), t..
$10.00

This Tutorial was purchased 3 times & rated A+ by student like you.

CIS-336: Lab 3: Building the Physical LAB OVERVIEW Scenario and Summary The lab for this week addresses taking a logical database design (data model) and transforming it into a physical model (tables, constraints, and relationships). As part of the lab, you will need to download the zip file ti..
$9.00

This Tutorial was purchased 0 times & rated No rating by student like you.

CIS336: Lab 4: Introduction to Select, Insert, Update, and Delete Statements LAB OVERVIEW Lab 4 will introduce the various aspects of the SQL select statement and the methods of retrieving data from the database tables. This lab will also introduce the fundamentals of updating and deleting records..
$9.00

This Tutorial was purchased 2 times & rated A by student like you.

CIS336: Lab5: Joining Tables LAB OVERVIEW Scenario and Summary Lab 5 will introduce the concept of multi-table JOINS in order to work with data in two or more related tables simultaneously. This lab may be completed using either DeVry’sOmnymbus EDUPE-APP lab environment, or a local copy of the My..
$9.00

This Tutorial was purchased 3 times & rated B+ by student like you.

CIS336: Lab 6: Group Functions and Subqueries LAB OVERVIEW Scenario and Summary Lab 6 will introduce the concept of group functions and subqueries to meet more complex report requirements. This lab may be completed using either DeVry’sOmnymbus EDUPE-APP lab environment, or a local copy of the MyS..
$9.00

This Tutorial was purchased 1 times & rated No rating by student like you.

CIS-336: Lab7: Working with Views LAB OVERVIEW Lab 7 will introduce the concept of database views. This lab may be completed using either DeVry’sOmnymbus EDUPE-APP lab environment, or a local copy of the MySQL database running on your own computer using the OM database tables. The lab will utilize..
$25.00

This Tutorial was purchased 4 times & rated A+ by student like you.

This project is designed to touch all aspects of the fundamental concepts of database design and logical data modeling covered during the class. Each student will be responsible for designing, developing, and demonstrating the functionality of a database created based on a set of business specificat..
$7.00

This Tutorial was purchased 2 times & rated A+ by student like you.

CIS 336 Week 6 Quiz Question 1. (TCO 6) Which of the following keywords can be used to change the size, datatype, or default value of an existing column? Question 2. (TCO 9) If the _____ clause of the UPDATE command is omitted, then all the rows in the specified table will be changed. ..
$7.00

This Tutorial was purchased 1 times & rated No rating by student like you.

CIS 336 Week 5 Quiz Question 1. (TCO 8) The ____ set operator in SQL combines rows from two queries and returns only the values that are common between the two sets. Question 2. (TCO 8) Which SQL function converts a value to a particular string format? Question 3. (TCO 8) Based upo..
$7.00

This Tutorial was purchased 0 times & rated No rating by student like you.

CIS 336 Week 4 Quiz Question 1. (TCO 7) Which of the following types of columns will automatically right-align the data when it is displayed? Question 2. (TCO 7) Which of the following symbols can be used to denote that any and all characters following the sign are eligible when use..
$7.00

This Tutorial was purchased 3 times & rated A+ by student like you.

CIS 336 Week 3 Quiz Question 1. (TCO 3) When designing a database, you should _____ Question 2. (TCO 3) A table that is in 1NF and includes no partial dependencies only is said to be in _____ Question 3. (TCO 3) (TCO 3) A table is not in 1NF if _______________. Question 4. (TCO 9) ..
$7.00

This Tutorial was purchased 5 times & rated B+ by student like you.

CIS 336 Week 2 Quiz Question 1. (TCO 1) Raw facts, such as a telephone number, a birth date, and a customer name, are also referred to as _____ Question 2. (TCO 1) The DBMS allows you to extrapolate information from your data by using a _____ Question 3. (TCO 1) A database row..
$15.00

This Tutorial was purchased 4 times & rated A by student like you.

CIS 336 Final Exam Question 1. 1. (TCO 1) A DBMS performs several important functions that guarantee the integrity and consistency of the data in the database. Which of the following is NOT one of those functions? Question 2. 2. (TCO 1) A relational DBMS provides protection of the _____ t..