In: Computer Science
[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.
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.
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:
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:
The steps for designing the logical data model are as follows:
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.