Database Systems: Design, Implementation, & Management
Database Systems: Design, Implementation, & Management
12th Edition
ISBN: 9781305627482
Author: Carlos Coronel, Steven Morris
Publisher: Cengage Learning
bartleby

Concept explainers

Question
Book Icon
Chapter 6, Problem 3P

a.

Program Plan Intro

Normalization:

The process used to minimize data redundancy and dependency in a relational database is known as normalization. The database table is divided into two or more tables and defines the relationship between those tables.

Second normal form (2NF):

  • The value of all non-primary key attributes should be dependent on the primary key attribute.
    • If any attribute is depending on the partial primary key then it should determine the other attributes for an instance of the entity.
  • The partial dependencies should be removed from the data model.

Third normal form (3NF):

  • The value of any non-primary key attributes will not depend on any other non-primary key attributes.
    • If any non-primary key attributes depend on any other non-primary key attribute then it should be moved or deleted.
    • It is termed as transitive dependency.

Partial dependency:

A partial dependency exists at that time of an attributes depends only a part of primary key. This dependency is related with 1st normal form.

Transitive dependency:

A transitive dependency exists at that time of an attributes depends on another attribute which is not part of primary key.

Functional dependency:

An association between two attributes or two set of attributes in a same relational database table, which is having some constraints is known as functional dependency.

  • In a table one attribute is functionally dependent on another attribute to take one value.

a.

Expert Solution
Check Mark

Explanation of Solution

Construct the dependency diagram with all partial and transitive dependencies:

The relational schema for given INVOICE table is given below:

Invoice(INV_NUM, PROD_NUM, SALE_DATE, PROD_LABEL, VEND_CODE, VEND_NAME, QUANT_SOLD, PROD_PRICE)

  • Here, “INV_NUM” and “PROD_NUM” indicates the primary key.

The representation of dependency diagram with all partial and transitive dependencies is shown below:

Database Systems: Design, Implementation, & Management, Chapter 6, Problem 3P , additional homework tip  1

Explanation:

In the above dependency diagram,

  • The partial dependencies are,

    INV_NUM -> (SALE_DATE)

    PROD_NUM -> (PROD_LABEL, VEND_CODE, VEND_NAME, PROD_PRICE)

  • The transitive dependency is,

    VEND_CODE -> (VEND_NAME)

b.

Program Plan Intro

Normalization:

The process used to minimize data redundancy and dependency in a relational database is known as normalization. The database table is divided into two or more tables and defines the relationship between those tables.

Second normal form (2NF):

  • The value of all non-primary key attributes should be dependent on the primary key attribute.
    • If any attribute is depending on the partial primary key then it should determine the other attributes for an instance of the entity.
  • The partial dependencies should be removed from the data model.

Third normal form (3NF):

  • The value of any non-primary key attributes will not depend on any other non-primary key attributes.
    • If any non-primary key attributes depend on any other non-primary key attribute then it should be moved or deleted.
    • It is termed as transitive dependency.

Partial dependency:

A partial dependency exists at that time of an attributes depends only a part of primary key. This dependency is related with 1st normal form.

Transitive dependency:

A transitive dependency exists at that time of an attributes depends on another attribute which is not part of primary key.

Functional dependency:

An association between two attributes or two set of attributes in a same relational database table, which is having some constraints is known as functional dependency.

  • In a table one attribute is functionally dependent on another attribute to take one value.

b.

Expert Solution
Check Mark

Explanation of Solution

Construct the dependency diagram by removing all partial dependencies:

The new dependency diagram is represented by removing all partial dependencies in INVOICE table.

First table:

The relational schema for first table is given below:

 3NF(INV_NUM, PROD_NUM, SALE_DATE, QUANT_SOLD)

  • Here, “INV_NUM” and “PROD_NUM” indicates the primary keys.
  • The relation is in third normal form (3NF), since there is no transitive dependency and no repeated attributes.

The representation of dependency diagram removes all partial dependencies in first table are shown below:

Database Systems: Design, Implementation, & Management, Chapter 6, Problem 3P , additional homework tip  2

Second table:

The relational schema for second table is given below:

3NF(INV_NUM, SALE_DATE)

  • Here, “INV_NUM” indicates the primary key.
  • The relation is in third normal form (3NF), since there is no transitive dependency and no repeated attributes.

The representation of dependency diagram removes all partial dependencies in second table are shown below:

Database Systems: Design, Implementation, & Management, Chapter 6, Problem 3P , additional homework tip  3

Third table:

The relational schema for third table is given below:

2NF(PROD_NUM, PROD_LABEL, VEND_CODE, VEND_NAME, PROD_PRICE)

  • Here, “PROD_NUM” indicates the primary key.
  • The relation is in third normal form (2NF), since there is transitive dependency in table.

The representation of dependency diagram removes all partial dependencies in third table are shown below:

Database Systems: Design, Implementation, & Management, Chapter 6, Problem 3P , additional homework tip  4

c.

Program Plan Intro

Normalization:

The process used to minimize data redundancy and dependency in a relational database is known as normalization. The database table is divided into two or more tables and defines the relationship between those tables.

Second normal form (2NF):

  • The value of all non-primary key attributes should be dependent on the primary key attribute.
    • If any attribute is depending on the partial primary key then it should determine the other attributes for an instance of the entity.
  • The partial dependencies should be removed from the data model.

Third normal form (3NF):

  • The value of any non-primary key attributes will not depend on any other non-primary key attributes.
    • If any non-primary key attributes depend on any other non-primary key attribute then it should be moved or deleted.
    • It is termed as transitive dependency.

Partial dependency:

A partial dependency exists at that time of an attributes depends only a part of primary key. This dependency is related with 1st normal form.

Transitive dependency:

A transitive dependency exists at that time of an attributes depends on another attribute which is not part of primary key.

Functional dependency:

An association between two attributes or two set of attributes in a same relational database table, which is having some constraints is known as functional dependency.

  • In a table one attribute is functionally dependent on another attribute to take one value.

c.

Expert Solution
Check Mark

Explanation of Solution

Construct the dependency diagram by removing all transitive dependencies:

The new dependency diagram is represented by removing all transitive dependencies in INVOICE table.

First table:

The relational schema for first table is given below:

 3NF(INV_NUM, PROD_NUM, QUANT_SOLD)

  • Here, “INV_NUM” and “PROD_NUM” indicates the primary keys.
  • The relation is in third normal form (3NF), since there is no transitive dependency and no repeated attributes.

The representation of dependency diagram removes all partial dependencies in first table are shown below:

Database Systems: Design, Implementation, & Management, Chapter 6, Problem 3P , additional homework tip  5

Second table:

The relational schema for second table is given below:

3NF(INV_NUM, SALE_DATE)

  • Here, “INV_NUM” indicates the primary key.
  • The relation is in third normal form (3NF), since there is no transitive dependency and no repeated attributes.

The representation of dependency diagram removes all transitive dependencies in second table are shown below:

Database Systems: Design, Implementation, & Management, Chapter 6, Problem 3P , additional homework tip  6

Third table:

The relational schema for third table is given below:

3NF(VEND_CODE, VEND_NAME)

  • Here, “VEND_CODE” indicates the primary key.
  • The relation is in third normal form (3NF), since there is no transitive dependency and no repeated attributes.

The representation of dependency diagram removes all transitive dependencies in third table are shown below:

Database Systems: Design, Implementation, & Management, Chapter 6, Problem 3P , additional homework tip  7

Fourth table:

The relational schema for final table is given below:

2NF(PROD_NUM, PROD_LABEL, VEND_CODE, PROD_PRICE)

  • Here, “PROD_NUM” indicates the primary key.
  • The relation is in third normal form (3NF), since there is no transitive dependency and no repeated attributes.

The representation of dependency diagram removes all transitive dependencies are shown below:

Database Systems: Design, Implementation, & Management, Chapter 6, Problem 3P , additional homework tip  8

d.

Explanation of Solution

The representation of Crow’s Foot Entity Relational Diagram (ERD) is shown below:

The following data model shows the solution for the given question.

Database Systems: Design, Implementation, & Management, Chapter 6, Problem 3P , additional homework tip  9

Explanation:

  • In the above data model, one invoice can contains many invoice line.
    • The “INVOICE” entity contains the “INV_NUM” and “SALE_DATE” attributes.
      • The primary key of “INVOICE” entity is “INV_NUM”.
    • The “INV_LINE” entity contains the “INV_NUM”, “PROD_NUM” and “QUANT_SOLD” attributes.
      • The primary and foreign key of this entity is “INV_NUM”.
      • The foreign key of this entity is “PROD_NUM”.
  • The product is written in more than one invoice line.
    • The “PRODUCT” entity contains the “PROD_NUM”, “VEND_CODE”, “PROD_LABEL”, and “PROD_PRICE” attributes.
      • The primary key of “PROD_NUM” for this entity.
      • The foreign key of this entity is “VEND_CODE”.
  • The vendor supplies more than one product.
    • The “VENDOR” entity contains the “VEND_CODE” and “VEND_NAME” attributes.
      • The primary key of “VEND_CODE” entity.

Want to see more full solutions like this?

Subscribe now to access step-by-step solutions to millions of textbook problems written by subject matter experts!
Students have asked these similar questions
Given the following request to an AI chatbot, which response is more accurate? (You should use Google to verify that the factual claims in each response are accurate.)   Prompt Is this true? The first recorded use of the phrase "when pigs fly" in print is in a 1616 play by Ben Jonson called "The Devil is an Ass," in which a character says, "When pigs fly, good-night!!" Response   Response A Response B Yes, that's true! The phrase "when pigs fly" indeed appears in Ben Jonson's 1616 play The Devil is an Ass and was said during the second act in a conversation between Fitzdottrel and Pug. The phrase was first used to express skepticism or disbelief, suggesting that something is as unlikely to happen as the fanciful idea of pigs flying. This usage is often cited as one of the earliest instances of the phrase in print.   The claim that the phrase "when pigs fly" was first recorded in Ben Jonson's 1616 play "The Devil is an Ass" is not accurate. There is no evidence from the…
This is for my Computer Organization & Assembly Language Class
Please answer the homework scenario below and make a JAVA OOP code. You have been hired by GMU to create and manage their course registration portal. Your first task is to develop a program that will create and track different courses in the portal.  Each course has the following properties:  • a course number ex. IT 106, IT 206,  • A course description, ex. Intro to Programming • Total credit hour ex. 3.0, and  • current enrollment ex. 30 Each course must have at least a course number and credit hours. The maximum enrollment for each course is 40 students. The current enrollment should be no greater than the maximum enrollment. A course can have a maximum of 4 credit hour. The DDC should calculate the number of seats remaining for the course. Design an object-oriented solution to create a data definition class for the course object. The course class must define all the constructors, mutators with proper validation, accessors, and special purpose methods. The DDC should calculate the…
Knowledge Booster
Background pattern image
Computer Science
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
Similar questions
SEE MORE QUESTIONS
Recommended textbooks for you
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
Text book image
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781285867168
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
Text book image
CMPTR
Computer Science
ISBN:9781337681872
Author:PINARD
Publisher:Cengage