Loading...

Messages

Proposals

Stuck in your homework and missing deadline? Get urgent help in $10/Page with 24 hours deadline

Get Urgent Writing Help In Your Essays, Assignments, Homeworks, Dissertation, Thesis Or Coursework & Achieve A+ Grades.

Privacy Guaranteed - 100% Plagiarism Free Writing - Free Turnitin Report - Professional And Experienced Writers - 24/7 Online Support

Which of the following can lead to partial dependencies

25/03/2021 Client: saad24vbs Deadline: 2 Day

CHAPTER 10: FUNCTIONAL DEPENDENCIES AND NORMALIZATION FOR RELATIONAL DATABASES

Answers to Selected Exercises

15.19 Suppose we have the following requirements for a university database that is

used to keep track of students transcripts:

(a) The university keeps track of each student's name (SNAME), student number

(SNUM), social security number (SSSN), current address (SCADDR) and phone

(SCPHONE), permanent address (SPADDR) and phone (SPPHONE), birthdate

(BDATE), sex (SEX), class (CLASS) (freshman, sophomore, ..., graduate),

major department (MAJORDEPTCODE), minor department (MINORDEPTCODE)

(if any), and degree program (PROG) (B.A., B.S., ..., Ph.D.). Both ssn and

student number have unique values for each student.

(b) Each department is described by a name (DEPTNAME), department code

(DEPTCODE), office number (DEPTOFFICE), office phone (DEPTPHONE), and

college (DEPTCOLLEGE). Both name and code have unique values for each

department.

(c) Each course has a course name (CNAME), description (CDESC), code number

(CNUM), number of semester hours (CREDIT), level (LEVEL), and offering

department (CDEPT). The value of code number is unique for each course.

(d) Each section has an instructor (INSTUCTORNAME), semester (SEMESTER), year

(YEAR), course (SECCOURSE), and section number (SECNUM). Section numbers

distinguish different sections of the same course that are taught during the same

semester/year; its values are 1, 2, 3, ...; up to the number of sections taught

during each semester.

(e) A transcript refers to a student (SSSN), refers to a particular section, and

grade (GRADE).

Design an relational database schema for this database application. First show all

the functional dependencies that should hold among the attributes. Then, design

relation schemas for the database that are each in 3NF or BCNF. Specify the key

attributes of each relation. Note any unspecified requirements, and make

appropriate assumptions to make the specification complete.

10.18 Prove or disprove the following inference rules for functional dependencies. A

proof can be made either by a proof argument or by using inference rules IR1 through IR3. A disproof should be done by demonstrating a relation instance that satisfies the conditions and functional dependencies in the left hand side of the inference rule but do not

satisfy the conditions or dependencies in the right hand side.

(a) {W ->Y, X ->Z} |= {WX ->Y }

(b) {X ->Y} and Z subset-of Y |= { X ->Z }

(c) { X ->Y, X ->W, WY ->Z} |= {X ->Z}

(d) {XY ->Z, Y ->W} |= {XW ->Z}

(e) {X ->Z, Y ->Z} |= {X ->Y}

(f) {X ->Y, XY ->Z} |= {X ->Z}

10.19 Consider the following two sets of functional dependencies F= {A ->C, AC ->D,

E ->AD, E ->H} and G = {A ->CD, E ->AH}. Check whether or not they are

equivalent.

10.22 What update anomalies occur in the EMP_PROJ and EMP_DEPT relations of

Figure 14.3 and 14.4?

10.23 In what normal form is the LOTS relation schema in Figure 10.11(a) with the

respect to the restrictive interpretations of normal form that take only the

primary key into account? Will it be in the same normal form if the general

definitions of normal form were used?

Answer:

If we only take the primary key into account, the LOTS relation schema in Figure 14.11

(a) will be in 2NF since there are no partial dependencies on the primary key .

However, it is not in 3NF, since there are the following two transitive dependencies on

the primary key:

PROPERTY_ID# ->COUNTY_NAME ->TAX_RATE, and

PROPERTY_ID# ->AREA ->PRICE.

Now, if we take all keys into account and use the general definition of 2NF and 3NF, the

LOTS relation schema will only be in 1NF because there is a partial dependency

COUNTY_NAME ->TAX_RATE on the secondary key {COUNTY_NAME, LOT#}, which

violates 2NF.

10.24 Prove that any relation schema with two attributes is in BCNF.

10.25 Why do spurious tuples occur in the result of joining the EMP_PROJ1 and

EMPLOCS relations of Figure 14.5 (result shown in Figure 14.6)?

10.26 Consider the universal relation R = {A, B, C, D, E, F, G, H, I} and the set of

functional dependencies F = { {A, B} -> {C}, {A} -> {D, E}, {B} -> {F}, {F} ->

{G, H}, {D} -> {I, J} }. What is the key for R? Decompose R into 2NF, then 3NF

relations.

10.27 Repeat exercise 10.26 for the following different set of functional dependencies

G = { {A, B} -> {C}, {B, D} -> {E, F}, {A, D} -> {G, H}, {A} -> {I}, {H} -> {J} }.

14.26, starting with the following relation R:

R = {A, B, D, C, E, F, G, H, I}

The first-level partial dependencies on the key (which violate 2NF) are:

{A, B} -> {C, I}, {B, D} -> {E, F}, {A, D}+ -> {G, H, I, J}

Hence, R is decomposed into R1, R2, R3, R4 (keys are underlined):

R1 = {A, B, C, I}, R2 = {B, D, E, F}, R3 = {A, D, G, H, I, J}, R4 = {A, B, D}

Additional partial dependencies exist in R1 and R3 because {A} -> {I}. Hence, we remove

{I} into R5, so the following relations are the result of 2NF decomposition:

R1 = {A, B, C}, R2 = {B, D, E, F}, R3 = {A, D, G, H, J}, R4 = {A, B, D}, R5 = {A, I}

Next, we check for transitive dependencies in each of the relations (which violate 3NF).

Only R3 has a transitive dependency {A, D} -> {H} -> {J}, so it is decomposed into R31

and R32 as follows:

R31 = {H, J}, R32 = {A, D, G, H}

The final set of 3NF relations is {R1, R2, R31, R32, R4, R5}

Homework is Completed By:

Writer Writer Name Amount Client Comments & Rating
Instant Homework Helper

ONLINE

Instant Homework Helper

$36

She helped me in last minute in a very reasonable price. She is a lifesaver, I got A+ grade in my homework, I will surely hire her again for my next assignments, Thumbs Up!

Order & Get This Solution Within 3 Hours in $25/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 3 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 6 Hours in $20/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 6 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 12 Hours in $15/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 12 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

6 writers have sent their proposals to do this homework:

Helping Hand
WRITING LAND
Coursework Assignment Help
Fatimah Syeda
Engineering Exam Guru
Pro Writer
Writer Writer Name Offer Chat
Helping Hand

ONLINE

Helping Hand

I have read your project details. I can do this within your deadline.

$110 Chat With Writer
WRITING LAND

ONLINE

WRITING LAND

I have read your project details. I can do this within your deadline.

$101 Chat With Writer
Coursework Assignment Help

ONLINE

Coursework Assignment Help

I have read your project details. I can do this within your deadline.

$139 Chat With Writer
Fatimah Syeda

ONLINE

Fatimah Syeda

I am known as Unrivaled Quality, Written to Standard, providing Plagiarism-free woork, and Always on Time

$31 Chat With Writer
Engineering Exam Guru

ONLINE

Engineering Exam Guru

Hello, I an ranked top 10 freelancers in academic and contents writing. I can write and updated your personal statement with great quality and free of plagiarism

$172 Chat With Writer
Pro Writer

ONLINE

Pro Writer

I will cover all the points which you have mentioned in your project details.

$28 Chat With Writer

Let our expert academic writers to help you in achieving a+ grades in your homework, assignment, quiz or exam.

Similar Homework Questions

Basic aeronautical knowledge for the rpl pdf - Data flow diagram practice problems - For anyone - 6 pages paper - Human resource management in australia strategy people performance 5th ed - Information Systems Research in the Digital World. - Case study on social media and planned parenthood - Annual demand for the notebook binders at duncans - The ethics of dissent managing guerrilla government pdf - The hardy weinberg equation pogil answers - You were hired as a consultant to quigley company - COM 201: The Power of Effective Speaking. - 7 4x 3 y 2x 3y 1 0 - Bates visual guide to physical examination pdf - National communication association credo for ethical communication - The dead james joyce - Unit 2 Contemporary Issues in Education - 137 palmateer drive bickley - Renew your nervous system and build stamina kriya - Find a book title - How to use excel qm - Case study treatment plan - Chapter 8 audit planning and analytical procedures solutions - 2021 weekly tax tables - BUS HW - Engr300 - Integrity tests indirectly estimate employee honesty by measuring psychological traits. - Acids bases ph and buffers report sheet answers - Billy hyde drum kit price - Donna eaton mater hospital - Math251 - Primary care respiratory society - As 2293.1 free download - Introduction to java programming and data structures 11th edition - Managerial accounting and cost concepts ppt - Need two responses to discussion questions. 150 words min with references if needed - Between 1879 and 1880, an estimated 40,000-60,000 african-americans migrated to - Range rule of thumb estimate - What is withitness in education - Registered nurse rn preload and afterload - Christmas acrostic poem ideas - Week 4: Discussion: Activities Encapsulated by Working With Data Options Menu: Forum - Vertical translation of a parabola - Calculus - NEED IN 10 HOURS or LESS - Fred 3 camp fred loser song lyrics - Guest and chrimes valves - Woolworths car insurance drive less pay less - Variable resistor in multisim - Geberit mepla installation guide - Pilot project evaluation template - Www footprintnetwork org en index php gfn page personal footprint - Eyebrow threading during pregnancy in tamil - The zaf radiator company uses a normal costing system - Sap hybris administration guide - Behaviour and design of steel structures to as4100 pdf - 23 kurnalpi turn wanneroo - Education planner learning style quiz - Full down cheer stunt - Hsc 2006 chemistry answers - Final Research paper - All bakery products list - Coir log suppliers brisbane - Report writing recommendations example - Ap biology 2018 free response answers - What are the dimensions of a2 paper in cm - John b. watson argued that psychologists should - Topic Selection & Developing a Thesis - Minimum pitch for custom orb - Week 3 quiz information - Week 5 Discussion question - Collective unconscious psychology definition - Who dies in hamlet in order - Mathsphere co uk worksheets - Plant cell analogy amusement park - Mad dogs englishmen and the errant anthropologist pdf - I have a bad habit of biting my nails - Http www ushistory org civ 6a asp - NURS-6050N-66/NURS-6050C-66-Policy & Advocacy - Words their way derivational relations list - Corporate leadership council talent management - Ing credit card pin - Ilearn qld health login - Stakeholders in the intervention - Correct pronoun antecedent agreement - Jzzmusic - Need two discussion questions 150 word max each w/reference - Using sources effectively 5th edition pdf - Ofcom simple uk licence - Definition Essay - Fractals googols and other mathematical tales - Prepare a budgeted income statement for 2014 - Martingale asset management case study solution - Sr flip flop characteristic table - Support activities and primary activities - Whats an interpretive text - Internal control questionnaire revenue cycle - Occupy all streets book amazon - Retrospect theme excel 2013 download - Paintball promoter of violence or healthy fun