The Language of this is SQL and this Question Involves Database Structuring ####

Question

The Language of this is SQL and this Question Involves Database Structuring

#############################################################################

Consider the relation: STUDENT (SSN, FirstName, MiddleInitial, LastName, Address, PhoneNumber, Age, GPA)

a) Give an argument for keeping the FirstName, MiddleInitial, LastName as separate attributes instead of combining them into a single attribute (e.g., Name).

b) Suppose we want to store three different phone numbers -- home phone, cell phone, office phone -- for each student. Then, the single attribute PhoneNumber is not suitable anymore. State the reason why it is not, and propose how you would change the schema to accommodate this situation.

c) Give an argument against using Age as an attribute, and suggest an alternative attribute that resolves the point of your argument

Explanation / Answer

a) An argument for keeping the FirstName, MiddleInitial, LastName as separate attributes instead of combining them into a single attribute (e.g., Name) : sometimes it is needed to sort the database based upon last name or some time it is needed to see how many students are there of same last name etc.; due to these situations we have to keep separate attributes of FirstName, MiddleInitial, LastName because if we combine them into a single attribute those queries would create problem. To make some situations easy to handle these attribute should be kept as separate fields.

b) If we have to store different phone numbers -- home phone, cell phone, office phone -- for each student, then, the single attribute PhoneNumber is not suitable anymore, because that would violate database property. To be in First Normal form, each relation should only contain atomic values. If we put 3 phone numbers under one field that would create atomic value problem. Then multiple values would come under one column which violate the basic property of database (which says every column should contain only a single value of a particular domain).

Proposal to change the schema to accommodate this situation: another additional relation may be added with this existing schema to resolve the issue: STUDENT_PH(SSN, HomePhone, OfficePhone) and the cell phone of the student will remain in the main STUDENT relation under PhoneNumber attribute. I think that would solve the problem.

c) An argument against using Age as an attribute: Age attribute is not that much helpful when we need to differentiate students based upon age because one student may be of age 16 years (exactly), another student may be of age 16 years and 11 months; if the attribute is Age then for both students that would be same as 16 years; so we can not make any difference from this information. That is the main reason why Age as an attribute is not that much helpful.

Suggestion: An alternative attribute that resolves the point of my argument is DateOfBirth. Instead of using age if we use DateOfBirth as an attribute that would be more accurate to differentiate students based upon their age when necessary.

/*If this helps you, please let me know by giving a positive thumbs up. In case you have any queries, do let me know. I will revert back to you. Thank you!!*/

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.