Normalize the following relational schema into at least Third Normal Form (3NF) and show that all entities are in at least 3NF.
Make a copy of the diagram Normalization-ERD in LucidChart and then modify that copy (use File/Duplicate in LucidChart -- do not ask for edit
access as we cannot have you edit a shared document, for obvious reasons). The ERD is below for reference but you must open the link and
copy the diagram.
Partial logical data model of a business process. Shows that
activities use documents and that activities are organized
(sometimes) into pools of activities. The same document can be
used by multiple activities. Time is always in a single time unit, which
can be either minutes, hours, or days.
Pool
PK pID numeric
pName text
Document
PK doc_id integer
name text
CK file_path text
uses A
PK aid integer
FK poolID numeric
Activity
aName text
aTime numeric
aTimeUnit {mins,hrs,days}
is part of A
You may collaborate and discuss this problem with up to two peers in private and you may post questions on the course forum but you may not
seek the assistance of anyone outside the course.
You must make an individual submission, i.e., you and your discussion partner cannot submit the same diagram.
Add additional entities (tables) as required.
Open the LucidChart diagram to see the glossary of terms on a separate page (tab).
Resolve any many-to-many relationships.
Submission
Submit a PDF that contains the revised ERD (in Crow's Foot Notation) and your "proofs" of 3NF compliance. You do not need to (but should) update
the glossary or sample tables. By "proof" we mean that you need to show that each table is in 3NF by listing the functional dependencies and
explain that the list of FDs meet the requirements of 3NF.
