Hai everybody recently i came across this article and i have tried to answer all the follwoing questions. But i am not sure its correct or not..so you peoples can comment on the follwoing questions.
2)
Employee (ssn, Name, Salary, Address, ListOfSkills)
Yes,
No.Ans: No. as list of skills would be repeated.
3)
Department (Did, Dname, ssn)
Yes,
No.Ans: No. ssn and did should be moved to a seperate table.
4)
Vehicle (LicensePlate,Brand,
Model, PurchasePrice, Year, OwnerSSN, OwnerName
Yes,
NoAns: No.
5)
Employee (ssn, Name, Salary, did) (obs.:
employee can only belong to one department)
Yes,
No.Ans: Yes.
6)
Customer (Cust_Id, Name, Salesperson, Region) where Salesperson
determines Region.
Yes,
No.Ans: No.Salesperson and region should be moved to a seperate table.
7)
Component (ItemNo, ComponentNo, ItemName, Quantity) where ItemNo
->ItemName
Yes,
No.Ans: No.As itemname is a subset of itemno and not a subset of both itemno and componentno.
Not homework, right? :)
Hai everybody recently i came across this article and i have tried to answer all the follwoing questions. But i am not sure its correct or not..so you peoples can comment on the follwoing questions.
2) Employee (ssn, Name, Salary, Address, ListOfSkills)
Yes, No. Ans: No. as list of skills would be repeated.
louis: exactly. Any column that is plural likely represents multiple things...
3) Department (Did, Dname, ssn)
Yes, No. Ans: No. ssn and did should be moved to a seperate table.
Louis: Well, Did is fine, but I would expect that ssn violates fourth normal form. If the SSN represents something where there is only one of them (like the manager,) then this is fine. If it represents a member of a department, then you definitely have problems because the department name and members of the department relate differently to the Did key of the Department table.
4) Vehicle (LicensePlate, Brand, Model, PurchasePrice, Year, OwnerSSN, OwnerName
Yes, No Ans: No.
Louis: if you are only allowing a single owner of the vehicle AND you only track the most recent purchase information, then yes. Else no. You always need to consider cardinality between attribute and key.
5) Employee (ssn, Name, Salary, did) (obs.: employee can only belong to one department)
Yes, No. Ans: Yes.
Louis: agree. One employee, one name, one salary, one department, all data corresponds to the employee. That is fine.
6) Customer (Cust_Id, Name, Salesperson, Region) where Salesperson determines Region.
Yes, No. Ans: No.Salesperson and region should be moved to a seperate table.
Louis: Good question. Was this the salesperson of the customer, and the Region of the customer? Or is this the region that the salesperson works, regardless of the location of the customer? That makes a big different.
7) Component (ItemNo, ComponentNo, ItemName, Quantity) where ItemNo -> ItemName
Yes, No. Ans: No.As itemname is a subset of itemno and not a subset of both itemno and componentno.
Louis. No, like you said, this violates second normal form
|||Thanks louis, definetly its not homework. I am very much interested in design, that's why i posted.sql