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