Entities, Attributes and Primary Keys

[kev88]

Hi there,

I was looking for guidance with regards to this problem. Any help would be greatly appreciated

Problem

A new accommodation block has been built on a university campus close to university facilities. There are three main buildings, named Hufflepuff, Ravensclaw and Slytherin, each with mixed accommodation. Hufflepuff also provides breakfasts and evening meals for those students who do not want to self-cater. The Accommodation Office requires a database to support the new letting processes. The type of accommodation available is as follows: a single room with shower; a single room with shared bathroom; small flat with 2 bedrooms. All bedrooms other than that of the flats have shared kitchen and dining facilities. Students can request the type of accommodation and the block they wish to live in, but are allocated places on a firstcome, first-served basis. They can also express a preference for self-catering, which
incurs an extra cost, or eating in the Hufflepuff canteen.

Required:

From the above description, identify the main entities, their attributes, data types and primary keys, for an Accommodation Office database.

Would the entities in this example be
- Building
- Meals
- Student

anything else?

I'm just trying to grasp the theory of this stuff so any help would be excellent

Thanks a lot!

 

[DCrake]

When has your assignment to be in by?

 

[kev88]

It's a past paper question which Im trying to do as part of my revision - it will help me do the main exam if someone can talk me through this example

 

[gemma-the-husky]

as well as building, you need a "flat" entity - to distinguish those flats which are self-contained, and those which use shared facilites. And obviously, the students live in falts, not in buildings. (unless they share a single large dorm!) There may be others entities you need also

one way is to go through, and list every datum (item of data) - and the entities (tables) you think you need - and then see if each datum can logically be attached to one of the propsed tables. If you find there is nowhere to put it, you need another entity.

you are looking to get a "chain" of 1 to many relations.

eg the flat/student relation is interesting. If only 1 student lives in a flat, you will find you don't need an "accommodates" table. you could either give a student a flatID - or give the student a FLATID. But if flats are multi-student then you do need an accommodation table.

All of which is why Data Analysis is so important to any project. Do it wrong at the start, and it becomes harder down the line.

And it's often an art as much as a science. In the real world the system specifiers often miss important details out that are "obvious" to them, but which affect the design.

 

[stopher]

you are looking to get a "chain" of 1 to many relations.

The question doesn't mention relationships at all. The question simply asks to "identify the main entities, their attributes, data types and primary keys".

In any case, an ER diagram could have many-to-many relationships (unresolved) depending on how they've been taught.

Chris

 

[gemma-the-husky]

The question doesn't mention relationships at all. The question simply asks to "identify the main entities, their attributes, data types and primary keys".

In any case, an ER diagram could have many-to-many relationships (unresolved) depending on how they've been taught.

Chris

whether it mentioned relationships or not - correct data analysis WILL result in a number of tables that form a series of 1 to many relationships, won't it?

many to many just isn't correct, is it?

"series" may be the wrong word - but all relationships will/should ultimately be 1-to-many.

 

[stopher]

whether it mentioned relationships or not - correct data analysis WILL result in a number of tables that form a series of 1 to many relationships, won't it?

Sure, but that isn't answering the question. The question simply asks to list the main entities (typically the first stage to producing an E-R diagram). If you start drawing diagrams with relationships then you’ve jumped the gun a bit. This is an exam question.

many to many just isn't correct, is it?

I agree. But the question asking only for the main entities. I reckon this means the many to many relationships have not yet been considered let alone resolved. Why would they be because we haven’t even gotten to the point of producing a diagram yet. Relationships do not come into the question.

Chris

 

[gemma-the-husky]

It just seems to me that "main" entities still to me means dissecting the problem into a normalised form.

the non-main entities to me would be the lookup tables.

so if you produce entiites such as originally premised by the OP

- Building
- Meals
- Student


you can't possibly find a hole for the attribute that deals with the "self-contained" nature of the flat. It seems to me that primary entities MUST still be posited on construction of a normalised form.

You might not draw in the relation lines - but it's surely part of the thought process, isn't it?

 

[stopher]

It just seems to me that "main" entities still to me means dissecting the problem into a normalised form.

The purpose of the E-R model is to conceptualise the problem and start to visualise how information is related. It is use to try and get some handle on what the problem looks like, what the scope is and to ensure all information requirements are captured in the model. It is not intended to be used directly as the design model for the constructing the database and it does not attempt to address database problems. It is the purpose of the Relational model to do this.

You might say that the E-R model is the same as the Relational model but this is not the case (otherwise why have two models). We are talking about Entities, not tables.

Here's an example. In an E-R model I might have a many-to-many relationship. Consider a many-to-many relationship between students and courses. In an E-R diagram we show this with crows feet at both ends to show n:n. In E-R modelling we are not so concerned about how this will be managed in a database. All we need to do is model that there is a relationship between students and courses. But in the Relational model we of course deal with this (typically with a junction table).

Here's another example. In an E-R model I would model the relationship between customers, orders and products as three entities. I don't need to deal with the problem that an order has many lines (this is a database design problem). However, in the Relational model I'd probably have four tables i.e. the order entity becomes and order header table and an order detail table in the Relational model.

It's also reasonable to show 1:1 relationships as two entities in the E-R model. I simply want to get a handle on the problem, not worry about how I will deal with it in a database. In the Relational model I might decide that I can deal with this with just one table.

I don't think normalisation has any particular relevance in the E-R model (otherwise I would have dealt with the orders entity). Normalisation is relevant to the Relational model where the purpose is to optimise for database structure.

The whole point is you can discuss an E-R model with a client. He will understand that customers have orders. But if you start talking about order header/detail and even worse, normalisation, the client will become lost.

It might help to watch this as it explains how E-R models are mapped to Relational models and hopefully goes some way to explain the difference between the two. However, I think teaching sometimes considers E-R and Relational modelling as the same effectively cutting out the conceptual modelling stage.

so if you produce entiites such as originally premised by the OP

- Building
- Meals
- Student


you can't possibly find a hole for the attribute that deals with the "self-contained" nature of the flat.

Good question. I'm thinking that Students live in an Abode so Abode might be an entity. Just an idea.

 

[The_Doc_Man]

many to many just isn't correct, is it?

Dave, Many-to-Many is a perfectly valid real-world description of a relationship. The problem is in implementing it, which is why Access has to use a JUNCTION table as the intermediary between two entities that are legitimately related via many:many setup. For instance, in a given store, the list of inventory items and suppliers is a many:many list, particularly if each supplier can supply items that overlap with those of other suppliers and each item can come from any of several suppliers.

 

[gemma-the-husky]

thanks for the clarification. It makes sense.

I think in another thread, I noted that I wasn't expert on the formal application of different analysis methods.

 

[kev88]

Thanks for all the detailed responses.

So here's my attempt at the original question

Entities with attributes

1) Building - Building ID, Building Name
2) Meals - Meal ID, Meal Type
3) Flat - Flat ID, Room Type
4) Student - Student ID, Name, Building ID, Flat ID

And the primary key would be Student ID?
Am I way off?

Thanks again for all the help - much appreciated!

 

[stopher]

I'm not sure there's a meals entity as such. This would imply that you are wanting to capture information about meals. But really you want to capture whether a building provides meals.

The way to do this is to go through each sentence in the question and see if you've accounted for it.

Take a look at the sentence:
"The type of accommodation available is as follows: a single room with shower; a single room with shared bathroom; small flat with 2 bedrooms."

This implies to me that there are two types of accommodation. I think you need to have a way of expressing this either as entities or attributes. And what about the amenities? Do these need to be modeled?

By the way, have you been taught about sub-entities?

As you've gathered from the previous posts, what is meant by "entities" depends a lot on how you've been taught. Imo, a pure entity does not have foreign keys. Foreign keys are used in relational models. If you were to draw an ERD (entity relationship diagram), the relationship is all that is required to identify that a student "resides in" a flat and you would write the words "resides in" in a diamond for example. So you really need to check how you've been taught to decide if you are being asked for entities for an entity model or for a Relational model.

The last two sentences suggest to me that there is an application entity with appropriate attributes.

You need to ensure that you've incorporated every bit of information that you think should be recorded in you model.

On the question of primary keys (PK), each entity should have a PK. The way I was taught was that building name is sufficient as a primary key because in the real world all the building names are different and in the real world they don't have IDs. However, with students I think student ID is appropriate as a PK since they do normally have one. Note this is different to the relational model where are database designed would assign discreet keys such as building ID. But that's just how I was taught.

Please note it was ten years ago since I studied this

hth
Chris

 

[kev88]

Ok thanks. I have attempted this simpler example here - maybe I've started with too hard a problem without having a basic grounding in the theory!

Question

A hospital requires a database to store admission information about patients. On arrival, each patient’s personal details (name, address, age, telephone number) are recorded, and they are given an admission number. The patient is then assigned to a particular ward (Accident and Emergency, Cardiology,
General, etc.). Each ward is staffed by a number of doctors and nurses. A patient will be treated by one doctor and several nurses over the course of their stay, and each doctor and nurse may be involved with several patients at any given time.


Identify the entities, their attributes and primary key, from this scenario

My answer attempt

Patient - Patient Admission No., Name, Address, Age, Telephone Number, Doctor ID, Nurse ID

Ward - Ward ID, Ward, Nurse ID

Doctor - Doctor ID, Doctor Name, Ward ID, Patient Admission No.

Nurse - Nurse ID, Nurse Name, Ward ID

Primary key would be patient admission number?

I think I've got this simpler example right? Have I? If I've done this right, I can apply my method to the harder problem in the original post

Thanks again for the help!