List and explain the defined business rules

Assignment Help Database Management System
Reference no: EM13317819

List and explain the defined business rules that can be determined from the business description.

The Enhanced Entity Relationship Diagram (EERD) produced with a drawing or case tool. Your EERD must use a 'standard' notation style such as Crows Foot or Chen. Please note that the relationship diagram produced using MS Access, or MS SQL Server is NOT an acceptable ERD notation. Your EERD must show:

All entities,

Primary and Foreign keys, as appropriate,

All attributes for each entity,

Relationships between entities, including their cardinality and optionality,

The EERD must not contain any M:N relationships. These must be decomposed.

The Relational Data Model (RDM) which corresponds to the ERD in the format shown below. The RDMMUSTidentify all primary keys, alternate keys and foreign key constraints. Raw SQL Create Table script dumps from a modeling tool, or SQL Server WILL NOT be accepted.

The RDM must be in 3rd Normal Form.

The assumptions that you have made to develop the system.

Understand and interpret a set of business rules for a database;

Create an Enhanced Entity Relationship Diagram that models the requirements accurately;

Create a Relational Data Model that accurately models the E ERD and requirements;

Normalise the model to 3rd Normal Form;

Task

Create a database design specification (Enhanced Entity Relationship Diagram (EERD) and Relational Data Model (RDM)) from the given business description. The RDM must be in 3rd Normal Form.

The following defines the minimum information that should be reported:

RDM format:
Table Name (attribute_1, attribute_2...attribute_n)
Primary Key(attribute_a,...attribute_n)
Foreign Keyattribute_xReferencesTable Name (attribute_x)
Alternate Keyattribute_c|none

Business description

Case summary

ELK County Maine has been awarded a federal grant to build a modest paediatric medical centre (PMC). The grant also paid for the medical education of a paediatrician that will practice at the medical centre for at least five years. The grant included purchasing a medical information system to assist the doctor and the county in managing the medical centre. The county's Director of Information Systems assigned a business analyst to explore and report on the hardware and software requirements of the new medical system. After the business analyst completed her report, she conducted an extensive investigation to see if an off-the-self software package would meet the functional requirements of the medical centre's stakeholders. It was determined that no such software package was available. Due to a near term software development backlog in the county's Information Systems Department, the county decided move things along by hiring a consultant to design the relational database for the new medical information system.

In a stroke of good fortune, you have been appointed as the design consultant.

Functional requirements

Below are the specifications contained the Data and Information Requirements section of the PMC Functional Requirements Report. Your database design (ERD and RDM) must capture these requirements.

Family Information

The PMC medical system needs to track information on the parents whose children are registered with the PMC and eligible to receive health care services. The new application must be able to determine the financially responsible head of the household's name, address, telephone number, and the name of their insurance carrier (if any). See Table 1 for sample data. The doctor would like to assign each family a unique alphanumeric identifier so that they may identify a specific family that is registered with the Paediatric Medical Centre. The identifier will consist of the first 4 letters of the family's last name with a unique two digit number added at the end. For example, if there were three families registered with the last name Smith, the 3 family numbers would be SMIT01, SMIT02, and SMIT03.

Patients

The new system must contain static information on all the patients that are registered with the PMC. The treating doctor needs to know the patient's name (last name may be different from the parent responsible for the child). Each patient is assigned a unique medical record number (MRN). A simple algorithm is used to create this number, which consists of three parts; 1) the first three characters of the patient's last name, 2) the first character of the patient's first name, and 3) two digits that are assigned sequentially so that the first two components when combined with the third insure the MRN is unique. For example, the boxer George Forman has five children named George. Their MRNs would be assigned as follows: FORG20, FORG21, FORG22, FORG23, and FORG24. This assumes that MRN FORG19 already existed in the database. A sample set of patient data is presented in Table 2. If the parents have any type of medical insurance, including Medical Assistance, then the parent's social security number must be tracked. The insurance companies require that this field be 12 characters long. The first 9 characters are the parent's specific social security number with no dashes. A unique 2 digit number is then assigned to each dependent child and appended to the end of the parent's social security number, i.e., 999999999-99, to form a unique identifier that can be used by the insurance companies to identity each child.

Services Performed

The billing application must contain information on all of the medical services available from the PMC. This information consists of an industry standard medical services code, a description of the service performed, the standard fee charged by PMC for this specific service (this is the fee charged to parents with no insurance), the fee that will be accepted for this service by Maine's Medical Assistance program (MA), and the fee that will be accepted by all of the other insurance carriers. See Table 3 for a sample fee schedule of the medical services provided by PMC.

Diagnosis Codes - DRG Codes

The new application needs to support the use of industry standard DRG (Diagnostic Related Group) codes. These are predefined unique codes where each code corresponds to a specific medical diagnosis that a physician may make. These codes and their corresponding descriptions may be purchased on a CD-ROM, where they are stored in tab delimited text file. Some of the most commonly used DRG codes by PMC are listed in Table 4.

Insurance Carriers

The new application requires the capability to track insurance carriers that have contracts with PMC including Medical Assistance provided by Maine's Department of Public Welfare. Elk County has contracted with the state for PMC to accept Medical Assistance patients. See Table 5 for a listing of all current contracted insurance companies. Each insurance company has been assigned a two character insurance code. Parents that do not have medical insurance with a firm that has a contract with PMC are considered to be self-insured.

Patient History

 The new system must retain a complete patient history. This includes services performed and fees charged for those services. The doctor wants the capability to over-ride the standard fee schedule. Also, some fees are subject to frequent change, especially those associated with injections and lab work. Consequently, the doctor needs to know the fee charged at the time the service was provided. In addition to tracking historical services, the system must maintain a complete history of the diagnoses made by the doctor on each patient visit. If a doctor sees a patient multiple times in a single day it will be recorded in the system as a single visit. The paediatrician may perform multiple services and make multiple diagnoses on individual patient in a single day. Also a doctor may perform a service without
making a diagnosis, for example: allergy injections given on a bi weekly basis or a scheduled immunization.

The data tables will be placed by the subject coordinator in Interact under Resources.

(This case is adapted from Ballenger, R.M. (2003). A database design and development case: Elk County Pediatric Medical Center. Journal of Information Systems Education, 14(3)).

Reference no: EM13317819

Questions Cloud

Pricing and price competition account for the number one : Pricing and price competition account for the number one problem facing many marketing executives. What are some of the frequent problems that companies encounter?
Calculate the mass of a solid gold rectanglar bar : Calculate the mass of a solid gold rectanglar bar that has dimensions lwh = 4.20 cm x 12.0 cm x 30.0 cm. (the density of gold is 19.3x10^3 kg/m^3.)
Determine the density on the highway and flow on the road : Traffic data collected at the same time indicated an average time headway of 4 sec. Determine the (a) density on the highway (b) flow on the road (c) space mean speed.
Determine the long-run number of firms in the industry : Graph the firm’s long-run average cost and show that it reaches a minimum where q =1. Determine the long-run equilibrium price (p*) and the firm’s long-run equilibrium output (q*).
List and explain the defined business rules : The Relational Data Model (RDM) which corresponds to the ERD in the format shown below. The RDMMUSTidentify all primary keys, alternate keys and foreign key constraints. Raw SQL Create Table script dumps from a modeling tool, or SQL Server WILL NO..
Calculate the pressure on the top lid of a chest buried : Calculate the pressure on the top lid of a chest buried under 3.65 meters of mud with density 1.75 103 kg/m3 at the bottom of a 11.5-m-deep lake
Find the fraction of the nitrogen that is in the ammonia : a. Find the fraction of the nitrogen that is in the ammonia (NH3) form (and hence strippable) as a function of pH. Include a table AND a graph. b. If the wastewater pH is raised to 10 and the atmospheric ammonia partial pressure
What will be the rate of return earned by most firms in the : In long-run equilibrium, most firms (the ones that remain from the original 298) will earn the normal % rate of return.
How far are the goggles from the edge of the pool : It's nighttime, and you've dropped your goggles into a swimming pool that is 3.0m deep. If you hold a laser pointer 1.2m above the edge of the pool, How far are the goggles from the edge of the pool

Reviews

Write a Review

 

Database Management System Questions & Answers

  What kind of information will be included in the database

To design a database, a database planner should spend a considerable amount of time thinking about what kind of information will be included in the database and how it will be organized. There are several approaches to creating a successful datab

  Edinburgh airport case study

Discuss why a relational database would be suitable for the organisation in the case study and provide three reasons for this recommendation and discuss using three advantages that a database management system provides to an organisation.

  Construct an entity-relationship model for the database

Construct an entity-relationship (ER) model for the database. Make sure you include in your model details of entities, relationships, attributes, keys and limits in participation.

  Determine the cartesian product of sets

Determine the Cartesian product (A X B) of the given pair of sets. A => Set of first letter of names of countries = A = { R, S, T } B => Set of numbers denoting Postal codes = B = { 0001, 1001}.

  Provide an entity relationship model

Provide an Entity Relationship Model (ERM) that will describe the data structure that will store all data elements.Note:The graphically depicted solution is not included in the required page length.

  Method to change column name

Hhow can you change column name or what is a different way to write statement in SQL so it displays the amount column as money.

  Create a data warehouse conceptual design

Create a Data Warehouse conceptual design using Star Schema Modelling and document all steps during the design process

  Describe implement relationship between employees-projects

Describe how you would implement relationship between employees and projects in each of the given scenarios: Many employees can work on given project, but each employee can work on only single project.

  Explaining concept of organizational intelligence

Need a page and half on concept of organizational intelligence. It must specifically talk about organizational intellignece being outcome of organization's efforts to gather store, process and interpret data.

  Write the functional dependencies in the table

Write the functional dependencies in the table which concerns invoicing (an application Premiere Products is considering adding to its database), subject to the specified conditions.

  Describing the purpose of database an its functionality

Describing the purpose of database an its functionality, plus a detailed E-R diagram.

  Create the primary and foreign key using a uml class diagram

Create the primary key and foreign keys using a UML Class diagram for each table and compare and contrast the key aspects that each system offers

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd