ER modelling - Generating patient ID every visit

Question

Hi I am doing an assignment on ER modelling and there is a part that I'm stuck on, here is an extract:

Patient is a person who is either admitted to the hospital or is registered in an outpatient program. Each patient has a patient number (ID), name, dob, and tele. Resident patients have a Date Admitted. Each outpatient is scheduled for zero or more return visits, which have data and comments. Each time a patient is admitted to the hospital or registered as an outpatient, they receive a new patient number.

I can't do the last section bolded. I have attempted the question: http://tinypic.com/r/358dus9/4

Also if anyone can check if I've done it correctly, would be highly appreciated thanks!

Answer 1

Sometimes assignments also contain "information" that is pretty much immaterial.

The purpose is precisely to learn to filter out the 'real' information from the noise.

(With the caveat that there are dozens and dozens of ER dialects, and each has its own peculiarities,) ER does not have a way to express the information that "attribute x in entity y is to be autogenerated by the system.". For this reason, and as far as the actual ER modeling is concerned, your bold phrase is just noise.

Answer 2

I agree with Erwin on this one. I'll add that not having to have a consistent structure for the patient means that you don't have to create another table for the patient, you can just put it into the ER case directly.

Generally, this is a bad practice however. In reality, you would still have a regular patients table with identifiable patients over several visits. Then again, this is a class and as we all know, the #1 rule is not to disobey the teacher (no matter how insane it is). The real lesson here is to learn how to take requirements, have them clarify the requirements, explain the consequences if they don't follow your advice on how the data will be modeled and then go ahead with whatever they say as they have the final say as the client.

Depends on the course that you're taking, as well. Microsoft SQL Server/SQL Express has the autonumber setting possible, while Oracle does not feature this (although it's accomplished through this). Insofar as the modeling is concerned, there is no way to model that requirement specifically, as far as I know.

Entity-relationship diagrams are used to model the relationships and the data itself as it exists. What you're looking for is more of a UML approach to describing the process in which it has data created for that field.