ZuoTi.Pro
Question

4. Using the STUDENI table structure shown in Table P6.4, do the tollowing: a. Write the relational schema and draw its dependency diagram. Identify all dependencies, including all transitive dependencies. b. Write the relational schema and draw the dependency diagram to meet the 3NF requirements to the greatest practical extent possible. If you believe that practical considerations dictate using a 2NF structure, explain why your decision to retain 2NF is appropriate. If necessary, add or modify attributes to create appropriate determinants and to adhere to the naming conventions. Using the results of Problem 4, draw the Crows Foot ERD. c. TABLE P6.4 ATTRIBUTE NAME STU NUM STU_LNAME STU_MAJOR DEPT CODE DEPT NAME DEPT PHONE 435 COLLEGE NAME Business Admin Business Admin Business Admin Business Admin Arts&Sciences ADVISOR LNAME Grastrand ADVISOR OFFICE T201 ADVISOR BLDG Torre Building Torre Building Torre Building Torre Building Jones Building ADVISOR PHONE2115 STU GPA STU_HOURS STU_CLASs SAMPLE VALUE SAMPLE VALUE SAMPLE VALUE SAMPLE VALUE SAMPLE VALUE 211343 Stephanos Accounting ACCT Accounting 200128 Smith Accounting ACCT Accounting 4356 199876 Jones Marketing MKTG Marketing 4378 198648 Ortiz Marketing MKTG Marketing 4378 223456 McKulski Statistics MATH Mathematics 3420 Grastrand T201 Gentry T228 Tillery T356 Chen J331 2115 2.78 45 Sophomore 3209 3.58 87 Junior 2123 2.31 3.87 75 Junior 2159 3.45 113 Senior Senior lote Although the completed student hours (STU_HOURS) do determine the student classifi- cation (STU_CLASS), this dependency is not as obvious as you might initially assume it to be. For example, a student is considered a junior if the student has completed between 61 and 90 credit hours

engineering   Computer-Science   
0 0
Add a comment Improve this question Transcribed image text
Next > < Previous
Sort answers by oldest

Homework Answers

✔ Recommended Answer
Answer #1

Normalization is the process of schema is divided into smaller relations a. Relational schema is as follows Primary key: STU NUM The transitive dependencies are as follows . DEPT. CODE → DEPT NAME, DEPT-PHONE, COLLEGE-NAME . ADVISOR OFFICE-> ADVISOR-BUILDING STU. HOURS→ STU. CLASS The dependency diagram is as follows: STU NUM İSTU LNAME | STU MAJOR | DEPT CODE | DEPT NAME | DEPT PHONE | COLLEGE NAME ADVISOR LNAME | ADVISOR OFFICE | ADVISOR BLDG | ADVISOR PHONE | STU GPA İSTU HOURS İSTU CLASS b. . STU_NUM will recognize ADV_NUM therefore we have added ADV_NUM in this relation. . Therefore ADVISOR_NUM is added to create a proper primary key for the advisor table . In the above dependency diagram the ADV_LASTNAME is not a determinant of ADV_OFFICE or ADV PHONE because there can be many advisors who have the same last name. Following is the dependency diagram for the first relation

4 0
Add a comment
Know the answer?
Add Answer to:
Using the STUDENT table structure shown in Table P6.4, do the following: a. Write the relational...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Similar Homework Help Questions

  • 1. Using the STUDENT table structure shown in Table P6.4, do the following. Table P6.4 Sample...

    1. Using the STUDENT table structure shown in Table P6.4, do the following. Table P6.4 Sample STUDENT Records Attributes Name Sample Value Sample Value Sample Value Sample Value Sample Value STU_NUM STU_LNAME STU_MAJOR DEPT_CODE DEPT_NAME DEPT_PHONE COLLEGE_NAME ADVISOR_LNAME ADVISOR_OFFICE ADVISOR_BLDG ADVISOR_PHONE STU_GPA STU_HOURS STU_CLASS 211343 stephanos Accounting ACCT Accounting 4356 Business Admin Grastrand T201 Torre Building 2115 3.87 75 junior 200128 Smith Accounting ACCT Accounting 4356 Business Admin Grastrand T201 Torre Building 2115 2.78 45 Sophomore 199876 Jones Marketing MKTG...

  • 3. Using the INVOICE table structure shown in Table P6.3, do the following: Table P6.3 a.  ...

    3. Using the INVOICE table structure shown in Table P6.3, do the following: Table P6.3 a.   Write the relational schema, draw its dependency diagram, and identify all dependencies, including all partial and transitive dependencies. You can assume that the table does not contain repeating groups and that an invoice number references more than one product. (Hint: This table uses a composite primary key.) b.   Remove all partial dependencies, write the relational schema, and draw the new dependency diagrams. Identify the...

  • need help! 1. To keep track of vendors and products they supply, XYZCorp. uses the table...

    need help! 1. To keep track of vendors and products they supply, XYZCorp. uses the table structure shown below. Assuming that the sample data are representative, draw a dependency diagram in Visio that shows all functional dependencies including both partial and transitive dependencies. (Hint: Look at the sample values to determine the nature of the relationships.) VEND TYPE Non-profit organization Non-profit organization Profit organization Profit organization Profit organization PART CODE 1234 PART DESC Logic Chip VEND NAME Fast Chips VEND...

  • To keep track of office furniture, computers, printers, and so on, the CSU DIT uses the...

    To keep track of office furniture, computers, printers, and so on, the CSU DIT uses the table structure shown in Table 1. Please note that BLDG stands for building. Table 1 Sample ITEM Records Attribute Name Sample Value Sample Value Sample Value ITEM_ID 231134-678 342245-225 254668-449 ITEM_LABEL HP DeskJet 895Cse HP Toner DT Scanner ROOM_NUMBER 325 325 123 BLDG_CODE NTC NTC CSF BLDG_NAME Nottooclear Nottoclear Canseefar BLDG_MANAGER I. B. Rightonit I. B. Rightonit May B. Next a. Given that information,...

  • PROBLEM STATEMENT: Suppose you have a client that has given you the following business rules to...

    PROBLEM STATEMENT: Suppose you have a client that has given you the following business rules to form the basis for a database design. The database must enable the manager of a company dinner club to mail invitations to the club’s members, to plan the meals, to keep track of who attends the dinners, and so on. Each dinner serves many members, and each member may attend many dinners. A member receives many invitations, and each invitation is mailed to many...

  • Question 1.Write a SQL statement for each of the following questions based on the above tables...

    Question 1.Write a SQL statement for each of the following questions based on the above tables (50 Points). 1) Create “Enrollment” table. 2) Change the credits of “Database” course from 2 to 3. 3) List the course titles (not Course_No) and grades John Doe had taken. 4) Calculate average of Café Balance, and name new field as Average Balance. 5) Create a roster for “Database” course (list student ID, last name, first name, major, and status for each student enrolled...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT