4_DB_development_method_l2_v1
Оценка 5

4_DB_development_method_l2_v1

Оценка 5
docx
09.05.2020
4_DB_development_method_l2_v1
4_DB_development_method_l2_v1.docx

However, things may not always be so clearly cut. Take this a table of football players for example:

Table:Player

Name: string

Position: string

Number: integer

injured: boolean

Team: String

Which of these would be the primary key?

Attribute

Unique

Reason

Name

No

you might have several players with the same name

Position

No

you might have two goalies

Number

No

you might be storing the details of multiple teams, in which case each number has several players from different teams

Injured

No

several people might be injured at the same time

Team

No

several players can play for the same team

So what do we do? Before we make our database tables it's best to go through this process and we come to realise that we need to introduce another attribute that is unique for each player. We might even invent one, a playerID:

Player(PlayerID, Name, Position, Number, Injured, Team)

Another way of solving these problems is to try and find a combination of attributes that together are unique. This is called a composite key. Take a look at this example for houses:

Table: House

Number: integer

Road: string

Colour: string

Post code: string

Attribute

Unique

Reason

Number

No

you might have a 61 on London Rd and a 61 on Manchester Rd

Road

No

you might have multiple houses on the same road

Colour

No

more than one house might be green

post code

No

multiple houses might have the same post code

We could make a primary key up, but if you look carefully, we can use a combination of attributes. The house number and the road name combined seem to be unique. You can't have two 45 Belmont Close can you?.

House(NumberRoad, Colour, Post code)

Does that sound ok? What about if we were storing data on all the towns in the country and there was a 5 London Road in Manchester and a 5 London Road in Winchester. This would mean that the combination was not unique. We might try using the house number and post code instead, and this combination is always unqiue, this is our composite key:

House(Number, Road, Colour, Post code)


 

However, things may not always be so clearly cut

However, things may not always be so clearly cut

Colour No more than one house might be green post code

Colour No more than one house might be green post code
Скачать файл