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 are requirements of a relational database

18/10/2021 Client: muhammad11 Deadline: 2 Day

CHAPTER 10: FUNCTIONAL DEPENDENCIES AND NORMALIZATION FOR RELATIONAL DATABASES

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}

10.28 Solution to come

10.29 Given relation R(A,B,C,D,E) with dependencies

C.AB

E.CD

B.DE

is AB a candidate key?

is ABD a candidate key?

10.30 Consider the relation R, which has attributes that hold schedules of courses and

sections at a university; R = {CourseNo, SecNo, OfferingDept, CreditHours,

CourseLevel, InstructorSSN, Semester, Year, Days_Hours, RoomNo,

NoOfStudents}. Suppose that the following functional dependencies hold on R:

{CourseNo} -> {OfferingDept, CreditHours, CourseLevel}

{CourseNo, SecNo, Semester, Year} ->

{Days_Hours, RoomNo, NoOfStudents, InstructorSSN}

{RoomNo, Days_Hours, Semester, Year} -> {InstructorSSN, CourseNo, SecNo}

Try to determine which sets of attributes form keys of R. How would you

normalize this relation?

Answer:

10.31 Consider the following relations for an order-processing application database at ABC, Inc.

ORDER (O#, Odate, Cust#, Total_amount)

ORDER-ITEM (O#, I#, Qty_ordered, Total_price, Discount%)

Assume that each item has a different discount. The Total_price refers to one item, Odate is the date on which the order was placed, and the Total_amount is the amount of the order. If we apply a natural join on the relations Order-Item and Order in this database, what does the resulting relation schema look like? What will be its key? Show the FDs in this resulting relation. Is it in 2NF? Is it in 3NF? Why or why not? (State any assumptions you make.)

O# .Total_amount

It is not in 2NF, as attributes Odate, Cut#, and Total_amount are only partially

dependent on the primary key, O#I#

Nor is it in 3NF, as a 2NF is a requirement for 3NF.

10.32 Consider the following relation:

CAR_SALE(Car#, Date_sold, Salesman#, Commision%, Discount_amt

Assume that a car may be sold by multiple salesmen and hence {CAR#, SALESMAN#} is the primary key. Additional dependencies are:

Date_sold ->Discount_amt

and

Salesman# ->commission%

Based on the given primary key, is this relation in 1NF, 2NF, or 3NF? Why or why not? How would you successively normalize it completely?

10.33 Consider the following relation for published books:

BOOK (Book_title, Authorname, Book_type, Listprice, Author_affil, Publisher)

Author_affil referes to the affiliation of the author. Suppose the following dependencies exist:

Book_title -> Publisher, Book_type

Book_type -> Listprice

Author_name -> Author-affil

(a) What normal form is the relation in? Explain your answer.

(b) Apply normalization until you cannot decompose the relations further. State the reasons behind each decomposition.

(a)The key for this relation is Book_title,Authorname. This relation is in 1NF and not in

2NF as no attributes are FFD on the key. It is also not in 3NF.

(b) 2NF decomposition:

Book0(Book_title, Authorname)

Book1(Book_title, Publisher, Book_type, Listprice)

Book2(Authorname, Author_affil)

This decomposition eliminates the partial dependencies.

3NF decomposition:

Book0(Book_title, Authorname)

Book1-1(Book_title, Publisher, Book_type)

Book1-2(Book_type, Listprice)

Book2(Authorname, Author_affil)

This decomposition eliminates the transitive dependency of Listprice

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:

Smart Accountants
Isabella K.
Homework Master
Math Exam Success
Assignment Hut
Essay & Assignment Help
Writer Writer Name Offer Chat
Smart Accountants

ONLINE

Smart Accountants

I will be delighted to work on your project. As an experienced writer, I can provide you top quality, well researched, concise and error-free work within your provided deadline at very reasonable prices.

$15 Chat With Writer
Isabella K.

ONLINE

Isabella K.

I am an experienced researcher here with master education. After reading your posting, I feel, you need an expert research writer to complete your project.Thank You

$25 Chat With Writer
Homework Master

ONLINE

Homework Master

I will provide you with the well organized and well research papers from different primary and secondary sources will write the content that will support your points.

$21 Chat With Writer
Math Exam Success

ONLINE

Math Exam Success

I am an elite class writer with more than 6 years of experience as an academic writer. I will provide you the 100 percent original and plagiarism-free content.

$45 Chat With Writer
Assignment Hut

ONLINE

Assignment Hut

I have read your project details and I can provide you QUALITY WORK within your given timeline and budget.

$30 Chat With Writer
Essay & Assignment Help

ONLINE

Essay & Assignment Help

I am an experienced researcher here with master education. After reading your posting, I feel, you need an expert research writer to complete your project.Thank You

$36 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

Mycampus aionline edu login page - Silhouette cameo failed to locate framework dll - Tcp ip attack lab solution - Reaction to movie vertigo - Electrical safety certificate template - Barbara ehrenreich serving in florida questions - Write a chapter review that 750 - 1000 word - Family relatives and kith - Self management project topic selection - Psychometric success spatial ability - Nursing Research - A sample of observation for 6 years old child - The scarlet letter chapter 12 questions and answers - Guided reading ideas ks1 - What is under armour's mission statement - Scu harvard referencing - Graduate visa application form - Blood pressure measurement in pregnancy - Union carbide bhopal disaster case study - Mod 1 Peer Review - Excel Statics - Use the above adjusted trial balance - Paper writing - 2015 methods exam 2 solutions - Project Mgmt Process - Reflection, Discussion and Assignment - A proper noun always begins with a - Celena duncan i want your love back - L oreal paris case study - Egypt is the gift of the nile - How many pages is 1400 words double spaced - When forming the disaccharide maltose from two glucose monosaccharides: - West bar surgery banbury - Financial accounting and reporting edition answers - In praise of folly summary sparknotes - Yeast fermentation lab balloon answers - Rank indicator in lte - Textbook summary ( 4pages summary ) - Discussion #2 Initial Post - Multiple exemplars aba - At the end of Chapter 11, Chua says, “All decent parents want to do what’s best for their children. The Chinese just have a totally different idea of how to do that” - Elizabeth peabody theory on early childhood education - Organ Leader & Decision Making - Eli ginzberg career choice theory - Unit 7 Discussions (ECO202) & (SOC101) - Intercultural Competence Worksheet - Clinical services building rnsh - Barwon heads primary school principal - Explain Nola Pender's model and how can you apply it to your nursing practice. Explain in 150 words - Animal testing persuasive speech - Holiday homework design ideas - The berndt corporation expects to have sales - Yan yean recycle centre - Current carrying capacity of 1.5mm cable australia - Supply Chain management - Excel module 1 sam project a - Nat translation table example - Biozone year 12 biology - Spencer supplies stock is currently selling - Critical thinking a user's manual 2nd edition - Planning involves all of the following except - Project Management - Science Experiment: Analysis - Km 3000 portable key mill - Short 150-200 words writing about plastic pollution - Against the grain windows and doors - St vincent's hospital pathology - Www luton gov uk parking - FOUNDATIONS OF DEVELOPMENT: THEORIES AND HISTORY MDP 500 Assignment 1 - Shape of the state of nevada - He paid the debt lyrics paul williams - General guide for cranes - The bloody chamber context - Hands on labs microscope - Djanogly learning resource centre - Kuttler linear algebra - Discussion 1: Emotional Abuse - Managing Risk in information system - Leadership Creatiity and AI Transformation - Top ten badmash in pakistan - Discussion Board - Research paper - Essay questions on socialization - Army sharp essay - 0.55 as a fraction in simplest form - Performance, Legal and Ethical Issues - Planet fitness protein commercial - Never had it so good speech - Who was prometheus married to - Sling login and password - White duck quarter exterior - Target brand proactiv - Adulthood - Discussion 1 - Smoke all day ka ikena ukulele chords - Hidden secrets answers - How to find rs configuration - Donald super career development theory - American HISTORY ASSIgnment - Ocr a level biology multiple choice questions - Social science question