Create a database design - relational data model

Assignment Help Database Management System
Reference no: EM1368376

Database Management System: 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: 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 RDM MUST identify 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. RDM format: Table Name (attribute_1, attribute_2.attribute_n) Primary Key (attribute_a,.attribute_n) Foreign Key attribute_x References Table Name (attribute_x) Alternate Key attribute_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 fo r 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 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 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. 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.

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.

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.estion.

Reference no: EM1368376

Questions Cloud

Training & development : Write a paper on Human Resources: Training & Development (one copy) - total 10 Pages, Describe how training contributes to the success of the organization.
Explain the term structure is flat and the rate of interest : Explain The term structure is flat and the rate of interest with continuous compounding is 5% per annum and The conversion factor for the bond is 1.2191 and The current quoted bond price is $137.
Write algorithm for graph minimum number of semesters : You are given a DAG called G which is the prerequisite graph for a set of courses required for a degree. Each vertex corresponds to course. Provide a high-level description of algorithm which labels each vertex in G with minimum number of semesters..
Calculate the probability of success or failure : Mark Martinko has been a class A racquetball payer for the last 5-years, and one of his biggest goals is to own and operate a racquetball facility.
Create a database design - relational data model : Create a database design specification Enhanced Entity Relationship Diagram (EERD) and Relational Data Model from the given business description - Database Management System
Calculate the point price elasticity of demand : In effort to decrease excess end of the model year inventory, Oaktown Ford offered a 1 percent discount off the average price of 4WD Escape gas-electric hybrid SUV's sold during the month of August.
Explain the basic steps of selling models : Explain the basic steps of selling models and Discuss the process and economic importance of a customer relationship management (CRM) program.
Calculate the two part pricing : A local surf store estimates that their average consumers demand a year is P=3.5-0.5Q, and knows that the marginal cost of each rental is $0.5.
Design main driver class to instantiate-compute sum-objects : Design a main driver class to instantiate and calculate sum of several MonetaryCoin objects. Demonstrate that monetary coin inherits its parent's ability to be flipped.

Reviews

Write a Review

 

Database Management System Questions & Answers

  Find names of students who have higher gpa from table

List the students ID, name, GPA, and course Number such that all students have GPA greater than 3 . 5 and enrolled in a course in Jan 1, 2011. Find the names of all students who have GPA greater than 3.

  Access values through queries to databases

However, the only way you can access values is through queries to the databases. In single query, you can specify value k to one of two databases.

  Database triggers are utilized to record logins by users

Several times, database triggers are utilized to record logins by users. Here is the example of login trigger that inserts row into table every time a user connects.

  Essay on data mining in warehouse architectures

Course: data mining. Require a 7 page essay on subjects: Warehouse Architectures: the paper requires to contain information about centralized, federated, and tiered data warehouse.

  Design a set of 3nf tables for database scenario

Draw an ER diagram for your database scenario. Design a set of 3NF tables for your database scenario.

  Determine functional dependencies of table

Using your knowledge of Premiere Products, determine the functional dependencies that exist in the following table.

  Design tables in 3nf various codes for at least three fields

Create tables in 3NF. As you create the database, include different codes for at least three of the fields. Use sample data to populate fields for at least three records in each table.

  What do you mean by data base scheme

Database Questions:  What do you mean by data base scheme?  What do you mean by cardinality ratio?   What do you mean by degree of relation?

  Draw inheritance hierarchy to represent shoe object

Draw an inheritance hierarchy to represent a shoe object. The base class should have derived classes of Dress Shoes, Tennis Shoes and Boots.

  Implement that entity as a database table with two subtypes

Implement that entity as a database table with two subtypes, Games and Movie.

  Write select statement that returns two columns

Write a SELECT statement that returns two columns: VendorName and PaymentSum, where PaymentSum is the sum of the PaymentTotal column.

  Describing the select statement

Data processing needs taking or receiving the data from a source and doing something with it. The same can be said about the transaction processing. When working along with a file, whether it be a fixed length.

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