Practice Exercise $09-$ Strong & Weak Entities

Special Cases of Referential Integrity:

Case $1$: Strong Entity

An entity type that has no existence dependency is called strong entity. It can exist without depending upon the existence of another entity. The strong entity is also referred to as parent, or owner, or dominant.

Case $2$: Weak Entity

An instance of a weak entity cannot exist independently, but depends on the existence of another entity $($usually a strong entity$)$ instance.

For examole:

EMPLOYEE$($ empNo, name, department, startingYear, benefits, ssn$,...)$

DEPENDENT$($ empNo, name, birthdate, sex, ssn$,...)$

Your Task

Create a database with two tables to demonstrate the difference between strong entities and weak entities.

$($Do NOT use this EMPLOYEE & DEPENDENT example from above, since we talked about it in class. Don't use any example from class. Come up with your own.$)$

Create an ER diagram that details each of the two entities and the relationship between them. You can use any tool that you like, such as

Draw.io$,$ Lucidchart, Gliffy, or many others.

Write a SQL script to create the two tables, including foreign key constraint$($s$),$ and insert several records into each table.

Include several queries against your data:

a$.$ Query of all records in the strong entity

b$.$ Query of all records in the weak entity

c$.$ Query of selective data from both the strong and weak entities:

i$.$ This should be a logical and meaningful report

ii$.$ Data should be included from both tables

iii. Include a projection of columns $($do not write SELECT $*...)$