Database Concept and Design I\'m having difficulty trying to solve these problem
ID: 3596493 • Letter: D
Question
Database Concept and Design
I'm having difficulty trying to solve these problems. Could someone help???
The tables on the following pages each violate first normalization rules described in Chapter 5 of the lectures. The tables have missing table names, duplicate column names in a table, composite attributes, row order or column order not arbitrary, repeating columns of similar information, and multi-valued attributes—all 1NF faults.
For each of the faulty designs,
(1) Set up the structure (table names, column names, primary keys, any foreign keys) of one table or several relational tables so the tables satisfy the first rule of normalization.
Design 1:
TABLE NAME
COLUMN NAME
Table and column names must satisfy naming rules listed on the first page, including no spaces or hyphens
KEY
For a foreign key,Tablename.Columnnameit references.
?
STUDENT FULLNAME
(This column stores 3 names in one column, a 1NF composite fault. What are you going to use for a primary key?)
STUDENT GPA
(For convenience, the student’s overall GPA is included in this table.) When would you update this column so the GPA is accurate?
STUDENT HOURS
(For convenience, this column stores the total number of credit hours completed by the student.)
STUDENT ADDRESS 1
(This column stores Street number, Street name, City, State, ZIP in one column; 1NF composite fault.)
STUDENT ADDRESS 2 (now the composite fault also becomes another 1NF fault: repeating groups of similar information.)
Corrections for the 1NF faults in Design 1 above:Use unique names for your tables. In each table, use unique names for your columnnames. You can copy and paste the above Microsoft table and edit it to correct the faults: Delete the comments within parentheses here and in later designs.
Design 2:I carried the Fullname column fault from Design 1 into this table and the later ones as well. In five separate columns, this table stores up to five sections that the student is enrolled in at the present time. Delete the comments within parentheses. The group that repeats has one column. In Design 6, the group that repeats has six columns.
TABLE NAME
COLUMN NAME
KEY
Tablename.Columnname referenced by any foreign key
STUDENT FULLNAME
(1NF composite fault because it stores 3 names of a student in one column. You can copy and paste your solution from Design 1 and set up a good primary key.)
SECTION 1
SECTION 2
SECTION 3
SECTION 4
SECTION 5
TABLE NAME
COLUMN NAME
Table and column names must satisfy naming rules listed on the first page, including no spaces or hyphens
KEY
For a foreign key,Tablename.Columnnameit references.
?
STUDENT FULLNAME
(This column stores 3 names in one column, a 1NF composite fault. What are you going to use for a primary key?)
STUDENT GPA
(For convenience, the student’s overall GPA is included in this table.) When would you update this column so the GPA is accurate?
STUDENT HOURS
(For convenience, this column stores the total number of credit hours completed by the student.)
STUDENT ADDRESS 1
(This column stores Street number, Street name, City, State, ZIP in one column; 1NF composite fault.)
STUDENT ADDRESS 2 (now the composite fault also becomes another 1NF fault: repeating groups of similar information.)
Explanation / Answer
DESIGN 1:-
TABLE NAME
COLUMN NAME
KEY
STUDENT
FIRSTNAME
PRIMARY KEY
MIDDLENAME
LASTNAME
STUDENT GPA
STUDENT HOURS
STREET
CITY
ZIPCODE
STREET2
CITY2
ZIPCODE2
DESIGN 2:-
TABLE NAME
COLUMN NAME
KEY
SECTION
FIRSTNAME
FOREIGN KEY (FIRSTNAME) REFERENCES STUDENT(FIRSTNAME)
SECTION 1
SECTION 2
SECTION 3
SECTION 4
SECTION 5
TABLE NAME
COLUMN NAME
KEY
STUDENT
FIRSTNAME
PRIMARY KEY
MIDDLENAME
LASTNAME
STUDENT GPA
STUDENT HOURS
STREET
CITY
ZIPCODE
STREET2
CITY2
ZIPCODE2
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.