** Please check this ERD **

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,
• email address,
• vehicle registration number.

Draw the entity-relationship diagram (ERD)

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


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

Thanks for your response.
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.

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

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_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.

Does that not link them?

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.

car park4 ERD

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:


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…

CPK CP_Space
FK Employee_ID

Latest version:

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.