Question

In: Computer Science

[Q.4] Answer the following questions You are invited as a database architect to develop database schema...

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

Solutions

Expert Solution

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.


Related Solutions

For the given database schema. Answer the following questions. Company Database customer(cust_id, name, address) product(product_id, product_name,...
For the given database schema. Answer the following questions. Company Database customer(cust_id, name, address) product(product_id, product_name, price, quantity) transaction(trans_id, cust_id, time_date) product_trans(product_id, trans_id) Identify the primary keys and foreign keys for the relations and specify at least two different types of integrity constraints that would be applicable for different relations given.
The schema for the Academics database is as follows. Understanding this schema is necessary to answer...
The schema for the Academics database is as follows. Understanding this schema is necessary to answer the questions in Part B. DEPARTMENT(deptnum, descrip, instname, deptname, state, postcode) ACADEMIC(acnum, deptnum*, famname, givename, initials, title) PAPER(panum, title) AUTHOR(panum*, acnum*) FIELD(fieldnum, id, title) INTEREST(fieldnum*, acnum*, descrip) The semantics of most attributes are self-explanatory. For each relation, the primary key is underlined and any foreign keys are denoted by an asterisk (*). Some additional information for relations is given below: DEPARTMENT: Each academic department...
All questions in this assignment refer to the “om” database (or Schema) that you will find...
All questions in this assignment refer to the “om” database (or Schema) that you will find in your MySQL Workbench program if you have run the sample database install script. Please save all of your answers in one script (.sql) or type all your answers into Notepad++ and submit them as a single .sql file. You are encouraged to test your SQL statements in Workbench, and please use the ‘tidy’ tool to properly format your SQL before you save it...
Q No. 4: Evaluate the following statements and answer the questions: a. “A Country is always...
Q No. 4: Evaluate the following statements and answer the questions: a. “A Country is always worse off when its currency is weak (falls in value)”. Is this statement true, false or uncertain? Explain your answer. 3 b) If the Pakistani government unexpectedly announces that it will be imposing higher tariffs on foreign goods one year from now, what will happen to the value of PKR today? c. Understanding regarding Translation and transaction exposure is considered important for the management...
Consider the following universal relation THE following database schema is in 4NF. What can you infer...
Consider the following universal relation THE following database schema is in 4NF. What can you infer about multi-valued dependencies? A C D A B C E E F A-It does not have multi-valued dependencies. B-The multi-valued dependency A ->-> C does not hold. If the multi-valued dependency A ->-> C held, the database would not be in 4NF. C-The multi-valued dependency A ->-> B does not hold. If the multi-valued dependency A ->-> B held, the database would not be...
Q. 4. In the following questions choose the most appropriate answer. (10 m) . The buyer...
Q. 4. In the following questions choose the most appropriate answer. (10 m) . The buyer purchases at his own risk. This doctrine is called a.Novation b.Caveat emptor c.Anticipatory breach of contract d.None of the above A sale of goods by Mumbai businessman to his customer in Delhi will be subject to- a.Delhi VAT b.Mumbai VAT c.Maharashtra VAT d.Central Sales Tax A chartered Accounts is liable to collect from his clients- a.Excise duty b.Service Tax c.VAT d.GST In case of...
This refer to the “om” database (or Schema) that you will find in your MySQL Workbench...
This refer to the “om” database (or Schema) that you will find in your MySQL Workbench program if you have run the sample database install script. Please save all of your answers in one script (.sql) or type all your answers into Notepad++ and submit them as a single .sql file. Please test your SQL statements in Workbench 1.       Using an INNER JOIN, select the order_id, order_date, shipped_date, fname, and customer_phone from the orders and customers tables. The fname is a...
lab requires you to use Oracle VIEW to implement a virtual database on DBSEC schema, for...
lab requires you to use Oracle VIEW to implement a virtual database on DBSEC schema, for example, on CUSTOMER table. Your task is to develop a single SQL script that will perform all the following tasks: Table created for this assignment is listed below: create table CUSTOMER_VPD( SALES_REP_ID NUMBER(4), CUSTOMER_ID NUMBER(8) NOT NULL, CUSTOMER_SSN VARCHAR(9), FIRST_NAME VARCHAR(20), LAST_NAME VARCHAR(20), ADDR_LINE VARCHAR(40), CITY VARCHAR(30), STATE VARCHAR(30), ZIP_CODE VARCHAR(9), PHONE VARCHAR(15), EMAIL VARCHAR(80), CC_NUMBER VARCHAR(20), CREDIT_LIMIT NUMBER, GENDER CHAR(1), STATUS CHAR(1), COMMENTS...
Consider the following database schema:                Product(maker, model, type)                PC(model, speed
Consider the following database schema:                Product(maker, model, type)                PC(model, speed, ram, hd, rd, price)                Laptop(model, speed, ram, hd, screen, price)                Printer(model, color, type, price) Give SQL statement for each of the following: (Grouping and Aggregation) Write the following queries in SQL: Find the average speed of laptops costing over $2000. Find the average price of PC’s and laptops made by manufacturer “D”. Find, for each manufacturer, the average screen size of its laptops. Find the manufacturers...
Consider the following database schema:                Product(maker, model, type)                PC(model, speed
Consider the following database schema:                Product(maker, model, type)                PC(model, speed, ram, hd, rd, price)                Laptop(model, speed, ram, hd, screen, price)                Printer(model, color, type, price) Give SQL statement for each of the following: (Subqueries, Join operations) Write the following queries in SQL: Find the makers of PC’s with a speed of at least 1200. Find the printers with the highest price. Find the laptops whose speed is lower than that of any PC. Find the model number...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT