Question

[Q.4] Answer the following questions

You are invited as a database architect to develop database schema for maintaining patient information for the NYU medical group (make necessary assumptions for the data requirements if needed).

• Each physician in the Lehman medical group is uniquely identified by physicianID o Each physician must have first name, and last name, and phone number

• Each patient is identified by patientID
  o Each patient must have first name, and last name, phone number, and insurance card number (if the patient has)

• Each patient gets a treatment from a physician.

  o Each treatment is identified by a unique treatementCode

  o Each treatment is described by a simple description

• Patients, Physicians, and Treatments are associated by a patientTreatement relationship type. The relationship type has two attributes to record date and time of the patient visit and patient co-payment (or minimum payment if the patient does not have medical insurance)

• (a) Design a conceptual schema using ER diagram for storing and managing the data as explained above.

• (b) Derive the logical database schema from the conceptual schema you designed.

Answer 1

Explanation:

Data modelling is the first step in the process of database design. This step is sometimes considered to be a high-level and abstract design phase, also referred to as conceptual design.

Data Model :

The Data Model is defined as an abstract model that organizes data description, data semantics, and consistency constraints of data. The data model emphasizes on what data is needed and how it should be organized instead of what operations will be performed on data. Data Model is like an architect's building plan, which helps to build conceptual models and set a relationship between data items.

Types of Data Models:

There are mainly three different types of data models: conceptual data models, logical data models, and physical data models, and each one has a specific purpose. The data models are used to represent the data and how it is stored in the database and to set the relationship between data items.

1. Conceptual Data Model: This Data Model defines WHAT the system contains. This model is typically created by Business stakeholders and Data Architects. The purpose is to organize, scope and define business concepts and rules.
2. Logical Data Model: Defines HOW the system should be implemented regardless of the DBMS. This model is typically created by Data Architects and Business Analysts. The purpose is to developed technical map of rules and data structures.
3. Physical Data Model: This Data Model describes HOW the system will be implemented using a specific DBMS system. This model is typically created by DBA and developers. The purpose is actual implementation of the database.

Conceptual data model:

A conceptual data model is a summary-level data model that is most often used on strategic data projects. It typically describes an entire enterprise. Due to its highly abstract nature, it may be referred to as a conceptual model.

A conceptual data model identifies the highest-level relationships between the different entities.

Features of conceptual data model include:

• Includes the important entities and the relationships among them
• No attribute is specified.
• No primary key is specified.

Logical data model:

Logical database design is the process of transforming (or mapping) a conceptual schema of the application domain into a schema for the data model underlying a particular DBMS, such as the relational or object-oriented data model. This mapping can be understood as the result of trying to achieve two distinct sets of goals: (i) representation goal: preserving the ability to capture and distinguish all valid states of the conceptual schema; (ii) data management goals: addressing issues related to the ease and cost of querying the logical schema, as well as costs of storage and constraint maintenance.

Features of a logical data model include:

• Includes all entities and relationships among them.
• All attributes for each entity are specified.
• The primary key for each entity is specified.
• Foreign keys (keys identifying the relationship between different entities) are specified.
• Normalization occurs at this level.

The steps for designing the logical data model are as follows:

1. Specify primary keys for all entities.
2. Find the relationships between different entities.
3. Find all attributes for each entity.
4. Resolve many-to-many relationships.
5. Normalization.

In the light of the above discussions the solution to the database in the given scenario is outlined below:

The Solution :

Q. 4. a. The Conceptual Schema :

PHYSICIAN TREATMENT PATIENT

Q. 4. b. The Logical Schema :

patientTreatment relation

Visit date & time

Payment ( copayment or minimun payment if no medical insurance )

     

     

PHYSICIAN      TREATMENT   PATIENT   

Physician ID (pk) Treatment Code (pk) Patient ID (pk)   

First Name Description First Name

Last Name    Last Name

Phone Number Phone Number

Insurance Card Number

N.B. : In the above diagram (pk) denotes the primary key of the entities.

This concludes the design of both the Conceptual and Logical Schema for the database design for the NYU medical group along with the necessary explanations.

Please do not forget to like the answer if it helps you. Thank you.