sql - Database Relation Anomalies -

i tasked find anomalies within relation. had identified few insertion, deletion , update anomalies.

commission percentage: percentage of total sales made salesperson paid commission salesperson.

year of hire: year salesperson first hired

department number: number of department salesperson works manager name: name of manager of department

however, confused anomalies pulled out. below statement:

there can not manager same name in company there no primary identifier manager entity except name, can duplicate within company.

may know how should phrase above statement , under (update/deletion/insertion) anomaly should include in?

thank you

---

may request additional assistance below well:

2. how change current design , how new design address problems have identified current design.

my current design splitting 3 relations: salesperson(salespersonnumber, salespersonname, commissionpercentage, yearofhire, deparetmentnumber)

product(productnumber, productname, unitprice)

manager(managernumber, managername, departmentnumber)

however, missing out quantity entity. quantity requires composite key of productnumber & salespersonnumber. should make in relation itself?

quantity(productnumber, salespersonnumber)

1. anomalies

when identifying identifying (potential) anomalies, you're listing dependent attributes affected anomalies (you forgot salesperson name, btw). specifically, listed attributes depended on subset of key (salesperson number, product number), violating 2nf. you're on right track.

however, careful not confuse attributes anomalies. update anomaly if 1 of 3 instances of bilstein got changed. (assumed) functional dependency salesperson name depends on salesperson number broken , data inconsistent (salesperson number 437 associated more 1 name). remember normalization aims eliminate redundant associations.

2. identity

the problem identifying managers name indicates poor modeling decision. stated, company's set of managers isn't uniquely identified name, there's mismatch between logical data model , world models. won't cause insert, update or delete anomalies long use different values different managers, prevent convenient identification of managers. possible improvements use multiple attributes (abstract domains identified combination of attributes, natural domains people aren't, e.g. manager name, birthdate more identifying still not solution), turn manager name surrogate key (e.g. scott1, scott2), or introduce new surrogate key (e.g. numeric id).

3. proposed improvement

your proposed design normalizes original table addressing identification problem. it's answer except 2 issues: doesn't include association between salesperson , manager, , in quantity relation, forgot include quantity dependent attribute.

good job far, hope helps.