Question

In: Computer Science

In the MedicalVisit table the primary key is defined as (Date, Doctor, Patient).     1. If the...

In the MedicalVisit table the primary key is defined as (Date, Doctor, Patient).    

1. If the primary key were to be changed to (Doctor, Patient), how would it affect the design and information in the database?  (eg information structure in the database, normal form etc.)

2.  Please Draw an ER diagram based on the above original Relational Model

Solutions

Expert Solution


PLEASE FIND THE ANSWER(S) and EXPLANATION BELOW.

  1. Primary key is used to UNIQUELY identify the row in a table.
    • When we change the primary key from (Date, Doctor, Patient) to (Doctor, Patient), we will no longer able to uniquely identify the rows in the MedicalVisit table.
    • So, this will make the table not in any relation form as it even won't satisfy the First Normal Form (1NF).
  2. ER Diagram for the Relational Model


Related Solutions

Why might the doctor want to know if the patient is having a primary or secondary...
Why might the doctor want to know if the patient is having a primary or secondary immune response?
create table node( node_id integer primary key, node_color varchar(10)); create table edge( edge_id integer primary key,...
create table node( node_id integer primary key, node_color varchar(10)); create table edge( edge_id integer primary key, origin_id integer, destination_id integer, foreign key (origin_id) references node(node_id), foreign key (destination_id) references node(node_id)); write an SQL query that lists all those nodes that have edges with a destination node that has color 'red'.
1. Use SQL to create a polyinstantiated table including a primary key and a unique constraint...
1. Use SQL to create a polyinstantiated table including a primary key and a unique constraint 2.Use SQL to insert multiple records for each security classification with the same ID. You must have 4 classifications. 3.Use SQL to create 4 schemas, one for each security classification 4.Use SQL to create a view in each schema that restricts the records to those belonging to a particular security classification and restricts the columns to only those columns that have relevant data. 5.Select...
There is a table t with a column salary which is the primary key. Write a...
There is a table t with a column salary which is the primary key. Write a query to get the nth minimum salary without using multi query or limit. You will be awarded no points if you use multi query or limit or rownum or rowid. no subquery are allowed. please solve it with joins
Create table, create primary and foreign key constraints. Create index on the table to satisfy a...
Create table, create primary and foreign key constraints. Create index on the table to satisfy a query with aggregate functions.
1. Select all Course table data and display in primary key order. Show this query result.
Table Course:    Column    dataType Constraint ccode char(8) primary key, meetingTime char(4), room char(6), fid    decimal(3) NOT NULL, cTitle    varchar(24) NOT NULL, fee    decimal(4,2),    prereq char(8) 1. Select all Course table data and display in primary key order. Show this query result. 2. By default MySQL runs in autocommit mode. Issue a START TRANSACTION command before a series of update commands that may need to be rolled back or to be committed. ROLLBACK will "undo" commands given in the transaction. [Note:...
Consider the following table definitions create table node( node_id integer primary key, node_color varchar(10)); create table...
Consider the following table definitions create table node( node_id integer primary key, node_color varchar(10)); create table edge( edge_id integer primary key, origin_id integer, destination_id integer, foreign key (origin_id) references node(node_id), foreign key (destination_id) references node(node_id)); What is the result of the following query? select node_id, node_color, destination_id from node, edge; An inner join of the tables node and edge that lists origin node_id and node_color together with the node_id of the destination node for all those nodes that have outgoing...
Why it is important for a Doctor of Chiropractic to be recognized as a "Primary Health...
Why it is important for a Doctor of Chiropractic to be recognized as a "Primary Health Care Provider" from the perspective of both the patient and doctor?
Database Problem description:1. What is the difference between a candidate key and the primary key for...
Database Problem description:1. What is the difference between a candidate key and the primary key for a given relation? What is a superkey? 2. What is a foreign key constraint? Why are such constraints important? What is referential integrity? 3. Answer teach of the following questions. The questions are based on the following relational schema:Emp(eid: integer, ename: string, age: integer, salary: real)Works(eid: integer, did: integer, pct_time: integer)Dept(did: integer, dname: string, budget: real, managerid: integer) (1) Write the SQL statements required...
The doctor prescribes her patient a treatment for a non-life-threatening condition. The doctor argues that this...
The doctor prescribes her patient a treatment for a non-life-threatening condition. The doctor argues that this is a useful allocation of health care resources because the treatment will improve the patient’s quality of life. A health economist disagrees with the doctor and says the treatment, in this particular case, is a wasteful use of resources. Explain each the doctor’s and the economist’s perspectives applying marginal analysis.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT