Draw an appropriate ER diagram

Assignment Help Database Management System
Reference no: EM131433975

Assignment -

Objectives -

  • To develop a conceptual data model diagram
  • To perform logical design

Assignment Specification -

In this assignment, you need to perform the following tasks for the given case study:

  • Draw an Entity Relationship (ER) diagram
  • Perform logical design
  • Create3NF Relations

Case Study

Many medical specialists offer their medical consultations and perform special medical procedures to the patients at CQ Specialist Medical Center (hereafter called as CQMC).  Patients generally book their appointments through a telephone call or by personal visits to the center.  Sometimes, the General Medical Practitioners (GPs) contact CQMC directly on behalf of their patients for the specialist appointments.

The patient details, such as name, gender, etc., are collected and stored at CQMC. Since the name of the patient is not going to be unique, each patient is identified by a unique patient ID. The details of patient's medical history (past surgeries undertaken), details of current medications (medicines and dosages) and other medication-allergies, if any, have to be maintained by CQMC. The referral letter from a GP is not a requirement at the time of booking for an appointment with the specialist but it is compulsory at the time of actual patient's consultation.  Just prior to the consultation, CQMC collects the referral letter from the patient and notes down the details of date of referral and the GP (identified by unique Medical Provider Number) who issued that referral letter.  Based on the necessity, the same GP may refer the same patient to the same specialist many times over a period of time.  During the consultation, a specialist may conduct various medical tests and procedures on the patient; may prescribe multiple prescriptions to the patient.  After each appointment, the name of the standard tests and procedures performed along with the medical prescription details are stored and maintained by CQMC.

The following table provides the details of consultation and other charges applicable for the specialist's appointments. The charges can be assumed to be constant values.

Type

Fees/Charges

Consultation

$120

Biopsy

$75

Excision

$175

Laser

$250

After each appointment, the patient pays the entire appointment charges to CQMC.  Each appointment charges include the consultation fees and additionally may include multiple other charges from the above table.  Sometimes, some specialists offer discount amount to some appointments on special grounds such as Pensioner, Veteran etc.,

Some GPs and patients often would like to know the details of qualifications and number of years of experience of the specialists.  Hence, CQMC maintains and updates those details periodically. You may note that a medical specialist may have multiple qualifications.

The attributes of the possible entities have not been mentioned clearly.  Hence, you need to assume at least three and at most seven relevant attributes for each of the entities that you will identify for your ER diagram.

Assignment Requirements -

In this assignment, you need to create an Entity Relationship (ER) diagram relevant to the above case study and perform logical design to produce appropriate3NF Relations.

The attributes for various entities have not been purposely described in the case study so that the students are encouraged to further research and list the pertinent attributes in addition to the required identifier for each and every entity in their ERD.

1. Draw an appropriate ER diagram

Use the symbols as prescribed in your unit-textbook to draw the ER diagram (ERD) for the above case study. 

Your ERD must

  • show all necessary entities, attributes and relationships
  • show identifiers/identifying attributes as necessary
  • show multi-valued attributes, if any
  • show participation and cardinality
  • show associative entities, if appropriate
  • show weak entities, if any
  • use the notation described in the set text
  • use consistent and appropriate naming for entities and attributes as specified in the unit text book

Some business rules or other aspects of the case study may not be clear to you when you read the case study. If this is the case then you should either approach your lecturer or tutor for clarification, or you may simply make assumptions and then develop your ERD accordingly.  For Example, the case study might not mention all relevant participation information (also called minimum cardinality). If so, you make an assumption about what the minimum cardinalities might reasonably be, and then show them in your ERD accordingly. You should justify each assumptions in terms of business, for Example, It is assumed that each customer must have at least one order because it is assume that the business does not record customer details until the customer places an order.

To get yourself started, ask yourself."If I were running this business, what things I need to keep a list of?" write those things down. For each thing. What information would you need to record about it?" how can it be identified? The answers to these questions will help you to develop your ERD.

2. Assumptions and Business rules

Provide assumptions and business rules relevant to your ERD.

3. Create Logical Design:

Map your Entity Relationship diagram (ERD) into relations and make sure that all the relations are in 3NF.  Provide all the relations in the following format:

Customer (CustomerId, customerName, Street, Suburb, State, PostCode, Email)

Invoice (InvoiceNumber, invoiceDate, Total, CustomerId)

Foreignkey (CustomerId) references Customer (CustomerId)

Textbook - MODERN DATABASE MANAGEMENT, Eleventh Edition by Jeffrey A. Hoffer, V. Ramesh and Heikki Topi.

Reference no: EM131433975

Questions Cloud

Effect of the government cutting taxes by amount : Assume in economy is in recession with a MPC of 0.75 and there is a GDP gap of $100 billion. How much must government spending increase to eliminate the gap? Instead of increasing government spending by the amount that you calculated what would be..
Discuss fully the caplans claim : Conditions of Performance. The Caplans contract with Faithful Construction, Inc., to build a house for them for $360,000. The specifications state “all plumbing bowls and fixtures … to be Crane brand.” The Caplans leave on vacation, and during their ..
Compute the own and cross price elasticity of demand : Estimate the model. Is SAS able to estimate the model correctly? Go back to the data and investigate why Proc MDC is unable to estimate the coefficient for feature. Report what you find - compute the own and cross price elasticity of demand for eac..
Military and the corporate world : How do you think the agency problem differs between the military and the corporate world? In the corporate world, the shareholders are the owners and they hire managers to run the firm.
Draw an appropriate ER diagram : Draw an appropriate ER diagram. Use the symbols as prescribed in your unit-textbook to draw the ER diagram (ERD) for the above case study. Your ERD must show all necessary entities, attributes and relationships
Should hospitals be required to post scorecard : Should hospitals be required to post a “Scorecard” indicating the number of days since a reportable error or other outcome statistics? Would it improve outcomes by transparency? Is there value to comparing outcomes for a rural critical access hospita..
Compute the cost of the basket for the first year : Suppose the price of hot dogs in years 1, 2, and 3 is $1, $2, $3. The price of veggie burgers is $2, $3, $4 for those years. The basket of goods is 4 hot dogs and 2 veggie burgers. Compute the cost of the basket for the first year.
Progress and benefit from globalization : What kind of reforms does globalization need in order for the entire world to progress and benefit from globalization and not only the world's leading nations or the ‘elites' of every country?
What are your thoughts on repealing the affordable care act : What are your thoughts on repealing the Affordable Care Act? What does this mean for those that will be impacted?Are there any potential feasible solutions as oppose to repealing the Affordable Care Act, to ensure that the right to health care does n..

Reviews

len1433975

3/20/2017 6:26:03 AM

Entities have been identified, named and drawn correctly including super type and sub type entities; associative entities; weak entities (if any). Provided appropriate attributes for all the entities. Provided identifiers for the required entities. Drawn correct relationships between entities and named them appropriately. Used proper symbols and shown min and max cardinalities for the relationships. Appropriate symbols have been used for super type and sub type relationships (if any). Provided appropriate justification for assumptions and business rules. Created correct 3NF Relations. Primary keys and foreign keys have been shown in relations correctly including foreign key references.

len1433975

3/20/2017 6:25:25 AM

You must state in your assignment(at the end under the heading Acknowledges is a good place)all materials and sources you used. This may be as simple as 'This assignment was completed using only the COIT20247unit materials' to mention that you have used only the items provided by the unit COIT20247. Before submitting your assignment, review the marking criteria that are available in the assignment specification. Ensure that all the criteria readdressed in your submission. After submitting your assignment, please verify that your submission was successful, i.e. download your submitted files to ensure that they are correct. Penalty related to late submission and plagiarism is handled as per the university policies.

len1433975

3/20/2017 6:25:19 AM

You should submit a Microsoft Word document containing: A copy of your ERD. A list of business rules and assumptions including their justifications. 3NF Relations. Your document should contain appropriate identifying information (your student number, name, unit number, etc.) and should contain appropriate headings for each section. You must work individually on this assignment. The model solution on this assignment will be released approximately TWO weeks after the due date. Assignments that are submitted after the release of the model solution will receive zero marks.

len1433975

3/20/2017 6:23:49 AM

Australian student, need it as per the guidelines. In my assignment file it is there IMPORTANT POINTS, all the materials should be used according to the course COIT20247, It’s a PowerPoint presentations week 1 to week 5 and it is clearly stated that USE APPROPRIATE NAMING CONVENTIONS AND ATTRIBUTES AS SPECIFIED IN THE UNIT TEXTBOOK, I don’t have text book, but I am attaching the ppt PLEASE REFER THIS TEXTBOOK FOR ER MODEL NAMING CONVENTIONS,,AS ITS ALREADY MENTION IN THE ASSIGNMENT FILE. Please make sure solution is good, as it is 30 percentage weight age. I don’t want any issues in my assignment. Sorry for keep repeating this again and again..please gone through the assignment file thoroughly where ever it is showing refer textbook please use it......if you want any resources I will provide you...but please i need good work....because i have 2 assignment which is 45% weight age and it is completly based on first assignment..and i have to give that assignment to you guys.

Write a Review

 

Database Management System Questions & Answers

  What data from the scenario is needed for the database

Case Needs Analysis: Determine the data requirements of Birchwood Lane Schools. What data from the scenario is needed for the database and why

  Write a report about how your nationometer works

Write a report about how your Nationometer works and provide a URL link to a working version and understand the different types of dataset you may need to use and how you can gain access to them.

  Draw erd with key attributes and data dictionary

Draw ERD with key attributes. You can use any notation to draw the ERD. Must include legend - Data dictionary for the above scenario

  Briefly how to make use of indexes such as b+ tree

Summarize briefly how to make use of indexes such as B+ tree or a hash indexes in selection, projection, and join operations?

  Design and implement a simple database application

UMBC - IS 676: Information Integration - Your inventory database contains information about books, and music CDs. The items are stored in different warehouses in the country. Each warehouse stocks different quantities of the products that you sell.

  Different types of information systems software

Demonstrate problem-solving skills by identifying and resolving issues relating to information systems and their components, and proficiently utilise different types of information systems software

  Stores a logically related database over two or more

A distributed database management system (DDBMS) stores a logically related database over two or more physically distinct locations connected by a computer network.

  Identify the potential sale and department store transaction

Evaluate whether the use of a distributed DBMS structure is appropriate and identify the optimization techniques that should be factored in to enhance the operations of the database in your design.

  Design an expanded entity relationship model diagram

You are to design an Expanded Entity Relationship Model Diagram. An example of the Expanded Entity Relationship Model Diagram is seen in Figure 3.27 on page 89.

  Process the weather data with data mining techniques

Process the weather data for Auckland and Invercargill in the given dataset (monthly readings) and-focusing in particular on temperature related data-experiment with various data mining techniques

  Estimated costs and expected activity

Abel Company uses activity-based costing. The company has two products - estimated costs and expected activity

  Relational algebragiven the following set of relations from

relational algebragiven the following set of relations from the east coast aquarium database list the sequence of

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