** Please check this ERD **

Hello.
I have a project I am working on for a data management course, where an ERD needs to be created.

==
This is the case study:

A large organisation has designated car parks in the vicinity of its main building, which are used by its employees.

Each car park has a:
• unique name,
• location,
• capacity,
• number of levels where appropriate.

In each car park, the spaces are uniquely identified using a space number.
Employees can request a designated space for their sole use.

Each employee has a
• unique employee number,
• name,
• telephone extension,
• vehicle registration number.

Draw the entity-relationship diagram (ERD)

==
As well as checks for accuracy for the ERD, any recommendations/suggestions would be appreciated.

Thanks

Can explain your reasoning for marking PK or FK for the applicable fields of each table? One of them does not seem correct.

Bear in mind that this is all new to me!

As far as I understand, PK is the primary key and is a unique identifier in a table.

FK is an attribute that links two tables.

I know what PK and FK are. I asked for you to explain your reasoning for assigning PK and FK to the fields in each table. Basically, explain why you chose PK or FK for a particular field. This will help me to understand your rational and give you better feedback on how your rational may be incorrect.

For example, start with `CAR PARK`. Why did you choose PK for CP_NAME and FK for CP_LEVEL? Do the same for the other 3 tables.

CAR PARK table:
CP_Name is a unique name for each car park, hence PK.
CP_Level is an attribute that links this table to Car Park levels, hence FK.

CAR PARK LEVELS table:
CP_Level is a unique identifier for each car park level, hence PK.
CP_Name links this table with CAR PARK table, hence FK.

CAR PARK SPACE table:
Each car park space is unique, hence CP_Space is PK.
Employee_ID links this table to CAR PARK table, hence FK.
CP_Name links this table to EMPLOYEE DETAILS table, hence FK.

EMPLOYEE DETAILS table:
Employee_ID is a unique identifier, hence PK.

Does it really? I do not see this table linked to CAR PARK via CP_NAME.

CP_Name is in both CAR PARK and CAR PARK LEVELS tables.

Did you create a link for them? I do not see one.

I presumed that as the two tables, (CAR PARK and CAR PARK LEVELS), are already linked via CP_Level they don’t need an extra line linking them, for CP_Name.

Can two tables be linked more than once, diagrammatically?

I’ve removed CP_Level from CAR PARK table, as it was perhaps unnecessary.

CP_Name now links CAR PARK and CAR PARK LEVELS.

From the case study you provided, I don’t see a need for the `CAR PARK LEVELS` table. I would just include it in the `CAR PARK` table:

``````CAR PARK:
Name
Location
Capacity
Levels
``````

Also, it says `In each car park, the spaces are uniquely identified using a space number.` - so assuming the `CP_Space` column is that space number, I don’t think that would make it unique to use as a primary key - two different car parks could have the same space number. I would make it a composite key that uses the `CP_Space` and `CP_Name` columns.

Thanks, Tom. That sounds logical.
Like so?

Sort of - you don’t need an extra column though for that composite key…

``````CAR PARK SPACE:
CPK CP_Space
FK Employee_ID
CPK CP_Name
``````