b. Show 2NF and tell what the rules are for second normal form. c. Show 3NF and
ID: 3581339 • Letter: B
Question
b. Show 2NF and tell what the rules are for second normal form.
c. Show 3NF and tell what the rules are for third normal form
Joe Amateur has designed the OFFENDER table of the National Sex Offenders Registry has care or concern for programming, redundancy or data without much integrity. You've been hired to straighten out the mess by implementing properly related tables. You've been able to come up with the ER diagram in Exhibit A. Now show functional dependency diagrams and the steps required to construct the schema constructs for tables in 3rd normal form. Assume that the following additional FD occurs in addition to what you can read from the ER diagram. zipcode) (City, State) a. Show INF and tell what the rules are for first normal form. (5 pts)Explanation / Answer
FirstNormalForm(1NF)
1NF means creating tables so that each attribute or field must contain a single value from its predefined domain...So 1NF for the above ERD is
CRIME(crime_id,crime,crimeCategory,convictionyear,recordsource,offender_id)
OFFENDER (offender_id,lastname,firstname,middlename,aliases,startdate,enddate,movingstatus,locid)
LOCATIONS(locid,mapid,street,zipcode,state,city)
Second Normal Form(2NF)
A relation is said to be in 2NF if every non-prime attribute should be fully functionally dependent on prime key attribute.
The above tables there is no partial dependency.So they are sai to be in 2NF
CRIME(crime_id,crime,crimeCategory,convictionyear,recordsource,offender_id)
OFFENDER (offender_id,lastname,firstname,middlename,aliases,startdate,enddate,movingstatus,loc_id)
LOCATIONS(loc_id,mapid,zipcode,state,city, street)
Third Normal From,3NF
A relation is said to be in 3 NF,if it is already in 2NF and there is no transitive dependecy.Aliases field is dependent on firstname middle name and lastname.
Sso it is splitted into another table
CRIME(crime_id,crime,crimeCategory,convictionyear,recordsource,offender_id)
OFFENDER (offender_id,name_id,startdate,enddate,movingstatus,loc_id)
LOCATIONS(locid,mapid,street)
ZIPCODE(zipcode,state,city)
NAMES(offende_id,name_id,firstname,middlename,lastname,aliases)
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.